MySQL复合索引未用于排序问题及查询优化求助

问题场景 #

有一张约7000万条记录的comment表，需要查询指定多个账号的评论，按date降序、date相同时按id降序排序，返回前20条结果。查询语句如下：

SELECT * 
FROM `comment` 
WHERE `account_id` IN ('accountId1','accountId2') 
ORDER BY `date` desc, `id` desc 
LIMIT 20; 

已创建复合索引(account_id, date, id)优化查询，但执行时仍触发filesort，EXPLAIN显示key_len为5，仅用到索引的account_id部分，未利用date和id完成排序。测试发现：

• 排序方向改为asc时问题依旧；
• 使用单账号account_id = 'accountId1'查询时，无filesort，说明问题出在IN多值查询上。
  该查询最高耗时可达20秒，需优化性能。

问题原因 #

MySQL处理IN多值条件时，每个account_id对应的索引片段中，date和id是有序的，但跨多个account_id的结果集无法直接通过索引合并成全局有序的数据集，因此MySQL会先取出所有符合条件的记录，再进行全局filesort，导致性能下降。

优化方案 #

1. 拆分查询+应用层合并 #

将多账号查询拆分为多个单账号查询，分别获取每个账号的前20条评论，再在应用层合并结果并排序，最终取前20条。
单账号查询语句示例：

SELECT * FROM `comment` WHERE `account_id` = 'accountId1' ORDER BY `date` desc, `id` desc LIMIT 20;
SELECT * FROM `comment` WHERE `account_id` = 'accountId2' ORDER BY `date` desc, `id` desc LIMIT 20;

每个单查询都能完整利用复合索引，避免filesort，应用层仅需处理最多40条数据的排序，性能大幅提升。

2. UNION ALL合并后排序 #

用UNION ALL合并多个单账号查询的结果，再排序取前20条。每个子查询添加LIMIT减少中间结果集大小：

(SELECT * FROM `comment` WHERE `account_id` = 'accountId1' ORDER BY `date` desc, `id` desc LIMIT 20)
UNION ALL
(SELECT * FROM `comment` WHERE `account_id` = 'accountId2' ORDER BY `date` desc, `id` desc LIMIT 20)
ORDER BY `date` desc, `id` desc LIMIT 20;

UNION ALL无需去重，比UNION更高效；合并后的结果集最多40条，排序成本极低，远低于原查询的大结果集filesort。

3. 明确索引排序方向（MySQL 8.0+适用） #

如果使用MySQL 8.0及以上版本，可以创建带降序方向的复合索引：

ALTER TABLE `comment` 
ADD INDEX `account_id_date_id_desc` (`account_id`, `date` DESC, `id` DESC); 

但该方案对IN多值查询的优化效果有限，仅作为补充方案。

4. 强制索引（谨慎使用） #

尝试用FORCE INDEX强制使用复合索引，测试是否能避免filesort：

SELECT * 
FROM `comment` FORCE INDEX (`account_id`)
WHERE `account_id` IN ('accountId1','accountId2') 
ORDER BY `date` desc, `id` desc 
LIMIT 20;

注意：MySQL优化器的选择逻辑复杂，该方案效果不稳定，优先考虑前两种方案。

内容的提问来源于stack exchange，提问作者Ali Akbar Azizi