對order by的理解

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

對order by的理解對order by的理解

前言

日常開發中,我們經常會使用到order by,親愛的小夥伴,你是否知道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 '員工表';

表資料如下:

對order by的理解對order by的理解

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

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

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

order by 工作原理

對order by的理解對order by的理解

explain 執行計劃

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

對order by的理解對order by的理解

  • 執行計劃的key這個欄位,表示使用到索引idx_city
  • Extra 這個欄位的 Using index condition 表示索引條件
  • Extra 這個欄位的 Using filesort表示用到排序

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

全欄位排序

MySQL 會給每個查詢執行緒分配一塊小記憶體,用於排序的,稱為 sort_buffer。什麼時候把欄位放進去排序呢,其實是通過idx_city索引找到對應的資料,才把資料放進去啦。

我們回顧下索引是怎麼找到匹配的資料的,現在先把索引樹畫出來吧,idx_city索引樹如下:

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

對order by的理解對order by的理解

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

對order by的理解對order by的理解

加上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行返回給客戶端。

執行示意圖如下:

對order by的理解對order by的理解

將查詢所需的欄位全部讀取到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 中看出,是否使用了臨時檔案。

對order by的理解對order by的理解

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

對order by的理解對order by的理解

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 三個欄位返回給客戶端。

執行示意圖如下:

對order by的理解對order by的理解

對比一下全欄位排序的流程,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

對order by的理解對order by的理解

全欄位排序與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;

對order by的理解對order by的理解

我們給查詢條件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;

對order by的理解對order by的理解

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

對order by的理解對order by的理解

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

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

流程示意圖如下:

對order by的理解對order by的理解

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

覆蓋索引:在查詢的資料列裡面,不需要回表去查,直接從索引列就能取到想要的結果。換句話說,你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

對order by的理解對order by的理解

這是因為,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;

對order by的理解對order by的理解

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

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

對order by的理解對order by的理解

這是因為:in有兩個條件,在滿足深圳時,age是排好序的,但是把滿足上海的age也加進來,就不能保證滿足所有的age都是排好序的。因此需要Using filesort。

本文地址:https://www.linuxprobe.com/order-by.html