MySQL 索引原理与优化

索引是数据库中用于提高查询效率的重要数据结构，类似于书籍的目录。

1. 常见索引模型

• 哈希表:
  • 适用于只有等值查询的场景（如 WHERE id = 1）。
  • 时间复杂度为 O(1)。
  • 不适用于范围查询（如 WHERE id > 5），因为哈希后的存储是无序的。
  • 常用于 Memcached 等 NoSQL 引擎。
• 有序数组:
  • 在等值查询和范围查询场景中性能都很好（可用二分查找）。
  • 只适用于静态存储引擎，因为插入和删除操作需要移动大量数据，成本很高。
• 搜索树 (B+树):
  • 为了减少磁盘 I/O 次数，数据库普遍采用 N 叉树结构，特别是 B+ 树。
  • B+ 树能够很好地配合磁盘的读写特性，将相关数据尽可能存放在同一个磁盘块（页）中，减少查询时访问磁盘的次数。

2. InnoDB 的索引模型 (B+树)

在 InnoDB 中，表数据本身就是按照主键顺序存放在一个 B+ 树结构中，这种表称为索引组织表 (Index-Organized Table, IOT)。

• 聚簇索引 (Clustered Index):
  • 即主键索引。
  • 叶子节点存储的是整行数据。
  • 每个 InnoDB 表有且只有一个聚簇索引。通常使用主键列，如果没有显式定义主键，InnoDB 会选择一个唯一的非空索引代替，如果还没有，InnoDB 会隐式定义一个 6 字节的 rowid 作为主键。
• 二级索引 (Secondary Index):
  • 即非主键索引（普通索引、唯一索引等）。
  • 叶子节点存储的是主键的值。

2.1 查询过程与回表

• 使用主键查询: 直接在聚簇索引树上搜索，找到叶子节点即可获取整行数据。
• 使用二级索引查询:
  1. 先在对应的二级索引树上搜索，找到叶子节点，获取主键值。
  2. 再用获取到的主键值，回到聚簇索引树上搜索，找到对应的叶子节点，获取整行数据。 这个通过二级索引找到主键值，再回到聚簇索引查找数据的过程，称为回表 (Back to Table)。

优化提示

由于回表需要多扫描一棵索引树，基于二级索引的查询通常比基于主键的查询慢。因此，在应用中应尽量使用主键查询。

2.2 主键选择建议

从性能和存储空间角度考虑：

• 推荐使用自增主键:
  • 性能: 插入新记录时，数据总是追加到末尾，避免了页分裂（数据页已满时，需要申请新页并移动部分数据）带来的性能损耗和空间浪费。
  • 存储: 相对于 UUID 或其他业务字段，自增 ID 通常更短小，可以节省二级索引的存储空间（因为二级索引叶子节点存储的是主键值）。
• 避免使用 UUID 或无序字段作主键: 会导致频繁的页分裂和数据移动，插入性能差，且占用更多空间。

3. 索引优化策略

3.1 覆盖索引 (Covering Index)

• 定义: 如果一个索引包含了查询需要的所有字段（SELECT 后面的字段以及 WHERE 条件中的字段），那么查询时只需要扫描这个二级索引树，而不需要回表到聚簇索引去获取数据。这个索引就称为覆盖索引。
• 优点: 减少了树的搜索次数（避免了回表），显著提升查询性能。
• 示例: 表 t 有联合索引 (a, b)。查询 SELECT b FROM t WHERE a = 5;，索引 (a, b) 包含了查询所需的 a 和 b 字段，可以直接从该索引获取结果，无需回表。
• 实践: 使用覆盖索引是常用的 SQL 优化手段。可以通过 EXPLAIN 查看执行计划，Extra 列显示 Using index 表示使用了覆盖索引。

3.2 最左前缀原则 (Leftmost Prefix Principle)

• 定义: 对于联合索引，MySQL 会一直向右匹配索引字段，直到遇到范围查询（>, <, BETWEEN, LIKE '%...'）就停止匹配。查询条件需要利用索引的最左边的字段。
• 示例: 表 t 有联合索引 (a, b, c)。
  • WHERE a = 1 -> 使用索引 a 部分。
  • WHERE a = 1 AND b = 2 -> 使用索引 a, b 部分。
  • WHERE a = 1 AND c = 3 -> 只使用索引 a 部分（跳过了 b）。
  • WHERE b = 2 AND c = 3 -> 无法使用该索引（没有从最左边的 a 开始）。
  • WHERE a = 1 AND b > 2 AND c = 3 -> 使用索引 a, b 部分（b 是范围查询，c 无法利用索引）。
• 字符串索引: 对于字符串类型的索引，也存在最左前缀的概念，例如 INDEX(name(10)) 只对 name 字段的前 10 个字符创建索引。
• 联合索引字段顺序:
  1. 复用性: 优先考虑是否可以通过调整顺序，让该索引能被更多查询复用（例如，如果存在对 (a) 的查询和对 (a, b) 的查询，那么创建 (a, b) 索引即可满足两者）。
  2. 空间: 在满足复用性的前提下，考虑将区分度高的字段放在前面（虽然 B+ 树结构本身不强制要求，但有时有助于优化器选择）。

3.3 索引下推 (Index Condition Pushdown, ICP)

