正排倒排，并不是 MySQL 的排序的全部！

「爱情、让人受尽委屈。」 2022-10-09 04:55 5阅读 0赞

**点击关注公众号，Java干货****及时送达**![7c034bddafb6f299cdf826b5a3f1d512.png][]

*作者：温安适  
来源：https://my.oschina.net/floor/blog/4961581*

## 引言 ##

[春节前一个悠闲的上午，小航送了我，一袋坚果，他看我吃的正香，慢慢问道：”温哥，mysql的排序，有什么要注意的吗，不就是正排倒排吗？”][mysql]

[我一听他问我的问题，顿感坚果不香了，但是为了技术（mainzi），我装作大师的说道：][mysql]

[“正排倒排，当然不是全部，你最少要知道，2个参数，1个优化，一种特殊情况”][mysql]

[注：东西不能乱吃啊][mysql]

## 两个核心参数 ##

**sort\_buffer\_size** 决定内排，外排。内排就是走内存，外排就是采用归并排序走磁盘。

**max\_length\_for\_sort\_data** 决定 全字段排序还是，rowid排序。

## 全字段排序 ##

字段都放到 sort\_buffer 中，排序后就会直接从内存里面返回查询结果了

## Rowid排序 ##

内存放rowid与排序字段，排序后，再从库中找数据，拼接返回。

## 优化手段覆盖索引 ##

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。另外，MySQL 系列面试题和答案全部整理好了，微信搜索Java技术栈，在后台发送：面试，可以在线阅读。

## 例子 ##

explain
    SELECT order_id,pay_date FROM orders_detail WHERE order_id='1001' ORDER BY pay_date asc

![b4e859449649c9013dc55ccf850f051b.png][]

用到了filesort，也就是需要排序。《[*MySQL 开发的 36 条军规*][MySQL _ 36]》推荐看下。

[调整索引][mysql]

ALTER TABLE `orders_detail` DROP INDEX `order_id`,ADD INDEX `order_id` (`order_id`, `pay_date`);

[之后][mysql]

explain
    SELECT order_id,pay_date FROM orders_detail WHERE order_id='1001' ORDER BY pay_date asc

[![6264e0e05d13527e4145d51b6e3d5bb7.png][]][mysql]

[没有用到filesort，因为复合索引，字段后是有序的][mysql]。

## 特殊情况 Order by+ Limit ##

Limit可能用到优先队列排序算法。

例子：

1.  开启优化追踪

SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=off;
    SET optimizer_trace_offset=-30, optimizer_trace_limit=30;

1.  查看字段索引

SHOW INDEX FROM oc_order_online WHERE COLUMN_NAME='order_name';

![70a14b23c468de48a5ee7d27eedb05f0.png][]

结果显示没有索引

1.  [执行order by+limit 查询语句][order by_limit]

select * from `oc_order_online`  order by  `order_name`  limit 20

1.  [查询优化追踪信息][order by_limit]

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30

[对应结果如下：][order by_limit]

![de6e55a098583d67f7e4971ee3e0faf6.png][]

查询将红框中数据，粘贴到json.cn查看格式化数据，有如下片段

![9af7d588ad5622d9e757e463cea1801c.png][]**filesort\_priority\_queue\_optimization** 中的chosen：true表示使用了优先队列排序。另外，关注公众号Java技术栈，在后台回复：面试，可以获取我整理的 Java 系列面试题和答案，非常齐全。

## 总结 ##

1.  sort\_buffer\_size 决定内排，外排
2.  max\_length\_for\_sort\_data 决定 全字段排序还是，rowid排序
3.  覆盖索引是一种优化手段
4.  Limit可能涉及优先队列排序

![9b5799b0da362b2485dca321123f3378.gif][]

![cbf2fadc73ecf11920894610cd4f2431.png][]

[![43d04712d979e8d2744356e963a3d818.png][]][43d04712d979e8d2744356e963a3d818.png 1]

[![9b9d6e9537b06655618fa704daad641e.png][]][9b9d6e9537b06655618fa704daad641e.png 1]

[![03ff7927a8cf210e2562bee38b782d52.png][]][03ff7927a8cf210e2562bee38b782d52.png 1]

[![455ac9ff1024ce9239ce7a5bde97c529.png][]][455ac9ff1024ce9239ce7a5bde97c529.png 1]

