我的书签
添加书签
移除书签在基于规则的路径选择之后，如果存在多个可以选择的路径，那么Oceanbase会按个计算每个路径的代价，并从中选择代价最小的路径作为最终选择的路径。Oceanbase的代价模型考虑了CPU代价(比如处理一个谓词的cpu开销)和IO代价(比如顺序和随机读取宏块和微块的代价)，CPU代价和IO代价最终相加得到一个总的代价。在Oceanbase中，每个访问路径的代价在计划中都会展示出来，下图展示了相关信息。
OceanBase (root@test)> create table t1(a int primary key, b int, c int, index k1(b));Query OK, 0 rows affected (0.35 sec)-- 主表路径的代价OceanBase (root@test)> explain select/*+index(t1 primary)*/ * from t1 where b < 10;+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Query Plan |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| ===================================|ID|OPERATOR |NAME|EST. ROWS|COST|-----------------------------------|0 |TABLE SCAN|t1 |200 |622 |===================================Outputs & filters:-------------------------------------0 - output([t1.a], [t1.b], [t1.c]), filter([t1.b < 10]),access([t1.b], [t1.a], [t1.c]), partitions(p0)-- 所以k1路径的代价OceanBase (root@test)> explain select/*+index(t1 k1)*/ * from t1 where b < 10;+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Query Plan |+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|t1(k1)|200 |1114|=====================================Outputs & filters:-------------------------------------0 - output([t1.a], [t1.b], [t1.c]), filter(nil),access([t1.b], [t1.a], [t1.c]), partitions(p0)
对于一个访问路径，它的代价主要由两部分组成，扫描访问路径的代价和回表的代价。如果一个访问路径不需要回表，那么就没有回表的代价。在Oceanbase中，访问路径的代价取决于很多因素，比如扫描的行数，回表的行数，投影的列数，谓词的个数等。但是对于访问路径来说，代价在很大程度上取决于行数，所以在下面的分析中，我们从行数这个维度来介绍这两部分的代价。
扫描访问路径的代价: 扫描访问路径的代价跟扫描的行数成正比，理论上来说扫描的行数越多，执行时间就会越久。对于一个访问路径，query range决定了需要扫描的范围，从而决定了需要扫描的行数。Query range的扫描是顺序IO。
回表的代价: 回表的代价跟回表的行数也也是正相关的，回表的行数越多(回表的行数是指满足所有能在索引上执行的谓词的行数)，执行时间就会越久。回表的扫描是随机IO，所以回表一行的代价会比query range扫描一行的代价高很多。
当我们分析一个访问路径的性能的时候，可以从上面两个因素入手，既定位出来通过query range扫描的行数以及回表的行数。这两个行数通常我们可以通过执行sql语句来获取，如下图所示，对于查询select * from t1 where c2 > 20 and c2 < 800 and c3 < 200, 对于索引k1这个访问路径，我们首先通常计划展示来获取用来抽取query range的谓词，如下图所示，谓词c2 > 20 and c2 < 800用来抽取query range了，谓词c3 < 200被当成回表前的谓词。那么我们就可以使用如下图所示的两个查询来检查query range抽取的行数以及回表之后的行数。
OceanBase (root@test)> create table t1(c1 int primary key, c2 int, c3 int, c4 int, c5 int, index k1(c2,c3));Query OK, 0 rows affected (0.26 sec)OceanBase (root@test)> explain extended_noaddr select/*+index(t1 k1)*/ * from t1 where c2 > 20 and c2 < 800 and c3 < 200;+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Query Plan |+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|t1(k1)|156 |1216|=====================================Outputs & filters:-------------------------------------0 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4], [t1.c5]), filter([t1.c3 < 200]),access([t1.c2], [t1.c3], [t1.c1], [t1.c4], [t1.c5]), partitions(p0),is_index_back=true, filter_before_indexback[true],range_key([t1.c2], [t1.c3], [t1.c1]), range(20,MAX,MAX ; 800,MIN,MIN),range_cond([t1.c2 > 20], [t1.c2 < 800])-- query range 扫描的行数OceanBase (root@test)> select/*+index(t1 k1)*/ count(*) from t1 where c2 > 20 and c2 < 800;+----------+| count(*) |+----------+| 779 |+----------+1 row in set (0.02 sec)-- 回表的行数OceanBase (root@test)> select/*+index(t1 k1)*/ count(*) from t1 where c2 > 20 and c2 < 800 and c3 < 200;+----------+| count(*) |+----------+| 179 |+----------+1 row in set (0.01 sec)
