第13章 索引
13.1 索引概述
13.1.1 索引的概念
索引（Index）是数据库中用于提高查询性能的数据结构，它通过建立数据值与存储位置之间的映射关系，加速数据的检索过程。索引类似于书籍的目录，可以帮助数据库系统快速定位到需要的数据，而不需要扫描整个表。
13.1.2 索引的作用
- 提高查询速度：通过索引可以直接定位到数据所在的物理位置，避免全表扫描
- 加速排序和分组：索引本身是有序的，可以直接用于排序和分组操作
- 保证数据唯一性：唯一索引可以确保数据的唯一性约束
- 加速连接查询：在连接查询中，索引可以加速表之间的关联
13.1.3 索引的优缺点
优点
- 显著提高查询速度
- 减少IO操作
- 加速排序和分组
- 保证数据完整性
缺点
- 占用额外的磁盘空间
- 降低数据插入、更新和删除的性能
- 需要维护索引结构，增加系统开销
- 过多的索引会影响查询优化器的选择
13.2 索引的工作原理
13.2.1 索引的基本原理
索引的基本原理是通过构建高效的数据结构，将数据列的值与数据行的物理地址关联起来。当执行查询时，数据库系统首先查询索引，找到匹配的数据行地址，然后根据地址直接访问数据行，从而避免全表扫描。
13.2.2 索引的存储结构
索引通常使用以下数据结构：
13.2.2.1 B树索引
B树（Balance Tree）是一种平衡多路查找树，具有以下特点：
- 每个节点可以包含多个关键字
- 根节点至少有2个子节点
- 除根节点外，每个节点至少有m/2个子节点（m为B树的阶）
- 所有叶子节点在同一层
- 关键字按顺序排列
B树的优点是：
- 平衡结构，查询效率稳定
- 适合范围查询
- 适合随机访问
13.2.2.2 B+树索引
B+树是B树的变种，是数据库系统中最常用的索引结构，具有以下特点：
- 所有关键字都存储在叶子节点中
- 叶子节点之间通过链表连接，形成有序链表
- 非叶子节点只存储关键字和指针，不存储数据
- 叶子节点存储完整的关键字和数据行指针
B+树相比B树的优点：
- 叶子节点有序链表，更适合范围查询
- 非叶子节点不存储数据，相同大小的节点可以存储更多关键字
- 查询效率稳定，始终需要访问到叶子节点
13.2.2.3 哈希索引
哈希索引基于哈希表实现，具有以下特点：
- 直接通过哈希函数计算数据位置
- 适合等值查询，不适合范围查询
- 查询速度快，时间复杂度为O(1)
- 不支持排序
13.2.2.4 全文索引
全文索引用于全文搜索，能够快速查找包含特定词语的文本数据。
13.2.3 索引的查找过程
以B+树索引为例，查找过程如下：
1. 从根节点开始，比较查询值与节点中的关键字
2. 根据比较结果，选择对应的子节点继续查找
3. 重复上述过程，直到到达叶子节点
4. 在叶子节点中查找匹配的关键字，获取对应的数据行指针
5. 根据数据行指针访问实际的数据行
13.3 索引的类型
13.3.1 按数据结构分类
- B+树索引：最常用的索引类型，适合范围查询和等值查询
- 哈希索引：适合等值查询，不适合范围查询
- 全文索引：用于全文搜索
- 空间索引：用于地理空间数据查询
13.3.2 按功能分类
13.3.2.1 普通索引
普通索引是最基本的索引类型，没有任何限制，用于加速数据查询。
13.3.2.2 唯一索引
唯一索引要求索引列的值必须唯一，但允许空值（最多一个NULL）。
13.3.2.3 主键索引
主键索引是一种特殊的唯一索引，不允许空值，一个表只能有一个主键索引。
13.3.2.4 复合索引
复合索引是基于多个列创建的索引，遵循最左前缀原则。
13.3.2.5 前缀索引
前缀索引是对字符串列的前几个字符创建索引，适用于长字符串列，可以减少索引的存储空间。
13.3.2.6 全文索引
全文索引用于全文搜索，支持在文本数据中查找特定词语。
13.3.3 按存储方式分类
13.3.3.1 聚簇索引
聚簇索引（Clustered Index）决定了数据行的物理存储顺序，叶子节点存储的是完整的数据行。一个表只能有一个聚簇索引。
13.3.3.2 非聚簇索引
非聚簇索引（Non-Clustered Index）的叶子节点存储的是数据行的指针或主键值，而不是完整的数据行。一个表可以有多个非聚簇索引。
13.3.4 聚簇索引与非聚簇索引的区别
特性 | 聚簇索引 | 非聚簇索引 |
数量限制 | 一个表只能有一个 | 一个表可以有多个 |
物理存储 | 决定数据行的物理存储顺序 | 不影响数据行的物理存储顺序 |
叶子节点内容 | 完整的数据行 | 数据行指针或主键值 |
查询效率 | 通常更高 | 通常低于聚簇索引 |
适用场景 | 主键或频繁查询的列 | 频繁查询的非主键列 |
13.4 索引的创建与管理
13.4.1 创建索引
13.4.1.1 创建表时创建索引
13.4.1.2 表创建后创建索引
13.4.2 查看索引
13.4.3 修改索引
在大多数数据库中，没有直接修改索引的语句，通常需要先删除索引，然后重新创建。
13.4.4 删除索引
13.5 索引的使用场景
13.5.1 适合创建索引的场景
- 频繁作为查询条件的列：如WHERE子句中经常出现的列
- 经常用于连接的列：如外键列，用于表连接
- 经常需要排序的列：如ORDER BY子句中的列
- 经常需要分组的列：如GROUP BY子句中的列
- 唯一约束的列：如身份证号、手机号等
- 字符串列的前缀：对于长字符串列，可以创建前缀索引
13.5.2 不适合创建索引的场景
- 数据量小的表：表数据量小时，全表扫描可能比索引查询更快
- 频繁更新的列：更新列时需要同时更新索引，会降低性能
- 值分布均匀的列：如性别列（只有男/女），索引选择性低
- 很少使用的列：创建索引会占用空间，且不会带来查询性能提升
- TEXT、BLOB等大字段：这些列的数据量大，创建索引会占用大量空间
13.6 索引的设计原则
13.6.1 最左前缀原则
复合索引遵循最左前缀原则，即查询条件中使用了复合索引的第一个列，索引才会被使用。
例如，创建复合索引
INDEX idx_name_age(name, age)，则：
- WHERE name = '张三'：会使用索引
- WHERE name = '张三' AND age = 18：会使用索引
- WHERE age = 18：不会使用索引13.6.2 选择高选择性的列作为索引
索引的选择性是指不重复的索引值与表记录数的比值，选择性越高，索引的查询效率越高。
例如，身份证号的选择性接近1，适合创建索引；而性别的选择性只有0.5，不适合创建索引。
13.6.3 避免过度索引
- 每个索引都需要占用额外的磁盘空间
- 索引会降低插入、更新和删除的性能
- 过多的索引会影响查询优化器的选择
13.6.4 考虑索引的存储成本
- 对于大表，索引的存储成本可能很高
- 可以考虑使用前缀索引减少索引的存储空间
- 定期清理无用的索引
13.6.5 合理使用复合索引
- 复合索引可以替代多个单列索引
- 复合索引的列顺序应该根据查询频率和选择性来确定
- 将最常用的列放在前面
- 将选择性高的列放在前面
13.6.6 考虑查询优化器的选择
- 查询优化器会根据索引的选择性、数据分布等因素选择是否使用索引
- 有时查询优化器可能会选择全表扫描而不是使用索引
13.7 索引的使用与优化
13.7.1 索引的使用
13.7.1.1 索引的触发条件
- 查询条件中使用了索引列（遵循最左前缀原则）
- 索引列上没有使用函数或表达式
- 索引列的数据类型与查询条件的数据类型匹配
13.7.1.2 索引失效的情况
- 在索引列上使用函数或表达式
- 使用!=或<>运算符
- 使用IS NULL或IS NOT NULL
- 使用OR连接条件（如果OR两边的列都有索引，可能会使用索引）
- 使用LIKE以通配符开头
- 数据类型不匹配
13.7.2 索引的优化策略
13.7.2.1 分析查询性能
使用EXPLAIN命令分析查询语句的执行计划，查看是否使用了索引。
13.7.2.2 优化查询语句
- 避免在索引列上使用函数或表达式
- 避免使用!=或<>运算符
- 避免使用LIKE以通配符开头
- 使用合适的数据类型
- 合理使用连接条件
13.7.2.3 优化索引结构
- 根据查询需求调整复合索引的列顺序
- 考虑使用覆盖索引，避免回表查询
- 定期重建索引，优化索引碎片
13.7.2.4 使用覆盖索引
覆盖索引是指查询的所有列都包含在索引中，不需要回表查询实际的数据行。
13.7.2.5 避免回表查询
回表查询是指通过索引找到数据行指针后，还需要访问实际的数据行。可以通过使用覆盖索引或主键索引来避免回表查询。
13.8 索引的维护与监控
13.8.1 索引的维护
13.8.1.1 重建索引
当索引出现碎片或性能下降时，可以重建索引。
13.8.1.2 优化索引
优化索引可以消除索引碎片，提高索引性能。
13.8.1.3 删除无用索引
定期检查并删除无用的索引，减少存储开销和维护成本。
13.8.2 索引的监控
13.8.2.1 监控索引使用情况
可以通过数据库系统视图或工具监控索引的使用情况，包括：
- 索引的查询次数
- 索引的命中率
- 索引的更新次数
- 索引的大小
13.8.2.2 识别低效索引
低效索引的特征：
- 很少被使用的索引
- 选择性低的索引
- 更新频繁的索引
- 占用大量空间的索引
13.8.2.3 识别缺失索引
通过分析查询日志和执行计划，可以识别出应该创建但尚未创建的索引。
13.9 索引的最佳实践
13.9.1 索引设计最佳实践
- 根据查询需求设计索引：分析常用的查询语句，针对查询条件设计索引
- 遵循最左前缀原则：复合索引的列顺序要根据查询频率和选择性来确定
- 选择高选择性的列：索引选择性越高，查询效率越高
- 避免过度索引：每个表的索引数量不宜过多，一般不超过5个
- 合理使用复合索引：复合索引可以替代多个单列索引
- 考虑索引的存储成本：对于大表，要考虑索引的存储空间
- 定期维护索引：重建或优化索引，消除索引碎片
- 监控索引使用情况：定期检查索引的使用情况，删除无用索引
13.9.2 索引使用最佳实践
- 避免在索引列上使用函数：会导致索引失效
- 使用合适的查询条件：避免使用!=、IS NULL等可能导致索引失效的条件
- 使用覆盖索引：减少回表查询，提高查询效率
- 合理使用连接条件：连接查询中，确保连接列上有索引
- 分析查询执行计划：使用EXPLAIN命令分析查询语句，优化查询
- 考虑数据类型匹配：查询条件的数据类型要与索引列的数据类型匹配
13.10 综合示例
13.10.1 示例1：用户表索引设计
假设有一个用户表users，包含以下字段：
- id（主键，自增）
- username（用户名，唯一）
- email（邮箱，唯一）
- phone（手机号）
- age（年龄）
- gender（性别）
- created_at（创建时间）
13.10.1.1 常用查询场景
- 根据用户名查询用户：
SELECT * FROM users WHERE username = '张三';
- 根据邮箱查询用户：
SELECT * FROM users WHERE email = 'zhangsan@example.com';
- 查询2023年创建的用户：
SELECT * FROM users WHERE YEAR(created_at) = 2023;
- 查询年龄大于18岁的用户：
SELECT * FROM users WHERE age > 18 ORDER BY created_at DESC;
- 查询男性用户，按年龄降序排序：
SELECT * FROM users WHERE gender = '男' ORDER BY age DESC;
13.10.1.2 索引设计
13.10.2 示例2：订单表索引设计
假设有一个订单表orders，包含以下字段：
- order_id（订单ID，主键）
- user_id（用户ID，外键）
- product_id（产品ID，外键）
- order_date（订单日期）
- total_amount（订单总金额）
- status（订单状态）
13.10.2.1 常用查询场景
- 根据订单ID查询订单：
SELECT * FROM orders WHERE order_id = '123456';
- 查询用户的所有订单：
SELECT * FROM orders WHERE user_id = 1001 ORDER BY order_date DESC;
- 查询2023年1月的订单：
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
- 查询状态为”已完成”的订单：
SELECT * FROM orders WHERE status = '已完成' ORDER BY order_date DESC;
- 查询用户2023年的订单总金额：
SELECT SUM(total_amount) FROM orders WHERE user_id = 1001 AND YEAR(order_date) = 2023;
13.10.2.2 索引设计
13.11 常见问题与解决方案
13.11.1 问题1：索引没有被使用
- 原因：查询条件不符合索引的使用规则，如在索引列上使用了函数、使用了!=运算符等
- 解决方案：优化查询语句，避免在索引列上使用函数，使用合适的查询条件
13.11.2 问题2：索引过多导致性能下降
- 原因：过多的索引会占用额外的磁盘空间，降低插入、更新和删除的性能
- 解决方案：删除无用的索引，合并重复的索引，根据查询需求优化索引结构
13.11.3 问题3：索引碎片导致性能下降
- 原因：频繁的插入、更新和删除操作会导致索引碎片
- 解决方案：定期重建或优化索引，消除索引碎片
13.11.4 问题4：复合索引的列顺序不合理
- 原因：复合索引的列顺序没有遵循最左前缀原则，导致索引失效
- 解决方案：根据查询频率和选择性调整复合索引的列顺序
13.11.5 问题5：回表查询过多
- 原因：查询的列不在索引中，需要回表查询实际的数据行
- 解决方案：使用覆盖索引，将查询的列包含在索引中
13.12 小结
索引是数据库中提高查询性能的重要工具，通过合理设计和使用索引，可以显著提高数据库的查询效率。本章介绍了索引的基本概念、工作原理、类型、创建和管理方法，以及索引的使用场景和最佳实践。
在实际应用中，需要根据具体的查询需求和数据特点，合理设计和优化索引。同时，还需要定期维护和监控索引，确保索引的性能和有效性。
索引的设计是一个权衡的过程，需要在查询性能和写入性能之间找到平衡。过多的索引会降低写入性能，而过少的索引会导致查询性能下降。因此，需要根据实际情况，选择合适的索引策略。
13.13 练习题
- 什么是索引？索引的作用是什么？
- 索引有哪些类型？请简要描述每种类型的特点。
- B树索引和B+树索引的区别是什么？
- 什么是聚簇索引和非聚簇索引？它们的区别是什么？
- 复合索引的最左前缀原则是什么？请举例说明。
- 哪些情况下索引会失效？请举例说明。
- 请描述适合创建索引的场景和不适合创建索引的场景。
- 索引设计的原则有哪些？
- 什么是覆盖索引？它有什么优点？
- 如何监控和维护索引？
