ToprowDB Dynamic Server 查询优化技术---子查询优化--01-1
探讨子查询优化，我们也许会有一些 “故地重游似曾相识”的感觉，感觉子查询和视图优化怎么那么相似？其实，这种感觉，是一种正确的良好的感觉，或者叫做直觉吧。视图和子查询确实相似，从长像看，放在SQL语句中，两者都导致了query产生了嵌套；从优化技术看，都是要消除嵌套、把子部分去除合并到父亲节点；所以，本质上，视图和子查询的优化方式是一样的，称之为“上拉/扁平化”优化。（提高效率，扁平化管理，一些歪想，神思千里…….托着下巴，想想扁平化后的好处……）
1 什么样的子查询，可以被优化？
什么样的子查询，可以被优化？
标题有点大，不过不是标题党，前面我们说视图和子查询的优化相似，那么视图如果可以被优化，似乎子查询的优化就是有路可寻的，或者聪明的你一定会想到，SPJ类型的子查询，是可以被优化的，非SPJ类型的子查询，是不可以被优化的，是吧？
1.1 SPJ类型子查询
沿用上一篇视图优化中提供的示例，我们进行如下的子查询：
SELECT * FROM (SELECT * FROM t1,t2) AS v_SPJ, t3;
注意视图优化篇中提及的是：SELECT * FROM v_SPJ, t3;
然后在ToprowDB中得到的查询执行计划可能如下（取决于统计数据导致的代价估算模型的计算值）：-
这里的子查询，是SPJ类型的子查询，与SPJ类型的视图相似，被优化，即子查询被消除。
1.2 非SPJ类型的子查询
非SPJ子查询，类似非SPJ类型的视图，如果子查询的语句中包含有如下子句，这样的子查询，称为非SPJ类型的子查询：
1 GROUP BY子句
2 DISTNICT子句
3 聚集函数
4 带有集合操作: UNION / UNION DISTINCT / UNION ALL
5 带有分析函数、connect by、OUTER JOIN、PL/SQL function等
同样用一个例子来说明：
执行：SELECT * FROM t3, (SELECT DISTINCT t1.b1, t2.b2 FROM t1, t2 GROUP BY t1.b1, t2.b2) AS v_NON_SPJ;
左侧执行计划中的t1和t2，是原始SQL中的t2和t1，这2个表作了连接操作，得到一个临时的结果集（(Temp Table For View)），这说明子查询v_NON_SPJ没有解体，即子查询v_NON_SPJ的query被单独执行了。换句话说，非SPJ类型的子查询不能被优化。
前面2个例子，说明了子查询的优化，和视图优化，确实是相似的，遵循着一样的规律，都是要区分SPJ类型还是非SPJ类型，前者可以被优化而后者不能被优化。
2 所有的SPJ类型子查询/视图都可以被优化吗？
在节，我们提到SPJ类型的子查询/视图是可以被优化的，那么，一个问题是：所有的SPJ类型的子查询/视图都可以被优化吗？
如下将执行的SQL语句为：
S1：SELECT * FROM t3 WHERE b3 IN (SELECT a1 FROM t1);
S2：SELECT * FROM t3 WHERE b3 IN (SELECT id1 FROM t1);
S3：SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1);
S4: SELECT * FROM t3 WHERE b3 IN (SELECT b1 FROM t1 WHERE a1=a3);
2.1 S1语句
SELECT * FROM t3 WHERE b3 IN (SELECT a1 FROM t1) //非相关的子查询
可以得到如下的执行计划：
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.t3: SEQUENTIAL SCAN
Filters: informix.t3.b3 = ANY <subquery> //此处表明IN子查询被转换为ANY，但依旧是子查询
Subquery:
---------
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) informix.t1: SEQUENTIAL SCAN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t3
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6 1 6 00:00.13 4
从上面的执行计划可以看出，非相关子查询没有被优化，嵌套层次依然存在。
2.2 S2语句
SELECT * FROM t3 WHERE b3 IN (SELECT id1 FROM t1) //非相关的子查询，子查询目标列为索引列id1
执行计划如下：
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.t3: SEQUENTIAL SCAN
Filters: informix.t3.b3 = ANY <subquery> //此处表明IN子查询被转换为ANY，但依旧是子查询
Subquery:
---------
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) informix.t1: SEQUENTIAL SCAN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t3
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6 1 6 00:00.06 4
Subquery statistics: //存在子查询
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 3 1 3 00:00.00 2
从上面的执行计划可以看出，ToprowDB对于非相关子查询不支持优化，嵌套层次依然存在。
2.3 S3语句
SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1) //非相关的子查询，子查询左操作符为索引列id1
执行计划如下：
Estimated Cost: 3
Estimated # of Rows Returned: 1
1) informix.t3: INDEX PATH //此处变为索引扫描
(1) Index Name: informix. 104_12
Index Keys: id3 (Serial, fragments: ALL) //id3上存在索引，而子查询值固定，所以可以对t3表执行索引扫描
Lower Index Filter: informix.t3.id3 = ANY <subquery> //此处表明IN子查询被转换为ANY，但依旧是子查询
Subquery:
---------
Estimated Cost: 2
Estimated # of Rows Returned: 1
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t3
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 3 1 3 00:00.10 4
Subquery statistics:
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 3 1 3 00:00.00 2
type rows_sort est_rows rows_cons time
-------------------------------------------------
sort 3 0 3 00:00.02 //在子查询上执行了一个排序操作
从上面的执行计划可以看出，非相关子查询左操作符带有主键索引列没有被优化，嵌套层次依然存在。但是， ToprowDB在子查询上执行了一个排序操作。
从前面的3个例子，我们可以看出，ToprowDB不支持对部分SPJ类型的子查询进行优化。
3 深入探讨
3.1 主流数据库对于例1至例3的优化情况
我们以MySQL 5.6.27和PostgreSQL 9.5为例，看看第二节所述的子查询的在MySQL和PostgreSQL的支持情况。
3.1.1 MySQL
3.1.1.1 S1语句
SELECT * FROM t3 WHERE b3 IN (SELECT a1 FROM t1) //非相关的子查询
可以得到如下的执行计划：
mysql> explain SELECT * FROM t3 WHERE b3 IN (SELECT a1 FROM t1);
+----+-------------+-------+-------+------+----------------------------------------------------+
| id | select_type | table | type | key | Extra |
+----+-------------+-------+-------+------+----------------------------------------------------+
| 1 | SIMPLE | t1 | index | a1 | Using index |
| 1 | SIMPLE | t3 | ALL | NULL | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+------+----------------------------------------------------+
2 rows in set (0.00 sec)
从查询执行计划看，嵌套不存在（id列的值相同表明不存在嵌套层次），子查询被消除，即被优化。
3.1.1.2 S2语句
SELECT * FROM t3 WHERE b3 IN (SELECT id1 FROM t1) //非相关的子查询，子查询目标列为索引列id1
执行计划如下：
mysql> EXPLAIN SELECT * FROM t3 WHERE b3 IN (SELECT id1 FROM t1);
+----+-------------+-------+-------+------+----------------------------------------------------+
| id | select_type | table | type | key | Extra |
+----+-------------+-------+-------+------+----------------------------------------------------+
| 1 | SIMPLE | t1 | index | a1 | Using index |
| 1 | SIMPLE | t3 | ALL | NULL | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+------+----------------------------------------------------+
从查询执行计划看，嵌套不存在，子查询被消除，即被优化。
3.1.1.3 S3语句
SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1) //非相关的子查询，子查询左操作符为索引列id1
执行计划如下：
mysql> explain SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1);
+----+-------------+-------+--------+---------+--------------------------+
| id | select_type | table | type | key | Extra |
+----+-------------+-------+--------+---------+--------------------------+
| 1 | SIMPLE | t1 | index | a1 | Using where; Using index |
| 1 | SIMPLE | t3 | eq_ref | PRIMARY | NULL |
+----+-------------+-------+--------+---------+--------------------------+
从查询执行计划看，嵌套不存在，子查询被消除，即被优化。
从上面的执行计划可以看出，MySQL对于ToprowDB不支持的三种子查询，都进行了优化。
3.1.2 PostgreSQL
3.1.2.1 S1语句
SELECT * FROM t3 WHERE b3 IN (SELECT a1 FROM t1) //非相关的子查询
可以得到如下的执行计划：
postgres=# explain SELECT * FROM t3 WHERE b3 IN (SELECT a1 FROM t1);
QUERY PLAN
------------------------------------------------------------------
Hash Semi Join (cost=55.90..103.00 rows=1020 width=12)
Hash Cond: (t3.b3 = t1.a1)
-> Seq Scan on t3 (cost=0.00..30.40 rows=2040 width=12)
-> Hash (cost=30.40..30.40 rows=2040 width=4)
-> Seq Scan on t1 (cost=0.00..30.40 rows=2040 width=4)
从查询执行计划看，嵌套不存在，子查询被消除变为“Semi Join”，即被优化。
3.1.2.2 S2语句
SELECT * FROM t3 WHERE b3 IN (SELECT id1 FROM t1) //非相关的子查询，子查询目标列为索引列id1
执行计划如下：
postgres=# EXPLAIN SELECT * FROM t3 WHERE b3 IN (SELECT id1 FROM t1);
QUERY PLAN
------------------------------------------------------------------
Hash Semi Join (cost=55.90..103.00 rows=1020 width=12)
Hash Cond: (t3.b3 = t1.id1)
-> Seq Scan on t3 (cost=0.00..30.40 rows=2040 width=12)
-> Hash (cost=30.40..30.40 rows=2040 width=4)
-> Seq Scan on t1 (cost=0.00..30.40 rows=2040 width=4)
从查询执行计划看，嵌套不存在，子查询被消除变为“Semi Join”，即被优化。
3.1.2.3 S3语句
SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1) //非相关的子查询，子查询左操作符为索引列id1
执行计划如下：
postgres=# explain SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1);
QUERY PLAN
------------------------------------------------------------------
Hash Semi Join (cost=55.90..114.35 rows=2040 width=12)
Hash Cond: (t3.id3 = t1.a1)
-> Seq Scan on t3 (cost=0.00..30.40 rows=2040 width=12)
-> Hash (cost=30.40..30.40 rows=2040 width=4)
-> Seq Scan on t1 (cost=0.00..30.40 rows=2040 width=4)
从查询执行计划看，嵌套不存在，子查询被消除变为“Semi Join”，即被优化。
从上面的执行计划可以看出，MySQL和PostgreSQL对于ToprowDB不支持的三种子查询，都进行了优化。
————————————————
版权声明：本文为CSDN博主「那海蓝蓝」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/fly2nn/article/details/61924786
相关文章