前言

• 在實際的開發(fā)中一定會碰到根據某個字段進行排序后來顯示結果的需求，但是你真的理解order by在 Mysql 底層是如何執(zhí)行的嗎？
• 假設你要查詢城市是蘇州的所有人名字，并且按照姓名進行排序返回前 1000 個人的姓名、年齡，這條 sql 語句應該如何寫？
• 首先創(chuàng)建一張用戶表，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 底層的執(zhí)行流程嗎？今天陳某來大家聊一聊這條 sql 語句是如何執(zhí)行的以及有什么參數會影響執(zhí)行的流程。
• 本篇文章分為如下幾個部分進行詳細的闡述：

全字段排序

rowid 排序

全字段排序 VS rowid 排序

如何避免排序

全字段排序

• 前面聊過索引能夠避免全表掃描，因此我們給city這個字段上添加了索引，當然城市的字段很小，不用考慮字符串的索引問題，之前有寫過一篇關于如何給字符串的加索引的文章，有不了解朋友看一下這篇文章:Mysql 性能優(yōu)化：如何給字符串加索引？
• 此時用Explain來分析一下的這條查詢語句的執(zhí)行情況，結果如下圖：
• Extra這個字段中的Using filesort表示的就是需要排序，MySQL 會給每個線程分配一塊內存用于排序，稱為sort_buffer。
• 既然使用了索引進行查詢，我們來簡單的畫一下city這棵索引樹的結構，如下圖：
• 從上圖可以看出，滿足city='蘇州'是從ID3到IDX這些記錄。
• 通常情況下，此條 sql 語句執(zhí)行流程如下：

初始化 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 行返回給客戶端。

我們稱這個排序過程為全字段排序，執(zhí)行的流程圖如下：圖中按name排序這個動作，可能在內存中完成，也可能需要使用外部排序，這取決于排序所需的內存和參數sort_buffer_size。sort_buffer_size：就是 MySQL 為排序開辟的內存(sort_buffer)的大小。如果要排序的數據量小于 sort_buffer_size，排序就在內存中完成。但如果排序數據量太大，內存放不下，則不得不利用磁盤臨時文件輔助排序。

rowid 排序

• 在上面這個算法過程里面，只對原表的數據讀了一遍，剩下的操作都是在sort_buffer和臨時文件中執(zhí)行的。但這個算法有一個問題，就是如果查詢要返回的字段很多的話，那么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 字段的值，不能直接返回了，整個執(zhí)行流程就變成如下所示的樣子：

初始化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 三個字段返回給客戶端。

這個執(zhí)行流程的示意圖如下，我把它稱為rowid排序。

對比全字段排序，rowid排序多了一次回表查詢，即是多了第7步的查詢主鍵索引樹。

全字段排序 VS rowid 排序

• 如果 MySQL 實在是擔心排序內存太小，會影響排序效率，才會采用 rowid 排序算法，這樣排序過程中一次可以排序更多行，但是需要再回到原表去取數據。
• 如果 MySQL 認為內存足夠大，會優(yōu)先選擇全字段排序，把需要的字段都放到 sort_buffer 中，這樣排序后就會直接從內存里面返回查詢結果了，不用再回到原表去取數據。
• 這也就體現了 MySQL 的一個設計思想：如果內存夠，就要多利用內存，盡量減少磁盤訪問。
• 對于 InnoDB 表來說，rowid 排序會要求回表多造成磁盤讀，因此不會被優(yōu)先選擇。

如何避免排序

• 其實，并不是所有的order by語句，都需要排序操作的。從上面分析的執(zhí)行過程，我們可以看到，MySQL 之所以需要生成臨時表，并且在臨時表上做排序操作，其原因是原來的數據都是無序的。
• 如果能夠保證從city這個索引上取出來的行，天然就是按照 name 遞增排序的話，是不是就可以不用再排序了呢？
• 因此想到了聯合索引，創(chuàng)建(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='蘇州'條件時循環(huán)結束。

對應的流程圖如下：可以看到，這個查詢過程不需要臨時表，也不需要排序。接下來，我們用 explain 的結果來印證一下。從圖中可以看到，Extra字段中沒有Using filesort了，也就是不需要排序了。而且由于(city,name)這個聯合索引本身有序，所以這個查詢也不用把 4000 行全都讀一遍，只要找到滿足條件的前 1000 條記錄就可以退出了。也就是說，在我們這個例子里，只需要掃描 1000 次。難道僅僅這樣就能滿足了？此條查詢語句是否能再優(yōu)化呢？朋友們還記得覆蓋索引嗎？覆蓋索引的好處就是能夠避免再次回表查詢，不了解的朋友們可以看一下陳某之前寫的文章：Mysql 性能優(yōu)化：如何使用覆蓋索引？。我們創(chuàng)建(city,name,age)聯合索引，這樣在執(zhí)行上面的查詢語句就能使用覆蓋索引了，避免了回表查詢了，sql 語句如下：alter?table?user?add?index?city_user_age(city,?name,?age);

• 此時執(zhí)行流程圖如下：
• 當然，覆蓋索引能夠提升效率，但是維護索引也是需要代價的，因此還需要權衡使用。

總結

• 今天這篇文章，我和你介紹了 MySQL 里面order by語句的幾種算法流程。
• 在開發(fā)系統(tǒng)的時候，你總是不可避免地會使用到 order by 語句。心里要清楚每個語句的排序邏輯是怎么實現的，還要能夠分析出在最壞情況下，每個語句的執(zhí)行對系統(tǒng)資源的消耗，這樣才能做到下筆如有神，不犯低級錯誤。

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎勵來咯，堅持創(chuàng)作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的权限表使用联合主键吗_天天写 order by，你知道Mysql底层执行流程吗？的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。