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

如果sql為

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

如何建立索引?

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

此題正確答法是，(a,b,c)或者(c,b,a)或者(b,a,c)都可以，重點(diǎn)要的是將區(qū)分度高的字段放在前面，區(qū)分度低的字段放后面。像性別、狀態(tài)這種字段區(qū)分度就很低，我們一般放后面。

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

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

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

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

場景二: a > 1 and b = 2

如果sql為

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

如何建立索引?

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

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

如果對(duì)(b,a)建立索引那么兩個(gè)字段都能用上，優(yōu)化器會(huì)幫我們調(diào)整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)都可以，要結(jié)合具體情況具體分析。

拓展一下

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;

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

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

如何建立索引？

對(duì)(a)建立索引，因?yàn)閍的值是一個(gè)范圍，這個(gè)范圍內(nèi)b值是無序的，沒有必要對(duì)(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; 

如何建立索引？

還是對(duì)(a，b)建立索引，因?yàn)镮N在這里可以視為等值引用，不會(huì)中止索引匹配，所以還是(a,b)!

拓展一下

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

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

總結(jié)

盡可能將范圍查詢轉(zhuǎn)換成“等值”查詢，如 “a>1 and a<5 and b>10” 可以寫成“a in (1,2,3,4,5) and b > 10”，然后設(shè)置索引為 idx(a,b)。

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

參考鏈接1

參考鏈接2

到此這篇關(guān)于MySQL范圍查詢優(yōu)化的文章就介紹到這了,更多相關(guān)MySQL范圍查詢優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！