MySQL 索引优化完全指南

MySQL 索引优化完全指南

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

**适用版本：**本章内容基于 MySQL 8.0 / 8.4 LTS（InnoDB 引擎）。涉及 8.0 新特性（降序索引、不可见索引、函数索引、直方图）会特别标注。早期版本（5.7 及以下）的差异也会说明。

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 索引的物理结构如下：

                          [Root Page]
                 /            |            \
           [Internal]    [Internal]    [Internal]
          /    |    \   /    |    \   /    |    \
      [Leaf] [Leaf] [Leaf] [Leaf] [Leaf] [Leaf] [Leaf]
        |----->|----->|----->|----->|----->|----->|
        |<-----|<-----|<-----|<-----|<-----|<-----|

      叶子节点通过双向链表连接（按 key 排序）

每个 Page 的内部结构： +--------------------------------------------------+ | Page Header (38 bytes) | | - Page Number, Previous Page, Next Page | | - Page Type, Space ID, LSN | +--------------------------------------------------+ | Infimum Record (最小虚拟记录) | | Supremum Record (最大虚拟记录) | +--------------------------------------------------+ | User Records (按 key 排序的实际索引记录) | | Record 1 → Record 2 → Record 3 → ... | | (单链表，通过 next_record 偏移量连接) | +--------------------------------------------------+ | Free Space (可用空间) | +--------------------------------------------------+ | Page Directory (槽位数组，用于页内二分查找) | +--------------------------------------------------+ | Page Trailer (8 bytes, checksum) | +--------------------------------------------------+

页内查找过程

在一个 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 中，表就是聚簇索引，聚簇索引就是表。它们是同一份数据的不同说法。.ibd 文件里的数据就是按主键 B+Tree 组织的。

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

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

**永远显式定义主键！**隐藏的 DB_ROW_ID 是全局自增的（不是表级），在高并发插入场景下会成为全局互斥锁的瓶颈。而且你无法在 WHERE 中使用它，等于白白浪费了聚簇索引的能力。

1.5 二级索引（Secondary Index）

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

聚簇索引（PRIMARY KEY = id）：

[Root: ..., 50, 100, ...]
     /        |        \

[Leaf: id=1..49] [Leaf: id=50..99] [Leaf: id=100..149] 每个叶子存完整行： 每个叶子存完整行： id=1, name='Alice' id=50, name='Zara' id=2, name='Bob' id=51, name='Yuki' ... ...

二级索引（INDEX idx_name (name)）：

[Root: ..., 'John', 'Mike', ...]
     /          |          \

[Leaf: 'A'..'J'] [Leaf: 'J'..'M'] [Leaf: 'M'..'Z'] 每个叶子存： 每个叶子存： name='Alice',id=1 name='John',id=35 name='Bob',id=2 name='Kate',id=88 ... ...

回表查询（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 路径）

**回表的代价：**每一行都需要一次独立的聚簇索引随机 I/O。如果二级索引扫描返回 1000 行，最坏情况下需要 1000 次额外的随机 I/O。这就是为什么当选择性低（返回大量行）时，优化器可能选择全表扫描而非使用索引——顺序扫描反而更快。

1.6 页分裂与页合并

页分裂（Page Split）

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

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

页分裂的代价：

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

**UUID 主键导致的灾难：**随机的 UUID（如 UUID()）作为主键时，新行的插入位置是随机的，几乎每次插入都会触发页分裂。相比之下，自增主键（AUTO_INCREMENT）总是追加到最后一个叶子页，几乎不会分裂。在高写入场景下，UUID 主键的写入性能可能比自增主键差 3-5 倍。

解决方案对比：

页合并（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 中每个索引记录的存储格式如下：

索引记录（Index Record）格式：

+------------------+------------------+-------------------+ | 变长列长度列表 | NULL 标志位 | 记录头信息 (5 B) | | (逆序存储) | (每个可 NULL 列 | - delete_flag | | | 占 1 bit) | - min_rec_flag | | | | - n_owned | | | | - heap_no | | | | - record_type | | | | - next_record | +------------------+------------------+-------------------+ | 列1 值 | 列2 值 | ... | 主键值（二级索引）/ 行数据（聚簇索引） | +--------+--------+-----+-----------------------------------------+

存储开销要点：

• 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%，足够了

前缀索引的限制：前缀索引不能用作覆盖索引（因为索引中没有完整值），也不能用于 ORDER BY 和 GROUP BY。只在存储空间敏感、列很长的场景下使用。

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)

何时需要降序索引？只有当 ORDER BY 中的多个列方向不同（一个 ASC 一个 DESC）时才需要。如果所有列方向相同，正序或反序索引都可以通过正向或反向扫描满足。

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';

