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

2024-06-03 19:58 由 MSSQL123 发表于 #数据库

本文测试环境为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_code, id 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的筛选字段，如果直接用索引过滤出来数据，再排序后返回数据，可能是一个非常消耗资源的操作，因此优化器不选择这些索引也很正常，如果能够通过索引建立与查询语句排序一致的索引，才能适应于此类查询。