mysql执行计划里索引访问算子和oracle的不同之处

mysql执行计划里索引访问算子和oracle的不同之处


1.mysql Index range scan算子包括index access,index filter(ICP),回表不带过滤的时间，
如果有额外的过滤条件，有Filter算子，过滤时间算在这里面。
ICP如果能大幅度减少回表的行，也就减少了回表的随机IO，则效率提升明显。


2.估算的rows实际上是访问方法扫描的行数，对应普通explain的rows,不包括ICP以及回表过滤filtered，不是结果行数，这个实际上和oracle不一样，oracle的rows/cardinality是整个条件结果行数


3.mysql的analyze的actual rows则包括过滤条件，所以估算的rows和actual rows实际上标准不统一，是缺陷


通过估算的rows可以看到索引index access效率，比如访问行数多，则效率低，然后actual rows少,说明要么走ICP，要么组合索引顺序非{BANNED}最佳佳


index访问，过滤，回表效率比较：index access > index filter >>> 回表访问，因为回表访问是随机IO，所以如果索引扫描大量行，回表过滤后行数很少，
说明索引非{BANNED}最佳佳，可以把过滤大量行的条件与索引访问条件一起，建立组合索引，就算like前通配，<>等也可以建到组合索引里，起到ICP的效果。


以上两点和ORACLE不一样，oracle的index range scan包括index access,index filter,但是不包括回表，回表有单独的index by rowid,然后回表包括其他过滤条件
oracle执行计划rows是当前步骤包括过滤条件的估算行数，和mysql不一样，相当于mysql的rows * filtered

1.走idx_emp1_icp索引(first_name,last_name)组合索引，可以根据first_name条件进行index access,然后根据last_name条件ICP，这样回表的行数只有1412行（actaul rows)
通过估算rows=28040，可以知道first_name like 'A%'条件估算返回28040行，很显然如果没有last_name like 'B%'作为ICP，回表会消耗大量随机IO，效率就低了

explain analyze
select/*+index(a idx_emp1_icp)*/ * from emp1 a
where first_name like 'A%' and last_name like 'B%' and hire_date>'1990-1-1'\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (a.hire_date > DATE'1990-01-01') (cost=12618.26 rows=1399) (actual time=0.041..10.664 rows=653 loops=1)
    -> Index range scan on a using idx_emp1_icp over ('A\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0' <= first_name <= 'A???????????????????????????????????????????????????????????????????????????????????????????????????' AND 'B\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0' <= last_name <= 'B???????????????'), with index condition: ((a.first_name like 'A%') and (a.last_name like 'B%')) (cost=12618.26 rows=28040) (actual time=0.039..10.529 rows=1412 loops=1)

1 row in set (0.02 sec)

2.走idx1_emp1，单独的first_name索引，则索引访问估算返回27534行，实际上返回行数14747,因为没有使用组合索引，用不到
last_name like 'B%' and hire_date>'1990-1-1'作为ICP，回表的行数比1的多（1是1412行），效率比1低：0.13s vs 0.02s

explain analyze
select/*+index(a idx1_emp1)*/ * from emp1 a
where first_name like 'A%' and last_name like 'B%' and hire_date>'1990-1-1'\G

*************************** 1. row ***************************
EXPLAIN: -> Filter: ((a.last_name like 'B%') and (a.hire_date > DATE'1990-01-01')) (cost=12390.56 rows=1374) (actual time=0.056..126.670 rows=653 loops=1)
    -> Index range scan on a using idx1_emp1 over ('A\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0' <= first_name <= 'A???????????????????????????????????????????????????????????????????????????????????????????????????'), with index condition: (a.first_name like 'A%') (cost=12390.56 rows=27534) (actual time=0.041..124.408 rows=14747 loops=1)

1 row in set (0.13 sec)

3.如果走索引，没有其他过滤条件，则只看到有索引扫描（包括index access,index filter,回表），
没有回表过滤

explain analyze
select/*+index(a idx_emp1_icp)*/ * from emp1 a
where first_name like 'A%' and last_name like 'B%'\G

*************************** 1. row ***************************
EXPLAIN: -> Index range scan on a using idx_emp1_icp over ('A\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0' <= first_name <= 'A???????????????????????????????????????????????????????????????????????????????????????????????????' AND 'B\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0' <= last_name <= 'B???????????????'), with index condition: ((a.first_name like 'A%') and (a.last_name like 'B%')) (cost=12618.26 rows=28040) (actual time=0.038..10.870 rows=1412 loops=1)