mysql subquery cache vs oracle subquery cache子查询缓存-凯发app官方网站

凯发app官方网站-凯发k8官网下载客户端中心 | | 凯发app官方网站-凯发k8官网下载客户端中心

dingjun123的blog

凯发app官方网站首页　| 　博文目录　| 　关于我

dingjun123

  • 博客访问： 1156265
  • 博文数量： 166
  • 博客积分： 0
  • 博客等级： 民兵
  • 技术积分： 3760
  • 用 户 组： 普通用户
  • 注册时间： 2011-03-30 13:00
个人简介

about me:oracle ace pro,optimistic,passionate and harmonious. focus on oracle,mysql and other database programming,peformance tuning,db design, j2ee,linux/aix,architecture tech,etc

文章分类

全部博文（166）

文章存档

2024年（21）

2023年（28）

2022年（43）

2020年（62）

2014年（3）

2013年（9）

我的朋友
最近访客
推荐博文
相关博文
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
mysql subquery cache vs oracle subquery cache子查询缓存_oracle部分_part3

分类： oracle

2024-05-23 16:29:04

接：mysql subquery cache vs oracle subquery cache子查询缓存_mysql部分_part2

下面研究下oracle里的subquery cache.

6.oracle具有全面的子查询cache优化功能


oracle同样的查询,不管是in还是exists,对于子查询不能unnest(这里使用no_unnest hints),都有subquery cache:


oracle里常见的unnest subquery的是标量子查询，update关联子查询等走filter操作的子查询，对应执行计划子查询部分
有系统绑定变量，比如：b1，有缓存优化，如果外层关联key基数小，则子查询执行次数少。

oracle建表语句:

点击(此处)折叠或打开

  1. create table subq_t1(a int,b int);
  2. insert into subq_t1(a,b)
  3. select 1,1
  4. from dual
  5. connect by level<=100000;

  7. insert into subq_t1(a,b)
  8. select 2,1level
  9. from dual
  10. connect by level<=100000;

  12. insert into subq_t1(a,b)
  13. select 3,1level
  14. from dual
  15. connect by level<=100000;


  18. create table subq_t2(a int,b int);
  19. insert into subq_t2(a,b)
  20. select level,level
  21. from dual
  22. connect by level<=100000;

  24. create index idx_subq_t2 on subq_t2(a);

  26. subq_t1表的a只有3个值：
  27. select count(*),count(distinct a) from subq_t1;

  29.   count(*) count(distincta)
  30. ---------- ----------------
  31.     300000 3

  33. subq_t2的a都是不重复的：
  34. select count(*),count(distinct a) from subq_t2;

  36.   count(*) count(distincta)
  37. ---------- ----------------
  38.     100000 100000

点击(此处)折叠或打开

  1. set serveroutput off
  2. set autotrace traceonly --不输出结果，太多
  3. select/*gather_plan_statistics*/ *
  4. from subq_t1 t1
  5. where t1.a in
  6. (select/*no_unnest*/ t2.a from subq_t2 t2);
  7. set autotrace off
  8. select sql_id,sql_text from v$sql where sql_text like '%gather_plan_statistics%subq_t2%';
  9. select * from table(dbms_xplan.display_cursor('0hrqfk46ss2ns',null,'allstats last'));

可以看到走filter，虽然subq_t1有30w行，但是子查询只执行3次（实际执行次数依赖subq_t1.a的相同值连续情况）

执行计划id=3对于谓词有系统绑定变量： 3 - access("t2"."a"=:b1)


点击(此处)折叠或打开

  1. plan hash value: 2995694926

  3. --------------------------------------------------------------------------------------------
  4. | id | operation | name | starts | e-rows | a-rows | a-time | buffers |
  5. --------------------------------------------------------------------------------------------
  6. | 0 | select statement | | 1 | | 300k|00:00:00.17 | 20535 |
  7. |* 1 | filter | | 1 | | 300k|00:00:00.17 | 20535 |
  8. | 2 | table access full| subq_t1 | 1 | 327k| 300k|00:00:00.11 | 20529 |
  9. |* 3 | index range scan | idx_subq_t2 | 3 | 1098 | 3 |00:00:00.01 | 6 |
  10. --------------------------------------------------------------------------------------------

  12. predicate information (identified by operation id):
  13. ---------------------------------------------------

  15.    1 - filter( is not null)
  16.    3 - access("t2"."a"=:b1)
