打開order by的大門，一探究竟《死磕MySQL系列 十二》

在日常開發(fā)工作中，你一定會經(jīng)常遇到要根據(jù)指定字段進(jìn)行排序的需求。

這時，你的SQL語句類似這樣。

select id,phone,code from evt_sms where phone like '13020%' order by id desc limit 10

1

這個SQL的邏輯是十分清晰明了，但其內(nèi)部的執(zhí)行原理你知多少。

接下來，本期文章將帶你打開order by的大門一探究竟。

本期所有結(jié)論都基于MySQL8.0.26版本

文章目錄

最新文章

一、常見的Extra幾個信息

二、文件排序

三、文件排序很慢，還有其它辦法嗎

四、優(yōu)化文件排序

五、總結(jié)

最新文章

字符串可以這樣加索引，你知嗎？《死磕MySQL系列 七》

無法復(fù)現(xiàn)的“慢”SQL《死磕MySQL系列 八》

什么？還在用delete刪除數(shù)據(jù)《死磕MySQL系列 九》

MySQL統(tǒng)計總數(shù)就用count(*)，別花里胡哨的《死磕MySQL系列 十》

文章總目錄

一、常見的Extra幾個信息

在MySQL中想看一條SQL的性能不僅僅看是否用上了索引，還要看Extra中的內(nèi)容，以下內(nèi)容來自官方文檔，給你最準(zhǔn)確的學(xué)習(xí)資料。

using index

根據(jù)索引樹可直接檢索列信息，無需額外的操作來讀取實際的行。

索引列即為查詢列，也為條件列。

using index condition

下面這條語句name為普通索引，age無索引。

select * from table where name = ? and age = ?

索引下推是在MySQL5.6及以后的版本出現(xiàn)的。

之前的查詢過程是，先根據(jù)name在存儲引擎中獲取數(shù)據(jù)，然后在根據(jù)age在server層進(jìn)行過濾。

在有了索引下推之后，查詢過程是根據(jù)name、age在存儲引擎獲取數(shù)據(jù)，返回對應(yīng)的數(shù)據(jù)，不再到server層進(jìn)行過濾。

當(dāng)你使用Explain分析SQL語句時，如果出現(xiàn)了using index condition那就是使用了索引下推，索引下推是在組合索引的情況出現(xiàn)幾率最大的。

using index for group_by

只查索引列，對索引列使用了group by

explain select phone from evt_sms where phone = "13054125874" group by phone;

1

using where

查詢的列被索引覆蓋，并且where篩選條件是索引列之一，但不是索引的前導(dǎo)列，Extra中為Using where; Using index，

意味著無法直接通過索引查找來查詢到符合條件的數(shù)據(jù)

查詢的列被索引覆蓋，并且where篩選條件是索引列前導(dǎo)列的一個范圍，同樣意味著無法直接通過索引查找查詢到符合條件的數(shù)據(jù)

zero limit

這個估計很少有小伙伴知道，就是你的SQL語句查詢數(shù)量為limit 0

using temporary

使用了臨時表，一般在使用group by、order by時會遇到。

這個也是本文即將要聊的話題。

using filesort

一般在使用group by、order by時會遇到，排序過程在內(nèi)存中完成

Backward index scan

對索引列使用了降序操作

這里只列舉了最常見的幾個信息，MySQL官方文檔中對Extra的解析大概有37個，感興趣的可以去看看，后期咔咔也會逐步完善這塊內(nèi)容。

二、文件排序

由于是在一些統(tǒng)計、排序的業(yè)務(wù)中會經(jīng)常見到Extra中出現(xiàn)using filesort的信息。

在MySQL8.0.26版本中對一個沒有索引的列進(jìn)行排序在Extra中顯示using filesort。在低版本中需要你進(jìn)行試驗在什么情況下會出現(xiàn)。

在Extra中顯示的using filesort表示的就是排序，MySQL會給每個線程分配一塊內(nèi)存用于排序，也被稱之為sort_buffer。這期文章和下期文章會牽扯到很多名詞，記得自己整理一下哈！

再看這條語句

那么這條SQL執(zhí)行的具體流程是什么呢？

1、初始化sort_buffer，放入字段phone、code字段

2、在phone的索引樹找到主鍵值

