【三十、MySql】进阶篇--SQL优化--order by优化

Order by优化
1，对于MySQL的排序有两种方式：
①Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫Filesort排序；
②Using index : 通过有索引顺序扫描直接返回有序数据，不需要额外排序，效率极高。
index 的性能高于filesort，所以在优化order by语句的时候，尽量优化为Using index

2，举例
①未走索引查询
此时表t_input中没有为age、phone 建立索引
查询如下语句：explain select id,age,phone from t_input order by age,phone; 并查看执行计划，可以看到排序方式为Using filesort

②走索引查询
为表t_input字段age、phone建立一个联合索引，再次执行explain select id,age,phone from t_input order by age,phone;，发现使用了索引，并且查询方式为Using index

③未遵循最左前缀法则排序
执行如下sql：explain select id,age,phone from t_input order by phone,age ; ，查看执行计划：索引字段的建立顺序是age、phone，在查询的时候为Using index，但是在排序的时候，phone在age前面，违背了最左前缀法则，所以排序方式为Using filesort

④在未指定索引字段的升/倒序排列情况1
执行如下sql，根据age、phone降序排序，查看执行计划：explain select id,age,phone from t_input order by age desc ,phone desc ;
此时虽然没有进行额外排序Using filesort，性能较高，但是出现Backward index scan反向扫描索引。这是因为刚刚创建的联合索引，其B＋树的结构是先按照age升序排，叶子节点是升序的，如果age相同，再按照phone升序排。而此时的语法是desc倒序排，则要反向扫描索引。

⑤在未指定索引字段的升/倒序排列情况2
执行如下sql：explain select id,age,phone from t_input order by age asc ,phone desc;查看执行计划，此时发现排序方式为Using sort

是因为在建立索引的时候，未指定顺序，则默认是升序走的，所以在上述sql排序phone的时候，需要进行额外的排序Using filesort

假如说非要执行这个sql的时候，需要优化这个执行操作，可以在建立索引的时候，为字段phone指定排序的顺序为倒序：create index idx_input_age_pho_ad on t_input(age asc,phone desc); ，
【备注：不知道是不是MySQL版本的问题，我创建不了倒序排序的索引】

再次查询，排序方式为Using index

联合索引的叶子节点

⑥未覆盖索引
执行如下sql:explain select * from t_input order by age,phone;查看执行计划，此时发现排序方式为Using filesort

3，小结：
①根据排序字段建立适合的索引，多字段排序时，需遵循最左前缀法则；
②尽量使用覆盖索引；
③多字段排序，一个升序，一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
④如果不可避免地出现filesort，大数据量排序时，可以适当增加排序缓冲区大小sort_buffer（默认为256k，如下图）

如果在排序的时候，256k占满了，就会涉及到磁盘文件，会在磁盘文件中进行排序，性能降低。