记一次基于倒序索引的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 索引的情况下，执行计划看起来确实是索引查找（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);
20250822补充：该思路在PostgreSQL下也嘎嘎好使
create table my_table01
(
c1 bigint generated always as identity primary key, --主键Id，自增
c2 char(7), --业务编码，一定范围内(测试代码从BC00001~BC00200)
c3 timestamp, --业务发生时间戳
c4 decimal(15,3), --业务发生时涉及的数值
c5 smallint, --业务状态编码(测试数据范围为1~5)
c6 varchar(100)
);
select * from my_table01;
insert into my_table01 (c2,c3,c4,c5,c6)
select
'BC' || lpad(((random() * 200)::int)::text, 3, '0') as code,
'2024-08-21 00:00:00':: timestamp + (gs * interval '1 second') as ts,
(random() * 10000)::int as amount,
(random() * 5)::int as category,
uuid_generate_v4() as uid
from generate_series(0, 31536000) as gs;
create index idx_c2_c5 on my_table01 (c2,c5);
select * from my_table01 limit 100;
EXPLAIN (ANALYZE, BUFFERS)
select * from my_table01 where c2 = 'BC146' and c5 in (3, 5) order by c1 desc limit 30;
delete from my_table01 where c2 = 'BC147';
create index idx_c2_c1_c5 on my_table01(c2, c1 desc, c5)
EXPLAIN (ANALYZE, BUFFERS)
select * from my_table01 where c2 = 'BC147' and c5 in (3, 5) order by c1 desc limit 30;
EXPLAIN (ANALYZE, BUFFERS)
select * from my_table01 where c2 = 'BC146' and c5 in (3, 5) order by c1 desc limit 30;
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------------------------------+
Limit (cost=0.56..341.25 rows=30 width=68) (actual time=0.044..0.153 rows=30 loops=1) |
Buffers: shared hit=94 |
-> Index Scan using idx_c2_c1_c5 on my_table01 (cost=0.56..524517.83 rows=46188 width=68) (actual time=0.042..0.149 rows=30 loops=1)|
Index Cond: (c2 = 'BC146'::bpchar) |
Filter: (c5 = ANY ('{3,5}'::integer[])) |
Rows Removed by Filter: 78 |
Buffers: shared hit=94 |
Planning Time: 0.112 ms |
Execution Time: 0.212 ms |
总结
针对查询创建索引的时候，不但要看where条件中字段的选择性，同时要关注排序条件，因为忽略了排序条件的情况下，仅关注查询where的筛选字段，如果直接用索引过滤出来数据，再排序后返回数据，可能是一个非常消耗资源的操作，因此优化器不选择这些索引也很正常，如果能够通过索引建立与查询语句排序一致的索引，才能适应于此类查询。
浙公网安备 33010602011771号