MySQL 索引

索引是影响数据库性能的一大部分, 也是面试很容易问到的方向. 这里记录 MySQL 索引相关的问题.

什么是索引?

索引是一种数据结构, 合理利用可以提高数据库的查询效率. 可以将索引类比为书的目录, 用于快速定位想要查找的位置.

索引一般存储在磁盘的文件中, 占用物理空间.

索引不能过多, 索引越多, 对数据库的插入和更新功能影响就越大.

MySQL 索引的类型

索引可以从几个方面分类:

• 数据结构
• 物理存储
• 逻辑

在数据结构方面, 索引包括如下类型:

• B+ 树索引: 所有的数据存储在叶子节点, 复杂度 $O(log n)$, 适用于范围查询.
• Hash 索引: 适用于等值查询, 检索效率高, 复杂度 $O(1)$.
• 全文索引: 适用于文本类型.
• R-Tree 索引: 适用于 GIS 类型数据.

在物理存储方面:

• 聚集索引: 以主键创建的索引, 叶子节点中保存的是表中的完整数据.
• 非聚集索引: 以非主键创建的索引, 叶子节点中保存的是主键和索引列.

在逻辑方面:

• 主键索引: 特殊的唯一索引, 不允许空值.
• 唯一索引: 索引列中的值必须是唯一的, 但是允许空值.
• 普通索引: MySQL 基本索引类型, 允许空值和重复值.
• 联合索引: 多个字段创建的索引, 遵循最左前缀原则.
• 空间索引: 遵循 OpenGIS 几何数据模型规则.

索引的优缺点?

优点:

• 加快查询速度.
• 唯一索引保证插入数据唯一性.

缺点:

• 索引的创建和维护都需要耗费时间.
• 索引要占用一定的物理空间.
• 对表中元素进行增删改, 需要动态维护索引.

为什么使用 B+ 树, 而不是二叉树?

思考方向: 查询速度, 查询稳定性, 存储数据数量, IO 次数.

普通二叉树由于不稳定, 可能会退化为链表, 相当于全表扫描, 肯定不会使用.

平衡二叉树一个节点只存储一个数据, 树高度很高, 磁盘 IO 次数多, 查询效率低(决定数据库查询效率的一大因素就是磁盘 IO 次数).

对于 B 树来说, 它的非叶子节点不仅会保存键值对, 也会保存数据, 这样的话保存键值的空间就会减少, 树的阶数也更小, 相对于 B+ 树来说就会更高瘦, 磁盘 IO 次数更多.

InnoDB 每页默认大小为 16KB, 以页为单位进行数据读写.

同时, B+ 树的所有数据都保存在叶子节点, 并且叶子节点的数据是被按照顺序排列的双向链表连接的, 非常有利于各种查找操作.

扩展: 参考 InnoDB 数据页, 页分裂机制...

Hash 索引和 B+ 树索引的区别?

• B+ 树支持范围查询, Hash 索引不行.
• B+ 树支持联合索引的最左前缀原则, Hash 索引不行.
• B+ 树支持 ORDER BY 排序, Hash 索引不行.
• B+ 树在使用 LIKE 进行模糊查询时, 可以进行优化, 而 Hash 索引不支持模糊查询.
• Hash 索引在等值查询上效率更高, 前提是 Hash 冲突少.

什么是回表, 如何减少回表?

如果要查询的数据在索引树中找不到, 就会拿到主键 id, 回到主键索引树中获取, 这就叫回表.
如: 我们有一个关于 student_id 的索引, 但是 SQL 语句为 SELECT * FROM user WHERE student_id = "2020080907004", 此时索引为非聚簇索引, 只保存了主键 id 和 student_id 两个数据, 不能满足 SELECT * 的需求, 就会拿着 主键 id 去主键索引树中进行查找.

