MySQL 索引优化完全指南

索引是 MySQL 性能优化中最重要的手段。一条正确的索引可以将查询速度从秒级提升到毫秒级，一条错误的索引可能让写入性能断崖式下降。本章将从 B+Tree 的物理存储结构讲起，一路深入到联合索引设计方法论，覆盖 12 种常见失效场景，并附带可交互的索引大小计算器。

1. B+Tree 数据结构

1.1 为什么是 B+Tree 而不是其他结构？

数据库索引本质上是一个"给定 key，快速定位 value"的数据结构问题。我们先看看候选结构的对比：

B+Tree 胜出的核心原因：

1. 极低的树高：假设每个页 16KB，每个索引指针 + key 占 14 字节，则每个非叶子节点的扇出约为 16384/14 ≈ 1170。三层 B+Tree 可以索引 1170^2 ≈ 136 万行；四层可以索引约 16 亿行。绝大多数表 2-4 次磁盘 I/O 就能定位到任何一行。
2. 叶子节点双向链表：B+Tree 的叶子节点通过前向和后向指针相连，范围查询（BETWEEN、>、ORDER BY）只需找到起始叶子节点后顺序扫描，无需回溯到非叶节点。
3. 非叶子节点只存 key：与 B-Tree 不同，B+Tree 非叶子节点不存储实际数据行，只存 key 和子页指针。这意味着非叶节点能容纳更多 key，扇出更大，树更矮。
4. 所有数据在叶子层：每次查找都走到叶子层，路径长度一致，性能稳定可预测。

1.2 InnoDB B+Tree 物理结构

InnoDB 以 页（Page） 为最小 I/O 单位，默认 16KB（innodb_page_size）。一个 B+Tree 索引的物理结构如下：

页内查找过程

在一个 Page 内部，记录按 key 排序组成单链表。为了避免 O(n) 遍历，InnoDB 维护了 Page Directory（页目录）——一组"槽（Slot）"，每个槽指向若干条记录中的一条。查找时先对槽做二分查找，定位到目标所在的记录组（通常 4-8 条），然后在组内顺序遍历。

1.3 B+Tree vs B-Tree 关键差异

1.4 聚簇索引（Clustered Index）

InnoDB 的表数据本身就按照主键的 B+Tree 组织存储。这棵以主键为 key、叶子节点存储完整行数据的 B+Tree 就是聚簇索引（Clustered Index）。

主键选择规则（InnoDB 的自动行为）：

1. 如果定义了 PRIMARY KEY，使用它
2. 如果没有 PRIMARY KEY，使用第一个 NOT NULL 的 UNIQUE KEY
3. 如果都没有，InnoDB 自动生成一个 6 字节的隐藏列 DB_ROW_ID 作为聚簇索引的 key

1.5 二级索引（Secondary Index）

除了聚簇索引以外的所有索引都是二级索引（也叫非聚簇索引、辅助索引）。它们的 B+Tree 叶子节点存储的不是行数据，而是 索引列的值 + 对应行的主键值。

回表查询（Bookmark Lookup / Back to Clustered Index）

当通过二级索引查找到主键值后，如果需要的列不在二级索引中，必须再去聚簇索引中查找完整行。这个过程叫做回表（Bookmark Lookup）。

-- 假设有 INDEX idx_name (name)
SELECT id, name, email FROM users WHERE name = 'Alice';

-- 执行过程：
-- 1. 在 idx_name 的 B+Tree 中查找 name='Alice'，得到 id=1
-- 2. 拿着 id=1 去聚簇索引（PRIMARY KEY）的 B+Tree 查找
-- 3. 在聚簇索引叶子节点找到完整行：id=1, name='Alice', email='[email protected]'
-- 4. 返回结果

-- 这个过程产生了 2 次 B+Tree 查找（2 次随机 I/O 路径）

1.6 页分裂与页合并

页分裂（Page Split）

当向一个已满的叶子页插入新记录时，InnoDB 必须将该页拆分成两个页。

1. 分配一个新的空页
2. 将原页约 50% 的记录移动到新页
3. 更新父节点的指针和 key
4. 如果父节点也满了，递归向上分裂（极少发生）

页分裂的代价：

• 一次分裂涉及至少 3 个页的修改（原页、新页、父页），产生多次随机写
• 分裂后两个页的空间利用率都约 50%，暂时浪费空间
• 如果分裂频繁，会导致数据在磁盘上不连续，影响范围扫描性能

解决方案对比：

页合并（Page Merge）

当一个页中的记录被大量删除（空间利用率低于约 50%，由 MERGE_THRESHOLD 控制），InnoDB 会尝试将相邻两个页合并成一个页，释放空页。

-- 可以通过 INFORMATION_SCHEMA 观察页合并行为
SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS
WHERE NAME LIKE '%index_page_merge%';

-- MySQL 8.0.25+ 可以为单个索引设置合并阈值
ALTER TABLE orders DROP INDEX idx_status,
  ADD INDEX idx_status (status) COMMENT 'MERGE_THRESHOLD=30';

1.7 索引 Key 的存储格式

InnoDB 中每个索引记录的存储格式如下：

存储开销要点：

• INT 定长 4 字节，BIGINT 定长 8 字节——作为索引列非常紧凑
• VARCHAR(255) 需要 1 字节存长度 + 实际内容字节数。如果列允许 NULL，还需要额外的 NULL 标志位
• 二级索引的每条记录都会追加主键列（如果主键不在索引列中），所以主键越短，二级索引越小
• CHAR(255) CHARSET utf8mb4 固定占 255×4=1020 字节——作为索引列是灾难级的浪费

