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

程序員的成長之路

互聯(lián)網(wǎng)/程序員/技術(shù)/資料共享 

閱讀本文大概需要 2 分鐘。

作者：溫安適
來源：https://my.oschina.net/floor/blog/4961581

引言

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

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

“正排倒排，當(dāng)然不是全部，你最少要知道，2個(gè)參數(shù)，1個(gè)優(yōu)化，一種特殊情況”

注：東西不能亂吃啊

兩個(gè)核心參數(shù)

sort_buffer_size 決定內(nèi)排，外排。內(nèi)排就是走內(nèi)存，外排就是采用歸并排序走磁盤。

max_length_for_sort_data 決定 全字段排序還是，rowid排序。

全字段排序

字段都放到 sort_buffer 中，排序后就會(huì)直接從內(nèi)存里面返回查詢結(jié)果了

Rowid排序

內(nèi)存放rowid與排序字段，排序后，再從庫中找數(shù)據(jù)，拼接返回。

優(yōu)化手段覆蓋索引

覆蓋索引是指，索引上的信息足夠滿足查詢請(qǐng)求，不需要再回到主鍵索引上去取數(shù)據(jù)。

例子

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

用到了filesort，也就是需要排序?！?a target="_blank" textvalue="MySQL 開發(fā)的 36 條軍規(guī)" data-itemshowtype="0" tab="innerlink" data-linktype="2" hasload="1" style="-webkit-tap-highlight-color: rgba(0, 0, 0, 0);cursor: pointer;max-width: 100%;box-sizing: border-box !important;overflow-wrap: break-word !important;">MySQL 開發(fā)的 36 條軍規(guī)》推薦看下。

調(diào)整索引

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，因?yàn)閺?fù)合索引，字段后是有序的。

特殊情況 Order by+ Limit

Limit可能用到優(yōu)先隊(duì)列排序算法。

例子：

1. 開啟優(yōu)化追蹤

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';

結(jié)果顯示沒有索引

3. 執(zhí)行order by+limit 查詢語句

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

4. 查詢優(yōu)化追蹤信息

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30

對(duì)應(yīng)結(jié)果如下：

查詢將紅框中數(shù)據(jù)，粘貼到j(luò)son.cn查看格式化數(shù)據(jù)，有如下片段

filesort_priority_queue_optimization 中的chosen：true表示使用了優(yōu)先隊(duì)列排序。

總結(jié)

1. sort_buffer_size 決定內(nèi)排，外排
2. max_length_for_sort_data 決定 全字段排序還是，rowid排序
3. 覆蓋索引是一種優(yōu)化手段
4. Limit可能涉及優(yōu)先隊(duì)列排序

<END>

推薦閱讀：

.apk 成為歷史！

如果mysql磁盤滿了，會(huì)發(fā)生什么？還真被我遇到了！

最近面試BAT，整理一份面試資料《Java面試BATJ通關(guān)手冊(cè)》，覆蓋了Java核心技術(shù)、JVM、Java并發(fā)、SSM、微服務(wù)、數(shù)據(jù)庫、數(shù)據(jù)結(jié)構(gòu)等等。

獲取方式：點(diǎn)個(gè)「在看」，點(diǎn)擊上方小卡片，進(jìn)入公眾號(hào)后回復(fù)「面試題」領(lǐng)取，更多內(nèi)容陸續(xù)奉上。

朕已閱