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

在mysql里执行计划对子查询no unnest subquery的有：subquery、dependent subquery、uncacheable subquery。
文档上说dependent subquery和uncacheable subquery不同，dependent subquery对于外部的每个不同的关联key执行一次子查询，uncacheable subquery意思是没有缓存，也就是外部的每行执行一次子查询。

文档10.8.2 explain output format：
dependent subquery evaluation differs from uncacheable subquery evaluation. 
for dependent subquery, the subquery is re-evaluated only once for each set of different values of the variables from its outer context. 
for uncacheable subquery, the subquery is re-evaluated for each row of the outer context.


通过测试，发现和文档有点出入，mysql貌似对subquery有缓存优化，dependent subquery实际没有看到缓存优化（有优化，但是效果不明显）。


subquery和dependent subquery实际上是没有unnest的子查询的算法，子查询没有unnest,必须是外层结果来驱动子查询，所以，外层结果的行数直接影响子查询执行次数,而子查询执行次数，直接影响整个没有unnest的子查询效率。


一般数据库subquery cache做法（以oracle为例）：
一般情况下，大多数据库对未unnest的子查询做了缓存优化，这种缓存优化怎么做呢？因为外层与子查询是通过关联key比较的，那么做cache,也就是需要一块内存区域保存外层的key与子查询比较的结果（key-value结构），一般用hash table保存，每一个key保存一行结果即可，像oracle
可以保存1024行左右。减少的子查询次数，和外层表的key存储顺序有关，相同值存储越连续，减少的子查询执行次数越多，反而按key存储越离散，则子查询执行次数越多。


在oracle里外层驱动子查询不能unnest的有一般子查询（比如子查询有or）、update关联子查询、标量子查询等，特点是执行计划被驱动部分谓词有系统绑定变量，比如access("c"=:b1)，这个:b1是oracle cbo自己定义的。


这样subquery cache优化,对于外部行关联值基数（distinct值）特别小的，可以极大减少子查询执行次数，从而提高执行效率，这就是为什么有时候在oracle里标量子查询比join效率更高的原因。


在mysql里执行计划有subquery和oracle里的unnest子查询类似，有缓存，dependent subquery貌似没有看到缓存(但是看到改写有cache).
mariadb有subquery cache:




下面分别研究下mysql里的subquery cache以及和oracle的对比。


1）mysql 执行计划select_type:subquery,也即不能unnest的子查询materilization有subquery cache


select_type:subquery对应的子查询使用materialization物化，物化过程只执行一次。
我们希望的是真正需要执行子查询才真正做子查询物化，比如外部有条件
返回0行，则子查询不需要做物化（这个后面会说，过滤条件写的位置在子查询前还是后有影响），需要子查询结果时做materilization,整个过程只做一次，
在内存里创建临时表并按照关联key剔重，并创建索引，然后外部表的行按照子查询关联条件驱动materialize表多次，

这种子查询是有subquery cache优化的，具体子查询被驱动多少次，要看外部表传入的关联key相同值是否连续（这个和oracle的filter子查询类似）。


materialization只执行一次，会按照关联key剔重然后建立索引，对于没有索引的，剔重后结果集少的，很适用，临时表创建好后，外部结果驱动临时表多次。
按照subquery cache重复的可能减少子查询执行（要看外部行key存储连续程度）。


2）dependent subquery就是普通的in to exists方式，外部表每一行会驱动子查询一次，文档上说有缓存，测试没有看到（但是看到改写有cache),实际
树形计划显示不能减少loops次数，效果不是很明显。


3）对于标量子查询，mysql因为不能走select_type:subquery,只能走dependent subquery，没有cache优化，所以要慎用

另外还有个注意点：
mysql里subquery unnest走subquery、dependent subquery、uncacheable subquery的，如果主表还有过滤条件，
过滤条件写在子查询前还是后，子查询驱动顺序不一样：


