mysql最左前缀原则学习笔记，in也可以走索引

ゝ一世哀愁。 2023-07-11 06:18 177阅读 0赞

通过我的测试得出的结论：  
如果索引是（a,b,c）那么不只是a,ab,abc可以走索引，像是bc.acb,bca都可以走索引，or < > between and甚至是**in都也是可以走到索引的。**（我在很多文章看到说in不能走索引，包括在面试中，面试官也觉得不会走索引的，这个也能跟mysql的版本有关系，下来再去研究下）  
在google我找到这样的解释  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70]  
百度上说：  
1，  
![在这里插入图片描述][20200304184229750.png]  
2，  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 1]  
mysql版本：8.0.18  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 2]  
sql：

建立索引：  
ALTER TABLE student ADD INDEX index\_student (NAME,weight,height);

验证最左优先原则：  
执行：EXPLAIN SELECT \* FROM student WHERE NAME = ‘xiaohong’  
可以看到走了索引  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 3]  
EXPLAIN SELECT \* FROM student WHERE NAME = ‘xiaohong’ AND weight > 120  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 4]  
这样肯定能走索引，再来看一个ba组合  
EXPLAIN SELECT NAME,weight,gender FROM student WHERE weight > 120 AND NAME = ‘xiaohong’  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 5]  
这样要可能走索引，但是这个并不满足最左优先原则  
还有cba这样的组合也能走索引 ，有点疑惑，不知道是不是innodb优化了sql  
EXPLAIN SELECT NAME,weight,gender FROM student WHERE height = 110 AND weight > 120 AND NAME IN (‘xiaohong’)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 6]  
但是cb bc是不能走索引的  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 7]

最左优先说会从左向又一次匹配知道遇到> < between and就会停止匹配，

EXPLAIN SELECT \* FROM student WHERE NAME = ‘xiaohong’ OR height IN (120,110) OR weight > 120  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 8]  
竟然这样也可以执行到索引。  
单独使用in，  
执行EXPLAIN SELECT \* FROM student WHERE NAME IN (‘xiaohong’)  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 9]

结论：  
如果索引是（a,b,c）那么不只是a,ab,abc可以走索引，像是bc.acb,bca都可以走索引，or < > between and甚至是**in都也是可以走到索引的。**（我在很多文章看到说in不能走索引，包括在面试中，面试官也觉得不会走索引的

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200304184047913.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz,size_16,color_FFFFFF,t_70
[20200304184229750.png]: https://img-blog.csdnimg.cn/20200304184229750.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200304184404111.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200304172035333.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200304164338650.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200304174415773.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200307100213460.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200307102237999.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200307102450234.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200304174551200.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20200307095348448.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3MDExNDIz,size_16,color_FFFFFF,t_70