mysql索引最左匹配原则理解以及常见的sql使用的索引情况的实测

朱雀 2020-11-21 18:36 840阅读 1赞

# 一、常见例子 #

mysql索引最左匹配原则主要是针对复合索引（联合索引）来说的，比如你在字段 a,b,c 建立了一个联合索引 index\_abc

ALTER TABLE `test` 
    ADD INDEX `index_abc`(`a`,`b`, `c`) USING BTREE;

1.  explain select \* from test where a<10 ; \# 用索引  
    ![在这里插入图片描述][20200627183231476.png]
2.  explain select \* from test where a<10 and b <10; \# 用索引  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dldGVyX2Ryb3A_size_16_color_FFFFFF_t_70]
3.  explain select \* from test where a<10 and b <10 and c<10; \# 用索引

![在这里插入图片描述][20200627183329388.png]  
能不能将 a，b出现顺序换一下，a，b，c出现顺序换一下

1.  explain select \* from test where b<10 and a <10; \# 用索引

![在这里插入图片描述][20200627183349382.png]  
5. explain select \* from test where b<10 and a <10 and c<10; \# 用索引

![在这里插入图片描述][202006271834045.png]

1.  explain select \* from test where b<10 and c <10;\# 用不到索引

![在这里插入图片描述][20200627183416960.png]

1.  explain select \* from test where a<10 and c <10; \# a 能用到索引， c用不到  
    ![在这里插入图片描述][20200627183519766.png]
2.  explain select \* from test where a=1001 \#用到索引  
    ![在这里插入图片描述][20200627183605365.png]
3.  explain select \* from test where a=1001 and b=1002 \#用到索引  
    ![在这里插入图片描述][20200627183620934.png]
4.  explain select \* from test where b=1002 and a=1001 \# 用到索引，mysql 优化器会将 sql优化成：explain select \* from test where a=1001 and b=1002

![在这里插入图片描述][20200627183635278.png]

1.  explain select \* from test where a=1001 and c=1003 \# a 能用到索引，c用不到  
    ![在这里插入图片描述][20200627183649508.png]
2.  explain select \* from test where c=1003 \# 用不到索引  
    ![在这里插入图片描述][2020062718370246.png]
3.  explain select \* from test where b=1002 \# 用不到索引  
    ![在这里插入图片描述][20200627183717493.png]

# 二、总结 #

如果有复合索引 index\_abc(a,b,c)，且是select \* 则

1.  只要保证 a 字段能够出现在where条件后，不论 a 出现的先后顺序，就一定能用索引 （适用于查询条件是 “=” ）
2.  a 是查询条件的第一个条件则一定能用索引（适用于查询条件是 “=”，"<" , “>”）
3.  查询条件只有 b 或 c 或 b c 则不能使用索引

一般在面试的时候查询条件是“=” 且前边都是select \* 如果不是这样则上边的结论不成立

[20200627183231476.png]: /images/1605983789385.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dldGVyX2Ryb3A_size_16_color_FFFFFF_t_70]: /images/1605983778844.png
[20200627183329388.png]: /images/1605983751175.png
[20200627183349382.png]: /images/1605983738913.png
[202006271834045.png]: /images/1605983726463.png
[20200627183416960.png]: /images/1605983711020.png
[20200627183519766.png]: /images/1605983696655.png
[20200627183605365.png]: /images/1605983683735.png
[20200627183620934.png]: /images/1605983670656.png
[20200627183635278.png]: /images/1605983655993.png
[20200627183649508.png]: /images/1605983643180.png
[2020062718370246.png]: /images/1605983631662.png
[20200627183717493.png]: /images/1605983617174.png