重复前缀索引导致排序内存溢出
一条按分组取最新记录的查询,EXPLAIN 明明列出了为排序准备的索引,实际却走了另一条唯一索引,并出现 Using filesort。分组里的行又宽又多时,排序内存不够,报:
ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size
下面用一张去敏后的 note 表复现。MySQL 8.4 上按本文数据可以直接打出这个计划和这个报错。
现象
表上有两条索引:
UNIQUE KEY uk_note_tenant_thread_key (tenant_id, thread_id, item_key)
KEY idx_note_tenant_thread_id (tenant_id, thread_id, id)
查询只按前两列过滤,再按主键倒序取 10 行:
SELECT id, body, detail
FROM note
WHERE thread_id = 'thread-long'
AND tenant_id = 1001
ORDER BY id DESC
LIMIT 10;
错误计划类似:
| key | key_len | rows | Extra |
|---|---|---|---|
uk_note_tenant_thread_key | 1032 | 分组内全部行 | Using index condition; Using filesort |
key_len = 1032 只覆盖 bigint + varchar(255),第三列没有参与等值匹配。LIMIT 10 也拦不住:filesort 先把整个分组排完,再截 10 行。body、detail 这类宽列放进排序行后,分组一大就超过 sort_buffer_size。
原因
两条索引的前两列相同,查询又只用这两列做等值过滤,过滤能力一样。这时优化器会比较索引本身:唯一索引更窄,统计上也更“干净”,等值访问常常选它。
选中之后,ORDER BY id 不在唯一索引里,只能把命中行拉出来排序。真正能按 id 倒序停在第 10 行的是 idx_note_tenant_thread_id,它只是在竞争里输了。
统计信息改不了这件事。绝大多数分组很短,个别分组极长,优化器按平均值估价,计划会一直停在短索引上。把 sort_buffer_size 调大也只是抬高报错门槛,行比缓冲区还宽时照样 1038。
准备环境
用一个空库。已有同名库时,初始化脚本会删掉 idx_lab。
CREATE DATABASE idx_lab DEFAULT CHARSET utf8mb4;
客户端执行:
mysql -u<user> -p idx_lab < 01-init.sql
脚本在 01-init.sql。它会:
- 建
note,唯一索引前缀和排序索引前缀相同 - 写入 800 条宽行,放在
thread-long - 再写入 4000 条短行,每条一个分组,把平均值压低
- 执行
ANALYZE TABLE
800 条宽行大约几十 MB,本机几秒到十几秒。生产库不要拿业务表做这个实验。
复现
先看计划:
EXPLAIN
SELECT id, body, detail
FROM note
WHERE thread_id = 'thread-long'
AND tenant_id = 1001
ORDER BY id DESC
LIMIT 10\G
MySQL 8.0.21 之后,ORDER BY ... LIMIT 会优先尝试能提供顺序的索引。本实验要看的是“等值过滤选了短唯一索引”这一步,所以本会话关掉这个偏好。这只影响当前连接,不改服务器配置。
SET SESSION optimizer_switch = 'prefer_ordering_index=off';
期望看到:
key: uk_note_tenant_thread_key
key_len: 1032
Extra: Using filesort
possible_keys 里应该同时有 idx_note_tenant_thread_id。有这条索引却没用它,就是这个问题。
然后跑原查询。服务器若使用默认 256KB 排序缓冲,这组 20KB 级别的行不一定溢出;把本次会话的缓冲区降到 32KB,就能稳定打出 1038。同样只影响当前连接。
SET SESSION sort_buffer_size = 32768;
SELECT id, body, detail
FROM note
WHERE thread_id = 'thread-long'
AND tenant_id = 1001
ORDER BY id DESC
LIMIT 10;
期望报错:
ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size
完整语句在 02-reproduce.sql。
只查 id 时通常不会报错:排序行很小,缓冲区放得下。报错来自宽列进了排序,不是 LIMIT 写错。
修复
把唯一索引的列顺序改成业务键在前。三个列都参与唯一约束,顺序变化不改变“哪几行算重复”。
ALTER TABLE note
DROP INDEX uk_note_tenant_thread_key,
ADD UNIQUE INDEX uk_note_tenant_thread_key (item_key, tenant_id, thread_id);
ANALYZE TABLE note;
语句在 03-fix.sql。改完后重新开一个连接,或者先执行:
SET SESSION optimizer_switch = DEFAULT;
再看计划:
EXPLAIN
SELECT id, body, detail
FROM note
WHERE thread_id = 'thread-long'
AND tenant_id = 1001
ORDER BY id DESC
LIMIT 10\G
期望变成:
possible_keys: idx_note_tenant_thread_id
key: idx_note_tenant_thread_id
Extra: Backward index scan
不应再出现 Using filesort。同一条 SELECT 应直接返回 10 行。
按三个列点查仍然走唯一索引:
EXPLAIN
SELECT id
FROM note
WHERE tenant_id = 1001
AND thread_id = 'thread-long'
AND item_key = 'key-10'\G
期望 type = const,key = uk_note_tenant_thread_key。三列都是等值条件,列顺序不影响这次查找。
为什么是这个列顺序
| 顺序 | 列表查询 | 三列点查 | 备注 |
|---|---|---|---|
(item_key, tenant_id, thread_id) | 用不上这条索引,只能走排序索引 | 唯一查找 | 本文采用 |
(tenant_id, item_key, thread_id) | 最左列仍是 tenant_id,优化器还可能扫进它 | 唯一查找 | 能用,但没拆干净 |
维持 (tenant_id, thread_id, item_key) | 继续和排序索引抢 | 唯一查找 | 问题还在 |
idx_note_tenant_thread_id 要留着。删掉它,列表查询就没有能按 id 停住的索引。
不要把 FORCE INDEX 写进业务 SQL。索引改名或换库时提示会失效,H2 这类库也不认 MySQL 的索引提示。
落到真实表时
对照关系:
| 实验列 | 含义 |
|---|---|
tenant_id | 过滤条件里选择性一般的列,实验里是数值 |
thread_id | 和上一列一起定位一个分组 |
item_key | 分组内的业务唯一键 |
id | 排序列,实验里就是主键 |
真实表按同样的原则改:唯一索引改成 (业务键, 分组列...),保留 (分组列..., 排序列)。改之前确认没有查询只靠原来的最左前缀,例如只按 tenant_id 扫描。
已上线的表用 ALTER TABLE ... DROP INDEX ... ADD UNIQUE INDEX ...,不要重建整张表。变更期间唯一约束会有一个空档,放在低峰执行。