索引

23 October 2025   2503 words   ~5 mins
#MySQL #Index #BplusTree #最左前缀

索引简介

Concept

Index 是什么

Index 是一种数据结构,用于加速查找和快速定位数据,.

为什么需要 Index

传统数据库中进行查找时往往需要在全表全库中进行查找, Index 可以帮助快速定位表,行所在的位置,避免全表查找减少硬盘IO.

Contrast

有哪些不同实现的 Index

Index有不同的数据结构,如 B+树, Hash索引, 全文索引. 在 InnoDB 中使用 B+树进行索引.

Context

如何使用和创建索引

当创建好索引之后,只要进行等值查询 (= , IN(), <=>) 时索引在查询条件的左侧,即可自动使用索引.

创建索引:

  • 直接创建 CREATE INDEX index_name ON table_name (key1, key2, ...)
  • 修改表时创建 ALTER TABLE users ADD INDEX idx_email (email);
  • 建表时创建
CREATE TABLE products (
        id INT PRIMARY KEY,
        name VARCHAR(100),
        INDEX idx_name (name)
    );

什么时候需要索引

  1. 当前数据具有唯一性可以当成主键.

  2. 当某些数据段的查询十分频繁时可以考虑添加索引.即经常出现在 WHERE 和 JOIN 条件里.

  3. 当前数据需要频繁排序(GROUP BY 和 ORDER BY) 时添加索引后就不再需要每次重排序.

索引的不同分类

索引的不同分类

按不同方法分,索引可以分一下几种:

  1. Clustered 与 Secondary
  2. B+树索引 哈希索引 全文索引
  3. 主键索引 唯一索引 前缀索引 普通索引

Clustered 与 Secondary

在InnoDB 中 每创建一个表都会自动创建一个 Clustered 索引,在没有创建其他索引前提下所有查找都通过 Clustered进行查找.

Secondary 是用户自己创建的 非主索引(二级索引)

哈希索引 B+树索引

哈希索引

通过哈希函数无序地将数据一一对应起来,因此哈希函数在等值查找时速度极佳 O(1) 但是因为哈希表的无序型使得 哈希索引无法进行范围查询.

优点: 速度快 占用内存小

缺点:

  • 不支持范围查询 模糊查询
  • 无法进行排序
  • 可能会发生哈希冲突
B+ 树索引

通过树结构将 从小到大 为 index 安排位置形成有序的树结构. 非子叶节点不存放数据,最后一层子叶节点从小到大进行排列,以链表形式彼此连接

优点 :

  • 支持范围查询 模糊查询
  • 支持最左前缀匹配
  • 可以进行排序
  • 低层级树减少磁盘 IO

缺点:

  • 维护树需要消耗更多资源
  • 速度相对哈希较慢 O(lgN)

Contrast

特性对比 Hash 索引 B+ 树索引
查找速度 极快，平均O(1) 较快，O(logN)
存储空间 通常较小 通常较大
支持的查询 仅支持等值查询 (=, IN) 支持等值、范围 (>, <, BETWEEN)、排序和前缀模糊查询 (LIKE ‘abc%’)
哈希冲突 存在，需要处理 不存在
维护成本 高 (尤其在数据频繁变更时) 高 (需要维护树平衡)

索引的创建与使用

索引越多越好吗

  • 每一次索引的创建都要构建对应的 B+ 树,会占用一定空间
  • 索引的更新与维护会占用数据库的资源
  • 索引创建不合理会导致大量回表

所以只有合适数量的索引才能达到最好效果.

索引创建应遵循什么原则

1. 索引创建应该遵循最左前缀原则,要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到

id	name	sex	position	Department
1	张三	男	软件工程师	    技术部
2	李四	女	产品经理	      产品部
3	王五	男	销售专员	      销售部
4	赵六	女	人力资源专员	    人力资源部
5	钱七	男	财务分析师	    财务部
6	孙八	女	前端开发工程师	    技术部
7	周九	男	市场专员	      市场部
8	吴十	女	测试工程师	    技术部

