深入理解Mysql索引底层数据结构与算法

渝洲
194 阅读8分钟

深入理解Mysql索引底层数据结构与算法

一、索引是什么

- 索引是帮助MySQL高效获取数据结构的 排好序 的 数据结构。

1.1 索引的优缺点：

优点：

  • 使用索引可以大大加快数据的查找速度（大大减少查找的数据量）
  • 通过唯一索引，可以保证数据表中的数据的唯一性。

缺点：

  • 创建索引和维护索引需要消耗许多时间，当对表中的数据进行增删改的时候，如果数据有索引，那么索引树也需要动态的修改，会降低SQL执行效率。
  • 索引需要使用物理文件存储，也会消耗一定空间。

二、索引数据结构

2.1、二叉树

二叉查找树(BST，Binary Search Tree)，也叫二叉排序树，在二叉树的基础上需要满足：任意节点的左子树上所有节点值不大于根节点的值，任意节点的右子树上所有节点值不小于根节点的值

image.png

数据库有序索引（链式存储）

image.png

缺点:

  • 顺序存储可能会浪费空间(在非完全二叉树的时候)，但是读取某个指定的节点的时候效率比较高
  • 链式存储相对二叉树比较大的时候浪费空间较少，但是读取某个指定节点的时候效率偏低

2.2、红黑树（二叉平衡树）

红黑树，是一种二叉平衡树，但在每个结点上增加一个存储位表示结点的颜色，可以是Red或Black。 通过任何一条从根到叶子的路径上各个结点着色方式的限制，红黑树确保没有一条路径会比其他路径长出俩倍，因而是接近平衡的

红黑树的性质

  1. 每个结点不是红色就是黑色
  2. 根节点是黑色的
  3. 如果一个节点是红色的，则它的两个孩子结点是黑色的
  4. 对于每个结点，从该结点到其所有后代叶结点的简单路径上，均包含相同数目的黑色结点
  5. 每个叶子结点都是黑色的(此处的叶子结点指的是空结点)

image.png

缺点

  • 数据在一定程度上回会产生偏移，造成树得高度特别高，造成多次io，耗费系统性能（树太高）

2.3、Hash

  • 对索引的key进行一次hash计算就可以定位出数据存储的位置
  • 很多时候Hash索引要比B+ 树索引更高效
  • 仅能满足 “=”，“IN”，不支持范围查询
  • hash冲突问题

image.png

2.4、B- Tree

  • 叶节点具有相同的深度，叶节点的指针为空
  • 所有索引元素不重复（数据不冗余）
  • 节点中的数据索引从左到右递增排列

image.png

2.5、B+ Tree

B+Tree 是**（B- Tree变种）**

  • 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
  • 叶子节点包含所有索引字段
  • 叶子节点用双向指针连接，提高区间访问的性能

image.png

三、MySQL数据结构

InnoDB

  • frm: 数据结构
  • ibd: 数据 和 索引

MyISAM

  • MYD: 数据
  • MYI： 索引
  • ibd: 数据 和 索引

image.png

四、MySQL存储引擎的索引实现

4.1 MyISAM存储引擎索引实现（非聚集索引）

  • 索引文件和数据文件分开离。
  • 叶子节点只存放数据文件所在的文件地址。

image.png

4.2 InnoDB存储引擎（聚集索引）

  • 表数据文件本身就是按B+ Tree组织的一个索引结构文件
  • 聚集索引-叶子节点包含了完整的数据记录

image.png

五、面试点

5.1 千万级数据表如何用B+树索引快速查找

mysql 根节点|叶节点 大概大小16K （SHOW GLOBAL STATUS like 'Innodb_page_size'）

image.png

为什么mysql页文件默认16K？

假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针)

那么一颗高度为2的B+树能存储的数据为：1170 * 16=18720条，一颗高度为3的B+树能存储的数据为：1170 * 1170 * 16=21902400（千万级条）

