根据官方文档:
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000003981255
之中的例子Q2:
obclient> CREATE INDEX e1 ON t1(c2,c3);
obclient> EXPLAIN SELECT * FROM t1 WHERE c2 < 1 AND c3 < 1 AND c4 < 1;
+---------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------- |
| |0 |TABLE RANGE SCAN|T1(E1)|1 |5 | |
| ================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T1.C1], [T1.C2], [T1.C3], [T1.C4]), filter([T1.C3 < cast(1, NUMBER(-1, -85))], [T1.C4 < cast(1, NUMBER(-1, -85))]), rowset=16 |
| access([T1.C1], [T1.C2], [T1.C3], [T1.C4]), partitions(p0) |
| is_index_back=true, is_global_index=false, filter_before_indexback[true,false], |
| range_key([T1.C2], [T1.C3], [T1.C1]), range(MIN,MIN,MIN ; 1,MIN,MIN), |
| range_cond([T1.C2 < cast(1, NUMBER(-1, -85))])
我从这里看不出filter_before_indexback的规律。
这条sql,会扫描索引上满足c2<1的索引块,然后在这些块里过滤c3,即"索引回索引",再回表过滤c4。我自己的猜测是,filter_before_indexback的中括号里本来应该是3个值,依次对应c2,c3,c4这三个条件。c2被自动省略,对c3的过滤翻译过来就是filter_before_indexback,所以c3对应的值是true,c4对应的值是false,不知道这个理解对不对?
假如是对的,那么为什么三个值对应的顺序依次是c2,c3,c4?在其它输出内容中看不到这种顺序。
