mysql 嵌套查询性能_嵌套mysql查询的性能损失

What is the performance penalty for SELECT * FROM Table VS SELECT * FROM (SELECT * FROM Table AS A) AS B

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...

解决方案

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.

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.

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.

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

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

+----+-------------+-----------------+-------+-----------------+---------+---------+------+-------+----------------------------------------------------+

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

+----+-------------+-----------------+-------+-----------------+---------+---------+------+-------+----------------------------------------------------+

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