带COUNT与GROUP BY的MySQL查询已建索引仍缓慢问题排查

我在一张约72万行的InnoDB表上执行以下查询，每次耗时2.5秒：

SELECT userId, COUNT(*) AS c 
FROM my_table 
WHERE someId = 11 AND userId != 0 
GROUP BY userId 
ORDER BY c DESC 
LIMIT 0, 5; 

已为userId和someId建立单独索引，但速度未达标；尝试过(someId, userId)和(userId, someId)联合索引，也没有改善。

执行计划（EXPLAIN） #

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra 
1   SIMPLE         my_table  ref    userId,someId  someId     4        const    6895    Using where; Using temporary; Using filesort

表结构（精简后） #

CREATE TABLE `my_table` (
  `userId` int(11) NOT NULL DEFAULT 0,
  `someId` int(11) NOT NULL,  
  # 其他无关列
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `someId` (`someId`),
  KEY `userId` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=746173

表状态信息 #

问题根源 #

当前执行计划显示：

1. 仅使用了someId单值索引，返回6895行符合someId=11的数据
2. 需要额外过滤userId!=0、创建临时表做分组、文件排序，这三个步骤是耗时的核心原因
3. 尝试的联合索引可能未被正确选用，或未形成覆盖索引，导致仍需回表读取数据

优化方案 #

1. 创建正确的覆盖联合索引

删除原有单值索引，创建(someId, userId)联合索引：

DROP INDEX someId ON my_table;
DROP INDEX userId ON my_table;
CREATE INDEX idx_someid_userid ON my_table(someId, userId);

这个索引的优势：

• 直接通过someId=11定位目标数据范围，无需回表（索引已包含userId，满足查询所有需求）
• 索引内userId是有序的，相同userId的条目连续，分组时无需临时表直接累加计数
• 过滤userId!=0也可在索引扫描阶段完成，减少无效数据处理

2. 验证执行计划

重新执行EXPLAIN，理想结果应包含：

• key字段显示idx_someid_userid
• Extra字段显示Using index（覆盖索引），且不再出现Using temporary

3. 针对排序的补充优化

如果仍存在Using filesort，可尝试通过子查询预先统计，再排序取前5：

SELECT userId, c
FROM (
    SELECT userId, COUNT(*) AS c
    FROM my_table
    WHERE someId = 11 AND userId != 0
    GROUP BY userId
) AS temp
ORDER BY c DESC
LIMIT 5;

不过对于6895行的分组结果，排序开销本应极小，若仍慢需检查MySQL配置：

• 确保sort_buffer_size足够大，避免排序时使用磁盘临时文件
• 检查tmp_table_size和max_heap_table_size，保证临时表可在内存中创建

4. 其他排查点

• 确认表碎片情况：执行OPTIMIZE TABLE my_table（注意锁表，需在业务低峰操作）
• 检查服务器磁盘IO负载，磁盘性能不足也会导致查询缓慢

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