oracle vs mysql 对组合索引包含in list执行计划研究(mysql部分)-凯发app官方网站

建表语句如下：

t1,t2表结构：

创建索引，将选择性好的放前面：
create index idx1_t1 on t1(first_name,status);



###对于mysql，看组合索引用了几个列参与索引access,可以通过索引列key_len查看，
也可以通过树形执行计划sing idx1_t1 over 有几个列组合查看。
或者可以查看status变量handler确定索引扫描区间数：
handler_read_key和handler_read_next，
handler_read_next是按照索引顺序扫描下一行的次数，也可以代表回表次数(比如有icp可以减少这
个值），
所以handler_read_next也不能判断索引实际扫描的行数，而是索引扫描结果行数。
看下执行计划：有using index condition，说明第二个列status in (1,2,8)还是icp条件,
索引access用到几个列，可以通过key_len看：
first_name的key_len=14*3 2 (变长的 2,utf8mb3是3个字节）=44,
status是int型，对应的key_len=4 1(允许null 1)=5
可以看到执行计划的key_len=49,说明两个条件都用到。


explain select * from t1 
where t1.first_name='saniya' and  t1.status in (1,2,8);


从树形计划上看：using idx1_t1 over 转为两个or分支，也就是2个区间，说明两个条件都用到，
对应的还有icp，不过这个icp貌似作用不大：



可以查看实际执行信息：


通过show status查看handler信息，可以看到handler_read_key=3，对应三个索引区间。
说明first_name和status都参与索引扫描，执行计划里显示的icp基本用不着，这时候
handler_read_next=扫描的行数。


下面将索引顺序换一下,貌似都和in非前导列的一样，都有icp，都是改成or条件（实际上这个icp没
有啥用filtered=100）：
将选择性不好的列放前面：
create index idx2_t1 on t1(status,first_name);


通过key_len和树形计划的using idx2_t1 over，可以看到两个列条件都用到：
现在让两个列都是in,看是否能转为2*3=6个索引区间：


实际执行看：

执行计划里转为的or超过3个后面变为or (n more)


可以看到转为6个扫描区间：handler_read_key=6，
icp的实际用不上，所以两个列都是index access,对应handler_read_next和rows_examined都是
扫描的行数144：




总结：mysql单表访问，组合索引in不管是否是前导列，都能按照leftmost prefix规则，转为or,然后
转为多个索引区间(interval),
能够充分利用组合索引条件进行index access,执行计划里虽然有icp,基本用不着。
mysql没有inlist iterator算子，可以将in list转为多个区间从而实现单表访问inlist iterator效果。




2.2）多表join,inlist表作为被驱动表
看能否实现和单表一样的效果


先给t2的last_name建个索引，让t2做驱动表，t1被驱动：
create index idx_t2 on t2(last_name);




mysql组合索引有in的，如果in里只有一个值，和oracle一样，也会转为等值，则组合索引都是
等值的，可以直接access


mysql和oracle对于组合索引中条件in有多个值的的处理貌似不一样：


###mysql没有index inlist iterator算子，如果组合索引前导列是in并且有多个值，则不能转为多个
等值的组合条件，
这样只能in的列作为access,后续列作为icp，如果关联查询且被驱动走nl，则in的列作为单独条件，
作为索引access效率则可能很低，
所以在mysql里，关联列作为索引前导列较好，in的作为非前导列（用于icp条件，可能有bug将in
条件放到回表filter，则可能非常影响效率）。


1)不管in是否多个值，列是否是前导列，则都有icp
2)如果in的是非前导列，则in作为icp条件，这个和oracle一样
3)如果in是前导列，则in条件列对应的后续列作为icp条件，这个和oracle不一样，oracle有inlist
iterator算子,
  将in转为or,变为多个索引等值条件，然后避免icp，mysql没有inlist interator算子，in作为前导列
在关联查询里效率可能很低，将关联列作为前导列较好。


###在mysql里in条件避免不了icp,所以关联查询的被驱动表，将关联列作为索引前导列，这样
可以access条件，in条件列作为非前导列使用icp，
如果将in条件作为前导列，则关联列只能用于icp，可能效率很低。

驱动表730行，被驱动表循环730次：



###1.先测试关联列是前导列，然后 in list组合索引，走nested loops,t1被驱动：

通过执行计划看，多表join和单表不一样，t1被驱动，走idx1_t1(first_name,status),对应的
key_len是44，不是49（两个列都用上是49），
可以看到filtered=59.26,也就是status in (1,3,8)起类似过滤效果是真正icp，过滤了接近40%的
数据，这个和oracle的多表join不一样，
在mysql里貌似多表join的索引有in list,不能让多列都走index access:

耗时0.22s,索引访问只能用到first_name,这里的first_name选择性比较好，这样status条件走不了
索引其实差别不大
改成union all效果不好，所以将关联列作为前导列较好，这样可以用到前导列索引：

show status like '%handler%';

handler_read_key=驱动表索引区间1 被驱动表730次（每次一个区间）=731，
handler_read_next这时候就不是索引真正扫描的次数了，因为被驱动表t1只走first_name列索引
扫描，还有icp:status in (1,3,8),
这时候只是结果行数，相应的examined_rows也一样，这时候都是结果行数，结果行数包括驱动
表t2和t1的索引结果行数。