B+Tree 索引生效规则与场景再现

一、简介

我们使用术语“B-Tree”，是因为MySQL很多地方都使用了该关键字，不过底层存储殷勤也可能使用不同的存储结构，例如：NDB集群存储殷勤内部实际上使用了T-Tree结构存储的这种索引，即使其名字是BTree;InnoDB则使用的是B+Tree。

每一个叶子节点到根节点的距离是一样的，跟二叉树不同，因为他会有很多叶子节点

二、生效规则

B-Tree索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找

索引生效的情况：

1. 匹配最左前缀
2. 全值匹配
3. 匹配列前缀
4. 匹配范围值
5. 精确匹配某一列并范围匹配另外一列

三、场景再现

举个栗子：有联合索引 ABC

查询索引是否生效 explain select * from tableA where A = ‘1’

场景一：

select * from tableA where A = '1’
会不会命中索引？
答案：会命中索引，因为他是匹配最左前缀【最左匹配原则】,上述第1种生效情况

场景二：

select * from tableA where B = '1’
会不会命中索引？
答案：不会命中索引，因为按照最左匹配原则来说，最左边的列都没有，后边的就不知道是索引了

场景三：

select * from tableA where A = ‘1’ and B=‘2’ and C='3’
会不会命中索引？
答案：会命中索引，匹配的是全值匹配索引，1+2+3 这个组成了一个索引。上述第2种生效情况

场景四

select * from tableA where A like '1%'
会不会命中索引？
答案：会命中索引，匹配列前缀，上述第3种生效情况

场景五

select * from tableA where A like '%1%'
会不会命中索引？
答案：不会命中索引，这是个全匹配，所以不会匹配索引。
备注：像like查询这种，左边千万不要打%，这样效率会很低

场景六

select * from tableA where A >‘1’ and A<'5’
会不会命中索引？
答案：会命中索引，上述第4中索引生效情况。
备注：mysql 的范围值是40%，超出这个范围则不会命中索引。比如表里有1-100 行数据，40%则是 40行，如果超出这个值，则不会命 中索引，从开始到结束的位置不能超过40%。如果只有一个条件的话，< 匹配的是从现在查找的值到最开始的位置，> 匹配的是从现在查找的位置到结束的位置

场景七

select * from tableA where A =‘1’ and B<‘5’ and C='3’
会命中几个索引？
答案：2个，范围查找会使用索引， A=‘1’ 使用了索引，B<‘5’ 使用了索引，而范围查找后面的则不会使用索引
备注：如果查询中某个列使用了范围查询，则右边的列都无法使用索引查询

场景八

select * from tableA where A=‘1’ and B<'5’
会不会命中索引？
答案：会命中索引，上述第5个索引，精确匹配其中一列，范围匹配另外一列。

场景九

select * from tableA where A=‘1’ and C<'5’
会不会命中索引？
答案：会命中一个索引，则是A=‘1’ 这个索引，C<'5’不会命中索引

场景切换

如果不用联合索引，把 B 和 C 都单独拿出来做索引。

场景一

select * from tableA where A=‘1’ and B='2’
会不会命中索引，会命中几个索引
答案：实际会命中一个索引，一个简单查询只有一个索引列生效

四、B-Tree的限制

B-Tree索引的限制：
1.如果不是按照索引的最左列开始查找，则无法使用索引
2.不能跳过索引中的列
3.如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询

五、总结：

基本都是围绕着最左原则匹配的。以上都是B-Tree索引
组合索引 列的顺序 是非常重要的东西，匹配的列越多，性能越高，是因为索引的函数会更少、更精确