这篇文章面向已经会写 SQL、但还没系统建立过索引设计观的后端工程师。
读完你应该能回答四个问题：
- 一条 SQL 的成本到底花在哪里，索引消掉了其中哪一部分？
- 面对一个查询场景，联合索引的列顺序该怎么排，依据是什么？
- "最左前缀""回表""覆盖索引""索引下推"分别约束了什么，边界在哪里？
- 线上发现慢 SQL 时，用什么手段定位，用什么手段验证，用什么手段安全上线？
一、先把问题定义清楚：索引优化的到底是什么成本
1.1 一次查询的成本构成
很多人把"加索引"等同于"变快"，但没说清快在哪。一条点查的成本大致可以拆成三段：
| 成本项 | 含义 | 索引能否降低 |
|---|---|---|
| 扫描成本 | 需要读取多少页、比较多少行 | 能，这是索引的主战场 |
| 回表成本 | 二级索引命中后，回聚簇索引取剩余列 | 能，但要靠覆盖索引/索引下推 |
| 排序与临时表成本 | ORDER BY / GROUP BY 无法借用索引顺序时额外排序 | 能，靠索引天然有序 |
索引的价值不是"让 MySQL 更聪明"，而是让优化器有机会把"扫全表"换成"扫一小段有序数据"。 后面所有的概念——回表、覆盖、下推、最左前缀——都在回答同一件事：这次查询要读多少页。
1.2 一个朴素的对照
CREATE TABLE `user` (
id BIGINT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0,
city VARCHAR(50) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
假设表里有千万级数据，执行：
SELECT * FROM `user` WHERE name = '张三';
name 上没有索引时，InnoDB 只能从聚簇索引的第一个叶子页顺序扫到最后一个叶子页，逐行比较。这就是全表扫描（Full Table Scan），EXPLAIN 里体现为 type=ALL。
代价的关键不在"比较次数"，而在要读多少个 16KB 的页。数据量远超 Buffer Pool 时，这些页大部分要从磁盘读。这是全表扫描真正昂贵的原因，也是为什么小表全表扫描反而比走索引更快——优化器算过账。
1.3 索引的本质：一份按字段排好序的目录
这里保留一个经典类比：索引就是《新华字典》前面的部首目录。
没有目录，你只能从第 1 页翻到第 1000 页；有了目录，你先定位"鑫"在第 872 页，然后直接翻过去。
索引 = 被索引列的值 + 指向数据位置的信息，并且按值排好序。 有序是关键：有序才能二分，才能做范围扫描，才能免掉排序。
目录的代价也很直观：占纸（占磁盘）、正文改了目录也要改（写放大）。
1.4 索引的代价
| 维度 | 具体影响 | 什么时候会咬人 |
|---|---|---|
| 磁盘空间 | 每个索引是一棵独立的 B+ 树 | 宽索引 + 大表，索引总量可能超过数据本身 |
| 写放大 | INSERT/UPDATE/DELETE 要同步维护所有相关索引 | 写多读少的表、批量导入、消息消费落库 |
| 内存竞争 | 索引页和数据页共享 Buffer Pool | 索引多了会挤掉热数据页，命中率下降 |
| 唯一索引额外成本 | 唯一二级索引无法使用 change buffer，插入时必须读页校验唯一性 | 高频写入 + 大表，随机 IO 明显上升 |
| 优化器成本 | 候选索引越多，执行计划选择空间越大 | 偶发选错索引，表现为"同一条 SQL 时快时慢" |
| DDL 成本 | 加/删索引是 DDL，大表有风险 | 见第八章 |
最后一条尤其值得记住：索引数量本身就是一种运维负担。它不只是占空间，还会让执行计划变得不稳定。
二、索引的底层结构：为什么 InnoDB 选 B+ 树
2.1 为什么不是二叉搜索树或红黑树
二叉搜索树、红黑树的查询复杂度都是 O(log n)，看起来够用。问题在于树高。
一棵平衡二叉树存千万级数据，树高约 log₂(10⁷) ≈ 23 层。最坏情况从根走到叶要触达 23 个节点。如果每个节点在不同的磁盘页上，就是 23 次随机 IO。机械盘一次随机 IO 在毫秒量级，SSD 在百微秒量级——无论哪种介质，23 次随机 IO 都比 3 次贵一个数量级。
所以选型的出发点不是"渐进复杂度更优"，而是同样的 O(log n)，底数越大越好。
2.2 B+ 树：用扇出换树高
B+ 树的做法是一个节点塞很多 key。InnoDB 的页默认 16KB，非叶子节点一页能放的 key 数量取决于索引列宽度——索引列越窄，扇出越大，树越矮。
结果是常见量级的表，B+ 树高度通常只有 3 到 4 层。加上根节点和大部分内部节点长期驻留 Buffer Pool，实际落到磁盘的 IO 往往只有最后一两次。
B+ 树相对 B 树还有两个针对性设计：
- 只有叶子节点存数据/主键，非叶子节点只存 key 和指针，因此扇出更大、树更矮。
- 叶子节点之间用双向链表相连，范围扫描（
BETWEEN、>、ORDER BY）可以顺着链表顺序读，而不是反复回到根节点。
[根: 10, 50, 100]
/ | \
[1,3,7] [10,20,30,40] [50,60,70,80,90]
⇄ ⇄ ⇄
（叶子节点双向链表，范围扫描顺着链表走）
一句话：B+ 树不是理论最优的查找结构，而是在"按页读磁盘"这个约束下最合适的结构。降低树高、把随机 IO 变成顺序 IO，才是它被选中的原因。
2.3 为什么不是哈希索引
哈希表的等值查询是 O(1)，比 B+ 树更快。InnoDB 也有自适应哈希索引（Adaptive Hash Index），但它是引擎内部对热点页的优化，不能由你显式创建。
原因是哈希结构不支持三件事：范围查询、排序、最左前缀匹配。业务 SQL 里这三类需求占比太高，所以主索引结构不能是哈希。
这是一个典型的工程取舍：放弃单点的极致性能，换取访问模式的通用性。
三、索引类型：先建立一个坐标系
在展开细节之前，先把术语摆清楚，避免后面混。MySQL 里"索引"这个词至少在三个维度上被使用：
| 维度 | 取值 | 说明 |
|---|---|---|
| 数据组织方式 | 聚簇索引 / 二级索引 | 叶子节点存整行，还是存主键值 |
| 约束语义 | 主键 / 唯一 / 普通 | 是否附带唯一性和非空约束 |
| 列数与形态 | 单列 / 联合 / 前缀 / 函数 / 全文 / 空间 | 索引键怎么构造 |
"覆盖索引"不在这张表里，因为它不是索引类型，而是"某条 SQL 刚好被某个索引完全满足"的运行时现象。这一点后面会单独说。
3.1 主键索引（聚簇索引）
InnoDB 的主键索引很特殊：叶子节点直接存整行数据。这种索引与数据同构的结构叫聚簇索引。
每张 InnoDB 表有且仅有一个聚簇索引，选取规则是：
- 有
PRIMARY KEY就用它； - 没有主键，就找第一个所有列都
NOT NULL的唯一索引； - 还找不到，InnoDB 生成一个 6 字节的隐藏列
row_id作为聚簇索引键。
第 3 种情况在工程上要避免。隐藏 row_id 由全局计数器分配，你既无法在 SQL 里引用它，也无法在主从复制、数据订阅（Canal/Debezium）里用它定位一行。"每张表都要有显式主键"不是洁癖，而是可运维性要求。
主键设计还有一条容易被忽略的影响：二级索引的叶子节点存的是主键值，所以主键越宽，所有二级索引都跟着变胖。 用 BIGINT 自增还是 32 位 UUID 字符串做主键，影响的不是一棵树，而是全部。
3.2 二级索引（普通索引 / 辅助索引）
主键之外的索引统称二级索引。它的叶子节点存的是 索引列值 + 主键值。
ALTER TABLE `user` ADD INDEX idx_name (name);
SELECT * FROM `user` WHERE name = '张三';
-- 1. 在 idx_name 上定位 name='张三'，拿到 id
-- 2. 用 id 去聚簇索引再查一次，取出整行
第 2 步叫回表（Back to Table）。注意回表的代价不是"多一次查询"这么轻描淡写：
- 回表次数等于命中行数，命中 1 万行就是 1 万次聚簇索引查找；
- 这些查找按主键值离散分布，是随机 IO；
- 所以"走了索引但依然很慢"最常见的原因，就是回表次数太多。
这也解释了优化器的一个行为：当它估算出需要回表的行数占比很高时，会认为顺序扫全表反而更便宜，于是放弃这个索引。这不是"索引失效"，而是基于成本的正确选择。
3.3 唯一索引
值不可重复，但允许 NULL，且可以有多个 NULL（SQL 标准里 NULL 之间不相等）。这一点在业务上很容易出事：
CREATE TABLE user_account (
id BIGINT NOT NULL AUTO_INCREMENT,
email VARCHAR(100) NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_email (email)
);
如果你打算用 uk_email 保证"一个邮箱只能注册一次"，而 email 可空，那么插入 100 条 email = NULL 的记录全部会成功。想靠唯一索引做业务约束，相关列必须 NOT NULL。
主键索引与唯一索引的对比：
| 主键索引 | 唯一索引 | |
|---|---|---|
| 允许 NULL | 不允许 | 允许，且可多个 |
| 每表数量 | 1 个 | 多个 |
| 是否聚簇 | 是 | 不是 |
| 能否用 change buffer | 不涉及 | 不能（插入需校验唯一性，必须读页） |
最后一行是工程上的隐藏成本：普通二级索引在插入时，如果目标页不在 Buffer Pool，可以把变更暂存到 change buffer 稍后合并；唯一索引必须立刻把页读进来做唯一性校验。在高写入量的大表上，唯一索引比普通索引贵，而且贵在随机读。
所以有一条经验：能靠业务幂等 + 逻辑校验解决的唯一性，未必都要压给数据库；但涉及资金、账号、订单号这类"绝对不能重复"的场景，唯一索引是最后一道防线，该加就加。 这是典型的"性能 vs 正确性"取舍，判断标准是重复数据的业务后果有多严重。
3.4 联合索引
一个索引包含多个列，实际工作中用得最多。
ALTER TABLE `user` ADD INDEX idx_city_age_name (city, age, name);
它的排序规则是：先按 city 排，city 相同再按 age 排，age 也相同再按 name 排。就像电话本先按姓排、再按名排。
联合索引的设计是本文的核心，单独放在第六章讲。
3.5 前缀索引
列很长（email、url、device_id）时，把整列塞进索引既占空间又降低扇出。可以只索引前 N 个字符：
ALTER TABLE `user` ADD INDEX idx_email_pre (email(10));
取舍很清晰：
- 省空间、扇出更大、树更矮；
- 无法作为覆盖索引使用——索引里只有截断前缀，MySQL 无法确定它等于原值，必须回表确认；
- 不能用于
ORDER BY/GROUP BY，同理，前缀顺序不代表原值顺序。
前缀长度靠区分度（selectivity）来选：
SELECT
COUNT(DISTINCT email) / COUNT(*) AS full_ratio,
COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS r5,
COUNT(DISTINCT LEFT(email, 7)) / COUNT(*) AS r7,
COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS r10
FROM `user`;
取"接近 full_ratio 的最短长度"。需要提醒的是：这个比值是在当前数据分布上算出来的，如果后续数据特征变化（比如新接入的渠道邮箱前缀高度雷同），区分度会退化。这类索引值得放进定期巡检清单。
对于邮箱这种"前缀相似、后缀区分"的场景，还有一个替代思路：存储一列反转字符串或哈希值并对它建索引，用等值查询代替前缀匹配。代价是要维护冗余列、哈希冲突需要二次过滤，且只支持等值。
3.6 函数索引与降序索引（MySQL 8.0）
MySQL 8.0.13 起支持函数索引，可以让原本"因为列上有函数而无法走索引"的 SQL 重新可用：
-- 注意表达式要用双层括号
ALTER TABLE `user` ADD INDEX idx_create_year ((YEAR(create_time)));
SELECT * FROM `user` WHERE YEAR(create_time) = 2026; -- 可以用上 idx_create_year
MySQL 8.0 也支持真正的降序索引：
ALTER TABLE feed ADD INDEX idx_uid_time (user_id, create_time DESC);
8.0 之前写 DESC 会被静默忽略，ORDER BY a ASC, b DESC 这类混合排序只能 filesort。
这两个特性能解决问题，但我倾向优先改 SQL、其次才用函数索引。原因是函数索引把业务逻辑写进了 schema：表达式一旦和代码里的写法不完全一致（比如代码改成了 DATE_FORMAT），索引就悄悄用不上了，而这种失配在代码评审里几乎看不出来。
3.7 全文索引
用于文本检索，只能建在 CHAR / VARCHAR / TEXT 上。
CREATE TABLE articles (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(200) NOT NULL DEFAULT '',
body TEXT,
PRIMARY KEY (id),
FULLTEXT KEY ft_title_body (title, body) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('数据库' IN NATURAL LANGUAGE MODE);
-- 布尔模式：+ 必含，- 必不含，* 前缀通配
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL +性能 -旧版本' IN BOOLEAN MODE);
工程注意点：
- 中文必须配
ngram分词器（MySQL 5.7.6+ 内置），ngram_token_size默认 2，短于该长度的词会被忽略； ngram_token_size是全局参数，修改后需要重建全文索引；- 相关度排序、同义词、拼音、纠错这些能力它基本没有；
- 写入维护成本明显高于普通索引。
结论：全文索引适合"已经有 MySQL，检索需求很轻，不想引入新组件"的场景。 一旦检索本身成为产品能力，就该上专门的检索引擎（如 Elasticsearch），并把 MySQL 定位成唯一数据源、检索侧做异步同步。
3.8 空间索引与外键索引
- 空间索引（SPATIAL） 服务 GIS 数据，列类型须为
GEOMETRY / POINT / LINESTRING / POLYGON且NOT NULL。常规业务基本用不到，知道存在即可。 - 外键（FOREIGN KEY） 本质是约束，但 MySQL 要求外键列上必须有索引，没有就自动创建。互联网业务普遍不用物理外键，原因不是"性能差"这么简单，而是：外键会在写入路径上引入跨表加锁、让分库分表几乎不可行、让数据修复和灰度回滚变得棘手。改为应用层保证一致性，本质是把一致性检查从不可控的引擎行为挪到可控的业务代码里。
四、聚簇索引 vs 二级索引：回表成本从哪来
| 聚簇索引 | 二级索引 | |
|---|---|---|
| 叶子节点存什么 | 整行数据 | 索引列值 + 主键值 |
| 每表数量 | 1 个 | 多个 |
| 查询路径 | 一棵树走到底 | 先查索引树拿主键，再回表查聚簇索引 |
| 主要成本 | 页数多（行宽） | 回表带来的随机 IO |
两句话记住：
- 聚簇索引 = 索引和数据是同一棵树
- 二级索引 = 索引树 + 回表查数据树
由此派生出两个设计准则：
准则一：主键要窄、要单调递增。 窄是因为所有二级索引都要存它；单调递增是因为聚簇索引按主键顺序存放，随机主键（如无序 UUID）会导致插入点在树中随机跳跃，引发页分裂和空间碎片。
准则二：行不要太宽。 聚簇索引叶子节点存整行，行越宽，一页装的行越少，全表扫描和主键范围扫描要读的页越多。把大字段（长文本、JSON、快照）拆到扩展表，是对聚簇索引的直接优化。
五、回表、覆盖索引、索引下推：同一条查询的三种命运
用同一张表，看同一类查询在不同条件下走出的三条路。
CREATE TABLE student (
id BIGINT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0,
city VARCHAR(50) NOT NULL DEFAULT '',
PRIMARY KEY (id),
INDEX idx_name_age (name, age)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
5.1 命运一：回表
SELECT * FROM student WHERE name = '小李';
流程：在 idx_name_age 上定位 name='小李' 的记录，取出 id；再拿 id 回聚簇索引取整行。
回表的原因很具体：SELECT * 需要 city，而二级索引里只有 name / age / id。
EXPLAIN 特征：key=idx_name_age，Extra 里没有 Using index。
5.2 命运二：覆盖索引
SELECT id, name, age FROM student WHERE name = '小李';
需要的列全部在索引里（id 作为主键值也在叶子节点上），不需要回表。
EXPLAIN 特征：Extra 出现 Using index。
覆盖索引不是一种索引类型，而是一种查询与索引的匹配状态。同一个
idx_name_age，对SELECT id,name,age是覆盖的，对SELECT *就不是。
这也意味着覆盖索引是"写 SQL 的技巧"而不是"建索引的技巧"：杜绝 SELECT *、只查真正需要的列，是把覆盖索引变成常态的前提。
5.3 命运三：索引下推（ICP）
MySQL 5.6 引入的优化，全称 Index Condition Pushdown。
SELECT * FROM student WHERE name LIKE '李%' AND age = 20;
name LIKE '李%' 是范围条件，按最左前缀规则，age 无法继续参与索引定位。区别在于 age = 20 这个条件在哪里被判断：
- 无 ICP：Server 层先让引擎把所有"姓李"的记录回表取出整行，再在 Server 层过滤
age = 20。假设有 1 万个姓李的，就是 1 万次回表。 - 有 ICP：
age就在索引里，引擎层直接用age = 20过滤索引记录，只对真正满足条件的记录回表。
EXPLAIN 特征：Extra 出现 Using index condition。
ICP 的边界要说清楚，这是很多文章一笔带过的地方：
- 下推的条件只能引用该索引包含的列。
WHERE name LIKE '李%' AND city = '北京'中的city不在索引里，无法下推。 - ICP 只适用于需要回表的场景。已经是覆盖索引时，根本没有回表可省。
- ICP 减少的是回表次数，不是索引扫描的行数。索引上要遍历的"姓李"区间还是那么长。
- 由参数
optimizer_switch中的index_condition_pushdown控制，默认开启。
5.4 三种命运的成本对照
| 场景 | 索引扫描 | 回表次数 | 典型 Extra |
|---|---|---|---|
| 普通回表 | 命中区间 | = 命中行数 | 无 Using index |
| 索引下推 | 命中区间 | = 过滤后行数 | Using index condition |
| 覆盖索引 | 命中区间 | 0 | Using index |
优化的方向永远是把回表次数往 0 压。 覆盖索引是终极形态，索引下推是退而求其次的补救。
六、联合索引怎么设计：等值、范围、排序的排列顺序
这一章是索引设计真正的难点。
6.1 最左前缀匹配原则
对 idx_city_age_name (city, age, name)：
| 查询条件 | 索引使用情况 |
|---|---|
city = '北京' | 用到 city |
city = '北京' AND age = 20 | 用到 city + age |
city = '北京' AND age = 20 AND name = '张三' | 三列都用到 |
city = '北京' AND name = '张三' | 只用 city 定位；name 可用于索引内过滤，不能用于定位 |
age = 20 AND name = '张三' | 一般用不上（见 6.2 的例外） |
name = '张三' | 一般用不上 |
联合索引像一串葡萄：想摘中间或末尾那颗，得从最上面开始往下撸，不能凭空从中间下手。
关于书写顺序的常见误区：
SELECT * FROM `user` WHERE age = 20 AND city = '北京';
这条能用上 idx_city_age_name。优化器会重排等值条件的匹配顺序，你不用纠结 WHERE 里的书写顺序，要保证的是"最左连续的那几列都出现了"。
6.2 一个需要更新的认知：跳跃扫描
MySQL 8.0.13 引入了 Skip Scan Range Access（跳跃扫描）。对 (city, age) 这样的索引，WHERE age = 20（缺最左列）在特定条件下可以走索引：优化器枚举 city 的每一个不同值，把查询改写成多段范围扫描。
EXPLAIN 里体现为 Extra: Using index for skip scan。
前置条件是最左列的不同值数量足够少，否则枚举成本超过全表扫描，优化器不会选。所以"最左前缀"依然是设计索引时的主原则，跳跃扫描只是排查执行计划时需要认识的一种形态——不要把它当成可以依赖的设计手段。
6.3 列顺序的排法
给一个可执行的判断顺序：
- 等值条件列放最左，且多个等值列之间按区分度从高到低排。
- 范围条件列紧随其后，且只能有一列吃到范围扫描的红利。
- 排序/分组列跟在等值列后面，用来消掉 filesort。
- 纯粹为了覆盖而带上的列放最后。
第 2 点是最容易踩的：一旦某列用了范围条件，它之后的列就无法再用于索引定位。
-- idx (city, age, name)
SELECT * FROM `user` WHERE city='北京' AND age > 20 AND name='张三';
city 等值 → age 范围定位 → name 不能继续缩小扫描区间，只能在扫到的索引记录上做过滤（ICP）。所以"要不要把 name 放进索引"这个问题，答案取决于你的目的：如果是为了过滤，收益有限；如果是为了覆盖，收益明确。 这两件事要分开评估，不能混为一谈。
6.4 一个完整的设计推演
需求 SQL：
SELECT id, name, age
FROM `user`
WHERE city = '北京' AND age BETWEEN 20 AND 30
ORDER BY age;
按上面的规则推：
city等值 → 放最左；age既是范围条件又是排序列，紧跟city。因为city已被固定为常量，索引中age在该city内部天然有序，范围扫描出来的结果自带age顺序，filesort 可以省掉；name是SELECT列，放第三位让整条 SQL 命中覆盖索引。
ALTER TABLE `user` ADD INDEX idx_city_age_name (city, age, name);
期望的执行计划：
type = rangekey = idx_city_age_nameExtra = Using where; Using index
这里值得多说一句：ORDER BY age 能省掉 filesort，前提是 city 是等值条件。 如果改成 WHERE city IN ('北京','上海') ORDER BY age，多个 city 值各自内部有序、整体无序，优化器可能重新引入排序。这类细节只能靠 EXPLAIN 确认，不能靠推理下结论。
6.5 索引不是越多越好，但也不是越少越好
两个反模式：
- 给每个列都建单列索引。 结果是
WHERE a=? AND b=?这类查询只能用一个索引（或退化为 index merge），回表量大，还白白承担了 N 倍写放大。 - 为每条 SQL 建一个专属联合索引。 结果是索引数量失控、写入变慢、执行计划不稳定。
正确的做法是看能不能合并：(a)、(a,b)、(a,b,c) 三个索引里，前两个基本是冗余的，(a,b,c) 已经覆盖它们的最左前缀场景。MySQL 的 sys 库提供了现成的检查视图：
-- 冗余/重复索引
SELECT * FROM sys.schema_redundant_indexes;
-- 从未被使用过的索引（依赖 performance_schema，需要足够长的观察期）
SELECT * FROM sys.schema_unused_indexes;
schema_unused_indexes 的结果不能直接拿来删索引——统计是从上次实例重启以来累积的，低频的月报、对账、运营后台 SQL 很可能在观察窗口内没跑过。"看起来没人用"和"确实没人用"之间，差的是一个完整的业务周期。
七、Java 侧最容易踩的四个索引坑
前面都是 SQL 视角。但线上的索引问题，相当一部分是在 Java 代码里造成的。
7.1 参数类型不匹配导致的隐式类型转换
这是我见过最高频、也最难在代码评审中发现的一类问题。
-- phone 是 VARCHAR(20)，上面有 idx_phone
// 反例：实体/参数用了 Long
public class UserQuery {
private Long phone; // ← 与列类型 VARCHAR 不一致
}
MyBatis 最终会把参数按数字绑定，SQL 语义等价于 WHERE phone = 13800000000。此时 MySQL 的类型转换规则是把字符串列转成数字再比较，也就是对列做了函数运算，索引无法用于定位。
// 正例：字段类型与列类型对齐
public class UserQuery {
private String phone;
}
<select id="selectByPhone" resultMap="userMap">
SELECT id, name, phone
FROM user
WHERE phone = #{phone,jdbcType=VARCHAR}
</select>
需要区分两个方向，不要记反：
| 列类型 | 传入参数 | 结果 |
|---|---|---|
VARCHAR | 数字 | 列被转成数字，索引不可用 |
INT / BIGINT | 字符串 | 参数被转成数字，索引仍可用 |
排查手段：慢日志里看到"明明有索引却 type=ALL"，第一反应就该去核对列类型与 Java 字段类型是否一致。这类问题在测试环境数据量小的时候完全没有症状。
7.2 动态 SQL 拼出没有最左列的查询
<!-- 危险的动态 SQL：索引是 idx_city_age_name -->
<select id="search" resultMap="userMap">
SELECT id, name, age, city FROM user
<where>
<if test="city != null">AND city = #{city}</if>
<if test="age != null">AND age = #{age}</if>
</where>
</select>
city 传空、只传 age 时，拼出的 SQL 缺最左列，退化成全表扫描。这类问题的特征是只在特定筛选条件组合下爆发，而这种组合往往是运营在后台随手勾出来的。
工程上的做法不是"把所有组合都建索引"，而是划定边界：
public PageResult<UserVO> search(UserQuery q) {
// 必要的过滤维度作为强制条件，把查询收敛到可被索引支撑的形态
if (!StringUtils.hasText(q.getCity())) {
throw new IllegalArgumentException("city 为必填筛选条件");
}
if (q.getPageSize() > MAX_PAGE_SIZE) {
q.setPageSize(MAX_PAGE_SIZE);
}
return userMapper.search(q);
}
能被索引支撑的查询组合是有限的，所以对外暴露的筛选组合也必须是有限的。 把"任意维度自由组合查询"的需求交给数据库，多半会在数据量涨上来之后出事；这类需求更适合走离线数仓或检索引擎。
7.3 深分页：回表次数被 OFFSET 悄悄放大
SELECT * FROM `user` WHERE city = '北京' ORDER BY id LIMIT 1000000, 20;
MySQL 会实际扫描并回表 1000020 行，然后丢弃前 100 万行。OFFSET 越大，浪费的回表越多，而这部分成本在 EXPLAIN 的 rows 里未必看得直观。
两种改法。
改法一：延迟关联（deferred join），先在覆盖索引上分页拿主键，再回表取整行。
SELECT u.*
FROM `user` u
JOIN (
SELECT id FROM `user`
WHERE city = '北京'
ORDER BY id
LIMIT 1000000, 20
) t ON u.id = t.id;
子查询只在 (city, id) 索引上扫，不回表；外层只回表 20 次。
改法二：游标分页（seek method），用上一页的最后一个主键做起点，彻底消掉 OFFSET。
public List<User> nextPage(String city, Long lastId, int size) {
// lastId 为空表示第一页
return userMapper.seekPage(city, lastId, size);
}
<select id="seekPage" resultType="User">
SELECT id, name, age, city
FROM user
WHERE city = #{city}
<if test="lastId != null">AND id > #{lastId}</if>
ORDER BY id
LIMIT #{size}
</select>
游标分页的性能与页码无关，代价是不能跳页。所以选择标准很清楚：
- 用户侧信息流、App 下拉加载、导出任务 → 游标分页；
- 后台管理页面必须显示"第 N 页" → 延迟关联，并对最大页码设上限。
7.4 批量查询里的 IN 列表与循环单查
// 反例：N 次网络往返 + N 次索引查找
for (Long id : ids) {
users.add(userMapper.selectById(id));
}
// 正例：分批 IN 查询，控制单批大小
List<User> users = new ArrayList<>(ids.size());
for (List<Long> batch : Lists.partition(new ArrayList<>(ids), 500)) {
users.addAll(userMapper.selectByIds(batch));
}
两个方向都要有节制：循环单查浪费在网络往返上；IN 列表无上限则可能让优化器放弃索引转全表扫描，还会顶到 max_allowed_packet。批大小需要按实际表结构压测确定，几百量级是常见起点，不是标准答案。
八、线上排查与安全变更
8.1 EXPLAIN 要看的几列
EXPLAIN SELECT id, name, age FROM `user`
WHERE city = '北京' AND age BETWEEN 20 AND 30 ORDER BY age;
| 列 | 关注点 |
|---|---|
type | 从好到坏：const / eq_ref / ref / range / index / ALL。index 是全索引扫描，也不理想 |
key | 实际选用的索引；NULL 表示没用索引 |
key_len | 用到了联合索引的前几列，判断最左前缀生效范围的关键依据 |
rows | 预估扫描行数，注意是估算值 |
filtered | 预估过滤后剩余比例，和 rows 相乘才是回表量级 |
Extra | Using index 覆盖；Using index condition 下推；Using filesort 额外排序；Using temporary 临时表 |
key_len 常被忽略，但它是唯一能告诉你"联合索引到底用到了第几列"的字段。计算方式与列类型、是否可空、字符集有关（例如 utf8 下 VARCHAR(50) NOT NULL 约为 50*4+2）。不必背公式，只需对比"同一索引在不同 SQL 下的 key_len 差异"就能判断哪一列没被用上。
更细的执行信息可以用：
EXPLAIN FORMAT=JSON SELECT ...; -- 看成本估算与各阶段细节
EXPLAIN ANALYZE SELECT ...; -- MySQL 8.0.18+，实际执行并给出真实耗时与行数
EXPLAIN ANALYZE 会真正执行 SQL，线上慎用，且不要对写语句使用。
8.2 优化器选错索引怎么定位
症状是同一条 SQL 时快时慢，或者上线后忽然变慢而代码没改。常见原因有两个。
原因一：统计信息过期。 SHOW INDEX 里的 Cardinality 是采样估算值（受 innodb_stats_persistent_sample_pages 影响，默认采样 20 个页），大批量增删改后可能严重偏离实际，导致成本估算失真。
SHOW INDEX FROM `user`;
ANALYZE TABLE `user`; -- 重新采样统计信息
ANALYZE TABLE 在 InnoDB 上很轻，但会刷新表定义缓存，高峰期仍建议避开。
原因二：确实是成本估算差异。 想知道优化器为什么这么选，用 optimizer trace：
SET optimizer_trace = 'enabled=on';
SELECT id, name FROM `user` WHERE city = '北京' AND age = 20;
SELECT trace FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace = 'enabled=off';
trace 里能看到每个候选索引的成本估算，这比猜测有效得多。
至于 FORCE INDEX 和 8.0.20+ 的 /*+ INDEX(t idx_x) */ 这类提示：它们是止血手段，不是解决方案。 强制指定的索引一旦被后续变更删掉或改名，SQL 直接报错；数据分布变化后，被强制的索引也可能从最优变成最差。用了就要留下注释和复查时间点。
8.3 慢 SQL 的观测闭环
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 按业务 SLA 调整
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- 量大，建议短期开启
配合 mysqldumpslow 或 pt-query-digest 做聚合，找的是总耗时占比最高的 SQL，而不是单次最慢的那条。一条 5 秒的报表 SQL 每天跑一次，远不如一条 200 毫秒、每秒跑 500 次的 SQL 值得优化。
Java 侧要补上应用视角。慢日志只能告诉你"哪条 SQL 慢"，没法告诉你"哪个接口触发的、参数是什么"。常见做法：
- 接入
p6spy（JDBC 代理），把实际执行的 SQL、参数和耗时打到应用日志，和 traceId 关联； - 用 APM / OpenTelemetry 让 SQL span 挂在接口调用链上；
- 在 CI 里加一道关卡：对核心 SQL 跑
EXPLAIN并断言执行计划不退化。
第三点的骨架很简单，价值在于把"索引是否生效"从人工评审变成自动校验：
/** 校验 SQL 的执行计划未退化为全表扫描 */
void assertNotFullScan(DataSource ds, String sql, Object... params) throws SQLException {
try (Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement("EXPLAIN " + sql)) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
String type = rs.getString("type");
String key = rs.getString("key");
if ("ALL".equalsIgnoreCase(type) || key == null) {
throw new AssertionError(
"执行计划退化: type=" + type + ", key=" + key + ", sql=" + sql);
}
}
}
}
}
这段代码要在有代表性数据量的测试库上跑才有意义。空表上的 EXPLAIN 结论没有参考价值——数据量太小时优化器本来就倾向全表扫描。
8.4 线上加索引与删索引
加索引。 MySQL 5.6+ 的 Online DDL 让大部分加索引操作不阻塞 DML，但仍不是零成本：
ALTER TABLE `user` ADD INDEX idx_city_age (city, age), ALGORITHM=INPLACE, LOCK=NONE;
显式写上 ALGORITHM 和 LOCK，可以让不支持的操作直接报错而不是悄悄退化成拷表，这是一个值得养成的习惯。
即便如此，千万级以上的大表仍建议用 gh-ost 或 pt-online-schema-change，原因是：
- Online DDL 期间的变更记录在 row log 里，DDL 结束时要一次性应用，长事务会拖住它；
- MDL（元数据锁）在 DDL 开始和结束时都需要短暂获取，如果此时有长事务或长查询持有该表的 MDL，后续所有请求都会排队，表现为瞬时雪崩；
- 影子表方案可以控速、可暂停、可回滚。
无论哪种方式，都要避开业务高峰，并提前确认没有长事务：
SELECT * FROM information_schema.INNODB_TRX WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10;
删索引。 删索引比加索引更危险，因为它是不可灰度、回滚昂贵的操作（大表重建索引可能要几十分钟）。MySQL 8.0 提供了不可见索引，可以先做一次"软删除"：
-- 让优化器忽略该索引，但索引本身仍在维护
ALTER TABLE `user` ALTER INDEX idx_old INVISIBLE;
-- 观察一到两个业务周期，确认无慢 SQL、无告警后再真正删除
ALTER TABLE `user` DROP INDEX idx_old;
-- 出问题立刻恢复，秒级生效
ALTER TABLE `user` ALTER INDEX idx_old VISIBLE;
会话级验证也很方便：
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
"先隐藏、再观察、后删除"应该成为索引下线的标准流程。 这是 8.0 在索引治理上最实用的一个特性。
九、常见"索引失效"说法的准确版本
网上流传的"索引失效十大原则"大多方向对、表述不严谨。下面逐条给出更准确的版本。
9.1 区分度低的列不适合建索引
更准确的说法：区分度低时，优化器可能判断走索引的成本（索引扫描 + 大量回表）高于全表扫描，从而不选它。
但有两个例外值得记住：
- 如果该列参与覆盖索引，不回表，低区分度也可能有效；
- 如果数据分布极度倾斜（例如
status=99只占 0.01%），查这个稀有值时索引非常有效。优化器靠直方图（MySQL 8.0ANALYZE TABLE ... UPDATE HISTOGRAM ON col）来识别这种倾斜。
所以不要一看到 status、type 就否决索引，要看具体查的是哪个值、结果集占比多少。
9.2 OR 会导致索引失效
更准确的说法：OR 两侧的列都有可用索引时，优化器可能选择索引合并（index merge），Extra 里体现为 Using union(...)；只要有一侧无索引，就只能全表扫描。
SELECT * FROM `user` WHERE name = '张三'
UNION
SELECT * FROM `user` WHERE age = 20;
UNION 会去重（隐含排序或临时表开销），UNION ALL 不去重但可能出现重复行（同时满足两个条件的记录）。改写前先确认哪种语义是业务要的，不能无脑替换。
9.3 列上做运算或函数调用
这一条是确定的：对索引列做函数或运算，B+ 树的有序性就用不上了。
-- 无法用于索引定位
SELECT * FROM `user` WHERE YEAR(create_time) = 2026;
SELECT * FROM `user` WHERE id + 1 = 100;
-- 改写为对列的范围/等值比较
SELECT * FROM `user` WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01';
SELECT * FROM `user` WHERE id = 99;
MySQL 8.0.13+ 可以用函数索引兜住第一种写法，但优先改 SQL，理由见 3.6。
9.4 隐式类型转换
见 7.1。补充一个同源问题：关联字段的字符集或排序规则不一致，JOIN 时也会因为隐式转换而无法使用被驱动表的索引。两张表都是 VARCHAR，但一张 utf8mb4_general_ci、另一张 utf8mb4_0900_ai_ci，就足以触发。这类问题在历史库和新库混用时特别常见。
9.5 LIKE 前缀通配
SELECT * FROM `user` WHERE name LIKE '张%'; -- 可用于索引定位
SELECT * FROM `user` WHERE name LIKE '%张'; -- 不能定位
SELECT * FROM `user` WHERE name LIKE '%张%'; -- 不能定位
原因是 B+ 树按前缀有序，给前缀能二分，给后缀不能。
一个常被忽略的补充：LIKE '%张%' 虽然不能用索引定位，但如果该 SQL 恰好是覆盖索引场景，优化器仍可能选择全索引扫描（type=index，Extra 含 Using index）——因为扫索引树比扫聚簇索引读的页更少。这不是"走了索引"，而是"扫了一棵更小的树"，别把它当成优化成功的信号。
9.6 NULL 的处理
IS NULL / IS NOT NULL 是可以使用索引的，这一点和很多文章说的相反。真正的问题是 NULL 带来的其他成本：
- 三值逻辑让条件判断和
NOT IN、聚合函数的语义变复杂，容易出业务 bug； - 可空列在索引里需要额外的 NULL 标记位，
key_len更大； - 唯一索引在可空列上无法保证业务唯一性（见 3.3）。
所以"所有列都 NOT NULL + 明确默认值"这条建议依然成立，但理由是语义清晰和约束可靠，不是"NULL 会让索引失效"。
9.7 != 与 NOT IN
更准确的说法：这类条件在语义上对应"取补集"，通常匹配大部分行，优化器因此倾向全表扫描。但它不是语法层面的禁用——如果不等值条件的结果集很小，或者能命中覆盖索引，依然可能走索引。
判断标准始终是结果集占全表的比例，而不是操作符本身。
9.8 索引数量上限
"一张表不超过 5 个索引"是经验值，我倾向这样表述：
- 判断依据是读写比例、表大小、查询组合数量，不是一个固定数字；
- 写多读少的表（日志、流水、消息落库）要更保守；
- 超过经验值时，先问"能不能合并成联合索引"，再问"这个查询是不是根本不该走 MySQL"。
十、KEY 与 INDEX 的区别：一个概念澄清
这两个词日常混用，但语义有区别：
- KEY 同时包含约束和索引两层含义：
PRIMARY KEY= 非空唯一约束 + 聚簇索引UNIQUE KEY= 唯一约束 + 唯一索引FOREIGN KEY= 引用完整性约束 + 索引
- INDEX 只表示索引，不带约束语义。
在"建普通索引"这件事上两者完全等价：
CREATE TABLE t1 (id INT, KEY idx_id (id));
CREATE TABLE t2 (id INT, INDEX idx_id (id));
涉及 PRIMARY / UNIQUE / FOREIGN 时用 KEY 更准确，因为它强调了约束含义。团队内统一一种写法比争论哪种对更有价值。
十一、总结
| 概念 | 一句话 |
|---|---|
| 索引本质 | 按列值排好序的目录树，让优化器有机会少读页 |
| 为什么 B+ 树 | 扇出大、树矮、叶子有序，是"按页读磁盘"约束下的最优解 |
| 聚簇索引 | 叶子节点 = 整行数据，每表一个，决定主键要窄要递增 |
| 二级索引 | 叶子节点 = 索引列 + 主键值，成本主要在回表的随机 IO |
| 回表 | 命中多少行就回多少次，是"走了索引还很慢"的头号原因 |
| 覆盖索引 | 一种匹配状态而非索引类型，靠"不写 SELECT *"来常态化 |
| 索引下推 | 只减少回表次数，不减少索引扫描行数，条件必须在索引内 |
| 最左前缀 | 从最左列连续使用；范围条件之后的列不能再用于定位 |
| 联合索引顺序 | 等值 → 范围/排序 → 覆盖列 |
| 前缀索引 | 省空间，但不能覆盖、不能排序，区分度会随数据漂移 |
| EXPLAIN | 重点看 type / key / key_len / rows × filtered / Extra |
| 索引下线 | 8.0 先 INVISIBLE 观察，再 DROP |
两句话收尾：
读操作享受索引带来的收益，写操作承担索引带来的成本。
索引设计的本质不是"让 SQL 变快"，而是"让查询的成本可预测"。
落地清单
- 新表设计时就规划索引，别等上线变慢再补——那时加索引本身就是一次风险操作。
- 一切以
EXPLAIN为准，并且在有代表性数据量的环境上验证。 - 慢日志 +
pt-query-digest常态化，按总耗时占比排序而不是按单次耗时。 - 主键窄且递增；能
NOT NULL就NOT NULL；大字段拆表。 - 对外暴露的筛选组合必须收敛到索引能支撑的范围内。
- 删索引走"先隐藏、再观察、后删除"；大表变更用 gh-ost 或 pt-osc。
- 把执行计划断言加进 CI，让"索引是否生效"变成可回归的测试项。
十二、延伸思考
留几个可以继续往下挖的问题：
- 索引与锁的关系。InnoDB 的行锁是加在索引记录上的。如果一条
UPDATE没走索引，锁的范围会扩大到什么程度？RR 隔离级别下，间隙锁的范围又是由哪个索引决定的？这直接影响线上死锁的排查思路。 - 索引在分库分表后的变化。分片键之外的查询怎么办？异构索引表、全局二级索引、把检索下沉到 ES，各自的一致性窗口和运维成本如何？
- 主键选型的连锁反应。自增 ID 在分布式环境下的冲突、雪花 ID 的单调性、UUID 对聚簇索引的破坏——这三者如何权衡？
- 索引与缓存的边界。什么样的查询应该靠索引解决，什么样的应该靠缓存解决？如果一个查询要靠三层嵌套索引才勉强跑得动，是不是说明它根本不该出现在 OLTP 库里？
这些问题都没有标准答案，取决于你的数据量、读写比和团队的运维能力。但把它们想清楚，比记住十条"索引失效原则"有用得多。
