SHOW WARNINGS优化SQL
前言:记录下自己实际coding过程中遇到的问题,在度娘一番搜索也没发现啥好的解决方法。网上关于explain执行计划关于
ref=func
的讨论也是比较少的,所以只能求助于MySQL官网的文档,上面会有比较全面的解释。
1.问题发现
业务上有一段SQL需要在 in 的里面使用UNION查询,但是实际的效率随数据量的变化而变化。
- 优化前
EXPLAIN EXTENDED
SELECT
*
FROM
A
WHERE
id IN (
SELECT news_id FROM B WHERE ${condition}
UNION SELECT news_id FROM C WHERE ${condition}
)
SHOW WARNINGS;
对比下原来业务sql
EXPLAIN EXTENDED
SELECT
*
FROM
A
WHERE
id IN (
SELECT news_id FROM B WHERE ${condition}
)
相比之下,与原来的业务区别就是在in里多UNION 了一份数据,实际上C表数据基本没有,在数据量上来说in里的数据前后量级是同等的。所以排除是大In问题。
于是只能借助explain
分析,发现了在关联C的时候ref=func
。看名称怀疑是函数(事实证明确实用了),于是就有了前言那一顿操作。
这里光看执行计划其实在看不出啥,得借助MySQL最终执行语句是怎样才行。
在执行的语句后面跟上SHOW WARNINGS;
,可以显示MySQL实际是如何执行的。
/* select#1 */ SELECT
*
FROM
A
WHERE
< in_optimizer > (
A.`id`,< EXISTS > (
/* select#2 */
SELECT
1
FROM
B
WHERE
( ( B.${condition} ) AND ( < CACHE > ( A.`id` ) = B.`news_id` ) ) UNION
/* select#3 */
SELECT
1
FROM
C
WHERE
( ( C.${condition} ) AND ( < CACHE > ( A.`id` ) = C.`news_id` ) )
)
)
LIMIT 20
- 对比下原sql
/* select#1 */ SELECT
*
FROM
A semi
JOIN ( B )
WHERE
( ( A.`id` = B.`news_id` ) AND ( B.${condition} ) )
LIMIT 20
发现会多出几个<>关键词
注:官方文档给的解释
- < cache> (expr)
表达式(例如标量子查询)执行一次,结果值保存在内存中以备后用。对于包含多个值的结果,可能会创建一个临时表,您可能会看到< temporary table >。
- < exists >(query fragment)
子查询谓词被转换为 EXISTS谓词,并且子查询被转换为可以与EXISTS谓词一起使用 。
- < in_optimizer >(query fragment)
这是一个内部优化器对象,对用户没有任何意义。
2. 解决问题
这里MySQL自己优化后执行,会把A
表的id
分别于B
表和C
表的关联id
做联接,再UNION
。但我们想做的仅是把B
、C
表的结果UNION
起来,然后放入A
表中in
里面查询。
所以可以在B
、C
表的结果UNION
外面套一层子查询
- 优化后
EXPLAIN EXTENDED
SELECT
*
FROM
A
WHERE
id IN ( SELECT news_id from (
SELECT news_id FROM B WHERE ${condition}
UNION SELECT news_id FROM C WHERE ${condition} ) temp
)
LIMIT 20;
SHOW WARNINGS;
这样上面的慢sql问题也就解决了。至于优化前的为什么会导致变慢,我理解的是MySQL在自己优化器的处理下,觉得使用EXISTS
替代子查询会更快,但实际我们并不需要去关心A
表的id
在B
、C
表中的关联关系,只要将B
、C
过滤后的符合条件的放入A
中进行再一次过滤即可。
话说这里的select_type
中的DEPENDENT SUBQUERY
、DEPENDENT UNION
、MATERIALIZED
可能也是排查优化的方向。
最后,在提下SHOW WARNINGS
,第一次使用这个去分析,可以清楚地知道语句的执行流程。