MySQL索引

索引

说说数据库索引的底层数据结构

数据库（不单指MySQL）索引可选的底层数据机构包括：

• 二叉树

• 红黑树

• Hash表

• B-Tree

但mysql索引的底层用的并不是二叉树和红黑树。因为二叉树和红黑树在某些场景下都会暴露出一些缺陷。

首先，二叉树在某些场景下会退化成链表，而链表的查找需要从头部开始遍历，而这就失去了加索引的意义。

不使用红黑树的原因是：红黑树作为底层数据结构在面对在些表数据动辄数百万数千万的场景时，会导致索引树的层数很高。索引从根节点开始查找，而如果我们需要查找的数据在底层的叶子节点上，那么树的高度是多少，就要进行多少次查找，数据存在磁盘上，访问需要进行磁盘IO，这会导致效率过低；

而B+树由B树和索引顺序访问方法演化而来，它是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点，各叶子节点通过指针进行链接。

B+树索引在数据库中的一个特点就是高扇出性，例如在InnoDB存储引擎中，每个页的大小为16KB。在数据库中，B+树的高度一般都在2～4层，这意味着查找某一键值最多只需要2到4次IO操作，这还不错。因为现在一般的磁盘每秒至少可以做100次IO操作，2～4次的IO操作意味着查询时间只需0.02～0.04秒。

为什么B+树的高度一般不会超过4层：

真实一个数据页可以存100条记录， 一个目录页能存1000条数据；4层B+树能存一千万条数据，我们用到的B+树都不会超过4层，每个页内部可以用二分查找更快查找。

InnoDB非聚簇索引情况：

