记一次基于倒序索引的SQL优化

本文测试环境为SQLserver2019

背景

某业务流水表会基于固定范围内的业务编号做写入以及查询操作热数据的量级在亿级别一个典型的查询是基于业务编码查询最新时间戳某种状态的前N条数据
简化后的表结构如下
create table TestTable01
(
    id                    bigint identity,   --主键Id,自增
    business_code        char(7),            --业务编码,一定范围内(测试代码从BC00001~BC00200)
    business_timestamp   datetime2,          --业务发生时间戳
    business_value       decimal(15,3),      --业务发生时涉及的数值
    business_status      tinyint,            --业务状态编码(测试数据范围为1~5)
    other_col            varchar(100),
    constraint pk_TestTable01 primary key(id)
);

基于上述表结构,简化数据量基于时间顺序生成1千万条数据

declare @i int = 0
begin tran
    while @i<10000000
    begin
        insert into TestTable01 values (concat('BC',FORMAT(cast(rand()*200 as int), '00000')),dateadd(ss,@i,'2024-01-01'),rand()*10000,rand()*5,newid())
        set @i = @i+1
    end
commit
典型的数据如下
select top 100 * from TestTable01

 

 

反向索引扫描的执行计划

除了主键索引开发人员创建的索引如下
create index idx_bcode_statue on TestTable01 (business_code,business_status)
一个典型的查询如下,咋一看确实没啥问题
select top 30 * from TestTable01 where business_code = 'BC00146' and business_status in (3, 5) order by id desc
所以一开始基于business_code+business_status建立的索引表面上看没有什么不妥当的仔细观察一下还是会发现一些潜在问题的端倪
首先看执行计划,改查执行的时候,并没有用到idx_bcode_statue这个索引,尽管这个索引包含了where条件中的两个字段,实际上执行计划是一个cluster index scan也就是全表扫描但是该查询瞬间就返回了,“体感上很快

上述查询IO以及CPU消耗信息,342次IO以及几乎为0的CPU消耗,,总耗时6毫秒,这个代价确实不大

可能有人会有疑问这里测试数据1000W的数据量全表扫描应该是很慢的才对事实上不要看到全量扫描就觉得有问题继续看一下该执行计划的细节
这里的Scan Direction是BACKWARD,也就是“反向(聚集)索引扫描”,他确实是一个表扫描,但是扫描过程中提出来符合条件的数据之后,扫描中断,并不会完整地扫描整个表的数据页。

这里不难理解,对于“聚集索引”这个B+树,因为是查询某个业务代码的某两种状态最新的30条数据,安装数据的生成逻辑(主键值跟随时间戳做递增),所以倒序扫描,绝大多数情况下可以很快找到符合条件的数据。原理如下示意图。所谓的反向索引扫描,就是从B+树的右边开始往左边扫描数据页,直至找到满足查询条件的数据。

关于“正向索引扫描”和反向索引扫描,参考这里https://www.cnblogs.com/wy123/p/5552719.html
所以,即便是开发人员基于where查询条件建立的create index idx_bcode_statue on TestTable01 (business_code,business_status)索引没有用上,但是这个查询在大多数情况下都没有问题。
 
上述采用(反向)索引扫描的执行计划,终究是扫描(scan)而不是查找(seek),如果不想采用默认的索引扫描策略,强制使用上述idx_bcode_statue这个索引呢,代价会变高,变低,还是差不多?
可以看到使用了idx_bcode_statue 索引的情况下,执行计划看起来确实是索引查找(index seek),但是相比默认的聚集索引反向扫描,这里的IO代价也翻了数十倍。
 
这就是为什么优化器在默认情况下不选择非聚集索引(idx_bcode_statue)查找的原因,如果明白了这个道理,其实就不用往下看了。

因此默认情况下,优化器选择执行聚集索引(反向)扫描是没有问题的,不出问题的话,问题就出来了。。。

 

反向索引扫描造成的IO消耗

某天业务反馈某生产系统偶发出现卡顿,严重影响业务,同时服务器上部署的扩展事件(Extended Event)也确实捕获到了一些关于该SQL的慢日志,结合业务反馈问题出现的时间点和扩展事件捕获的慢查询,确定就是该语句引起的性能问题。
通过分析慢查询日志发现:这个SQL在某些参数下执行时间较长,远超预期,同时其IO消耗非常大,有超过10W次甚至更多的IO。
但从扩展事件里解析出来的SQL语句,包括了参数,当尝试再次去服务器上执行的时候发现其执行非常快,执行计划也是预期的反向聚集索引扫描,IO消耗非常低,那么在当时的情况下,为什么会出现性能如此的低下,需要消耗如此多的IO?
 
