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

最新推荐文章于 2024-03-20 06:00:00 发布
最新推荐文章于 2024-03-20 06:00:00 发布
阅读量393 收藏
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/fly2nn/article/details/61924786
版权

ToprowDB Dynamic Server 查询优化技术


（三）ToprowDB Dynamic Server 查询优化技术之子查询

本篇是《ToprowDB Dynamic Server 查询优化技术》系列的第三篇，探讨ToprowDB逻辑查询优化技术中的子查询优化技术。

在第二篇里，探讨了视图优化，这一次探讨子查询优化，我们也许会有一些 “故地重游似曾相识”的感觉，感觉子查询和视图优化怎么那么相似？其实，这种感觉，是一种正确的良好的感觉，或者叫做直觉吧。视图和子查询确实相似，从长像看，放在SQL语句中，两者都导致了query产生了嵌套；从优化技术看，都是要消除嵌套、把子部分去除合并到父亲节点；所以，本质上，视图和子查询的优化方式是一样的，称之为“上拉/扁平化”优化。（提高效率，扁平化管理，一些歪想，神思千里…….托着下巴，想想扁平化后的好处……）

1 什么样的子查询，可以被优化？

    什么样的子查询，可以被优化？

    标题有点大，不过不是标题党，前面我们说视图和子查询的优化相似，那么视图如果可以被优化，似乎子查询的优化就是有路可寻的，或者聪明的你一定会想到，SPJ类型的子查询，是可以被优化的，非SPJ类型的子查询，是不可以被优化的，是吧？

1.1 SPJ类型子查询

沿用上一篇视图优化中提供的示例，我们进行如下的子查询：

SELECT * FROM (SELECT * FROM t1,t2) AS v_SPJ, t3;

注意视图优化篇中提及的是：SELECT * FROM v_SPJ, t3;

 

然后在ToprowDB中得到的查询执行计划可能如下（取决于统计数据导致的代价估算模型的计算值）：-

ToprowDB Dynamic Server 查询优化技术---子查询优化--01-1 - 那海蓝蓝 - 那海蓝蓝的博客
 


    这里的子查询，是SPJ类型的子查询，与SPJ类型的视图相似，被优化，即子查询被消除。

1.2 非SPJ类型的子查询

    非SPJ子查询，类似非SPJ类型的视图，如果子查询的语句中包含有如下子句，这样的子查询，称为非SPJ类型的子查询：

1 GROUP BY子句

2 DISTNICT子句

3 聚集函数

4 带有集合操作: UNION / UNION DISTINCT / UNION ALL

5 带有分析函数、connect by、OUTER JOIN、PL/SQL function等

 

同样用一个例子来说明：

执行：SELECT * FROM t3, (SELECT DISTINCT t1.b1, t2.b2 FROM t1, t2 GROUP BY t1.b1, t2.b2) AS v_NON_SPJ;

ToprowDB Dynamic Server 查询优化技术---子查询优化--01-1 - 那海蓝蓝 - 那海蓝蓝的博客
 


左侧执行计划中的t1和t2，是原始SQL中的t2和t1，这2个表作了连接操作，得到一个临时的结果集（(Temp Table For View)），这说明子查询v_NON_SPJ没有解体，即子查询v_NON_SPJ的query被单独执行了。换句话说，非SPJ类型的子查询不能被优化。

 

前面2个例子，说明了子查询的优化，和视图优化，确实是相似的，遵循着一样的规律，都是要区分SPJ类型还是非SPJ类型，前者可以被优化而后者不能被优化。

2 所有的SPJ类型子查询/视图都可以被优化吗？

在第一节，我们提到SPJ类型的子查询/视图是可以被优化的，那么，一个问题是：所有的SPJ类型的子查询/视图都可以被优化吗？

如下将执行的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);

 

2.1 S1语句

SELECT * FROM t3 WHERE b3 IN (SELECT a1 FROM t1)  //非相关的子查询

可以得到如下的执行计划：

Estimated Cost: 4

Estimated # of Rows Returned: 1

 

  1) informix.t3: SEQUENTIAL SCAN

 

        Filters: informix.t3.b3 = ANY <subquery>  //此处表明IN子查询被转换为ANY，但依旧是子查询

 

    Subquery:

    ---------

    Estimated Cost: 2

    Estimated # of Rows Returned: 1

 

      1) informix.t1: SEQUENTIAL SCAN

 

Query statistics:

-----------------

 

  Table map :

  ----------------------------

  Internal name     Table name

  ----------------------------

  t1                t3

 

  type     table  rows_prod  est_rows  rows_scan  time       est_cost

  -------------------------------------------------------------------

  scan     t1     6          1         6          00:00.13   4

 

    从上面的执行计划可以看出，非相关子查询没有被优化，嵌套层次依然存在。

 

