文章目录
- 索引优化实战(中)：常见sql优化
- Order by与Group by优化
- 文件排序原理详解
- 分页查询优化
索引优化实战(中)：常见sql优化
用到的表优化三一样。employees有name, age, position的一个联合索引
Order by与Group by优化
1.EXPLAIN SELECT * FROM employees where name = ‘edg_is_champion_1’ and position = ‘dev’ order by age;
用到了索引排序。因为Extra字段里没有using filesort。这很好理解，因为用到了name，接着排序用到了age，符合最左前缀原则。
2.EXPLAIN SELECT * FROM employees where name = ‘edg_is_champion_1’ order by position;
没有用到索引排序，因为因为Extra字段里出现using filesort。这也好理解，虽然用到了name长度的索引，但是跳过了age，不符合最左前缀原则。
3.EXPLAIN SELECT * FROM employees where name = ‘edg_is_champion_1’ order by age,position;
用到了索引排序，用到了name的长度，age和position用于排序。
4.EXPLAIN SELECT * FROM employees where name = ‘edg_is_champion_1’ order by position,age;
排序没用到索引，因为先是position在到age排序，不符合最左前缀原则。5.EXPLAIN SELECT * FROM employees WHERE name = ‘edg_is_champion_1’ ORDER BY age ,position desc;
虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的 排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式
EXPLAIN SELECT * FROM employees WHERE name in (‘edg_is_champion_1’, ‘edg_is_champion_2’) ORDER BY age ,position desc;
EXPLAIN SELECT * FROM employees WHERE name > ‘edg_is_champion_1’ ORDER BY age ,position desc;
EXPLAIN SELECT * FROM employees WHERE name like ‘edg_is_champion_%’ ORDER BY age ,position desc;
第一个索引字段使用范围查询，都没法使用索引排序。类似于范围查询后面的索引会失效类似。因为前面一个索引字段是一个范围，不能保证整个后面的字段在整个索引树的有序性。不好理解可以看看优化一。
看了这么多排序索引失效的例子，那么要怎么优化呢。
优化一：对于不满足最左前缀原则的情况，例如2和4。想办法让它满足最左前缀原则。调整排序字段的顺序，或者插入某个条件使其满足最左前缀原则。
优化二：对于范围查询导致排序索引失效的原因。例如6。如果能通过范围的字段排序，也可以使用到索引排序。例如下图name是范围查询，如果通过name来排序能够使用索引排序。
优化三：group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了
文件排序原理详解
根据上面的例子，如果索引不在order by的字段上，就会产生文件排序Using 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 >
简单来说呢，单路排序就是把所有满足sql的where条件的数据(select 的字段)然后在sort buffer(系统内存)进行排序，拍好了之后结果集就出来了。双路呢只是把主键id和排序的字段拿出来。排好序之后，通过id进行回表把相关数据获取出来，在返回结果集。
MySQL 通过比较系统变量max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。
- 如果字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；
- 如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模式。
其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 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实现mysql> select * from employees limit 100000,10; 表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。
这里用时0.049s
优化一：因为这张表的主键是自增而且连续的，因此可以如下优化
select * from employees where id > 100000 limit 10;
用时0.023s 速度快了一倍，但是这种优化方式然并卵，基本上我们的表都不会是连续的。
如果我们根据非主键字段排序的分页查询：例如select * from employees ORDER BY name limit 100000,5;
用时0.139s。通过explain分析这条sql
可以看到没有用上索引，并且排序是按照文件排序。employees 有name、age、position的联合索引，为什么用不到索引呢。
具体原因上一篇优化三讲过：因为扫描二级索引，最后还是要回表，如果扫描的数据量过大，不如直接全盘扫描更直接。也可以通过trace进行分析，扫描二级索引的成本高于全盘扫描，因此优化器放弃使用索引。
知道不走索引的原因，那么怎么优化呢？
其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL 改写如下 select * from employees as e inner join (select id from employees ORDER BY name limit 100000,5) as p on e.id = p.id;
用时0.043s。在通过explain进行分析
用到了索引，也用到了索引排序。
后面还有 join 、count优化，索引设计原则、优化总结，放在一起的话篇幅太长，还是分开写把
