MySQL的order by怎么正確運用

2024-07-24 12:33:32

字體：大中小

供稿：網(wǎng)友

　　這篇文章主要介紹“MySQL的order by怎么正確使用”的相關(guān)知識，小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強(qiáng)，希望這篇“MySQL的order by怎么正確使用”文章能幫助大家解決問題。

　　排序這個詞，我的第一感覺是幾乎所有App都有排序的地方，淘寶商品有按照購買時間的排序、B站的評論有按照熱度排序的...，當(dāng)然我們今天說的并不是大數(shù)據(jù)下該如何優(yōu)雅的排序，如何提升排序性能的問題，我們說一說MySQL中的排序。

　　對于MySQL，一說到排序，你第一時間想到的是什么？關(guān)鍵字order by？order by的字段最好有索引？葉子結(jié)點已經(jīng)是順序的？還是說盡量不要在MySQL內(nèi)部排序？

　　事情的起因

　　現(xiàn)在假設(shè)有一張用戶的朋友表：

　　CREATE TABLE `user` (
　　 `id` int(10) AUTO_INCREMENT,
　　 `user_id` int(10),
　　 `friend_addr` varchar(1000),
　　 `friend_name` varchar(100),
　　 PRIMARY KEY (`id`),
　　 KEY `user_id` (`user_id`)
　　) ENGINE=InnoDB;
　　表中目前有兩個點需要關(guān)注下：

　　用戶的 user_id ，朋友的姓名 friend_name、朋友的地址 friend_addr

　　user_id 是有索引的

　　有一天，有個初級開發(fā)工程師小猿，收到了來自初級產(chǎn)品經(jīng)理小汪的需求：

　　小汪：小猿同志，現(xiàn)在需要在后臺加個功能，這個功能要支持根據(jù)用戶 id 能查到他所有的朋友姓名和地址，并且要求朋友的姓名是按照字典排序的。

　　小猿：好的，這個功能簡單，我馬上就上線。

　　于是小猿書寫了這樣的sql：

　　select friend_name，friend_addr from user where user_id=? order by name
　　在電光石火的瞬間，小猿趾高氣昂的上線了，這一切都很順利，直到有一天有個運營同學(xué)導(dǎo)致了這樣的查詢：

　　select friend_name，friend_addr from user where user_id=10086 order by name
　　然而，這個查詢竟然比平時慢很多，數(shù)據(jù)庫報了慢查詢，小猿此時慌的一b：這是怎么回事？user_id 明明有索引啊，而且機(jī)智地我還只用了 select friend_name,friend_addr，并沒有用 select *呀。小猿此時不停地安慰自己，要淡定要淡定，然后突然想到有個explain命令，用explain來查看下那條sql的執(zhí)行計劃吧，當(dāng)小猿用了explain之后，發(fā)現(xiàn)extra字段里面有個看起來很危險的字眼：using filesort。

　　“這個查詢竟然用到了傳說中的文件排序，但是如果一個人朋友不是很多，就算了用了文件排序，應(yīng)該也很快吧”，除非這個user_id=10086的朋友很多，后來小猿去查了下，這個用戶的朋友竟然有10w多個～。

　　陷入了沉思的小猿心想：這個鍋看來是背定了，10w數(shù)據(jù)是有點大了，還有這個 using filesort 到底是怎么個排序原理？

　　解剖文件排序
　　有人可能說上面的問題是10w數(shù)據(jù)太大了，就算不排序也慢，這個其實是有道理的，10w數(shù)據(jù)一次性查出來，無論是MySQL內(nèi)存緩沖區(qū)的占用，還是網(wǎng)絡(luò)帶寬的消耗都是非常大的，那如果我加了limit 1000呢？網(wǎng)絡(luò)帶寬的問題肯定是解決了，因為數(shù)據(jù)包整體變小了，但是 using filesort 的問題其實還是沒有解決，看到這里你可能會有疑問，using filesort 難道是在文件中排序的？在文件中到底是怎么排序的？或者我這樣問：如果給你來設(shè)計排序你會怎么處理？帶著這些疑問和思考我們來看看 using filesort 會涉及到哪些技術(shù)難點以及是如何解決的？

