MySQL索引原理篇：揭秘InnoDB索引底层数据结构

xuxiancheng
162 阅读13分钟

前言

索引本质是一种加速访问的数据结构，可以类比成一本书的目录；查找数据时，先从目录中找到对应章节的页码，再通过页码找到目标内容。本文将带领大家搞清楚以下几个问题：索引是什么、有什么作用、MySQL索引数据结构、索引创建和优化的原则，索引失效的场景。

索引是什么？

image.png

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目 录，能加快数据库的查询速度。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能 存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

索引分类

按数据结构分类

  1. B+树索引：这是MySQL中最常见的索引类型，适用于全键值、键值范围和最左前缀查找。B+树索引在非叶子节点上仅存储键值，所有数据记录均存储在叶子节点上，并且数据是按照顺序排列的
  2. 哈希索引：基于哈希算法实现，适用于精确匹配查询，不支持范围查询。哈希索引的检索效率非常高，但由于其特殊性，也带来了一些限制和弊端。
  3. 全文索引：用于查找文本中的关键词，通常使用倒排索引实现。

按物理存储分类

  1. 聚集索引：按照每张表的主键构造一颗B+树，叶子节点存放的是整张表的行记录数据。InnoDB表要求必须有聚集索引
  2. 非聚集索引：叶子节点存放的是数据主键，而不是行数据。使用非聚集索引进行查找时，需要先查找到主键值，然后再到聚集索引中进行查找

按字段特性分类

  1. 主键索引：建立在主键上的索引，一张数据表只能有一个主键索引，索引列值不允许有空值
  2. 唯一索引：建立在UNIQUE字段上的索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突
  3. 普通索引：建立在普通字段上的索引
  4. 全文索引：用于查找文本中的关键词

按字段个数分类

  1. 单列索引：建立在单个列上的索引
  2. 联合索引（复合索引、组合索引） ：建立在多个列上的索引，使用联合索引时要遵循最左前缀匹配原则

索引优势和劣势

优势

  • 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
    • 被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂 一些。
    • 如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。

劣势

  • 索引会占据磁盘空间。
  • 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅 要保存数据，还要维护索引文件。

索引数据结构

基本需求

索引的数据结构，至少需要支持两种最常用的查询需求：

  • 等值查询：根据某个值查找数据，比如： select * from t_user where age=76;
  • 范围查询：根据某个范围区间查找数据，比如： select * from t_user where age>=76 and age<=86;
  • 排序
  • 分组

同时需要考虑时间和空间因素：

  • 性价比高 在执行时间方面，我们希望通过索引，查询数据的时间尽可能小。
  • 在存储空间方面，我们希望索引不要消耗太多的内存空间和磁盘空间。

常见数据结构

Hash table

Hash表，常见的数据结构之一。 我们使用Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等 值查询时效率很高，时间复杂度为O(1)； 但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

使用场景： 对查询并发要求很高，K/V内存数据库，缓存。

二叉查找树

二叉树特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。

二叉树的检索复杂度和树高相关：理想状态下效率可以达到O(logn)

image.png

极端情况下，二叉查找树会构建成为单向链表 = 查找全表扫描。 对磁盘不友好（一旦变成了全表扫描，磁盘io将是极其沉重）。

红黑树

平衡二叉树是采用二分法思维，平衡二叉查找树除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很 高、右子树很矮的情况，避免了二叉查找树退化成链表的情况。但是由于红黑树是二叉树，数据量较大时，树的高度变大，导致查询数据时，需要随机访问多次才能找到数据。

使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(log2n)。

image.png

平衡二叉树存在的问题 ：

时间复杂度和树高相关：树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘 IO 操作 【瓶颈】。 磁盘每次寻道时间为10ms，在表数据量大时，对响应时间要求高的场景下，查询性能就会出 现瓶颈。 举例：1百万的数据量，log2n约等于20次磁盘IO，时间20*10=0.2s 。

B树：改进二叉树，为多叉树

想要减少耗时的IO操作，就要尽量降低树的高度。每个节点存储多个元素，在每个节点尽可能多的存储 数据。每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。

主要特点：

  1. B树的节点中存储着多个元素，每个节点内有多个分叉。
  2. 节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。
  3. 父节点当中的元素不会出现在子节点中。
  4. 所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

image.png

优点：

  • 磁盘IO次数会大大减少。
  • 比较是在内存中进行的，比较的耗时可以忽略不计。
  • B树的高度相比于平衡二叉树会大幅缩小，所以使用B树构建索引可以很好的提升查询的效率。

缺点：

  • B树不支持范围查询的快速查找：如果我们想要查找15和26之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。
  • 空间占用较大：如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

B+树：改进B树，非叶子节点不存储数据

在B树基础上，MySQL在B树的基础上继续改造，使用B+树构建索引。B+树和B树最主要的区别在于非 叶子节点是否存储数据的问题。

  • B树：非叶子节点和叶子节点都会存储数据。
  • B+树：只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最 底层的叶子节点形成了一个双向有序链表。

image.png

等值查询：假如我们查询值等于15的数据。查询路径磁盘块1->磁盘块2->磁盘块5。

