同时有COUNT(DISTINCT)和GROUP BY的慢查询sql优化

本文链接：https://blog.csdn.net/eagle89/article/details/108621846

项目中碰到一个慢查询，里面既有COUNT(DISTINCT)，又有GROUP BY，查询性能很差，26万条数据查询下来需要18秒，sql如下

    SELECT
       a.channel_code AS channelCode,
       a.channel_name AS channelName,
       DATE_FORMAT(a.create_date, '%Y') AS orderDate,
       COUNT(DISTINCT a.order_no) AS orderCount,
       COUNT(DISTINCT a.user_id) AS userCount,
       SUM(a.payment) AS totalAmount,
       SUM(a.content_rate_fee) AS rateAmount
    FROM
       user_order a
    WHERE
       a.del_flag = '0'
    AND a.create_date >= '2017'
    AND a.create_date <= '2018'
    GROUP BY
       a.channel_code,
       DATE_FORMAT(a.create_date, '%Y') DESC
    LIMIT 80,20

以上sql运行时间18秒

网上找到的优化方案都是简单的COUNT(DISTINCT)，没有GROUP BY子句，然后让优化成GROUP BY的形式。但是上面的例子比较复杂，既有COUNT(DISTINCT)又有GROUP BY，这样就不好优化了。有些人建议后台写个定时任务，查出来存起来，这样虽然可行，但是并不是直接解决方案，还需要额外的存储，不灵活。

分析慢查询发现主要因为group by形成了很多分组，然后COUNT(DISTINCT)对每个分组进行虑重，最后只取分页的那20条，其他都丢弃了，这个很浪费性能

经过探索，发现了如下写法，可以极大的优化性能

    SELECT
       b.channel_code AS channelCode,
       b.channel_name AS channelName,
       DATE_FORMAT(b.create_date, '%Y') AS orderDate,
       COUNT(DISTINCT b.order_no) AS orderCount,
       COUNT(DISTINCT b.user_id) AS userCount,
       SUM(b.payment) AS totalAmount,
       SUM(b.content_rate_fee) AS rateAmount
    FROM
       user_order b
    JOIN (
       SELECT
           a.channel_code AS channelCode,
           DATE_FORMAT(a.create_date, '%Y') AS orderDate
       FROM
           user_order a
       WHERE
           a.del_flag = '0'
       AND a.create_date >= '2017'
       AND a.create_date <= '2018'
       GROUP BY
           a.channel_code,
           DATE_FORMAT(a.create_date, '%Y') DESC
       LIMIT 80,20
    ) aa ON b.channel_code = aa.channelCode
    AND DATE_FORMAT(b.create_date, '%Y') = aa.orderDate
    AND b.del_flag = '0'
    GROUP BY
       b.channel_code,
       DATE_FORMAT(b.create_date, '%Y') DESC