实现原理：MySQL 8.0 的函数索引本质上是隐藏的虚拟生成列 + 普通索引。ALTER TABLE t ADD INDEX ((expr)) 等价于 ALTER TABLE t ADD COLUMN hidden_col TYPE GENERATED ALWAYS AS (expr) VIRTUAL, ADD INDEX (hidden_col)。

3. 最左前缀原则

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

假设有联合索引：

INDEX idx_abc (a, b, c)

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

idx_abc (a, b, c) 的 B+Tree 叶子节点数据（示意）：

| a=1, b=1, c=1 | → | a=1, b=1, c=3 | → | a=1, b=2, c=1 | → | a=1, b=2, c=5 | → | a=2, b=1, c=2 | → | a=2, b=1, c=4 | → | a=2, b=3, c=1 | → | a=3, b=1, c=1 |

观察：

• a 列是全局有序的：1, 1, 1, 1, 2, 2, 2, 3
• b 列只在同一个 a 值内有序
• c 列只在同一个 (a, b) 值内有序

3.1 12 个查询示例

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

1 WHERE a = 1

能使用索引。命中最左列 a，B+Tree 可以精确定位。key_len 只包含 a 列。

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

2 WHERE a = 1 AND b = 2

能使用索引。命中 a 和 b 两列。

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

3 WHERE a = 1 AND b = 2 AND c = 3

能使用索引。三列全部命中。

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

4 WHERE b = 2

不能使用索引。跳过了最左列 a。在索引中 b 不是全局有序的，无法利用 B+Tree。

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

5 WHERE b = 2 AND c = 3

不能使用索引。缺少最左列 a。

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

6 WHERE a = 1 AND c = 3

部分使用索引。使用 a 列过滤后，因为跳过了 b，c 列无法用索引过滤（在 a=1 的范围内，c 不是有序的）。但 MySQL 8.0 的 Index Condition Pushdown (ICP) 可以在索引层过滤 c，减少回表次数。

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)

7 WHERE a = 1 ORDER BY b

能使用索引。a 等值过滤后，在 a=1 的范围内 b 已经有序，无需额外排序。

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

8 WHERE a = 1 ORDER BY c

部分使用索引。a 用于过滤，但在 a=1 范围内 c 不是有序的（因为 b 不同），需要 filesort。

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

9 WHERE a = 1 AND b = 2 ORDER BY c

能使用索引。a=1, b=2 等值过滤后，c 在该范围内是有序的。

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

10 WHERE a > 1 AND b = 2

部分使用索引。a 列用于范围扫描，但范围条件之后的列 b 无法继续使用索引精确查找（因为在 a 的不同值中，b 不是连续有序的）。

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

**范围条件断点规则：**联合索引中，遇到 >、<、BETWEEN、LIKE 'xxx%' 等范围条件后，该列之后的所有索引列都无法用于精确查找。等值条件（=、IN）不会中断。

11 WHERE a IN (1, 2) AND b = 2 AND c = 3

能使用完整索引。IN 相当于多个等值条件的 OR，MySQL 优化器会将其转化为多个 ref 查找或 range 扫描，不会中断后续列的索引使用。

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

12 WHERE a = 1 AND b > 5 AND c = 3

部分使用索引。a 等值 + b 范围，到 b 的范围条件就中断了，c 无法用于索引查找。但 ICP 可以在索引层过滤 c。

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 输出。

**核心原则：**索引失效的根本原因只有两个——(1) 查询条件无法匹配 B+Tree 的排序结构；(2) 优化器判断使用索引不如全表扫描高效。理解这两个原因，就能理解所有失效场景。

1 对索引列使用函数

**问题：**在 WHERE 条件中对索引列应用函数，破坏了 B+Tree 的排序结构。

-- 有索引 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

**原理：**索引中存储的是 created_at 的原始值（如 2026-03-15 14:30:00），按原始值排序。YEAR(created_at) 的结果没有存在索引中，B+Tree 无法按函数结果查找。

2 隐式类型转换

**问题：**WHERE 条件中，列的类型与比较值的类型不匹配，MySQL 自动进行类型转换，等效于对列施加了函数。

-- 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 隐式转换规则：当字符串列与数字比较时，MySQL 将字符串转为数字（而不是数字转为字符串）。这相当于对索引列施加了 CAST 函数。反过来，如果是数字列与字符串比较（WHERE int_col = '123'），MySQL 将字符串转为数字，不影响索引使用。

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

3 LIKE 以通配符开头

问题：LIKE '%xxx' 或 LIKE '%xxx%' 无法使用 B+Tree 索引（因为不知道前缀，无法确定 B+Tree 搜索起点）。

-- 有索引 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

替代方案：

• FULLTEXT 索引 + MATCH...AGAINST
• 将搜索场景交给 Elasticsearch
• 对于固定模式（如后缀搜索），可以新增一列存储反转字符串：REVERSE(name)，然后对反转列做右模糊

