在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