使用SHOW WARNINGS优化SQL

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外面套一层子查询

1. 优化后

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，第一次使用这个去分析，可以清楚地知道语句的执行流程。