ToprowDB Dynamic Server 查询优化技术---子查询优化--02-4

最新推荐文章于 2024-03-25 06:00:00 发布

原创最新推荐文章于 2024-03-25 06:00:00 发布 · 431 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

289 篇文章

订阅专栏

本文对比分析了PostgreSQL、MySQL及ToprowDB对不同类型的SQL子查询（如ANY/SOME/ALL）的执行计划与优化策略，揭示了各自的特点与优势。

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

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

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

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

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

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

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

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

2 rows in set (0.00 sec)

1.2.3.3 S8语句

查看查询执行计划，子查询被优化（采用半连接）。

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

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

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

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

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

2 rows in set (0.00 sec)

1.2.3.4 S9语句

查看查询执行计划，子查询被优化。

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

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

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

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

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

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

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

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

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

| 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），极大地加快了执行速度，这也是上面的表中用“特定的优化”表达的含义