1.子查询的优化
MySQL 从 4.1 版本开始支持子查询，使用子查询可以进行 SELECT 语句的嵌套查询，即一个 SELECT 查询的结果作为另一个 SELECT 语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的操作 。
子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。
原因:
执行子查询时，MySQL 需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表 。这样会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。
子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会受到一定的影响。
对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
在 MySQL 中，可以使用连接（JOIN）查询来替代子查询。 连接查询 不需要建立临时表，其 速度比子查询要快，如果查询中使用索引的话，性能就会更好。
举例1：查询学生表中是班长的学生信息
使用子查询
#创建班级表中班长的索引 CREATE INDEX idx_monitor ON class(monitor); #查询班长的信息 EXPLAIN SELECT * FROM student stu1 WHERE stu1.`stuno` IN ( SELECT monitor FROM class c WHERE monitor IS NOT NULL );
本地测试执行实践0.036s。
推荐：使用多表查询，本地测试时间仅仅为0.016s。
EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c ON stu1.`stuno` = c.`monitor` WHERE c.`monitor` IS NOT NULL;
举例2：取所有不为班长的同学
不推荐
#查询不为班长的学生信息 EXPLAIN SELECT SQL_NO_CACHE a.* FROM student a WHERE a.stuno NOT IN ( SELECT monitor FROM class b WHERE monitor IS NOT NULL);
推荐
EXPLAIN SELECT SQL_NO_CACHE a.* FROM student a LEFT OUTER JOIN class b ON a.stuno =b.monitor WHERE b.monitor IS NULL;
🌹结论：尽量不要使用 NOT IN 或者 NOT EXISTS，用 LEFT JOIN xxx ON xx WHERE xx IS NULL 替代
2 排序优化
2.1 排序优化
问题：在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？
在 MySQL 中，支持两种排序方式，分别是 FileSort 和 Index 排序。 Index 排序中，索引可以保证数据的有序性，就不需要再进行排序，效率更更高。
FileSort 排序则一般在 内存中 进行排序，占用 CPU 较多。如果待排序的结果较大，会产生临时文件 I/O 到磁盘进行排序的情况，效率低。
优化建议:
SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描，在 ORDER BY 子句 避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
无法使用 Index 时，需要对 FileSort 方式进行调优。
2.2 测试
先准备下，调用存储过程删除student,和class表上的索引。
CALL proc_drop_index('mysql', 'student') CALL proc_drop_index('mysql', 'class') 1 2
现在进行排序查询。
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 1
很明显是using filesort.
创建索引。但是不加limit限制，索引失效。
CREATE INDEX idx_age_classid_name ON student (age,classid,NAME); #不限制,索引失效 EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;
这是优化器通过计算发现，这里需要回表的数据量特别大，使用索引的性能代价反而比不上不用索引的。
再来，不需要回表。
# 会使用索引 （覆盖索引） EXPLAIN SELECT SQL_NO_CACHE age,classid,name,id FROM student ORDER BY age,classid;
再来。限制排序返回的结果数量，可以使用索引。
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10; 1 再来，order by 时顺序错误，索引失效 #创建索引age,classid,stuno CREATE INDEX idx_age_classid_stuno ON student (age,classid,stuno); #以下哪些索引失效? # 失效 EXPLAIN SELECT * FROM student ORDER BY classid LIMIT 10; # 失效 EXPLAIN SELECT * FROM student ORDER BY classid,NAME LIMIT 10; # 可以 EXPLAIN SELECT * FROM student ORDER BY age,classid,stuno LIMIT 10; # 可以 EXPLAIN SELECT * FROM student ORDER BY age,classid LIMIT 10; # 可以 EXPLAIN SELECT * FROM student ORDER BY age LIMIT 10;
再来。order by 时规则不一致, 索引失效 （顺序错，不索引；方向反，不索引）
# 失效,因为age是降序排序，但是索引是升序排序 EXPLAIN SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10; # 失效 EXPLAIN SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10; # 失效 EXPLAIN SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10; # 可以，这是因为order by和classid在使用时都是降序查找的，统一了反而被优化器优化可以使用索引了 EXPLAIN SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10; 再来。无过滤，不索引。下面执行结果都是和优化器的优化有关，大家可以自己验证思考。
# 可以 EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid; # 可以 EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid,NAME; # 失效 EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age; # 可以 EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age LIMIT 10; CREATE INDEX idx_cid ON student(classid); # 可以 EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age; 💡小结 INDEX a_b_c(a,b,c) order by 能使用索引最左前缀 ORDER BY a ORDER BY a,b
ORDER BY a,b,c
ORDER BY a DESC,b DESC,c DESC
如果 WHERE 使用索引的最左前缀定义为常量，则 order by 能使用索引
WHERE a = const ORDER BY b,c
WHERE a = const AND b = const ORDER BY c
WHERE a = const ORDER BY b,c
WHERE a = const AND b > const ORDER BY b,c
不能使用索引进行排序
ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
WHERE g = const ORDER BY b,c /丢失a索引/
WHERE a = const ORDER BY c /丢失b索引/
WHERE a = const ORDER BY a,d /d不是索引的一部分/
WHERE a in (…) ORDER BY b,c /对于排序来说，多个相等条件也是范围查询/
2.3 案例实战
下面我们通过一个案例来实战filesort和index两种排序。对ORDER BY子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序。
场景:查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序
先删除以前的索引。再测试如下sql。
CALL proc_drop_index(`mysql`,`student`) EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;
此时显然使用的是filesort进行排序。
