高性能索引该如何设计

0. 引言

索引是数据库性能的第一杠杆：一条 SQL 走不走索引，性能差距可达千倍。理解索引背后的数据结构（B+Tree）、两类索引（聚簇/二级）的工作原理，掌握最左前缀、覆盖索引、索引下推等匹配规则，才能设计出"小而准"的索引。本文以 MySQL 8.0 为基准，从原理到实战给出完整索引设计方法。

1. 索引数据结构

1.1 从二分查找说起

索引的本质是"有序 + 可快速定位"。二分查找要求数据有序且支持随机访问——数组满足但插入删除代价 O(N)，链表插入快但无法二分。数据库需要"插入快 + 查找快"的结构，于是有了树。

1.2 Hash 索引

对索引列计算哈希值（如 CRC32），哈希表定位到槽位：

优点	缺点
等值查询 O(1)，极快	不支持范围查询（>、<、BETWEEN）
	不支持排序 ORDER BY
	不支持部分列匹配（联合索引必须整键哈希）
	哈希冲突需链表处理，退化风险

MySQL 的 Memory 引擎支持显式 HASH 索引；**InnoDB 的"自适应哈希索引（AHI）"**是 InnoDB 内部对高频等值访问的热点页自动建立的 Hash 加速（innodb_adaptive_hash_index，默认开启），对用户透明。

1.3 B+Tree 索引（InnoDB 核心）

图表渲染中…

B+Tree 相比 B-Tree 的关键设计：

非叶子节点只存键值不存数据 → 单节点可容纳更多分支（扇出大），树更矮（3-4 层可支撑千万级数据）；
叶子节点存全量数据且有序连接 → 范围查询（>、BETWEEN）只需定位起点后顺序遍历链表；
所有查询都走根到叶，路径长度恒定 → 查询时间稳定（没有"有时快有时慢"）。

一个 16KB 数据页约可容纳 1000+ 个键值指针，3 层 B+Tree 可支撑约 10 亿条记录，且每次查询只需 3 次磁盘 IO（根节点常驻内存）。

2. 聚簇索引与二级索引

InnoDB 是索引组织表（IOT）：数据按主键顺序物理组织。

2.1 聚簇索引（主键索引）

叶子节点 = 完整数据行；
表只有一个聚簇索引：显式主键 → 第一个非空唯一索引 → 隐藏 rowid；
特点：主键查询快（一次定位即得数据）；但主键值无序插入会引发页分裂（页分裂是性能杀手）。

2.2 二级索引（辅助索引）

叶子节点存 索引列 + 主键值（不是数据行指针——为了主键变更时无需更新二级索引）；
通过二级索引查询数据需要回表：先查二级索引得主键，再按主键查聚簇索引；
8.0 中主键为二级索引的"行指针"，因此主键越短，二级索引越小（每棵二级索引都冗余一份主键）。

text

二级索引 idx_name: [name, 主键id, ...]
查询 WHERE name='张三'：
  ① 走 idx_name 找到主键 id=1024
  ② 回表：按 id=1024 查聚簇索引，取完整行

3. 索引匹配规则

3.1 最左前缀匹配

联合索引 (a, b, c) 的匹配规则：

查询条件	是否走索引	用到的列
`WHERE a=1`	✅	a
`WHERE a=1 AND b=2`	✅	a, b
`WHERE a=1 AND b=2 AND c=3`	✅	a, b, c
`WHERE b=2`	❌（8.0 前）/ 索引跳跃扫描	无法用最左前缀
`WHERE a=1 AND c=3`	⚠️	a（c 被跳过，无法用索引过滤）
`WHERE a>1 AND b=2`	⚠️	a（范围后失效）

排序也遵循最左前缀：ORDER BY a, b 可以用索引避免 filesort；ORDER BY b, a 不行。

8.0 的索引跳跃扫描（Skip Scan）：WHERE b=2 在 (a,b) 联合索引下，8.0.13+ 优化器可自动"跳过 a 的每个值"进行扫描（SKIP SCAN），但仅在 a 的基数小时有效，不能作为常规依赖。

3.2 覆盖索引（Covering Index）

若查询所需列全部在索引中，则无需回表——直接从索引树取数据：

sql

-- idx(a, b)：查询只取 a、b 两列
SELECT a, b FROM t WHERE a = 1;   -- ✅ 覆盖索引，不回表
SELECT * FROM t WHERE a = 1;      -- ❌ 需要回表取其他列

设计启示：高频查询的"查询列"可刻意纳入联合索引，用空间换回表次数（数据库通用的"索引下推覆盖"技巧）。