2. MySQL 索引类型全解

2.1 PRIMARY KEY（主键索引）

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  -- ...
  PRIMARY KEY (id)
) ENGINE=InnoDB;

• 每表只能有一个
• 所有列必须 NOT NULL
• 在 InnoDB 中即聚簇索引
• 自动唯一

2.2 UNIQUE KEY（唯一索引）

ALTER TABLE users ADD UNIQUE KEY uk_email (email);

• 保证列值唯一，允许一个 NULL（多个 NULL 在 MySQL 8.0.16+ 中允许，旧版本行为不同）
• 与 INDEX 的查找效率几乎无差别——唯一区别在于找到第一条匹配后不再继续扫描
• 会增加 INSERT/UPDATE 时的唯一性检查开销（需要读取索引页判断是否冲突）

2.3 INDEX / KEY（普通二级索引）

ALTER TABLE orders ADD INDEX idx_user_id (user_id);

• 最常用的索引类型
• 允许重复值
• 等值查找 + 范围查找 + 排序 + 分组均可使用

2.4 FULLTEXT（全文索引）

ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, body)
  WITH PARSER ngram; -- 中文需要 ngram 解析器

-- 查询
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('MySQL 优化' IN BOOLEAN MODE);

• InnoDB 全文索引使用倒排索引（inverted index）结构
• 适合关键词搜索，不适合前缀匹配或精确匹配
• 中文需要配置 ngram 解析器（ngram_token_size 默认 2）
• 大数据量下建议使用 Elasticsearch / Meilisearch 替代

2.5 SPATIAL（空间索引）

CREATE TABLE places (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  location POINT NOT NULL SRID 4326,
  SPATIAL INDEX sp_location (location)
) ENGINE=InnoDB;

-- 查找半径 5km 内的地点
SELECT name, ST_Distance_Sphere(location, ST_GeomFromText('POINT(116.397 39.907)', 4326)) AS dist
FROM places
WHERE ST_Contains(
  ST_Buffer(ST_GeomFromText('POINT(116.397 39.907)', 4326), 5000),
  location
)
ORDER BY dist;

• 使用 R-Tree 数据结构（不是 B+Tree）
• 列必须为 NOT NULL 且声明 SRID
• MySQL 8.0 开始 InnoDB 支持空间索引

2.6 覆盖索引（Covering Index）

覆盖索引不是一种独立的索引类型，而是一种查询优化效果：当查询所需的所有列都包含在索引中时，无需回表。详见 第5节。

2.7 前缀索引（Prefix Index）

