跳至主要內容

MySQL 索引深入

SunnyFan大约 16 分钟约 4726 字

MySQL 索引深入

简介

索引是 MySQL 性能优化的核心手段，本质是一种空间换时间的数据结构。InnoDB 使用 B+ 树作为主要索引结构，同时支持全文索引、空间索引和哈希索引（自适应哈希索引，AHI）。一个设计良好的索引可以将查询时间从秒级降到毫秒级，但一个设计糟糕的索引不仅浪费存储空间，还会拖慢写入性能。

为什么索引如此重要

在实际生产中，数据量增长到百万级甚至千万级后，没有索引的查询会变得极慢。考虑一个包含 1000 万行记录的订单表：

  • 无索引全表扫描：需要读取约 500MB 数据（假设每行 50 字节），耗时 1-5 秒
  • 有索引查找：B+ 树 3-4 层即可定位到数据，只需读取几个数据页，耗时 1-5 毫秒

性能差距达到 1000 倍以上，这就是索引的价值。

B+ 树索引结构

InnoDB B+ 树的特点

InnoDB 的 B+ 树索引有以下关键特点：

B+ 树结构示意（3 层，以订单表的联合索引 idx_user_status_time 为例）：

根节点（Page 1）
+------------------------------------------+
| user_id=500 | 指向 Page 100              |
| user_id=2000 | 指向 Page 200             |
+------------------------------------------+
        |                    |
中间节点（Page 100）      中间节点（Page 200）
+------------------+      +------------------+
| user_id=100,     |      | user_id=2000,    |
| status='paid',   |      | status='paid',   |
| 指向 Page 300    |      | 指向 Page 400    |
+------------------+      +------------------+
        |
叶子节点（Page 300）
+----------------------------------------------------------+
| (user_id=50, status='paid', created_at='2024-01-15',     |
|  主键=1001) → 数据行指针                                  |
| (user_id=50, status='paid', created_at='2024-01-20',     |
|  主键=1005) → 数据行指针                                  |
| (user_id=50, status='paid', created_at='2024-02-01',     |
|  主键=1012) → 数据行指针                                  |
+----------------------------------------------------------+

聚簇索引 vs 二级索引：

特性聚簇索引（主键索引）二级索引
叶子节点内容完整数据行主键值 + 索引列值
查询方式直接获取数据（无需回表）先查到主键，再回表查聚簇索引
数量每张表只有 1 个可以有多个
存储开销数据按主键顺序存储额外占用空间
典型场景主键查询条件查询、排序、覆盖索引

回表的开销：二级索引查询通常需要两次 B+ 树查找——先在二级索引中找到主键值，再到聚簇索引中找到完整数据行。这就是为什么覆盖索引如此重要：如果查询的所有列都在二级索引中，就不需要回表。

实战示例

示例 1：联合索引设计与最左前缀验证

-- 创建测试表
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    order_total DECIMAL(12, 2),
    INDEX idx_user_status_time (user_id, status, created_at)
) ENGINE=InnoDB;

-- 命中索引：按最左前缀顺序查询
EXPLAIN SELECT * FROM orders WHERE user_id = 1001;
-- type: ref，key: idx_user_status_time（命中第一列）

EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 'paid';
-- type: ref，key: idx_user_status_time（命中前两列）

EXPLAIN SELECT * FROM orders
WHERE user_id = 1001 AND status = 'paid' AND created_at > '2024-01-01';
-- type: range，key: idx_user_status_time（命中全部三列）

-- 不命中索引：跳过 user_id 直接查 status
EXPLAIN SELECT * FROM orders WHERE status = 'paid';
-- type: ALL（全表扫描！最左前缀原则被违反）

-- 特殊情况：跳过中间列（user_id 存在，跳过 status，直接用 created_at）
EXPLAIN SELECT * FROM orders
WHERE user_id = 1001 AND created_at > '2024-01-01';
-- type: range，key: idx_user_status_time
-- 命中了 user_id，但 status 列被跳过，created_at 只能做范围过滤

-- MySQL 8.0 索引跳跃扫描（Index Skip Scan）
-- 如果 user_id 基数很低（如只有少量不同值），MySQL 8.0.13+ 可能自动使用跳跃扫描
EXPLAIN SELECT * FROM orders WHERE status = 'paid';
-- 在 MySQL 8.0+ 且 user_id 基数很低时，可能显示：
-- type: range, Extra: Using index for skip scan