2.2 S2语句

SELECT * FROM t3 WHERE b3 IN (SELECT id1 FROM t1)  //非相关的子查询，子查询目标列为索引列id1

执行计划如下：

 

Estimated Cost: 4

Estimated # of Rows Returned: 1

 

  1) informix.t3: SEQUENTIAL SCAN

 

        Filters: informix.t3.b3 = ANY <subquery>  //此处表明IN子查询被转换为ANY，但依旧是子查询

 

    Subquery:

    ---------

    Estimated Cost: 2

    Estimated # of Rows Returned: 1

 

      1) informix.t1: SEQUENTIAL SCAN

 

Query statistics:

-----------------

 

  Table map :

  ----------------------------

  Internal name     Table name

  ----------------------------

  t1                t3

 

  type     table  rows_prod  est_rows  rows_scan  time       est_cost

  -------------------------------------------------------------------

  scan     t1     6          1         6          00:00.06   4

 

 

Subquery statistics:  //存在子查询

--------------------

 

  Table map :

  ----------------------------

  Internal name     Table name

  ----------------------------

  t1                t1

 

  type     table  rows_prod  est_rows  rows_scan  time       est_cost

  -------------------------------------------------------------------

  scan     t1     3          1         3          00:00.00   2

 

    从上面的执行计划可以看出，ToprowDB对于非相关子查询不支持优化，嵌套层次依然存在。

 

 

2.3 S3语句

SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1)  //非相关的子查询，子查询左操作符为索引列id1

执行计划如下：

 

Estimated Cost: 3

Estimated # of Rows Returned: 1

 

  1) informix.t3: INDEX PATH  //此处变为索引扫描

 

    (1) Index Name: informix. 104_12

        Index Keys: id3   (Serial, fragments: ALL)  //id3上存在索引，而子查询值固定，所以可以对t3表执行索引扫描

        Lower Index Filter: informix.t3.id3 = ANY <subquery>  //此处表明IN子查询被转换为ANY，但依旧是子查询

 

    Subquery:

    ---------

    Estimated Cost: 2

    Estimated # of Rows Returned: 1

 

Query statistics:

-----------------

 

  Table map :

  ----------------------------

  Internal name     Table name

  ----------------------------

  t1                t3

 

  type     table  rows_prod  est_rows  rows_scan  time       est_cost

  -------------------------------------------------------------------

  scan     t1     3          1         3          00:00.10   4

 

 

Subquery statistics:

--------------------

 

  Table map :

  ----------------------------

  Internal name     Table name

  ----------------------------

  t1                t1

 

  type     table  rows_prod  est_rows  rows_scan  time       est_cost

  -------------------------------------------------------------------

  scan     t1     3          1         3          00:00.00   2

 

  type     rows_sort  est_rows  rows_cons  time

  -------------------------------------------------

  sort     3          0         3          00:00.02 //在子查询上执行了一个排序操作

   

    从上面的执行计划可以看出，非相关子查询左操作符带有主键索引列没有被优化，嵌套层次依然存在。但是， ToprowDB在子查询上执行了一个排序操作。

 

从前面的3个例子，我们可以看出，ToprowDB不支持对部分SPJ类型的子查询进行优化。

3 深入探讨

3.1 主流数据库对于例1至例3的优化情况

我们以MySQL 5.6.27和PostgreSQL 9.5为例，看看第二节所述的子查询的在MySQL和PostgreSQL的支持情况。

 

3.1.1 MySQL

3.1.1.1 S1语句

SELECT * FROM t3 WHERE b3 IN (SELECT a1 FROM t1)  //非相关的子查询

可以得到如下的执行计划：

mysql> explain SELECT * FROM t3 WHERE b3 IN (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)

    从查询执行计划看，嵌套不存在（id列的值相同表明不存在嵌套层次），子查询被消除，即被优化。

 

3.1.1.2 S2语句

SELECT * FROM t3 WHERE b3 IN (SELECT id1 FROM t1)  //非相关的子查询，子查询目标列为索引列id1

执行计划如下：

mysql> EXPLAIN SELECT * FROM t3 WHERE b3 IN (SELECT id1 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) |

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

    从查询执行计划看，嵌套不存在，子查询被消除，即被优化。

 

 

3.1.1.3 S3语句

SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1)  //非相关的子查询，子查询左操作符为索引列id1

执行计划如下：

mysql> explain SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1);

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

| id | select_type | table | type   | key     | Extra                    |

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

|  1 | SIMPLE      | t1    | index  | a1      | Using where; Using index |

