PostgreSQL子查詢最佳化詳解

1.2.2 PostgreSQL

1.2.2.1 S6 語句

查看查詢執行計畫，子查詢被最佳化（採用物化的方式，使得子查詢結果被緩衝，既子查詢只執行一次）。

postgres=# EXPLAIN SELECT * FROM t3 WHERE b3 >= ANY (SELECT b1 FROM t1);

Nested Loop Semi Join   (cost=0.00..41707.39 rows=680 width=12)

Join Filter: (t3.b3 >= t1.b1)

->   Seq Scan on t3   (cost=0.00..30.40 rows=2040 width=12)

->   Materialize   (cost=0.00..40.60 rows=2040 width=4)

->   Seq Scan on t1   (cost=0.00..30.40 rows=2040 width=4)

1.2.2.2 S7 語句

查看查詢執行計畫，子查詢被最佳化（採用半串連）。

postgres=# EXPLAIN SELECT * FROM t3 WHERE b3 >= ANY (SELECT a1 FROM t1);

Nested Loop Semi Join   (cost=0.15..377.82 rows=680 width=12)

->   Seq Scan on t3   (cost=0.00..30.40 rows=2040 width=12)

->   Index Only Scan using t1_a1_key on t1   (cost=0.15..12.08 rows=680 width=4)

Index Cond: (a1 <= t3.b3)

1.2.2.3 S8 語句

查看查詢執行計畫，子查詢被最佳化（採用半串連）。

postgres=# EXPLAIN SELECT * FROM t3 WHERE b3 <= SOME (SELECT a1 FROM t1);

Nested Loop Semi Join   (cost=0.15..377.82 rows=680 width=12)

->   Seq Scan on t3   (cost=0.00..30.40 rows=2040 width=12)

->   Index Only Scan using t1_a1_key on t1   (cost=0.15..12.08 rows=680 width=4)

Index Cond: (a1 >= t3.b3)

1.2.2.4 S9 語句

查看查詢執行計畫，子查詢被最佳化。

postgres=# EXPLAIN SELECT * FROM t3 WHERE b3 = SOME (SELECT a1 FROM t1);

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)

1.2.2.5 S10 語句

查看查詢執行計畫，子查詢沒有被最佳化。

postgres=# EXPLAIN SELECT * FROM t3 WHERE b3 <= ALL (SELECT a1 FROM t1);

Seq Scan on t3   (cost=0.00..46649.50 rows=1020 width=12)

Filter: (SubPlan 1)

SubPlan 1

->   Materialize   (cost=0.00..40.60 rows=2040 width=4)

->   Seq Scan on t1   (cost=0.00..30.40 rows=2040 width=4)

1.2.2.6 S11 語句

查看查詢執行計畫，子查詢被最佳化（採用半串連）。

查看查詢執行計畫，子查詢沒有被最佳化。

postgres=# EXPLAIN SELECT * FROM t3 WHERE b3 <= ALL (SELECT a1 FROM t1 WHERE a3=a1);

Seq Scan on t3   (cost=0.00..8527.00 rows=1020 width=12)

Filter: (SubPlan 1)

SubPlan 1

->   Index Only Scan using t1_a1_key on t1   (cost=0.15..8.17 rows=1 width=4)

Index Cond: (a1 = t3.a3)

1.2.3 MySQL

1.2.3.1 S6 語句

查看查詢執行計畫，子查詢沒有被最佳化。

mysql> EXPLAIN SELECT * FROM t3 WHERE b3 >= ANY (SELECT b1 FROM t1);

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

| id | select_type | table | type | key | Extra        |

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

| 1 | PRIMARY      | t3     | ALL   | NULL | Using where |

| 2 | SUBQUERY     | t1     | ALL   | NULL | NULL         |

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

2 rows in set (0.04 sec)

1.2.3.2 S7 語句

查看查詢執行計畫，子查詢沒有被最佳化（但注意“Select tables optimized away”，表明子查詢被使用特有的技術進行了最佳化，但不是常規的子查詢被上拉的最佳化方式）。

mysql> EXPLAIN SELECT * FROM t3 WHERE b3 >= ANY (SELECT a1 FROM t1);

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

| id | select_type | table | type | key | Extra                         |

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

| 1 | PRIMARY      | t3     | ALL   | NULL | Using where                   |

| 2 | SUBQUERY     | NULL   | NULL | NULL | Select tables optimized away |

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

2 rows in set (0.00 sec)

1.2.3.3 S8 語句

查看查詢執行計畫，子查詢被最佳化（採用半串連）。

mysql> EXPLAIN SELECT * FROM t3 WHERE b3 <= SOME (SELECT a1 FROM t1);

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

| id | select_type | table | type | key | Extra                         |

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

| 1 | PRIMARY      | t3     | ALL   | NULL | Using where                   |

