一、索引的基本原理与作用

在优化之前，必须理解索引是如何工作的。

  1. 索引是什么？

    • 索引（Index）是帮助 MySQL 高效获取数据的数据结构（通常是 B+Tree）。
    • 你可以把它想象成一本书的目录。没有目录，你要找特定内容就得一页一页翻（全表扫描）。有了目录，你可以快速定位到对应的页码。

  2. 为什么索引能加快查询？

    • 大大减少需要扫描的数据行数。
    • 使得数据检索从随机 I/O 变为更顺序的 I/O。
    • 数据库引擎（如 InnoDB）通过遍历索引树来找到所需数据的指针，然后直接去磁盘定位行数据。

  3. 索引的代价（为什么不能乱建）

    • 空间代价：索引也是一张表，需要占用磁盘空间。
    • 时间代价：对表进行 INSERT、UPDATE、DELETE 操作时，MySQL 不仅要操作数据，还要更新对应的索引树，维护索引结构会降低写操作的速度。

二、索引优化核心策略

1. 为合适的列创建索引

  • WHERE 子句中的列：这是最应该考虑建立索引的列。频繁作为查询条件的字段，例如 WHERE user_id = 123。
  • 连接（JOIN）使用的列：例如 ON a.user_id = b.id，user_id 和 id 上都应该有索引。
  • 排序（ORDER BY）和分组（GROUP BY）的列：索引可以帮助避免额外的排序操作。如果 ORDER BY last_name 很频繁，在 last_name 上建索引会极大提升性能。
  • 高选择性的列：选择性越高（唯一值越多），索引的效率就越高。例如，为“性别”这种只有两三个值的列建索引意义不大，但为“用户名”、“手机号”建索引就非常有效。

2. 避免冗余和重复索引

  • 重复索引：在同一个列上创建多个相同顺序、相同类型的索引。例如，已经有一个 INDEX (a)，又创建一个 INDEX (a)。这是完全没有必要的。
  • 冗余索引：如果已经有了联合索引 INDEX (a, b)，那么再创建一个 INDEX (a) 就是冗余的，因为前者已经可以优化只查询 a 的场景。但 INDEX (b) 就不是冗余的，因为它无法利用 (a, b)。

3. 善用联合索引（复合索引）与最左前缀原则

这是索引优化中最重要、最强大的技巧。

  • 最左前缀原则 (Leftmost Prefix Principle)：MySQL 在使用联合索引时，会从索引的最左边开始匹配，向右连续匹配，直到遇到范围查询（>, <, BETWEEN, LIKE）就停止匹配。

  • 示例：假设有一个联合索引 INDEX (last_name, first_name, age)。

    • WHERE last_name = 'Wang' ✅ (使用索引)
    • WHERE last_name = 'Wang' AND first_name = 'Alan' ✅ (使用索引)
    • WHERE last_name = 'Wang' AND first_name = 'Alan' AND age = 30 ✅ (使用索引)
    • WHERE first_name = 'Alan' ❌ (不满足最左前缀，无法使用此索引)
    • WHERE last_name = 'Wang' AND age = 30 ✅ (部分使用，只使用了 last_name，age 无法被利用)
    • WHERE last_name = 'Wang' AND first_name LIKE 'A%' AND age = 30 ✅ (使用到 last_name 和 first_name，age 由于 LIKE 是范围查询，无法被用于索引查找，但可以用于索引过滤)

设计技巧：将选择性最高、最常用的列放在联合索引的左边。

4. 索引失效的常见场景

