多表关联group by优化：去除Using temporary; Using filesort

  最近因为项目很赶，很久没写博客了，今天算是已经把项目进度超前完成了，写一个今天解决的SQL优化问题。

  这次优化花了半小时，但很值得，之前作为一个单纯后端，很少关注SQL效率，但是自从上个项目当上临时DBA后，或多或少的都会关注自己书写的SQL执行计划。

   原始第一映像写出的脚本： 

   EXPLAIN
SELECT
    service_id,service_name,count(1)
FROM
    order_orders t1,
    order_user_coupon t2
WHERE
    t1.coupon_id = t2.coupon_id
AND t1.merchant_id = '1'
AND (
    t1.update_time BETWEEN str_to_date(
        '2019-01-01 00:00:00',
        '%Y-%m-%d %H:%i:%s'
    )
    AND str_to_date(
        '2019-03-06 00:00:00',
        '%Y-%m-%d %H:%i:%s'
    )
)
group by service_id;

执行计划如下：

两张表各种加索引，没用，后来仔细分析了一下，之所以产生临时表，是因为整个查询过程，你首先要通过关联条件查询出一个结果集，然后再根据这个结果集进行分组排序，自然就会有临时表和文件排序。

 

第二次优化脚本如下：

EXPLAIN SELECT
    t1.service_id,
    t1.service_name,
    count(1)
FROM
    order_user_coupon t1
WHERE
    t1.coupon_id in(
        SELECT
            coupon_id
        FROM
            order_orders t2
        WHERE
     t2.merchant_id = '1'
        AND t2.update_time BETWEEN str_to_date(
            '2019-01-01 00:00:00',
            '%Y-%m-%d %H:%i:%s'
        )
        AND str_to_date(
            '2019-03-06 00:00:00',
            '%Y-%m-%d %H:%i:%s'
        )
    )
GROUP BY
    t1.service_id;

本想着，避免了关联查询，使用t1表的结果集做分组，应该能避免临时表及文件排序，结果没想到in中的查询使用了，好吧，废弃。

 

第三次优化如下：

EXPLAIN SELECT
    t1.service_id,
    t1.service_name,
    count(1)
FROM
    order_user_coupon t1
WHERE
    EXISTS (
        SELECT
            coupon_id
        FROM
            order_orders t2
        WHERE
            t1.coupon_id = t2.coupon_id
        AND t2.merchant_id = '1'
        AND t2.update_time BETWEEN str_to_date(
            '2019-01-01 00:00:00',
            '%Y-%m-%d %H:%i:%s'
        )
        AND str_to_date(
            '2019-03-06 00:00:00',
            '%Y-%m-%d %H:%i:%s'
        )
    )
GROUP BY
    t1.service_id;

思路：以条件筛选出t2表中数据，以少量数据作为驱动，查询t1表关联数据，之后基于t1表的结果集进行group by操作。

完美解决！

 

最后优化索引：

1、 group by 后的字段一定是要添加索引的，否则还是 临时表进行排序；

2、 这里merchant_id基本能完成大部分数据筛选了，所以添加merchant_id的索引，而不再添加coupon_id索引。

最终结果如下：

Using Index Condition

     在MySQL 5.6版本后加入的新特性（Index Condition Pushdown）;会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；

够用了，完美！