3、根據(jù)主鍵值到主鍵索引樹中檢索處phone、code對應(yīng)字段的值，再存儲sort_buffer中

4、繼續(xù)從phone取下一個主鍵值

5、重復(fù)第三、第四，直到不滿足phone = 條件為止

6、在sort_buffer中的數(shù)據(jù)按照字段phone做快排

7、按照快排的結(jié)果取出前10行返回改客戶端即可

問題：所有的排序都是在內(nèi)存中進(jìn)行的？

當(dāng)然不是，任何內(nèi)存都不是無限制的，是否在內(nèi)存中排序取決于MySQL參數(shù)sort_buffer_sort。

在MySQL8.0.26版本中這個值大小默認(rèn)為256kb。

當(dāng)需要排序的數(shù)據(jù)量大于256kb的閥值時，則會利用臨時文件進(jìn)行輔助排序，也就是常說的歸并排序算法實現(xiàn)。

sort_buffer_size跟需要臨時文件的個數(shù)成正比，如果sort_buffer_size越小則臨時文件的數(shù)量就越多。

如何查看一個排序是否使用了臨時文件，這個答案就交給大家來實現(xiàn)，版本不一致會導(dǎo)致很多結(jié)果都不同。

問題：你知道歸并排序是如何實現(xiàn)的嗎？

現(xiàn)在你知道了如果排序的數(shù)據(jù)大于sort_buffer_size會使用臨時文件排序，這種排序使用的就是歸并排序的思想，接下來讓我們看看具體的流程是怎么樣的。

1、把需要排序的數(shù)據(jù)分割，分割成每塊數(shù)據(jù)都可以存放到sort_buufer中

2、對每塊數(shù)據(jù)在sort_buufer中進(jìn)行排序，排序好后，寫入某個臨時文件

3、當(dāng)所有的數(shù)據(jù)都寫入臨時文件后，這時對于每個臨時文件內(nèi)部來說是有序的，但對于所有臨時文件是無序的，所以還需要合并數(shù)據(jù)

4、假設(shè)現(xiàn)在存在tmp1和tmp2兩個臨時文件，這時分別從tmp1、tmp2讀入部分?jǐn)?shù)據(jù)到內(nèi)存

5、假設(shè)從tmp1和tmp2中分別讀入[0-5]的數(shù)據(jù)，然后分別使用tmp1[0]、tmp2[0] 進(jìn)行對比，一直到tmp1[5]、tmp2[5]，這樣兩兩比較就可以把tmp1、tmp2合并為一個文件。經(jīng)過幾輪下來所有分割的數(shù)據(jù)都會合并為一個有序的大文件

三、文件排序很慢，還有其它辦法嗎

通過上面的案例，如果排序的數(shù)據(jù)量非常大則會超過sort_buffer_size的最大值，就只能使用文件排序，文件排序涉及了多次的文件合并是非常消耗性能的。

在上文你有沒有發(fā)現(xiàn)一個細(xì)節(jié)，SQL中只需要排序code字段，但把phone字段也加到了sort_buufer中了。

這樣單行的數(shù)據(jù)大小無形中就增大了，這樣內(nèi)存中能夠存放的行數(shù)就減少了，需要分割成多個臨時文件，排序性能會很差，那么有沒有其它方案可以解決這種問題呢？

答案是肯定有的，就是接下來要聊的rowid排序。

先看一個參數(shù)max_length_for_sort_data

默認(rèn)max_length_for_sort_data的大小為4096字節(jié)，假設(shè)現(xiàn)在要排序的數(shù)據(jù)非常多，我們可以修改這個參數(shù)讓其使用rowid的算法。

MySQL中專門控制用戶排序的行數(shù)據(jù)長度的參數(shù)，如果單行的數(shù)據(jù)長度超過了這個值，則MySQL會自動更換為rowid算法。

rowid排序的思想就是把不需要的數(shù)據(jù)不放到sort_buufer中，讓sort_buffer中只存放需要排序的字段。

問題：如果你是設(shè)計者，你會存放那些字段

假設(shè)現(xiàn)在存放只需要排序的字段，排序很快完成了，拿到排序后的數(shù)據(jù)結(jié)果你應(yīng)該怎么辦呢？你已經(jīng)無從下手了。

