mysql-索引原理

HILY
318 阅读7分钟

mysql索引底层原理探究：索引存储结构，B+树，多值索引，虚拟列，虚拟索引

1. B+树

MySQL 中最常用的索引的数据结构是 B+ 树。B+树有如下特点：

  • 在 B+ 树中，所有数据记录节点都是按照键值的大小存放在同一层的叶子节点上，而非叶子结点只存储key的信息，这样可以大大减少每个节点的存储的key的数量，降低B+ 树的高度
  • B+ 树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针。
  • B+ 树的层级更少：相较于 B 树 B+ 每个非叶子节点存储的关键字数更多，树的层级更少所以查询数据更快
  • B+ 树查询速度更稳定：B+ 所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定;
  • B+ 树天然具备排序功能：B+ 树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高。
  • B+ 树全节点遍历更快：B+ 树遍历整棵树只需要遍历所有的叶子节点即可，，而不需要像 B 树一样需要对每一层进行遍历，这有利于数据库做全表扫描

Mysql B+树示例图

叶子节点的数据页就是实际存放数据页的地方，且数据页之间是通过双向链表进行连接的。

MySQL 在存储数据的时候是以数据页为最小单位的，且数据在数据页中的存储是连续的，数据页中的数据是按照主键排序的（没有主键是由 MySQL自己维护的 ROW_ID 来排序的），数据页和数据页之间是通过双向链表来关联的，数据与数据时间是通过单向链表来关联的。

mysql一种新的存储结构—索引页：索引页中记录的是每页数据页的页号和该数据页中最小的主键的记录，也就是说最小主键和数据页号不是单纯的维护在主键目录中了，而是演变成了索引页，索引页和数据页类似，一张不够存就分裂到下一张。

对于如何确定在哪个索引页上，MySQL 同时也设计出了用于维护索引页的数据结构，其实也还叫索引页，只不过他们是在不同的层级，类似下面这样子的：

也就是说维护索引页的索引页是在真正存储记录和数据页的索引页的上一层，现在如果你想查找 id=20 的这条记录，那就是从最上层的索引页开始查找，通过二分法查找，很快就能够定位到 id=20 s这条记录是在索引页 2 上，然后到就索引页 2 上面查找，接着就是和之前一样了（注意，索引页中的记录也是通过单向链表连接的），根据各个最小的主键能够定位到 id=20 是在数据页5上。

索引页的分层： 当索引页记录也太多问题，索引页会向上继续分裂

实际上这就是一颗 B+ 树的结构，这也是数据在磁盘中真正存储的物理结构。B+树的特性是什么呢？B+树，也是二叉搜索树的一种，但是他的数据仅仅存储在叶子节点（在这里就是数据页），像这种索引页+数据页组成的组成的B+树就是聚簇索引。聚簇索引是 MySQL 基于主键索引结构创建的。索引页和数据页示例图如下：

回表：根据非主键索引查询到的结果并没有查找的字段值，此时就需要再次根据主键从聚簇索引的根节点开始查找，这样再次查找到的记录才是完成的。

2. 多值索引

  1. 概述

MySQL 8.0新增的一种索引类型：多值索引；从MySQL 8.0.17开始，InnoDB支持多值索引。多值索引是在存储值数组的列上定义的二级索引。“普通”索引对每个数据记录有一个索引记录（1:1）。对于单个数据记录（N:1），多值索引可以有多个索引记录。多值索引旨在为 JSON 数组建立索引。

多值索引可以在CREATE TABLE、ALTER TABLE或CREATE INDEX语句中创建多值索引。这要求使用CAST(… AS … ARRAY)索引定义，该定义将JSON数组中相同类型的标量值转换为SQL数据类型数组。然后，使用SQL数据类型数组中的值透明地生成一个虚拟列。最后，在虚拟列上创建一个功能索引（也称为虚拟索引）。是在SQL数据类型数组的值的虚拟列上定义的功能索引，该索引构成了多值索引。

  1. 创建方式

为json格式的custinfo中的数组属性zipcode，创建一个多值索引
建表时创建：
CREATE TABLE customers (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
custinfo JSON,
INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) )
);

单独添加：
ALTER TABLE customers ADD INDEX comp(id, modified,(CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

  1. 如何使用多值索引

WHERE子句中指定以下功能，优化程序将使用多值索引来获取记录

* MEMBER OF() * JSON_CONTAINS() * JSON_OVERLAPS()

  1. 多值索引的限制

具有多值键部分的索引不支持排序，因此不能用作主键。出于相同的原因，不能使用ASC或DESC 关键字定义多值索引。

  • 多值索引不能是覆盖索引。

  • 多值索引的每条记录的最大值数由可以在单个撤消日志页上存储的数据量决定，即65221字节（64K减去315字节的开销），这意味着最大总数键值的长度也是65221字节。键的最大数量取决于各种因素，这会阻止定义特定的限制。测试显示了一个多值索引，例如，每个记录允许多达1604个整数键

3.虚拟列和虚拟索引

虚拟列 Generated (Virtual) Columns：Mysql 5.7 中推出了一个非常实用的功能 ，支持两种Generated Column，即Virtual Generated Column和Stored Generated Column，前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与Virtual Column相比并没有优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。

一般情况下，都使用Virtual Generated Column，这也是MySQL默认的方式

虚拟列的作用：能对 where 条件 上使用函数等原因使索引失效 如使用函数 就可以对这个 字段和函数建立一个虚拟类和虚拟索引提高效率

为什么要使用虚拟列：

（1）可以为虚拟列创建索引（oracle为其创建function index）

（2）可以搜集虚拟列的统计信息statistics，为CBO提供一定的采样分析。

（3）可以在where 后面使用虚拟列作为选择条件

（4）只在一处定义，不存储多余数据，查询是动态生成。

使用方式：

ALTER TABLE 表名 ADD COLUMN 虚拟列名 类型  GENERATED 指定虚拟列的值/虚拟列表达式;

为虚拟列创建的索引就是虚拟索引：

ALTER TABLE 表名 ADD INDEX 虚拟索引名(虚拟列) USING BTREE COMMENT '虚拟索引';
avatar
文章
阅读
粉丝