在基于规则的路径选择之后,如果存在多个可以选择的路径,那么Oceanbase会按个计算每个路径的代价,并从中选择代价最小的路径作为最终选择的路径。Oceanbase的代价模型考虑了CPU代价(比如处理一个谓词的cpu开销)和IO代价(比如顺序和随机读取宏块和微块的代价),CPU代价和IO代价最终相加得到一个总的代价。在Oceanbase中,每个访问路径的代价在计划中都会展示出来,下图展示了相关信息。

    1. OceanBase (root@test)> create table t1(a int primary key, b int, c int, index k1(b));
    2. Query OK, 0 rows affected (0.35 sec)
    3. -- 主表路径的代价
    4. OceanBase (root@test)> explain select/*+index(t1 primary)*/ * from t1 where b < 10;
    5. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    6. | Query Plan |
    7. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    8. | ===================================
    9. |ID|OPERATOR |NAME|EST. ROWS|COST|
    10. -----------------------------------
    11. |0 |TABLE SCAN|t1 |200 |622 |
    12. ===================================
    13. Outputs & filters:
    14. -------------------------------------
    15. 0 - output([t1.a], [t1.b], [t1.c]), filter([t1.b < 10]),
    16. access([t1.b], [t1.a], [t1.c]), partitions(p0)
    17. -- 所以k1路径的代价
    18. OceanBase (root@test)> explain select/*+index(t1 k1)*/ * from t1 where b < 10;
    19. +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    20. | Query Plan |
    21. +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    22. | =====================================
    23. |ID|OPERATOR |NAME |EST. ROWS|COST|
    24. -------------------------------------
    25. |0 |TABLE SCAN|t1(k1)|200 |1114|
    26. =====================================
    27. Outputs & filters:
    28. -------------------------------------
    29. 0 - output([t1.a], [t1.b], [t1.c]), filter(nil),
    30. access([t1.b], [t1.a], [t1.c]), partitions(p0)

    对于一个访问路径,它的代价主要由两部分组成,扫描访问路径的代价和回表的代价。如果一个访问路径不需要回表,那么就没有回表的代价。在Oceanbase中,访问路径的代价取决于很多因素,比如扫描的行数,回表的行数,投影的列数,谓词的个数等。但是对于访问路径来说,代价在很大程度上取决于行数,所以在下面的分析中,我们从行数这个维度来介绍这两部分的代价。

    1. 扫描访问路径的代价: 扫描访问路径的代价跟扫描的行数成正比,理论上来说扫描的行数越多,执行时间就会越久。对于一个访问路径,query range决定了需要扫描的范围,从而决定了需要扫描的行数。Query range的扫描是顺序IO。

    2. 回表的代价: 回表的代价跟回表的行数也也是正相关的,回表的行数越多(回表的行数是指满足所有能在索引上执行的谓词的行数),执行时间就会越久。回表的扫描是随机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抽取的行数以及回表之后的行数。

    1. OceanBase (root@test)> create table t1(c1 int primary key, c2 int, c3 int, c4 int, c5 int, index k1(c2,c3));
    2. Query OK, 0 rows affected (0.26 sec)
    3. OceanBase (root@test)> explain extended_noaddr select/*+index(t1 k1)*/ * from t1 where c2 > 20 and c2 < 800 and c3 < 200;
    4. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    5. | Query Plan |
    6. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    7. | =====================================
    8. |ID|OPERATOR |NAME |EST. ROWS|COST|
    9. -------------------------------------
    10. |0 |TABLE SCAN|t1(k1)|156 |1216|
    11. =====================================
    12. Outputs & filters:
    13. -------------------------------------
    14. 0 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4], [t1.c5]), filter([t1.c3 < 200]),
    15. access([t1.c2], [t1.c3], [t1.c1], [t1.c4], [t1.c5]), partitions(p0),
    16. is_index_back=true, filter_before_indexback[true],
    17. range_key([t1.c2], [t1.c3], [t1.c1]), range(20,MAX,MAX ; 800,MIN,MIN),
    18. range_cond([t1.c2 > 20], [t1.c2 < 800])
    19. -- query range 扫描的行数
    20. OceanBase (root@test)> select/*+index(t1 k1)*/ count(*) from t1 where c2 > 20 and c2 < 800;
    21. +----------+
    22. | count(*) |
    23. +----------+
    24. | 779 |
    25. +----------+
    26. 1 row in set (0.02 sec)
    27. -- 回表的行数
    28. OceanBase (root@test)> select/*+index(t1 k1)*/ count(*) from t1 where c2 > 20 and c2 < 800 and c3 < 200;
    29. +----------+
    30. | count(*) |
    31. +----------+
    32. | 179 |
    33. +----------+
    34. 1 row in set (0.01 sec)