在上一篇文章
蓝天:MySQL千万大表优化实践zhuanlan.zhihu.com末尾遗留了一个问题,为什么将group by置于子查询内部时,它的查询性能有了非常大的提升呢。其实这是派生表的功劳,派生表在mysql中也称为Derived Table,如果mysql使用了派生表进行了优化,那么在mysql的Explain结果中就会出现derived字样,如下所示
![4935603b7e3dc6d44f622597a9f18a3e.png](https://i-blog.csdnimg.cn/blog_migrate/531641f239977b8da69c70351d2c4602.png)
派生表是一种特殊的子查询,它紧随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
![0c1944b87364593d1922b31ec56dd39e.png](https://i-blog.csdnimg.cn/blog_migrate/d5df0bd0490f0ed3834122e04c442c5c.png)
可以看到查询并没有涉及到derived字样,我们再看一下mysql实际改写执行的sql语句
![f076a12a28facb2676fb446d360505f8.png](https://i-blog.csdnimg.cn/blog_migrate/ee366a57fe9101a494797812fade3f3f.png)
可以看到Mysql实际执行是将子查询展开执行了,如此一来就不需要为子查询创建派生表了,这是mysql5.7引入的对子查询的优化,我们可以执行如下命令关闭mysql的该项优化
![112812c96d1b9dc28babad155a8e3de5.png](https://i-blog.csdnimg.cn/blog_migrate/abc9cbfba494a7f686186189a1d6f9a6.png)
关闭derived_merge优化之后的explain输出如下,可以看到mysql关联子查询查询再次涉及到了临时表并针对派生表创建了索引
![7abee8e3f62266f492076c617173b86f.png](https://i-blog.csdnimg.cn/blog_migrate/5d0361b46c5a9b50af0df4cdf6af4353.png)
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结果如下所示
![823313b802332c84ab774bcc543e5868.png](https://i-blog.csdnimg.cn/blog_migrate/6802e899d4a143b3ccfdc05b2f8399dd.jpeg)
可以看到派生表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索引
![a77b958dcb81bcf2351941ab4f34e926.png](https://i-blog.csdnimg.cn/blog_migrate/a70c986e0b42da7bf32f7e30e224f403.jpeg)
如果派生表出现了排序,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 结果如下
![afe7b4b07d4c13bb92e5b319ef3437f2.png](https://i-blog.csdnimg.cn/blog_migrate/8e3ab530f51f764b0954c63c55190e31.png)
可以发现mysql子查询在遇到order by的时候, mysql没有为涉及order by的子查询创建索引。
可以看到mysql并不总是为派生表创建索引,如果我们需要mysql为派生表生成索引,在不影响业务输出结果的前提下,我们可以为派生表添加一个distinct或者group by条件,如此一来,既不会影响查询结果,同时又会为派生表创建一个索引加速关联查询