ToprowDB Dynamic Server 查询优化技术---子查询优化--01-2

3.2 深入分析—相关子查询

细心的读者可以发现，上一节中，我们选取的例子，都是非相关子查询，对于相关的子查询，ToprowDB和MySQL优化器的优化能力又是怎么样的呢？

在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各有所长。

 

其实，仅仅有上面的比较，还不足以说明问题，子查询优化技术，不仅仅是上面这些内容，所以我们还不能以偏概全，得出MySQL在子查询方面的优化能力强大的结论。

期待下一篇，我们继续深入探讨子查询优化技术。更多更精彩的内容，值得在“Informix中国”期待……