　　首先我們的 user_id 是有索引的，所以會先在 user_id 索引樹上檢索我們的目標(biāo)數(shù)據(jù)，即 user_id=10086 的數(shù)據(jù)，但是我們要查詢的是 friend_name 和 friend_addr 字段，很不幸，光靠 user_id 索引是找不到這兩個字段值的
　　于是需要回表，通過 user_id 對應(yīng)的主鍵去主鍵索引樹上去查找，ok，我們找到了第一條 user_id=10086 的 friend_name 和 friend_addr 字段
　　這時該怎么辦？直接返回回去肯定不對，因為我需要對 friend_name 排序，如何排？數(shù)據(jù)都還沒找全，那么就得把查到的數(shù)據(jù)先放在一個地方，這個地方就是 sort_buffer，看到名字我想你應(yīng)該猜出來，沒錯，sort_buffer 就是用于這種情況下排序用的緩沖區(qū)，這里需要注意的是每個線程都會有一個單獨的 sort_buffer，這么做的目的主要是為了避免多個線程對同一塊內(nèi)存進(jìn)行操作帶來鎖競爭的問題。
　　當(dāng)?shù)谝粭l數(shù)據(jù)的 friend_name 和 friend_addr 已經(jīng)放入 sort_buffer 中，這當(dāng)然沒完，會一直重復(fù)同步的步驟，直至把所有 user_id=10086 的 friend_name 和 friend_addr 都放入到 sort_buffer 中才結(jié)束
　　sort_buffer 中的數(shù)據(jù)已經(jīng)放入完畢，接下來就該排序了，這里 MySQL 會對 friend_name 進(jìn)行快排，通過快排后，sort_buffer 中 friend_name 就是有序的了
　　最后返回 sort_buffer 中的前1000條，結(jié)束。
　　MySQL的order by怎么正確使用

　　一切看起來很絲滑，但是 sort_buffer 占用的是內(nèi)存空間，這就尷尬了，內(nèi)存本身就不是無限大的，它肯定是有上限的，當(dāng)然 sort_buffer 也不能太小，太小的話，意義不大。在 InnoDB 存儲引擎中，這個值是默認(rèn)是256K。

　　mysql> show variables like 'sort_buffer_size';
　　+------------------+--------+
　　| Variable_name    | Value |
　　+------------------+--------+
　　| sort_buffer_size | 262144 |
　　+------------------+--------+
　　也就是說，如果要放進(jìn) sort_buffer 中的數(shù)據(jù)是大于256K的話，那么采用在 sort_buffer 中快排的方式肯定是行不通的，這時候，你可能會問：MySQL難道不能根據(jù)數(shù)據(jù)大小自動擴(kuò)充嗎？額，MySQL是多線程模型，如果每個線程都擴(kuò)充，那么分給其他功能buffer就小了（比如change buffer等），就會影響其他功能的質(zhì)量。

　　這時就得換種方式來排序了，沒錯，此時就是真正的文件排序了，也就是磁盤的臨時文件，MySQL會采用歸并排序的思想，把要排序的數(shù)據(jù)分成若干份，每一份數(shù)據(jù)在內(nèi)存中排序后會放入臨時文件中，最終對這些已經(jīng)排序好的臨時文件的數(shù)據(jù)再做一次合并排序就ok了，典型的分而治之原理，它的具體步驟如下：

　　先將要排序的數(shù)據(jù)分割，分割成每塊數(shù)據(jù)都可以放到 sort_buffer 中
　　對每塊數(shù)據(jù)在 sort_buffer 中進(jìn)行排序，排序好后，寫入某個臨時文件中
　　當(dāng)所有的數(shù)據(jù)都寫入臨時文件后，這時對于每個臨時文件而言，內(nèi)部都是有序的，但是它們并不是一個整體，整體還不是有序的，所以接下來就得合并數(shù)據(jù)了
　　假設(shè)現(xiàn)在存在 tmpX 和 tmpY 兩個臨時文件，這時會從 tmpX 讀取一部分?jǐn)?shù)據(jù)進(jìn)入內(nèi)存，然后從 tmpY 中讀取一部分?jǐn)?shù)據(jù)進(jìn)入內(nèi)存，這里你可能會好奇為什么是一部分而不是整個或者單個？因為首先磁盤是緩慢的，所以盡量每次多讀點數(shù)據(jù)進(jìn)入內(nèi)存，但是不能讀太多，因為還有 buffer 空間的限制。
　　對于 tmpX 假設(shè)讀進(jìn)來了的是 tmpX[0-5] ,對于 tmpY 假設(shè)讀進(jìn)來了的是 tmpY[0-5]，于是只需要這樣比較：
　　如果 tmpX[0] < tmpY[0]，那么 tmpX[0] 肯定是最小的，然后 tmpX[1] 和 tmpY[0] 比較，如果 tmpX[1] > tmpY[0]，那么 tmpY[0] 肯定是第二小的...，就這樣兩兩比較最終就可以把 tmpX 和 tmpY 合并成一個有序的文件tmpZ，多個這樣的tmpZ再次合并...，最終就可以把所有的數(shù)據(jù)合并成一個有序的大文件。

