mysql複合索引+範圍搜索中索引順序的問題？

02-02

是這樣的，表裡邊有2個欄位`DistriType` varchar(20)和`DistriButTime` int(11)，我建了一個聯合索引KEY `DistriType` (`DistriType`,`DistriButTime`)，執行了一個查詢計劃SELECT * FROM zt_customerinfo WHERE DistriButTime&<1417327703 AND DistriType="xxx"看到搜索結果應用到了聯合索引且掃了364行，如果將聯合索引的順序換一下KEY `DistriType` (`DistriButTime`,`DistriType`) 再執行explain看到掃表2800多行，相當於只應用了單索引DistriButTime，請問這是什麼原因~

這裡涉及兩個知識點：最左前綴left-prefix，列基數cardinality

最左前綴，可以理解為在複合索引中，越是左邊越好

因為分別對A,B,C三個列建聯合索引index，實際上是建立3個索引，每個索引都包含A

A,B,C

A,B

列基數，是指它所容納的所有非重複值的個數，可以理解為 distinct（某列）後的結果

比如某個列包含值1,3,7,4,7,3，那麼它的基數就是4了

列基數越大，重複值越少，需要建索引，因為如果某列的值都是重複的就沒必要建索引了

是(`DistriType`,`DistriButTime`) 這樣建？還是這樣建 (`DistriButTime`,`DistriType`) ？

count一下就知道了

count(distinct DistriType) 與 count(distinct DistriButTime）比較

猜測肯定是前者&<後者，所以聯合索引把DistriTyp放左邊更優

再舉一個例子，一本字典，記錄了所有的姓名

分別用兩個欄位，第一列：姓第二列：名

很明顯 count（distinct 姓）要比 count(distinct 名) 要少

因為百家姓，姓氏只有一百個，而名字可以自由組合很多個

劉燁

劉德華

劉爾達

張震

張學友

所以如果要建立聯合索引，肯定是 key index ( 姓，名)，先排姓氏，再排名字

注意：以下說法非常的不嚴謹，只對於初學者可以先（暫時）這樣理解。

為了簡化題主的問題，我用一張簡單的表作為例子，3個欄位：id, name, age。

以下為數據（由於重名，有2個叫張三的人和2個叫李四的人）：

id | name | age 1 | zhang three | 20 2 | zhang three | 21 3 | li four | 20 4 | li four | 21

如果建立索引：(name, age)，在索引文件中，它會先按照name排序，在name相同時，再按照age排序。

大概的順序如下：

line no | id | name | age 1 | 3 | li four | 20 2 | 4 | li four | 21 3 | 1 | zhang three | 20 4 | 2 | zhang three | 21

此時執行：

SELECT * from table_name where name = li four and age &< 21

然後，直接二分查找(li four, *)可以確定下邊界，二分查找(li four, 21)可以確定上邊界。2界之間的就是最終結果。

反之，

如果建立索引(age, name)，在索引文件中，會先按照age排序，age相同時，再按照name排序。

大概的順序如下：

line no | id | name | age 1 | 3 | li four | 20 2 | 1 | zhang three | 20 3 | 4 | li four | 21 4 | 2 | zhang three | 21

此時執行：

SELECT * from table_name where name = li four and age &< 21

只能先二分查找(*, *)確定下邊界，二分查找(21, *)確定上邊界。2界之間的結果還需要再遍歷一遍，查找name = li four的行。

推薦一篇美團技術團隊的文章：MySQL索引原理及慢查詢優化

範圍索引導致等值精確匹配失效。應該把等值精確匹配DistriType放在索引第一列。

索引匹配到範圍查詢，就會終止索引，所以實際上只按照DistriButTime進行了匹配，所以導致命中的數據行很大，查詢所以才會變慢。

因為你的語句 AND DistriType="xxx"

慕課網上面講的，好像跟上面說的剛還想反，是我理解錯了，還是咋地？？？http://www.imooc.com/video/3997