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

以你之姓@ 2022-10-18 12:44 29阅读 0赞

![0D5F937FE12312312D81F17F.jpg][]

程序员的成长之路

互联网/程序员/技术/资料共享

[关注][Link 1]

阅读本文大概需要 2 分钟。

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

## 引言 ##

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

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

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

注：东西不能乱吃啊

## 两个核心参数 ##

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

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

## 全字段排序 ##

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

## Rowid排序 ##

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

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

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

## 例子 ##

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

![e5d49107cbaea9ff91504b8be49dad4b.png][]

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

调整索引

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

之后

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

![2734e325a8b2e590518ba61176ac9bcc.png][]

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

## 特殊情况 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';

![5e89f4a814f9e8ebd86bd3e7a44420f7.png][]

结果显示没有索引

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

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

1.  [查询优化追踪信息][Link 2]

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30

[对应结果如下：][Link 2]

![9c7fe992b2dbc25d5cf68c52b5c4454e.png][]

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

![a465d21de91f45fbc4183ad920cba8de.png][]

**filesort\_priority\_queue\_optimization** 中的chosen：true表示使用了优先队列排序。

## 总结 ##

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

<END>

**推荐阅读：**

[.apk 成为历史！][.apk]

[如果mysql磁盘满了，会发生什么？还真被我遇到了！][mysql]

最近面试BAT，整理一份面试资料《Java面试BATJ通关手册》，覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。

**获取方式：点个「****在看****」，点击上方小卡片，进入公众号后回复「****面试题****」领取，更多内容陆续奉上。**

朕已阅 ![dd2afc0fb73bb1f21295dd284d46afee.gif][]

[0D5F937FE12312312D81F17F.jpg]: /images/20221005/8e9eb35715da44a3b7ddbc465e905f0b.png
[Link 1]: https://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ%3D%3D&chksm=fa24f0bacd5379ac93d74ffebac826d2401fd494f82e900e5102a8bf968f108e4ec72a4ad548&idx=3&lang=zh_CN&mid=2247487442&scene=21&sn=61e758d4bf9f15a3419fd028146469a3&token=1854383306#wechat_redirect
[e5d49107cbaea9ff91504b8be49dad4b.png]: /images/20221005/da926f3b50254714b126ff8aad2cb4f2.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
[2734e325a8b2e590518ba61176ac9bcc.png]: /images/20221005/714f195302e64f02bab1a980d185e4fe.png
[5e89f4a814f9e8ebd86bd3e7a44420f7.png]: /images/20221005/89265d3295ad420f9234770e3efdbe2d.png
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&chksm=eb50f765dc277e7328851c2d884e557c362c8cbfdfea922fd49b2cbacffc4ab10034c784b82c&idx=2&mid=2247529043&scene=21&sn=ccbb24c037bbe434e86779c4206f0040#wechat_redirect
[9c7fe992b2dbc25d5cf68c52b5c4454e.png]: /images/20221005/028912054e774294b6320bb3ba5ad322.png
[a465d21de91f45fbc4183ad920cba8de.png]: /images/20221005/221455e814b94e48a3d6841af00c41c5.png
[.apk]: http://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ%3D%3D&chksm=fa275b07cd50d2114dd63d76c20a11a9bd94e991fb69af142983f9fe813df43131de42ffbe8a&idx=1&mid=2247509103&scene=21&sn=6a78c2427f4a53b36bad47939628fbb7#wechat_redirect
[mysql]: http://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ%3D%3D&chksm=fa275b07cd50d211ba3323fc097c3944ddccf25c0c927a69dbd1446618bb0e01ba00e379624f&idx=2&mid=2247509103&scene=21&sn=9b3a90ecb7d2b674bb0e1036fefe5fa9#wechat_redirect
[dd2afc0fb73bb1f21295dd284d46afee.gif]: /images/20221005/41658b2123f44f3db21ef37fc7089d46.png