[![bb6733854df0eed8d75001f11239cd56.png][]][bb6733854df0eed8d75001f11239cd56.png 1]

**关注Java技术栈看更多干货**

![a3c4c842d196be60329a7187121e2421.png][]

![0bb067720dc1a39a39de7f17390987a4.gif][]

获取 Spring Boot 实战笔记！

[7c034bddafb6f299cdf826b5a3f1d512.png]: /images/20221005/1d8b851718444d01afe8f62b71aba28b.png
[mysql]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&chksm=eb50f82edc2771386229a5bb81d865eee375f8ecbec665494417beb8c933260114d67b34c3a0&idx=2&mid=2247531800&scene=21&sn=fbf496a2626d9d550c4ae95bb4f60c11#wechat_redirect
[b4e859449649c9013dc55ccf850f051b.png]: /images/20221005/fec7f70b03984261a7cc7c11e4bbc0b0.png
[MySQL _ 36]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&chksm=eb538febdc2406fd70748604c500eeaea584306c137cb2020ab5a72187c9359926015e00a46d&idx=2&mid=2247486173&scene=21&sn=0603756b36279b5f5f036151af85f9fd#wechat_redirect
[6264e0e05d13527e4145d51b6e3d5bb7.png]: /images/20221005/2a731a434cec4aa8905e602d9168487b.png
[70a14b23c468de48a5ee7d27eedb05f0.png]: /images/20221005/b564a81a71604f2d9f0cbe4302abda68.png
[order by_limit]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&chksm=eb50f765dc277e7328851c2d884e557c362c8cbfdfea922fd49b2cbacffc4ab10034c784b82c&idx=2&mid=2247529043&scene=21&sn=ccbb24c037bbe434e86779c4206f0040#wechat_redirect
[de6e55a098583d67f7e4971ee3e0faf6.png]: /images/20221005/e3ece127db194b498b3465b01e5dd395.png
[9af7d588ad5622d9e757e463cea1801c.png]: /images/20221005/7e926870513d48838c6fb6ecbc8a6e0e.png
[9b5799b0da362b2485dca321123f3378.gif]: /images/20221005/62a722a95bde4cf596952137fe666460.png
[cbf2fadc73ecf11920894610cd4f2431.png]: /images/20221005/a21a142ffe634d4f9714b869b7a4ef91.png
[43d04712d979e8d2744356e963a3d818.png]: /images/20221005/07048be006b84395a5f7491faa1f701a.png
[43d04712d979e8d2744356e963a3d818.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&idx=1&mid=2247535009&scene=21&sn=5a0ac485739f9ee7be02bbe938ff0eb3#wechat_redirect
[9b9d6e9537b06655618fa704daad641e.png]: /images/20221005/3c5c61ed0bd74fa8a1c250d36ef00588.png
[9b9d6e9537b06655618fa704daad641e.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&idx=1&mid=2247534855&scene=21&sn=fdaf7ea70fca4597d3cc4e18f5ada334#wechat_redirect
[03ff7927a8cf210e2562bee38b782d52.png]: /images/20221005/06631d4d730c41f0802f5a0f7e9e5a08.png
[03ff7927a8cf210e2562bee38b782d52.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&idx=1&mid=2247530418&scene=21&sn=e6a0138c7c2f9a9ff6a8b4c878386211#wechat_redirect
[455ac9ff1024ce9239ce7a5bde97c529.png]: /images/20221005/adec72b6646c42f8804f619d496ab2f4.png
[455ac9ff1024ce9239ce7a5bde97c529.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&idx=1&mid=2247524153&scene=21&sn=6c53d1287b640350cf7eeea7f04773c1#wechat_redirect
[bb6733854df0eed8d75001f11239cd56.png]: /images/20221005/65d3b15650ce4aecbdc6c6e555aca76d.png
[bb6733854df0eed8d75001f11239cd56.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&idx=1&mid=2247530007&scene=21&sn=39f404a41d5f7f814cbd796b84de17d1#wechat_redirect
[a3c4c842d196be60329a7187121e2421.png]: /images/20221005/d74fea8c04fd44cb9113b8e5c53708e6.png
[0bb067720dc1a39a39de7f17390987a4.gif]: /images/20221005/45894953ae174ddca05a0b4726831818.png