深入理解Mysql的B+Tree索引原理

索引是什么？

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。

索引的工作机制是怎样的？

当进行匹配的时候，会把字符串转换成ascll码，如abc变成97 98 99，然后从左往右一个字符一个字符进行对比。所以在sql查询中使用like %a 时候索引会失效，因为%表示全匹配，如果已经全匹配就不需要索引，还不如直接全表扫描。

3、最少空间原则

前面已经说过，当关键字占用的空间越小，则每个节点保存的关键字个数就越多，每次加载进内存的关键字个数就越多，检索效率就越高。

联合索引：

单列索引：节点中的关键字[name]

联合索引：节点中的关键字[name, phoneNum]

可以把单列索引看成特殊的联合索引，联合索引的比较也是根据最左匹配原则。

联合索引列的选择原则：

（1） 经常用的列优先（最左匹配原则）

（2） 离散度高的列优先（离散度高原则）

（3） 宽度小的列优先，（最少空间原则）

下面简单举例平时经常会遇到的问题：

如，平时经常使用的查询sql如下：

Select * from users where name = ?

Select * from users where name = ? and pahOneNum= ?

为了加快检索速度，为上面的查询sql创建索引如下：

Create index idx_name on users(name)

Create index idx_name_phoneNum on users(name, phoneNum)

在上面解决方案中，根据最左匹配原则，idx_name为冗余索引， where name = ?同样可以利用索引idx_name_phoneNum进行检索。冗余索引会增减维护B+TREE平衡时的性能消耗，并且占用磁盘空间。

覆盖索引：

如果查询的列，通过索引项的信息可直接返回，则该索引称之为查询SQL的覆盖索引。覆盖索引可以提高查询的效率。

下面通过例子说明覆盖索引。

表：teacher

索引：PK（id）， key（name， phoneNum）， unique（teacherNo）

下面哪些sql使用到了覆盖索引？

Select teacherNo from teacher where teacherNo = ?：使用到了，检索到teacherNo 时候，可以直接将索引中的teacherNo 值返回，不需要进入数据区。

Select id，teacherNo from teacher where teacherNo = ?：使用到了，辅助索引的叶子节点保存了主索引的值，所以检索到辅助索引的叶子节点的时候就可以之间返回id。

Select name，phoneNum from teacher where teacherNo = ?：没有用到

Select phoneNum from teacher where name = ?, 使用到了。

知道了覆盖索引，就知道了为什么sql中要求尽量不要使用select *，要写明具体要查询的字段，一个原因就是，这样在使用到覆盖索引的情况下，不需要进入到数据区，数据就能直接返回，提升了查询效率。

通过前面的学习，我们可以很容易的明白如下一下结论：

1、索引列的数据长度满足业务的情况下能少则少。

2、表中的索引并不是越多越好。

3、Where 条件中，like 9%， like %9%， like%9，三种方式都用不到索引。后两种方式对于索引是无效的。第一种9%是不确定的，决定于列的离散型，结论上讲可以用到，如果发现离散情况特别差的情况下，查询优化器觉得走索引查询性能更差，还不如全表扫描。

4、Where条件中 NOT IN 无法使用索引

5、多用指定查询，只返回自己想要的列，少用select *。

6、查询条件中使用函数，索引将会失效，这和列的离散型有关，一旦使用到函数，函数具有不确定性。

7、联合索引中，如果不是按照索引最左列开始查找，无法使用索引。

8、对联合索引精确匹配最左前列并范围匹配另一列，可以使用到索引。

9、联合索引中，如果查询有某个列的范围查询，其右边所有的列都无法使用索引。

推荐Mysql教程《Mysql教程》

以上就是深入理解Mysql的B+Tree索引原理的详细内容，更多请关注其它相关文章！