导航：
【Java笔记+踩坑汇总】Java基础+JavaWeb+SSM+SpringBoot+SpringCloud+瑞吉外卖/谷粒商城/学成在线+设计模式+面试题汇总+性能调优/架构设计+源码解析
最新MySQL调优的文章：
【MySQL调优】如何进行MySQL调优？从参数、数据建模、索引、SQL语句等方向，三万字详细解读MySQL的性能优化方案（2024版）-CSDN博客
目录
MySQL调优主要分为三个步骤：监控报警、 排查慢SQL、MySQL调优。
一、监控报警
监控工具（例如Prometheus+Grafana）监控MySQL，发现查询性能变慢，报警提醒运维人员：
二、排查慢SQL
2.1 开启慢查询日志
查看慢查询次数：
show status like 'slow_queries';
开启慢查询日志，修改慢查询阈值：
set slow_query_log='ON'; #开启慢查询日志 set long_query_time = 1; #设置慢查询阈值
2.2 找出最慢的几条SQL
慢查询日志分析工具mysqldumpslow找到最慢的几条语句：
mysqldumpslow 命令的具体参数如下：
- -a: 不将数字抽象成N，字符串抽象成S
- -s: 是表示按照何种方式排序：
- c: 访问次数
- l: 锁定时间
- r: 返回记录
- t: 查询时间
- al:平均锁定时间
- ar:平均返回记录数
- at:平均查询时间 （默认方式）
- ac:平均查询次数
- -t: 即为返回前面多少条的数据；
- -g: 后边搭配一个正则匹配模式，大小写不敏感的；
示例： 按照查询时间排序，查看前五条 慢查询SQL 语句
#命令行，按照查询时间排序，查看前五条 慢查询SQL 语句 mysqldumpslow -s t -t 5 /var/lib/mysql/xxx-slow.log
2.3 分析查询计划
explan分析sql执行计划（访问类型、记录条数、索引长度等）；主要关注字段：
- possible_keys：查询可能用到的索引
- key：实际使用的索引
- key_len：实际使用的索引的字节数长度。
- type：访问类型，看有没有走索引。all（全表扫描），ref（命中非唯一索引），const（命中主键/唯一索引）、range(范围索引查询)、index_merge(使用多个索引)、 system(一行记录时,快速查询)。
- Extra：额外信息。看有没有走索引。
- using index：覆盖索引，不回表。
- using filesort：需要额外的排序。排序分为索引排序和filesort排序，索引排序一般更快，深分页等查询数据量大时filesort更快。
- using index condition：索引下推。MySQL5.6开始支持。联合索引某字段是模糊查询（非左模糊）时，该字段进行条件判断后，后面几个字段可以直接条件判断，判断过滤后再回表对不包含在联合索引内的字段条件进行判断。
- using where：不走索引，全表扫描。
执行计划各个列的作用
| id | 每个SELECT子句或者join操作都会被分配一个唯一的编号，编号越小优先级越高，id相同的语句可以被认为是一组。id为NULL表示独立的子查询，子查询优先级都比主查询高。 |
| select_type | 查询的类型。主查询(primary)、普通查询(simple)、联合查询、子查询(subquery)、derived(from表临时子查询)、union(union后查询)、union result() |
| table | 表名。显示当前这行的数据是哪个表的。 |
| partitions | 匹配的分区信息。如果表未分区则为NULL。 |
| type | 访问类型，根据索引、全表扫描等方法来执行查询的优化策略。all（全表扫描），ref（命中非唯一索引），const（命中主键/唯一索引）、range(范围索引查询)、index_merge(使用多个索引)、 system(一行记录时,快速查询)。 |
| possible_keys | 可能用到的索引。列出MySQL能够使用哪些索引来查询。 如果该列只有一个possible_keys，通常意味着这个查询是高效的。 如果这个列有多个possible_keys，并且MySQL只使用了其中一个，则需要考虑是否需要在该列上增加一个联合索引。 |
| key | 实际上使用的索引。如果没有明确的指定KEY，MySQL会根据查询条件自动选择最优的索引。 |
| key_len | 实际使用到索引的字节数长度。越短表示越快，一般表示索引字段越小越好。 |
| ref | 当使用索引列等值查询时，与索引列进行等值匹配的对象信息。常量等值查询const, 表达式/函数使用到时func,关联查询显示关联字段名 |
| rows | 预估的需要读取的记录条数。数值越小越好，表示结果集越小，查询越高效。 |
| filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比。这个值越小越好，说明可通过索引直接返回数据。 |
| Extra | 额外信息。看有没有走索引，还是全表扫描了。一般搭配type字段看。Using index(使用到覆盖索引)、Using where(未使用索引查询)、Using temporary(临时表存储结果集.排序/分组会使用)、Using filesort(排序操作未用索引)、Using join buffer(连接条件未用索引)、Impossible where(where约束语句可能有问题导致没有结果集) |
三、MySQL调优
3.1 基础优化
3.1.1 缓存优化
mysql调整缓冲池大小等参数；引入redis。tip：InnoDB使用缓冲池缓存记录和索引
3.1.2 硬件优化
服务器加内存条、升级SSD固态硬盘、把磁盘I/O分散在多个设备、配置多处理器。
3.1.3 参数优化
关闭不必要的服务和日志：调优结束关闭慢查询日志；
调整最大连接数：max_connections ；
线程池缓存线程数：thread_cache_size，缓存空闲线程，有连接时直接分配该线程处理连接；
缓冲池大小：innodb_buffer_pool_size 。
3.1.4 定期清理垃圾
对于不再使用的表、数据、日志、缓存等，应该及时清理，避免占用过多的MySQL资源，从而提高MySQL的性能。
3.1.5 使用合适的存储引擎
MyISAM：适合读取频繁，写入较少的场景（因为表级锁、B+树叶存地址）
InnoDB：适合并发写入的场景（因为行级锁、B+树叶存记录）。
InnoDB：支持外键和事务，行锁适合高并发，缓存索引和数据，内存要求高（因为要缓存索引和记录），适合存大数据量，增删改性能更优（行级锁高并发），耗费磁盘（因为有多个非聚簇索引，索引可能比记录空间还大）。
MyISAM：不支持外键和事务，表锁不适合高并发，缓存索引和数据地址，内存要求低（因为不用缓存记录），查询性能更优（因为查询时InnoDB要维护MVCC一致，而且多缓存了记录），节省磁盘（因为磁盘不存完整记录）。
对比 |
InnoDB |
MyISAM |
特点 |
支持外键和事务 |
不支持外键和事务 |
行表锁 |
行锁，操作时只锁某一行，不对其它行有影响， 适合高并发的操作 |
表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 |
缓存 |
缓存索引和数据，对内存要求较高，而且内存大小对性能有决定性的影响 |
只缓存索引，不缓存真实数据 |
关注点 |
事务：并发写、事务、更大资源 |
性能：节省资源、消耗少、简单业务、查询快 |
默认使用 |
5.5及其之后 | 5.5之前 |
3.1.6 读写分离
读写分离：读写分离能有效提高查询性能。主从同步用到bin log和relay log。
3.1.7 分库分表
分库分表：数据量级到达千万级以上后，垂直拆分（分库）、水平拆分（分表）、垂直+水平拆分（分库分表）。
概念：
- 只分表：单表数据量大，读写出现瓶颈，这个表所在的库还可以支撑未来几年的增长。
- 只分库：整个数据库读写出现性能瓶颈，将整个库拆开。
- 分库分表：单表数据量大，所在库也出现性能瓶颈，就要既分库又分表。
- 垂直拆分：把字段分开。例如spu表的pic字段特别长，建议把这个pic字段拆到另一个表（同库或不同库）。
- 水平拆分：把记录分开。例如表数据量到达百万，我们拆成四张20万的表。
拆分原则：
| 数据量增长情况 | 数据表类型 | 优化核心思想 |
| 数据量为千万级，是一个相对稳定的数据量 | 状态表 | 能不拆就不拆读需求水平扩展 |
| 数据量为千万级，可能达到亿级或者更高 | 流水表 | 业务拆分，面向分布式存储设计 |
| 数据量为千万级，可能达到亿级或者更高 | 流水表 | 设计数据统计需求存储的分布式扩展 |
| 数据量为千万级，不应该有这么多的数据 | 配置表 | 小而简，避免大一统 |
分库分表步骤：
- MySQL调优：数据量能稳定在千万级，近几年不会到达亿级，其实是不用着急拆的，先尝试MySQL调优，优化读写性能。
- 目标评估：评估拆几个库、表，举例: 当前20亿，5年后评估为100亿。分几个表? 分几个库?解答:一个合理的答案，1024个表，16个库按1024个表算，拆分完单表200万，5年后为1000万.1024个表*200w≈100亿
- 表拆分：
- 业务层拆分：混合业务拆分为独立业务、冷热分离
- 数据层拆分：
- 按日期拆分：这种使用方式比较普遍，尤其是按照日期维度的拆分，其实在程序层面的改动很小，但是扩展性方面的收益很大。
- 日维度拆分，如test_20191021
- 月维度拆分,如test_201910
- 年维度拆分,如test_2019
- 按主键范围拆分：例如【1,200w】主键在一个表，【200w，400w】主键在一个表。优点是单表数据量可控。缺点是流量无法分摊，写操作集中在最后面的表。
- 中间表映射：表随意拆分，引入中间表记录查询的字段值，以及它对应的数据在哪个表里。优点是灵活。确定是引入中间表让流程变复杂。
- hash切分：sharding_key%N。优点是数据分片均匀，流量分摊。缺点是扩容需要迁移数据，跨节点查询问题。
- 按分区拆分：hash,range等方式。不建议，因为数据其实难以实现水平扩展。
- sharding_key（分表字段）选择：尽量选择查询频率最高的字段，然后根据表拆分方式选择字段。
- 代码改造：修改代码里的查询、更新语句，以便让其适应分库分表后的情况。
- 数据迁移：最简单的就是停机迁移，复杂点的就是不停机迁移，要考虑增量同步和全量同步的问题。
- 全量同步：老库到新库的数据迁移，要控制好迁移效率，解决增量数据的一致性。
- 定时任务：定时任务查老库写新库
- 中间件：使用中间件迁移数据
- 增量同步：老库迁移到新库期间，新增删改命令的落库不能出错
- 同步双写：同步写新库和老库；
- 异步双写（推荐）： 写老库，监听binlog异步同步到新库
- 中间件同步工具：通过一定的规则将数据同步到目标库表
- 数据一致性校验和补偿：假设采用异步双写方案，在迁移完成后，逐条对比新老库数据，一致则跳过，不一致则补偿：
- 新库存在，老库不存在：新库删除数据
- 新库不存在，老库存在：新库插入数据
- 新库存在、老库存在：比较所有字段，不一致则将新库更新为老库数据
- 灰度切读：灰度发布指黑（旧版本）与白（新版本）之间，让一些用户继续用旧版本，一些用户开始用新版本，如果用户对新版本没什么意见，就逐步把所有用户迁移到新版本，实现平滑过渡发布。原则：
- 有问题及时切回老库
- 灰度放量先慢后快，每次放量观察一段时间
- 支持灵活的规则：门店维度灰度、百 (万)分比灰度
- 停老用新：下线老库，用新库读写。
3.2 表设计优化
3.2.1 混合业务分表、冷热数据分表
例如把一个大的任务表，分离成任务表和历史任务表，任务表里任务完成后移动到历史任务表。任务表是热数据，历史任务表是冷数据，提高查询性能。
3.2.2 联合查询改为中间关系表
例如属性表和属性分组表，不使用连接查询，使用“属性-属性分组表”存储每条属性与“属性关系”的id。
3.2.3 遵循三个范式
每个属性不可再分、表必须有且只有一个主键、非主键列必须直接依赖于主键
3.2.4 字段建议非空约束
①可能查询出现空指针问题；
②导致聚合函数不准确，因为它会忽略null
③不能用“=”判断，只能用is null判断；
④null和其他值运算只能是null，可能让你不小心把它当成0；
⑤null值比空字符更占用空间，空值长度是0，null长度是1bit；
⑥不覆盖索引情况下，is not null无法用索引
3.2.5 使用冗余字段
虽然列字段不能太多，但为查询效率可增加冗余字段
3.2.6 数据类型优化
整数类型：
考虑好数值范围，前期可以使用int保证稳定性。非负数类型要用UNSIGNED；同样字节数，存储的数值范围更大。主键一般使用bigint,布尔类型tinint
能整数就不要用文本类型：
跟文本类型数据相比，大整数往往占用更少的存储空间。
避免使用TEXT、BLOB数据类：
这两个大数据类型，排序时不能使用临时内存表，只能使用磁盘临时表，效率很差，建议别用，或分表到单独扩展表里。LongBlob类型能存储4G文件；
避免使用枚举类型：
排序很慢。
使用TIMESTAMP存储时间：
TIMESTAMP使用4字节，DATETIME使用8个字节，同时TIMESTAMP具有自动赋值以及自动更新的特性。 缺点是只能存到2038年，MySQL5.6.4版本可以参数配置，自动修改它为BIGINT类型。
DECIMAL存浮点数：
Decimal类型为精准浮点数，在计算时不会丢失精度，尤其是财务相关的金融类数据。占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节。可用于存储比bigint更大的整型数据。
3.3 索引优化
3.3.1 考虑索引失效的11个场景
详细请参考：
尽量全值匹配：
查询age and classId and name时，(age,classId,name)索引比(age,classId)快。
考虑最左前缀：
联合索引把频繁查询的列放左。索引（a,b,c），只能查(a,b,c),(a,b),(a)。
主键尽量有序：
如果主键不有序，需要查找目标位置再插入，并且如果目标位置所在数据页满了就必须得分页，造成性能损耗。可以选择自增策略或MySQL8.0有序UUID策略。
计算、函数导致索引失效：
计算例如where num+1=2，函数例如abs(num)取绝对值
类型转换导致索引失效：
例如name=123，而不是name='123'。又例如使用了不同字符集。
范围条件右边的列索引失效：
例如（a,b,c）联合索引，查询条件a,b,c，如果b使用了范围查询，那么b右边的c索引失效。建议把需要范围查询的字段放在最后。范围包括：(<) (<=) (>) (>=) 和 between。
没覆盖索引时，“不等于”导致索引失效：
因为“不等于”不能精准匹配，全表扫描二级索引树再回表效率不如直接全表扫描聚簇索引树。但使用覆盖索引时，联合索引数据量小，加载到内存所需空间比聚簇索引树小，且不需要回表，索引效率优于全表扫描聚簇索引树。
覆盖索引：一个索引包含了满足查询结果的数据就叫做覆盖索引，不需要回表等操作。
没覆盖索引时，左模糊查询导致索引失效：
例如LIKE '%abc'。因为字符串开头都不能精准匹配。跟上面一个道理。
没覆盖索引时，is not null、not like无法使用索引：
因为不能精准匹配。跟上面一个道理。
“OR”前后存在非索引列，导致索引失效：
MySQL里，即使or左边条件满足，右边条件依然要进行判断。
不同字符集导致索引失败：
建议utf8mb4，不同的字符集进行比较前需要进行 转换 会造成索引失效。
3.3.2 遵循索引设计原则
详细请参考：
- 命名：索引的字段个数尽量别超过5个，命名格式“idx_col1_col2”
- 在频繁查询（特别是分组、范围、排序查询）的列建立索引；
- 频繁更新的表，不要创建过多索引
- 唯一特性的字段，适合创建索引；
- 很长的varchar字段，适合根据区分度和长度创建前缀索引；
- 多个字段都要创建索引时，联合索引优于单值索引；
- 避免创建过多索引，避免索引失效；
- 尽量用有序的字段作为主键索引：防止乱序时新主键前移到已满的数据页，导致插入后分裂数据页，造成性能损耗；
3.3.3 连接查询优化
详细请参考：
外连 接时优先给被驱动表连接字段加索引：
外连接查询时，右表就是被驱动表，建议加索引。因为左表是查所有数据，右表是按条件查询，所以右表的条件字段创建索引价值更高一点。
内连接时优化器自动非索引驱动索引表、小表驱动大表：
先优先选有索引的表做被驱动表。两个表都没有索引时，查询优化器会自动让小表驱动大表。被驱动表的JOIN字段创建索引会极大地提高查询效率。
两表连接字段 类型必须一致：
两个表JOIN字段数据类型保持绝对一致。防止自动类型转换导致索引失效。
3.3.4 子查询优化
详细请参考：
关联替代子查询：能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)。子查询是一个SELECT查询的结果作为另一个SELECT语句的条件。
#取所有不为班长的同学 SELECT a.* FROM student a WHERE a.stuno NOT IN ( SELECT monitor FROM class b WHERE monitor IS NOT NULL ); #优化成关联查询 SELECT a.* FROM student a LEFT OUTER JOIN class b ON a.stuno = b.monitor WHERE b.monitor IS NULL;
多次查询代替子查询：不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。
3.3.5 排序优化
详细请参考：
- 优化器自动选择排序方式：MySQL支持索引排序和FileSort排序，索引保证记录有序性，性能高，推荐使用。FileSort排序是内存中排序，数据量大时产生临时文件在磁盘里排序，效率低还占用大量CPU。并不是说FileSort一定效率低，一些情况它可能效率高。例如没覆盖索引的左模糊、“不等于”、not null等索引失效情况下，全表扫描效率比非聚簇索引树遍历再回表更高。
- 要符合最左前缀：where后条件和order by字段创建联合索引，顺序要需要符合最左前缀。例如索引(a,b,c)，查询where a=1 order by b,c。
- 要么全升序要么全降序：排序顺序必须要么全部DESC，要么全部ASC。乱序会导致索引失效。
- 待排序数量大时，尽管索引没失效，索引效率不如filesort：待排序数据量大约超过一万个，就不走索引走filesort了。建议用limit和where过滤，减少数据量。数据量很大时，索引排序完需要回表查所有数据，性能很差，还不如FileSort在内存中排序效率高。并不是说使用limit一定会走索引排序，关键看的是数据量，数据量过大时优化器会使用FileSort排序。
- 范围查询右边排序索引失效：例如索引(a,b,c)，查询where a>1 order by b,c，导致b,c排序不能走索引，需要filesort。
- 范围查询过滤量大时，优先范围字段加索引：当【范围条件】和【group by 或者 order by】的字段出现二选一时，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。这样即使范围查询导致排序索引失效，效率依然比只索引排序字段时候高。如果只能过滤一点点，那就优先索引放到排序字段上。
- 调优FileSort ：无法使用 Index 排序时，需要对 FileSort 方式进行调优。例如增大sort_buffer_size（排序缓冲区大小）和 max_length_for_sort_data（排序数据最大长度）
3.3.6 分组优化
跟排序基本一个思路。
排序分组都比较耗费cpu，能不用就不用。
where效率高于having。where是分组前过滤，having是分组后过滤。
3.3.7 深分页查询优化
需求是返回第2000000~2000010 的记录
主键有序的表根据主键排序，先过滤再排序：直接查范围之后的几个数据。
EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;
主键不有序的表根据主键排序，先给主键分页，然后内连接原表：当前表内连接排序截取后的主键表，连接字段是主键。因为查主键是在聚簇索引树查，不用回表，排序和分页很快
EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a WHERE t.id = a.id;
主键有序的表根据非主键排序：得到上一页最后一条记录x，那么目标页码的所有记录id都比x.id小（因为逆序，且排序依据其实是age,id，主键自增），目标页码的所有记录age都比x.age小或等于。
EXPLAIN SELECT * FROM student WHERE id<#{x.id} AND age>=#{x.age} ORDER BY age DESC LIMIT 10;
3.3.8 尽量覆盖索引
详细请参考：
一个索引包含了满足查询结果的数据。因为不需要回表，所以查询效率高。覆盖索引时“左模糊”和“不等于”不能让索引失效。
示例：
#没覆盖索引的情况下，左模糊查询导致索引失效 CREATE INDEX idx_age_name ON student(age, NAME); EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';
覆盖索引：一个索引包含了满足查询结果的数据就叫做覆盖索引，不需要回表等操作。
索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。
覆盖索引是非聚簇索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列 （即建索引的字段正好是覆盖查询条件中所涉及的字段）。简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列 。
3.3.9 字符串前缀索引
例如(email(6))，给字符串前缀而不是整个字符串添加索引，前缀长度要根据区分度和长度进行取舍。
示例：
MySQL是支持前缀索引的。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。
mysql> alter table teacher add index index1(email); #或 mysql> alter table teacher add index index2(email(6));
这两种不同的定义在数据结构和存储上有什么区别呢？下图就是这两个索引的示意图。
如果使用的是index1（索引包含整个字符串），执行顺序是这样的：
- 从index1索引树找到满足索引值是’ zhangssxyz@xxx.com’的这条记录，取得ID2的值；
- 回表到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集；
- 取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足email=' zhangssxyz@xxx.com ’的 条件了，循环结束。
这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。
如果使用的是index2（索引包含字符串前缀email(6)），执行顺序是这样的：
- 从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；
- 回表到主键上查到主键值是ID1的行，判断出email的值不是’ zhangssxyz@xxx.com ’，这行记录丢弃；
- 取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到回表到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集；
- 重复上一步，直到在index2上取到的值不是’zhangs’时，循环结束。
也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。前面 已经讲过区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。
3.3.10 尽量使用MySQL5.6支持的索引下推
联合索引某字段是模糊查询（非左模糊）时，该字段进行条件判断后，后面几个字段可以直接条件判断，判断过滤后再回表对不包含在联合索引内的字段条件进行判断。
例如索引(name,age)，查询name like 'z%' and age and address，模糊查询导致age无序。
在联合索引树查询时不止查name，还会判断后面的age，过滤后再回表判断address。而如果关闭了索引下推，联合索引里模糊查询（非左）后面几个字段不能在联合索引树里直接条件判断，必须回表后再判断。
详解：
索引下推(ICP，Index Condition Pushdown)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。
- 如果没有ICP：联合索引某字段是模糊查询（非左模糊）时，该字段进行条件判断后，后面几个字段不能用来直接条件判断，必须回表后再判断。
- 启用ICP 后：联合索引某字段是模糊查询（非左模糊）时，该字段进行条件判断后，后面几个字段可以直接条件判断，判断过滤后再回表对不包含在联合索引内的字段条件进行判断。主要优化点是在回表之前过滤，减少回表次数。主要应用：模糊查询（非左模糊）导致索引里该字段后面的字段无序，必须要回表判断，而使用了索引下推，就不需要回表，直接在联合索引树里判断。
如果没有ICP ，存储引擎会遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，由 MySQL 服务器评古WHERE 后面的条件是否保留行。
启用ICP 后，如果部分 WHERE 条件可以仅使用索引中的列进行筛选，则MySQL 服务器会把这部分WHERE 条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。
好处: ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。但是，ICP的 加速效果 取决于在存储引擎内通过 ICP筛选 的数据的比例。
举例：
不支持索引下推的联合索引：例如索引(name,age)，查询name like 'z%' and age=？，模糊查询导致age无序。在联合索引树查询时只会查name，后面的age乱序不能直接进行条件判断，必须回表后再判断age。
而支持索引下推的联合索引：例如索引(name,age)，查询name like 'z%' and age and address，在联合索引树查询时不止查name，还会判断后面的age，过滤后再回表判断address。
CREATE INDEX idx_name_age ON student(name,age); #索引失败；非覆盖索引时，左模糊导致索引失效 EXPLAIN SELECT * FROM student WHERE name like '%bc%' AND age=30; #索引成功；MySQL5.6引入索引下推，where后面的name和age都在联合索引里，可以又过滤又索引，不用回表，索引生效 EXPLAIN SELECT * FROM student WHERE `name` like 'bc%' AND age=30; #索引成功；name走索引，age用到索引下推过滤，classid不在联合索引里，需要回表。 EXPLAIN SELECT * FROM student WHERE `name` like 'bc%' AND age=30 AND classid=2;
好处: 某些场景下ICP可以大大减少回表次数，提高性能。ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。但是，ICP的 加速效果 取决于在存储引擎内通过 ICP筛选 的数据的比例。
3.3.11 写多读少的场景，尽量用普通索引
查询时普通索引和唯一索引效率差不多；更新时普通索引效率更高，因为有change buffer（写缓存）将更新后的数据页缓存到内存，下次访问时或后台定期会执行merge操作，将该数据页写入磁盘。
change buffer在事务提交时会写入redo log，保证数据持久化。普通索引：不加任何限制条件，如create index idx_name on student(name)。唯一索引：UNIQUE参数限制索引唯一，如create UNIQUE index idx_name on student(name)。
详解：
写缓存（change buffer）：
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话， 在不影响数据一致性的前提下， InooDB会将这些更新操作缓存在change buffer中 ，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
merge ：将change buffer中的操作应用到原数据页，得到最新结果的过程称为 merge 。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge 操作。
如果能够将更新操作先记录在change buffer， 减少读磁盘 ，语句的执行速度会得到明显的提升。而且， 数据读入内存是需要占用 buffer pool 的，所以这种方式还能够 避免占用内存，提高内存利用率。
唯一索引的更新就不能使用change buffer ，实际上也只有普通索引可以使用。
做好区分：
- 读数据用的是缓冲池buffer pool；
- 重做日志有个redo log buffer，是将缓冲池里更新的数据写入redo log buffer，事务提交时根据刷盘策略，将redo log buffer刷盘到redo log file或page cache。
3.4 SQL优化
详细请参考：
合理选用EXISTS 和 IN ：
遵循小表驱动大表原则，左边表小就是EXISTS，左边表大就用IN。
尽量COUNT(1)或COUNT(*)：
innoDB时，COUNT(1),COUNT(*)时，查询优化器会优先选用有索引的、占用空间最小的二级索引树进行统计，只有找不到非聚簇索引树时采用使用聚簇索引树统计，空间占用大。当然也能COUNT(最小空间二级索引字段)，但麻烦不如交给优化器自动选择。MyISAM时，就无所谓了，用哪个时间复杂度都是O(1)。
尽量SELECT(明确字段)：
建议明确字段，查询优化器解析“*”符号为所有列名耗费时间，并且“*”号无法使用覆盖索引。
全表扫描时尽量用“LIMIT”：
当全表扫描时，并且你知道结果集记录数量时，用limit限制，这样扫描足够数量后就停止，不再扫描完全表了。如果有索引，就无需用limit了。
使用limit N，少用limit M，N：
特别是大表或M比较大的时候。
将长事务拆为多个小事务：
尽量多使用 COMMIT，用编程式事务而不是声明式事务，降低事务粒度。提交事务可以释放的资源：回滚段上用于恢复数据的信息、锁、redo / undo log buffer 中的空间。
先查再删改：
UPDATE、DELETE语句一定要有明确的WHERE条件。
尽量UNION ALL而不是UNION：
UNION A LL不去重，速度更快。
