索引优化：子查询、排序

• 子查询

子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高
① 执行子查询时，MySQL需要为内层查询语句的查询结果 建立一个临时表 ，然后外层查询语句从临时表
中查询记录。查询完毕后，再 撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会
受到一定的影响。
③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
 
在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，
如果查询中使用索引的话，性能就会更好
 
尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

• 代码案例

# 创建班级表中班长的索引
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
);
 
# 修改为连接查询
EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c 
ON stu1.`stuno` = c.`monitor`
WHERE c.`monitor` IS NOT NULL;
 
# 查询不为班长的学生信息
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;

• 排序

在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方式进行调优。

• 代码案例1

# 删除student和class表中的非主键索引
CALL proc_drop_index('atguigudb2','student');
CALL proc_drop_index('atguigudb2','class');
 
# 查看索引
SHOW INDEX FROM student;
SHOW INDEX FROM class;
 
# 案例一：没有索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 
 
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10; 
 
# 案例二：order by时不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; 
 
# 增加limit过滤条件，索引未失效
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;  
 
# 案例三：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时规则不一致, 索引失效 （顺序错，不索引；方向反，不索引）
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;#用上索引，倒着看索引
 
# 案例五：无过滤，不索引
EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid;  #where用上索引
 
EXPLAIN  SELECT * FROM student WHERE  age=45 ORDER BY classid,NAME; #where用上索引
 
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);
# 使用到idx_cid该索引
EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age;

• 代码案例2

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

• filesort算法：双路排序和单路排序

双路排序（慢）
1、MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，
按照列表中的值重新从列表中读取对应的数据输出
2、从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
3、取一批数据，要对磁盘进行两次扫描，众所周知，l0是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序
 
# 单路排序（快）
从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。
并且把随机Io变成了顺序Io，但是它会使用更多的空间，因为它把每一行都保存在内存中了

• 单路排序存在的问题

在sort_buffer中，单路比多路要多占用很多空间，因为单路是把所有字段都取出，所以有可能取出的数据的
总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序(创建tmp文件，多路合并)，排完再取sort_buffer容量大小，
再排………从而多次I/o。
单路本来想省一次/O操作，反而导致了大量的I/0操作，反而得不偿失

• 优化策略

1、提高sort_buffer_size
 
不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程（connection)的1M-8M之间调整。
MySQL5.7,InnoDB存储引擎默认值是1048576字节，1MB
 
# 查看大小
SHOW VARIABLES LIKE '%sort_buffer_size%';
 
2、提高max_length for_sort_data
提高这个参数，会增加用改进算法的概率；但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的
磁盘I/o活动和低的处理器使用率。如果需要返回的列的总长度大于max_length_for_sort_data,使用双路算法,否则使用单路算法。
1024-8192字节之间调整
 
# 查看大小
SHOW VARIABLES LIKE '%max_length_for_sort_data%';  # 默认1024字节
 
3、Order by 时select*是一个大忌。最好只Query需要的字段
当Query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT|BLOB类型时，会用改进后的算法–单路排序，否则
用老算法–多路排序。
两种算法的数据都有可能超出sort_buffer_size的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O,但是用单路排序算法
的风险会更大一些,所以要提高sort_buffer_size