【MySQL】order by 排序优化

常见排序情况示例

create index idx_ageName on tblA(age, Birth);

# using index 使用到了索引
select * from tblA where age > 20 order by age;
# using index 使用到了索引
select * from tblA where age > 20 order by age,birth;
# using firesort 出现了文件内排序
select * from tblA where age > 20 order by birth;
# using firesort 出现了文件内排序
select * from tblA where age > 20 order by birth,age;
# using firesort 出现了文件内排序
select * from tblA order by birth;
# using firesort 出现了文件内排序
select * from tblA where birth > '2022-03-31 00:00:00' order by birth;
# using index 使用到了索引
select * from tblA where birth > '2022-03-31 00:00:00' order by age;
# using firesort 出现了文件内排序
select * from tblA order by age asc, birth desc;

结论：
1.order by 语句使用索引最左前列；
2.使用where字句与order by字句条件列满足索引最左前列；

排序方式

排序方式分为两大类：文件排序（using firesort）和扫描有序索引排序（using index）；其中文件排序又分如下两种；
MySQL 4.1 之前使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，其中，第一次读取指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重写从列表中读取对应的数据进行输出；
MySQL 4.1之后，对排序进行优化，使用了单路排序：从磁盘中读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。
结论：
总体而言单路排序好过双路两次读取数据的操作，但是单路排序也有弊端，那就是一次性排序大量的数据导致超出了 sort_buffer，这将引起更多的磁盘 IO反而得不尝失。

优化策略

1. ordre by 时使用 select * 是一大忌；它容易导致使用内存超过 sort_buffer_size和 max_length_for_sort_data所设置的最大限制；
2. 增大 sort_buffer_size 参数设置；
3. 增大 max_length_for_sort_data 参数设置

总结

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