在MySQL的查詢中常常會用到 order by 和 group by 這兩個關鍵字,它們的相同點是都會對欄位進行排序,那查詢語句中的排序是如何實現的呢? ...
本文分享自華為雲社區《MySQL怎樣處理排序⭐️如何優化需要排序的查詢?》,作者:菜菜的後端私房菜。
前言
在MySQL的查詢中常常會用到 order by
和 group by
這兩個關鍵字
它們的相同點是都會對欄位進行排序,那查詢語句中的排序是如何實現的呢?
當使用的查詢語句需要進行排序時有兩種處理情況:
- 當前記錄本來就是有序的,不需要進行排序
- 當前記錄未保持順序,需要排序
使用索引保證有序
對於第一種情況,常常是使用二級索引中索引列的有序來保證結果集有序,從而不需要進行排序
對於表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列的記錄本身就是有序的,因此不需要再使用其他開銷進行排序
當然,優化器也有可能不使用a2索引(當優化器認為使用a2回表開銷太大時會使用全表掃描)
當優化器使用的索引上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中,然後對需要排序的列進行排序,最後返回結果
當查詢需要的欄位長度大於 max_length_for_sort_data
時,只會將需要排序的欄位和主鍵值放入sort_buffer中,等到排序後再去查詢聚簇索引獲取需要查詢的列(相當於又多了一次回表)
在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大小
因此當使用order by、group by等需要排序的關鍵字時,最好建立合適的索引
如果數據量小可以在sort buffer中排序,如果數據量太大還需要與磁碟交互
總結
當查詢語句需要排序時會分為不用排序和需要排序兩種情況
當使用的索引有序時則不用再進行排序,通過索引來保證有序
當使用的索引無序時則會使用sort_buffer進行排序,當查詢欄位的長度未超過限制時,sort_buffer中每條記錄會存儲需要查詢的列
如果超過限制,則sort_buffer只會存儲需要排序的列和主鍵值,排序後再通過主鍵值進行回表獲取需要查詢的列
當數據量太大不夠在記憶體中排序完,會使用磁碟頁輔助排序,使用歸併演算法將排序數據分散在多個頁再合併
可以通過追蹤優化器 optimizer_trace 分析內容查看輔助頁的數量等信息
為需要排序的列建立合適的索引,避免使用磁碟頁輔助排序
當無法使用索引時可以調整sort buffer 或 max_length_for_sort_data(謹慎)