减少回表的方式:
如果要查找的数据组相对固定, 可以为其建立联合索引, 这样保证要查找的数据在对应的索引树中. 如: 在用户登录时, 我们需要查找用户的 student_id, password, salt, 就可以新建一个结构为 (student_id, password, salt) 的联合索引(注意最左前缀原则), 这样就不不会产生回表操作, 只是会多一些索引树占用空间.

覆盖索引

其实覆盖索引就是上一个问题的解决方案. 如果 SELECT 需要的数据列只需要从索引中就能取得, 那么就不需要回表, 也就是说查询列被所建的索引覆盖.

索引的最左前缀原则

最左前缀原则用于联合索引中, 很好的提高了索引的复用能力. 如: 我们建立了联合索引 (a, b, c), 相当于建立了 (a), (a, b), (a, b, c) 三个索引. 在字符串中, 最左前缀可以是字符串索引的最左 N 个字符.

在联合索引树中查找时, 会优先匹配左侧的索引字段, 然后逐步向右匹配索引字段, 所以要注意建立联合索引的字段顺序和 SQL 语句中编写的查询字段的顺序.

联合索引的最左前缀匹配原则中, MySQL 会向右一直匹配到遇到范围查询为止. 如: 有联合索引 (a, b, c, d), SQL 语句为 a = 1 AND b = 2 AND c > 3 AND d = 4, 此时按照联合索引的最左前缀匹配, 只会匹配到 (a, b, c), d 用不到索引; 如果想要 d 用上索引, 就需要将索引建为 (a, b, d, c)(保证 c 在最后即可), 此时就可以使 a, b, c, d 都用上索引.

索引下推

这是 MySQL 5.6 之后的功能, 也是针对联合索引进行的优化. 如: 我们有 (name, age) 的联合索引, 在 5.6 之前, 在联合索引树找到 name 对应的的数据之后, 会拿到其主键 id, 然后回表匹配其他字段, 这样效率很低.
因此, 在 5.6 之后, 提出了索引下推的优化, 当找到 name 对应的数据之后, 会继续在联合索引树中对 age (其余的联合索引字段)进行筛选, 最后才进行回表, 减少回表带来的性能损失.

索引失效条件

• 查询条件包含 OR, 索引可能失效.
• 如果字段类型是字符串, 在使用 WHERE 时, 一定要用引号括起来, 否则索引失效.
• LIKE 可能会导致索引失效.
• 联合索引中, 查询时的条件列不遵循最左前缀原则, 索引失效(必须要左侧字段用上索引, 右侧元素才能正常使用索引, 并且左侧元素不能是范围查询).
• 在索引列上使用 MySQL 内置函数, 索引失效.
• 对索引列进行计算, 索引失效.
• 对索引字段使用 !=, <>, not in, 索引可能失效.
• 对索引字段使用 is null 或 is not null, 索引可能失效.
• 左连接查询或右连接查询关联的字段编码格式不同, 索引失效.
• MySQL 估计走全表要比走索引快, 就不用索引.

大表如何添加索引?

在添加索引时, 会对表加锁, 可能会影响效率或出现故障. 因此, 我们采用如下方案:

1. 创建一张和原表 A 相同数据结构的新表 B;
2. 在新表 B 添加需要的索引;
3. 将原表 A 数据导入 新表 B;
4. 将新表 B 重命名为原表 A 的表名, 原表 A 改为别的名字.

如何判断语句是否走索引?

使用 explain 语句.

建立索引的原则

1. 一定要遵循最左前缀原则.
2. 针对 (1), = 和 IN 可以乱序.
3. 选择区分度高的列作为索引. 区分度公式: count(distinct col)/count(*)
4. 索引列不能参与计算. 尽量计算参数, 不要计算索引列, 否则会导致对全表元素的计算, 相当于全表扫描.
5. 尽量扩展索引, 不要新建索引. 如: 表中已有 a 的索引, 现在要加上 (a, b) 的索引, 只需要修改原来的索引即可.