思考題

假設有一張訂單表 order，主要包含了主鍵訂單編碼 order_no、訂單狀態(tài) status、提交時間 create_time 等列，并且創(chuàng)建了 status 列索引和 create_time 列索引。此時通過創(chuàng)建時間降序獲取狀態(tài)為 1 的訂單編碼，以下是具體實現(xiàn)代碼：

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

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

解析

status和create_time單獨建索引，在查詢時只會遍歷status索引對數(shù)據(jù)進行過濾，不會用到create_time列索引，將符合條件的數(shù)據(jù)返回到server層，在server對數(shù)據(jù)通過快排算法進行排序，Extra列會出現(xiàn)file sort；

應該利用索引的有序性，在status和create_time列建立聯(lián)合索引，這樣根據(jù)status過濾后的數(shù)據(jù)就是按照create_time排好序的，避免在server層排序

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

filesort是通過相應的排序算法將取得的數(shù)據(jù)在內存中進行排序，如果內存不夠則會使用磁盤文件作為輔助。雖然在一些場景中，filesort并不是特別消耗性能，但是我們可以避免filesort就盡量避免。

阿里巴巴MySQL規(guī)范

【推薦】 如果有 order by 的場景，請注意利用索引的有序性。 order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現(xiàn) file_sort 的情況，影響查詢性能。

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

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

范圍查詢-基礎

講聯(lián)合索引，一定要扯最左匹配!

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

a = 1
a = 1 and b = 2

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

b= 2 and a =1

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

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)都可以，重點要的是將區(qū)分度高的字段放在前面，區(qū)分度低的字段放后面。像性別、狀態(tài)這種字段區(qū)分度就很低，我們一般放后面。

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

阿里巴巴Java 開發(fā)手冊

【強制】 在 varchar 字段上建立索引時，必須指定索引長度，沒必要對全字段建立索引，根據(jù) 實際文本區(qū)分度決定索引長度。

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

場景二: a > 1 and b = 2

如果sql為

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

如何建立索引?

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

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

如果對(b,a)建立索引那么兩個字段都能用上，優(yōu)化器會幫我們調整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范圍查詢優(yōu)化的文章就介紹到這了,更多相關MySQL范圍查詢優(yōu)化內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論