一条 WHERE user_id = 10086 走不走索引、回表几次，答案全在索引的物理结构里。InnoDB 的索引不是给列加个目录，而是一棵以 16 KB 页为节点、叶子页按主键有序串联的 B+Tree。页分裂、索引膨胀、写放大这些词，只有先把页和树画出来才有意义。以下按 MySQL 8.0 + InnoDB 默认配置核对。

---

1. 为什么是 B+Tree：扇出与树高

磁盘读写单位是页（4 KB / 16 KB 一整块），不是一条记录。索引要解决的就是：用最少的页读取次数定位到记录。

结构	等值查找	范围查找	单次查询的页 IO	问题
二叉搜索树	O(log₂N)	需中序遍历	树高次，每层一次	扇出仅 2，太深
哈希表	O(1)	不支持	1 次	无序，无法扫范围
B+Tree	O(log_f N)	叶子链表顺序扫	树高次（典型 3–4）	写入需维护有序

扇出是 B+Tree 的关键。按 8 字节主键 + 6 字节页号算：

非叶子页条目 ≈ 8 + 6 = 14 B  → 16384 / 14 ≈ 1170 个指针
叶子页一行   ≈ 1 KB（宽表）   → 16384 / 1024 ≈ 16 行
树高 3：1170 × 1170 × 16 ≈ 2.2 × 10^7 行
树高 4：1170^3 × 16      ≈ 2.6 × 10^10 行

千万级表的等值查询从根到叶子典型 3 次页访问，前两层几乎常驻 buffer pool，真实物理 IO 通常只有 1 次。范围查询靠叶子页间双向链表：WHERE id BETWEEN 100 AND 5000 定位起点后顺链扫。代价是所有叶子必须有序，这正是页分裂的来源。

---

2. 页结构与"一页能放多少行"

区域	作用
File Header	页号、上下页指针、页类型、LSN
Page Header	记录数、堆顶、页目录槽数、最大事务 ID
Infimum / Supremum	两条虚拟记录，页内链表哨兵
User Records	真实记录，按主键单向链表串联，物理不保证有序
Page Directory	稀疏槽位，每槽指向一组记录
File Trailer	校验和 + LSN 低位，用于检测页损坏

页内查找分两步：先在页目录上二分（槽数约为记录数的 1/6），定位到小范围，再在组内顺链表线性找，复杂度 O(log(N/6) + 6)。

估算一页行数，用 avg_row_length 加上每行约 20 字节页内开销（记录头、事务 ID、回滚指针、变长列长度数组）：

SHOW VARIABLES LIKE 'innodb_page_size';

SELECT table_name, avg_row_length,
       ROUND(16384 / (avg_row_length + 20)) AS rows_per_page_est
FROM information_schema.tables
WHERE table_schema = 'app' AND table_name = 't_order';

SELECT table_name,
       ROUND(data_length / 1024 / 1024, 1)  AS data_mb,
       ROUND(index_length / 1024 / 1024, 1) AS index_mb,
       ROUND(data_free / 1024 / 1024, 1)    AS free_mb
FROM information_schema.tables WHERE table_schema = 'app';

avg_row_length 是估算值；data_free 长期不降说明删过大量行却没重建。要看页级真相（填充率、页分布），用 innodb_ruby 的 innodb_space 读表空间。

---

3. 聚簇索引与二级索引：叶子上装的东西不一样

InnoDB 是索引组织表：主键那棵 B+Tree 的叶子页就是行数据本身，这棵树叫聚簇索引，等于表。无主键时选第一个 NOT NULL UNIQUE 索引，都没有就生成 6 字节隐藏列 DB_ROW_ID。

索引	叶子页内容	一次等值查询的路径
聚簇索引	整行 + 事务信息	定位到叶子即得行
二级索引	索引列 + 主键值	定位到叶子 → 拿主键 → 回聚簇索引
唯一二级索引	同上	同上，可少一次唯一性确认

回表成本容易被低估：命中 N 行就要在聚簇索引上下降 N 次，每次典型 3 次页访问；页不在 buffer pool 里就是 N 次随机 IO。

-- 走 idx_user，status/amount 不在索引里 → 每行一次回表
EXPLAIN SELECT status, amount FROM t_order WHERE user_id = 10086;
-- type=ref key=idx_user rows≈N Extra=NULL

-- 命中行太多时优化器可能改判
EXPLAIN SELECT status, amount FROM t_order WHERE user_id = 10086;
-- type=ALL key=NULL Extra=Using where

第二段不是"索引失效"，而是代价模型判断：N 次随机回表比一次顺序全表扫更贵。

---

4. 主键的三个判据与页分裂

判据	含义	违反后的直接后果
单调	新值总比旧值大（或总小）	插入落在随机页 → 页分裂、脏页分散
定长	宽度固定（BIGINT 而非 VARCHAR）	非叶子条目变长 → 扇出下降 → 树高上升
唯一	天然不重复	需额外唯一性检查，或靠业务保证出重复

UUID() / 随机字符串当主键，三条全破：CHAR(36) 占 36 字节，二级索引叶子各存一份，体积典型膨胀 2–4 倍；插入点均匀散布，每次都可能命中已满的中间页；字符串比较比整数慢。

页分裂只在一种情况发生：要插入的页已满，且插入位置不在该索引最右端。

插入模式	分裂位置	分裂后填充率	写放大来源
自增主键顺序追加	只在最右页	左页 100%，新页从少量记录起	新页分配 + 父页加指针
随机主键	任意中间页	两页各约 50%	记录搬移 + 父页更新
有序但前缀相似	最右页（近似顺序）	接近 100%	接近顺序插入