改为exists和in一样，因为在oracle里in/exists基本都可以相互转换：  


点击(此处)折叠或打开

  1. set serveroutput off
  2. set autotrace traceonly --不输出结果，太多
  3. select/*gather_plan_statistics*/ *
  4. from subq_t1 t1
  5. where exists
  6. (select/*no_unnest*/ 1 from subq_t2 t2 where t1.a = t2.a);
  7. set autotrace off
  8. select sql_id,sql_text from v$sql where sql_text like '%gather_plan_statistics%subq_t2%';
  9. select * from table(dbms_xplan.display_cursor('f8uyx3hynta6x',null,'allstats last'));

  11. plan_table_output
  12. ----------------------------------------------------------------------------------------------------
  13. sql_id f8uyx3hynta6x, child number 0
  14. -------------------------------------
  15. select/*gather_plan_statistics*/ * from subq_t1 t1 where exists
  16. (select/*no_unnest*/ 1 from subq_t2 t2 where t1.a = t2.a)

  18. plan hash value: 2995694926

  20. --------------------------------------------------------------------------------------------
  21. | id | operation | name | starts | e-rows | a-rows | a-time | buffers |
  22. --------------------------------------------------------------------------------------------
  23. | 0 | select statement | | 1 | | 300k|00:00:00.16 | 20535 |
  24. |* 1 | filter | | 1 | | 300k|00:00:00.16 | 20535 |
  25. | 2 | table access full| subq_t1 | 1 | 327k| 300k|00:00:00.09 | 20529 |
  26. |* 3 | index range scan | idx_subq_t2 | 3 | 1098 | 3 |00:00:00.01 | 6 |
  27. --------------------------------------------------------------------------------------------

  29. predicate information (identified by operation id):
  30. ---------------------------------------------------

  32.    1 - filter( is not null)
  33.    3 - access("t2"."a"=:b1)
oracle标量子查询，也有subquery cache优化：


点击(此处)折叠或打开

  1. set serveroutput off
  2. set autotrace traceonly --不输出结果，太多
  3. select/*gather_plan_statistics*/ t1.a,(select t2.b from subq_t2 t2 where t1.a = t2.a) b
  4. from subq_t1 t1;
  5. set autotrace off
  6. select sql_id,sql_text from v$sql where sql_text like '%gather_plan_statistics%subq_t2%';
  7. select * from table(dbms_xplan.display_cursor('cwvtbwcaytnz5',null,'allstats last'));
可以看到id=1和id=2只执行3次：


点击(此处)折叠或打开

  1. plan hash value: 438047693

  3. -------------------------------------------------------------------------------------------------------------
  4. | id | operation | name | starts | e-rows | a-rows | a-time | buffers |
  5. -------------------------------------------------------------------------------------------------------------
  6. | 0 | select statement | | 1 | | 300k|00:00:00.09 | 20529 |
  7. | 1 | table access by index rowid batched| subq_t2 | 3 | 1098 | 3 |00:00:00.01 | 9 |
  8. |* 2 | index range scan | idx_subq_t2 | 3 | 439 | 3 |00:00:00.01 | 6 |
  9. | 3 | table access full | subq_t1 | 1 | 327k| 300k|00:00:00.09 | 20529 |
  10. -------------------------------------------------------------------------------------------------------------

  12. predicate information (identified by operation id):
  13. ---------------------------------------------------

  15.    2 - access("t2"."a"=:b1)
甚至在oracle里semi join/anti join走nested loops的（普通nested loops没有缓存优化），都有缓存优化，可以减少被驱动表回表次数，比如：

1.semi join走nested loops有缓存优化


