探讨子查询优化，我们也许会有一些 “故地重游似曾相识”的感觉，感觉子查询和视图优化怎么那么相似？其实，这种感觉，是一种正确的良好的感觉，或者叫做直觉吧。视图和子查询确实相似，从长像看，放在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