即使创建了索引，错误的查询方式也会导致索引失效，变成全表扫描。

  • 在索引列上做计算、函数或类型转换：
    • WHERE YEAR(create_time) = 2023 ❌ (对 create_time 使用了函数)
    • WHERE amount * 2 > 100 ❌ (对 amount 做了计算)
    • 应改为：WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31' ✅
  • 使用 LIKE 以通配符开头：
    • WHERE name LIKE '%son' ❌
    • WHERE name LIKE 'John%' ✅ (可以使用索引)
  • OR 条件不当：
    • WHERE a = 1 OR b = 2，如果 a 和 b 分别有单列索引，MySQL 有时会使用索引合并（index_merge），但效率通常不如一个联合索引高。如果只有一个字段有索引，则会导致全表扫描。
  • 不符合最左前缀原则：（如上文所述）
  • 使用不等于操作符（!= 或 <>）：
    • WHERE status != 'active' ❌ (通常会导致全表扫描)
  • 数据类型不匹配（隐式类型转换）：
    • 如果列 user_id 是字符串类型（varchar），但查询写 WHERE user_id = 123（数字），MySQL 会隐式地将列转换为数字，导致索引失效。应写为 WHERE user_id = '123'。

三、高级技巧与最佳实践

  1. 使用覆盖索引 (Covering Index)

    • 概念：如果一个索引包含（覆盖）了所有需要查询的字段的值，我们就称之为覆盖索引。
    • 优势：MySQL 只需要在索引中就能获取所需数据，而无需回表（不需要根据主键再去数据文件里查找），这可以极大地提升性能。
    • 示例：表 users 有 (id, name, age)，主键是 id。
      • 查询：SELECT name, age FROM users WHERE name = 'Alice';
      • 如果有一个索引 INDEX (name, age)，那么这个索引已经包含了所有需要的数据（name, age），甚至不需要主键 id 的值。这就是覆盖索引。

  2. 利用索引进行排序

    • 如果 ORDER BY 的子句满足最左前缀原则，MySQL 可以直接使用索引来排序，避免昂贵的 filesort 操作。
    • 示例：有索引 (a, b)
      • ORDER BY a ✅
      • ORDER BY a, b ✅
      • ORDER BY b ❌
      • ORDER BY a DESC, b DESC ✅

  3. 前缀索引 (Prefix Indexes)

    • 对于很长的字符列（如 TEXT, VARCHAR(255)），为整个列建索引会占用大量空间。可以只索引列的前面一部分字符，既能节省空间，又基本能达到区分度的要求。
    • ALTER TABLE users ADD INDEX idx_name (name(10)); – 只对 name 的前 10 个字符创建索引
    • 如何确定长度？ 目标是保证较高的选择性同时长度最小。可以通过计算不同前缀长度的选择性来决定：
      1
      2
      3
      4
      SELECT 
        COUNT(DISTINCT LEFT(column_name, 10)) / COUNT(*) AS selectivity_10,
        COUNT(DISTINCT LEFT(column_name, 20)) / COUNT(*) AS selectivity_20
      FROM table_name;

  4. 使用 EXPLAIN 分析查询

    • 这是最重要的工具！ 在任何查询语句前加上 EXPLAIN，MySQL 会告诉你它打算如何执行这个查询。
    • 关键字段解读：
      • type：访问类型。从好到坏：system > const > eq_ref > ref > range > index > ALL。至少要达到 range 级别，最好能达到 ref。
      • key：实际使用的索引。
      • rows：预估需要扫描的行数。越小越好。
      • Extra：额外信息。如果出现 Using filesort 或 Using temporary，就需要警惕和优化。如果出现 Using index，恭喜你，用上了覆盖索引。

四、总结与忠告

  • 没有银弹：索引优化是一个权衡的过程，需要在查询速度和更新速度之间取得平衡。
  • 分析为先：不要凭感觉创建索引。使用 慢查询日志 (Slow Query Log) 找出数据库中真正耗时的 SQL，然后用 EXPLAIN 逐一分析优化。
  • 联合索引优于多个单列索引：在复杂查询中，一个设计良好的联合索引远比多个孤立的单列索引有效。
  • 单表索引数量不宜过多：一般建议不超过 5 个。过多的索引会加重写负担，并且优化器在选择索引时也可能犯错。
  • 定期审视和优化：随着业务发展和数据变化，旧的索引可能不再高效，需要定期清理无用索引或创建新索引。