跳到主要内容

重复前缀索引导致排序内存溢出

一条按分组取最新记录的查询,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;

错误计划类似:

keykey_lenrowsExtra
uk_note_tenant_thread_key1032分组内全部行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 ...,不要重建整张表。变更期间唯一约束会有一个空档,放在低峰执行。