看一遍就了解：order by詳解

前言

日常開發中，我們經常會使用到order by，親愛的小夥伴，你是否知道order by 的工作原理呢？order by的優化思路是怎樣的呢？使用order by有哪些注意的問題呢？本文将跟大家一起來學習，攻克order by~

微信公衆号：撿田螺的小男孩

一個使用order by 的簡單例子

假設用一張員工表，表結構如下：

CREATE TABLE `staff` (
`id` BIGINT ( 11 ) AUTO_INCREMENT COMMENT '主鍵id',
`id_card` VARCHAR ( 20 ) NOT NULL COMMENT '身份證号碼',
`name` VARCHAR ( 64 ) NOT NULL COMMENT '姓名',
`age` INT ( 4 ) NOT NULL COMMENT '年齡',
`city` VARCHAR ( 64 ) NOT NULL COMMENT '城市',
PRIMARY KEY ( `id`),
INDEX idx_city ( `city` )
) ENGINE = INNODB COMMENT '員工表';

表資料如下：

我們現在有這麼一個需求：查詢前10個，來自深圳員工的姓名、年齡、城市，并且按照年齡小到大排序。對應的 SQL 語句就可以這麼寫：

select name,age,city from staff where city = '深圳' order by age limit 10;

這條語句的邏輯很清楚，但是它的底層執行流程是怎樣的呢？

order by 工作原理

explain 執行計劃

我們先用Explain關鍵字檢視一下執行計劃

執行計劃的key這個字段，表示使用到索引idx_city
Extra 這個字段的Using index condition 表示索引條件
Extra 這個字段的Using filesort表示用到排序

我們可以發現，這條SQL使用到了索引，并且也用到排序。那麼它是怎麼排序的呢？

全字段排序

MySQL 會給每個查詢線程配置設定一塊小記憶體，用于排序的，稱為 sort_buffer。什麼時候把字段放進去排序呢，其實是通過

idx_city

索引找到對應的資料，才把資料放進去啦。

我們回顧下索引是怎麼找到比對的資料的，現在先把索引樹畫出來吧，idx_city索引樹如下：

idx_city索引樹，葉子節點存儲的是主鍵id。還有一棵id主鍵聚族索引樹，我們再畫出聚族索引樹圖吧：

我們的查詢語句是怎麼找到比對資料的呢？先通過idx_city索引樹，找到對應的主鍵id，然後再通過拿到的主鍵id，搜尋id主鍵索引樹，找到對應的行資料。

加上order by之後，整體的執行流程就是：

MySQL 為對應的線程初始化sort_buffer，放入需要查詢的name、age、city字段；
從索引樹idx_city，找到第一個滿足 city='深圳’條件的主鍵 id，也就是圖中的id=9；
到主鍵 id 索引樹拿到id=9的這一行資料，取name、age、city三個字段的值，存到sort_buffer；
從索引樹idx_city 拿到下一個記錄的主鍵 id，即圖中的id=13；
重複步驟 3、4 直到city的值不等于深圳為止；
前面5步已經查找到了所有city為深圳的資料，在 sort_buffer中，将所有資料根據age進行排序；
按照排序結果取前10行傳回給用戶端。

執行示意圖如下：

将查詢所需的字段全部讀取到sort_buffer中，就是全字段排序。這裡面，有些小夥伴可能會有個疑問,把查詢的所有字段都放到sort_buffer，而sort_buffer是一塊記憶體來的，如果資料量太大，sort_buffer放不下怎麼辦呢？

磁盤臨時檔案輔助排序

實際上，sort_buffer的大小是由一個參數控制的：sort_buffer_size。如果要排序的資料小于sort_buffer_size，排序在sort_buffer 記憶體中完成，如果要排序的資料大于sort_buffer_size，則借助磁盤檔案來進行排序

如何确定是否使用了磁盤檔案來進行排序呢？可以使用以下這幾個指令

## 打開optimizer_trace，開啟統計
set optimizer_trace = "enabled=on";
## 執行SQL語句
select name,age,city from staff where city = '深圳' order by age limit 10;
## 查詢輸出的統計資訊
select * from information_schema.optimizer_trace

可以從 number_of_tmp_files 中看出，是否使用了臨時檔案。

number_of_tmp_files 表示使用來排序的磁盤臨時檔案數。如果number_of_tmp_files>0，則表示使用了磁盤檔案來進行排序。

使用了磁盤臨時檔案，整個排序過程又是怎樣的呢？

從主鍵Id索引樹，拿到需要的資料，并放到sort_buffer記憶體塊中。當sort_buffer快要滿時，就對sort_buffer中的資料排序，排完後，把資料臨時放到磁盤一個小檔案中。
繼續回到主鍵 id 索引樹取資料，繼續放到sort_buffer記憶體中，排序後，也把這些資料寫入到磁盤臨時小檔案中。
繼續循環，直到取出所有滿足條件的資料。最後把磁盤的臨時排好序的小檔案，合并成一個有序的大檔案。

