技术小册

主题

索引与 B+ 树 ​

一句话：InnoDB 用 B+ 树把「一次磁盘 IO 读一页」变成按页扇出的查找；聚簇索引的叶子存整行，二级索引的叶子存主键，换来有序范围和稳定查找，代价是回表和写放大。

适用版本：MySQL 8.0（InnoDB），页大小默认 16 KiB。innodb_page_size 可在初始化时改，改后扇出计算随之变化。

为什么需要 ​

没有索引，查一行要扫完整张表；数据在磁盘上，扫描的代价是页 IO，不是 CPU。二叉树高度约 log2(N)，2000 万行要 24 层，每层一次随机 IO；B+ 树把每个节点塞满一页，扇出上千，高度降到 3 层。

选择 B+ 树而不是 B 树或哈希，是因为数据库同时要等值、范围和排序：哈希只能等值；B 树非叶也存数据，扇出小、范围查询要回溯；B+ 树非叶只存键，叶子用双向链表串起来，范围查询定位起点后顺序读。

核心机制 ​

扇出与高度的推导 ​

一页 16 KiB = 16384 字节。非叶节点的一个条目 = 主键 8 字节（BIGINT）+ 子页指针 6 字节 = 14 字节。

理论上限：16384 / 14 ≈ 1170 个条目/页
按 15/16 的填充率估算：15 * 1024 / 14 ≈ 1097 个条目/页

叶子节点存整行。假设一行 1 KiB，一页放 16 行（实际受行格式、溢出页影响，通常更少）。

高度 2：1170 * 16        ≈ 1.87 万行
高度 3：1170 * 1170 * 16 ≈ 2190 万行
高度 4：1170^3 * 16      ≈ 256 亿行

所以「2000 万行三次 IO」是按 1 KiB 行、满页估算的数量级，不是硬指标：行更宽、页填充率更低，能放的行数会少一个量级。根节点通常常驻 Buffer Pool，实际磁盘 IO 往往从第二层算起。

聚簇索引与二级索引 ​

  • 聚簇索引：InnoDB 必须有且只有一个。优先主键；没有主键用第一个非空唯一索引；都没有则生成 6 字节隐藏 DB_ROW_ID。叶子存完整行。
  • 二级索引：叶子存「索引列 + 主键」。查完整行要拿主键回聚簇索引再查一次，这叫回表。

二级索引存主键而不是物理地址，是因为页分裂会让物理地址变化；存主键只需维护聚簇索引的位置，代价是回表。

sql
-- 二级索引 (user_id, status, created_at)
-- 查询只取索引列和主键：覆盖索引，不回表
SELECT user_id, status, created_at
FROM t_order
WHERE user_id = 42 AND status = 1;

-- 查询取 amount：需要回表
SELECT user_id, amount
FROM t_order
WHERE user_id = 42 AND status = 1;

最左前缀 ​

联合索引 (a, b, c) 先按 a 排，a 相同按 b 排，b 相同按 c 排。只有从最左列开始、且前面的列是等值条件，后面的列才能继续用于定位。

条件索引使用情况
a=1 AND b=2 AND c=3三列都用于定位
a=1 AND c=3用 a 定位，c 只能靠索引下推/回表后过滤
b=2 AND c=3无法用该索引定位
a=1 AND b>2 AND c=3用 a、b 定位；b 是范围后，c 不能继续用于定位
a=1 ORDER BY b, c用 a 定位，b/c 可利用索引顺序
a IN (1,2,3) AND b=2IN 展开为多个等值区间，优化器可能继续用 b；以 EXPLAIN 为准

「范围列后面的列一定失效」是简化说法：用于定位的确实到此为止，但后面的列仍可能被索引下推用于过滤，减少回表。

索引下推与 MRR ​

  • ICP（Index Condition Pushdown，5.6+）：把能在索引上判断的条件下推到引擎层，减少回表次数。EXPLAIN 的 Extra 显示 Using index condition。
  • MRR（Multi-Range Read，5.6+）：二级索引拿到一批主键后先排序再回表，把随机 IO 变成更接近顺序的 IO。