| 2 | SUBQUERY     | NULL   | NULL | NULL | Select tables optimized away |

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

2 rows in set (0.00 sec)

1.2.3.4 S9 語句

查看查詢執行計畫， 子查詢被最佳化 。

mysql> EXPLAIN SELECT * FROM t3 WHERE b3 = SOME (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)

1.2.3.5 S10 語句

查看查詢執行計畫，子查詢沒有被最佳化。

mysql> EXPLAIN SELECT * FROM t3 WHERE b3 <= ALL (SELECT a1 FROM t1);

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

| id | select_type | table | type | key   | Extra        |

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

| 1 | PRIMARY      | t3     | ALL    | NULL | Using where |

| 2 | SUBQUERY     | t1     | index | a1    | Using index |

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

2 rows in set (0.00 sec)

1.2.3.6 S11 語句

查看查詢執行計畫，子查詢沒有被最佳化。

mysql> EXPLAIN SELECT * FROM t3 WHERE b3 <= ALL (SELECT a1 FROM t1 WHERE a3=a1);

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

| id | select_type | table | type | key   | Extra                     |

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

| 1 | PRIMARY             | t3     | ALL   | NULL | Using where               |

| 2 | DEPENDENT SUBQUERY | t1     | ref   | a1    | Using where; Using index |

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

2 rows in set (0.00 sec)

1.2.4 對比

我們把原始的 SQL 陳述式複製一份，目的是查閱方便。

S6: SELECT * FROM t3 WHERE b3 >= ANY (SELECT b1 FROM t1);

S7: SELECT * FROM t3 WHERE b3 >= ANY (SELECT a1 FROM t1);

S8: SELECT * FROM t3 WHERE b3 <= SOME (SELECT a1 FROM t1);

S9: SELECT * FROM t3 WHERE b3 = SOME (SELECT a1 FROM t1);

S10: SELECT * FROM t3 WHERE b3 <= ALL (SELECT a1 FROM t1);

S11: SELECT * FROM t3 WHERE b3 <= ALL (SELECT a1 FROM t1 WHERE a3=a1 );

然後對比如下 :

SQL語句

語句特點

ToprowDB

PostgreSQL

MySQL

S6

非相互關聯的子查詢， >= ANY 操作， b1 普通列

Join 最佳化

Semi Join 最佳化 + 物化（非相互關聯的子查詢）

沒有被最佳化

S7

非相互關聯的子查詢， >= ANY 操作， a1 索引鍵

Join 最佳化

Semi Join 最佳化 + 索引掃描

特定的最佳化

S8

非相互關聯的子查詢， <= SOME 操作， a1 索引鍵

Join 最佳化

Semi Join 最佳化 + 索引掃描

S9

非相互關聯的子查詢， = SOME 操作， a1 索引鍵

沒有被最佳化

Semi Join 最佳化

被最佳化

S10

非相互關聯的子查詢， <= ALL 操作， a1 索引鍵

沒有被最佳化

沒有被最佳化

特定的最佳化

S11

相互關聯的子查詢 ， <= ALL 操作， a1 索引鍵 ，條件 a3=a1 為 索引鍵

Semi Join 最佳化

沒有被最佳化

特定的最佳化

分析：

q   從整體上看，對於 ANY/SOME/ALL 類型的子查詢， PostgreSQL 、 MySQL 、 ToprowDB 各有特色， MySQL 表面上沒有上拉子查詢顯得弱一些，但是內部啟用了索引在子查詢的目標列上進行聚集運算（利用索引求最大值最小值，對比 S6 和 S7 ），極大地加快了執行速度，這也是上面的表中用“特定的最佳化”表達的含義

q   對比 S8 和 S9 ，可以看出， ToprowDB 對 S9 沒有提供最佳化，而 PostgreSQL 、 MySQL 能夠最佳化，這一點 ToprowDB 需要努力

q   對比 S11 ， ToprowDB 和 MySQL 強於 PostgreSQL

在子查詢最佳化的第一篇中，我們發現 MySQL 的 IN 子查詢最佳化技術高於其他資料庫，而本篇重， EXISTS 類型的子查詢， MySQL 又弱於 PostgreSQL 、 ToprowDB ，而 ANY/SOME/ALL 類型的子查詢三者幾乎打平，所以我們可以認為三者子查詢的最佳化能力各有所長。

1.3 還有其他類型的子查詢嗎？

前面幾節，看到了形形色色的各種子查詢語句，種類眾多顯得琳琅滿目，子查詢的內容和形式十分地豐富多彩。但是，這些尚沒有囊括到所有的類型，比如 NOT IN 、 NOT EXISTS 、各種 ANY （ =ANY 、 >ANY 、 >=ANY 、 <ANY 、 <=ANY ）和類型 ANY 的各種 SOME 、各種 ALL 等等。