④. Order by与Group by优化

优化总结写在最前面

(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限定了

①. 利用最左前缀法则:中间字段不能断,因此查询用到了name索引,从key_len=74也能看出,age索引列用在排序过程中,因为Extra字段里没有using filesort

EXPLAIN select * from employees where name='LiLei' ORDER BY age,position

②.从explain的执行结果来看:key_len=74,查询使用了name索引,由于用了position进行排序,跳过了age,出现了Using filesort。

③. 查找只用到索引name,age和position用于排序,无Using filesort

④. 和Case 3中explain的执行结果一样,但是出现了Using filesort,因为索引的创建顺序name,age,position,但是排序的时候age和position颠倒位置了

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

⑥. 虽然排序的字段列与索引顺序一样,且order by默认升序,这里position desc变成了降序,导致与索引的排序方式不同,从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式

⑦. 对于排序来说,多个相等条件也是范围查询

⑧. 可以用覆盖索引优化