嵌套 mysql 查询的性能损失 [英] Performance penalty for nested mysql queries

问题描述

SELECT * FROM Table VS SELECT * FROM (SELECT * FROM Table AS A) AS B 的性能损失是什么

我的问题是:首先，SELECT * 是否涉及对表中的行进行迭代，还是将所有行作为一个块返回而不进行任何迭代(因为没有给出 WHERE 子句)，如果是的话，嵌套的示例二中的查询涉及对表进行两次迭代，并且将花费第一次查询时间的 2 倍?谢谢...

My questions are: Firstly, does the SELECT * involve iteration over the rows in the table, or will it simply return all rows as a chunk without any iteration (because no WHERE clause was given), and if so does the nested query in example two involve iterating over the table twice, and will take 2x the time of the first query? thanks...

推荐答案

这个问题的答案取决于你使用的是 5.7 之前的 mysql，还是 5.7 及之后的版本.我可能会稍微改变您的问题，但希望以下内容能抓住您的需求.

The answer to this question hinges on whether you are using mysql before 5.7, or 5.7 and after. I may be altering your question slightly, but hopefully the following captures what you are after.

您的 SELECT * FROM Table 通过聚集索引(物理顺序)进行表扫描.在没有主键的情况下，一个是 隐式可用于引擎.没有你说的 where 子句.不会尝试过滤或选择其他索引.

Your SELECT * FROM Table does a table scan via the clustered index (the physical ordering). In the case of no primary key, one is implicitly available to the engine. There is no where clause as you say. No filtering or choice of another index is attempted.

解释输出(见也) 在其摘要中显示 1 行.这是相对直接的.派生表 B 的解释输出和性能将根据您使用的是 5.7 之前的版本还是 5.7 及之后的版本而有所不同.

The Explain output (see also) shows 1 row in its summary. It is relatively straight forward. The explain output and performance with your derived table B will differ depending on whether you are on a version before 5.7, or 5.7 and after.

文档MySQL 5.7 中的派生表 很好地描述了它在 5.6 和 5.7 版本中，后者不会因为物化派生表输出的变化被合并到外部查询中而提供任何惩罚.在以前的版本中，带有派生表的临时表承受了大量开销.

The document Derived Tables in MySQL 5.7 describes it well for versions 5.6 and 5.7, where the latter will provide no penalty due to the change in materialized derived table output being incorporated into the outer query. In prior versions, substantial overhead was endured with temporary tables with the derived.

在 5.7 之前测试性能损失是很容易的.只需要一个中等大小的表即可查看问题的派生表对性能影响的显着影响.下面的例子是在 5.6 版本的一个小表上:

It is quite easy to test the performance penalty prior to 5.7. All it takes is a medium sized table to see the noticeable impact that your question's derived table has on impacting performance. The following example is on a small table in version 5.6:

explain 
select qm1.title  
from questions_mysql qm1 
join questions_mysql qm2 
on qm2.qid<qm1.qid 
where qm1.qid>3333 and qm1.status='O';
+----+-------------+-------+-------+-----------------+---------+---------+------+-------+------------------------------------------------+
| id | select_type | table | type  | possible_keys   | key     | key_len | ref  | rows  | Extra                                          |
+----+-------------+-------+-------+-----------------+---------+---------+------+-------+------------------------------------------------+
|  1 | SIMPLE      | qm1   | range | PRIMARY,cactus1 | PRIMARY | 4       | NULL |  5441 | Using where                                    |
|  1 | SIMPLE      | qm2   | ALL   | PRIMARY,cactus1 | NULL    | NULL    | NULL | 10882 | Range checked for each record (index map: 0x3) |
+----+-------------+-------+-------+-----------------+---------+---------+------+-------+------------------------------------------------+


explain 
select b.title from 
(   select qid,title from questions_mysql where qid>3333 and status='O' 
) b 
join questions_mysql qm2 
on qm2.qid<b.qid; 
+----+-------------+-----------------+-------+-----------------+---------+---------+------+-------+----------------------------------------------------+
| id | select_type | table           | type  | possible_keys   | key     | key_len | ref  | rows  | Extra                                              |
+----+-------------+-----------------+-------+-----------------+---------+---------+------+-------+----------------------------------------------------+
|  1 | PRIMARY     | qm2             | index | PRIMARY,cactus1 | cactus1 | 10      | NULL | 10882 | Using index                                        |
|  1 | PRIMARY     | <derived2>      | ALL   | NULL            | NULL    | NULL    | NULL |  5441 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | questions_mysql | range | PRIMARY,cactus1 | PRIMARY | 4       | NULL |  5441 | Using where                                        |
+----+-------------+-----------------+-------+-----------------+---------+---------+------+-------+----------------------------------------------------+

请注意，我确实更改了问题，但它说明了派生表及其未与优化器一起使用索引在 5.7 之前的版本中的影响.派生表受益于索引，因为它正在被物化.但此后它作为临时表承受开销，并在不使用索引的情况下合并到外部查询中.5.7版本不是这样

Note, I did change the question, but it illustrates the impact that derived tables and their lack of index use with the optimizer has in versions prior to 5.7. The derived table benefits from indexes as it is being materialized. But thereafter it endures overhead as a temporary table and is incorporated into the outer query without index use. This is not the case in version 5.7

这篇关于嵌套 mysql 查询的性能损失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！