看一遍就理解: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;
這是因爲: in 有兩個條件,在滿足深圳時,age 是排好序的,但是把滿足上海的 age 也加進來,就不能保證滿足所有的 age 都是排好序的。因此需要 Using filesort。
參考與感謝
- MySQL 實戰 45 講
推薦關注「數據分析與開發」,提升數據技能
本文由 Readfog 進行 AMP 轉碼,版權歸原作者所有。
來源:https://mp.weixin.qq.com/s/_Cnfy9uKvBg2IWUchbrSNg