前缀索引 ​

长字符串可以只索引前 N 个字符：

sql
CREATE INDEX idx_email_prefix ON t_user (email(10));

-- 评估区分度，越接近 1 越好
SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS selectivity FROM t_user;

前缀索引不能用于覆盖索引（拿不到完整值），也常用于 LIKE 'abc%'；LIKE '%abc' 仍然用不上。

实战场景 ​

一、为救一条慢 SQL 加了四个索引，写入了 40% 变慢 ​

现象：一张订单表原本 3 个二级索引。为了满足“运营任意条件组合查询”的需求，陆续加到 9 个索引。单条查询确实从 3s 降到 80ms，但两周后：插入 TPS 下降约 40%，Buffer Pool 命中率从 99.5% 掉到 91%，磁盘从 60% 涨到 85%（经验值，需校准），并且出现新的慢 SQL——因为优化器在多个可用索引间选了错的。

排查：索引换来的读收益，代价是写放大与内存占用，必须一起算。

定位层取证据判定
索引数量与体积information_schema.STATISTICS + index_stat，各索引的 size单表索引体积超过主数据一定比例即冗余
是否被用过sys.schema_unused_indexes、performance_schema.table_io_waits_summary_by_index_usage计数为 0 的索引是纯成本
写放大插入/更新的 P99 与加索引时间点台阶式上升即相关
缓存效率innodb_buffer_pool_read_requests vs reads，命中率页种类变多、命中率下降
优化器选择EXPLAIN 的 key 与 possible_keys，必要时 optimizer_trace索引变多后选错
查询模式运营查询是否本质上是“多维检索”该走搜索引擎或宽表，不该堆索引

聚簇索引叶子存完整行、二级索引叶子存“索引列 + 主键”，每次写入要维护全部二级索引；页分裂还会让维护成本进一步上升。多维任意组合查询用 B+ 树索引硬撑，是方向错了。

对症做法：

sql
-- 1. 用统计信息决定去留，而不是靠“可能有用”
SELECT index_name, rows_selected FROM mysql.innodb_index_stats
 WHERE database_name='trade' AND table_name='t_order' AND stat_name='size';
SELECT * FROM sys.schema_unused_indexes WHERE object_name = 't_order';

ALTER TABLE t_order DROP INDEX idx_status_created, DROP INDEX idx_channel_status;

-- 2. 保留的是“能同时服务于定位和排序”的联合索引，遵循最左前缀
--    高频条件是 (user_id, status, created_at)：等值前缀 + 排序后缀
ALTER TABLE t_order ADD INDEX idx_user_status_created (user_id, status, created_at);
text
3. 多维检索迁出去：ES/OpenSearch 承担组合筛选，MySQL 只做主键/唯一键读写。
   路由方式见 [ES 聚合分析](../../S03_数据与存储/21_Elasticsearch/05-聚合分析.md)。
4. 加索引要走评审：写放大、体积、缓存影响三项数字必须写进变更单，
   一张表的二级索引数量设上限（例如 5 个，经验值需校准）。
5. 已经不得不留多个索引时，给关键查询用索引显式提示（FORCE INDEX）要谨慎，
   更稳的办法是把 SQL 改造成能命中单一索引的形态。

复盘要点：判据是“未使用索引数量应为 0”+“索引体积/主数据体积比”有预算。索引是为查询模式服务的，不是为“可能的查询”服务的——这一点与 SQL 调优与 EXPLAIN 里的“优化器选择取决于统计信息”是同一件事的两端。

二、email(10) 前缀索引让登录查询扫了两万行 ​

