聊聊MySQL是如何處理排序的

华为云开发者联盟發表於2024-05-16
MySql排序

本文分享自華為雲社群《MySQL怎樣處理排序⭐️如何最佳化需要排序的查詢？》，作者：菜菜的後端私房菜。

前言

在MySQL的查詢中常常會用到 order by 和 group by 這兩個關鍵字

它們的相同點是都會對欄位進行排序，那查詢語句中的排序是如何實現的呢？

當使用的查詢語句需要進行排序時有兩種處理情況：

  1. 當前記錄本來就是有序的，不需要進行排序
  2. 當前記錄未保持順序，需要排序

使用索引保證有序

對於第一種情況，常常是使用二級索引中索引列的有序來保證結果集有序，從而不需要進行排序

對於表a，為a2建立二級索引，那麼在二級索引上a2就是有序的

 CREATE TABLE `a` (
   `a1` int(11) NOT NULL AUTO_INCREMENT,
   `a2` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
   `a3` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`a1`),
   KEY `idx_a2` (`a2`)
 ) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8;

select * from a order by a.a2 limit 10

當最佳化器選擇使用a2索引時，a2列的記錄本身就是有序的，因此不需要再使用其他開銷進行排序

image.png

當然，最佳化器也有可能不使用a2索引（當最佳化器認為使用a2回表開銷太大時會使用全表掃描）

image.png

當最佳化器使用的索引上a2無序時，則會透過其他手段對結果進行排序

filesort

當執行計劃的Extra附加資訊中出現 Using filesort 時，會使用sort_buffer對結果進行排序

sort_buffer是一塊用於排序的記憶體，sort_buffer可能存放查詢需要的所有欄位，也可能只存放需要排序的欄位和主鍵

show variables like 'max_length_for_sort_data'

當查詢需要的欄位長度小於 max_length_for_sort_data 時，則會將查詢需要的所有欄位放入sort_buffer中，然後對需要排序的列進行排序，最後返回結果

image.png

當查詢需要的欄位長度大於 max_length_for_sort_data 時，只會將需要排序的欄位和主鍵值放入sort_buffer中，等到排序後再去查詢聚簇索引獲取需要查詢的列（相當於又多了一次回表）

image.png

在sort_buffer中進行排序時，如果記憶體足夠則會在記憶體中進行排序，如果記憶體不夠則會使用磁碟的臨時檔案來輔助排序

開啟 optimizer_trace 可以檢視是否使用臨時檔案輔助排序

 #開啟最佳化器追蹤
 SET optimizer_trace='enabled=on'; 
 ​
 #sql語句
 select * from student order by student_name limit 10000;
 ​
 #檢視最佳化器追蹤的資訊
 SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G;

排序使用的演算法是歸併演算法，先分割成多個小檔案排序再進行合併

其中number_of_tmp_files 為使用到的臨時檔案數量，sort_buffer_size 為sort_buffer大小

image.png

因此當使用order by、group by等需要排序的關鍵字時，最好建立合適的索引

如果資料量小可以在sort buffer中排序，如果資料量太大還需要與磁碟互動

總結

當查詢語句需要排序時會分為不用排序和需要排序兩種情況

當使用的索引有序時則不用再進行排序，透過索引來保證有序

當使用的索引無序時則會使用sort_buffer進行排序，當查詢欄位的長度未超過限制時，sort_buffer中每條記錄會儲存需要查詢的列

如果超過限制，則sort_buffer只會儲存需要排序的列和主鍵值，排序後再透過主鍵值進行回表獲取需要查詢的列

當資料量太大不夠在記憶體中排序完，會使用磁碟頁輔助排序，使用歸併演算法將排序資料分散在多個頁再合併

可以透過追蹤最佳化器 optimizer_trace 分析內容檢視輔助頁的數量等資訊

為需要排序的列建立合適的索引，避免使用磁碟頁輔助排序

當無法使用索引時可以調整sort buffer 或 max_length_for_sort_data（謹慎）

點選關注，第一時間瞭解華為雲新鮮技術~

相關文章