如何优化MySQL统计查询性能，解决count distinct导致查询变慢问题

问题原因 #

你当前的写法性能差的核心原因是：先对两表做全量左连接生成远大于商品表规模的中间临时表，再在大结果集上执行count(distinct)去重统计，计算量随订单表数据量线性上涨。

优化方案 #

方案1：修改查询逻辑，先统计订单再关联商品（无额外改造成本，性能提升最明显） #

先在子查询里提前按hid聚合订单表，每个商品仅返回1行统计结果，再和商品表关联，避免生成大中间表，优化后的SQL如下：

select  t.*,
        t.hdate, t.title,
        ifnull(b.zuid, 0) as zuid,
        ifnull(b.zbuy, 0) as zbuy
from product_today t
left join (
    select 
        hid,
        count(distinct uid) as zuid,
        sum(buy) as zbuy
    from product_today_buy
    group by hid
) b on b.hid = t.hid
where date(t.dtime) = ?
group by t.sid
order by t.dtime desc

如果订单表存在跨日数据，可以在子查询里也加上时间过滤条件进一步缩小统计范围。

方案2：新增覆盖索引（搭配方案1使用，性能可再提升5-10倍） #

给两张表新增联合覆盖索引，让查询直接走索引无需回表读取数据：

• 给product_today加索引：ALTER TABLE product_today ADD INDEX idx_dtime_sid_hid(dtime, sid, hid, hdate, title); （可覆盖你查询要用到的所有字段）
• 给product_today_buy加索引：ALTER TABLE product_today_buy ADD INDEX idx_hid_uid_buy(hid, uid, buy); （订单表的统计逻辑可以完全走索引完成）

方案3：预统计（适合实时性要求低于1分钟的场景，查询速度可恢复到原0.001秒水平） #

可以在product_today表新增zuid和zbuy两个字段，写定时任务每分钟按hid维度统计订单表的最新数据更新到商品表，查询时直接读商品表单表即可，完全不需要关联订单表。

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