前言
- 文章首發于微信公眾號【碼猿技術專欄】，
- 在實際的開發中一定會碰到根據某個欄位進行排序后來顯示結果的需求，但是你真的理解
order by在 Mysql 底層是如何執行的嗎？ - 假設你要查詢城市是
蘇州的所有人名字，并且按照姓名進行排序回傳前 1000 個人的姓名、年齡，這條 sql 陳述句應該如何寫？ - 首先創建一張用戶表，sql 陳述句如下：
CREATE TABLE user (
id int(11) NOT NULL,
city varchar(16) NOT NULL,
name varchar(16) NOT NULL,
age int(11) NOT NULL,
PRIMARY KEY (id),
KEY city (city)
) ENGINE=InnoDB;
- 則上述需求的 sql 查詢陳述句如下：
select city,name,age from user where city='蘇州' order by name limit 1000;
- 這條 sql 查詢陳述句相信大家都能寫出來，但是你了解它在 Mysql 底層的執行流程嗎？今天陳某來大家聊一聊這條 sql 陳述句是如何執行的以及有什么引數會影響執行的流程，
- 本篇文章分為如下幾個部分進行詳細的闡述：
- 全欄位排序
- rowid 排序
- 全欄位排序 VS rowid 排序
- 如何避免排序
全欄位排序
- 前面聊過索引能夠避免全表掃描，因此我們給
city這個欄位上添加了索引，當然城市的欄位很小，不用考慮字串的索引問題，之前有寫過一篇關于如何給字串的加索引的文章，有不了解朋友看一下這篇文章:Mysql 性能優化：如何給字串加索引？ - 此時用
Explain來分析一下的這條查詢陳述句的執行情況，結果如下圖：
Extra這個欄位中的Using filesort表示的就是需要排序，MySQL 會給每個執行緒分配一塊記憶體用于排序，稱為sort_buffer，- 既然使用了索引進行查詢，我們來簡單的畫一下
city這棵索引樹的結構，如下圖：
- 從上圖可以看出，滿足
city='蘇州'是從ID3到IDX這些記錄， - 通常情況下，此條 sql 陳述句執行流程如下：我們稱這個排序程序為
全欄位排序，執行的流程圖如下：
- 初始化 sort_buffer，確定放入 name、city、age 這三個欄位，
- 從索引 city 找到第一個滿足
city='蘇州'條件的主鍵id，也就是圖中的ID3， - 到
主鍵id索引取出整行，取name、city、age三個欄位的值，存入sort_buffer中， - 從索引
city取下一個記錄的主鍵 id， - 重復步驟 3、4 直到 city 的值不滿足查詢條件為止，對應的主鍵 id 也就是圖中的
IDX， - 對
sort_buffer中的資料按照欄位name做快速排序， - 按照排序結果取前 1000 行回傳給客戶端，
- 圖中
按name排序這個動作，可能在記憶體中完成，也可能需要使用外部排序，這取決于排序所需的記憶體和引數sort_buffer_size， sort_buffer_size：就是 MySQL 為排序開辟的記憶體（sort_buffer）的大小，如果要排序的資料量小于 sort_buffer_size，排序就在記憶體中完成，但如果排序資料量太大，記憶體放不下，則不得不利用磁盤臨時檔案輔助排序，
rowid 排序
- 在上面這個演算法程序里面，只對原表的資料讀了一遍，剩下的操作都是在
sort_buffer和臨時檔案中執行的，但這個演算法有一個問題，就是如果查詢要回傳的欄位很多的話，那么sort_buffer里面要放的欄位數太多，這樣記憶體里能夠同時放下的行數很少，要分成很多個臨時檔案，排序的性能會很差， - 所以如果單行很大，這個方法效率不夠好，
- 我們可以修改一個
max_length_for_sort_data這個引數使其使用另外一種演算法，max_length_for_sort_data，是 MySQL 中專門控制用于排序的行資料的長度的一個引數，它的意思是，如果單行的長度超過這個值，MySQL 就認為單行太大，要換一個演算法， city、name、age這三個欄位的定義總長度是36，我把max_length_for_sort_data設定為 16，我們再來看看計算程序有什么改變，設定的 sql 陳述句如下：
SET max_length_for_sort_data = 16;
-
新的演算法放入 sort_buffer 的欄位，只有要排序的列（即 name 欄位）和主鍵 id，
-
但這時，排序的結果就因為少了 city 和 age 欄位的值，不能直接回傳了，整個執行流程就變成如下所示的樣子：
- 初始化
sort_buffer，確定放入兩個欄位，即name和id， - 從索引 city 找到第一個滿足
city='蘇州'條件的主鍵id，也就是圖中的ID3， - 到
主鍵id索引取出整行，取 name、id 這兩個欄位，存入 sort_buffer 中， - 從索引
city取下一個記錄的主鍵 id， - 重復步驟 3、4 直到 city 的值不滿足查詢條件為止，對應的主鍵 id 也就是圖中的
IDX， - 對
sort_buffer中的資料按照欄位name做快速排序， - 遍歷排序結果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三個欄位回傳給客戶端，
- 初始化
-
這個執行流程的示意圖如下，我把它稱為
rowid排序，
-
對比
全欄位排序，rowid排序多了一次回表查詢，即是多了第7步的查詢主鍵索引樹，
全欄位排序 VS rowid 排序
- 如果 MySQL 實在是擔心排序記憶體太小，會影響排序效率，才會采用 rowid 排序演算法，這樣排序程序中一次可以排序更多行，但是需要再回到原表去取資料，
- 如果 MySQL 認為記憶體足夠大，會優先選擇全欄位排序，把需要的欄位都放到 sort_buffer 中，這樣排序后就會直接從記憶體里面回傳查詢結果了，不用再回到原表去取資料，
- 這也就體現了 MySQL 的一個設計思想：如果記憶體夠，就要多利用記憶體，盡量減少磁盤訪問，
- 對于 InnoDB 表來說，rowid 排序會要求回表多造成磁盤讀，因此不會被優先選擇，
如何避免排序
- 其實，并不是所有的
order by陳述句，都需要排序操作的，從上面分析的執行程序，我們可以看到，MySQL 之所以需要生成臨時表，并且在臨時表上做排序操作，其原因是原來的資料都是無序的， - 如果能夠保證從
city這個索引上取出來的行，天然就是按照 name 遞增排序的話，是不是就可以不用再排序了呢？ - 因此想到了聯合索引，創建
(city,name)聯合索引，sql 陳述句如下：
alter table user add index city_user(city, name);
- 此時的索引樹如下：
-
- 在這個索引里面，我們依然可以用樹搜索的方式定位到第一個滿足
city='蘇州'的記錄，并且額外確保了，接下來按順序取“下一條記錄”的遍歷程序中，只要 city 的值是杭州，name 的值就一定是有序的， - 按照上圖，整個查詢的流程如下：對應的流程圖如下：
- 從索引(city,name)找到第一個滿足 city='蘇州’條件的主鍵 id，
- 到主鍵 id 索引取出整行，取 name、city、age 三個欄位的值，作為結果集的一部分直接回傳，
- 從索引(city,name)取下一個記錄主鍵 id，
- 重復步驟 2、3，直到查到第 1000 條記錄，或者是不滿足 city='蘇州'條件時回圈結束，
- 可以看到，這個查詢程序不需要臨時表，也不需要排序，接下來，我們用 explain 的結果來印證一下，
- 從圖中可以看到，
Extra欄位中沒有Using filesort了，也就是不需要排序了，而且由于(city,name)這個聯合索引本身有序，所以這個查詢也不用把 4000 行全都讀一遍，只要找到滿足條件的前 1000 條記錄就可以退出了，也就是說，在我們這個例子里，只需要掃描 1000 次， - 難道僅僅這樣就能滿足了？此條查詢陳述句是否能再優化呢？
-
- 朋友們還記得覆寫索引嗎？覆寫索引的好處就是能夠避免再次回表查詢，不了解的朋友們可以看一下陳某之前寫的文章：Mysql 性能優化：如何使用覆寫索引？，
- 我們創建
(city,name,age)聯合索引，這樣在執行上面的查詢陳述句就能使用覆寫索引了，避免了回表查詢了，sql 陳述句如下：
alter table user add index city_user_age(city, name, age);
- 此時執行流程圖如下：
-
- 當然，覆寫索引能夠提升效率，但是維護索引也是需要代價的，因此還需要權衡使用，
總結
- 今天這篇文章，我和你介紹了 MySQL 里面
order by陳述句的幾種演算法流程， - 在開發系統的時候，你總是不可避免地會使用到 order by 陳述句，心里要清楚每個陳述句的排序邏輯是怎么實作的，還要能夠分析出在最壞情況下，每個陳述句的執行對系統資源的消耗，這樣才能做到下筆如有神，不犯低級錯誤，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/56222.html
標籤：MySQL