TPS: 借助磁盤臨時小檔案排序，實際上使用的是歸并排序算法。

小夥伴們可能會有個疑問，既然sort_buffer放不下，就需要用到臨時磁盤檔案，這會影響排序效率。那為什麼還要把排序不相關的字段（name，city）放到sort_buffer中呢？隻放排序相關的age字段，它不香嗎？可以了解下rowid 排序。

rowid 排序

rowid 排序就是，隻把查詢SQL需要用于排序的字段和主鍵id，放到sort_buffer中。那怎麼确定走的是全字段排序還是rowid 排序排序呢？

實際上有個參數控制的。這個參數就是max_length_for_sort_data，它表示MySQL用于排序行資料的長度的一個參數，如果單行的長度超過這個值，MySQL 就認為單行太大，就換rowid 排序。我們可以通過指令看下這個參數取值。

show variables like 'max_length_for_sort_data';

max_length_for_sort_data 預設值是1024。因為本文示例中name,age,city長度=64+4+64 =132 < 1024, 是以走的是全字段排序。我們來改下這個參數，改小一點，

## 修改排序資料最大單行長度為32
set max_length_for_sort_data = 32;
## 執行查詢SQL
select name,age,city from staff where city = '深圳' order by age limit 10;

使用rowid 排序的話，整個SQL執行流程又是怎樣的呢？

MySQL 為對應的線程初始化sort_buffer，放入需要排序的age字段，以及主鍵id；
從索引樹idx_city，找到第一個滿足 city='深圳’條件的主鍵 id，也就是圖中的id=9；
到主鍵 id 索引樹拿到id=9的這一行資料，取age和主鍵id的值，存到sort_buffer；
從索引樹idx_city 拿到下一個記錄的主鍵 id，即圖中的id=13；
重複步驟 3、4 直到city的值不等于深圳為止；
前面5步已經查找到了所有city為深圳的資料，在sort_buffer中，将所有資料根據age進行排序；
周遊排序結果，取前10行，并按照 id 的值回到原表中，取出city、name 和 age 三個字段傳回給用戶端。

執行示意圖如下：

對比一下全字段排序的流程，rowid 排序多了一次回表。

★

什麼是回表？拿到主鍵再回到主鍵索引查詢的過程，就叫做回表

”

我們通過optimizer_trace，可以看到是否使用了rowid排序的：

## 打開optimizer_trace，開啟統計
set optimizer_trace = "enabled=on";
## 執行SQL語句
select name,age,city from staff where city = '深圳' order by age limit 10;
## 查詢輸出的統計資訊
select * from information_schema.optimizer_trace

全字段排序與rowid排序對比

全字段排序：sort_buffer記憶體不夠的話，就需要用到磁盤臨時檔案，造成磁盤通路。
rowid排序：sort_buffer可以放更多資料，但是需要再回到原表去取資料，比全字段排序多一次回表。

一般情況下，對于InnoDB存儲引擎，會優先使用全字段排序。可以發現 max_length_for_sort_data 參數設定為1024，這個數比較大的。一般情況下，排序字段不會超過這個值，也就是都會走全字段排序。

order by的一些優化思路

我們如何優化order by語句呢？

因為資料是無序的，是以就需要排序。如果資料本身是有序的，那就不用排了。而索引資料本身是有序的，我們通過建立聯合索引，優化order by 語句。
我們還可以通過調整max_length_for_sort_data等參數優化；

聯合索引優化

再回顧下示例SQL的查詢計劃

explain select name,age,city from staff where city = '深圳' order by age limit 10;

我們給查詢條件

city

和排序字段

age

，加個聯合索引idx_city_age。再去檢視執行計劃

alter table staff add  index idx_city_age(city,age);
explain select name,age,city from staff where city = '深圳' order by age limit 10;

可以發現，加上idx_city_age聯合索引，就不需要Using filesort排序了。為什麼呢？因為索引本身是有序的，我們可以看下idx_city_age聯合索引示意圖，如下：

整個SQL執行流程變成醬紫：

從索引idx_city_age找到滿足city='深圳’ 的主鍵 id
到主鍵 id索引取出整行，拿到 name、city、age 三個字段的值，作為結果集的一部分直接傳回
從索引idx_city_age取下一個記錄主鍵id
重複步驟 2、3，直到查到第10條記錄，或者是不滿足city='深圳’ 條件時循環結束。

流程示意圖如下：

從示意圖看來，還是有一次回表操作。針對本次示例，有沒有更高效的方案呢？有的，可以使用覆寫索引：

★

覆寫索引：在查詢的資料列裡面，不需要回表去查，直接從索引列就能取到想要的結果。換句話說，你SQL用到的索引列資料，覆寫了查詢結果的列，就算上覆寫索引了。

”

我們給city，name，age 組成一個聯合索引，即可用到了覆寫索引，這時候SQL執行時，連回表操作都可以省去啦。

調整參數優化

