03|Order by与Group by优化

索引顺序依次是 ： name,age,position

案例1

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND position ='dev' ORDER BY age;

分析: 联合索引中只是用到了name字段做等值查询[通过key_len 74可以看出因为name字段的len=74]，在这个基础上使用了age进行排序【通过Extra： Using index condition可以看出虽然使用了联合索引但是因为中间有跳过 并没有使用到索引覆盖】

案例2

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY position;

分析:
● 根据type key key_len74可以看出使用了联合索引中的name
● 根据Extra:Using filesort可以看出使用了额外的文件排序(因为position排序不符合最左前缀的原则， 中间有断开)

案例3

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY age, position;

查找只用到索引name，age和position用于排序，无Using filesort。

案例4

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY position,age;

分析：
和案例3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。

案例5

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' and age = 18 ORDER BY position, age;

8版本

分析: 和case4比较使用索引多了age len_key78
● 使用了name 和 age的索引信息
● 因为查询结果为空并没有使用extra信息

5.7版本

分析：
与Case 4对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。

案例6

EXPLAIN SELECT * FROM employees WHERE name = 'zhuge' ORDER BY age asc, position desc;

分析:
● 查询使用name索引字段
● 排序因为age是asc 因此不符合索引结构的排序特点， 因此使用了文件排序

案例7

EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','zhuge') ORDER BY age, position;

8版本

分析:
● 范围查询使用name索引
● 排序断开使用了文件排序

5.7版本

案例8

（可以重点关注不同的范围条件可能会选择不同的执行计划，和查询结果集有关系）

EXPLAIN SELECT * FROM employees WHERE name > 'a' ORDER BY name;

EXPLAIN SELECT * FROM employees WHERE name > 'zzz' ORDER BY name;

分析:
● > 'a’会产生大量的结果集，因此范围查询没有使用索引，使用了文件排序
● > 'zzz’会产生小的结果集，因此使用了索引和Using index condition

>a优化:

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'a' ORDER BY name;

优化总结：

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
2、order by满足两种情况会使用Using index。
- order by语句使用索引最左前列。
- 使用where子句与order by子句条件列组合满足索引最左前列。
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果order by的条件不在索引列上，就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。