[[407094]]

引言

春節前一個悠閑的上午，小航送了我，一袋堅果，他看我吃的正香，慢慢問道：”溫哥，mysql的排序，有什么要注意的嗎，不就是正排倒排嗎？”

我一聽他問我的問題，頓感堅果不香了，但是為了技術（mainzi），我裝作大師的說道：

“正排倒排，當然不是全部，你最少要知道，2個參數，1個優化，一種特殊情況”

注：東西不能亂吃啊

兩個核心參數

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 

用到了filesort，也就是需要排序。《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 

沒有用到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; 

    2.  查看字段索引 

SHOW INDEX FROM oc_order_online WHERE COLUMN_NAME='order_name'; 

結果顯示沒有索引

    3.  執行order by+limit 查詢語句 

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

    4.  查詢優化追蹤信息 

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30 

對應結果如下：

查詢將紅框中數據，粘貼到json.cn查看格式化數據，有如下片段

filesort_priority_queue_optimization 中的chosen：true表示使用了優先隊列排序。另外，關注公眾號Java技術棧，在后臺回復：面試，可以獲取我整理的 Java 系列面試題和答案，非常齊全。

總結

1.  sort_buffer_size 決定內排，外排
2.  max_length_for_sort_data 決定 全字段排序還是，rowid排序
3.  覆蓋索引是一種優化手段
4.  Limit可能涉及優先隊列排序