子查询优化与排序优化

子查询优化

子查询介绍

MySQL从4.1版本开始支持子查询，使用子查询可以进行select语句嵌套查询，即一个select查询的结果作为另一个select语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的sql操作。

子查询是MySQL的一项重要的功能，可以帮助我们通过一个SQL语句实现比较复杂的查询。但是，子查询的执行效率并不高，原因如下：

1. 执行子查询时，MySQL需要为内存查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和io资源，产生大量的慢查询。
2. 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
3. 对于返回结果集比较大的子查询，其对查询性能的影响也越大。

在MySQL中，可以用连接（join）查询代替子查询，连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引，则性能更好。

子查询优化技巧get

• 保证被驱动表的join字段已经创建了索引
• 需要join的字段，数据类型保持绝对一致
• LEFT JOIN时，选择小表作为驱动表，大表作为被驱动表，减少外层循环的次数
• INNER JOIN时，MySQL会自动将小结果集的表选为驱动表（前提是选择相信MySQL优化策略）
• 能够直接多表关联的尽量直接关联，不用子查询（减少查询的趟数），或者将子查询sql拆开结合程序多次查询
• 尽量不要使用not in/not exists/not null,用left join xxx where xx is null替代，测试样例代码如下（用的是上一篇文章创建的数据表）

#创建班级表中班长的索引
CREATE INDEX idx_monitor ON class(monitor);

#查询不为班长的学生信息（子查询）
EXPLAIN SELECT SQL_NO_CACHE a.* 
FROM student a 
WHERE  a.stuno  NOT  IN (
            SELECT monitor FROM class b 
            WHERE monitor IS NOT NULL) 

#查询不为班长的学生信息（join查询）
EXPLAIN SELECT SQL_NO_CACHE a.*
FROM  student a LEFT OUTER JOIN class b 
ON a.stuno =b.monitor
WHERE b.monitor IS NULL;

排序优化

问题：在where条件字段上加索引，但是为什么在order by字段上还有加索引呢？

回答：在MySQL中，支持两种排序方式，分别是 FileSort 和 Index 排序。

• index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
• filesort排序则一般在内存中进行排序，占用CPU较多，如果待排序的结果集较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。

优化建议：

1. SQL中，可以在where子句和order by子句中使用索引，目的是在where子句中避免全表扫描，在order by子句中避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢，但总的来说，我们还是要避免，以提高查询效率。
2. 尽量使用 index 完成 order by 排序。如果 where 和 order by 后面是相同的列就是用单索引列；如果不同就使用联合索引。
3. 无法使用index时，需要对Filesort进行调优。

测试

测试前准备：清除所有干扰索引（沿用本专栏的数据表）

#删除student和class表中的非主键索引
CALL proc_drop_index('atguigudb2','student');
CALL proc_drop_index('atguigudb2','class');

SHOW INDEX FROM student;
SHOW INDEX FROM class;

过程一：order by时不limit，索引失效

#创建索引  
CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);

#不限制,索引失效(由于查询的数据未进行限制，且需要进行回表操作，为了节省开销，优化器会先将所有数据加载到内存中进行Filesort，然后再进行order by，导致索引失效)
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 

#这里查询的字段（对比上述查询所有字段而言）都存在于二级索引的叶子节点中，无需回表，因此使用上索引了（即直接order by）
EXPLAIN  SELECT SQL_NO_CACHE age,classid,NAME,id FROM student ORDER BY age,classid; 

#增加limit过滤条件，使用上索引了（由于数据量有限，因此先使用索引进行order by排序再进行回表操作更节省开销）。
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;  

技巧get：在已知查询数据量的情况下加上limit条件可以提高查询效率

过程二：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;

解析：很显然根据最左前缀原则前两条sql语句会出现索引失效，目前存在两个联合索引（分别是：idx_age_classid_name、idx_age_classid_stuno ），第三条语句很显然会选择匹配度高的联合索引idx_age_classid_stuno ，而后两条语句则选择了key_len为73的联合索引idx_age_classid_name来提升查询效率（一定条件下索引长度长的索引查询效率更高，可以理解为唯一性越高）