|  1 | SIMPLE      | t3    | eq_ref | PRIMARY | NULL                     |

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

    从查询执行计划看，嵌套不存在，子查询被消除，即被优化。

 

    从上面的执行计划可以看出，MySQL对于ToprowDB不支持的三种子查询，都进行了优化。

3.1.2 PostgreSQL

3.1.2.1 S1语句

SELECT * FROM t3 WHERE b3 IN (SELECT a1 FROM t1)  //非相关的子查询

可以得到如下的执行计划：

postgres=# explain SELECT * FROM t3 WHERE b3 IN (SELECT a1 FROM t1);

                            QUERY PLAN

------------------------------------------------------------------

 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)

    从查询执行计划看，嵌套不存在，子查询被消除变为“Semi Join”，即被优化。

 

3.1.2.2 S2语句

SELECT * FROM t3 WHERE b3 IN (SELECT id1 FROM t1)  //非相关的子查询，子查询目标列为索引列id1

执行计划如下：

postgres=# EXPLAIN SELECT * FROM t3 WHERE b3 IN (SELECT id1 FROM t1);

                            QUERY PLAN

------------------------------------------------------------------

 Hash Semi Join  (cost=55.90..103.00 rows=1020 width=12)

   Hash Cond: (t3.b3 = t1.id1)

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

    从查询执行计划看，嵌套不存在，子查询被消除变为“Semi Join”，即被优化。

 

 

3.1.2.3 S3语句

SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1)  //非相关的子查询，子查询左操作符为索引列id1

执行计划如下：

postgres=# explain SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1);

                            QUERY PLAN

------------------------------------------------------------------

 Hash Semi Join  (cost=55.90..114.35 rows=2040 width=12)

   Hash Cond: (t3.id3 = 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)

    从查询执行计划看，嵌套不存在，子查询被消除变为“Semi Join”，即被优化。

 

    从上面的执行计划可以看出，MySQL和PostgreSQL对于ToprowDB不支持的三种子查询，都进行了优化。

