mysql in与exists问题剖析
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秒执行完成。
更多信息请关注公众号:「软件老王」,关注不迷路,IT技术与相关干货分享,回复关键字获取对应干货,本文版权归作者软件老王所有,转载需注明作者、超链接,否则保留追究法律责任的权利。