点击(此处)折叠或打开

  1. set serveroutput off
  2. set autotrace traceonly --不输出结果，太多

  4. --去掉no_unnest
  5. select/*gather_plan_statistics leading(t1) use_nl(t2@subq) */ *
  6. from subq_t1 t1
  7. where t1.a in
  8. (select/*qb_name(subq)*/ t2.a from subq_t2 t2);

nested loops semi，子查询只执行3次：

点击(此处)折叠或打开

  1. set autotrace off
  2. select sql_id,sql_text from v$sql where sql_text like '%gather_plan_statistics%subq_t2%';
  3. select * from table(dbms_xplan.display_cursor('cacaags9gcy2a',null,'allstats last'));
  4. plan hash value: 3871264342

  6. --------------------------------------------------------------------------------------------
  7. | id | operation | name | starts | e-rows | a-rows | a-time | buffers |
  8. --------------------------------------------------------------------------------------------
  9. | 0 | select statement | | 1 | | 300k|00:00:00.18 | 20535 |
  10. | 1 | nested loops semi | | 1 | 327k| 300k|00:00:00.18 | 20535 |
  11. | 2 | table access full| subq_t1 | 1 | 327k| 300k|00:00:00.13 | 20529 |
  12. |* 3 | index range scan | idx_subq_t2 | 3 | 109k| 3 |00:00:00.01 | 6 |
  13. --------------------------------------------------------------------------------------------

  15. predicate information (identified by operation id):
  16. ---------------------------------------------------

  18.    3 - access("t1"."a"="t2"."a")

2.anti join同样有subquery cache


点击(此处)折叠或打开

  1. set serveroutput off
  2. set autotrace traceonly --不输出结果，太多

  4. --去掉no_unnest
  5. select/*gather_plan_statistics leading(t1) use_nl(t2@subq) */ *
  6. from subq_t1 t1
  7. where t1.a not in
  8. (select/*qb_name(subq)*/ t2.a from subq_t2 t2 where t2.a is not null) and t1.a is not null;

  10. set autotrace off
  11. select sql_id,sql_text from v$sql where sql_text like '%gather_plan_statistics%subq_t2%';
  12. select * from table(dbms_xplan.display_cursor('fs848pak6turs',null,'allstats last'));

因为有缓存，not in的执行效率非常高，没有找到数据：


点击(此处)折叠或打开

  1. plan hash value: 3776200174

  3. --------------------------------------------------------------------------------------------
  4. | id | operation | name | starts | e-rows | a-rows | a-time | buffers |
  5. --------------------------------------------------------------------------------------------
  6. | 0 | select statement | | 1 | | 0 |00:00:00.05 | 558 |
  7. | 1 | nested loops anti | | 1 | 327k| 0 |00:00:00.05 | 558 |
  8. |* 2 | table access full| subq_t1 | 1 | 327k| 300k|00:00:00.06 | 553 |
  9. |* 3 | index range scan | idx_subq_t2 | 3 | 1 | 3 |00:00:00.01 | 5 |
  10. --------------------------------------------------------------------------------------------

  12. predicate information (identified by operation id):
  13. ---------------------------------------------------

  15.    2 - filter("t1"."a" is not null)
  16.    3 - access("t1"."a"="t2"."a")
  17.        filter("t2"."a" is not null)

3.普通的nested loops join没有缓存优化,但是有批处理


点击(此处)折叠或打开

  1. set serveroutput off
  2. set autotrace traceonly --不输出结果，太多
  3. select/*gather_plan_statistics leading(t1) use_nl(t2)*/ *
  4. from subq_t1 t1,subq_t2 t2
  5. where t1.a = t2.a;
  6. set autotrace off
  7. select sql_id,sql_text from v$sql where sql_text like '%gather_plan_statistics%subq_t2%';
  8. select * from table(dbms_xplan.display_cursor('2qa1g6az4hmk8',null,'allstats last'));
两个nested loops就是bulk获取rowid的优化：

