【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 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。

结论：

总体而言单路排序好过双路两次读取数据的操作，但是单路排序也有弊端mysql排序，那就是一次性排序大量的数据导致超出了 sort_buffer，这将引起更多的磁盘 IO反而得不尝失。

优化策略 ordre by 时使用 select * 是一大忌；它容易导致使用内存超过 sort_buffer_size和 max_length_for_sort_data所设置的最大限制；增大 sort_buffer_size 参数设置；增大 max_length_for_sort_data 参数设置 总结

order by 能使用索引最左前缀情况

如果 where 使用索引的最左前缀定义为常量，则 order by 能使用索引：

（编辑：上海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!