# 反向索引扫描 #### 操作场景 Backward Index Scan 是一种反向索引扫描技术，通过反向扫描索引的方式可以避免排序操作。然而，由于反向扫描与某些特性（如 Index Condition Pushdown (ICP)）不兼容，这可能导致优化器选择 Backward Index Scan 后性能下降。 为了解决这一问题，TaurusDB 增加了 Backward Index Scan 的开关功能，支持动态开启和关闭，从而帮助用户应对上述挑战。 #### 前提条件 内核版本大于等于2.0.48.231200可使用该功能。内核版本的查询方法请参见[如何查看云数据库 TaurusDB实例的版本号](https://support.huaweicloud.com/taurusdb_faq/taurusdb_faq_0141.html)。 #### 设置BackwardIndexScan 可以通过参数开关来控制BackwardIndexScan特性，也可以使用HINT来实现。 - [通过参数设置] 表1参数说明 | 参数名称 | 级别 | 描述 | |:---|:---|:---| | optimizer_switch | Global，Session | 查询优化的总控制开关。 其中，BackwardIndexScan子控制开关为backward_index_scan，用于控制是否启用BackwardIndexScan特性，默认值为ON。 - ON：优化器能选用BackwardIndexScan。 - OFF：优化器不能选用BackwardIndexScan。 | - [通过Hint设置] - 在SQL语句执行期间开启Backward Index Scan特性 ``` /*+ set_var(optimizer_switch='backward_index_scan=on') */ : ``` - 在SQL语句执行期间关闭Backward Index Scan特性 ``` /*+ set_var(optimizer_switch='backward_index_scan=off') */ : ``` #### 使用示例 1. 开启BackwardIndexScan。 - 在optimizer_switch参数里设置此开关值。 ``` set optimizer_switch='backward_index_scan=on'; Query OK, 0 rows affected (0.00 sec) ``` ``` set optimizer_switch='backward_index_scan=off'; Query OK, 0 rows affected (0.00 sec) ``` - 通过HINT方式在SQL语句中设置开关值。 ``` explain select /*+ set_var(optimizer_switch='backward_index_scan=on') */ c13,c16 from tt where c10=10 and c7=7 and c12=12 and to_days(c13)=547864 and c16 is not null order by c13 desc; ``` ``` explain select /*+ set_var(optimizer_switch='backward_index_scan=off') */ c13,c16 from tt where c10=10 and c7=7 and c12=12 and to_days(c13)=547864 and c16 is not null order by c13 desc; ``` 2. 查看控制效果。 通过执行explain语句查看执行计划中是否包含"Backward index scan"来确认控制效果。 1. 准备数据。 ``` create table tt( id int not null primary key, a int, b int, c int, key idx_a_b(a, b)); ``` ``` insert into tt values(1,1,1,1),(2,1,2,1),(3,2,3,2),(4,2,4,3),(5,2,4,4); ``` 2. 当Backward Index Scan特性开启时，优化器会选择该特性来消除排序，可以通过查询optimizer_switch参数来判断backward_index_scan特性是否开启。 ``` select @@optimizer_switch\G *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on,derived_merge_no_subquery_check=off,gen_col_partition_prune=off,partial_result_cache=off,offset_pushdown=off,backward_index_scan=on 1 row in set (0.00 sec) ``` ``` explain select * from tt where a = 2 order by b desc; +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+---------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+---------------------+ | 1 | SIMPLE | tt | NULL | ref | idx_a_b | idx_a_b | 5 | const | 3 | 100.00 | Backward index scan | +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+---------------------+ 1 row in set, 1 warning (0.00 sec) ``` 3. 当Backward Index Scan特性关闭时，优化器会增加Sort算子进行排序，查看以下执行计划。 ``` set optimizer_switch='backward_index_scan=off'; Query OK, 0 rows affected (0.00 sec) ``` ``` explain select * from tt where a = 2 order by b desc; +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+----------------+ | 1 | SIMPLE | tt | NULL | ref | idx_a_b | idx_a_b | 5 | const | 3 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) ``` #### 性能测试 执行某条SQL语句时优化器选用上了Backward Index Scan特性，查询耗时约为4.54s。 ``` explain analyze select detail_record_id, record_id, business_id, business_detail_id, unique_code, create_time, creator, last_updater, last_update_time, tenant_id, is_usable, operation_time, detail_operation_type, work_time, operator_id from detail_record d where d.tenant_id=554008 and d.creator = 585764 and operation_type = 3 and to_days(operation_time) = to_days(now()) and detail_operation_type is not null order by operation_time desc limit 1\G *************************** 1. row *************************** EXPLAIN: -> Limit: 1 row(s) (cost=151707.54 rows=1) (actual time=4539.137..4539.137 rows=0 loops=1) -> Filter: ((d.creator = 585764) and (to_days(d.operation_time) = (to_days(now()))) and (d.detail_operation_type is not null)) (cost=151707.54 rows=263565) (actual time=4539.135..4539.135 rows=0 loops=1) -> Index lookup on d using idx_time (tenant_id=554008, operation_type=3; iterate backwards) (cost=151707.54 rows=2928502) (actual time=0.089..4449.445 rows=1562755 loops=1) 1 row in set (4.54 sec) ``` 通过HINT的方式控制优化器不能选用BackwardIndexScan特性后，规避此场景下BackwardIndexScan特性与索引条件下推不兼容的问题，再次查询耗时约为0.37s，执行效率明显提升。 ``` explain analyze select /*+ set_var(optimizer_switch='backward_index_scan=off') */ detail_record_id, record_id, business_id, business_detail_id, unique_code, create_time, creator, last_updater, last_update_time, tenant_id, is_usable, operation_time, detail_operation_type, work_time, operator_id from detail_record d where d.tenant_id=554008 and d.creator = 585764 and operation_type = 3 and to_days(operation_time) = to_days(now()) and detail_operation_type is not null order by operation_time desc limit 1\G *************************** 1. row *************************** EXPLAIN: -> Limit: 1 row(s) (cost=209431.59 rows=1) (actual time=370.208..370.208 rows=0 loops=1) -> Sort: d.operation_time DESC, limit input to 1 row(s) per chunk (cost=209431.59 rows=2928502) (actual time=370.207..370.207 rows=0 loops=1) -> Filter: ((d.creator = 585764) and (d.detail_operation_type is not null)) (actual time=370.189..370.189 rows=0 loops=1) -> Index lookup on d using idx_time (tenant_id=554008, operation_type=3), with index condition: (to_days(d.operation_time) = (to_days(now()))) (actual time=370.188..370.188 rows=0 loops=1) 1 row in set (0.37 sec) ```