这里(删除一部分数据)模拟某个设备很久没有业务流水数据生成 然后对这个Id做类似上述做同样的查询,会出现什么结果?
对比上述的测试案例,大约在6毫秒返回结果,这次的案例就出现在1秒多,IO在80000多次。这明显跟一开始的case(8毫秒返回结果)有天壤之别。

由上可见:

1,基于主键(聚集索引)的反向索引扫描,对于多数case没有问题,但是对于特殊case会造成巨大的IO消耗,并不是一个最优解。

2,对于常规的基于where条件的索引,优化器压根就不会选择它。

如何建立索引

上文一开始就提到了,对于常规的case优化器默认的反向聚集索引扫描已经是一个最优解了基于where条件建立的索引(create index idx_bcode_statue on TestTable01 (business_code,business_status)),默认情况下也用不上。但是对于非常规的case比如上述BC147这种某些业务代码最近一段时间内并没有产生业务数据再用默认的反向聚集索引扫描就不合理了因为这个扫描会扫描出来一大批数据页面才能找到满足条件的数据因此这是一个不合理的执行计划但同时代码层面无法判断某个业务代码在近期否产生了业务数据也就无从得知那个参数使用默认的聚集索引扫描更高效因此需要一种对于两种case都适用的优化方式既然按照id做倒序排序查询某个业务代码的数据那可以直接基于业务代码和倒序Id的联合索引

直接上代码
create index idx_bcode_id_status on TestTable01(business_codeid desc, business_status)
基于上面idx_bcode_id_status 创建之后的执行计划如下:
对于数据分布均匀的case

 对于基于时间戳最近没有生成数据的case,一样都会基于idx_bcode_id_status 这个索引做index seek

该索引的思路就是,基于business_code,然后加上自增Id的倒序方式,让business_code的数据按照Id倒序的方式组织索引,其查询的过程中会先扫描出来最新的数据页。
 

反向索引优化是否适合于MySQL

同时,该场景问题的优化,不但可以应用在SQLServer上,索引的原理是一样的,在MySQL上页同样适用,MySQL早期版本不支持倒序索引,但是语法不报错,MySQL 8.0之后是支持反向索引的,下面是在MySQL 8.0.36上测试的,效果类似于SQLserver中。

 

MySQL下测试代码

create table TestTable01
(
    id                            bigint AUTO_INCREMENT NOT NULL PRIMARY key,    
    business_code            char(7),                
    business_timestamp    datetime,        
    business_value            decimal(15,3),    
    business_status        tinyint,    
    other_col                varchar(100)
);
 
SET GLOBAL innodb_flush_log_at_trx_commit = 0;
SET GLOBAL sync_binlog = 0;

-- 生成测试数据存储过程
CREATE DEFINER=`root`@`%` PROCEDURE `create_test_data`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    -- start TRANSACTION;
    set loop_count = 0;
    while loop_count<10000000 do
        INSERT INTO TestTable01(business_code,business_timestamp,business_value,business_status,other_col)
        VALUES (CONCAT('SC',LPAD(cast(CAST(RAND()*200 AS UNSIGNED int) AS CHAR), 5, '0')),DATE_ADD('2024-01-01', INTERVAL loop_count second),RAND()*10000,CAST(RAND()*5 AS UNSIGNED INT),UUID()); 
        SET loop_count = loop_count + 1;
    END while;
    -- COMMIT;
END


call create_test_data(1000000);

 

总结

针对查询创建索引的时候,不但要看where条件中字段的选择性,同时要关注排序条件,因为忽略了排序条件的情况下,仅关注查询where的筛选字段,如果直接用索引过滤出来数据,再排序后返回数据,可能是一个非常消耗资源的操作,因此优化器不选择这些索引也很正常,如果能够通过索引建立与查询语句排序一致的索引,才能适应于此类查询。

 

 

热门相关:恶魔总裁霸道宠:老婆,太腹黑   隐婚101天:唐少宠妻成瘾   新书   万里情深不负   千夫斩