-- 对 VARCHAR(255) 列只索引前 20 个字符
ALTER TABLE users ADD INDEX idx_email_prefix (email(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;

-- 示例输出：
-- sel_10: 0.8934  sel_15: 0.9876  sel_20: 0.9998  sel_full: 1.0000
-- → 前缀长度 20 的选择性已经接近 100%，足够了

2.8 联合索引 / 复合索引（Composite Index）

ALTER TABLE orders ADD INDEX idx_user_status_created
  (user_id, status, created_at);

联合索引是索引优化的核心。它将多个列的值拼接成一个 key 存入 B+Tree。详见 第3节（最左前缀原则）。

2.9 降序索引（Descending Index） — MySQL 8.0+

-- MySQL 8.0 之前：DESC 被解析但被忽略，实际都是 ASC
-- MySQL 8.0+：真正支持降序存储

ALTER TABLE events ADD INDEX idx_time_score (created_at DESC, score ASC);

-- 最适合的查询模式：
SELECT * FROM events ORDER BY created_at DESC, score ASC LIMIT 20;
-- EXPLAIN Extra: Using index (无 filesort)

2.10 不可见索引（Invisible Index） — MySQL 8.0+

-- 将索引设为不可见：优化器忽略它，但索引仍然被维护
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;

-- 观察一段时间后确认没有性能退化，再安全删除
ALTER TABLE orders DROP INDEX idx_status;

-- 如果发现有问题，快速恢复
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;

用途：安全地测试删除索引的影响。传统做法是 DROP INDEX 然后发现有问题再 ADD INDEX（在大表上可能需要几小时）。不可见索引让你"软删除"索引，随时恢复。

-- 即使索引不可见，也可以通过 optimizer_switch 强制使用
SET optimizer_switch = 'use_invisible_indexes=on';
-- 用于诊断：确认某条慢查询是不是因为缺少这个索引

2.11 函数索引 / 表达式索引（Functional Index） — MySQL 8.0.13+

-- 需要按年份查询，但 WHERE YEAR(created_at) = 2026 无法使用普通索引
-- 解决方案：创建函数索引
ALTER TABLE orders ADD INDEX idx_year ((YEAR(created_at)));

-- 现在这个查询可以使用索引了
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- EXPLAIN: type=ref, key=idx_year

-- JSON 字段的函数索引
ALTER TABLE products ADD INDEX idx_brand
  ((CAST(attributes->>'$.brand' AS CHAR(50) CHARSET utf8mb4)));

SELECT * FROM products
WHERE CAST(attributes->>'$.brand' AS CHAR(50) CHARSET utf8mb4) = 'Apple';

3. 最左前缀原则

联合索引的 B+Tree 按照声明的列顺序依次排序。这意味着索引只有在查询条件从最左列开始连续匹配时才能被使用。这就是最左前缀原则（Leftmost Prefix Rule）。

假设有联合索引：

INDEX idx_abc (a, b, c)

它的 B+Tree 排序方式是：先按 a 排序，a 相同时按 b 排序，a 和 b 都相同时按 c 排序。

3.1 12 个查询示例

以下所有示例基于 INDEX idx_abc (a, b, c)：

EXPLAIN SELECT * FROM t WHERE a = 1;
-- type: ref | key: idx_abc | key_len: 4 | ref: const

EXPLAIN SELECT * FROM t WHERE a = 1 AND b = 2;
-- type: ref | key: idx_abc | key_len: 8 | ref: const,const

EXPLAIN SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;
-- type: ref | key: idx_abc | key_len: 12 | ref: const,const,const

EXPLAIN SELECT * FROM t WHERE b = 2;
-- type: ALL | key: NULL (全表扫描)

EXPLAIN SELECT * FROM t WHERE b = 2 AND c = 3;
-- type: ALL | key: NULL

EXPLAIN SELECT * FROM t WHERE a = 1 AND c = 3;
-- type: ref | key: idx_abc | key_len: 4 (只用了 a)
-- Extra: Using index condition (ICP 在索引层过滤 c)

EXPLAIN SELECT * FROM t WHERE a = 1 ORDER BY b;
-- type: ref | key: idx_abc
-- Extra: 无 filesort

EXPLAIN SELECT * FROM t WHERE a = 1 ORDER BY c;
-- type: ref | key: idx_abc
-- Extra: Using filesort (需要排序)

EXPLAIN SELECT * FROM t WHERE a = 1 AND b = 2 ORDER BY c;
-- type: ref | key: idx_abc | key_len: 8
-- Extra: 无 filesort

EXPLAIN SELECT * FROM t WHERE a > 1 AND b = 2;
-- type: range | key: idx_abc | key_len: 4 (只用了 a)
-- Extra: Using index condition

EXPLAIN SELECT * FROM t WHERE a IN (1, 2) AND b = 2 AND c = 3;
-- type: range | key: idx_abc | key_len: 12 (三列全用)

EXPLAIN SELECT * FROM t WHERE a = 1 AND b > 5 AND c = 3;
-- type: range | key: idx_abc | key_len: 8 (用了 a + b)
-- Extra: Using index condition

3.2 最左前缀原则总结表

3.3 列顺序设计原则

设计联合索引列顺序时，优先级：

1. 等值查询的列放前面（最重要）
2. 选择性高的列放前面（在等值列中，选择性高的先排）
3. 范围查询的列放在最后一个有效位（范围条件中断后续列）
4. ORDER BY / GROUP BY 列紧跟等值列之后（避免 filesort）

-- 查询模式：WHERE user_id = ? AND status = ? AND created_at > ? ORDER BY created_at
-- 最优索引设计：
INDEX idx_user_status_created (user_id, status, created_at)
-- user_id: 等值，放最前
-- status:  等值，放中间
-- created_at: 范围 + 排序，放最后（范围后无更多列需要索引）

4. 12 种索引失效场景

即使创建了索引，以下 12 种常见场景会导致索引无法使用（或优化器选择不使用），查询退化为全表扫描。每个场景都附带 SQL 示例和 EXPLAIN 输出。

-- 有索引 INDEX idx_created (created_at)

-- ❌ 索引失效：YEAR() 函数
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- EXPLAIN: type=ALL, key=NULL, rows=10000000

-- ✅ 改写为范围查询
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
-- EXPLAIN: type=range, key=idx_created, rows=850000

-- ✅ MySQL 8.0.13+ 方案：函数索引
ALTER TABLE orders ADD INDEX idx_year_created ((YEAR(created_at)));
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- EXPLAIN: type=ref, key=idx_year_created

-- phone 列类型为 VARCHAR(20)，有索引 INDEX idx_phone (phone)

-- ❌ 索引失效：传入整数，MySQL 将 phone 转为数字比较
SELECT * FROM users WHERE phone = 13800138000;
-- 等效于: WHERE CAST(phone AS DECIMAL) = 13800138000
-- EXPLAIN: type=ALL, key=NULL

-- ✅ 传入字符串
SELECT * FROM users WHERE phone = '13800138000';
-- EXPLAIN: type=ref, key=idx_phone, rows=1

-- 验证 MySQL 的转换方向
SELECT 'abc' = 0;  -- 结果: 1 (字符串 'abc' 被转为 0)
SELECT '123' = 123; -- 结果: 1 (字符串 '123' 被转为 123)
SELECT '123abc' = 123; -- 结果: 1 (截取前导数字部分)

-- 有索引 INDEX idx_name (name)

-- ❌ 索引失效：左模糊
SELECT * FROM users WHERE name LIKE '%张';
-- EXPLAIN: type=ALL, key=NULL

-- ❌ 索引失效：双向模糊
SELECT * FROM users WHERE name LIKE '%张三%';
-- EXPLAIN: type=ALL, key=NULL

-- ✅ 右模糊可以使用索引
SELECT * FROM users WHERE name LIKE '张%';
-- EXPLAIN: type=range, key=idx_name

-- 有索引 INDEX idx_name (name)，无 remark 索引

-- ❌ 索引失效
SELECT * FROM users WHERE name = '张三' OR remark = 'VIP';
-- EXPLAIN: type=ALL, key=NULL

-- ✅ 方案1：给 remark 也加索引（会启用 Index Merge）
ALTER TABLE users ADD INDEX idx_remark (remark);
SELECT * FROM users WHERE name = '张三' OR remark = 'VIP';
-- EXPLAIN: type=index_merge, key=idx_name,idx_remark
-- Extra: Using union(idx_name,idx_remark)

-- ✅ 方案2：改写为 UNION
SELECT * FROM users WHERE name = '张三'
UNION ALL
SELECT * FROM users WHERE remark = 'VIP' AND name != '张三';

-- 有索引 INDEX idx_status (status)

-- ❌ 通常索引失效（取决于数据分布）
SELECT * FROM orders WHERE status != 'completed';
SELECT * FROM orders WHERE status NOT IN ('completed', 'cancelled');
SELECT * FROM orders WHERE status <> 'completed';
-- EXPLAIN: type=ALL (当 'completed' 占大多数行时)

-- ⚠️ 但如果否定条件过滤掉大部分数据（高选择性），优化器可能使用索引
-- 例如 status 有 20 个取值，排除 1 个仍然返回 95% 的行 → 全表扫描更快
-- 例如 status 有 2 个取值，排除 1 个只返回 5% 的行 → 可能使用索引

-- ✅ 改写为正向条件
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped');

-- 有索引 INDEX idx_id (id)

-- ❌ 索引失效
SELECT * FROM orders WHERE id + 1 = 10;
-- EXPLAIN: type=ALL, key=NULL

-- ✅ 移项改写
SELECT * FROM orders WHERE id = 10 - 1;
-- 即 WHERE id = 9
-- EXPLAIN: type=const, key=PRIMARY, rows=1

-- ❌ 索引失效
SELECT * FROM products WHERE price * 0.8 > 100;
-- EXPLAIN: type=ALL

-- ✅ 改写
SELECT * FROM products WHERE price > 100 / 0.8;
-- 即 WHERE price > 125
-- EXPLAIN: type=range, key=idx_price

-- table_a.user_id 是 utf8mb4_general_ci
-- table_b.user_id 是 utf8_general_ci

-- ❌ 索引失效：字符集不匹配
SELECT a.*, b.name
FROM table_a a JOIN table_b b ON a.user_id = b.user_id;
-- EXPLAIN (table_b): type=ALL, key=NULL
-- Extra: Using where; Using join buffer

-- ✅ 统一字符集
ALTER TABLE table_b MODIFY user_id VARCHAR(50)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 现在可以正常使用索引
-- EXPLAIN (table_b): type=ref, key=idx_user_id

-- 检查所有列的字符集
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
       CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_db'
  AND CHARACTER_SET_NAME IS NOT NULL
ORDER BY CHARACTER_SET_NAME, COLLATION_NAME;

-- gender 列有索引，但只有 2 个不同值
SELECT * FROM users WHERE gender = 'M';
-- 假设表有 100 万行，约 50 万行匹配
-- EXPLAIN: type=ALL (优化器选择全表扫描)

-- 原因：50 万次回表的随机 I/O 远比顺序扫描 100 万行更慢

-- 索引 INDEX idx_a_b (a ASC, b ASC) （MySQL 5.7 忽略 DESC 关键字）

-- ❌ 无法利用索引排序
SELECT * FROM t WHERE a = 1 ORDER BY b DESC, c ASC;
-- MySQL 5.7: Extra: Using filesort

-- ✅ MySQL 8.0+ 创建降序索引
ALTER TABLE t ADD INDEX idx_a_b_desc_c_asc (a, b DESC, c ASC);
SELECT * FROM t WHERE a = 1 ORDER BY b DESC, c ASC;
-- EXPLAIN: Extra: 无 filesort

-- 有索引 INDEX idx_user_status (user_id, status)

-- ❌ SELECT * 必须回表
SELECT * FROM orders WHERE user_id = 100 AND status = 'pending';
-- EXPLAIN: type=ref, Extra: NULL (需要回表)

-- ✅ 只查索引中的列，触发覆盖索引
SELECT user_id, status FROM orders WHERE user_id = 100 AND status = 'pending';
-- EXPLAIN: type=ref, Extra: Using index (覆盖索引，无回表)

-- ✅ 如果确实需要更多列，扩展索引
ALTER TABLE orders ADD INDEX idx_user_status_amt (user_id, status, amount);
SELECT user_id, status, amount FROM orders WHERE user_id = 100 AND status = 'pending';
-- EXPLAIN: Extra: Using index

-- 索引 INDEX idx_abc (a, b, c)

-- a 等值 + b 范围 + c 等值
SELECT * FROM t WHERE a = 1 AND b > 10 AND c = 5;
-- 索引只用了 a 和 b（范围），c 无法用于索引过滤
-- key_len 只包含 a + b 的长度

-- ✅ 优化方案：调整列顺序
-- 如果 c 的等值过滤比 b 的范围更有选择性，考虑
INDEX idx_acb (a, c, b)
-- WHERE a = 1 AND c = 5 AND b > 10
-- 索引用了 a + c（等值），b 的范围扫描在最后，效率更高

-- config 表只有 20 行
SELECT * FROM config WHERE key_name = 'max_retries';
-- EXPLAIN: type=ALL, rows=20
-- 即使 key_name 上有索引，优化器仍选择全表扫描

-- 这是正确的优化决策！20 行的全表扫描可能只需 0.01ms
-- 走索引反而可能因为多一次 I/O 更慢

-- 不需要修复。接受优化器的判断。

4.1 失效场景速查表

5. 覆盖索引深度解析

5.1 什么是覆盖索引？

当查询的所有列（SELECT、WHERE、ORDER BY、GROUP BY 涉及的列）都包含在一个索引中时，InnoDB 可以直接从索引 B+Tree 返回结果，完全无需访问聚簇索引（无回表）。这种查询被称为覆盖索引查询（Covering Index）。

-- 索引 INDEX idx_user_status_amount (user_id, status, amount)

-- ✅ 覆盖索引：SELECT 的三列 + WHERE 的两列全在索引中
SELECT user_id, status, amount
FROM orders
WHERE user_id = 100 AND status = 'pending';

EXPLAIN 输出:
+----+-------+------+-----------------------+---------+-------+------+-------------+
| id | type  | key  | key_len               | ref     | rows  | filt | Extra       |
+----+-------+------+-----------------------+---------+-------+------+-------------+
|  1 | ref   | idx_user_status_amount | 42   | const,const | 15 | 100  | Using index |
+----+-------+------+-----------------------+---------+-------+------+-------------+
                                                                       ^^^^^^^^^^^
                                                                       关键标识！

EXPLAIN 中的 Extra 列：

• Using index — 覆盖索引，无回表
• Using index condition — Index Condition Pushdown (ICP)，在索引层做了过滤但仍需回表
• NULL（Extra 列为空）— 使用了索引定位，但需要回表获取完整行

5.2 覆盖索引的性能收益

覆盖索引的性能优势是数量级的，尤其在以下场景：

5.3 Index Condition Pushdown (ICP)

ICP 是 MySQL 5.6 引入的优化。在没有 ICP 之前，存储引擎只用索引的前缀列定位记录，然后将完整行传给 Server 层做进一步过滤。ICP 允许存储引擎在索引层就用索引中的其余列做过滤，减少回表次数。

-- 索引 INDEX idx_abc (a, b, c)
-- 查询：WHERE a = 1 AND c = 5（跳过了 b）

-- 无 ICP（MySQL 5.5 及以前）：
-- 1. 用 a=1 在索引中定位所有匹配行（可能 10000 行）
-- 2. 对每一行回表获取完整数据
-- 3. 在 Server 层过滤 c=5（可能只留 100 行）
-- 结果：10000 次回表，9900 次浪费

-- 有 ICP（MySQL 5.6+）：
-- 1. 用 a=1 在索引中定位所有匹配行
-- 2. 在索引层就检查 c=5（因为 c 在索引中）
-- 3. 只有通过 c=5 过滤的行才回表
-- 结果：只有 100 次回表

EXPLAIN SELECT * FROM t WHERE a = 1 AND c = 5;
-- Extra: Using index condition (表示 ICP 生效)

5.4 如何设计覆盖索引

设计覆盖索引的步骤：

1. 确定查询的所有涉及列：包括 SELECT、WHERE、ORDER BY、GROUP BY 中的所有列
2. 将 WHERE 中的等值条件列放在索引最前面
3. ORDER BY / GROUP BY 列紧随其后
4. SELECT 中只用于展示的列放在最后
5. 评估索引宽度：如果覆盖索引太宽（包含大量列），可能得不偿失——索引变大，写入变慢，Buffer Pool 占用更多

-- 原始查询：
SELECT user_id, order_date, total_amount
FROM orders
WHERE user_id = 100 AND status = 'completed'
ORDER BY order_date DESC
LIMIT 20;

-- 分析涉及的列：
-- WHERE: user_id, status (等值)
-- ORDER BY: order_date (DESC)
-- SELECT: user_id, total_amount (user_id 已在 WHERE 中)

-- 覆盖索引设计（等值 → 排序 → 展示）：
ALTER TABLE orders ADD INDEX idx_cover_user_orders
  (user_id, status, order_date DESC, total_amount);

-- EXPLAIN: Extra: Using index (覆盖索引，无回表，无 filesort)

6. Index Merge（索引合并）

正常情况下，MySQL 一次查询只使用一个索引。但在某些 OR / AND 条件下，优化器可以同时使用多个索引，将结果合并。这就是 Index Merge 优化。

6.1 三种 Index Merge 算法

6.2 Intersection（交集合并）

-- 有两个独立索引 INDEX idx_a (a), INDEX idx_b (b)

SELECT * FROM t WHERE a = 1 AND b = 2;
-- 优化器可能：
-- 1. 在 idx_a 中找到 a=1 的所有主键 → {1, 5, 8, 12, 15}
-- 2. 在 idx_b 中找到 b=2 的所有主键 → {3, 5, 9, 12, 20}
-- 3. 取交集 → {5, 12}
-- 4. 用 {5, 12} 去聚簇索引回表
-- EXPLAIN: type=index_merge, Extra: Using intersect(idx_a,idx_b)

6.3 Union（并集合并）

SELECT * FROM t WHERE a = 1 OR b = 2;
-- 1. 在 idx_a 中找到 a=1 的主键集合
-- 2. 在 idx_b 中找到 b=2 的主键集合
-- 3. 取并集（去重）
-- 4. 回表
-- EXPLAIN: type=index_merge, Extra: Using union(idx_a,idx_b)

6.4 Sort-Union（排序并集合并）

SELECT * FROM t WHERE a > 10 OR b > 20;
-- 与 Union 类似，但因为是范围条件，
-- 各索引返回的主键不是有序的，需要先排序再合并
-- EXPLAIN: type=index_merge, Extra: Using sort_union(idx_a,idx_b)

6.5 控制 Index Merge 行为

-- 查看当前设置
SELECT @@optimizer_switch\G
-- ...index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on...

-- 禁用 intersection（如果你发现它比联合索引更慢）
SET optimizer_switch = 'index_merge_intersection=off';

-- 通过 hint 控制
SELECT /*+ NO_INDEX_MERGE(t) */ * FROM t WHERE a = 1 AND b = 2;
SELECT /*+ INDEX_MERGE(t idx_a, idx_b) */ * FROM t WHERE a = 1 OR b = 2;

7. 基数（Cardinality）与选择性（Selectivity）

7.1 什么是基数？

基数（Cardinality） 是指索引列中不同值的数量估算。选择性 = 基数 / 总行数。选择性越接近 1，索引越有效。

-- 查看索引基数
SHOW INDEX FROM orders;
-- +--------+------------+-----------+--------------+-------------+-----------+
-- | Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Cardinality |
-- +--------+------------+-----------+--------------+-------------+-----------+
-- | orders |          0 | PRIMARY   |            1 | id          |    9823456 |
-- | orders |          1 | idx_user  |            1 | user_id     |     523467 |
-- | orders |          1 | idx_status|            1 | status      |          5 |
-- +--------+------------+-----------+--------------+-------------+-----------+

-- user_id 选择性：523467 / 9823456 ≈ 0.053 → 适中（每个用户约 19 个订单）
-- status 选择性：5 / 9823456 ≈ 0.0000005 → 极低（不适合单列索引）

7.2 InnoDB 如何估算基数

InnoDB 不会扫描全表来计算精确基数，而是采用随机采样：

1. 随机选取 innodb_stats_persistent_sample_pages（默认 20）个叶子页
2. 统计这些页中不同值的数量
3. 按比例推算整个索引的基数

-- 查看采样配置
SHOW VARIABLES LIKE 'innodb_stats%';
-- innodb_stats_persistent = ON        -- 持久化统计信息到磁盘
-- innodb_stats_persistent_sample_pages = 20  -- 采样页数
-- innodb_stats_auto_recalc = ON       -- DML 变更超过 10% 时自动重新计算
-- innodb_stats_transient_sample_pages = 8    -- 非持久化模式的采样页数

为什么基数有时不准？

• 采样 20 个页可能无法代表整体数据分布
• 数据分布不均匀时（如某些 user_id 有百万行，大多数只有几行），采样偏差更大
• 大批量 INSERT/DELETE 后，统计信息可能未及时更新

7.3 ANALYZE TABLE

-- 手动触发统计信息重新计算
ANALYZE TABLE orders;

-- 注意：
-- 1. ANALYZE TABLE 在 InnoDB 中是在线操作，不会阻塞读写
-- 2. 但在大表上仍需要一定时间（取决于采样页数）
-- 3. 会获取短暂的 read lock 用于采样

-- 增加采样精度（对大表或数据分布不均的表）
ALTER TABLE orders STATS_SAMPLE_PAGES = 100;
ANALYZE TABLE orders;
-- 采样 100 个页比默认的 20 个页更准确，但耗时也更长

7.4 错误基数导致糟糕的执行计划

-- 场景：orders 表 user_id 的实际基数是 50 万
-- 但 InnoDB 估算的基数只有 1000（采样偏差）
-- 优化器认为 user_id 索引选择性很低，选择全表扫描

EXPLAIN SELECT * FROM orders WHERE user_id = 12345;
-- type=ALL (应该是 ref 或 eq_ref)

-- 修复：
ANALYZE TABLE orders; -- 重新采样

-- 或者增加采样页数
ALTER TABLE orders STATS_SAMPLE_PAGES = 200;
ANALYZE TABLE orders;

-- 验证基数已更新
SHOW INDEX FROM orders WHERE Key_name = 'idx_user_id';

7.5 直方图（Histogram） — MySQL 8.0+

直方图记录了列值的分布信息，帮助优化器对非索引列或索引选择性估算做出更准确的判断。

-- 创建直方图（不需要索引）
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;
ANALYZE TABLE orders UPDATE HISTOGRAM ON total_amount WITH 64 BUCKETS;

-- 查看直方图信息
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,
       JSON_EXTRACT(histogram, '$.histogram-type') AS type,
       JSON_EXTRACT(histogram, '$.number-of-buckets-specified') AS buckets,
       JSON_EXTRACT(histogram, '$.sampling-rate') AS sample_rate
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE TABLE_NAME = 'orders';

-- 直方图类型：
-- singleton: 值的数量 ≤ bucket 数，每个 bucket 存一个精确值和频率
-- equi-height: 值的数量 > bucket 数，每个 bucket 覆盖一个值范围

-- 删除直方图
ANALYZE TABLE orders DROP HISTOGRAM ON status;

直方图 vs 索引：

8. 索引设计方法论

8.1 系统化设计流程

1. 收集查询模式
   • 分析慢查询日志（slow_query_log）
   • 检查 performance_schema.events_statements_summary_by_digest 中的高频查询
   • 与开发团队确认核心业务查询
2. 对每个查询，识别关键要素
   • WHERE 中的等值条件列
   • WHERE 中的范围条件列
   • JOIN 的关联列
   • ORDER BY / GROUP BY 列
   • SELECT 的列（是否可以覆盖索引）
3. 应用三星索引法则（见下节）
4. 合并索引：多个查询如果可以共用一个联合索引（利用最左前缀），优先合并而不是创建多个索引
5. 评估写入开销：每个索引都增加写入时的 B+Tree 维护成本
6. 上线后验证：用 EXPLAIN 验证查询确实使用了索引，监控索引使用率

8.2 三星索引系统（Three-Star Index System）

Lahdenmaki & Leach 在《Relational Database Index Design and the Optimizers》中提出的索引设计评估方法：

-- 查询：
SELECT user_id, order_date, total_amount
FROM orders
WHERE user_id = 100 AND status = 'completed'
ORDER BY order_date DESC
LIMIT 20;

-- ★☆☆ 一星索引（只满足过滤）：
INDEX (user_id)

-- ★★☆ 二星索引（过滤 + 排序）：
INDEX (user_id, status, order_date DESC)

-- ★★★ 三星索引（过滤 + 排序 + 覆盖）：
INDEX (user_id, status, order_date DESC, total_amount)

8.3 索引开销分析

写放大（Write Amplification）

每个索引在 INSERT/UPDATE/DELETE 时都需要额外维护：

• INSERT：除了写入聚簇索引，每个二级索引都需要插入一条记录
• DELETE：每个二级索引都需要标记删除（标记后由 purge 线程清理）
• UPDATE 索引列：等效于 DELETE + INSERT（对每个涉及的索引）

-- 假设一个表有 5 个二级索引
-- 一次 INSERT 需要写入：
-- 1 次聚簇索引写 + 5 次二级索引写 = 6 次 B+Tree 插入操作
-- 加上 redo log、undo log、doublewrite buffer...
-- 实际放大倍数可能达 10-20x

-- Change Buffer 优化：
-- 对于非唯一的二级索引，InnoDB 使用 Change Buffer 将写操作缓存在内存中
-- 后续读到该页时再 merge，减少随机 I/O
-- 可通过 innodb_change_buffer_max_size 调整（默认 25% 的 Buffer Pool）

存储成本

-- 查看每个索引的存储大小
SELECT
  TABLE_NAME,
  INDEX_NAME,
  ROUND(STAT_VALUE * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'your_db'
  AND stat_name = 'size'
ORDER BY STAT_VALUE DESC;

8.4 何时不应该加索引

• 极小表（< 1000 行）：全表扫描只需一次 I/O，索引反而增加开销
• 极低选择性列（如 boolean、gender）：除非作为联合索引的一部分
• 频繁大批量更新的列：索引维护代价可能超过查询收益
• 极少被查询的表（如审计日志只写不读）：写入密集型场景索引是纯负担
• 已有索引可以满足：已有 INDEX (a, b, c)，不需要再建 INDEX (a) 或 INDEX (a, b)
• 全表总行数 * 选择性 > 总行数的 20-30%：优化器通常选择全表扫描

9. 实战案例

9.1 电商订单表（1 亿行）

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  shop_id INT UNSIGNED NOT NULL,
  order_no VARCHAR(32) NOT NULL,
  status TINYINT NOT NULL DEFAULT 0 COMMENT '0-待付款 1-已付款 2-已发货 3-已完成 4-已取消',
  total_amount DECIMAL(12,2) NOT NULL,
  pay_time DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  INDEX idx_user_status_created (user_id, status, created_at),
  INDEX idx_shop_created (shop_id, created_at),
  INDEX idx_pay_time (pay_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

业务查询分析

-- 查询1：用户查看自己的订单列表（分页）
-- 高频查询，QPS > 5000
SELECT id, order_no, status, total_amount, created_at
FROM orders
WHERE user_id = ? AND status = ?
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
-- 使用索引：idx_user_status_created（★★ 二星：过滤 + 排序）
-- 要达到 ★★★ 需要覆盖 order_no 和 total_amount

-- 优化为三星索引：
ALTER TABLE orders ADD INDEX idx_user_orders_cover
  (user_id, status, created_at DESC, order_no, total_amount);
-- 然后可以删除 idx_user_status_created（被新索引的最左前缀覆盖）

-- 查询2：后台按时间范围查订单
-- 低频查询，QPS < 10
SELECT * FROM orders
WHERE created_at BETWEEN '2026-03-01' AND '2026-03-31'
  AND status = 3
ORDER BY created_at DESC
LIMIT 50;

-- 当前无合适索引。created_at 不在任何索引的最左位置
-- 方案1：新建索引
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
-- status 等值 + created_at 范围/排序，两星

-- 方案2：如果不想新增索引（写入开销考虑），接受使用 idx_pay_time 的近似扫描
-- 低频查询可以容忍稍慢的响应

-- 查询3：统计商家的订单金额
SELECT shop_id,
       COUNT(*) AS order_count,
       SUM(total_amount) AS total_revenue
FROM orders
WHERE shop_id = ? AND created_at >= '2026-01-01'
GROUP BY shop_id;
-- 使用索引：idx_shop_created
-- 但需要回表获取 total_amount
-- 优化：
ALTER TABLE orders ADD INDEX idx_shop_created_amount
  (shop_id, created_at, total_amount);
-- 覆盖索引，三星

深翻页优化

-- ❌ 深翻页性能灾难：OFFSET 100000
SELECT * FROM orders WHERE user_id = 100
ORDER BY created_at DESC LIMIT 20 OFFSET 100000;
-- MySQL 需要扫描前 100020 行然后丢弃前 100000 行

-- ✅ 方案1：游标分页（推荐）
-- 前端传上一页最后一条的 created_at 和 id
SELECT * FROM orders
WHERE user_id = 100
  AND (created_at, id) < ('2026-01-15 10:30:00', 99850)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- 利用索引直接定位到游标位置，无需扫描前面的行

-- ✅ 方案2：延迟关联（deferred join）
SELECT o.* FROM orders o
INNER JOIN (
  SELECT id FROM orders
  WHERE user_id = 100
  ORDER BY created_at DESC
  LIMIT 20 OFFSET 100000
) t ON o.id = t.id;
-- 子查询在索引上完成 OFFSET 跳过（覆盖索引，只读取 id）
-- 外层只回表 20 行

9.2 用户活动日志表（时间范围查询）

CREATE TABLE activity_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  action VARCHAR(50) NOT NULL,
  target_type VARCHAR(30),
  target_id BIGINT UNSIGNED,
  ip VARCHAR(45),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_user_created (user_id, created_at),
  INDEX idx_action_created (action, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
  PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
  PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 查询用户最近 7 天的操作记录
SELECT action, target_type, target_id, created_at
FROM activity_log
WHERE user_id = 12345
  AND created_at >= NOW() - INTERVAL 7 DAY
ORDER BY created_at DESC
LIMIT 50;

-- 使用 idx_user_created：user_id 等值 + created_at 范围/排序
-- 分区裁剪：只扫描 p202604 分区

-- 覆盖索引优化：
ALTER TABLE activity_log ADD INDEX idx_user_cover
  (user_id, created_at, action, target_type, target_id);

9.3 社交 Feed（复杂 JOIN + 分页）

-- 用户关注的人的最新动态（典型 Feed 流查询）
CREATE TABLE follows (
  follower_id BIGINT UNSIGNED NOT NULL,
  following_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (follower_id, following_id),
  INDEX idx_following (following_id)
) ENGINE=InnoDB;

CREATE TABLE posts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  content TEXT,
  media_urls JSON,
  like_count INT UNSIGNED DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_user_created (user_id, created_at DESC)
) ENGINE=InnoDB;

-- Feed 查询（拉模式）
SELECT p.id, p.user_id, p.content, p.like_count, p.created_at
FROM posts p
INNER JOIN follows f ON f.following_id = p.user_id
WHERE f.follower_id = 12345
  AND p.created_at > NOW() - INTERVAL 7 DAY
ORDER BY p.created_at DESC
LIMIT 20;

-- 执行流程：
-- 1. follows 表：用 PRIMARY KEY (follower_id, following_id)
--    找到 follower_id=12345 的所有 following_id（假设 200 人）
-- 2. posts 表：对每个 following_id，用 idx_user_created
--    找到最近 7 天的帖子
-- 3. 合并排序，取 TOP 20

-- 优化关键：
-- follows 表的 PRIMARY KEY 已经是最优设计（follower_id 在前）
-- posts 表的 idx_user_created (user_id, created_at DESC) 完美匹配
-- 如果需要覆盖索引：
ALTER TABLE posts ADD INDEX idx_feed_cover
  (user_id, created_at DESC, id, content(100), like_count);
-- 注意：content 是 TEXT 类型，只能前缀索引，无法完全覆盖
-- 实际生产中通常接受回表，或使用缓存层

10. 索引监控

10.1 查找未使用的索引

-- 方法1：sys.schema_unused_indexes（推荐）
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_db';

-- 输出示例：
-- +---------------+--------------+-------------------+
-- | object_schema | object_name  | index_name        |
-- +---------------+--------------+-------------------+
-- | your_db       | orders       | idx_old_campaign  |
-- | your_db       | users        | idx_tmp_debug     |
-- +---------------+--------------+-------------------+

-- 注意：
-- 1. 这个视图基于 performance_schema，需要 MySQL 重启后才能清零统计
-- 2. 某些索引可能只在月末报表或年度审计时使用，需要观察足够长的时间
-- 3. UNIQUE KEY 即使从未用于查询也有数据完整性作用，不要轻易删除

10.2 查找冗余索引

-- sys.schema_redundant_indexes 视图
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'your_db';

-- 输出示例：
-- +---------------+------------+----------------------+--------------------+
-- | table_schema  | table_name | redundant_index_name | dominant_index_name |
-- +---------------+------------+----------------------+--------------------+
-- | your_db       | orders     | idx_user_id          | idx_user_status    |
-- +---------------+------------+----------------------+--------------------+
-- idx_user_id (user_id) 被 idx_user_status (user_id, status) 的最左前缀覆盖

-- 也可以用 pt-duplicate-key-checker (Percona Toolkit)
-- pt-duplicate-key-checker --host=localhost --user=root --password=xxx

10.3 索引使用统计

-- performance_schema.table_io_waits_summary_by_index_usage
SELECT
  OBJECT_SCHEMA,
  OBJECT_NAME,
  INDEX_NAME,
  COUNT_STAR AS total_io,
  COUNT_READ AS reads,
  COUNT_WRITE AS writes,
  COUNT_FETCH AS fetches,
  COUNT_INSERT AS inserts,
  COUNT_UPDATE AS updates,
  COUNT_DELETE AS deletes
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_db'
  AND INDEX_NAME IS NOT NULL
ORDER BY COUNT_STAR DESC;

10.4 索引碎片率检查

-- 查看表和索引的空间使用情况
SELECT
  TABLE_NAME,
  INDEX_NAME,
  STAT_VALUE AS pages,
  ROUND(STAT_VALUE * 16 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'your_db'
  AND stat_name = 'size'
ORDER BY STAT_VALUE DESC;

-- 查看数据和索引的总空间
SELECT
  TABLE_NAME,
  ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
  ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
  ROUND(DATA_FREE / 1024 / 1024, 2) AS free_mb,
  ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE) * 100, 1) AS frag_pct
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db'
ORDER BY DATA_LENGTH + INDEX_LENGTH DESC;

-- 碎片率 > 20% 时考虑重建
ALTER TABLE orders ENGINE=InnoDB; -- 在线重建（MySQL 8.0）
-- 或
OPTIMIZE TABLE orders; -- 等效于 ALTER TABLE + ANALYZE TABLE

11. 索引大小计算器

估算添加新索引后的存储空间增长。输入表行数和索引列信息，计算预估的索引大小。

12. 常见问题 (FAQ)

ALTER TABLE orders ADD INDEX idx_new (col1, col2), ALGORITHM=INPLACE, LOCK=NONE;