mysql 关联查询_MySQL派生表优化

在上一篇文章

蓝天：MySQL千万大表优化实践​zhuanlan.zhihu.com

末尾遗留了一个问题，为什么将group by置于子查询内部时，它的查询性能有了非常大的提升呢。其实这是派生表的功劳，派生表在mysql中也称为Derived Table，如果mysql使用了派生表进行了优化，那么在mysql的Explain结果中就会出现derived字样，如下所示

派生表是一种特殊的子查询，它紧随from或者join语句之后，比如下面的语句中t就是一个派生表

SELECT 
	tb_article.`title`,
	tb_user.`name`,
	`total`
from tb_article
	LEFT JOIN (
		    SELECT article_id ,count( 1 ) AS `total` FROM  tb_cmt
		    WHERE tb_cmt.upvote > 100 
		    AND tb_cmt.len in (10 ,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29, 30 )
				GROUP BY article_id
				ORDER BY null
		) t
   on t.article_id=tb_article.id
   INNER JOIN tb_user ON tb_user.id = tb_article.userid
   AND tb_article.create_time BETWEEN  '2019-12-01 00:00:00'  AND '2019-12-31 23:59:59'
   AND tb_article.type IN(
   		        '1213331',
                '1374609',
                '1389750',
                '1204526',
                '1382565',
                '1239054',
                '1321189',
                '1292666'
   	)

派生表的目的就是用于保存子查询的中间结果，以便执行关联查询。上述派生表t在生成的时候需要进行group by，mysql在派生表article_id字段上建立了索引auto_key0，同时这个索引也被mysql用于优化派生表t和tb_article关于article_id字段上的关联查询。

创建派生表是有成本开销的，mysql并不会傻到遇到子查询就创建派生表。上文提到mysql的派生表紧随from 和 join语句之后，但是mysql并不会为每个from和 join后的子查询都创建派生表，请看下面的查询语句和Explain分析。

explain SELECT
	count(*) 
FROM
	( SELECT article_id FROM tb_cmt WHERE create_time BETWEEN '2019-12-01 00:00:00' AND '2019-12-31 23:59:59' ) t1
	 INNER JOIN
	 ( SELECT * FROM tb_article WHERE id > 200 AND id < 1000000 ) t2 
	ON t2.id = t1.article_id

可以看到查询并没有涉及到derived字样，我们再看一下mysql实际改写执行的sql语句

可以看到Mysql实际执行是将子查询展开执行了，如此一来就不需要为子查询创建派生表了，这是mysql5.7引入的对子查询的优化，我们可以执行如下命令关闭mysql的该项优化

关闭derived_merge优化之后的explain输出如下，可以看到mysql关联子查询查询再次涉及到了临时表并针对派生表创建了索引

derived_merge是mysql5.7引入对子查询的优化，虽然关闭该参数可以优化部分查询语句，但是关闭该参数对其它的查询也可能会产生负面影响，我们最好不要将该参数其关闭。

我们再回到上一篇文章遇到的问题，为什么将group by从外部查询移动到子查询内部的时候查询性能就提升了呢？

因为mysql5.7的derived_merge优化在子查询遇到了如下5种情况的时候，derivedmerge优化也便失效了,便会开始使用临时的派生表，而这个派生表上的索引帮助了派生表和tb_article的关联查询

1. UNION

2. GROUP BY

3. DISTINCT

4.用户自定义变量

5. limit

看到这，笔者有一个疑问，mysql是否总会在派生表上创建索引以帮助查询呢呢？笔者首先关闭了derived_merge优化，在上一篇文章中，mysql针对派生表t建立索引auto_key0的目的是为了帮助子查询group，还是为了帮助派生表t和tb_article表进行关联呢？笔者注释了子查询中的group by，explain结果如下所示

可以看到派生表t和tb_article的关联不再使用原先的派生表t上的auto_key0索引了，这说明了mysql在这个查询创建auto_key0的目的并不是为了帮助关联查询。

笔者认为mysql为派生表创建索引目的仅仅是为了加速派生表的生成，除了上述的group by ，笔者再次尝试distinct查询语句，查询语句如下所示

explain 
SELECT
	tb_article.`title`,
	tb_user.`name`
-- 	,
-- 	`total`
from tb_article
	LEFT JOIN (
		    SELECT DISTINCT article_id 
-- 				,count( 1 ) AS `total` 
				FROM  tb_cmt
		    WHERE tb_cmt.upvote > 100 
		    AND tb_cmt.len in (10 ,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29, 30 )
-- 				GROUP BY article_id
-- 				ORDER BY null
		) t -- FORCE INDEX(`<auto_key0>`)
   on t.article_id=tb_article.id
   INNER JOIN tb_user ON tb_user.id = tb_article.userid
   AND tb_article.create_time BETWEEN  '2019-12-01 00:00:00'  AND '2019-12-31 23:59:59'
   AND tb_article.type IN(
   		        '1213331',
                '1374609',
                '1389750',
                '1204526',
                '1382565',
                '1239054',
                '1321189',
                '1292666'
   	)

Explain 结果如下，笔者发现，再次出现了auto_key0索引

如果派生表出现了排序，mysql会不会为派生表建立索引呢

笔者再次进行验证，查询语句如下

SELECT
	tb_article.`title`,
	tb_user.`name`
-- 	,
-- 	`total`
from tb_article
	LEFT JOIN (
		    SELECT article_id 
-- 				,count( 1 ) AS `total` 
				FROM  tb_cmt
		    WHERE tb_cmt.upvote > 100 
		    AND tb_cmt.len in (10 ,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29, 30 )
				order by article_id 
-- 				GROUP BY article_id
-- 				ORDER BY null
		) t -- FORCE INDEX(`<auto_key0>`)
   on t.article_id=tb_article.id
   INNER JOIN tb_user ON tb_user.id = tb_article.userid
   AND tb_article.create_time BETWEEN  '2019-12-01 00:00:00'  AND '2019-12-31 23:59:59'
   AND tb_article.type IN(
   		        '1213331',
                '1374609',
                '1389750',
                '1204526',
                '1382565',
                '1239054',
                '1321189',
                '1292666'
   	)

Explain 结果如下

可以发现mysql子查询在遇到order by的时候， mysql没有为涉及order by的子查询创建索引。

可以看到mysql并不总是为派生表创建索引，如果我们需要mysql为派生表生成索引，在不影响业务输出结果的前提下，我们可以为派生表添加一个distinct或者group by条件，如此一来，既不会影响查询结果，同时又会为派生表创建一个索引加速关联查询