末尾遗留了一个问题，为什么将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条件，如此一来，既不会影响查询结果，同时又会为派生表创建一个索引加速关联查询