-- 反例：随机主键
CREATE TABLE t_event_bad (
  id CHAR(36) NOT NULL DEFAULT (UUID()),
  user_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id), KEY idx_user (user_id)
) ENGINE=InnoDB;

-- 正例：单调定长主键 + 业务唯一键
CREATE TABLE t_event (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid BINARY(16) NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id), UNIQUE KEY uk_uuid (uuid), KEY idx_user (user_id)
) ENGINE=InnoDB;

SHOW ENGINE INNODB STATUS\G   -- 看 Pages created / written 的宏观速率

顺序主键"少分裂"的准确说法是：分裂只发生在树的最右边界，不涉及中间页随机写。对外需要不可猜测 ID，把 UUIDv7 的 BINARY(16) 放唯一键即可。

---

5. 联合索引：最左前缀、下推与覆盖

(a, b, c) 先按 a 排，a 相同按 b，b 相同按 c；能"用上"的前提是从最左列起连续匹配：

查询条件	能定位的列	说明
a=? AND b=? AND c=?	a, b, c	全部用上
a=? AND c=?	a	c 只能在 a 范围内过滤
b=? AND c=?	无	缺最左列
a=? AND b>?	a, b	范围列之后的 c 不参与定位
a=? AND b IN (...) AND c=?	a, b, c	IN 视为多个等值区间

"范围条件后面的列用不上"只对定位成立；若 c 在索引里，它仍可参与过滤，这是索引下推（ICP）：

ALTER TABLE t_order ADD KEY idx_user_status_amount (user_id, status, amount);

EXPLAIN SELECT id FROM t_order
WHERE user_id = 10086 AND status > 1 AND amount > 100;
-- Extra: Using index condition   → 条件下推到引擎层，在索引页上过滤
-- Extra: Using index            → 覆盖索引，完全不回表

Extra	含义	是否回表
Using index	所需列全在索引里	否
Using index condition	ICP，引擎层在索引上过滤	命中行仍回表
Using where	Server 层过滤	视情况
Using index; Using where	覆盖 + 残余条件下推不到	否

覆盖索引的判定：SELECT 列 + WHERE 列 + ORDER BY 列是否全在同一索引里；二级索引叶子自带主键，SELECT id 天然被覆盖。自适应哈希索引（AHI）只为高频等值查找建哈希，范围查询无效，8.0 默认开启。

---

6. 选择性、维护成本与重建

选择性 = 不同值个数 / 总行数：

SELECT COUNT(*) total,
       COUNT(DISTINCT status) ndv_status,
       COUNT(DISTINCT user_id) ndv_user,
       ROUND(COUNT(DISTINCT status) / COUNT(*), 4) sel_status
FROM t_order;

选择性	典型列	建索引效果
接近 1	用户 ID、订单号、时间戳	命中行极少，值得
0.01–0.1	城市、品类	联合索引前缀列可用
< 0.01	status、is_deleted、gender	单列索引基本无效

低选择性列不能单独成索引，但可作联合索引前缀：(status, created_at) 先用 status 切子集，再用 created_at 做范围。判据是"能否把候选行压到全表的百分之几"。

每个二级索引都是一棵独立 B+Tree：一次 INSERT 写 1 次聚簇 + N 次二级索引；更新索引列等于删旧键插新键。单表二级索引典型建议控制在 5 个以内。

-- OPTIMIZE TABLE 本质是 ALTER TABLE ... FORCE：重建表 + 重建索引
OPTIMIZE TABLE t_order;
ALTER TABLE t_order ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

重建期需与原表等量的临时空间与同量级写入 IO，主从会被推高延迟；追加型表基本无收益。

---

7. 排错：五种"建了索引却没用上"

现象	原因	处理
type=ALL	列上用了函数：DATE(created_at)='2026-01-01'	改范围：>= '...' AND < '...'
走索引但 rows 极大	隐式类型转换：varchar 列与数字比	参数类型对齐，phone = '138...'
key=NULL	前导模糊 LIKE '%abc'、OR 连非索引列	改后缀匹配/全文索引；OR 拆 UNION
联合索引只用上第一列	范围条件位置不对	等值在前、范围在后
写入变慢	索引过多，每次写放大	合并前缀重复的冗余索引
更新后查询变慢	索引列被更新，删旧键插新键	避免更新高频索引列

---

8. 边界：B+Tree 本就解决不了的情况

场景	更合适的方案	理由
全文关键词检索	全文索引 / Elasticsearch	B+Tree 只能前缀匹配，切不了词
LIKE '%词%'	倒排索引、ngram 全文索引	无前缀无法定位
低基数枚举单独查询	不建索引或作联合前缀	命中占比太高，回表不如全扫
超大宽表只查少数列	覆盖索引 / 列式存储	行存宽表 IO 放大
极高频写入日志表	顺序主键 + 批量插入	写放大是主要矛盾

B+Tree 擅长有序、前缀与范围。

---

9. 自测

1. 8 字节主键下，非叶子页能放约多少个指针？树高因此典型是多少？
2. 页目录槽数为什么远小于记录数？页内查找分成哪两步？
3. 同样 WHERE user_id = ?，命中 10 行走索引、命中 10 万行走全扫，判断依据是什么？
4. 自增主键"减少页分裂"的准确说法是什么？它真的不分裂吗？
5. Using index 与 Using index condition 差在哪一步？哪个仍需回表？
6. status 只有 5 个取值，什么时候它作为联合索引前缀反而有用？