MySQL-索引优化杂述

索引下推

索引下推（Index Condition Pushdown,ICP）：对于辅助的联合索引，正常情况按照最左前缀原则，SELECT * FROM employees WHERE name like ‘LiLei%’ AND age = 22 AND position =‘manager’（以name，age，position为联合索引）这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。
在MySQL5.6之前的版本，这个查询只能在联合里匹配到名字是**‘LiLei’开头**的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，在比对age和position这两个字段的值是否符合。
在MySQL5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推以后，上面按个查询在联合索引里匹配到名字是‘LiLei’开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查郑航数据。

索引下推会减少回表次数，对于innoDB引擎的表索引下推只能用于二级索引，innoDB的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到较少查询全行数据的效果。

为什么范围查找MySQL没有用到索引下推优化
估计应该是MySQL认为范围查找过滤的结果集过大，like kk%在绝大多数情况下看，过滤后的结果集比较小，所以这里MySQL选择给like KK%用了索引下推优化，当然，这也不是绝对的，有事like KK%也不一定就会走索引下推。和优化器处理有关

强制走索引

EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 A ND position ='manager';

加上force index，不过没怎么看到有使用过。

in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

like KK% 一般情况都会走索引

trace 工具用法

set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace
select * from employees where name > 'a' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;

set session optimizer_trace="enabled=off"; ‐‐关闭trace

常见SQL深入优化

• MySql支持两种方式的排序filesort和index,Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
• order by 满足两种情况湖使用Using index。
  1）order by语句使用索引索引最左前列
  2）使用where子语句与order by 子句条件列组合满足索引最左前列。
• 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则
• 如果order by的条件不在索引列上，就会产生Using filesort。
• 能用覆盖索引尽量用覆盖索引。
• group by 与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要用having限定。

Useing filesort文件排序原理详解

filesort文件排序方式

• 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里现实<sort_key,additional_fields>或者<sort_key,packed_additional_fields>
• 双路排序(又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后在sort buffer中进行排序，排序完后需要再次取回其他需要的字段；用trace工具可以看到sort_mode信息里现实<sort_key,rowid>
  MySql通过比较系统变量max_length_for_sort_data(默认1024字节)的大小和需要查询的字段总大小来判断使用哪种排序模式
• 如果字段的总长度小于max_length_for_sort_data,那么使用单路排序
• 如果字段的总长度大于max_length_for_sort_data,那么使用双路排序。
  示例：

 select * from employees where name = 'xinggu' order by position;

单路排序：

• 从索引name找到第一个满足name=‘xinggu’条件的主键id
• 根据主键id取出整行，取出所有字段的值，存入sort_buffer中
• 从索引name找到下一个满足name='xinggu’条件的主键id
• 重复步骤2、3直到不满足name=‘xinggu’
• 对sort_buffer中的数据按照字段position进行排序
• 返回结果给客户端

双路排序：

• 从索引name找到第一个满足name=‘xinggu’的主键id
• 根据主键id取出整行，把排序字段position和主键id这两个字段放到sort buffer中
• 从索引name取下一个满足name=‘xinggu’记录的主键id
• 重复3、4直到不满足name=‘xinggu’
• 对sort buffer中的字段position和主键id按照字段position进行排序
• 遍历排序好的id和字段position，按照id的值回到原表中取出，所有字段的值返回给客户端

其实对比两个排序模式，单路排序会把所有需要查询的字段都放到sort buffer 中，而双路排序只会把主键和需要排序的字段放到sort buffer中进行排序，然后再通过主键回到原表查询需要的字段。

如果MySQL排序内存sort buffer配置的比较小并且没有条件继续增加了，可以适当把max_length_for_sort_data配置小点，让优化器选择使用双路排序算法，可以在sort_buffer中一次排序更多的行，只是需要再根据主键回到原表取数据。

如果MySql排序内存有条件可以配置比较大，可以适当增加max_length_for_sort_data的值，让优化器先选择全字段排序（单路排序），把需要的字段放到sort_buffer中，这样排序后就会直接从内存返回查询结果。

所以，MySql通过max_length_for_sort_data这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。
**注意：**如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer（默认1M）,MySql很多参数设置都是做过优化的，不要轻易调整。

Count

‐‐ 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
 set global query_cache_size=0;
 set global query_cache_type=0;

 EXPLAIN select count(1) from employees;
 EXPLAIN select count(id) from employees;
 EXPLAIN select count(name) from employees;
 EXPLAIN select count(*) from employees;

注意：以上4条sql只有根据某个字段count不会统计字段为null值得数据行
四个SQL的执行计划一样，说明这四个sql执行效率应该差不多
字段有索引：count()≈count(1)>count(字段)>count(主键 id) //字段有索引，count(字段)统计走二级索引，二 级索引存储数据比主键索引少，所以count(字段)>count(主键 id)
字段无索引：count()≈count(1)>count(主键 id)>count(字段) //字段没有索引count(字段)统计走不了索引， count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段) count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出 字段，所以理论上count(1)比count(字段)会快一点。
count() 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用 count(列名)或count(常量)来替代 count()。
为什么对于count(id)，mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索 性能应该更高，mysql内部做了点优化(应该是在5.7版本才优化)。