首页 / MYSQL / 子查询中的filter剖析

子查询中的filter剖析

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了子查询中的filter剖析，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7487字，纯文字阅读大概需要11分钟。

内容图文

在oracle 8i时候，子查询展开为表连接有很多的限制，所以经常我们会听见in和exist的写法会影响sql的性能，会看见很多文章提到一个论点是：in操作是优先对子查询做操作，然后驱动外部的查询，exists则是优先进行外部的查询，然后去驱动子查询，这个结论真的

在oracle 8i时候，子查询展开为表连接有很多的限制，所以经常我们会听见in和exist的写法会影响sql的性能，会看见很多文章提到一个论点是：in操作是优先对子查询做操作，然后驱动外部的查询，exists则是优先进行外部的查询，然后去驱动子查询，

这个结论真的正确吗，我们来看看如果我们关闭子查询展开为表连接的特性，再来看看是否真的如上述所述：
SQL> create table xiaoyu03 as select * from dba_objects;
SQL> create table xiaoyu04 as select * from dba_objects where rownum<10;
SQL> explain plan for select /*+optimizer_features_enable('8.1.7')*/* from xiaoyu04 a where a.object_id in (select /*+no_unnest*/object_id from xiaoyu03 b where b.object_type='TABLE');

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2861815236

---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 3312 | 1 |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL| XIAOYU04 | 16 | 3312 | 1 |
|* 3 | TABLE ACCESS FULL| XIAOYU03 | 1 | 11 | 24 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "XIAOYU03" "B"
WHERE "OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE'))
3 - filter("OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE')

Note
-----
- cpu costing is off (consider enabling it)

我们发现了虽然关闭了子查询为表连接，优化器也回到了8I的版本，这里只是表连接的方式改为了filter，然后依然是以外部的表作为驱动表，子查询的表作为被驱动表来做关联的，这个对于之前我们推断in一定先走子查询为驱动表是不合理的：
1 子查询展开为表连接下，此时是两个结果集的关联，优化器为选择少的结果集为驱动表，in的方式不能保证此时优化器一定先选择子查询为驱动表
2 子查询不展开表连接下，此时优化器会选择filter关联方式，优化器选择了主查询的表为驱动表，做filter关联子查询的结果集，in的方式依然不能保证此时优化器一定先走子表

可能有朋友对上面的论点2有异议：优化器这里做了一个查询转换，将in改写为了exists的方式，这个好像是为了满足优化器的驱动准则，就是用小的结果集去做驱动表，这里xiaoyu04比较少，所以这里选择用xiaoyu04去做驱动表是最佳的，而in的写法优化器得先走子表xiaoyu03，然后再走主表xiaoyu04，查询转换in为exists后，优化器可以先走xiaoyu04然后走xiaoyu03了，这个貌似看起来非常合理了，事实真是如下？

这里我们改变一下查询的关系，xiaoyu03是大表在主查询中，xiaoyu04是小表在子查询中，优化器回到11g的版本，而且此时我们还建立xiaoyu03的object_id的一个高效索引，再来看下no_unnest hint作用下的关联顺序：
SQL> create index ind_objid_xiaoyu03 on xiaoyu03(object_id);

Index created.
SQL> explain plan for select * from xiaoyu03 a where a.object_id in(select /*+no_unnest*/b.object_id from xiaoyu04 b where b.object_type='TABLE');

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2805971548

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 88 | 14764 (1)| 00:02:58 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| XIAOYU03 | 9811 | 843K| 43 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| XIAOYU04 | 1 | 10 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "XIAOYU04" "B"
WHERE "B"."OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE'))
3 - filter("B"."OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE')

17 rows selected.
还是以主查询的表为驱动表做filter关联。

同样我们再来看看exists是否也是如此，关闭子查询为表连接后，优化器回到11g的版本，exists的sql语句能否将小结果集的子查询作为驱动表，而将大结果集的外部表作为被驱动表了：
SQL> explain plan for select * from xiaoyu03 a where exists(select /*+no_unnest*/1 from xiaoyu04 b where b.object_type='TABLE' and a.object_id=b.object_id);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2805971548

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 88 | 14764 (1)| 00:02:58 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| XIAOYU03 | 9811 | 843K| 43 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| XIAOYU04 | 1 | 10 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "XIAOYU04" "B"
WHERE "B"."OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE'))
3 - filter("B"."OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE')

17 rows selected.

看来对于关闭子查询为表连接后，exists确实还是只能先扫描外部的表为驱动表，然后去驱动子查询的表。

这里再次要推翻我们之前的理解：就是优化器这里选择驱动表是考虑了选择小的驱动结果集为驱动表，会通过查询转换in和exists来满足小的结果集去做驱动表。

那么优化器为什么无论in还是exists的方式，在关闭子查询为表连接的特性后，都是选择的外部的表作为驱动表了，其实解释这个现象的根本原因是关联方式filter。

filter究竟如何工作：
表关联方式filter跟nested loop是相同的，只是filter维护了一个hash table用于记录了满足主表和子表关联的关联列和这个关联列相对应的这行数据的部分列。

确实很不好解释这个filter，我们举个简单的例子吧：比如查询是select * from xiaoyu03 a where exists(select /*+no_unnest*/1 from xiaoyu04 b where b.object_type='TABLE' and a.object_id=b.object_id);

oracle这里如果走fiter的关联方式，先从xiaoyu03表取出一条数据，然后a.object_id=b.object_id和b.object_type='TABLE'关联，如果满足，则记录a.object_id的值到hash table，返回满足这个关联条件的a.object_id对应的数据列；然后重复的取出所有xiaoyu03表的数据，如果发现下一个a.object_id已经记录到了hash table中则优化器不再需要去和xiaoyu04表做关联，而是直接再次返回a.object_id对应的这行数据列，这种filter关联方式只能选择主表做驱动表，所以我们看见了在关闭子查询转换为表连接后in和exists对应的filter执行计划都是以外部的表去做驱动表。

所以一般而言filter相比nested loop高效一点，对于重复的满足的关联列节省被驱动表的IO消耗。

本文出自：http://www.dbaxiaoyu.com, 原文地址：http://www.dbaxiaoyu.com/archives/2360, 感谢原作者分享。

内容总结

以上是互联网集市为您收集整理的子查询中的filter剖析全部内容，希望文章能够帮你解决子查询中的filter剖析所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/555305.html

来源：【匿名】

【上一篇】关于谓词条件有倾斜性的全表扫描countstopkey的成本估算影响【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【子查询中的filter剖析】教程文章相关的互联网学习教程文章

Django filter中用contains 在mysql中的问题【代码】

用PYTHON ,DJANGO 做站，在通常的情况下，需要用到 orM 的查询方法，比如object.filter(tag__contains=‘keywords‘)....在这种情况下，如果你跟踪 sql 语句，你会发现，SQL 语句会生成 select .... like bianry ‘%keywords%‘, 如果是这样的语句，在某些情况是下是会出问题的，也就是说查询出来的数据可能会比你预计的少。如果你用 raw sql 查总数 select count(*) from table where like ‘%keywords%‘ 得到数量可能比你用ORM ...

AddingFilterinHadoopMapperClass

There is my solutions to tackle the disk spaces shortage problem I described in the previous post. The core principle of the solution is to reduce the number of output records at Mapper stage; the method I used is Filter , adding a filter,There is my solutions to tackle the disk spaces shortage problem I described in the previous post. The core principle of the solution is to reduce the number of ...

为什么Mysqlexplainextended中的filtered列值总是100%_PHP教程

为什么Mysql explain extended中的filtered列值总是100%1. 问题执行Mysql的explain extended的输出会比单纯的explain多一列filtered(MySQL5.7缺省就会输出filtered)，它指返回结果的行占需要读到的行(rows列的值)的百分比。按说filtered是个非常有用的值，因为对于join操作，前一个表的结果集大小直接影响了循环的次数。但是我的环境下测试的结果却是，filtered的值一直是100%，也就是说失去了意义。参考下面mysql 5.6的代码，filt...

filterindexs

筛选索引--filter indexs 筛选索引是一种经过优化的非聚集索引，尤其适用于涵盖从定义完善的数据子集中选择数据的查询。筛选索引使用筛选谓词对表中的部分行进行索引。一：筛选索引与全表索引相比具有以下优点： 1.提高了查询性能和计划质量设计良好的筛筛选索引--filter indexs 筛选索引是一种经过优化的非聚集索引，尤其适用于涵盖从定义完善的数据子集中选择数据的查询。筛选索引使用筛选谓词对表中的部分行进行索引。一：筛...

[Leveldb]源码分析之三BloomFilter模块的实现

应用场景：由于Leveldb采用的是分层的存储结构，那么当Get一个key的时候最坏情况就是在所有的层级上都查询一遍这个key，这个开销是非常大的，引入BloomFilter之后，利用BloomFilter能够快速判断是否存在的特点可以很快速的知道需不需要在这个Level中进行查应用场景：由于Leveldb采用的是分层的存储结构，那么当Get一个key的时候最坏情况就是在所有的层级上都查询一遍这个key，这个开销是非常大的，引入BloomFilter之后，利用Bloo...

filter造成的性能问题【图】

filter这个词总让人很费解，它下一级可以挂一个子节点，二个子节点，三个子节点...。挂一个子节点意思过滤，如对全表进行扫描后挂一个子节点意思过滤，如对全表进行扫描后，，按照条件过滤，丢弃不满足条件的数据。挂二个子节点类似是nest loop。挂三个子节点类似1和2做nest loop，结果集再与3做nest loop（这个是我推测的）。 SQL> set linesize 300SQL> set timing onSQL> set autotrace traceonlySQL> SELECT count(DISTIN...

探索执行计划中filter的原理【图】

filter下挂一个节点：挂一个节点时，下面的例子可以看到，对表扫描一次，就是对返回的结果集进行过滤，不过有趣的是当条件为假时 filter这个词总让人很费解，它下一级可以挂一个子节点，二个子节点，三个子节点...。挂一个子节点意思过滤，，如对全表进行扫描后，按照条件过滤，丢弃不满足条件的数据。挂二个子节点类似是nest loop。挂三个子节点类似1和2做nest loop，结果集再与3做nest loop（这个是我推测的）。为了证明当...

SQL调优之关注filter信息【图】

SQL调优之关注filter信息,开发人员发来邮件，叫我优化下面SQL Hi Robinson, Kindly need your help to check the below SQL for SQL调优之关注filter信息,开发人员发来邮件，叫我优化下面SQL Hi Robinson, Kindly need your help to check the below SQL for performance issue with Hyper plan detail report. Thanks in advance. Elapsed: 00:00:24.50 -------- SQL能在24秒跑完。看到这里明白怎么做SQL优化了没？我只要是看见一...

Oracle使用WITHAS和HINTMATERIALIZE优化SQL解决FILTER效率低下【图】

在做项目的过程中，一个页面使用类似如下的SQL查询数据，为了保密和使用方便，我把项目中有关的表名和字段替换使用ORACLE数据库中在做项目的过程中，一个页面使用类似如下的SQL查询数据，为了保密和使用方便，我把项目中有关的表名和字段替换使用Oracle数据库中的系统表和字段。在我所做的项目中，类似ALL_TABLES的表中大概有8W多条数据，下面这个查询SQL很慢。 WITH PARAMS AS (SELECT USER_ID, SDATE, % || || % SNAME FROM...

OracleASMFilterDriver(ASMFD)–NewFeaturesfo

什么是Oracle ASM Filter Driver（ASMFD）？简单地说，这是一个可以取代ASMLIB和udev设置的新功能，并且还增加了I/O Filter功能，这也体现在该功能的命名中。ASMFD目前只在Linux操作系统中有效，并且必须要使用最新版的Oracle ASM 12.1.0.2。在之前，由于Li什么是Oracle ASM Filter Driver（ASMFD）？简单地说，这是一个可以取代ASMLIB和udev设置的新功能，并且还增加了I/O Filter功能，这也体现在该功能的命名中。ASMFD目前只在...