B+树 可以存放千万级数据, 树高度为3，根节点放到内存里面属于常驻内存,在查询根节点会使用二分算法或者折半算法 在内存中快速定位, 最耗时的就是从磁盘吧叶子节点数据加载到内存的一点点时间。

mysql高一点版本 把所有非叶子节点都放在内存里面

5.2 为什么建议InnoDB表必须建一个主键 , 并且推荐使用整型的自增主键？

必须要用B+ Tree组织

如果自带主键 就使用主键和组织索引结构 ， 如果没有设置主键，会从第一列(clo1)一直往后找 直到一列数据是唯一的 作为主键, 如果都没有唯一列 ，mysql 会自动生成一个隐藏列(rowId) 生成唯一id来存储 来组织。

使用整形自增 好处， 因为是排好序的 有序的排列 根节点来 比大小 效率高，

如果是用UUID, 排序的话需要 吧UUID转ASC码 一个一个的比较， 虽然不影响很大的查询效率 ，而且uuid 占用空间比较大

5.3 自增

如果是无序增加 ，由于mysql是 会进行排序 如果先添加10 在添加 9 会先让10那个节点添加，如果节点存满了 会对节点进行分裂 在添加一个新节点 ，然后在进行平衡。

有序增加: 如果先添加 9 在添加10 节点存满了 会直接添加一个新节点 ，然后在进行平衡。 效率快很多

范围查找

因为排好序 叶子节点范围操作速度非常快 ， 因为叶子节点用指针连接，提高区间访问的性能。

5.4 聚集索引和 非聚集索引的 查询速度？

  • 聚集索引 查询叶子节点就读取到了数据
  • 非聚集索引 数据(MYD)和索引(MYI)不在同一个文件，索引查到磁盘文件地址 需要回表查询数据

image.png

5.5 为什么非主键索引结构叶子节点存储的是主键值？

  • 只有一个聚集索引，一般是那主键，没有主键会把rowId当做聚集索引
  • 聚集索引-叶子节点包含了完整的数据记录
  • 为什么二级索引不保存数据: 一致性和节省存储空间
  • 二级索引 相当于也是非聚集索引|稀疏索引 ,需要拿到主键id 根据主键id回表聚集索引 获取值

image.png

5.6 联合索引的底层存储结构长什么样？

1. 联合索引数据结构图

如下图所示联合索引的数据结构, 通过name，age，position三个字典进行一个联合索引，构建B+树索引结构。

image.png

2. 联合索引是如何进行排序的

B+树是一个排好序的数据结构，联合索引是如何进行排序的。遵循最左原则，根据字段的先后顺序做排序。

  • 首先比较name字段，Bill ,HanMeimei, Lilei, 这三个进行字母比较，根据首字母可以将排序排列出来 image.png
  • 当第一个字段无法判断大小时，这时候根据第二个age字段进行排序, 30 < 31 <32 image.png
  • 当第二个字段无法比较出来后，根据第三个字段进行排序，依次类推 字母转化Ascll进行大小的比较, d<m

image.png

如果联合索引的几个字段都相同的情况下，这时使用主键索引作比较，主键索引是唯一的，根据这个大小来进行排序

3. 联合索引查询特点

下图有一个联合索引表

EXPLAIN SELECT * FROM employees WHERE name='Bill' and age = 31;

EXPLAIN SELECT * FROM employees WHERE age = 31 and position='dev';

EXPLAIN SELECT * FROM employees WHERE position='manager';

上面3条sql语句，哪条语句会走索引?

第一条语句会走索引. 为什么第一条语句走索引? 根据联合索引最左原则，联合索引的排序的优先级为 name > age position，而且是一环套一环的。 根据这个特性可以看出，第一条sql语句首先通过name字段进行一个索引快速查找，然后根据name=Bill过滤后的基础，在查询age=31的值

第二条sql就无法满足此条件，他没有经过name首次排序查询的前置条件，是无法进行age排序查询，当只有age = 30 and position='dev'此条件时，是无法通过索引树进行定位，只能走全表扫描，才能获取这两条记录

第三条sql和第二条一样

avatar
文章
阅读
粉丝