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

问题描述

SELECT * FROM Table VS SELECT * FROM (SELECT * FROM Table AS A) AS B

我的问题是:首先，SELECT *是否涉及对表中的行进行迭代，还是将它作为一个块简单地返回所有行，而不进行任何迭代(因为未提供WHERE子句)，如果嵌套，则进行嵌套示例2中的查询涉及对表进行两次迭代，并且将花费第一次查询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以及之后的mysql.我可能会稍微改变您的问题，但希望以下内容能抓住您的需要.

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.

解释输出(请参见

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屋！