1. 简介
上一篇文章我们介绍了 1024程序员节|【MySQL从入门到精通】【高级篇】（二十七）外连接和内连接如何进行查询优化呢？join的原理了解一波，这篇文章我们接着来学习，本文主要学习的是子查询优化，排序优化，GROUP BY优化以及分页查询优化。
测试数据
1.测试表 student表中有50万条数据
2.测试表 class表中有1万条数据
1.子查询优化
MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT 语句的嵌套查询，即一个SELECT 查询的结果作为另一个SELECT 语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。
子查询是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
这里可以看出使用使用子查询的耗时是 。0.33秒。
接下来看下使用多表关联查询
-- 使用多表关联查询 EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c ON c.monitor IS NOT NULL AND stu1.
可以看出使用多表关联查询的比子查询要耗时，两个表的索引情况如下，在student表中的stuno字段没有添加索引，所以关联的时候会比较耗时。
给student表的stuno字段添加索引
CREATE INDEX idex_stuno ON student(stuno);
下面就给student表中的stuno字段添加索引再看下效果。两者的速度都比较快
如果子查询的表数据比较少，子查询的结果比较少的话可以使用子查询。否则可以考虑使用多表关联，但是关联的字段必须要建立索引，比如关联查询效果比子查询还要差。
举例2：取所有不为班长的同学
SELECT SQL_NO_CACHE * FROM student stu1 WHERE stu1.stuno NOT IN(SELECT monitor FROM class c WHERE monitor IS NOT NULL);
可以看出使用关联查询的效果非常差，查询时间高达2.086s。
SELECT SQL_NO_CACHE stu1.* FROM student stu1 LEFT JOIN class c ON stu1.stuno=c.monitor AND c.monitor IS NULL ;
使用关联查询则好一点。
2. 排序优化
问题：在WHERE条件字段上加索引，但是为什么在ORDER BY字段上还要加索引呢？
回答：在MySQL中，支持两种排序方式，分别为FileSort和Index排序。
Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
FileSort排序则一般在内存中进行排序，占用CPU比较多，如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。
过程一：删除两个表的索引，写一个普通的排序查询
ALTER TABLE student DROP INDEX idex_stuno; ALTER TABLE class DROP INDEX idx_monitor; -- 过程一 EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classId;
可以看出没有索引的情况下，排序使用的是 filesort 排序，也就是在内存中排序。
过程二，创建索引，order by时不limit，索引失效
下面在student表中创建idx_age_classid_name索引。
CREATE INDEX idx_age_classid_name ON student(age,classId,`name`); EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classId;
可以看出使用的排序方式还是 filesort的方式，也就以为着索引失效了。
增加limit过滤条件，使用上索引了。
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classId LIMIT 1000;
过程三：ORDER BY时顺序错误，索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age DESC,classId LIMIT 1000;
过程五：无过滤，不索引
SHOW INDEX FROM student; # 索引有效 EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classId; #索引有效 EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classId LIMIT 10; # 索引无效 EXPLAIN SELECT * FROM student WHERE classId=45 ORDER BY classId LIMIT 10;
优化建议：
SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是WHERE子句中避免全表扫描，在ORDER BY子句避免使用FileSort排序。，当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
尽量使用Index完成ORDER BY排序。如果WHERE和ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
无法使用Index时，需要对FileSort方式进行调优。
小结
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 b=const ORDER 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 /*对于排序来说，多个相等条件也是范围查询*/
排序优化实战
ORDER BY子句，尽量使用index方式，避免使用FileSort方式排序。
执行案例前先清除student上的索引，只留主键：
场景：查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND stuno<101000 ORDER BY NAME;
结论：type是ALL，即最坏的情况，Extra里出现了Using filesort也是最坏的情况，优化是必须的。
优化思路：
方案一：为了去掉filesort我们可以把索引建成如下这样：
CREATE INDEX idx_age_name ON student(age,`name`); EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND stuno<101000 ORDER BY NAM
这样就优化掉了using filesort。
3. GROUP BY优化
group by 使用索引的原则几乎跟order by一致，group by即使没有过滤条件用到索引，也可以直接使用索引。
group by先排序在分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大max_length_for_sort_data 和sort_buffer_size 参数的设置
where效率高于having，能写在where限定的条件就不要写在having中了
减少使用order by，和业务沟通能不排序就不排序，或者将排序放在程序端去做，order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源极其宝贵。
包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保存在1000行以内，否则SQL会很慢。
4. 优化分页查询
一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是limit 200000,10，此时需要MySQL排序前200010记录，仅仅返回200000~200010 的记录，其他记录丢弃，查询排序的代价非常大。
EXPLAIN SELECT * FROM student LIMIT 200000,10;
这里全表扫描，共计扫描了499086条数据。
优化思路一：
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 200000,10) t1 WHERE t.id=t1.id;
优化思路二：
该方案适用于自增的表，可以将limit查询转换成某个位置的查询。
EXPLAIN SELECT * FROM student WHERE id>200000 LIMIT 10;