过程三：order by时规则不一致, 索引失效 （顺序错，失索引；方向反，失索引）

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; 

EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;

执行结果：只有最后一条sql语句用上索引了 ，可见联合索引上的字段排序方式统一，也会使用上索引。要么都降序；要么都升序。

过程四：无过滤，失索引

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;

理解：根上还是优化器有一套自己计算开销的方式，具体问题具体优化，我们只能从一些Explain参数做一个大概的规律总结，那么这里根据执行结果我的理解是当选择在where或者order by上使用索引时，如果数据量大，就优先选择在where过滤条件上使用索引（参照前两条sql语句执行结果），当limit限制了数据量的情况则还是会优先选择在order by上使用索引（参照最后一条sql语句执行结果）

排序优化小结

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,HY 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 /*对于排序来说，多个相等条件也是范围查询*/

测试filesort和index排序

• 测试前的准备

CALL proc_drop_index('atguigudb2','student');

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

这里清除了之前建立的所有索引，没有索引的条件下执行select查询显然选择了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 NAME ;

解析：这里建立age、name字段的索引，就去掉了FileSort排序方式，key_len = 5 说明只用上了age这个字段的索引就进行回表了，执行速度还没有FileSort的快，数据量大的情况下越明显，原因是因为所有的排序都是金国过滤后执行，但是age字段过滤数据量并不是很大因此性能提升比较有限，相对于stuno<101000这个条件而言的话，索引放在stuno字段上性能开销更小。

方案二

CREATE INDEX idx_age_stuno_name ON student(age,stuno,NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

DROP INDEX idx_age_stuno_name ON student;

CREATE INDEX idx_age_stuno ON student(age,stuno);

解析：这里创建的索引idx_age_stuno_name 使得性能提升更加地明显，而新建索引idx_age_stuno 时，速度则更快，说明冗余的索引段也会影响查询性能。

结论：

1. 两个索引同时存在，MySQL自动选择最优的方案。对于这个例子，MySQL选择idx_age_stuno_name ，但是随着数据量的变化，选择的索引也会随之变化。
2. 当【范围条件】和【group by或者order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

FileSort算法：双路排序和单路排序

• 当排序的字段若不在索引列上，则filesort会有两种算法：双路排序算法和单路排序算法
• 双路排序：即先对order by或者group by的字段排好序后，再根据该字段的顺序读取对应的query字段数据；若从磁盘取排序字段，会先在buffer进行排序，再从磁盘取其他字段，即会对磁盘进行两次扫描，极其耗时
• 单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机io变成了顺序io，但是它会使用更多的空间，因为它把每一行需要的列都保存在内存中
• 单路排序存在的问题：在sort_buffer中，单路比多路要占用更多的空间，因为单路是把所有字段取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排……从而多次I/O，单路本来想省一次I/O操作，反而导致大量的I/O操作，反而得不偿失。

order by优化策略

1. 尝试提高Sort_buffer_size，两种算法均可提升效率（InnoDB默认大小是1M，该参数针对每个进程connection的1M-8M之间的调整）
2. 尝试提高max_length_for_sort_data，通常返回的列的总长度大于max_length_for_sort_data，使用双路算法，否则使用单路算法，也是1M-8M之间调整
3. order by 时select * 是一个大忌，最好只query需要的字段
4. 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct这些语句较为耗费CPU，而数据库CPU资源极其宝贵
5. 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持再1000行以内，否则SQL会很慢

附：

group by优化：

• group by 使用索引的原则几乎跟order by 一致，group by即使没有过滤条件用到索引，也可以直接使用索引
• group by 先排序再分组，遵照索引建的最佳左前缀法则
• 当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数设置
• where效率高于having，能写where限定条件尽量不要写having中

分页查询优化：

#场景描述：排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃（代价极大）
Explain select * from student limit 2000000,10;

• 优化思路一：在索引上完成排序分页操作，最后根据主键关联回表查询所需要的其他列内容 Explain select * from student t,(select id from student order by id limit 20000000,10) a where t.id = a.id;
• 优化思路二：可以把limit查询准换成某个位置的查询 Explain select * from student where id>2000000 limit 10;
• 宗旨就是尽量往聚簇索引上靠