示例 2：覆盖索引与回表优化

覆盖索引是索引优化的最高境界——查询所需的所有列都在索引中，无需回表。

-- 非覆盖索引查询：需要回表取 order_total
EXPLAIN SELECT order_id, user_id, status, order_total
FROM orders WHERE user_id = 1001 AND status = 'paid';
-- Extra: NULL（需要回表获取 order_total）

-- 覆盖索引查询：所有列都在索引中
-- 创建包含所需列的覆盖索引
CREATE INDEX idx_cover ON orders (user_id, status, created_at, order_total);

EXPLAIN SELECT user_id, status, created_at, order_total
FROM orders WHERE user_id = 1001 AND status = 'paid';
-- Extra: Using index（覆盖索引，无需回表）

-- 注意：覆盖索引中必须包含 SELECT 的所有列
-- 如果 SELECT *，则几乎不可能覆盖（除非表只有索引列）
-- 这也是为什么不推荐 SELECT * 的另一个原因

-- 实际案例分析：用户订单列表接口
-- 需求：查询用户最近的订单，显示订单号、状态、金额、时间
-- 优化前：
SELECT order_id, status, order_total, created_at
FROM orders WHERE user_id = 1001
ORDER BY created_at DESC LIMIT 20;
-- 需要回表取 order_total

-- 优化后：建立覆盖索引
CREATE INDEX idx_user_time_cover ON orders (user_id, created_at DESC, order_total, status);
-- 现在 Extra 显示 Using index，完全避免回表
-- 性能提升 3-10 倍（取决于数据量和 IO 情况）

示例 3：索引失效的常见场景

索引失效是 MySQL 性能问题最常见的根因。以下是五大典型场景：

-- 1. 对索引列使用函数 → 索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-15';
-- 失效原因：对 created_at 列使用了 DATE() 函数，优化器无法使用索引范围扫描
-- 优化方案：改为范围查询
SELECT * FROM orders
WHERE created_at >= '2024-01-15' AND created_at < '2024-01-16';
-- 命中索引

-- MySQL 8.0 函数索引（新特性）
CREATE INDEX idx_created_date ON orders ((DATE(created_at)));
-- 现在可以使用函数索引了
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-15';
-- type: ref, key: idx_created_date

-- 2. 隐式类型转换 → 索引失效
-- 假设 user_id 是 VARCHAR 类型
CREATE TABLE users (
    user_id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(100)
);
SELECT * FROM users WHERE user_id = 1001;
-- 失效原因：字符串与数字比较时，MySQL 将 user_id 转为数字（对索引列使用函数）
-- 优化方案：传入正确类型
SELECT * FROM users WHERE user_id = '1001';
-- 命中索引

-- 3. LIKE 左模糊 → 索引失效
SELECT * FROM orders WHERE order_no LIKE '%20240115';
-- 失效原因：通配符在最前面，无法利用索引前缀匹配
-- 优化方案：确保通配符在后面
SELECT * FROM orders WHERE order_no LIKE 'ORD2024%';
-- 命中索引

-- 全文搜索替代方案
ALTER TABLE orders ADD FULLTEXT INDEX ft_order_no (order_no);
SELECT * FROM orders WHERE MATCH(order_no) AGAINST('20240115');

-- 4. OR 条件中包含无索引列
SELECT * FROM orders WHERE user_id = 1001 OR order_total > 5000;
-- 如果 order_total 没有索引，整个查询走全表扫描
-- 优化方案 A：给 order_total 也建索引
CREATE INDEX idx_total ON orders (order_total);
-- 优化方案 B：使用 UNION ALL 替代 OR
SELECT * FROM orders WHERE user_id = 1001
UNION ALL
SELECT * FROM orders WHERE order_total > 5000 AND user_id != 1001;

-- 5. 使用 != 或 NOT IN
SELECT * FROM orders WHERE status != 'cancelled';
-- 通常失效，因为优化器认为大部分数据需要返回
-- 优化方案：使用 IN 替代
SELECT * FROM orders WHERE status IN ('pending', 'paid', 'shipped');

-- 6. 对索引列做运算
SELECT * FROM orders WHERE id + 1 = 1002;
-- 失效原因：对 id 列做了运算
-- 优化方案：
SELECT * FROM orders WHERE id = 1001;

示例 4：索引统计信息与维护

