B+树索引的使用

最新推荐文章于 2022-10-29 13:36:51 发布

封闭货车_

最新推荐文章于 2022-10-29 13:36:51 发布

阅读量282

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_47129394/article/details/105997750

版权

每个索引都对应一棵B+树，B+树分为好多层，最下边一层是叶子节点，其余的是内节点。所有用户记录都存储在B+树的叶子节点，所有目录项记录都存储在内节点。
InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。
我们可以为自己感兴趣的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。
通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory（页目录），所以在这些页面中的查找非常快。

索引的代价

时间上的：每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引
空间上的：每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成
所以说，一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。

B+树索引适用的条件

假设表格：person_info表中有idx_name_birthday_phone_number索引
1、全值匹配
建立的idx_name_birthday_phone_number索引

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';

如果包含的3个列在这个查询语句中都展现出来了。查询过程为：

因为B+树的数据页和记录先是按照name列的值进行排序的，所以先可以很快定位name列的值是Ashburn的记录位置。
在name列相同的记录里又是按照birthday列的值进行排序的，所以在name列的值是Ashburn的记录里又可以快速定位birthday列的值是’1990-09-27’的记录。
如果很不幸，name和birthday列的值都是相同的，那记录是按照phone_number列的值排序的，所以联合索引中的三个列都可能被用到。
2、匹配左边的列
查询条件是最左边的一列，或者多个左边的列
如：

SELECT * FROM person_info WHERE name = 'Ashburn';

`SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';

`
如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列

匹配列前缀
对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的，比方说我们想查询名字以’As’开头的记录，那就可以这么写查询语句：

SELECT * FROM person_info WHERE name LIKE 'As%';

而查询字符中间的值或者后缀就不能用该字符串列索引快速查找，只能全表查找，但是后缀可以采用逆序排序，从而转换为匹配前缀
匹配范围值
对于联合索引来说只有查找最左边的索引进行范围查找时用得到B+树索引，如：索引：idx_name_birthday_phone_number来说

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';

只有根据name查找范围时用B+树索引，因为对于不同的name来说birthday并不是按照顺序进行排序的，所以不能使用B+树索引进行查找
精确匹配某一列并范围匹配另外一列
如果左边的列是精确查找，则右边的列可以进行范围查找

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1980-01-01' AND phone_number > '15100000000';

用于排序
如：

SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

先按照name排序，然后时birthday，phone_number ，可见与idx_name_birthday_phone_number 索引建立的原理相同，这个B+树就是按照上述顺序排好序的，可见使用索引就会非常简单
ORDER BY的子句后边的列的顺序也必须按照索引列的顺序给出
当联合索引左边列的值为常量，也可以使用后边的列进行排序

SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;

不可以使用索引进行排序的几种情况
1、ASC、DESC混用
对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序。
2、WHERE子句中出现非排序使用到的索引列
如：

SELECT * FROM person_info WHERE country = 'China' ORDER BY name LIMIT 10;

3、排序列包含非同一个索引的列
如：

SELECT * FROM person_info ORDER BY name, country LIMIT 10;

name和country并不属于一个联合索引中的列，所以无法使用索引进行排序
4、排序列使用了复杂的表达式
如：

SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;

使用UPPER函数修饰过的列就不是单独的列啦，这样就无法使用索引进行排序啦。
用于分组
和使用B+树索引进行排序是一个道理，分组列的顺序也需要和索引列的顺序一致，也可以只使用索引列中左边的列进行分组

回表的代价
如：

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

使用索引idx_name_birthday_phone_number的查询有这么两个特点：

会使用到两个B+树索引，一个二级索引，一个聚簇索引。
访问二级索引使用顺序I/O，访问聚簇索引使用随机I/O。

需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式
覆盖索引
为了彻底告别回表操作带来的性能损耗，我们建议：最好在查询列表里只包含索引列

如何挑选索引

只为用于搜索、排序或分组的列创建索引

考虑列的基数

最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好

索引列的类型尽量小

数据类型越小，在查询时进行的比较操作越快（这是CPU层次的东东）
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

索引字符串值的前缀

B+树索引中的记录需要把该列的完整字符串存储起来，而且字符串越长，在索引中占用的存储空间越大。
如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

只对字符串的前几个字符进行索引也就是说在二级索引的记录中只保留字符串前几个字符
**name(10)**就表示在建立的B+树索引中只保留记录的前10个字符的编码，这种只索引字符串值的前缀的策略是我们非常鼓励的，尤其是在字符串类型能存储的字符比较多的时候。
使用索引列前缀的方式无法支持使用索引排序

让索引列在比较表达式中单独出现

如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。

主键插入顺序

让主键具有AUTO_INCREMENT，让存储引擎自己为表生成主键，而不是我们手动插入

冗余和重复索引

总结

B+树索引在空间和时间上都有代价，所以没事儿别瞎建索引。
B+树索引适用于下边这些情况：
全值匹配
匹配左边的列
匹配范围值
精确匹配某一列并范围匹配另外一列
用于排序
用于分组

在使用索引时需要注意下边这些事项：

只为用于搜索、排序或分组的列创建索引
为列的基数大的列创建索引
索引列的类型尽量小
可以只对字符串值的前缀建立索引
只有索引列在比较表达式中单独出现才可以适用索引
为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。
定位并删除表中的重复和冗余索引
尽量使用覆盖索引进行查询，避免回表带来的性能损耗。