什么是索引覆盖？回表？下推？为什么插入数据会造成页分裂？

最新推荐文章于 2025-02-27 10:20:45 发布

大华Coding

最新推荐文章于 2025-02-27 10:20:45 发布

阅读量971

点赞数 20

分类专栏：数据库从入门到大师【MySQL】文章标签：数据库

本文链接：https://blog.youkuaiyun.com/m0_73337964/article/details/145438539

版权

数据库从入门到大师【MySQL】专栏收录该内容

11 篇文章

订阅专栏

文章目录

1.索引数据结构

什么是索引？为什么索引可以加快查询？

索引的数据结构？

B+树和B树、红黑树有什么区别？为什么选择B+树，作为索引数据结构？

索引是一种能提高查询速度的数据结构。

MySQL有哪些索引类型？

按数据结构分类：B+树索引、Hash索引、全文索引。
按物理存储分类：聚簇索引、二级索引。
按字段特性分类：主键索引、唯一索引、普通索引、前缀索引。
按字段个数分类：单列索引、联合索引。

InnoDB的索引数据结构是什么？（InnoDB是支持B+树和FULLTEXT索引的）

在数据组织形式上，B+树的非叶子节点只存放索引和指向子节点的指针，叶子节点存储索引和行数据。并且所有叶子节点都是通过指针相连，形成一个双向链表，支持快速的顺序访问和范围查询。树的高度平衡，稳定性好。

B+树和B树有什么区别呢?

B树的每个节点都存有索引和数据，数据的分散可能导致查询性能不稳定，而B+树只有叶子节点存放索引和数据，更适合磁盘存储和范围查询，非叶子节点只需要存储索引，可以容纳更多的键值，减少磁盘IO操作。
B树每个节点之间没有双向链表连接，B+树节点之间有双向链表连接，在查询时无需返回到根节点，更加适合范围查询。
B树查询数据的时候使用中序遍历，有时需要多次磁盘IO，查询不稳定，B+树查询数据时，都需要从根节点到叶子节点，查询稳定。

1.1 为什么MySQL使用B+树？

平衡二叉树

红黑树

跳表

B+是多叉树，形状更加矮胖，每次查询数据时IO性能都差不多，但是二叉平衡树、红黑树都是二叉树，在数据很多的时候，高度很高，磁盘IO次数更多，而且还需要额外的执行平衡操作。

跳表需要更多的内存去维护多级索引，而且跳表在极端情况下会退化为单链表，平衡性差，而数据库查询需要一个稳定的查询时间。

B树的叶子节点和非叶子节点都存储了索引和数据，可能内存装不下，B+树的非叶子节点只有索引，而且节点之间也由双向链表连接，适合范围查询。

B+树

B+树是多叉树，在数据存储上，叶子节点存储索引和数据，非叶子节点存放索引和指向子节点的指针。
节点之间都是由双向链表连接的，能够快速的进行顺序访问和范围查询。
所有的叶子节点都是在同一层的，查询数据的时候有较好的稳定性，而且B+树大概都是3_{4层，所需要的磁盘IO也就3}4次。

1.2 为什么不使用红黑树？B树？哈希表？

红黑树是二叉树在同等数据量的情况下，高度更高，那么在查询数据的时候就需要更多的磁盘IO次数；B树的索引和数据，都存储到节点中，叶子节点之间没有双向链表，那么范围查询就不太适合，要通过中序遍历查询性能差；哈希表不能进行范围查询和排序，如果存在重复的键值，还有可能造成哈希冲突。B+树的优点就是叶子节点之间通过了双向链表连接，有利于进行范围查询，但是在增加删除操作的时候，由于要进行树的合并分裂的调整操作，就会造成更多的随机IO操作，影响性能。

2.索引覆盖？回表？下推？

创建联合索引之后，进行查询时索引覆盖还是回表，主要是看SELECT的列。

注：以下采用sakila数据库做演示https://www.sjkjc.com/sakila/

EXPLAIN SELECT actor_id, last_name FROM actor WHERE last_name = 'WAHLBERG';

在这里插入图片描述

可以看到在Extra字段为using index，表示为索引覆盖。为什么呢？

因为触发索引覆盖的条件就是所要查询的字段为这个索引本身或主键值。如果有其他字段就需要就行回表查询。

EXPLAIN SELECT actor_id,first_name, last_name FROM actor WHERE last_name = 'WAHLBERG';

在这里插入图片描述

索引下推是MySQL5.6的优化机制，默认是开启的，如果条件判断字段，在二级索引B+树里，就会下推到InnoDB存储引擎层来过滤，过滤完的记录，才会回表，相比没有索引下推的时候，可以减少回表次数。

3.关于插入造成的页分裂

为什么会出现数据页的分裂呢?

如果使用非自增主键就比如使用uuid，那么每次插入数据的索引值都是随机的，那么就有可能插入其它的数据页。但是使用自增主键的话，会进行顺序递增依次将数据插入到叶子节点最右边的节点里，除非页满了，否则不会调整页面，那么就能更高效的使用数据页。

就比如一个数据页里的主键数据(这里表示主键不是递增顺序)如下，

此时，我在添加一个新的数据，主键为3，那么就会进行调整数据，进行分页合并等操作。

所以，在设计数据表的时候最好设计为自增主键。

参考资料

1、https://xiaolincoding.com/mysql/index/why_index_chose_bpuls_tree.html#%E6%80%8E%E6%A0%B7%E7%9A%84%E7%B4%A2%E5%BC%95%E7%9A%84%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84%E6%98%AF%E5%A5%BD%E7%9A%84

2、https://developer.aliyun.com/article/1415520

3、https://blog.youkuaiyun.com/weixin_46204056/article/details/125595803

4、https://www.cnblogs.com/zhuchangwu/p/14041410.html
.youkuaiyun.com/weixin_46204056/article/details/125595803

4、https://www.cnblogs.com/zhuchangwu/p/14041410.html

原文链接：https://mp.weixin.qq.com/s/SDOxaQykyP7eU1GsBZ7jtg?token=1566802147&lang=zh_CN