点击(此处)折叠或打开

  1. plan hash value: 2089944506

  3. ------------------------------------------------------------------------------------------------------
  4. | id | operation | name | starts | e-rows | a-rows | a-time | buffers |
  5. ------------------------------------------------------------------------------------------------------
  6. | 0 | select statement | | 1 | | 300k|00:00:00.70 | 80537 |
  7. | 1 | nested loops | | 1 | 327k| 300k|00:00:00.70 | 80537 |
  8. | 2 | nested loops | | 1 | 327k| 300k|00:00:00.47 | 60536 |
  9. | 3 | table access full | subq_t1 | 1 | 327k| 300k|00:00:00.06 | 20529 |
  10. |* 4 | index range scan | idx_subq_t2 | 300k| 1 | 300k|00:00:00.25 | 40007 |
  11. | 5 | table access by index rowid| subq_t2 | 300k| 1 | 300k|00:00:00.14 | 20001 |
  12. ------------------------------------------------------------------------------------------------------

  14. predicate information (identified by operation id):
  15. ---------------------------------------------------

  17.    4 - access("t1"."a"="t2"."a")

在oracle里，subquery cache优化比较全面，不能unnest走filter的有cache优化，可以减少执行次数，semi join/anti join同样有cache优化，普通nested loops join没有cache优化但是有批量获取rowid回表的优化。

总结：
1.mysql的子查询subquery cache,仅限于无法unnest subquery,走select_type:subquery,也就是物化方式，如果外部表与子查询关联的key重复值特别多，
则可以有效减少子查询执行次数，具体减少多少，要看这个key存储的连续程度。


至于dependent subquery是mysql子查询兜底招数，虽然改写和执行计划里对外部传递的列有,但是不能减少子查询执行次数，效果比较差。


另外mysql的标量子查询只能走dependent subquery，无subquery cache优化，效率不好。


mariadb是做了subquery cache优化的：见


mysql对于不能unnest的，如果外层表有过滤条件，如果不能走索引，则条件写在子查询前还是后对驱动子查询次数有影响，
能走索引先走索引过滤无影响。


不走索引的，写在前先过滤后然后驱动子查询，写在后则是先驱动子查询后过滤外层条件，可能执行次数会多很多。




2.oracle对无法unnest的子查询、标量子查询、update关联子查询是有缓存结果优化的，一般most缓存数目在1024个key左右，
如果outer table关联key的基数(distinct数目）很小，则可以大幅度减少子查询执行次数，具体也要看数据物理分布连续性是否强，
连续性强，减少的子查询次数越少，如果很离散存储，则子查询次数会增多。详细见：
filter operation中子查询的执行次数问题分析（http://blog.chinaunix.net/uid-7655508-id-5875810.html）。


另外oracle对能够unnest的semi/anti join,走nested loops的也有缓存优化，普通join走nested loops没有缓存优化，但是有批处理优化。


oracle这方面优化做的比较全面。


3.mysql子查询优化还是不够强大，所以在mysql里让子查询能够subquery unnest较好,这样可以转为join,semi join,anti join优化，避免unnest subquery只有2种方法，
完全依赖于外层结果驱动子查询，缓存优化也只有materialization才有，标量子查询在mysql里要慎用，和o不同的是，它没有缓存优化。
在mysql里经常会使用materialization来优化子查询，因为它会按照关联key剔除重复行并且创建索引，一定程度上能够弥补不能缓存的差距。

本系列结束.

阅读(66) | 评论(0) | 转发(0) |
0

上一篇：mysql subquery cache vs oracle subquery cache子查询缓存_mysql部分_part2

下一篇：没有了

给主人留下些什么吧！~~
| | | | |

凯发app官方网站 copyright 2001-2010 chinaunix.net all rights reserved 北京皓辰网域网络信息技术有限公司. 凯发k8官网下载客户端中心的版权所有

感谢所有关心和支持过chinaunix的朋友们
")); function link(t){ var href= $(t).attr('href'); href ="?url=" encodeuricomponent(location.href); $(t).attr('href',href); //setcookie("returnouturl", location.href, 60, "/"); }
网站地图