带WHERE HAVING COUNT(*)的SQL查询执行过慢该如何优化

当前待优化查询全量执行耗时32秒，原始SQL如下：

SELECT accounts.*
FROM accounts
WHERE accounts.account_id IN (SELECT map.account_id 
                              FROM  map 
                              WHERE map.account_id=accounts.account_id                              
                              HAVING COUNT(*)<2) 
ORDER BY rand() 
LIMIT 1

原始SQL性能瓶颈 #

• 子查询为关联子查询，会对accounts表的每一行都执行一次map表的count聚合计算，accounts表数据量越大，重复计算的开销越高
• ORDER BY rand()逻辑会为所有符合条件的记录生成随机值并做全量排序，即使最终只取1条数据，也会加载全量符合条件的数据集到内存计算排序，IO和内存开销极高
• 无对应索引时，map表的count统计、两张表的关联匹配都会走全表扫描，进一步放大性能损耗
• 子查询内部的WHERE map.account_id=accounts.account_id属于冗余关联条件，强制数据库走嵌套循环的关联执行逻辑，是性能差的核心诱因之一

具体优化方案 #

1. 改写查询逻辑，替换低效关联子查询 #

将逐行执行的关联子查询改为单次执行的预聚合结果关联，map表仅需做一次分组聚合即可，避免重复计算：

SELECT a.*
FROM accounts a
INNER JOIN (
    SELECT account_id
    FROM map
    GROUP BY account_id
    HAVING COUNT(*) < 2
) m ON a.account_id = m.account_id

2. 替换ORDER BY rand()的全排序逻辑 #

针对仅随机取1条记录的需求，不需要对全量结果排序，可通过「统计总数→随机偏移取数」的方式大幅降低计算开销，以MySQL环境为例：

-- 统计符合条件的总记录数
SELECT COUNT(*) INTO @total_cnt
FROM accounts a
INNER JOIN (
    SELECT account_id
    FROM map
    GROUP BY account_id
    HAVING COUNT(*) < 2
) m ON a.account_id = m.account_id;

-- 生成随机偏移量
SET @rand_offset = FLOOR(RAND() * @total_cnt);

-- 按偏移量取1条随机记录，无需全量排序
SELECT a.*
FROM accounts a
INNER JOIN (
    SELECT account_id
    FROM map
    GROUP BY account_id
    HAVING COUNT(*) < 2
) m ON a.account_id = m.account_id
LIMIT @rand_offset, 1;

如果业务不允许使用多语句，也可以保留ORDER BY rand()，但因为前面已经把符合条件的结果集通过预聚合大幅缩小，排序开销会比原始SQL低很多。

3. 添加匹配索引减少全表扫描 #

• 给map表的account_id字段创建普通索引：CREATE INDEX idx_map_account_id ON map(account_id);，创建后map表按account_id分组统计count时可直接遍历索引完成，无需回表扫描全表数据
• 确认accounts表的account_id字段为主键或唯一索引，正常业务设计中account_id作为账户主键默认会有该索引，若缺失请补上，关联时可直接通过索引定位到对应行，避免全表扫描

优化效果预期 #

以上优化全部落地后，单表百万级数据量的场景下，查询耗时通常可以降到100毫秒以内，整体计算和IO开销相比原始SQL下降99%以上。

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