　　文件排序很慢，還有其他辦法嗎
　　通過上面的排序流程我們知道，如果要排序的數(shù)據(jù)很大，超過 sort_buffer 的大小，那么就需要文件排序，文件排序涉及到分批排序與合并，很耗時，造成這個問題的根本原因是 sort_buffer 不夠用，不知道你發(fā)現(xiàn)沒有我們的 friend_name 需要排序，但是卻把 friend_addr 也塞進(jìn)了 sort_buffer 中，這樣單行數(shù)據(jù)的大小就等于 friend_name 的長度 + friend_addr 的長度，能否讓 sort_buffer 中只存 friend_name 字段，這樣的話，整體的利用空間就大了，不一定用得到到臨時文件。沒錯，這就是接下來要說的另一種排序優(yōu)化rowid排序。

　　rowid 排序的思想就是把不需要的數(shù)據(jù)不要放到 sort_buffer 中，讓 sort_buffer 中只保留必要的數(shù)據(jù)，那么你認(rèn)為什么是必要的數(shù)據(jù)呢？只放 friend_name？這肯定不行，排序完了之后，friend_addr 怎么辦？因此還要把主鍵id放進(jìn)去，這樣排完之后，通過 id 再回次表，拿到 friend_addr 即可，因此它的大致流程如下：

　　根據(jù) user_id 索引，查到目標(biāo)數(shù)據(jù)，然后回表，只把 id 和 friend_name 放進(jìn) sort_buffer 中
　　重復(fù)1步驟，直至全部的目標(biāo)數(shù)據(jù)都在 sort_buffer 中
　　對 sort_buffer 中的數(shù)據(jù)按照 friend_name 字段進(jìn)行排序
　　排序后根據(jù) id 再次回表查到 friend_addr 返回，直至返回1000條數(shù)據(jù)，結(jié)束。

　　這里面其實有幾點需要注意的：

　　這種方式需要兩次回表的
　　sort_buffer 雖然小了，但是如果數(shù)據(jù)量本身還是很大，應(yīng)該還是要臨時文件排序的
　　那么問題來了，兩種方式，MySQL 該如何選擇？得根據(jù)某個條件來判斷走哪種方式吧，這個條件就是進(jìn) sort_buffer 單行的長度，如果長度太大（friend_name + friend_addr的長度），就會采用 rowid 這種方式，否則第一種，長度的標(biāo)準(zhǔn)是根據(jù) max_length_for_sort_data 來的，這個值默認(rèn)是1024字節(jié)：

　　mysql> show variables like 'max_length_for_sort_data';
　　+--------------------------+-------+
　　| Variable_name          | Value |
　　+--------------------------+-------+
　　| max_length_for_sort_data | 1024 |
　　+--------------------------+-------+
　　不想回表，不想再次排序

　　其實不管是上面哪種方法，他們都需要回表+排序，回表是因為二級索引上沒有目標(biāo)字段，排序是因為數(shù)據(jù)不是有序的，那如果二級索引上有目標(biāo)字段并且已經(jīng)是排序好的了，那不就兩全其美了嘛。

　　沒錯，就是聯(lián)合索引，我們只需要建立一個（user_id，friend_name，friend_addr）的聯(lián)合索引即可，這樣我就可以通過這個索引拿到目標(biāo)數(shù)據(jù)，并且friend_name已經(jīng)是排序好的，同時還有friend_addr字段，一招搞定，不需要回表，不需要再次排序。因此對于上述的sql，它的大致流程如下：

　　通過聯(lián)合索引找到user_id=10086的數(shù)據(jù)，然后讀取對應(yīng)的 friend_name 和 friend_addr 字段直接返回，因為 friend_name 已經(jīng)是排序好的了，不需要額外處理
　　重復(fù)第一步驟，順著葉子節(jié)點接著向后找，直至找到第一個不是10086的數(shù)據(jù)，結(jié)束。
　　MySQL的order by怎么正確使用

　　聯(lián)合索引雖然可以解決這種問題，但是在實際應(yīng)用中切不可盲目建立，要根據(jù)實際的業(yè)務(wù)邏輯來判斷是否需要建立，如果不是經(jīng)常有類似的查詢，可以不用建立，因為聯(lián)合索引會占用更多的存儲空間和維護(hù)開銷。

　　關(guān)于“MySQL的order by怎么正確使用”的內(nèi)容就介紹到這里了，感謝大家的閱讀。

（編輯：武林網(wǎng)）

上一篇：MySQL與InnoDB下共享鎖與排他鎖實例剖析

下一篇：MySql Group by函數(shù)怎么正確利用