ToprowDB Dynamic Server 查询优化技术---子查询优化--01-2
在ToprowDB和MySQL执行同样的查询，用WHERE条件使得子查询为相关子查询，即前面的S4语句：
SELECT * FROM t3 WHERE a3 IN (SELECT a1 FROM t1 WHERE b1=b3);
ToprowDB的查询执行计划如下：
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.t3: SEQUENTIAL SCAN
2) informix.t1: INDEX PATH
Filters: informix.t1.b1 = informix.t3.b3
(1) Index Name: informix. 102_7
Index Keys: a1 (Serial, fragments: ALL)
Lower Index Filter: informix.t3.a3 = informix.t1.a1
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t3
t2 t1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6 1 6 00:00.00 2
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 3 1 3 00:00.01 0
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 3 1 00:00.02 4
从查询执行计划可以看出，子查询已经被消除，变为了两表的嵌套循环连接，这说明ToprowDB对此类的子查询进行了优化。看到这里，Informix迷们该常舒一口气了吧，Informix子查询总算没有那么差，是吧？^_^
PostgreSQL的查询执行计划如下：
postgres=# explain SELECT * FROM t3 WHERE a3 IN (SELECT a1 FROM t1 WHERE b1=b3);
QUERY PLAN
------------------------------------------------------------
Seq Scan on t3 (cost=0.00..36271.00 rows=1020 width=12)
Filter: (SubPlan 1)
SubPlan 1
-> Seq Scan on t1 (cost=0.00..35.50 rows=10 width=4)
Filter: (b1 = t3.b3)
从查询执行计划可以看出，子查询没有被消除，这说明PosatgreSQL对此类的子查询不能优化，这是因为PosatgreSQL此处不支持对相关子查询的优化。
MySQL的查询执行计划如下：
mysql> explain SELECT * FROM t3 WHERE a3 IN (SELECT a1 FROM t1 WHERE b1=b3);
+----+-------------+-------+------+------+-------------+
| id | select_type | table | type | key | Extra |
+----+-------------+-------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | Using where |
| 1 | SIMPLE | t3 | ref | a3 | Using where |
+----+-------------+-------+------+------+-------------+
从查询执行计划可以看出，子查询已经被消除，变为了两表的嵌套循环连接，这说明MySQL对此类的子查询进行了优化。比较上面PostgreSQL的例子，我们可以发现，IN子查询的为非相关子查询的时候，MySQL也可以支持优化。
看到这里，MySQL迷们会继续兴高采烈，MySQL子查询优化很强，是吧？^_^
3.3 继续深入分析--相关子查询，不带有索引键
细心的读者可以发现，上一节中，我们选取的例子，都是相关子查询，而且，子查询左表达式的对象是一个索引列(a3列上存在索引)，ToprowDB和MySQL优化器的能够对这样的子查询进行优化。
现在，我们修改SQL语句如下，注意变化的部分：
SELECT * FROM t3 WHERE b3 IN (SELECT b1 FROM t1 WHERE a1=a3);
ToprowDB的查询执行计划如下：
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.t3: SEQUENTIAL SCAN
2) informix.t1: INDEX PATH //t1表上执行了索引扫描，但注意是子查询中的表
Filters: informix.t3.b3 = informix.t1.b1
(1) Index Name: informix. 102_7 //索引被上拉之后，执行了索引扫描，
Index Keys: a1 (Serial, fragments: ALL)
Lower Index Filter: informix.t1.a1 = informix.t3.a3
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t3
t2 t1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6 1 6 00:00.00 2
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 3 1 3 00:00.00 0
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 3 1 00:00.00 4
从查询执行计划可以看出，子查询已经被消除，变为了两表的嵌套循环连接，这说明ToprowDB对此类的子查询进行了优化。当我们再次感受到ToprowDB的子查询优化技术似乎不弱的时候，可以再看看PostgreSQL和MySQL的执行计划。
PostgreSQL的查询执行计划如下：
postgres=# explain SELECT * FROM t3 WHERE b3 IN (SELECT b1 FROM t1 WHERE a1=a3);
QUERY PLAN
----------------------------------------------------------------------------
Seq Scan on t3 (cost=0.00..8527.00 rows=1020 width=12)
Filter: (SubPlan 1)
SubPlan 1
-> Index Scan using t1_a1_key on t1 (cost=0.15..8.17 rows=1 width=4)
Index Cond: (a1 = t3.a3)
从查询执行计划可以看出，子查询没有被消除。
MySQL的查询执行计划如下：
mysql> explain SELECT * FROM t3 WHERE b3 IN (SELECT b1 FROM t1 WHERE a1=a3);
+----+-------------+-------+------+------+-------------+
| id | select_type | table | type | key | Extra |
+----+-------------+-------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | Using where |
| 1 | SIMPLE | t3 | ref | a3 | Using where |
+----+-------------+-------+------+------+-------------+
从查询执行计划可以看出，子查询已经被消除，变为了两表的嵌套循环连接，这说明MySQL对此类的子查询进行了优化。看到这里，我们可以感受到MySQL在子查询方面的强大了，是不是？
4 对比
我们把原始的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);
然后对比如下:
SQL语句
语句特点
ToprowDB
PostgreSQL
MySQL
S1
非相关子查询，子查询目标列为普通列
没有被优化
Semi Join优化
Join优化
S2
非相关子查询，子查询目标列为键
没有被优化
Semi Join优化
Join优化
S3
非相关子查询，子查询目标列为主键列
没有被优化
Semi Join优化
Join优化
S4
相关子查询，子查询带有WHERE条件
Join优化
没有被优化
Join优化
分析：
q 从整体上看，对于IN类型的子查询，MySQL的优化能力强，ToprowDB差，PostgreSQL接近MySQL
q PostgreSQL不支持相关子查询的优化，而MySQL和ToprowDB都支持
5 结尾
总结一下，从前面的例子可以看出，MySQL全面胜出，ToprowDB和PostgreSQL各有所长。
————————————————
版权声明：本文为CSDN博主「那海蓝蓝」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/fly2nn/article/details/61924787
相关文章