4 OR 条件混合有索引和无索引的列

**问题：**OR 的两边，一边有索引一边没有，优化器只能全表扫描。

-- 有索引 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 != '张三';

5 NOT IN / NOT EXISTS / != / <>

**问题：**否定条件通常无法有效利用 B+Tree 的范围扫描能力。

-- 有索引 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');

6 索引列参与运算表达式

**问题：**与函数类似，索引列参与数学运算时，B+Tree 无法直接匹配。

-- 有索引 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

7 JOIN 时字符集不匹配

**问题：**两个表 JOIN 时，关联列的字符集（charset）或排序规则（collation）不同，MySQL 需要在运行时转换，等效于对列施加了 CONVERT 函数。

-- 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

**这是生产环境最隐蔽的性能陷阱之一。**很多团队在不同时期建的表使用了不同的字符集，JOIN 时关联列的字符集不匹配导致全表扫描，且只在数据量增大后才暴露。建议用以下 SQL 检查全库的字符集一致性：

-- 检查所有列的字符集
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;

8 低基数（Low Cardinality）— 优化器选择全表扫描

**问题：**索引的选择性太低（如 gender 列只有 M/F 两个值），使用索引后还要大量回表，优化器评估后选择全表扫描更快。

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

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

什么时候低基数索引有用？

• 与其他高选择性列组合成联合索引：INDEX (gender, city)
• 用于覆盖索引（无需回表）
• 数据分布极度不均匀：90% 是 'M'，只需要查 'F'（10%），这时索引对查 'F' 有效

9 ORDER BY 方向与索引方向不匹配

**问题：**联合索引的排序方向与 ORDER BY 不一致（一个 ASC 一个 DESC），在 MySQL 5.7 及以下版本无解。

-- 索引 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

**索引正向扫描 vs 反向扫描：**B+Tree 支持正向（前 → 后）和反向（后 → 前）扫描。所以 ORDER BY a ASC 和 ORDER BY a DESC 都可以用一个 ASC 索引，只是扫描方向不同。但 ORDER BY a ASC, b DESC 时，两列方向不同，单一方向的索引无法满足。

10 SELECT * 未利用覆盖索引

**问题：**本可以通过覆盖索引在索引层直接返回结果，但 SELECT * 需要所有列，必须回表。

-- 有索引 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

11 范围条件中断后续索引列

**问题：**联合索引中，范围条件（>、<、BETWEEN、LIKE 'xx%'）之后的列无法继续使用索引做精确查找。

-- 索引 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 的范围扫描在最后，效率更高

**设计技巧：**等值条件列放前面，范围条件列放最后。这条规则与最左前缀原则结合，是联合索引设计的核心策略。

12 小表 — 优化器判定全表扫描更快

**问题：**当表的数据量非常小（几十到几百行），优化器可能认为全表扫描（一次顺序 I/O 就能读完全表）比走索引（先查索引页再查数据页，两次 I/O）更高效。

-- 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 生效)

**ICP 的源码位置：**在 MySQL 源码中，ICP 的实现在 handler::pushed_idx_cond 和 ha_innobase::index_read 中。InnoDB 存储引擎在读取每条索引记录后，先检查 pushed condition，不满足则跳过，不回表。

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)

**Index Merge Intersection 通常意味着缺少联合索引。**如果看到 Using intersect，99% 的情况下创建联合索引 INDEX (a, b) 比 Index Merge 更高效。联合索引一次 B+Tree 查找就能定位结果，而 Intersection 需要两次 B+Tree 查找 + 排序合并。

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）
2. 检查 performance_schema.events_statements_summary_by_digest 中的高频查询
3. 与开发团队确认核心业务查询
4. 对每个查询，识别关键要素 WHERE 中的等值条件列
5. WHERE 中的范围条件列
6. JOIN 的关联列
7. ORDER BY / GROUP BY 列
8. SELECT 的列（是否可以覆盖索引）
9. 应用三星索引法则（见下节）
10. 合并索引：多个查询如果可以共用一个联合索引（利用最左前缀），优先合并而不是创建多个索引
11. 评估写入开销：每个索引都增加写入时的 B+Tree 维护成本
12. 上线后验证：用 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. 索引大小计算器

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

  表总行数





  主键类型

    INT (4 bytes)
    BIGINT (8 bytes)
    BINARY(16) / UUID (16 bytes)
    VARCHAR(36) UUID string (36 bytes)

索引列（每行一列，格式：类型 长度） BIGINT TINYINT DATETIME

支持的类型：INT, BIGINT, TINYINT, SMALLINT, MEDIUMINT, DATETIME, TIMESTAMP, DATE, FLOAT, DOUBLE, DECIMAL, CHAR(n), VARCHAR(n), BINARY(n), VARBINARY(n)。联合索引请输入所有列。

