目录
Using filesort文件排序原理详解 filesort文件排序方fa：
资料来源于图灵课堂，本贴是学习后的归纳、记录，仅作为个人学习使用。
学习优化的前提是明白mysql索引底层的数据结构，对于理解和学习尤为重要。
创建实例表用于实践：
向表中用存储函数插入十万条记录，这些数据没任何意义。
再创建一张employees_copy，里面插入三条数据，主要是为了在数据量上和employees形成对比。
举一个大家不容易理解的综合例子：
EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
结论：联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，如果使用联合索引就需要大量回表，效率不高，还不如就全表扫描
EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 A ND position ='manager';不过强制索引的效率不一定就比全表高，因为全表不需要回表，所以具体情况具体分析。
EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
结论：在查询字段被联合索引覆盖了之后，走的联合索引。因为联合索引包含所有查询字段，不需要回表进行二次查询，因此此时走索引效率高。
EXPLAIN SELECT * FROM employees WHERE name
in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
or同理，不过当我们使用只有三条数据的employees_copy进行查询的时候
EXPLAIN SELECT * FROM employees_copy WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND posit ion ='manager';
可见此时没有走联合索引，道理同覆盖索引优化，表数据少mysql的会选择走all（不一定）
1 EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager'
即便是使用 employees_copy作为查询表，仍然会走索引。（具体原有不知道，可能写死了。。）
关于索引下推 ：
这里用到了 like lilei%这样的条件，按道理来说这样确定出来的name后面的age以及position无法保证其有序性，因此是不会走age以及position的索引字段，但是我们看到结果的key_len说明三个字段都使用了，这时候就是mysql5.6之后的索引下推的设计。
在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 'LiLei' 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。
MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。
索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。
为什么范围查找Mysql没有用索引下推优化？
估计应该是Mysql认为范围查找过滤的结果集过大，like xxxx% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like xxxx% 用了索引下推优化，当然这也不是绝对的，有时like xxxxx% 也不一定就会走索引下推。
mysql的索引选择机制：
EXPLAIN select * from employees where name > 'a';
mysql> EXPLAIN select * from employees where name > 'zzz' ; 
可以看到都是select* ，都需要回表，但是mysql选择all和联合索引。这其中的原因也和数据量有关。在mysql执行的时候，会预先分析一下，它认为name>‘zzz‘的数据量小，因此可以接受回表的时间，就选择了索引。
在mysql内部可以用trace工具查看mysql在执行前的分析报告。
1 mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace
2 mysql> select * from employees where name > 'a' order by position; 3 mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
常见sql深入优化：
1、Order by与Group by优化
Case1：
结论：利用最左前缀法则：中间字段不能断，因此查询用到了name索引
Case 2：
从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了 age，索引中按照position排序没有办法保证age的有序性，出现了Using filesort。
Case 3：
查找只用到索引name，age和position用于排序，因为是先按照age然后是position排序，因此没有使用using dilesort
Case 4：
和Case 3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为 name,age,position，但是排序的时候age和position颠倒位置了 。
Case 5：
与Case 4对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，相当于age是有序的，所以索引未颠倒，实际执行的排序只有 order by position.不会出现Using filesort。
Case 6
虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。
Case 7:
对于排序来说，多个相等条件也是范围查询
Case 8：
可以用覆盖索引优化
优化总结：
- MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低。
- order by满足两种情况会使用Using index。
- order by语句使用索引最左前列。
- 使用where子句与order by子句条件列组合满足索引最左前列。
- 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
- 如果order by的条件不在索引列上，就会产生Using filesort。
- 能用覆盖索引尽量用覆盖索引
- group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group
by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。
Using filesort文件排序原理详解 filesort文件排序方fa：
单路排序：是一次性取出满足条件行的所有字段，然后在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 ，那么使用 双路排序模∙式。
单路排序的详细过程：
- 从索引name找到第一个满足 name = ‘zhuge’ 条件的主键 id
- 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
- 从索引name找到下一个满足 name = ‘zhuge’ 条件的主键 id
- 重复步骤 2、3 直到不满足 name = ‘zhuge’
- 对 sort_buffer 中的数据按照字段 position 进行排序
- 返回结果给客户端双路排序的详细过程
双路排序的详细过程：
- 从索引 name 找到第一个满足 name = ‘zhuge’ 的主键id
- 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
- 从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id
- 重复 3、4 直到不满足 name = ‘zhuge’
- 对 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很多参数设置都是做过优化的，不要轻易调整。
索引设计原则：
- 代码先行，索引后上不知大家一般是怎么给数据表建立索引的，是建完表马上就建立索引吗？这其实是不对的，一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。
- 联合索引尽量覆盖条件，比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的 where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。
- 不要在小基数字段上建立索引，
索引 基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段， 其值不是男就是女，那么该字段的基数就是2。如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查 找的优势来。
268
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
