上一篇文章我們通過(guò)實(shí)操得出了一個(gè)結(jié)論：在單個(gè)索引的情況下，列的散列度越高，創(chuàng)建索引后的查詢效果越明顯。那么在查詢條件中有多個(gè)的情況下，我們要怎么創(chuàng)建索引呢？答案是創(chuàng)建聯(lián)合索引。首先我們來(lái)看一個(gè)查詢語(yǔ)句：

• 查詢user_name 和user_password

SELECT user_name,user_password from sys_user where user_name = 'zhangsan0' and user_password = '1234560'

上面是user_name 和 user_password 聯(lián)合查詢。在沒(méi)有使用任何索引的情況下：

image.png

 EXPLAIN SELECT user_name,user_password from sys_user where user_name = 'zhangsan0' and user_password = '1234560'

image.png

• user_name 單獨(dú)創(chuàng)建索引

SELECT user_name,user_password from sys_user where user_name = 'zhangsan0' and user_password = '1234560'

SELECT user_name,user_password from sys_user where user_password = '1234560' and user_name = 'zhangsan0'

上面的語(yǔ)句是在給user_name 創(chuàng)建了索引的前提下執(zhí)行的，發(fā)現(xiàn)兩條sql 語(yǔ)句執(zhí)行的結(jié)果查詢效果相差無(wú)幾，基本沒(méi)有任何差別。說(shuō)明：在 and 的情況下，條件的前后順序在有索引的情況下，對(duì)查詢結(jié)果沒(méi)有影響。那是因?yàn)閙ysql 的底層對(duì)sql 語(yǔ)句在執(zhí)行的時(shí)候做了優(yōu)化，具體我們后面分析。

查詢結(jié)果.png

• explain

image.png

image.png

SELECT user_name,user_password from sys_user where user_password = '1234560'

結(jié)果是肯定的，因?yàn)樯厦孢@個(gè)語(yǔ)句沒(méi)有命中索引，沒(méi)有使用到索引。

• user_password 創(chuàng)建普通索引
  上面查詢語(yǔ)句我們指定，where 條件中有兩個(gè)user_name = xxxxx and user_password = xxxx 。為了解決使用 user_password 作為條件查詢慢的問(wèn)題，我們?cè)贋?user_password 創(chuàng)建索引

image.png

• 執(zhí)行查詢

  
  
  image.png

SELECT user_name,user_password from sys_user where user_password = '1234560'

image.png

SELECT user_name,user_password from sys_user where user_name = 'zhangsan0' and user_password = '1234560'

根據(jù)上面兩條sql 語(yǔ)句執(zhí)行的情況看，在查詢時(shí)都命中了索引。

• 多條件查詢創(chuàng)建聯(lián)合索引

上面 使用 user_name 和 user_password 作為查詢條件時(shí)，創(chuàng)建了兩個(gè)索引，那么在mysql 底層就維護(hù)了兩個(gè)B+tree 。如果在多個(gè)查詢條件中，沒(méi)有用到 單個(gè)條件查詢時(shí)，也就是說(shuō) user_name或者user_password 沒(méi)有單獨(dú)作為查詢條件使用，建議使用聯(lián)合索引。

• 創(chuàng)建聯(lián)合索引idx_user_name_user_password
  
  聯(lián)合索引.png

注意：聯(lián)合索引， user_name 在 user_password 的左邊，也就是在構(gòu)建B+tree 時(shí)，在一個(gè)節(jié)點(diǎn)中user_name 在 user_password 的左邊。懵逼的話，沒(méi)關(guān)系，我們先看案例

• 查詢條件中命中user_name。使用聯(lián)合索引
  
  image.png

SELECT user_name,user_password FROM sys_user where user_name = 'zhangsan0'

• explain

  
  
  image.png

如圖，我們?cè)诓樵冎惺褂昧寺?lián)合索引。

• 查詢條件使用 user_password ，不能使用聯(lián)合索引

SELECT user_name,user_password FROM sys_user where user_password = '1234560'

image.png

• explain

  
  
  未命中索引.png

上面查詢結(jié)果顯示我們?cè)诓樵儠r(shí)，未使用到聯(lián)合索引。原因就在一個(gè)B+tree 中，user_name 為被查詢，無(wú)法判斷后面節(jié)點(diǎn)的走向，雖然建立了索引，但是無(wú)法使用。后面我們?cè)诜治?B+tree 的數(shù)據(jù)結(jié)構(gòu)時(shí)在詳細(xì)說(shuō)明。于是就有了 聯(lián)合索引最左匹配原則。啥意思，接下來(lái)就來(lái)說(shuō)一下。

• 能夠使用到聯(lián)合索引的情況

SELECT user_name,user_password FROM sys_user where user_name = 'zhangsan0'
SELECT user_name,user_password FROM sys_user where user_name = 'zhangsan0' and user_password = '1234560' 
SELECT user_name,user_password FROM sys_user where user_password = '1234560' and user_name = 'zhangsan0'

ok，上面都是可以使用到聯(lián)合索引的情況，都一個(gè)共同的查詢條件user_name。注意，我們創(chuàng)建的索引的時(shí)候，user_name 在user_password 的左邊。聯(lián)合索引的最左匹配原則就是 在查詢條件中 必須要命中最左邊的字段（user_name）或者說(shuō)最左邊字段所在的索引。

• 注意
  最左匹配原則是指的命中索引中最左的那個(gè)字段就ok，不是要求 字段（user_name）一定要在 sql 語(yǔ)句的最左。下面的這兩句在命中索引的時(shí)候是一樣的。

SELECT user_name,user_password FROM sys_user where user_password = '1234560' and user_name = 'zhangsan0' 

SELECT user_name,user_password FROM sys_user where   user_name = 'zhangsan0' and  user_password = '1234560'

• 無(wú)法命中聯(lián)合索引的情況

SELECT user_name,user_password FROM sys_user where user_password = '1234560'
SELECT user_name,user_password FROM sys_user where user_name = 'zhangsan0' OR user_password = '1234560' 
SELECT user_name,user_password FROM sys_user where user_password = '1234560' or user_name = 'zhangsan0'

SELECT user_name,user_password FROM sys_user where user_password = '1234560' 未使用user_name 自然無(wú)法命中。
SELECT user_name,user_password FROM sys_user where user_name = 'zhangsan0' OR user_password = '1234560' or 不走索引的原因，暫時(shí)未明白，如有讀者明白，還望不吝賜教。