派生表
优化器可以使用两种策略来处理派生表引用（这也适用于视图引用）：
- 将派生表合并到外部查询中（5.7引入的优化策略 derived_merge）；
- 将派生表物化为内部临时表，再用于外部查询。
什么是派生表？形如以下子查询结果作为表对象的就是派生表：
SELECT * FROM (SELECT * FROM t1) AS derived_t1;
SELECT * FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1WHERE t1.f1 > 0;通过 derived_merge 策略，上面两个派生子查询执行时等同：
SELECT * FROM t1;
SELECT t1.*, t2.f1 FROM t1 JOIN t2 ON t1.f2=t2.f1 WHERE t1.f1 > 0;限制
当子查询包含以下操作时，derived_merge 策略失效：
- Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
- DISTINCT
- GROUP BY
- HAVING
- LIMIT
- UNION or UNION ALL
- Subqueries in the select list
- Assignments to user variables
- Refererences only to literal values (in this case, there is no underlying table)
如果 derived_merge 策略失效，将按以下策略执行查询：
- 在需要派生表之前，优化器会推迟派生表的物化，这能提高性能。举例：表 t1 与派生表进行联接，如果处理 t1 表时返回结果为空，则不需要进行下一步联接，这是可以完全跳过派生表的物化；
- 查询执行期间，优化器会给物化派生表添加索引，提升效率。
对于第二点，可以看这个例子：
mysql> explain select * from t1 join (select distinct a from t2) as derived_t2 on t1.a=derived_t2.a;
+----+-------------+------------+------------+-------+---------------+-------------+---------+----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-------------+---------+----------------+------+----------+-------------+
| 1 | PRIMARY | t1 | NULL | ALL | a | NULL | NULL | NULL | 100 | 100.00 | Using where |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 5 | join_test.t1.a | 10 | 100.00 | Using index |
| 2 | DERIVED | t2 | NULL | index | a | a | 5 | NULL | 1000 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------------+---------+----------------+------+----------+-------------+
可以看到派生表确实是走索引的。不过也不是所有情况下都会给派生表添加索引，官档上原文：
The optimizer constructs an index over column f1 from derived_t2 if doing so would enable use of ref access for the lowest cost execution plan.
参考资料
- https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html
- https://www.jianshu.com/p/06e76b819319
https://blog.xqlee.com/article/2504231259401286.html
评论
相关文章
SQL : 原始不含子查询，排序正常SELECT id, goods_id, create_time, price FROM mkt_price_control
函数使用mysql>l; explain SELECT a.custid, b.score, b.xcreditscore, b.lrscore FROM(SELE
SQL SERVER数据库中SUBSTRING函数的使用SELECT SUBSTRING(a.receiveTime,0,20) formatReceiveTi
sql server 2016数据库导出表数据/表结构1.打开Microsoft SQL Server Management Studio工具选择要导出表的数据
max_allowed_packet 表示 MySQL Server 或者客户端接收的 packet 的最大大小，packet 即数据包，MySQL Serve