计算索引大小

12. 常见问题 (FAQ)

Q1: 一个表最多可以创建多少个索引？

InnoDB 的技术上限是 64 个二级索引（加上 1 个聚簇索引）。每个索引最多包含 16 个列。但实际生产中，一个表超过 5-7 个索引就应该审视是否有冗余。索引越多，写入越慢。

Q2: 联合索引的列顺序到底怎么决定？

核心原则：等值条件列 → 排序列 → 范围条件列。在等值条件列中，选择性高的放前面（但差异不大时也可以按业务语义排列）。范围条件列永远放在最后，因为范围条件会中断后续列的索引使用。如果需要覆盖索引，SELECT 中的展示列追加在最后。

Q3: 主键应该用自增 ID 还是 UUID？

绝大多数场景推荐自增 BIGINT。原因：(1) 顺序插入，不会导致页分裂；(2) 8 字节固定长度，二级索引更小；(3) 比较速度最快。如果需要分布式唯一 ID，推荐 UUID v7 / ULID / Snowflake——它们是时间有序的，兼顾唯一性和顺序性。避免使用完全随机的 UUID v4 作为主键。

Q4: EXPLAIN 中 key_len 怎么计算？

key_len 表示索引中实际使用了多少字节。计算规则：INT=4, BIGINT=8, DATETIME=5, TIMESTAMP=4, DATE=3, CHAR(n)=n字符集最大字节(utf8mb4=4), VARCHAR(n)=n字符集最大字节+2(长度存储)。如果列允许 NULL，额外+1。通过 key_len 可以判断联合索引用了几列。

Q5: 加索引会锁表吗？

MySQL 8.0 的 Online DDL 对大多数索引操作支持 ALGORITHM=INPLACE, LOCK=NONE，即不锁表。创建/删除二级索引、添加列等操作都可以在线进行。但以下操作仍然需要短暂的排他锁：ALTER PRIMARY KEY、变更列类型（某些情况）。建议始终显式指定 LOCK=NONE，如果不支持会直接报错，而不是静默锁表。 ALTER TABLE orders ADD INDEX idx_new (col1, col2), ALGORITHM=INPLACE, LOCK=NONE;

Q6: 为什么 EXPLAIN 显示可能使用索引但实际没用？

EXPLAIN 的 possible_keys 列显示理论上可用的索引，key 列显示实际选择的索引。两者不同的原因：(1) 优化器的代价估算认为全表扫描更快（数据量小或选择性低）；(2) 基数估算不准确——用 ANALYZE TABLE 更新统计信息；(3) 可以用 FORCE INDEX(idx_name) 强制使用，但仅用于诊断，生产代码不建议 FORCE INDEX。

Q7: 索引碎片怎么处理？

大量随机 INSERT/DELETE 后，B+Tree 页的空间利用率下降（页内有大量空洞），这就是索引碎片。查看碎片率：SELECT DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE) FROM INFORMATION_SCHEMA.TABLES。碎片率超过 20-30% 时考虑重建：ALTER TABLE t ENGINE=InnoDB（在线操作）或 OPTIMIZE TABLE t。但注意：重建会产生大量 I/O 和 redo log，建议在低峰期执行。

Q8: 如何判断一个索引是否值得保留？

综合以下数据判断：(1) sys.schema_unused_indexes 查看是否有查询使用它；(2) performance_schema.table_io_waits_summary_by_index_usage 查看读写比例——如果写远多于读，可能不值得；(3) sys.schema_redundant_indexes 检查是否被其他索引的最左前缀覆盖。删除前先设为 INVISIBLE 观察一周（MySQL 8.0+）。

Q9: InnoDB 索引和 MyISAM 索引有什么区别？

InnoDB 使用聚簇索引（数据和主键索引一体化），二级索引叶子存主键值。MyISAM 所有索引都是非聚簇的，叶子节点存储的是行在数据文件中的物理地址（行指针）。因此 MyISAM 的二级索引查找无需回表到另一棵 B+Tree，但主键查找没有 InnoDB 快。另外 MyISAM 不支持事务、行锁和外键，现代应用几乎不再使用 MyISAM。

Q10: MySQL 8.0 的索引新特性有哪些？

主要新特性：(1) 降序索引——真正支持 DESC 存储，优化混合排序查询；(2) 不可见索引——安全测试删除索引的影响；(3) 函数索引——对表达式建索引，解决函数导致索引失效的问题；(4) 直方图统计——更精确的代价估算，无需建索引；(5) HASH JOIN——无索引的 JOIN 性能大幅提升；(6) Index Skip Scan——某些场景下跳过联合索引最左列。