索引原理与实战

索引是 InnoDB 性能的地基：查询快不快、锁重不重、深分页翻不翻车，几乎都能追溯到"索引建得对不对"。本章从 B+ 树的 IO 账算起，讲清主键怎么选、联合索引为什么讲究列顺序，再落到覆盖索引、索引下推、前缀索引与一整份失效清单，最后用六个实战案例串起来。版本基线 MySQL 8.4 LTS（8.4.13），存储引擎默认 InnoDB。

1. 为什么需要索引

1.1 先看一笔 IO 账

假设 orders 表有 1000 万行，平均每行 1KB，数据总量约 10GB。InnoDB 以 16KB 页为最小读写单位，这张表约 64 万个页。

现在执行一条没有索引的查询：

SELECT * FROM orders WHERE user_id = 10086;

无索引 = 全表扫描：
  64 万个页 × 16KB 全部读一遍，逐行比对 user_id
  机械盘顺序读 ~200MB/s → 约 50 秒
  NVMe SSD ~2GB/s       → 约 5 秒，仍远慢于"毫秒级"的接口预期

有 B+ 树索引后：

三层树点查：
  根节点（通常常驻内存）→ 定位中间页 → 定位叶子页
  实际物理读通常 1~3 个页，每页 16KB
  数据在内存中时是纯 CPU 操作，纳秒~微秒级

索引的本质是把"IO 次数"从与表大小成正比，压到与树高成正比。 表涨一百倍，树高只涨一层，这就是 B+ 树的价值。

1.2 为什么是 B+ 树：选型对比

1.3 B+ 树胜出的三个原因

1. 矮：非叶节点只存"键 + 子页指针"，不存数据，单个 16KB 页能放一千多条指针，扇出极大。
2. 数据全在叶子层：任何一次查询都走到同一层，路径长度稳定；点查性能可预期。
3. 叶子节点是双向有序链表：WHERE id BETWEEN 100 AND 200、ORDER BY 这类需求沿着链表顺序扫即可，不需要回到上层再遍历。

数据库页面对齐磁盘块、范围查询高频出现，这两点决定了"矮、胖、叶子有序链表"的 B+ 树是磁盘索引的最优解。

2. 本章业务表与最小 EXPLAIN 阅读能力

2.1 贯穿全章的业务表

后面所有示例基于电商四表（orders 千万级、users 百万级）：

CREATE TABLE users (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username    VARCHAR(64)  NOT NULL,
    password    VARCHAR(128) NOT NULL,
    real_name   VARCHAR(64)  NOT NULL DEFAULT '',
    age         TINYINT      NOT NULL DEFAULT 0,
    mobile      VARCHAR(20)  NOT NULL DEFAULT '',
    city        VARCHAR(32)  NOT NULL DEFAULT '',
    status      TINYINT      NOT NULL DEFAULT 1,   -- 1 正常 0 冻结
    create_time DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_username (username)
) ENGINE = InnoDB;

CREATE TABLE orders (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_no    VARCHAR(32) NOT NULL,
    user_id     BIGINT UNSIGNED NOT NULL,
    status      TINYINT NOT NULL DEFAULT 0,  -- 0 待付款 1 已付款 2 已发货 3 完成 4 取消
    amount      DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    remark      VARCHAR(255) NOT NULL DEFAULT '',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_order_no (order_no)
) ENGINE = InnoDB;

