mysql in与exists问题剖析

最新推荐文章于 2024-05-26 18:48:02 发布

banguiqian3370

最新推荐文章于 2024-05-26 18:48:02 发布

阅读量83

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/ruanjianlaowang/p/11182625.html

版权

1 问题描述

发布当天发现一个日志分析的sql，在测试环境上执行良好，1秒内，而在线上环境上，执行要13秒左右。

嵌套sql一步一步分析后，发现出在in上，因时间紧迫，来补不及具体分析原因，尝试使用exists来解决，秒出（0.03秒）

1.1 具体sql（in），执行时间：12.370

SELECT DISTINCT
            t2.reportdate,
            t2.uid,
            t2.datasetnum,
            t2.visualnum
        FROM
            report_dvnum t2
        WHERE
            t2.reportdate IN (
        
                SELECT
                    max(t3.DAY_SHORT_DESC) AS reportdate
                FROM
                    dim_day t3
                GROUP BY
                    t3.WEEK_ID
                UNION ALL
                    SELECT
                        date_sub(CURDATE(), INTERVAL 1 DAY) AS reportdate
            )

1.2 具体sql（exists），执行时间：0.034

SELECT DISTINCT
    t2.reportdate,
    t2.uid,
    t2.datasetnum,
    t2.visualnum
FROM
    report_dvnum t2
WHERE
EXISTS (
    SELECT
        1
    FROM
        (
            SELECT
                max(t3.DAY_SHORT_DESC) AS reportdate
            FROM
                dim_day t3
            GROUP BY
                t3.WEEK_ID
            UNION ALL
                SELECT
                    date_sub(CURDATE(), INTERVAL 1 DAY) AS reportdate
        ) t4
    WHERE
        t2.reportdate = t4.reportdate

2 事后分析

2.1 首先查看执行计划

sql（in）:

slq(exists):

发现exists多了索引关联的项。

2.2 in与exists区别

exists和in的使用方式：　　

`#对B查询涉及id，使用索引，故B表效率高，可用大表 ``-->外小内大`

　　1、exists是对外表做loop循环，每次loop循环再对内表（子查询）进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（尽量用小表），故内表大的使用exists，可加快效率；

　　2、in是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。

结论简单说：外小内大，用exists，外大内小用in，但是我们的情况其实是外大内小，用in是没问题。。

3 问题解决

问题出在in 里面的union all上，in改成：

SELECT DISTINCT
            t2.reportdate,
            t2.uid,
            t2.datasetnum,
            t2.visualnum
        FROM
            report_dvnum t2
        WHERE
            t2.reportdate IN (
  
                select reportdate from(
                SELECT
                    max(t3.DAY_SHORT_DESC) AS reportdate
                FROM
                    dim_day t3
                GROUP BY
                    t3.WEEK_ID
                UNION ALL
                    SELECT
                        date_sub(CURDATE(), INTERVAL 1 DAY) AS reportdate) t4
            )

使用大括号把 in里面的sql括起来，就会使用索引，0.01秒执行完成。