Backward Index Scan

使用须知

内核版本大于等于2.0.48.231200可使用该功能。

开启BackwardIndexScan

除了使用上述开关来控制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参数里设置此开关值。

     mysql> set optimizer_switch='backward_index_scan=on';
     Query OK, 0 rows affected (0.00 sec)
     
     mysql> set optimizer_switch='backward_index_scan=off';
     Query OK, 0 rows affected (0.00 sec)

   • 通过HINT方式在SQL语句中设置开关值。

     mysql> 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;
     
     mysql> 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特性是否开启。

      mysql> 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)
      
      mysql> 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算子进行排序，查看以下执行计划。

      mysql> set optimizer_switch='backward_index_scan=off';
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> 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。

mysql> 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) = <cache>(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，执行效率明显提升。

mysql> 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) = <cache>(to_days(now())))  (actual time=370.188..370.188 rows=0 loops=1)
1 row in set (0.37 sec)