现象：用户表用 email 做登录查询，DBA 为省空间把索引建成 email(10)。上线一年后企业邮箱域名高度集中（同一前缀 enterprise），一条 WHERE email='enterprise001@corp.com' 的查询扫了 2.1 万行才命中，P99 从 5ms 涨到 400ms；同时“覆盖索引免回表”的优化也失效。

排查：前缀索引的代价是区分度和覆盖能力。

定位层取证据判定
前缀区分度SELECT COUNT(DISTINCT LEFT(email,10))/COUNT(*) FROM t_user远低于 1（本例 0.06）即失效
扫描行数EXPLAIN 的 rows 与实际 rows_examined命中前缀后仍需逐行比较完整值
是否覆盖Extra 里还有 Using index condition/回表，却没有 Using index前缀索引拿不到完整值，不能覆盖
LIKE 形态LIKE 'abc%' 可用；LIKE '%abc' 用不上前缀方向也必须匹配
数据分布变化新增大客户/新域名导致前缀集中索引是当初建的，分布是后来变的
sql
-- 用区分度选择前缀长度，而不是随手写个 10
SELECT
  COUNT(DISTINCT LEFT(email, 8))  / COUNT(*) AS s8,
  COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS s12,
  COUNT(DISTINCT LEFT(email, 16)) / COUNT(*) AS s16,
  COUNT(DISTINCT email)           / COUNT(*) AS sfull
FROM t_user;
-- 取“接近 sfull 的最短前缀”

对症做法：

sql
-- 1. 换成全列索引（varchar 长度允许时），或把前缀拉长到区分度接近 1
ALTER TABLE t_user DROP INDEX idx_email_prefix, ADD INDEX idx_email (email);

-- 2. 列太长（如 TEXT/大 varchar）时用“生成列 + 索引”，保留完整可比性
ALTER TABLE t_user ADD COLUMN email_hash CHAR(32)
    GENERATED ALWAYS AS (MD5(email)) STORED, ADD INDEX idx_email_hash (email_hash);
-- 查询带原值比较，哈希只做定位，避免哈希相同但内容不同的误命中
SELECT id FROM t_user WHERE email_hash = MD5(?) AND email = ?;

-- 3. 需要覆盖索引避免回表时，索引列必须包含被查询的全部列（前缀索引做不到）
text
4. 登录路径的兜底：把 (email) 的唯一约束加上，
   既保证业务唯一性，也让等值查询退化为 Record Lock（见 03 篇）。

复盘要点：判据是“定期复算前缀区分度”——数据分布会变，当初合适的长度可能今天就不合适；把这条放进季度巡检。前缀索引能省空间，但“省空间”只在区分度接近 1 时成立，否则换来的是扫描行数上升与回表增加。

三、UUID 做主键，插入性能半年内掉了三倍 ​

现象：新建的业务表用 UUID 字符串做主键（为了分布式生成方便）。上线时压测正常，半年后：同样写入量下，插入 P99 从 4ms 涨到 30ms，innodb_buffer_pool_page_discards 上升，表体积比同数据量的自增主键表大 2.3 倍，备份时间也翻倍。

排查：聚簇索引按主键组织数据，随机主键让写入变成随机 IO。

定位层取证据判定
主键形态是否 UUID 字符串/随机数非单调即存在页分裂
页分裂指标information_schema.INNODB_METRICS 的 index_page_splits持续增长即频繁分裂
空间放大数据页平均填充率、碎片率（DATA_FREE）随机插入让页长期填不满
主键宽度二级索引叶子存“索引列 + 主键”，主键越宽二级索引越大36 字符 UUID 放大所有二级索引
缓存命中Buffer Pool 命中率、innodb_buffer_pool_reads随机访问降低局部性
行格式VARCHAR 主键的比较与排序开销字符集/排序规则也参与比较

“2000 万行三次 IO”是按 1 KiB 行、满页估算的数量级，不是硬指标：行更宽、填充率更低，能放的行数会少一个量级。

对症做法：

