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

2022-03-30 00:00:00 查询 语句 执行 优化 计划

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等等。

这些格式的子查询,我们不再举例,留待大家自己探讨。
————————————————
版权声明:本文为CSDN博主「那海蓝蓝」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/fly2nn/article/details/61924795

相关文章