• 背景: 在 MySQL 5.6 之前，对于联合索引，如果 WHERE 条件中包含索引未覆盖的字段，即使该字段在索引定义中存在，也需要在回表后由 Server 层进行过滤。
• ICP 优化: MySQL 5.6 引入了索引下推。在索引遍历过程中，如果 WHERE 条件中包含了索引内的字段（即使不是最左前缀的部分），存储引擎层会先对索引包含的字段进行判断，过滤掉不满足条件的记录，然后再回表。
• 优点: 减少了回表次数，提高了查询效率。
• 示例: 表 t 有联合索引 (name, age)。查询 SELECT * FROM t WHERE name LIKE '张%' AND age = 10;
  • 无 ICP: 引擎层通过索引找到所有 name 以 "张" 开头的记录，全部回表，然后 Server 层再根据 age = 10 进行过滤。
  • 有 ICP: 引擎层在索引 (name, age) 上找到 name 以 "张" 开头的记录后，直接在索引内部判断 age 是否等于 10，只将满足条件的记录回表。
• 查看: EXPLAIN 的 Extra 列显示 Using index condition 表示使用了索引下推。

4. 普通索引 vs 唯一索引

• 查询性能: 对于等值查询，两者性能差距微乎其微。唯一索引理论上找到第一条满足条件的记录就会停止，普通索引会继续查找直到不满足条件。但这个差异通常可以忽略。
• 更新性能:
  • Change Buffer: 这是 InnoDB 的一个优化机制。当需要更新一个数据页时，如果该页不在内存 (Buffer Pool) 中，普通索引会将更新操作缓存在 Change Buffer 里，等到下次需要访问该页时再将操作合并 (merge) 到数据页，减少了随机 I/O。
  • 唯一索引: 为了保证唯一性，更新操作必须将数据页读入内存进行判断，因此无法使用 Change Buffer 优化。
• 选择建议:
  • 如果业务上不需要强制唯一性约束，或者可以通过其他方式（如应用层逻辑）保证，推荐使用普通索引，以利用 Change Buffer 提升更新性能，尤其是在写多读少的场景。
  • 如果必须依赖数据库保证唯一性，则使用唯一索引。

Change Buffer

• 作用: 缓存非唯一二级索引的更新操作（INSERT, DELETE, UPDATE），将随机 I/O 转换为顺序 I/O（写 Change Buffer）和延迟 I/O（后台 merge 或访问时 merge）。
• 持久化: Change Buffer 的内容也会被记录到 redo log 中，因此是 crash-safe 的。
• 适用场景: 写多读少的业务，或者写入后不会立即读取的场景。如果写入后马上读取，会立即触发 merge，反而增加了 Change Buffer 的维护开销。
• 与 Redo Log 对比: Redo Log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 Change Buffer 主要节省的是随机读磁盘的 IO 消耗（避免读入数据页进行更新）。

5. 索引选择异常与处理

MySQL 优化器在大多数情况下能选择正确的索引，但有时会因为统计信息不准或成本估算偏差而选错索引。

5.1 优化器逻辑

• 基于成本: 优化器选择索引的目标是找到一个总成本最低的执行计划。成本主要包括：
  • I/O 成本: 从磁盘读取数据页的成本。读取一个页的成本通常计为 1.0。
  • CPU 成本: 处理内存中数据的成本，如比较、计算等。通常远小于 I/O 成本，计为 0.2。
  • 优化器会估算不同执行计划（如全表扫描 vs 使用不同索引）的成本，选择最低的那个。
• 统计信息 (Cardinality):
  • 优化器依赖表的统计信息来估算成本，其中最重要的是基数 (Cardinality)，即索引列中不重复值的数量。
  • 统计信息是通过采样得到的，可能不完全准确，尤其是在数据频繁变动或采样不充分时。不准确的统计信息是导致优化器选错索引的常见原因。
  • 可以使用 SHOW INDEX FROM table_name; 查看索引的 Cardinality 值。
• 成本估算因素:
  • 扫描行数: 优化器会估算需要扫描的行数。
  • 是否回表: 如果使用二级索引且需要访问未包含在索引中的列，则需要回表，增加 I/O 成本。
  • 是否使用覆盖索引: 使用覆盖索引可以避免回表，降低成本。
  • 是否需要排序: 如果查询需要排序且无法利用索引的有序性，会增加额外的排序成本。

5.2 处理方法

当发现优化器选错索引时，可以尝试以下方法：

1. 更新统计信息: 使用 ANALYZE TABLE table_name; 命令重新收集表的统计信息。这通常能解决因统计信息陈旧或不准导致的问题。
2. 强制使用索引 (Force Index):
   • 在查询语句中使用 FORCE INDEX(index_name) 来强制优化器使用指定的索引。
   • 示例: SELECT * FROM t FORCE INDEX(idx_a) WHERE a = 1 AND b = 2;
   • 注意: 这是一种临时或最后的手段，因为它将优化逻辑硬编码到 SQL 中，可能在未来数据分布变化或版本升级后不再是最优选择。
3. 修改 SQL 语句:
   • 尝试改写查询，引导优化器使用正确的索引。例如，避免在索引列上使用函数，调整 WHERE 条件的顺序（虽然顺序通常不影响 B+ 树索引的选择，但可能影响其他优化过程）。
   • 确保查询条件符合最左前缀原则。
4. 修改或添加索引:
   • 考虑是否可以创建更合适的索引（如覆盖索引、调整联合索引的列顺序）来优化查询。
   • 删除冗余或很少使用的索引。
5. 调整优化器参数: (不常用，风险较高)
   • 可以通过 optimizer_switch 等参数调整优化器的行为，但这需要深入理解其工作原理，不建议轻易修改。

---