因此，你可以把主鍵ID的值也存放到sort_buufer中，當(dāng)排序完成后通過ID回表即可得到排序后的數(shù)據(jù)。

執(zhí)行流程

試想一下，這個執(zhí)行流程其實跟文件排序的流程大差不差。

只是存放到sort_buufer中的字段變?yōu)樾枰判虻淖侄渭由现麈I字段。

接著在sort_buufer中按照排序字段進(jìn)行排序

最后再遍歷排序結(jié)果，取需要的行數(shù)，并使用id進(jìn)行回表一次，查出你需要的列即可。

注意點

這不是說使用了rowid的排序算法后就不使用臨時文件排序了，不是這樣的。

使用rowid只是存放到sort_buffer中的數(shù)據(jù)多個，若需要排序的數(shù)據(jù)很多還是需要使用臨時文件的。

四、優(yōu)化文件排序

如果MySQL發(fā)現(xiàn)sort_buufer內(nèi)存太小，會影響排序效率，才會采用rowid排序算法，使用rowid算法的好處就是sort_buffer中可以一次排序更多的行，缺點就是需要回表。

在MySQL中如果內(nèi)存夠用，就多利用內(nèi)存，盡量減少磁盤訪問。所有rowid的算法不會被優(yōu)先選擇，因為回表會造成過的磁盤讀。

不是所有的order by語句，都需要排序操作的，上面分析的兩種排序算法的由來都是因為原來的數(shù)據(jù)都是無序的。

問題：什么是有序的？

看過了索引那一期文章后，你現(xiàn)在應(yīng)該知道以下兩點。

索引本身具有順序性，在進(jìn)行范圍查詢時，獲取的數(shù)據(jù)已經(jīng)排好了序，從而避免服務(wù)器再次排序和建立臨時表的問題。

索引的底層實現(xiàn)本身具有順序性，通過磁盤預(yù)讀使得在磁盤上對數(shù)據(jù)的訪問大致呈順序的尋址，也就是將隨機(jī)的I/O變?yōu)轫樞騃/O。

問題：如何防止進(jìn)行排序

現(xiàn)在你應(yīng)該知道答案了，就是給需要排序的列創(chuàng)建聯(lián)合索引。

現(xiàn)在給phone、code建立一個聯(lián)合索引，對應(yīng)的SQL語句如下

alter table evt_sms add index idx_phone_code (phone,code);

1

那么執(zhí)行同樣的語句就不會使用排序操作了，接下來看一下執(zhí)行流程

執(zhí)行流程

1、從索引(phone,code)找到滿足phone='123456’的記錄，取出phone、code的值，作為結(jié)果集的一部分直接返回

3、從索引(phone、code)取下一個記錄，同樣取出phone、code的值，作為結(jié)果集的一部分直接返回

4、重復(fù)步驟2直到查出1000行數(shù)據(jù)，或者不滿足查詢條件為止

五、總結(jié)

order by沒有用到索引時，執(zhí)行計劃中會出現(xiàn)using filesort

using filesort根據(jù)參數(shù)sort_buffer_size的值來決定使用需要使用臨時文件

max_length_for_sort_data參數(shù)決定是否使用rowid算法，若放入sort_buffer的每行數(shù)據(jù)大于設(shè)置的值就會使用rowid算法

現(xiàn)在你應(yīng)該知道了rowid排序只是把需要排序的字段和主鍵ID放入sort_buffer中，而文件排序則是把查詢的所有字段全部放入sort_buffer中。

還有rowid會多造成一次回表操作，這個你也要知道。

最后提到了優(yōu)化order by語句，這里提到了建立覆蓋索引，利用索引的有序性直接返回結(jié)果不用進(jìn)行排序。

這里并不是提倡大家在實際生產(chǎn)環(huán)境中盲目建立，而是根據(jù)具體業(yè)務(wù)情況，如果數(shù)據(jù)非常的小在內(nèi)存排序是非?？斓?。并且覆蓋索引會占用更多的存儲空間和維護(hù)開銷。

堅持學(xué)習(xí)、堅持寫作、堅持分享是咔咔從業(yè)以來所秉持的信念。愿文章在偌大的互聯(lián)網(wǎng)上能給你帶來一點幫助，我是咔咔，下期見。

MySQL SQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。