B+ 树与索引设计

用途：解释“为什么这个查询没走索引”、决定该建什么索引

为什么是 B+ 树

结构	特点	为什么不用
哈希表	等值 O(1)	不支持范围与排序；冲突退化
二叉搜索/红黑树	树高随数据量增长快	磁盘场景 IO 次数过多
B 树	多路平衡，非叶节点也存数据	单页能放的键更少，树更高；范围扫描需中序回溯
B+ 树	只有叶子存数据，叶子间双向链表	InnoDB 采用：树矮（3-4 层可放千万级）、范围扫描顺序 IO

InnoDB 页默认 16KB，非叶节点只存键与页号，因此扇出大、树高通常 3-4 层，一次主键查询约 3-4 次页访问（热点页在缓冲池中则无 IO）。

聚簇索引与二级索引

• 聚簇索引：叶子节点存整行数据。InnoDB 表必有且仅有一个，选择顺序为：主键 → 首个非空唯一索引 → 隐藏 ROW_ID。因此主键应当短且单调（自增或趋势递增），过长的联合主键会让所有二级索引膨胀。
• 二级索引：叶子存索引列 + 主键值。按二级索引查非覆盖列需要回表（拿主键再查聚簇索引）。

索引生效规则

规则	说明
最左前缀	联合索引 (a,b,c) 可服务 a、a,b、a,b,c；单独查 b 不走（8.0 有跳跃扫描但受限）
前缀中断	WHERE a=1 AND c=3 只用上 a
范围之后停止	a=1 AND b>2 AND c=3：b 用范围，c 无法用于索引过滤
函数/表达式破坏	WHERE DATE(t)=... 不走 t 索引；可用函数索引（8.0）或改写为区间
隐式类型转换	字符串列与数字比较会转列类型，索引失效
前导模糊	LIKE '%x' 不走；LIKE 'x%' 可走
OR 与 NOT/!=	可能导致放弃索引（取决于选择性）
排序方向	8.0 支持降序索引，混合方向排序可受益
覆盖索引	查询列都在索引内则免回表（Extra: Using index）
索引下推（ICP）	存储引擎层用索引列提前过滤，减少回表次数

设计方法

1. 从查询出发：列出该表的高频查询条件与排序，再设计索引，而不是“每个字段都建”。
2. 选择性优先：把区分度高的列放前面（等值列在前、范围列在后）。
3. 控制数量：每个二级索引都增加写放大与优化器选择负担；用 sys.schema_unused_indexes 定期清理未使用索引。
4. 考虑覆盖：热点查询可加“冗余列”换取免回表，权衡写放大。
5. 主键要短：避免 UUID 直接做主键（随机写导致页分裂）；需要全局唯一时用有序 ID（如趋势递增的雪花/号段）或另建代理主键。
6. 前缀索引：长字符串用前缀（VARCHAR(255) 取 16-32 字符），用 SELECT COUNT(DISTINCT LEFT(col,n))/COUNT(*) 选长度；代价是不能用于覆盖。

唯一性约束

唯一索引不只是查询加速，更是数据正确性的最后防线（业务幂等键、防重复登记）。应用层“先查后插”在并发下必然产生重复，必须靠唯一约束 + 冲突处理（INSERT ... ON DUPLICATE KEY UPDATE 或捕获重复键错误后转查询）。

常见误区

误区	修正
索引越多查询越快	写放大、优化器成本估算偏差、空间膨胀
用 FORCE INDEX 长期兜底	掩盖统计信息问题，数据分布变化后更糟
随机 UUID 做主键	页分裂与缓存命中率下降
只看 rows 估算判断索引好坏	结合 EXPLAIN ANALYZE 的真实耗时与扫描行数
以为有索引一定被使用	选择性差、类型转换、统计信息陈旧都会导致全表扫描

延伸阅读

• MySQL 官方文档：Indexing、B-Tree Index Characteristics、Optimizer Statistics
• 《高性能 MySQL》第 6-7 章
• 丁奇《MySQL 实战 45 讲》索引部分

索引失效的具体触发条件

写法	为什么失效	改写
WHERE DATE(created)=\'2026-10-16\'	列上套函数	改为 >= 与 < 的区间条件
WHERE varchar_col = 123	隐式类型转换	传字符串
LIKE \'%kw%\'	前导通配无法定位起点	ngram / 全文索引 / 搜索引擎
ORDER BY b 而索引为 (a,b)	排序列前缺等值条件	建 (b) 或带上 a 的等值条件
!= / NOT IN / IS NULL	选择性差，优化器放弃	改正向枚举或补索引
join 两侧 collation 不一致	隐式转换	统一字符集与排序规则

联合索引的设计步骤

1. 从慢日志与代码扫描取真实条件分布（不要凭印象）。
2. 每个查询拆成“等值列 + 排序列 + 范围列”。
3. 列顺序：等值列在前（选择性高者优先）→ 排序列 → 范围列；范围列之后的列只能用于索引过滤（ICP）。
4. 返回列少且固定时追加进索引做覆盖索引，以写放大换读性能。
5. EXPLAIN + EXPLAIN ANALYZE 验证实际扫描行数与耗时；估算偏差大时 ANALYZE TABLE 或建直方图。
6. 上线后用 sys.schema_unused_indexes、schema_index_statistics 复核并清理无用索引。

可复算示例

SELECT * FROM orders WHERE customer_id=88 AND status=\'PAID\' ORDER BY created_at DESC LIMIT 20;
现状索引 idx_customer(customer_id)：type=ref, rows=41200, Extra=Using where; Using filesort

诊断：命中 customer_id 后仍需回表过滤 status，并对约 4 万行排序。 优化：建 (customer_id, status, created_at)，等值列在前、排序列在后，filesort 消失、扫描量降到 limit 量级。 代价：写路径多维护一棵树；若 status 更新频繁，索引维护成本随之上升——写放大必须计入决策。

排查清单

• 高频查询是否都有基于真实条件分布的索引设计？
• 主键是否短且趋势递增（有无随机 UUID 主键）？
• 是否存在长期未使用的索引？
• 关键查询是否出现 Using filesort / Using temporary？
• 隐式类型转换与 collation 不一致是否已清理？
• 唯一性是否落在数据库层（而非“先查后插”）？