sql
-- 1. 内部主键用单调递增 BIGINT，业务标识用另一列 + 唯一索引
CREATE TABLE t_invoice (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  invoice_no  CHAR(32) NOT NULL,                  -- 对外仍是 UUID/业务号
  ...
  PRIMARY KEY (id),
  UNIQUE KEY uk_invoice_no (invoice_no)
);

-- 2. 必须用 UUID 时选“时间有序”的变体（UUIDv7 / 有序 GUID），把随机性压到尾部
-- 3. 主键宽度控制：BIGINT 8 字节 vs CHAR(36) 36 字节，二级索引全部跟着放大
java
// 4. 分布式 ID 与主键解耦：ID 负责全局唯一，主键负责写入局部有序
long pk = idAllocator.next();            // 号段/雪花，单调
entity.setInvoiceNo(uuidV7());            // 对外标识，可乱序
text
5. 已经用随机主键的大表迁移路径：
   新建自增主键表 → 双写 → 按主键区间回填历史 → 切读 → 下线旧表；
   不要直接 ALTER 主键，锁表时间与数据量成正比。

复盘要点：判据是“index_page_splits 增速”和“同数据量下的表体积对比”。主键设计一旦定下，成本会随数据量单调放大，属于典型的“上线时看不出来、半年后集中爆发”的问题。ID 生成方案本身见 分布式 ID 生成。

最小上手 ​

sql
CREATE TABLE t_order (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    BIGINT UNSIGNED NOT NULL,
  status     TINYINT NOT NULL,
  amount     DECIMAL(12,2) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_user_status_created (user_id, status, created_at)
) ENGINE=InnoDB;

EXPLAIN
SELECT id, amount
FROM t_order
WHERE user_id = 42 AND status = 1
ORDER BY created_at DESC
LIMIT 20;

-- 期望：key = idx_user_status_created，type = ref
-- Extra 若出现 Using index，说明覆盖索引，不回表

生产动作 ​

  • 索引设计先看查询：把「等值列」放前、「范围/排序列」放后；区分度高的列优先，但不要为低基数列单独建索引。
  • SHOW INDEX FROM t_order; 看 Cardinality；它只是统计估算，不等于真实区分度。
  • 冗余索引要清理：(a) 和 (a,b) 同时存在时，前者通常可删；用 sys.schema_redundant_indexes 找。
  • 大表加索引用 Online DDL 或 pt-osc/gh-ost，并确认没有长事务持有 MDL。
  • 写多的表控制索引数量：每个二级索引都会放大 INSERT/UPDATE/DELETE 的维护成本。
  • 主键优先自增 BIGINT：随机主键（UUID）会造成页分裂和碎片；分布式场景用雪花或号段，不要直接用 UUID 当聚簇索引。

边界 ​

  • 索引不是越多越好：空间、写入放大、优化器选择成本都会上升。
  • 低区分度列单独建索引价值低：status 只有几个值，单独索引常被优化器放弃；放在联合索引里才可能有用。
  • 覆盖索引有边界：SELECT * 基本一定回表；前缀索引不能覆盖。
  • B+ 树不擅长全文和向量：全文检索用倒排索引（ES/MySQL FULLTEXT），向量检索用专用向量库。
  • 统计信息会过期：大批量增删后 ANALYZE TABLE，否则优化器可能选错索引。

讲清楚 ​

InnoDB 的索引是 B+ 树。磁盘一次 IO 读一页 16 KiB，B+ 树把非叶节点塞满键和指针，一个条目 14 字节，一页约一千多个分支，所以三层就能覆盖千万级行，任意一行三次 IO 左右。聚簇索引的叶子存整行，二级索引的叶子存索引列加主键；查完整行要回表，查的列都在索引里就是覆盖索引。联合索引按最左前缀排序，范围列之后的列不能继续用于定位，但仍可能被索引下推用来过滤。代价是回表和写放大，所以索引要按查询设计，不是越多越好。

把一门技术讲透到既懂也会用

技术小册 · 持续更新