我們還可以通過調整參數，去優化order by的執行。比如可以調整sort_buffer_size的值。因為sort_buffer值太小，資料量大的話，會借助磁盤臨時檔案排序。如果MySQL伺服器配置高的話，可以使用稍微調整大點。

我們還可以調整max_length_for_sort_data的值，這個值太小的話，order by會走rowid排序，會回表，降低查詢性能。是以max_length_for_sort_data可以适當大一點。

當然，很多時候，這些MySQL參數值，我們直接采用預設值就可以了。

使用order by 的一些注意點

沒有where條件，order by字段需要加索引嗎

日常開發過程中，我們可能會遇到沒有where條件的order by，那麼，這時候order by後面的字段是否需要加索引呢。如有這麼一個SQL，create_time是否需要加索引：

select * from A order by create_time;

無條件查詢的話，即使create_time上有索引,也不會使用到。因為MySQL優化器認為走普通二級索引，再去回表成本比全表掃描排序更高。是以選擇走全表掃描,然後根據全字段排序或者rowid排序來進行。

如果查詢SQL修改一下：

select * from A order by create_time limit m;

無條件查詢,如果m值較小,是可以走索引的.因為MySQL優化器認為，根據索引有序性去回表查資料,然後得到m條資料,就可以終止循環,那麼成本比全表掃描小,則選擇走二級索引。

分頁limit過大時，會導緻大量排序怎麼辦?

假設SQL如下：

select * from A order by a limit 100000,10

可以記錄上一頁最後的id，下一頁查詢時，查詢條件帶上id，如：where id > 上一頁最後id limit 10。
也可以在業務允許的情況下，限制頁數。

索引存儲順序與order by不一緻，如何優化？

假設有聯合索引 idx_age_name, 我們需求修改為這樣：查詢前10個員工的姓名、年齡，并且按照年齡小到大排序，如果年齡相同，則按姓名降序排。對應的 SQL 語句就可以這麼寫：

select name,age from staff  order by age ,name desc limit 10;

我們看下執行計劃，發現使用到Using filesort。

這是因為，idx_age_name索引樹中，age從小到大排序，如果age相同，再按name從小到大排序。而order by 中，是按age從小到大排序，如果age相同，再按name從大到小排序。也就是說，索引存儲順序與order by不一緻。

我們怎麼優化呢？如果MySQL是8.0版本，支援Descending Indexes，可以這樣修改索引：

CREATE TABLE `staff` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
  `id_card` varchar(20) NOT NULL COMMENT '身份證号碼',
  `name` varchar(64) NOT NULL COMMENT '姓名',
  `age` int(4) NOT NULL COMMENT '年齡',
  `city` varchar(64) NOT NULL COMMENT '城市',
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`age`,`name` desc) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='員工表';

使用了in條件多個屬性時，SQL執行是否有排序過程

如果我們有聯合索引idx_city_name，執行這個SQL的話，是不會走排序過程的，如下：

select * from staff where city in ('深圳') order by age limit 10;

但是，如果使用in條件，并且有多個條件時，就會有排序過程。

explain select * from staff where city in ('深圳','上海') order by age limit 10;

最後

如果覺得有收獲，幫忙點贊，轉發下哈，感謝感謝
公衆号：撿田螺的小男孩，
公衆号背景回複資料，可以獲得原創學習資料

參考與感謝

MySQL實戰45講

看一遍就了解：order by詳解

前言

一個使用order by 的簡單例子

order by 工作原理

explain 執行計劃

全字段排序

磁盤臨時檔案輔助排序

rowid 排序

全字段排序與rowid排序對比

order by的一些優化思路

聯合索引優化

調整參數優化

使用order by 的一些注意點

沒有where條件，order by字段需要加索引嗎

分頁limit過大時，會導緻大量排序怎麼辦?

索引存儲順序與order by不一緻，如何優化？

使用了in條件多個屬性時，SQL執行是否有排序過程

最後

參考與感謝

繼續閱讀

Kafka：Topic概念與API介紹

5G小型蜂應用指南

PAT (Advanced Level) Practise 1012 The Best Rank (25)

mysql5.7的sql優化

線程通信和程序通信差別（線程程序差別）

Matlab随機波動率SV、GARCH用MCMC馬爾可夫鍊蒙特卡羅方法分析匯率時間序列

微信小程式前端解密擷取使用者資訊

Spring MVC 自學雜記（五） -- SpringMVC與前台的json資料互動

《MySQL技術内幕：InnoDB存儲引擎》筆記

擴容TIKV節點遇到的坑

PHP輔導代做程式設計：CS353 Database System

自學Zabbix3.10.2-事件通知Notifications upon events-Actions報警配置點選傳回：自學zabbix集錦

HDU 5678 ztr loves trees

拓端tecdat|R語言彈性網絡Elastic Net正則化懲罰回歸模型交叉驗證可視化

二叉樹及其應用--二叉樹建立

詳解STM32單片機的堆棧