MySQL範圍查詢優化的場景範例詳解

思考題

假設有一張訂單表 order，主要包含了主鍵訂單編碼 order_no、訂單狀態 status、提交時間 create_time 等列，並且建立了 status 列索引和 create_time 列索引。此時通過建立時間降序獲取狀態為 1 的訂單編碼，以下是具體實現程式碼：

select order_no from order where status =1 order by create_time desc;

你知道其中的問題所在嗎？我們又該如何優化？

解析

status和create_time單獨建索引，在查詢時只會遍歷status索引對資料進行過濾，不會用到create_time列索引，將符合條件的資料返回到server層，在server對資料通過快排演演算法進行排序，Extra列會出現file sort；

應該利用索引的有序性，在status和create_time列建立聯合索引，這樣根據status過濾後的資料就是按照create_time排好序的，避免在server層排序

對的，為了避免檔案排序的發生。因為查詢時我們只能用到status索引，如果要對create_time進行排序，則需要使用檔案排序filesort。

filesort是通過相應的排序演演算法將取得的資料在記憶體中進行排序，如果記憶體不夠則會使用磁碟檔案作為輔助。雖然在一些場景中，filesort並不是特別消耗效能，但是我們可以避免filesort就儘量避免。

阿里巴巴MySQL規範

【推薦】 如果有 order by 的場景，請注意利用索引的有序性。 order by 最後的欄位是組合索引的一部分，並且放在索引組合順序的最後，避免出現 file_sort 的情況，影響查詢效能。

正例： where a=? and b=? order by c; 索引： a_b_c

反例： 索引如果存在範圍查詢， 那麼索引有序性無法利用，如： WHERE a>10 ORDER BY b; 索引 a_b 無 法排序

範圍查詢-基礎

講聯合索引，一定要扯最左匹配!

最左匹配 所謂最左原則指的就是如果你的 SQL 語句中用到了聯合索引中的最左邊的索引，那麼這條 SQL 語句就可以利用這個聯合索引去進行匹配，值得注意的是，當遇到範圍查詢(>、<、between、like)就會停止匹配。 假設，我們對(a,b)欄位建立一個索引，也就是說，你where後條件為

a = 1
a = 1 and b = 2

是可以匹配索引的。但是要注意的是~你執行

b= 2 and a =1

也是能匹配到索引的，因為Mysql有優化器會自動調整a,b的順序與索引順序一致。 相反的，你執行

b = 2

就匹配不到索引了。 而你對(a,b,c,d)建立索引,where後條件為

a = 1 and b = 2 and c > 3 and d = 4

那麼，a,b,c三個欄位能用到索引，而d就匹配不到。因為遇到了範圍查詢！

場景一: a = 1 and b = 2 and c = 3

如果sql為

SELECT * FROM table WHERE a = 1 and b = 2 and c = 3; 

如何建立索引?

如果此題回答為對(a,b,c)建立索引，那都可以回去等通知了。

此題正確答法是，(a,b,c)或者(c,b,a)或者(b,a,c)都可以，重點要的是將區分度高的欄位放在前面，區分度低的欄位放後面。像性別、狀態這種欄位區分度就很低，我們一般放後面。

例如假設區分度由大到小為b,a,c。那麼我們就對(b,a,c)建立索引。在執行sql的時候，優化器會 幫我們調整where後a,b,c的順序，讓我們用上索引。

阿里巴巴Java 開發手冊

【強制】 在 varchar 欄位上建立索引時，必須指定索引長度，沒必要對全欄位建立索引，根據 實際文字區分度決定索引長度。

說明： 索引的長度與區分度是一對矛盾體，一般對字串型別資料，長度為 20 的索引，區分度會高達 90%以上，可以使用 count(distinct left(列名, 索引長度))/count(*)的區分度來確定。

場景二: a > 1 and b = 2

如果sql為

SELECT * FROM table WHERE a > 1 and b = 2; 

如何建立索引?

如果此題回答為對(a,b)建立索引，那都可以回去等通知了。

此題正確答法是，對(b,a)建立索引。如果你建立的是(a,b)索引，那麼只有a欄位能用得上索引，畢竟最左匹配原則遇到範圍查詢就停止匹配。

如果對(b,a)建立索引那麼兩個欄位都能用上，優化器會幫我們調整where後a,b的順序，讓我們用上索引。

場景三:a > 1 and b = 2 and c > 3

如果sql為

SELECT * FROM `table` WHERE a > 1 and b = 2 and c > 3; 

如何建立索引? 此題回答也是不一定，(b,a)或者(b,c)都可以，要結合具體情況具體分析。

拓展一下

SELECT * FROM `table` WHERE a = 1 and b = 2 and c > 3; 

怎麼建索引？嗯，大家一定都懂了！

場景四: a > 1 ORDER BY b

SELECT * FROM `table` WHERE a = 1 ORDER BY b;

如何建立索引？ 這還需要想？一看就是對(a,b)建索引，當a = 1的時候，b相對有序，可以避免再次排序！ 那麼

SELECT * FROM `table` WHERE a > 1 ORDER BY b; 

如何建立索引？

對(a)建立索引，因為a的值是一個範圍，這個範圍內b值是無序的，沒有必要對(a,b)建立索引。

拓展一下

SELECT * FROM `table` WHERE a = 1 AND b = 2 AND c > 3 ORDER BY c;

怎麼建索引?

場景五: a IN (1,2,3) and b > 1

SELECT * FROM `table` WHERE a IN (1,2,3) and b > 1; 

如何建立索引？

還是對(a，b)建立索引，因為IN在這裡可以視為等值參照，不會中止索引匹配，所以還是(a,b)!

拓展一下

SELECT * FROM `table` WHERE a = 1 AND b IN (1,2,3) AND c > 3 ORDER BY c;

如何建立索引？此時c排序是用不到索引的。

總結

儘可能將範圍查詢轉換成“等值”查詢，如 “a>1 and a<5 and b>10” 可以寫成“a in (1,2,3,4,5) and b > 10”，然後設定索引為 idx(a,b)。

將“等值”條件放在最左邊，按最左匹配就可以命中索引。

參考連結1

參考連結2

到此這篇關於MySQL範圍查詢優化的文章就介紹到這了,更多相關MySQL範圍查詢優化內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！