-- 查看索引基数（Cardinality 越高，索引越有效）
SHOW INDEX FROM orders;
-- 关键字段：
-- Seq_in_index: 索引中列的位置
-- Cardinality: 索引列的不重复值数量估算
-- Null: 索引列是否允许 NULL

-- 分析索引选择性
-- 选择性 = 不重复值数 / 总行数，越接近 1 越好
SELECT
    COUNT(DISTINCT status) / COUNT(*) AS selectivity_status,
    COUNT(DISTINCT user_id) / COUNT(*) AS selectivity_user_id,
    COUNT(*) AS total_rows
FROM orders;
-- status 可能只有 5 种值，选择性 0.000001 → 索引效果差
-- user_id 有 100 万种值，选择性 0.5 → 索引效果好

-- 更新索引统计信息
ANALYZE TABLE orders;
-- 在大量数据导入、删除后必须执行 ANALYZE TABLE
-- 否则优化器可能因为统计信息不准确而选择错误的执行计划

-- 查看索引使用情况（MySQL 8.0+）
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_db';
-- 找出从未使用过的索引，这些索引浪费空间且拖慢写入

-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'your_db';
-- 找出重复的索引，例如已有 (a, b) 索引时再建 (a) 索引就是冗余的

索引设计最佳实践

联合索引的列顺序原则

联合索引列顺序决策流程：

1. 等值查询列优先
   WHERE user_id = 1001 AND status = 'paid'
   → user_id 在前，status 在后

2. 排序列紧跟其后
   ORDER BY created_at DESC
   → created_at 在 status 后面

3. 范围查询列放最后
   WHERE created_at > '2024-01-01'
   → created_at 放在最后（范围查询后的列无法使用索引）

4. 高选择性列靠前（当等值查询列有多个时）
   user_id（选择性 0.8）> status（选择性 0.01）
   → user_id 在前

最终索引：(user_id, status, created_at)

前缀索引

对于长字符串列，可以使用前缀索引节省空间：

-- 完整索引（占用空间大）
CREATE INDEX idx_email_full ON users (email);
-- 索引大小 = 平均 email 长度 * 行数

-- 前缀索引（节省空间）
CREATE INDEX idx_email_prefix ON users (email(20));
-- 索引大小 = 20 * 行数，大幅节省

-- 如何选择前缀长度？
-- 原则：前缀的选择性应接近完整列的选择性
SELECT
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS sel_15,
    COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS sel_20,
    COUNT(DISTINCT email) / COUNT(*) AS sel_full
FROM users;

-- 前缀索引的缺点：无法用于覆盖索引和 ORDER BY

优点

缺点

总结

索引设计是数据库性能优化的第一道防线。核心原则是：高选择性列在前、覆盖高频查询、控制索引数量、定期审查。不要盲目加索引，每加一个索引都要验证 EXPLAIN 结果并评估写入代价。

索引设计的决策框架：

  1. 分析查询模式：收集 TOP 20 高频查询，分析 WHERE/ORDER BY/GROUP BY 列
  2. 设计候选索引：为每个高频查询设计最优的联合索引
  3. 合并冗余索引：检查是否有多个索引可以合并为一个
  4. 验证 EXPLAIN：确保所有高频查询都能走索引
  5. 评估写入代价：计算每张表的索引数量，单表索引不超过 5-6 个

关键知识点

  • InnoDB 聚簇索引按主键顺序存储数据，二级索引叶子节点存储主键值
  • 联合索引遵循最左前缀原则，等值查询列在前、范围查询列在后
  • MySQL 8.0 支持函数索引 CREATE INDEX idx_func ON t ((UPPER(name))) 和隐藏索引 ALTER TABLE t ALTER INDEX idx INVISIBLE
  • 索引选择性 = COUNT(DISTINCT col) / COUNT(*)，选择性越高索引效果越好

项目落地视角

  • 建立索引命名规范：idx_表名_列名，联合索引用 idx_表名_列1_列2
  • 上线 SQL 必须附带 EXPLAIN 结果，确保 WHERE/ORDER BY/GROUP BY 有索引支持
  • 定期使用 sys.schema_unused_indexes 清理无用索引
  • 监控 innodb_rows_read 和 innodb_rows_inserted 的比值，读远多于写时索引收益大

