MySQL多列排序致索引失效，如何优化SQL A的查询速度？

场景还原 #

表结构 #

CREATE TABLE ta (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) DEFAULT ''
);
CREATE TABLE tb (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    aid BIGINT REFERENCES ta,
    home VARCHAR(20) DEFAULT '',
    INDEX tb_aid (aid)
);

数据规模 #

• ta表：40万条记录
• tb表：20万条记录

问题现象 #

• SQL A（执行耗时3秒）：需按ta.id DESC, tb.id DESC排序后取前10条

SELECT *
FROM ta LEFT JOIN tb ON ta.id = tb.aid
ORDER BY ta.id DESC, tb.id DESC
LIMIT 10;

• SQL B（仅移除tb.id DESC排序条件，执行耗时74ms）：

SELECT *
FROM ta LEFT JOIN tb ON ta.id = tb.aid
ORDER BY ta.id DESC
LIMIT 10;

性能差异原因 #

SQL B之所以快，核心是仅按ta.id DESC排序：

1. ta的主键为自增类型，主键索引本身有序，数据库可直接从索引末尾取前10条ta记录，无需全表扫描
2. 关联tb时，利用已有的tb_aid索引快速匹配对应记录，最终仅需整理少量关联结果，无大规模排序操作

而SQL A的双排序条件ta.id DESC, tb.id DESC导致：
数据库无法利用现有索引完成联合排序，只能先执行全表LEFT JOIN得到所有关联结果，再对几十万条结果集做filesort（文件排序），这是耗时的核心原因。

优化方案 #

方案1：创建针对性联合索引 #

给tb表创建联合索引idx_aid_id_desc (aid, id DESC)：

CREATE INDEX idx_aid_id_desc ON tb(aid, id DESC);

原理：

该索引按aid分组、id降序存储tb记录。执行SQL A时：

1. 数据库从ta的主键索引直接取id DESC的记录
2. 关联tb时，通过idx_aid_id_desc索引直接获取对应aid下已按id DESC排序好的tb记录
3. 整个排序过程完全依赖索引完成，无需对全量结果集做filesort

方案2：改写SQL，缩小排序范围 #

先通过子查询获取前10条ta记录（利用ta主键索引的有序性），再关联tb并排序：

SELECT ta.*, tb.*
FROM (SELECT * FROM ta ORDER BY id DESC LIMIT 10) ta
LEFT JOIN tb ON ta.id = tb.aid
ORDER BY ta.id DESC, tb.id DESC;

原理：

• 子查询仅获取10条ta记录，耗时极短
• 关联tb时利用tb_aid或上述联合索引快速匹配
• 最终仅需对这10条ta关联出的少量tb记录做排序，排序成本可忽略不计

验证方式 #

执行EXPLAIN查看优化后的执行计划：

• 若Extra列无Using filesort，说明排序已通过索引完成，性能会大幅提升

内容的提问来源于stack exchange，提问作者water