持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第6天，[点击查看活动详情]
上篇文章介绍了关联查询的优化，本文主要讲述子查询优化、order by排序优化、group by分组优化、limit分页优化。
1. 子查询优化
大家都知道说join的效率比子查询高，能够使用join代替的SQL语句尽量不要使用子查询，为什么？
来看下面两条SQL语句：
子查询：``，查看执行计划如下图：
join连接查询：``查看执行计划如下图：
根据上面子查询和连接查询执行计划分析可得如下结论：
-
根据执行计划可知，子查询的id有2条而join连接查询只有一条，id越多意味着执行次数越多，执行次数和io直接相关。
-
如果在加上索引的情况下，join的性能会更好，原因如下：
- join连接可以使用索引，并且不会产生临时表。
- 子查询产生了临时表，创建和销毁需要消耗时间，并且在有索引的情况下临时表无法使用。
- join查询可以使用到嵌套算法（SNLJ/INLJ/BNLJ,mysql8.0还有hash join算法）加快连接速度，即使没有索引的情况下也可以使用，仔细的伙伴可以看到，在子查询的执行计划截图里，字段
extra里也有：block nested loop，不是说只有join连接查询才有吗？大家仔细看子查询执行计划里id为2的记录，MySQL优化器将class表物化了，物化什么意思呢？实际上就是将select monitor from class变成了一个结果集，然后依赖此结果集生产了表subquery2,再利用临时表去join表student,最终还是一个join连接。
现在大家知道为什么join性能比子查询好了吧。
2. order by排序优化
order byyou优化主要是避免filesort文件排序，如果排序结果集过大，无法在内存中操作，MySQL就会将结果写入磁盘，产生io，执行如下SQL:
explain select * from student s where name like 'abc%' order by id ,name;
新增一个联合索引idx_classId_id_name，索引字段顺序为classId,id,name,如下图：
执行如下SQL:explain select * from student s where classId = 1 order by id ,name;执行计划如下图：
可以看到使用到了联合索引，but,仔细的伙伴可能看到为什么key_len只有5，这是不完全使用索引啊，是这样的，order by排序是在where条件之后的，数据集都已经出来了，使用联合索引排序是在查询之后，所以对key_len来说只使用到了classId字段。
补充：order by使用联合索引也需要严格遵循最左匹配原则，否则还是会出现filesort的情况，并且对于给定联合索引的情况下，不能交错排序规则混用asc/desc否则都会出现filesort的情况。
3. group by分组优化
对于group by来说其实和order by使用其实一样，也要遵循最左匹配原则，对于实在无法使用索引的排序我们可以配置MySQL排序参数sort_buffer_size,增加一次加载的数据减少次数，对于order by和group by是很耗费服务器资源的，我们可以将其放到业务代码层面实现。
4. 分页优化
分页是工作中最常见的功能了，但是如果数据量一旦大起来，按照传统的limit分页会很卡，SQL执行很慢，如下SQL
传统：select * from student limit 4000000,10;可以看到跨过前400w数据只取10条数据都要1s+
优化：select s.* from student s ,(select id from student limit 4000000,10) s2 where s2.id =s.id
如果是自增id可以使用：select * from student where id > 4000000 limit 10;
