涛涛小站

Appearance

认识索引 ​

MySQL 索引是一种用于提高数据库查询性能的数据结构。例如一本书就是一个数据库，“索引”则是前面的目录，查找内容时可以先通过目录快速定位章节，而不是全书逐页比对内容。

索引归类 ​

MySQL 索引可以按照不同维度归类总结；

数据结构 ​

MySQL 索引分别有 HASH 和 BTREE（B+） 两种数据结构；

  • HASH: 适用于等值查询，时间复杂度 O(1)，但不适用范围查询和排序
  • BTREE: 适用于各种查询，查询性能非常稳定

InnoDB 引擎不支持 HASH，选用的 BTREE 结构。

为什么不是二叉树，或者普通的 BTREE？

  • 二叉树极端情况会退化成链表，时间复杂度下降到 O(n)
  • 普通的 BTREE 每个节点都是完整的数据，导致节点数较多层级较高
  • 而且 BTREE 数据分散在不同的节点，性能不稳定

存储方式 ​

根据 BTREE 叶子节点存储记录的完整性，索引又能分成；

  • 聚簇索引(clustered): 叶子节点存放的是完整的记录，包含所有列字段，以及行属性
  • 非聚簇索引(secondary): 也叫辅助索引、二级索引，叶子节点存放的只有索引列和关联的聚簇索引键（主键）

索引类型 ​

在具体添加索引时，也有以下几个类型可选；

  • 主键索引(primary): 记录的唯一标识，每个表只能有一个主键
  • 普通索引(normal): 最基本的索引类型
  • 唯一索引(unique): 要求索引列的值是唯一的，不允许有重复
  • 全文索引(fulltext): 支持全文搜索，适用于大量文本数据的列。InnoDB 自 5.6 开始支持
  • 空间索引(spatial): 用于地理空间(GEO)数据类型，应用较少

数据量和并发量都不大情况下，完全可以用全文索引替代 ES，自 5.7 开始内置了中文分词插件 ngram，添加全文索引时需要手动指定，查询语句也需要使用特定语法。

sql
-- 添加索引
ALTER TABLE user ADD FULLTEXT index_username(`username`) WITH PARSER ngram;
-- 查询数据
select * from user where match(`username`) against('建涛');

底层原理 ​

页 InnoDB 管理存储空间的基本单位，一页大小一般是 16KB。上面所说的索引 BTREE 一个节点也就是一页，下面是页的结构示意图；

每条记录除了保存真实数据意外，还保存了指向下一条记录的指针，组成一个完整的记录链表。为避免遍历整个链表，将用户记录进行分组（最多 8 条），将每个分组最大记录的地址偏移量写入页目录中。

页内查询的过程可总结成两个步骤；

  • 通过页目录二分法确定目标可能所在分组
  • 遍历该分组内的所有记录匹配则返回

表中存放的记录通常需要用到很多页，页间通过双向链表相关联。与页目录类似，可以将多个页进行分组，将每个页中最小的记录写入一个新页中。由于页大小限制，新页也会有多个，从而继续分组。这也就是 BTREE 的由来。

数据页使用双向链表相连，页内记录用单向链表相连。 我的理解前者是便于索引的逆向遍历，后者总是要遍历记录链表可以节省一个指针。

高级术语 ​

联合索引 ​

"联合索引"是指在数据库表中的多个列上创建的一种索引。它也被称为复合索引或组合索引。

覆盖索引 ​

"覆盖索引"是指一个查询可以通过索引本身满足查询的需求，而无需再去访问实际的数据行例如主键索引树。

反之一个查询需要通过索引查询到符合条件的主键后，再通过主键索引去获取完整的数据，这个过程称为“回表”。

sql
-- 覆盖索引
select id, username from user where username = '涛';
-- 回表(age 列不在 username 索引树)
select id, username, age from user where username = '涛';

前缀索引 ​

"前缀索引"是一种索引类型，它并不是对整个列值进行索引，而是只对列值的前缀（前面的一部分字符）进行索引。通过只索引列值的一部分，前缀索引可以在一定程度上减小索引的大小，从而节省存储空间和提高检索性能。

sql
-- 前缀索引
alter table user add index index_username(username(10));

索引下推 ​

索引下推（Index Condition Pushdown，简称ICP）是一种数据库查询优化技术，用于将部分过滤条件推送到存储引擎层进行处理，减少回表次数，从而提高查询性能。

假设联合索引为 (group_id, nickname)，查询语句如下；

sql
select * from user where group_id = 100 and nickname like '%涛%'；
  • ICP 关闭: 通过索引查找所有 group_id = 100 的记录并回表校验 nickname 是否匹配
  • ICP 开启: 在索引查找 group_id = 100 的记录时，同时校验 nickname 是否匹配

自适应哈希索引 ​

自适应哈希索引（Adaptive Hash Index，简称 AHI）是MySQL InnoDB存储引擎引入的一项优化特性，用于提高特定查询模式下的性能。InnoDB 存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

经验之谈 ​

失效场景 ​

  • 索引列参与了运算
  • 对索引列进行了隐式类型转换
  • like '%value'
  • where or

索引优化 ​

  • 尽量作用在区分度较高的列上
  • 最好是走覆盖索引避免回表
  • 长文本模糊查询可以加上全文索引
  • 尽量选择占用存储空间较小的列
  • 更新频繁的字段不适合添加索引

基于 MIT 许可发布

版权所有 © 2023-2024 曹建涛