常见误区

  • 认为索引越多越好：每个索引都有写入维护成本，需要权衡读写比例
  • 滥用 SELECT *：无法利用覆盖索引优化，且浪费网络带宽
  • 索引列使用函数或表达式：导致索引失效但不报错，是最难排查的慢查询原因
  • 不关注索引基数：低基数列（如性别、状态）建单独索引效果差
  • 忽略隐藏索引的价值：MySQL 8.0 的隐藏索引可以安全测试删除索引的效果

进阶路线

  • 深入 B+ 树的页分裂与合并机制，理解索引写入代价
  • 学习 MySQL 8.0 的函数索引、降序索引、隐藏索引等新特性
  • 研究 Index Merge、索引条件下推（ICP）、多范围读（MRR）等优化策略
  • 了解自适应哈希索引（AHI）的工作原理和适用场景

适用场景

  • 高频查询的 WHERE 条件列需要建立索引
  • ORDER BY / GROUP BY 列配合索引避免额外排序
  • 外键列建立索引避免锁升级和连接性能问题

落地建议

  • 核心业务表每月审查索引使用情况，清理 90 天未使用的索引
  • 高频查询优先设计覆盖索引，减少回表 IO
  • 长字符串列考虑前缀索引 INDEX idx_name (name(20))
  • 新建索引前先在测试环境验证 EXPLAIN 结果

排错清单

  • EXPLAIN 检查 type 是否为 ALL（全表扫描）
  • EXPLAIN 检查 Extra 是否有 Using filesort / Using temporary
  • SHOW INDEX FROM table 检查 Cardinality 是否接近实际基数
  • 检查是否有冗余索引（sys.schema_redundant_indexes）
  • 检查索引列是否存在隐式类型转换

复盘问题

  • 当前最大的慢查询表有哪些索引？是否有缺失或冗余？
  • 最近添加的索引是否真正改善了查询性能？效果如何度量？
  • 索引维护策略是什么？是否在数据大量变化后更新统计信息？
  • 每张表的索引数量是多少？是否超过了 5-6 个？

索引条件下推（ICP）与 MRR

-- 索引条件下推（Index Condition Pushdown）
-- MySQL 5.6+ 引入，存储引擎层提前过滤数据

-- 示例：联合索引 idx_user_status_time (user_id, status, created_at)
-- 查询：WHERE user_id = 1001 AND status = 'paid' AND created_at > '2024-01-01'

-- 没有 ICP：存储引擎返回所有 user_id=1001 的行给 Server 层过滤
-- 有 ICP：存储引擎在索引中直接过滤 status 和 created_at，减少回表次数

EXPLAIN SELECT * FROM orders
WHERE user_id = 1001 AND status = 'paid'
AND created_at > '2024-01-01';
-- Extra: Using index condition (表示使用了 ICP)
-- Extra: Using where; Using index condition (同时有 ICP 和 where 过滤)

-- ICP 的适用条件：
-- 1. 使用联合索引
-- 2. WHERE 条件包含索引列但不在最左前缀中的列
-- 3. 表类型必须是 InnoDB 或 MyISAM

-- 多范围读优化（MRR - Multi-Range Read）
-- MySQL 5.6+ 引入，优化二级索引回表顺序

-- 默认 MRR 关闭时：按二级索引顺序逐条回表（随机 IO）
-- MRR 开启时：先收集所有二级索引的主键，排序后批量回表（顺序 IO）

-- 开启 MRR
SET optimizer_switch = 'mrr=on,mrr_cost_based=off';

EXPLAIN SELECT * FROM orders
WHERE user_id BETWEEN 1001 AND 2000
AND status = 'paid';
-- Extra: Using MRR (表示使用了 MRR 优化)

-- MRR 适合：
-- 1. 范围扫描二级索引
-- 2. 需要大量回表
-- 3. 表数据分散在磁盘上（随机 IO 成本高）

索引与锁的关系

-- 索引对锁的影响：索引不同，锁行为不同

-- 1. 无索引更新 — 行锁退化为表锁
-- 如果 WHERE 条件没有索引，InnoDB 会对所有扫描的行加锁
UPDATE orders SET status = 'shipped' WHERE total_amount > 10000;
-- 如果 total_amount 没有索引 → 锁全表
-- 如果 total_amount 有索引 → 只锁匹配的行

-- 2. 间隙锁（Gap Lock）
-- 在索引范围扫描时，InnoDB 会对索引间隙加锁，防止幻读
SELECT * FROM orders WHERE user_id = 1001 AND status = 'paid' FOR UPDATE;
-- 如果 idx_user_status_time 索引存在，会锁住：
-- (1001, 'paid') 这条记录
-- 以及 (1001, 'paid') 到下一条记录之间的间隙

