作者：溫安適
來源：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
用到了filesort，也就是需要排序，
調整索引
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
沒有用到filesort，因為復合索引，欄位后是有序的
特殊情況 Order by+ Limit
Limit可能用到優先佇列排序演算法
例子：
- 開啟優化追蹤
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=off;
SET optimizer_trace_offset=-30, optimizer_trace_limit=30;
- 查看欄位索引
SHOW INDEX FROM oc_order_online WHERE COLUMN_NAME='order_name';
結果顯示沒有索引
- 執行order by+limit 查詢陳述句
select * from `oc_order_online` order by `order_name` limit 20
- 查詢優化追蹤資訊
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30
對應結果如下：
查詢將紅框中資料，粘貼到json.cn查看格式化資料，有如下片段
filesort_priority_queue_optimization 中的chosen：true表示使用了優先佇列排序，
總結
- sort_buffer_size 決定內排，外排
- max_length_for_sort_data 決定 全欄位排序還是，rowid排序
- 覆寫索引是一種優化手段
- Limit可能涉及優先佇列排序
近期熱文推薦：
1.1,000+ 道 Java面試題及答案整理(2021最新版)
2.終于靠開源專案弄到 IntelliJ IDEA 激活碼了，真香！
3.阿里 Mock 工具正式開源，干掉市面上所有 Mock 工具！
4.Spring Cloud 2020.0.0 正式發布，全新顛覆性版本！
5.《Java開發手冊（嵩山版）》最新發布，速速下載！
覺得不錯，別忘了隨手點贊+轉發哦！
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/288423.html
標籤：Java
下一篇：我招了個“水貨”程式員