1）如果条件不能走索引：这个是规则式的，在oracle基于cbo的优化器下没有这种区别
from a where a.cond and subquery 是先执行a.cond过滤，然后每一行驱动执行subquery,较好。
from a where subquery and a.cond 这个是先a的每行驱动执行subquery,如果子查询返回true,则判断a.cond,false则不执行a.cond,一般效率差。


2）如果条件能走索引：mysql不管condition位置，先执行condition过滤，然后执行子查询，也就是上面2个写法没有区别。


3) 使用subquery hints的，in/exists不能互相转换为相同执行计划，subquery hints.
    对应的exists就是intoexists策略，是dependent subquery
   in的可以指定intoexists或materialization策略。
   如果不用hints,mysql 8对于in和exists可以等价转换。


一般主流数据库都会做subquery cache优化，比如oracle，这种优化有时候还是很有用的，比如外部表驱动子查询的关联key基数很小，这时候实际执行效率也是很高的，如果没有subquery cache优化，外部每一行驱动一次子查询，必然效率低很多。
mysql建表语句：

subq_t1表的a只有3个值：

subq_t2的a都是不重复的：

###标量子查询貌似只能走dependent subquery,没有cache


本文对于mysql子查询研究的都是不能unnest的，具体通过hints控制，也就是select_type:subquery、dependent subquery、uncacheable subquery.


能够unnest subquery的，可以走pullout,firstmatch,loosescan,materialization,dupsweedout方式，这个实际是转为join方式，按照join方式走，mysql对于join走
nested loops的没有cache优化。

1.mysql子查询计划是subquery （materialization)的，有subquery cache

exists subquery(@subq materialization)也走不了materialization，还是和intoexists一样，走dependent subquery，只不过改写没有cache.
当然，如果exists不写hints,可能转为in materialization,也可以有缓存。

sql如下：


执行计划如下

和预期的走subquery不一样，走的是dependent subquery。


查看改写的，虽然是intoexists,但是改写没有,用subquery(@subq intoexists) in子查询方式虽然执行计划显示一样，但是改写有


从执行计划看，dependent subquery 走intoexists策略，外部表subq_t1有30w行，但是a字段只有3个不同值，子查询被驱动30w次,没有减少子查询执行次数。


改为in materialization：


现在执行计划符合预期，走subquery，也就是materialization，可以通过树形计划看到：


可以看扩展信息，子查询使用materialization。
检验子查询执行次数：可以看到主表subq_t1返回30w行，如果没有缓存，物化表应该被驱动30w次，但是现在是loops=4，说明有缓存,subq_t1的a字段只有3个不同值，现在物化表被驱动4次，
比预想的多1次，这还是在subq_t1的a字段是按顺序存储的情况下，如果离散存储，则子查询驱动次数更多。

materialization有subquery cache,子查询执行次数少，外部行30w,子查询物化表只执行4次查询，
执行时间从intoexists的1248ms到335ms，效率提升明显。


总结：mysql对不能unnest的子查询，只有subquery方式（走materilization)才有subquery cache优化，exists写hints不能转为subquery方式，不写可能能转。

2.mysql的dependent subquery，如果是intoexists对外部表字段cache,但是不能减少子查询执行次数，效果不是太明显

改为in intoexists看看：

看起来in intoexists也做了优化，改写里有((`employees`.`t1`.`a`) ，实际上有多大效果，看explain analyze:
查看树形计划：走索引有a=(t1.a)，但是索引还是loops=300000,没有减少执行次数：

对于dependent subquery方式，树形计划里显示a=(t1.a)，也就是在索引查找时做了cache优化，而不是减少索引扫描的次数。
索引查询平均每次0.002ms.原来exists方式没有,in intoexists的字段cache,优化效果不是很明显，只在外部表subq_t1的全表扫描上降了30%时间，
而不是子查询表的执行次数减少。



总结:
dependent subquery实际上是不能unnest subquery的保底执行方法，经常效率比较烂，子查询能够unnest较好,有5种策略可以使用，能充分使用join/semi join/anti join算法提高效率。

不能unnest的只有materialization和intoexists两种策略,完全是外表驱动子查询走filter，只有materialization才有缓存减少子查询执行次数的优化。

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