記一次基於倒序索引的SQL優化

本文測試環境為SQLserver2019 背景某業務流水錶，會基於固定範圍內的業務編號做寫入以及查詢操作，熱數據的量級在億級別，一個典型的查詢是基於業務編碼查詢最新（時間戳）某種狀態的前N條數據簡化後的表結構如下 create table TestTable01 ( id bigint iden ...

本文測試環境為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的篩選欄位，如果直接用索引過濾出來數據，再排序後返回數據，可能是一個非常消耗資源的操作，因此優化器不選擇這些索引也很正常，如果能夠通過索引建立與查詢語句排序一致的索引，才能適應於此類查詢。