范围查询：假如我们想要查找15和26之间的数据。

  • 查找路径是磁盘块1->磁盘块2->磁盘块5。
  • 首先查找值等于15的数据，将值等于15的数据缓存到结果集【三次磁盘IO】。
  • 查找到15之后，底层的叶子节点是一个有序列表，我们从磁盘块5，键值15开始向后遍历筛选所有符合筛选条件的数据。
  • 第四次磁盘IO：根据磁盘5后继指针到磁盘中寻址定位到磁盘块6，将磁盘6加载到内存中，在内存 中从头遍历比较，15<17<26，15<26<=26，将data缓存到结果集。

优点：

  • 继承了B树的优点【多叉树的优点】
  • 保证等值和范围查询的快速查找
  • MySQL的索引就采用了B+树的数据结构

索引创建原则

    1. 表记录很少不需创建索引 ：索引是要有存储的开销

  • 2.一个表的索引个数不能过多：

    • 空间：浪费空间。每个索引都是一个索引树，占据大量的磁盘空间。
    • 时间：更新（插入/Delete/Update）变慢。需要更新所有的索引树。

  • 3.频繁更新的字段不建议作为索引：频繁更新的字段引发频繁的页分裂和页合并，性能消耗比较高。

  • 4.区分度低的字段，不建议建索引。

  • 5.在InnoDB存储引擎中，主键索引建议使用自增的长整型，避免使用很长的字段。

    • 使用自增的数字类型，是因为B+tree是顺序排列的。
    • 主键索引树一个页节点是16K，主键字段越长，一个页可存储的数据量就会越少，比较臃肿，查询时尤其是区间查询时磁盘IO次数会增多。辅助索引树上叶子节点存储的数据是主键值，主键值越长，一个页可存储的数据量就会越少，查询时磁盘IO次数会增多，查询效率会降低。

  • 6.不建议用无序的值作为索引：例如身份证、UUID。更新数据时会发生频繁的页分裂，页内数据不紧凑，浪费磁盘空间。

  • 7.尽量创建组合索引，而不是单列索引。

    • 1个组合索引等同于多个索引效果，节省空间。
    • 可以使用覆盖索引

  • 8.最左原则：组合索引应该把频繁用到的列、区分度高的值放在前面。频繁使用代表索引的利用率高，区分度高代表筛选粒度大，这样做可最大限度利用索引价值，缩小筛选范围

InnoDB索引介绍

主键索引

每个InnoDB表都有一个主键索引（也叫聚簇索引） ，主键索引使用B+树构建，叶子节点存储的数据是整行记录。当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。

image.png

二级索引

除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据都是该行的主键值。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录（回表）。

image.png

什么是回表？

根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。

image.png

组合索引

组合索引也是二级索引，和单列索引不同，组合索引包含多个字段，支持多条件搜索。

image.png

覆盖索引

select中列数据如果可以直接在辅助索引树上全部获取，也就是说索引树已经“覆盖”了我们的查询需求， 这时MySQL就不会白费力气的回表查询，这中现象就是覆盖索引。

索引下推

官方索引条件下推： Index Condition Pushdown，简称ICP。是MySQL5.6对使用索引从表中检索行的 一种优化。ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。可用于InnoDB和MyISAM表，对于InnoDB表ICP仅用于辅助索引。

在没有使用ICP的情况下，MySQL的查询过程如下：

  1. 存储引擎读取索引记录。
  2. 根据索引中的主键值，定位并读取完整的行记录。
  3. 存储引擎将记录交给服务器层，服务器层检测该记录是否满足WHERE条件。

使用ICP的情况下，查询过程如下：

  1. 存储引擎读取索引记录（不是完整的行记录）。
  2. 判断WHERE条件部分能否用索引中的列来做检查，条件不满足则处理下一行索引记录。
  3. 条件满足，使用索引中的主键定位并读取完整的行记录。
  4. 存储引擎将记录交给服务器层，服务器层检测该记录是否满足WHERE条件的其余部分。

实践示例

假设有一个用户表tuser，表里创建了联合索引（name, age）。如果需要检索名字第一个字是“张”，且年龄是10岁的所有用户，可以使用以下SQL语句：

SELECT * FROM tuser WHERE name LIKE '张%' AND age = 10;

在没有使用ICP的情况下，存储引擎会根据联合索引的最左匹配原则，遇到范围查询或者模糊查询就停止，因此找到name LIKE '张%' 的主键id，然后逐一进行回表扫描，再由服务器层对数据根据age = 10进行筛选。

使用ICP后，存储引擎会在联合索引里按照age = 10过滤，减少回表次数，从而提高查询效率。

索引失效场景

  1. 不符合最左前缀匹配原则。
  2. 不在索引列上做任何操作【计算、函数、类型转换】，会导致索引失效，转而使用全表扫描
  3. 存储引擎不能使用索引中范围条件右边的列。
  4. 不等于【!= 或 <>】，索引会失效
  5. is null，is not null，索引会失效
  6. like以通配符开头，索引会失效
  7. 隐式类型转换的：如字符串不加单引号，索引会失效。
  8. 少用or，用它来连接时，索引会失效

总结

索引在MySQL高性能查询方面非常重要，要用好索引，需要注意以下几点：

  • 理解索引的数据结构。
  • 掌握索引的创建原则。
  • 掌握索引的使用原则：避免索引失效、减少回表次数。
avatar
文章
阅读
粉丝