CREATE TABLE order_items (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id    BIGINT UNSIGNED NOT NULL,
    product_id  BIGINT UNSIGNED NOT NULL,
    quantity    INT NOT NULL DEFAULT 1,
    price       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE = InnoDB;

CREATE TABLE products (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name        VARCHAR(128) NOT NULL,
    category_id INT NOT NULL DEFAULT 0,
    brand       VARCHAR(64) NOT NULL DEFAULT '',
    price       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    stock       INT NOT NULL DEFAULT 0,
    extra       JSON NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE = InnoDB;

2.2 EXPLAIN 只带四列上路

本章只需要会看四列，完整读法（cost、访问序列、8.0.18 起的 EXPLAIN ANALYZE）全部在 08-EXPLAIN与查询优化.md 展开。

• type：访问方式的级别，从好到差大致是 const > eq_ref > ref > range > index > ALL——等值命中唯一键、等值命中主键/唯一键 JOIN、等值走普通索引、索引范围扫描、扫整棵索引树、全表扫描。大表查询看到 ALL 就该警觉。
• key：实际用到的索引；为 NULL 说明没用任何索引。
• key_len：索引用掉了多少字节——判断联合索引"吃到第几列"的硬指标。
• Extra：执行细节。Using index = 覆盖索引不回表；Using index condition = 发生了索引下推（见 §8）；Using where = 存储引擎返回后还要逐行过滤；Using filesort / Using temporary = 额外排序或临时表，大结果集下是危险信号。

rows 列是基于统计信息的估算值，别当精确行数用（§14）。key_len 常用换算（utf8mb4，可空再 +1 字节）：

注意：VARCHAR 按定义长度而非实际长度计，还带 2 字节变长头；上表是本章示例够用的最小集，完整规则（NULL 标志位、复合索引逐列累加）见 08。

3. 一棵 B+ 树到底能存多少行

3.1 完整算术

条件：16KB 页、主键 BIGINT（8B）、平均每行 1KB。页头页尾与 slot 目录要吃掉约 1KB，按可用 15KB 估算。

非叶节点：每条 = 键(8B) + 子页指针(4B) + 记录开销 ≈ 14B，单页可放 15KB / 14B ≈ 1000 条指针，扇出记作 1000。

叶子节点：每条 = 完整行(1KB)，单页可放 15KB / 1KB ≈ 15 行，取整按 16 行算（行有大小差异，数量级不变）。

3.2 “单表不超过 500 万/2000 万行"的真实来源

这句经验值的本意是：让树高尽量不超过 3 层，而不是行数本身有什么魔法。三点推论：

• 行数不是硬指标，行宽和主键宽度才是。上例假设行 1KB；如果行平均 4KB，叶子每页只剩 4 行，三层树只能撑约 400 万行。
• 主键变宽，扇出被腰斩。主键若是 VARCHAR(32) utf8mb4，非叶节点每条约 130B+，扇出从 1000 掉到 100 出头，三层树容量暴跌到百万级——这是 §5 反对字符串主键的算术依据。
• 树高 4 层并不等于灾难：根和第二层几乎永远在 buffer pool 里，真实物理读仍然只有一次。经验值防的是"随机 IO × 并发"的叠加，不是纸面层数。

结论：判断"要不要拆表”，先看单行大小、主键宽度、查询 IO 模式，再看行数。

4. InnoDB 的两种索引

4.1 聚簇索引：主键即数据

InnoDB 的表本身就是一棵按主键组织的 B+ 树，叶子节点存的是整行——这就是聚簇索引，一张表只有一棵（没有显式主键时依次选第一个非空唯一索引，再没有就藏着一个 6 字节的内部 ROW_ID，见 01/03）：

                 [ 17 | 35 | 62 ]            非叶节点：只有 键 + 子页指针
                /       |       \
  +-----------+  +-------------+  +-----------+
  | 1  5  9   |  | 18 22 31    |  | 63 70 99  |   叶子节点：完整行记录
  +-----------+  +-------------+  +-----------+
     \______________双向链表______________/          顺序扫沿链表走

主键决定物理插入位置：INSERT 一个新 id，InnoDB 按主键找到对应叶子页追加。自增主键永远追加在链表尾部（“最右叶”），几乎不产生中间插入。

4.2 二级索引与回表

UNIQUE KEY uk_username (username) 是另一棵独立的 B+ 树，叶子存的是 username + 主键 id，不是整行：

uk_username 树                          聚簇索引树
[ 'bob' | id=5 ]  --回表：拿 id=5 -->   [ ... id=5 的整行 ... ]

SELECT * FROM users WHERE username = 'bob';
-- EXPLAIN: type=const key=uk_username
-- 执行两步：uk_username 树点查拿到 id，再到聚簇树按 id 点查整行

拿二级索引的结果去聚簇索引取整行的过程叫回表。回表是二级索引查询的主要隐性成本——每次回表在数据未命中缓存时是一次随机页读。

注意：旧文档常提 MRR（Multi-Range Read，把回表的随机 IO 攒起来排序转成顺序读）作为回表兜底优化，该优化自 MySQL 8.0.21 起已被移除，8.4 里不存在，不要再拿它论证"回表无所谓"。

4.3 插入与更新的连锁反应

• 插入：目标叶子页已满（页内空闲低于最低水位）就页分裂——一拆二、一半数据搬家、父节点加一条指针；父节点也满则继续向上分裂。自增主键顺序追加，分裂极少；随机主键则分裂常态化，还留下大量半空页（碎片，见 11）。
• 更新二级索引列：InnoDB 对二级索引的更新实质是旧记录打删除标记 + 插入新记录（旧记录由后台 purge 清理），所以 UPDATE ... SET username = ... 可能触发回表定位、再在新位置插入、再分裂，全链条都付代价。ORDER BY/范围扫描初期也会频繁路过未 purge 的删除标记记录。
• 更新主键：等于搬家整行——聚簇树删旧插新 + 所有二级索引叶子跟着改。所以主键一旦定下就不要更新业务含义字段充当主键。

5. 主键选择实战

5.1 自增 BIGINT：默认正确答案

顺序写让聚簇树只在尾部追加：页分裂少、碎片少、写放大低，二级索引叶子只背 8 字节。绝大多数互联网业务表没有自然主键（手机号、身份证都不能当主键，会因变更/复用出问题），没有合适的自然主键就加自增 BIGINT，业务唯一性交给唯一索引。

5.2 UUID / 雪花 ID：随机写的代价

分布式系统偏爱全局唯一 ID（分库分表需要，见 12），但直接拿来做 InnoDB 主键有三笔账：

1. 随机写：UUID 落点在全树随机位置，每条插入平均要拉一个新页进 buffer pool 并随时可能页分裂——插入吞吐可能掉到顺序写的几分之一。
2. 写放大与空间：UUID() 返回 36 字符。CHAR(36) utf8mb4 占 144B，是 BIGINT 的 18 倍；这个宽度会被每棵二级索引的叶子重复存储，索引整体膨胀（§3.2 的扇出账）。BINARY(16) 存 UUID 可压到 16B，是及格线做法。
3. buffer pool 污染：随机插入不断触碰冷页，把热数据挤出缓存，连带拖慢读。

半序的雪花 ID（毫秒时间戳在高位）插入近似顺序，问题比 UUID 轻得多；真正放大问题的是把雪花存成 VARCHAR（应为 BIGINT UNSIGNED）。

5.3 非要用 UUID：有序化的取舍

MySQL 8.0 提供 UUID_TO_BIN(uuid, 1)：把 v1 型 UUID 的时间字段重排到高位（swap=1），让 BINARY(16) 按插入时间大致有序：

INSERT INTO orders (id, ...) VALUES (UUID_TO_BIN(UUID(), 1), ...);
SELECT BIN_TO_UUID(id) FROM orders WHERE ...;

取舍要讲清楚：重排后不是严格单调递增——低位时间字段回绕、重启后时钟字段变化仍会造成局部乱序；且 swap=1 的字节序与 MySQL 8.0.18+ 默认的 uuid_time_format 配置有关，跨版本/跨库迁移前先实测比对排序结果。想要真正的时间前缀有序，应用侧生成 UUIDv7 再存 BINARY(16) 是更稳的路子（此段建议上线前实测，属本章疑点清单）。

5.4 小结

• 单机/单库业务表：自增 BIGINT 主键 + 业务单号唯一索引。
• 全局 ID 需求：雪花存 BIGINT，或 UUID 存 BINARY(16) + UUID_TO_BIN(..., 1)；绝不用 VARCHAR 存 ID 做主键。
• 主键长度是复利：每棵二级索引都要背一遍，建表前想清楚。

6. 联合索引与最左前缀

本章核心小节。先建立直觉，再逐条判定。

6.1 (a,b,c) 的排序结构直觉

给 orders 建联合索引：

ALTER TABLE orders ADD INDEX idx_uid_status_ctime (user_id, status, create_time);

这棵二级索引的叶子按 (user_id, status, create_time) 整体排序，类比字典：先按第一个字段排，第一个字段相同的相邻堆放，再按第二个字段排，依此类推。

(1, 0, '03-01')   ← user_id=1 内部又按 status 分段
(1, 0, '03-05')
(1, 1, '05-02')   ← status=1 段内 create_time 有序
(1, 1, '09-30')
(1, 2, '06-11')
(2, 0, '07-01')   ← 换到 user_id=2，status 从头开始
(2, 0, '07-04')

由此推出最左前缀铁律：只有把前面的列用"等值"钉死，后面的列才是有序的，才能用来定位。跳过 user_id 直接问 status=1，整个索引里 status=1 散落在每个 user_id 段里，无序可言。

6.2 WHERE 组合逐条判定

以 idx_uid_status_ctime (user_id, status, create_time) 为例：

-- 1. 吃到 1 列：type=ref, key_len=8（BIGINT）
SELECT * FROM orders WHERE user_id = 10086;

-- 2. 吃到 2 列：type=ref, key_len=9（8 + status TINYINT 1）
SELECT * FROM orders WHERE user_id = 10086 AND status = 1;

-- 3. 三列全吃：type=range，等值两列 + 范围一列
SELECT * FROM orders
WHERE user_id = 10086 AND status = 1 AND create_time >= '2026-10-01';

-- 4. a + c 缺 b：user_id 等值吃掉 1 列；create_time 在 user_id=10086 段内
--    因 status 不同而整体无序，不能用于"定位"，只能靠 ICP/回表后过滤（§8）
SELECT * FROM orders WHERE user_id = 10086 AND create_time >= '2026-10-01';
-- EXPLAIN: type=ref key_len=8，Extra: Using index condition; Using where

-- 5. b + c 缺 a：一棵都用不上，全表扫描
SELECT * FROM orders WHERE status = 1 AND create_time >= '2026-10-01';
-- EXPLAIN: type=ALL key=NULL —— WHERE 里写了列名不等于索引存在

顺序不重要（优化器会调整），列在索引里的相对位置才重要。

6.3 范围之后，定位能力全停

-- status 用 range 定位后，create_time 在 status 的范围内不再有序
SELECT * FROM orders
WHERE user_id = 10086 AND status > 1 AND create_time = '2026-10-01 00:00:00';
-- 实际吃到：user_id=ref + status=range，create_time 只能回表后（或 ICP）逐行过滤

一旦某列走了 > / BETWEEN / LIKE 'abc%'，它在"定位"上已是最后一名。等值 → 范围的方向不能反。

6.4 GROUP BY / ORDER BY 也吃最左前缀

索引序能免掉 filesort 的前提同样是"前缀被钉死、剩余列有序"：

-- 排序匹配索引序：走索引，无 Using filesort
SELECT * FROM orders WHERE user_id = 10086
ORDER BY status, create_time LIMIT 10;

-- 排序列反了：单列等值钉住后，整棵索引段可反向扫，普通升序索引也能吃下
-- （MySQL 8.0 对 DESC 支持反向扫描，Extra: Backward index scan）
SELECT * FROM orders WHERE user_id = 10086
ORDER BY create_time DESC LIMIT 10;   -- 注意：跳过 status，吃到 1 列后按 create_time
                                       -- 段内无序，这条例外会 filesort，见下方注意

SELECT * FROM orders WHERE user_id = 10086
ORDER BY status, create_time DESC LIMIT 10;  -- 混合方向：8.0 前必 filesort，
                                             -- 8.0 可建降序索引消掉（§12.3）

注意：ORDER BY create_time 想吃到 (user_id, status, create_time) 必须先把 status 也等值钉死——跳过中间列排序就断了。“排序列吃剩余前缀"的意思是从被等值钉住的列之后、连续不断地吃到，不是任意挑列。

6.5 口诀与列顺序设计

口诀：等值列在前、范围列在后、排序列吃剩余。

更精确的三段式：等值 → 排序 → 范围——把 GROUP BY/ORDER BY 的列放在范围列之前，一次查询就能"定位 + 免排序"双赢；范围列一旦放前面，后面的列既不能定位也不能供排序。区分度（等值列内部再按谁排）不必教条：左列区分度大能截得更短，但当两列都是低频等值时，优先照顾排序需求。

7. 覆盖索引

7.1 消灭回表

覆盖索引不是一种索引类型，是一种效果：二级索引的叶子（列值 + 主键）已经装着查询要的全部列，回表这步整个省掉。EXPLAIN 的 Extra 出现 Using index。

ALTER TABLE orders ADD INDEX idx_uid_ctime_amount (user_id, create_time, amount);

SELECT user_id, create_time, amount
FROM orders WHERE user_id = 10086 ORDER BY create_time DESC LIMIT 20;
-- 三列全在索引里：不碰聚簇树，Extra: Using index
-- 对比：同样的 WHERE 但 SELECT *，每行都要拿主键回聚簇树点查

列表页查询是覆盖索引的主战场：只选展示要的窄列（id、时间、金额），永远比 SELECT * 更容易被覆盖。这也是"禁止 SELECT *“的第一条工程理由（见 02）。

7.2 count 到底扫哪棵树

SELECT COUNT(*) FROM orders;
SELECT COUNT(1) FROM orders;
SELECT COUNT(status) FROM orders;

COUNT(*) 与 COUNT(1) 语义相同（统计行数），InnoDB 优化器会自动挑最小的那棵索引来扫——二级索引比聚簇索引窄得多，扫它 IO 更少；若表没有任何二级索引才扫聚簇索引。COUNT(col) 要排除 NULL，必须真读到那一列：col 有独立索引则扫该索引，否则只能扫更宽的树。所以"count(*) 比 count(id) 快/慢"的争论没有意义，关键在 count 的列是否恰好落在某棵窄索引里（8.0.13+ 对 count(*) 另有内部缓存优化，细节与验证归 08）。

8. 索引下推 ICP

MySQL 5.6 引入。只发生在”二级索引扫描 + 回表“的场景：索引里能过滤的条件，不再等回表后由 server 层判，而是存储引擎在索引叶子层就地筛掉，不满足的直接不回表。

ALTER TABLE users ADD INDEX idx_name_age (real_name, age);

SELECT * FROM users WHERE real_name LIKE '张%' AND age = 30;

无 ICP：
  扫索引吃掉所有 real_name LIKE '张%' 的条目（设 1 万条）
  → 每条都回表 → server 层 WHERE age=30 过滤 → 剩 200 条
  回表 10000 次

有 ICP：
  扫索引时顺手比对叶子里的 age（age 就在这棵索引里！）
  → 只有 200 条进入回表
  回表 200 次，省掉 9800 次随机 IO
EXPLAIN 特征：Extra: Using index condition

两条边界：

• 查询列全部在索引里时本就不回表（覆盖索引），谈不上"下推”，ICP 不会出现在这种计划的 Extra 里。
• ICP 只是省 IO，不改变"定位用不上 age"的事实——real_name LIKE '张%' 之后 age 无序，ICP 是逐条目过滤，不是范围扫描。

9. 前缀索引

products.name VARCHAR(128) 全长建索引太占空间（utf8mb4 下 514B 一条），可以只索引前 n 个字符：

ALTER TABLE products ADD INDEX idx_name_full (name);      -- 全长：128×4+2 = 514B
ALTER TABLE products ADD INDEX idx_name7 (name(7));       -- 前缀：7×4+2 = 30B

9.1 取几位：用选择性算

选择性 = 前缀去重数 / 全值去重数：

SELECT COUNT(DISTINCT name)                                AS full_dv,
       COUNT(DISTINCT LEFT(name, 4)) / COUNT(DISTINCT name) AS sel4,
       COUNT(DISTINCT LEFT(name, 7)) / COUNT(DISTINCT name) AS sel7,
       COUNT(DISTINCT LEFT(name, 10)) / COUNT(DISTINCT name) AS sel10
FROM products;
-- 示例输出：sel4=0.61  sel7=0.95  sel10=0.97
-- 判断：7 位已保留 95% 区分度，10 位只多 2 个点——取 7 位够用

9.2 代价：覆盖与排序两不吃

注意：前缀索引只存前 n 个字符，叶子中没有完整值——永远无法成为覆盖索引，ORDER BY 该列也吃不到索引序（要 filesort）。idx_name7 定位到候选行后，完整 name 的比较发生在聚簇树回表之后。字符串列需要高频前缀搜索时优先精确前缀 + 覆盖设计；需要"包含"匹配走全文/ES（§12.4）。

10. 索引失效清单

每条给反例、原因、改写。共同前提：索引存在，但计划没用上。

10.1 列上套函数/表达式

-- 反例：索引失效，type=ALL
SELECT * FROM orders WHERE YEAR(create_time) = 2026;
-- 改写：把函数变成对列的范围
SELECT * FROM orders
WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01';

-- 同理反例：WHERE id + 1 = 5、WHERE DATE(create_time) = '2026-10-20'

B+ 树按列原值排序，YEAR(create_time) 的序和 create_time 的序对不上。改写公式：f(col) 等值 → col 范围。函数确实无法避免时，用生成列索引兜底（§12.2）。

10.2 隐式类型转换

-- 反例：mobile 是 VARCHAR，传数字触发对"列"的类型转换，索引失效
SELECT * FROM users WHERE mobile = 13800138000;
-- 改写：字符串字面量与列类型一致
SELECT * FROM users WHERE mobile = '13800138000';

-- 反向不成立：id 是 BIGINT，传字符串没事（'123' 只转换常量侧）
SELECT * FROM users WHERE id = '123';   -- 仍能走主键

规则（MySQL 比较语义）：数字与字符串比较时，字符串转数字。所以"字符串列 = 数字"要在每一行上执行 CAST，索引作废；“数字列 = 字符串"只转常量一次，无伤。字符集/排序规则的隐式转换同理（§10.7）。类型与字面量对齐的完整规则见 03-数据类型与建表.md。

10.3 LIKE 以 % 开头

SELECT * FROM products WHERE name LIKE '%手机%';    -- 失效：前缀不确定，索引无从定位
SELECT * FROM products WHERE name LIKE '小米%';     -- 有效：前缀确定，range 扫描

后缀匹配改写见 §13.5。

10.4 OR 两侧并非都有索引

-- 反例：remark 无索引，整体退化为全表
SELECT * FROM orders WHERE user_id = 10086 OR remark = '加急';
-- 改写：两侧各有索引（或拆两条 UNION 合并结果集）

两侧都有索引时优化器可能走 index merge（对两棵索引各扫再合并主键去重回表），但它对成本估算很敏感、常见场景未必选，把性能赌在 index merge 上不划算；稳妥做法是补齐索引或改写 SQL。

10.5 联合索引非最左列

WHERE status = 1 想走 (user_id, status, create_time)——见 §6.2 例 5，用不上就是全表，不重复论证。

10.6 不等号与 NOT IN：不是绝对失效，是看分布

SELECT * FROM orders WHERE status <> 4;      -- 取消单只占 2%
SELECT * FROM orders WHERE status NOT IN (3, 4);

技术上 <> 可以拆成两段 range 走索引，真实执行的是"扫索引 + 回表 98% 的行”。优化器按统计信息估到回表比全表顺序扫更贵时主动放弃索引——这是合理的成本决策，不是索引失效。反过来 status <> 0 且 0 占九成时，走索引反而是对的。结论：不等号能不能用索引取决于被排除行的占比，别把”<> 不走索引"背成铁律。

10.7 JOIN 列字符集/排序规则不一致

-- users.mobile utf8mb4，order_items.contact utf8mb3：
-- 连接时一侧列被隐式 CONVERT() 包裹，该列索引作废，nested loop 退化成扫表
SELECT o.id FROM orders o JOIN users u ON u.mobile = o.contact_tel;

JOIN 键两侧必须类型 + 字符集 + collation 全一致（utf8mb4_general_ci 与 utf8mb4_0900_ai_ci 不同排序规则同样出问题）。建表期就统一，存量表修复的 Online DDL 见 09-Online-DDL与大表变更.md。

10.8 低选择性列与小表：优化器主动放弃

给 status（只有 0-4 五个值）建索引有没有用？算一笔账（1000 万行 orders）：

• 查 status = 3（完成单，占 88%）：走索引 = 880 万次随机回表；全表扫描 = 64 万页顺序读 + 少量预读。随机 IO 贵一个量级，优化器弃索引是正确答案。
• 查 status = 2（在途单，占 0.3%）：3 万次回表，索引完胜。

所以"状态字段要不要索引"没有标准答案：看单值的行占比与行宽。真正通用的写法是别单建 KEY(status)，而是 (status, create_time) 这类"低选择性列 + 高选择性列"联合——低选择性列被用作等值段入口，配合二级列定位（§6.5）。小于几页的表（几千行内）永远全表更快，buffer pool 里一搬就到。

10.9 兜底：USE / FORCE INDEX

SELECT * FROM orders FORCE INDEX (idx_uid_ctime_amount)
WHERE user_id = 10086 ORDER BY create_time DESC LIMIT 20;

注意：hint 是把执行计划焊死。数据分布、索引增删、版本升级都可能让今天的"正确强制"变成明天的枷锁，且 hint 不会随 SQL 被所有调用方感知。只在统计信息失准导致线上选错索引的救火中使用（治本是 §14），并加注释说明原因与复查日期。

11. 冗余索引、重复索引与维护成本

11.1 判定方法

SHOW INDEX FROM orders;
-- 观察 Key_name 的列序组合

• 重复索引：KEY (user_id) 与 UNIQUE KEY (user_id)、或两棵列序完全相同的索引——纯浪费，直接删一个。
• 冗余索引：KEY (user_id) 已被 KEY (user_id, status, create_time) 的"最左前缀"包含——大多数场景下前者可删。例外：(user_id, status) 能覆盖 WHERE user_id=? + 需要 status 的查询，删掉它可能让某些覆盖查询回表，逐一 EXPLAIN 后再动手。

侦察工具一句话带过：sys.schema_redundant_indexes 找冗余、sys.schema_unused_indexes 找长期闲置（它统计自实例启动，重启清零，观察要给足一个业务周期），读法归 08 / 11。

11.2 索引不是越多越好

每棵二级索引都在每次 INSERT/UPDATE/DELETE 时被同步维护（§4.3）：十棵索引意味着写一次业务行、改一次索引记录。除写放大外还有两笔隐性成本——页分裂与碎片侵蚀空间；以及优化器选择困难：候选索引越多，枚举比较越贵，统计信息稍有偏差越容易选错（§14）。删除前可用 8.0+ 的 Invisible Index 灰度观察：

ALTER TABLE orders ALTER INDEX idx_uid_status_ctime INVISIBLE;
-- 优化器立刻"看不见"它，表和数据完全不动；观察一周无性能回退再 DROP
ALTER TABLE orders ALTER INDEX idx_uid_status_ctime VISIBLE;   -- 出问题的后悔药

12. 唯一索引与特殊索引形态

12.1 唯一索引 vs 普通索引

选择依据首先是语义约束：业务上不该重复的列（order_no、username）就建 UNIQUE，把脏数据挡在写入层，这比应用层查重可靠（并发查重有窗口期）。性能差异两点：

• 唯一二级索引写入时不能进 change buffer——必须先读页判断是否撞唯一键，随机写场景下放大 IO（普通非唯一二级索引可以缓冲合并，见 01/11）。
• 唯一键冲突路径上的加锁行为更重（共享锁/间隙锁），高并发"先查后插"的重复冲突会演化成锁等待——锁的细节在 05-事务与锁.md 展开，此处立此存照。

结论：该唯一就唯一（防脏数据优先），但纯查询加速的列用普通索引。

12.2 表达式不能建索引，生成列可以

MySQL 不支持 PostgreSQL 式的"函数索引"，等价方案是 STORED 生成列 + 普通索引（5.7 引入，8.x 沿用；对 VIRTUAL 生成列建索引在二级索引里实际按存值实现，版本细节多，稳妥用 STORED，以 03 为准）：

-- 场景一：按 JSON 属性查询 products.extra
ALTER TABLE products
  ADD COLUMN color VARCHAR(16)
      GENERATED ALWAYS AS (extra ->> '$.color') STORED,
  ADD INDEX idx_color (color);
SELECT id FROM products WHERE color = 'red';
-- 反例：WHERE extra->>'$.color' = 'red' 直接写在列上，无索引可用（§10.1）

-- 场景二：DATE(create_time) 高频查询（§10.1 改写不了的遗留 SQL）
ALTER TABLE orders
  ADD COLUMN day DATE GENERATED ALWAYS AS (DATE(create_time)) STORED,
  ADD INDEX idx_day (day);

代价：多一列存储 + 多一棵索引（§11 的写放大账）。

12.3 降序索引与全文/空间索引

• 降序索引（8.0+）：ADD INDEX (user_id ASC, create_time DESC)，让"第一列升、第二列降"的混合方向 ORDER BY 免 filesort（§6.4 的遗留问题）；8.0 对普通索引的等值前缀反向扫描已支持 ORDER BY ... DESC（Backward index scan），降序索引主要是补混合方向的课。
• 全文索引（FULLTEXT）：InnoDB 自 5.6 起支持，倒排实现，带最小词长等停用词参数，对中文分词基本无能为力（ngram 插件效果有限）。生产级站内搜索建议独立检索引擎（ES 等），通过 Canal/binlog 做异构索引同步——展开在 12-分库分表.md 的异构索引一节。
• 空间索引（SPATIAL）：GIS 场景专用（POINT/GEOMETRY + R-Tree），业务库极少用；LBS 选型另议，本章不展开。

13. 实战案例组

13.1 深分页：LIMIT 1000000, 20

-- 反例
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- EXPLAIN: type=index key=PRIMARY
-- 本质：沿聚簇树扫过 1000020 行、丢掉前 100 万行。offset 越大越接近全表扫描

改写一——游标/续传（前端能带上一页末尾 id 时，唯一治本方案）：

SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;
-- type=range，无论翻到多深，永远只读 20 行

改写二——延迟关联（页码必须随机跳的场景）：

SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t ON o.id = t.id;
-- 子查询在聚簇索引上只"读主键"（不回表取整行），扫 100 万条窄记录
-- 比外层逐行读 100 万个宽行便宜一个量级——缓解而非根治，深页仍要限流

13.2 Top-N：ORDER BY + LIMIT

-- 需求：全站最新 10 单
SELECT * FROM orders ORDER BY create_time DESC LIMIT 10;
-- 无索引：type=ALL + Using filesort，对 1000 万行全量排序，不可接受
ALTER TABLE orders ADD INDEX idx_ctime (create_time);
-- 有索引：沿索引末段反向扫 10 条即停（LIMIT 截断扫描），毫秒级

配合过滤条件时回到 §6.5 三段式：WHERE status=2 ORDER BY create_time DESC LIMIT 10 应建 (status, create_time)，等值钉段、段尾反向取 10 条，一条 SQL 同时吃定位与排序。

13.3 查某用户最近 5 笔订单

-- 反例：单列索引仍然排序
ALTER TABLE orders ADD INDEX idx_uid (user_id);
SELECT * FROM orders WHERE user_id = 10086 ORDER BY create_time DESC LIMIT 5;
-- EXPLAIN: type=ref key=idx_uid，Extra: Using filesort
-- 该用户 3000 单全部回表后在排序区重排，取 5 条

-- 正解：排序列进联合索引
ALTER TABLE orders ADD INDEX idx_uid_ctime (user_id, create_time);
-- EXPLAIN: type=ref key=idx_uid_ctime，无 filesort
-- user_id 段内天然按 create_time 有序，反向读 5 条叶子 + 回表 5 次即结束

13.4 后台多条件搜索的列顺序

需求：city、status 两个等值可选条件 + create_time 区间 + 按时间倒序分页。

SELECT id, order_no, amount, create_time
FROM orders
WHERE user_id = 10086 AND status = 1
  AND create_time BETWEEN '2026-09-01' AND '2026-10-01'
ORDER BY create_time DESC LIMIT 20;

-- 列顺序设计（§6.5 三段式：等值 → 排序 → 范围，排序与范围同列时合一）：
ALTER TABLE orders ADD INDEX idx_uid_status_ctime (user_id, status, create_time);
-- user_id、status 等值钉死 → 段内 create_time 有序
-- → BETWEEN 既是定位也是排序轴，反向扫 20 条即停，零 filesort

若前端允许只按 user_id 查（不给 status）：同一棵索引仍吃到最左列，无需另建。若存在"只按 status 查"的高频入口：那是另一棵 (status, create_time) 的事——按真实流量组合建索引，不做组合学穷举（§11）。

13.5 LIKE 后缀匹配

LIKE '%1380' 无解（§10.3），可用生成列反转（§12.2 思路）把后缀变前缀：

ALTER TABLE users
  ADD COLUMN mobile_rev CHAR(11)
      GENERATED ALWAYS AS (REVERSE(mobile)) STORED,
  ADD INDEX idx_mobile_rev (mobile_rev);

SELECT id FROM users WHERE mobile_rev LIKE CONCAT(REVERSE('8000'), '%');
-- 原查询"尾号 8000"变成前缀匹配，range 扫描；等值场景直接 mobile_rev = REVERSE('13800138000')

真实模糊搜索（关键词、拼音、分词）上 ES，不要魔改 SQL——见 12。

13.6 大表计数

SELECT COUNT(*) FROM orders;   -- 千万行：扫最小索引 + 回表计数，秒级起步

产品需求若是"大约多少条"：

ANALYZE TABLE orders;   -- 先让统计信息新鲜（§14）
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'orders';
-- TABLE_ROWS 是采样估算，倾斜表误差可达 ±几十%，只配展示"约 1000 万"

精确总数：COUNT(*) + 窄覆盖索引（§7.2）扛住，或者用计数表/缓存异步维护（指路 07 日志体系与 11 调优）；分页组件别把总数当真，“仅展示前 1000 页"是比任何优化都便宜的答案（§13.1）。

14. 统计信息与索引质量

索引建对了，优化器选错照样全表扫。它的依据是采样统计：InnoDB 默认 innodb_stats_persistent = ON，对每张表的每棵索引抽样约 20 个叶子页（innodb_stats_persistent_sample_pages）估算行数与区分度，持久化到 mysql.innodb_*_stats；行数变化超过约 10% 时后台自动重算（innodb_stats_auto_recalc，数字以当前版本文档为准）。

三个常见病与对策：

ANALYZE TABLE orders;
-- 手动重新采样。8.x 执行很快，但入口有表级短暂互斥，别在流量尖峰裸跑

SET innodb_stats_persistent_sample_pages = 64;   -- 全局，需评估重启/内存开销
ALTER TABLE orders STATS_PERSISTENT=1, STATS_AUTO_RECALC=1;  -- 表级覆盖

ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 8 BUCKETS;
-- 直方图（8.0+）：为"无索引列"补充值分布画像
-- §10.8 的 status 之争，优化器有了直方图才真能分清 0.3% 和 88%

大表、数据倾斜（某 user_id 占一半）最容易采样失真 → 估算行数与真实差一个数量级 → 选错索引。排查时先对比 EXPLAIN 的 rows 与实际 COUNT(*) 同数量级，再调采样页/直方图；索引成本模型与 optimizer_switch 全景归 08-EXPLAIN与查询优化.md。

15. 实战建议

建索引的决策流程（每次新增慢查询治理按此走）：

1. 列出该表所有高频查询形态（WHERE 等值/IN、范围、ORDER BY、JOIN 键、返回列），先问"要什么索引"不如先问"它们能共享一棵什么树”。
2. 按三段式排列：等值（最左）→ 排序 → 范围；跳过列就是断链（§6）。
3. 把高频窄查询 SELECT 列补进联合索引尾巴，凑覆盖（§7）；TEXT/长 VARCHAR 用前缀索引并把覆盖预期调低（§9）。
4. 自查失效项（§10 清单逐条对）：函数上列、字面量类型、字符集一致性、LIKE 前缀。
5. 上线前用真实数据量 EXPLAIN 验收：type 到 ref/range、key_len 符合预期、无 Using filesort（大结果集）、深分页走游标；数据分布可疑时补直方图（§14）。
6. 下线旧索引前用 Invisible Index 灰度观察（§11），别让"顺手清理"变回滚事故。

先加索引还是先改 SQL？ 默认先改 SQL，再配索引：函数上列、SELECT *、前导 %、OR 混无索引列这些写法，加什么索引都接不住；SQL 改成"索引友好"的形状后，一棵设计正确的联合索引往往能覆盖一整族查询。反过来，SQL 已是理想形状仍慢，才检讨索引与统计信息。索引是长期契约（每次写入都在付费，§11），SQL 是需求表达——需求表达错了就用契约去硬补，通常是双输。唯一例外是线上救火：一条 FORCE INDEX 或紧急 ADD INDEX ... INVISIBLE 观察后再显形，比说服团队重写 SQL 更快（§10.9）。