-- 3. 死锁检测
-- 死锁的两个必要条件：
-- a) 两个事务以不同顺序锁定同一组行
-- b) 锁超时时间过长

-- 查看当前锁等待
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM performance_schema.data_locks;

-- 减少死锁的建议：
-- 1. 按固定顺序访问表和行
-- 2. 保持事务简短
-- 3. 使用合适的索引减少锁定的行数
-- 4. 考虑使用 READ COMMITTED 隔离级别（减少间隙锁）

大表索引维护策略

-- 大表（千万级以上）索引维护注意事项

-- 1. 在线添加索引（MySQL 5.6+ ALGORITHM=INPLACE）
ALTER TABLE orders ADD INDEX idx_new_column (new_column),
ALGORITHM=INPLACE, LOCK=NONE;
-- ALGORITHM=INPLACE: 不复制全表，在线添加
-- LOCK=NONE: 允许并发读写
-- 注意：仍然会短暂锁表（取决于 MySQL 版本和索引类型）

-- 2. 添加全文索引 — 需要锁表
ALTER TABLE orders ADD FULLTEXT INDEX ft_description (description),
ALGORITHM=COPY;
-- FULLTEXT 索引只能用 COPY 算法，会锁表

-- 3. 删除索引 — 快速操作
ALTER TABLE orders DROP INDEX idx_unused,
ALGORITHM=INPLACE, LOCK=NONE;
-- 删除索引是元数据操作，几乎瞬间完成

-- 4. pt-online-schema-change（Percona 工具）
-- 对于特别大的表（亿级），考虑使用 pt-online-schema-change
-- 原理：创建新表 → 建索引 → 同步数据 → 切换表名
-- 优点：不锁表，不影响在线业务
-- 缺点：需要额外的磁盘空间，工具复杂度高

-- 5. 大表添加索引的性能预估
-- 表大小: 1000 万行，每行 200 字节 → 约 2GB
-- 索引大小: 1000 万 × 8 字节（主键）+ 索引列大小
-- 添加时间: 约 30 秒 - 5 分钟（取决于硬件）
-- 期间 IO 负载会明显增加，建议在低峰期操作

索引设计决策框架

-- 索引设计的完整决策框架

-- 步骤 1: 收集高频查询
-- 找出 TOP 20 慢查询
SELECT * FROM sys.statements_with_rdd_and_temporal
ORDER BY avg_timer_wait DESC LIMIT 20;

-- 步骤 2: 分析每个查询的 WHERE / ORDER BY / GROUP BY
-- 提取所有涉及的列

-- 步骤 3: 合并相同列集的查询
-- 例如 5 个查询都用到 (user_id, status)，可以共享一个联合索引

-- 步骤 4: 设计联合索引
-- 遵循 ESR 原则：Equality → Sort → Range
-- WHERE user_id = ? AND status = ? ORDER BY created_at DESC
-- → 索引 (user_id, status, created_at)

-- 步骤 5: 检查是否可以覆盖
-- SELECT user_id, status, created_at, order_total FROM orders WHERE ...
-- → 在索引中包含 order_total 实现覆盖

-- 步骤 6: 验证 EXPLAIN
EXPLAIN SELECT user_id, status, created_at, order_total
FROM orders WHERE user_id = 1001 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;
-- 预期：type=ref, key=idx_user_status_time, Extra=Using index; Backward index scan

-- 步骤 7: 评估写入代价
-- 每多一个索引，INSERT/UPDATE/DELETE 都要额外维护
-- 单表索引建议不超过 5-6 个

-- 步骤 8: 定期审查
-- 每月检查未使用的索引
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'your_db';

-- 每月检查冗余索引
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'your_db';

-- 步骤 9: 索引命名规范
-- idx_<表名>_<列1>_<列2>
-- 例如: idx_orders_user_status_time
-- 联合索引列按索引中的顺序排列
索引设计黄金法则：
1. 一个查询只需要一个最优索引（不是越多越好）
2. 索引列顺序：等值条件在前、排序在中间、范围条件在后
3. 覆盖索引是最有效的优化手段
4. 低基数列（如性别、状态）单独建索引效果差
5. 定期清理未使用和冗余的索引
6. 新建索引必须在测试环境验证 EXPLAIN

延伸阅读

Copyright © 2026 SunnyFan