如果我们查找在技术部的测试工程师"吴十" 应该如何创建索引?

依据最左前缀,我们要由上到下地不断起进行过滤,所以如此安排 (Department, position, name)

注意, 应尽量将范围查询和模糊查询靠右放置,B+树数据存储节点也是先按最左边第一个字段排序，在第一个字段的排序基础上，然后在对后续字段进行排序.

![img](/home/pipo/文档/Obsidian Vault/Java/pics/BplusTreeSort.png)

2. 应选取多读少写的,常用的字段组成索引

选取多读少写的字段,即不更新的字段以避免频繁更新导致的树重建造成的资源浪费.

3. 应尽量建立覆盖索引减少回表次数

通过覆盖索引我们只需在 Secondary 索引中查询便可以得到全部所需信息,不需要再次进入 Clusters 索引查找字段.

什么是覆盖索引

覆盖索引就是指在当前使用索引查询中无需再次回到 Clusters 索引树即可拿到该次查询所需信息的索引.

索引如何更新和维护?

1. 索引更新

索引更新通常发生在数据发生变化时，例如插入、删除或修改操作。索引需要相应地进行调整以反映这些变化。

  • 插入数据时：

  • 新记录会根据索引键值插入到索引结构中。例如，在B树索引中，新键值会被插入到适当的位置，可能需要拆分节点以保持树的平衡。

  • 删除数据时：

  • 索引中的对应记录会被标记为删除或直接移除。如果删除操作导致节点变得空闲，可能会合并相邻节点以优化空间使用。

  • 修改数据时：

  • 如果修改了索引键值，索引会进行更新，可能包括删除旧键值并插入新键值。这可能导致索引树的重新平衡。

2. 索引维护

索引维护是为了保持索引的高效性和准确性，通常包括以下操作：

  • 索引重建：

  • 定期重建索引可以优化索引结构，合并碎片，释放空间，提高查询效率。重建索引通常在低峰时段进行，以减少对系统性能的影响。

  • 索引优化：

  • 优化索引结构，例如重新组织索引节点以减少树的高度，提高查询速度。这可能涉及重新平衡索引树或调整索引参数。

  • 清理无用索引：

  • 定期检查索引的使用情况，删除不再使用的索引，以避免占用不必要的存储空间和影响性能。

  • 索引碎片整理：

  • 碎片整理可以减少索引中的碎片，优化存储空间，提高访问效率。对于B树索引，这可能涉及合并相邻的空闲页。

  • 性能监控：

  • 监控索引的查询性能和存储使用情况，及时发现索引膨胀或性能下降的问题。

  • 分析工具：

  • 使用数据库的分析工具（如 EXPLAIN 语句）来检查查询是否有效利用索引，并根据结果调整索引策略。

索引什么时候会失效

1. 用联合索引时加入了范围和模糊查询

2. 索引使用函数或表达式

查询语句中，如果对索引字段**使用“函数”或“表达式”，**会导致索引失效。

因为索引树存储的是索引字段的原始值，因此无法索引经过函数计算或表达式计算后的值。

❌ 错误示例：

SELECT actor_id FROM actor WHERE actor_id + 1 = 5; SELECT ... WHERE TO_DAYS(current_date) - TO_DAYS(date_col) <= 10;

3. 对索引隐式类型转换

查询语句中，如果对索引字段进行隐式类型转换，会导致索引失效。由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以会导致索引失效。

4. 联合索引不遵循最左匹配原则

联合索引如果不遵循最左匹配原则，就会导致索引失效。原因是，在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。

5. 索引列判空

索引列与 NULL 或者 NOT NULL 进行判断的时候也会失效。这是因为索引并不存储空值.

参考

理解Mysql索引原理及特性 | 京东物流技术团队

https://dunwu.github.io/pages/fcb19c/

[04 深入浅出索引（上）](https://learn.lianglianglee.com/专栏/MySQL实战45讲/04 深入浅出索引（上）.md)

待完成

  • 索引优化

  • 叶子节点内部查找

← prev next →