那海蓝蓝
关注
专栏目录
Seata 整合多数据源dynamic-datasource-spring-boot-starter | Spring Cloud 60
gmHappy
06-30 1万+
在这篇文章中，我们使用Seata整合一下多数据源的场景。多数据源切换的功能我们使用`dynamic-datasource-spring-boot-starter`来完成，并且这个组件还可以和`Seata`进行整合，实现数据源的代理。
多一层查询带来的性能极度下降
Uncle.Cui的技术博客
10-15 312
QUERY: (OPTIMIZATION TIMESTAMP: 10-15-2020 19:16:20) ------ select first 10 * from ( select b.ajbp1400 as leftOverChildId, b1.ajep0100 as id, b1.ajep0101 as childNumber, b1.ajep0103 as name, b1.ajep0104 as idCard, ...
MySQL 执行计划里的rows
weixin_30293079的博客
06-05 596
<pre name="code" class="html">SQL> alter session set statistics_level=all; Session altered. SQL> select e.ename,e.job,d.dname from emp e,dept d where e.deptno=d.deptno and e.sal<...
gbase8t和oracle12c,GBase 8t数据库SQL优化一例
weixin_33391709的博客
04-09 364
客户的数据库从Oracle迁移到GBase 8t，反应有个SQL语句变得很慢很慢。而在原Oracle环境下，执行只需要10秒钟。SQL语句如下：select count(*)from TA_DOC doc left join TA_FORM_DATA ton t.docid=doc.idand t.elementname = '纸质文件印数'where isprint!=1and doc.stat...
GBase 8s 数据库中count(字段) ，count(主键) ，count(1)和count(*)的区别
liaosnet的专栏
01-12 1001
首先要弄清楚 count() 的语义。count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。 所以，count(*)、count(1)和count(主键) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数 “字段” 不为 NULL 的总个数。 以测试表为例，表内加载了10万行记录，其中允许null值的字段name存在3个null值； create table tab
SQL语句优化之二
Uncle.Cui的技术博客
09-09 445
如下SQL语句执行大约需要10分钟： update leapusertable set hasposition =1 where id in (select userid from leappositionusermap) 改写SQL语句的写法，40秒： update leapusertable a set a.hasposition =1 where exists (select b.userid from leappositionusermap b where b.userid=a.id)..
ToprowDB Dynamic Server 查询优化技术---子查询优化--03-2
那海蓝蓝的博客
03-13 405
3 深入探讨，一类很少被人关注的子查询 3.1 派生表的优化 在子查询中，有一类常见的子查询形式，是子查询出现在FROM子句中，这样的子查询，有个学名，叫做“派生表”。这样的且符合SPJ格式要求派生表，可以被优化。 这一节，我们来对比四种数据库，看看各个数据库对于派生表的支持情况。大家可以看到，我们将加入Oracle的对比。 3.1.1 Q1 select * from (select
ToprowDB Dynamic Server 查询优化技术---子查询优化--03-1
那海蓝蓝的博客
03-13 540
ToprowDB Dynamic Server 查询优化技术 华胜信泰架构师  李海翔 （五）ToprowDB Dynamic Server 查询优化技术之子查询 本篇是《ToprowDB Dynamic Server 查询优化技术》系列的第五篇，继续 探讨ToprowDB逻辑查询优化技术中的子查询优化技术。 1 还有什么样的子查询，可以被优化？ 参见上篇《ToprowDB Dyna
SpringBoot 多数据源 - dynamic-datasource | 快速入门
甘蓝的专栏
03-20 1966
基于 SpringBoot 的多数据源 - dynamic-datasource 快速入门。
SpringBoot使用dynamic-datasource-spring-boot-starter搭建多数据源
shz_123的博客
04-11 6400
SpringBoot使用dynamic-datasource-spring-boot-starter搭建多数据源
Spring Boot入门(20)：使用Mybatis-plus实现Spring Boot多数据源配置，轻松实现数据库读写分离！
热门推荐
**My Coding Family**
08-22 1万+
如何实现多数据源的配置及项目使用，此文教会你，超级简单，开箱即用。
dynamic-datasource-spring-boot-starter 动态加载多数据源问题处理
bayoddnnl的博客
04-21 1万+
本文主要阐述用dynamic-datasource-spring-boot-starter配置多数据源时，按需动态初始化数据库or按需加载数据源的问题处理。 背景：有个做数据处理的组件，数据来源是某几个数据库，这时候可用dynamic-datasource-spring-boot-starter做多数据源配置（具体配置不细述，可见官方文档：https://github.com/baomidou/dynamic-datasource-spring-boot-starter）使...
informix 分页
yutonging2016的博客
04-25 1852
/***********************************************************************/informix 分页处理/***********************************************************************/一 设置 执行时间显示 export DBACCESS_SHOW_TIME=1二
Informix Dynamic Server 11.5 SQL 语句性能调优最佳实践
09-07 409
袁红涛, 软件工程师, IBM 简介：SQL 语言是关系型数据库与应用交互的重要途径，书写高效率的 SQL 是每一个 DBA 和开发人员必备的技能。本文以调整 SQL 执行效率为最终目标，给大家介绍如何查看 Inf...
JSP学生学籍管理系统设计与实现(源代码+论文+开题报告+外文翻译+答辩PPT).zip
09-21
1、资源项目源码均已通过严格测试验证，保证能够正常运行； 2、项目问题、技术讨论，可以给博主私信或留言，博主看到后会第一时间与您进行沟通； 3、本项目比较适合计算机领域相关的毕业设计课题、课程作业等使用，尤其对于人工智能、计算机科学与技术等相关专业，更为适合； 4、下载使用后，可先查看README.md或论文文件（如有），本项目仅用作交流学习参考，请切勿用于商业用途。 5、资源来自互联网采集，如有侵权，私聊博主删除。 6、可私信博主看论文后选择购买源代码。 1、资源项目源码均已通过严格测试验证，保证能够正常运行； 2、项目问题、技术讨论，可以给博主私信或留言，博主看到后会第一时间与您进行沟通； 3、本项目比较适合计算机领域相关的毕业设计课题、课程作业等使用，尤其对于人工智能、计算机科学与技术等相关专业，更为适合； 4、下载使用后，可先查看README.md或论文文件（如有），本项目仅用作交流学习参考，请切勿用于商业用途。 5、资源来自互联网采集，如有侵权，私聊博主删除。 6、可私信博主看论文后选择购买源代码。 1、资源项目源码均已通过严格测试验证，保证能够正常运行； 2、项目问题、技术讨论，可以给博主私信或留言，博主看到后会第一时间与您进行沟通； 3、本项目比较适合计算机领域相关的毕业设计课题、课程作业等使用，尤其对于人工智能、计算机科学与技术等相关专业，更为适合； 4、下载使用后，可先查看README.md或论文文件（如有），本项目仅用作交流学习参考，请切勿用于商业用途。 5、资源来自互联网采集，如有侵权，私聊博主删除。 6、可私信博主看论文后选择购买源代码。
省市区数据，完成三级联动，选择地区
09-21
省市区数据，完成三级联动，选择地区
机械原理课程设计网球自动捡球机.doc
最新发布
09-21
机械原理课程设计网球自动捡球机.doc
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值