[000-002-01].排序优化

5.排序优化

5.1.问题引入

问题：在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？
回答：
在 MySQL 中，支持两种排序方式，分别是 FileSort 和 Index 排序：
① 在 lndex 排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
② FileSort 排序则一般在内存中进行排序，占用 CPU 较多。如果待排结果较大，会产生临时文件 I/O 到磁盘进行排序的情况，效率较低。

5.2.优化建议

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

5.3.测试

（1）删除 student 和 class 表中的非主键索引。

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

（2）无索引可用。

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid; 

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

（3）在 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; 

由上图可知，索引 idx_age_classid_name 失效，其原因在于优化器估计了使用与不适用该索引的成本，如果使用该索引，由于查询的是所有字段（上述 SQL 语句中的 * ），所以在使用二级索引 idx_age_classid_name 之后还要进行回表操作，当数据量比较大时，其成本可能要比不使用索引而直接查询的成本还要大！所以上述 SQL 未使用索引 idx_age_classid_name 。

② 查询部分字段，索引生效

EXPLAIN  SELECT SQL_NO_CACHE age, classid FROM student ORDER BY age, classid; 

由上图可知，索引 idx_age_classid_name 被使用了，其原因在于要查询的字段不是全部的字段，而只是部分索引上的字段，此时不需要回表就能查到，其成本较小，此时索引 idx_age_classid_name 也被称为覆盖索引。

③ 查询所有字段，但使用 limit，索引生效

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

由于 limit 限制了要查询的数量，所以此时的回表操作的成本就低了许多，所以使用上了索引 idx_age_classid_name 。

（4）创建索引 idx_age_classid_stuno。

CREATE  INDEX idx_age_classid_stuno ON student (age,classid,stuno); 

SHOW INDEX FROM student;

判断：下面的语句会使用到索引吗？

# 没有使用到索引
EXPLAIN  SELECT * FROM student ORDER BY classid LIMIT 10;

# 没有使用到索引
EXPLAIN  SELECT * FROM student ORDER BY classid, NAME LIMIT 10;

# 使用了索引 idx_age_classid_stuno
EXPLAIN  SELECT * FROM student ORDER BY age, classid, stuno LIMIT 10;

# 使用了索引 idx_age_classid_name
EXPLAIN  SELECT * FROM student ORDER BY age, classid LIMIT 10;

# 使用了索引 idx_age_classid_name
EXPLAIN  SELECT * FROM student ORDER BY age LIMIT 10;

（5）order by 时规则不一致，索引失效 （顺序错，不索引；方向反，不索引）

# age 字段的排序方式 (DESC) 与索引中的相反
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;

# classid 字段的排序方式 (DESC) 与索引中的相反
EXPLAIN  SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10;

# age 与 classid 的排序方式一升一降
EXPLAIN  SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10; 

# age 与 classid 的排序方式同为降序时，此时 MySQL 会选择从后往前遍历，即 "Backward index scan"
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;

（6）无过滤，不索引

EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid;

1

根据上图可知，索引 idx_age_classid_name 被使用了，但需要注意的是，key_len = 5，其原因在于经过 where age = 45 这个条件过滤之后，剩下的数据量可能比较少（上图中的 rows = 19500，说明过滤之后只剩 19500 条数据，这对于 CPU 来说是比较少的），所以此时不需要再使用 idx_age_classid_name 中的索引列，而是直接回表并将得到的数据进行排序。

EXPLAIN  SELECT * FROM student WHERE  age=45 ORDER BY classid, NAME; 

结果分析与上面的类似，这里就不再赘述。

EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age;

1

EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age LIMIT 10;

1

（7）小结

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