3.3 索引下推（ICP，Index Condition Pushdown）

sql

-- 联合索引 (zipcode, lastname)，查询：
SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunko%';

5.6 之前：先按 zipcode 从索引取主键 → 回表 → 再过滤 lastname。5.6+ 的 ICP：在索引遍历时就过滤 lastname 条件，减少回表次数。EXPLAIN 的 Using index condition 即 ICP 生效。

3.4 回表代价与优化顺序

图表渲染中…

4. 索引设计实战

4.1 Cardinality（基数）

Cardinality = 索引列去重后的不同值数量；Cardinality/n 越接近 1，选择性越好；
低基数列（性别、状态）不适合建单列索引：走索引还要回表，不如全表扫描；
查看：SHOW INDEX FROM t; 或 information_schema.statistics（8.0 统计信息默认持久化在 mysql.innodb_index_stats，innodb_stats_persistent=ON）。

4.2 联合索引设计三步法

等值条件列放最前（过滤性最强、且范围列之后失效的坑最小）；
范围条件列放中间偏后（>/</BETWEEN 之后的条件无法用索引）；
排序列放最后（避免 filesort）。

sql

-- 业务：按 user_id 查订单，按 created_at 排序，分页
-- ❌ KEY (user_id, created_at, status) 或 KEY (created_at, user_id)
-- ✅ 最佳：KEY (user_id, created_at)  —— 等值在前，排序在后，天然覆盖
SELECT * FROM user_order WHERE user_id=1001 ORDER BY created_at DESC LIMIT 20;

4.3 索引创建规范

单表索引数控制：一般 ≤ 5 个（每个索引都是写放大 + 存储开销）；
冗余检测：(a,b) 已存在时，(a) 单列索引冗余（最左前缀已覆盖）；
前缀索引：超长字符串（如 url）用 KEY idx (url(64)) 节省空间，但无法用于 ORDER BY 与覆盖；
大表建索引：8.0 支持 ALTER TABLE ... ADD INDEX ... ALGORITHM=INPLACE（在线，不阻塞 DML），但建议业务低峰执行；
删除无用索引：performance_schema.schema_unused_indexes（8.0 记录从未被使用的索引）。

5. MySQL 8.0 索引新特性

特性	语法	价值
降序索引	`KEY idx (a ASC, b DESC)`	8.0 前反向扫描 b；现在可正向使用索引避免 filesort
函数索引（表达式索引）	`KEY idx ((LOWER(email)))`	8.0.13+，`WHERE LOWER(email)=...` 也能走索引
不可见索引	`CREATE INDEX ... INVISIBLE`	先建后"试运行"：`optimizer_switch` 开启 `use_invisible_indexes` 测试，确认无副作用再 VISIBLE
多值索引	`CAST(json_col AS CHAR ARRAY)`	8.0.17+，JSON 数组字段建索引（配合 `JSON_TABLE`）
索引跳跃扫描	自动	8.0.13+，部分场景跳过最左列

sql

-- 函数索引实战：邮箱登录查询
ALTER TABLE user ADD INDEX idx_email_lower ((LOWER(email)));
SELECT * FROM user WHERE LOWER(email) = 'admin@example.com';  -- 走 idx_email_lower

6. 常见误区

索引越多越好：每个索引都拖慢 INSERT/UPDATE（写放大），且占内存；
在低基数列建单列索引：性别/状态列索引等于没建；
对索引列使用函数/运算：WHERE DATE(created_at)='2026-01-01' 无法走索引（用范围条件 created_at >= ... AND < ... 或函数索引）；
隐式类型转换：WHERE phone = 13800138000（字符串列 vs 数字）导致索引失效，必须 WHERE phone = '13800138000'；
LIKE '%xxx' 前导通配符：无法用索引（LIKE 'xxx%' 可以）；
!=/NOT IN/OR 的索引失效：优化器可能全表扫描，用 UNION ALL 或改等值。

7. 小结

索引选型：InnoDB 一律 B+Tree（Hash 只适合 Memory 引擎等值场景），3 层树支撑亿级数据；
聚簇索引存整行、二级索引存主键，回表是二级索引查询的主要代价；
匹配规则：最左前缀 + 覆盖索引 + 索引下推，是 EXPLAIN 优化的三大抓手；
设计方法：等值列在前、范围列居中、排序列最后，控制索引数量与冗余；
8.0 新特性：降序/函数/不可见/多值索引，为历史"不可能走索引"的 SQL 提供了新解法。

下一章讲解如何提高查询性能：EXPLAIN 执行计划分析、优化器行为与慢查询治理实战。