InnoDB 页的大小为 16KB，一般表的主键类型为 INT(占用4个字节)或 BIGINT (占用8个字节)，指针类型也一般为4或8个字节，也就是说一个页 (B+Tree 中的一个节点)中大概存储16KB/(8B+8B)=1K个键值 (因为是估值，为方便计算，这里的K取值为 10^3。也就是说一个深度为3的B+Tree 索引可以维护 10^310^310^3= 1 亿条记录(这里假定一个数据页也存储10A3条行记录数据了)

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在 2~4 层。MySQL的lnnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要 2~4 次磁盘IO操作。

数据页大小：

MyISAM 的数据页大小是固定的，是 1KB，也就是说，MyISAM 存储引擎的数据都是以 1KB 的块进行管理的。

InnoDB 存储引擎的数据页大小是可调的，默认是 16KB。在 MySQL 5.7 版本之前，InnoDB 的数据页大小默认是 8KB。可以通过参数 innodb_page_size 来设置 InnoDB 的数据页大小，取值范围是 4KB、8KB、16KB 和 32KB。

请你说说索引怎么实现的B+树,为什么选这个数据结构？

索引本质上就是通过预排序+树型结构来加快检索的效率，而MySQL中使用InnoDB和MyISAM引擎时都使用了B+树实现索引。

它是一棵平衡多路查找树，是在二叉查找树基础上的改进数据结构。在二叉查找树上查找一个数据时，最坏情况的查找次数为树的深度，当数据量很大时，查询次数可能还是很大，造成大量的磁盘IO，从而影响查询效率；

为了减少磁盘IO的次数，必须降低树的深度，因此在二叉查找树基础上将树改成了多叉加上一些限制条件，就形成了B树；

B+树中所有叶子节点值的总集就是全部关键字集合；B+树为所有叶子节点增加了链接，从而实现了快速的范围查找；

在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。在数据库中，B+树的高度一般都在2～4层，这也就是说查找某一键值的行记录时最多只需要2到4次IO。这很不错，因为当前一般的机械磁盘每秒至少可以做100次IO，2～4次的IO意味着查询时间只需0.02～0.04秒。

什么是覆盖索引？

覆盖索引是指一个索引包含了查询所需的所有字段，而不需要回表查询原始数据。例如，假设我们有一个用户表，包含了用户的id，姓名，年龄，性别等字段，我们想要查询所有年龄大于18岁的用户的姓名和性别，那么我们可以创建一个包含了姓名，性别和年龄的索引，这样就可以直接从索引中获取查询结果，而不需要再访问用户表。

覆盖索引的优点是：

- 减少了磁盘I/O，提高了查询速度

- 减少了内存占用，节省了资源

- 减少了锁的竞争，提高了并发能力,它可以避免回表查询原始数据，从而减少了对数据行的访问和锁定。如果使用索引覆盖，那么只需要对索引记录加锁，而不需要对数据记录加锁。这样就可以减少锁的范围和冲突，提高并发能力

覆盖索引的缺点是：

- 增加了索引的大小，占用了更多的磁盘空间

- 增加了数据更新的开销，因为每次更新数据都需要更新索引

因此，在创建覆盖索引时，我们需要权衡利弊，选择合适的字段和顺序，避免创建过多或过大的索引。

什么是索引下推？

索引下推是指在使用联合索引时，将过滤条件下推到索引层，从而减少回表的次数。

与联合索引不同的是，联合索引只能在查询条件中包含索引的前缀部分时才能生效，而索引下推可以在查询条件中包含任意部分的索引时生效。

在没有使用索引下推的情况下，MySQL的查询：

• 存储引擎读取索引记录；

• 根据索引中的主键值，定位并读取完整的行记录；

• 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用索引下推的情况下，查询过程：

• 存储引擎读取索引记录（不是完整的行记录）；

• 判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；

• 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；

• 存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

覆盖索引和索引下推是两种提高数据库查询效率的技术。覆盖索引是指一个索引包含了查询所需的所有字段，而不需要回表查询原始数据。索引下推是指在使用联合索引时，将过滤条件下推到索引层，从而减少回表的次数。

索引的分类（必会）

(1) 普通索引：最基本的索引，它没有任何限制。

黑马程序员·济南校区(2) 唯一索引：与普通索引类似，不同的就是索引列的值必须唯一，但允许有

空值。如果是组合索引，则列值的组合必须唯一。

(3) 主键索引：它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记

录，不允许有空值，一般用

primary key 来约束。

(4) 联合索引（又叫复合索引）：多个字段上建立的索引，能够加速复合查询

条件的检索。

(5) 全文索引：老版本 MySQL 自带的全文索引只能用于数据库引擎为

MyISAM 的数据表，新版本 MySQL 5.6 的 InnoDB 支持全文索引。默认 MySQL

不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供

一个对应的英文索引表的方式来支持中文。

\3. 索引的底层实现原理

1.索引结构

索引是在 Mysql 的存储引擎(InnoDB,MyISAM)层中实现的, 而不是在服务层实现

的. 所以每种存储引擎的索引都不一定完全相同, 也不是所有的存储引擎都支持所有的索引

类型的, Mysql 目前提供了以下 4 种索引:

B+Tree 索引: 最常见的索引类型, 大部分索引都支持 B+树索引.

Hash 索引: 只有 Memory 引擎支持, 使用场景简单.

R-Tree 索引(空间索引): 空间索引是 MyISAM 引擎的一个特殊索引类型, 主要地理

空间数据, 使用也很少.

S-Full-text(全文索引): 全文索引也是 MyISAM 的一个特殊索引类型, 主要用于全

文索引, InnoDB 从 Mysql5.6 版本开始支持全文索引.

2.B树结构

一个m阶B树 (多路 平衡搜索树) 的具有的特征（或必须满足的条件） 如果根节点不是叶子节点，那么它至少有两个节点。

所有叶子节点位于同一层(高度一致)。

除根节点和叶子节点之外，一个节点至少拥有ceil( m / 2)个节点）。

ceil() :向上取整，ceil(3/2)=ceil(1.5)=2

每个叶子节点包含k-1个元素，ceil(m/2)<= k <=m 。

若m=3, 2<=k<=3, 1<=(k-1)<=2,也就是3阶的树叶子节点包含1~2个元素

拥有k个节点的非叶子节点，包含k-1个元素。

B树的查找 如图是一颗3阶B树，满足B树的特征

每层每个节点上都有信息

3.B+Tree 结构

B+Tree 为 BTree 的变种, B+Tree 与 BTree 的区别:

1.B+Tree 的叶子节点保存所有的 key 信息, 依 key 大小顺序排列.

2.B+Tree 叶子节点元素维护了一个单项链表.

所有的非叶子节点都可以看作是 key 的索引部分.

信息都被包含在叶子节点中

由于 B+Tree 只有叶子节点保存 key 信息, 查询任何 key 都要从 root 走的叶子. 所以

B+Tree 查询效率更稳定.

Mysql 中的 B+Tree

MySql 索引数据结构对经典的 B+Tree 进行了优化, 在原 B+Tree 的基础上, 增加

了一个指向相邻叶子节点的链表指针, 就形成了带有顺序指针的 B+Tree, 提高区间访问的

性能.