記一次基于倒序索引的SQL優(yōu)化

當前位置：點晴教程→知識管理交流 →『技術(shù)文檔交流』

freeflydom

2025年2月17日 15:50 本文熱度 1118

本文測試環(huán)境為SQLserver2019

背景

某業(yè)務流水表，會基于固定范圍內(nèi)的業(yè)務編號做寫入以及查詢操作，熱數(shù)據(jù)的量級在億級別，一個典型的查詢是基于業(yè)務編碼查詢最新（時間戳）某種狀態(tài)的前N條數(shù)據(jù)

簡化后的表結(jié)構(gòu)如下

create table TestTable01
(
    id                    bigint identity,   --主鍵Id，自增
    business_code        char(7),            --業(yè)務編碼，一定范圍內(nèi)(測試代碼從BC00001~BC00200)
    business_timestamp   datetime2,          --業(yè)務發(fā)生時間戳
    business_value       decimal(15,3),      --業(yè)務發(fā)生時涉及的數(shù)值
    business_status      tinyint,            --業(yè)務狀態(tài)編碼(測試數(shù)據(jù)范圍為1~5)
    other_col            varchar(100),
    constraint pk_TestTable01 primary key(id)
);

基于上述表結(jié)構(gòu),簡化數(shù)據(jù)量，基于時間順序生成1千萬條數(shù)據(jù)

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

典型的數(shù)據(jù)如下

select top 100 * from TestTable01

反向索引掃描的執(zhí)行計劃

除了主鍵索引，開發(fā)人員創(chuàng)建的索引如下

create index idx_bcode_statue on TestTable01 (business_code,business_status)

一個典型的查詢?nèi)缦拢?span data-wiz-span="data-wiz-span">咋一看確實沒啥問題

select top 30 * from TestTable01 where business_code = 'BC00146' and business_status in (3, 5) order by id desc

所以一開始基于business_code+business_status建立的索引，表面上看沒有什么不妥當?shù)?span data-wiz-span="data-wiz-span">，仔細觀察一下，還是會發(fā)現(xiàn)一些潛在問題的端倪

首先看執(zhí)行計劃，改查執(zhí)行的時候，并沒有用到idx_bcode_statue這個索引，盡管這個索引包含了where條件中的兩個字段，實際上執(zhí)行計劃是一個cluster index scan，也就是全表掃描，但是該查詢瞬間就返回了，“體感上”很快。

上述查詢IO以及CPU消耗信息，342次IO以及幾乎為0的CPU消耗，，總耗時6毫秒，這個代價確實不大

可能有人會有疑問，這里測試數(shù)據(jù)1000W的數(shù)據(jù)量，全表掃描應該是很慢的才對，事實上不要看到全量掃描就覺得有問題，繼續(xù)看一下該執(zhí)行計劃的細節(jié)。

這里的Scan Direction是BACKWARD，也就是“反向（聚集）索引掃描”，他確實是一個表掃描，但是掃描過程中提出來符合條件的數(shù)據(jù)之后，掃描中斷，并不會完整地掃描整個表的數(shù)據(jù)頁。

這里不難理解，對于“聚集索引”這個B+樹，因為是查詢某個業(yè)務代碼的某兩種狀態(tài)最新的30條數(shù)據(jù)，按照數(shù)據(jù)的生成邏輯（主鍵值跟隨時間戳做遞增），所以倒序掃描，絕大多數(shù)情況下可以很快找到符合條件的數(shù)據(jù)。原理如下示意圖。所謂的反向索引掃描，就是從B+樹的右邊開始往左邊掃描數(shù)據(jù)頁，直至找到滿足查詢條件的數(shù)據(jù)。

關(guān)于“正向索引掃描”和反向索引掃描，參考這里：https://www.cnblogs.com/wy123/p/5552719.html

所以，即便是開發(fā)人員基于where查詢條件建立的create index idx_bcode_statue on TestTable01 (business_code,business_status)索引沒有用上，但是這個查詢在大多數(shù)情況下都沒有問題。

上述采用（反向）索引掃描的執(zhí)行計劃，終究是掃描（scan）而不是查找（seek），如果不想采用默認的索引掃描策略，強制使用上述idx_bcode_statue這個索引呢，代價會變高，變低，還是差不多？
可以看到使用了idx_bcode_statue 索引的情況下，執(zhí)行計劃看起來確實是索引查找（index seek），但是相比默認的聚集索引反向掃描，這里的IO代價也翻了數(shù)十倍。

這就是為什么優(yōu)化器在默認情況下不選擇非聚集索引（idx_bcode_statue）查找的原因，如果明白了這個道理，其實就不用往下看了。

因此默認情況下，優(yōu)化器選擇執(zhí)行聚集索引（反向）掃描是沒有問題的，不出問題的話，問題就出來了。。。

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

某天業(yè)務反饋某生產(chǎn)系統(tǒng)偶發(fā)出現(xiàn)卡頓，嚴重影響業(yè)務，同時服務器上部署的擴展事件（Extended Event）也確實捕獲到了一些關(guān)于該SQL的慢日志，結(jié)合業(yè)務反饋問題出現(xiàn)的時間點和擴展事件捕獲的慢查詢，確定就是該語句引起的性能問題。

通過分析慢查詢?nèi)罩景l(fā)現(xiàn)：這個SQL在某些參數(shù)下執(zhí)行時間較長，遠超預期，同時其IO消耗非常大，有超過10W次甚至更多的IO。

但從擴展事件里解析出來的SQL語句，包括了參數(shù)，當嘗試再次去服務器上執(zhí)行的時候發(fā)現(xiàn)其執(zhí)行非常快，執(zhí)行計劃也是預期的反向聚集索引掃描，IO消耗非常低，那么在當時的情況下，為什么會出現(xiàn)性能如此的低下，需要消耗如此多的IO？

這里（刪除一部分數(shù)據(jù)）模擬某個設備很久沒有業(yè)務流水數(shù)據(jù)生成然后對這個Id做類似上述做同樣的查詢，會出現(xiàn)什么結(jié)果？

對比上述的測試案例，大約在6毫秒返回結(jié)果，這次的案例就出現(xiàn)在1秒多，IO在80000多次。這明顯跟一開始的case（8毫秒返回結(jié)果）有天壤之別。

由上可見：

1，基于主鍵（聚集索引）的反向索引掃描，對于多數(shù)case沒有問題，但是對于特殊case會造成巨大的IO消耗，并不是一個最優(yōu)解。

2，對于常規(guī)的基于where條件的索引，優(yōu)化器壓根就不會選擇它。

如何建立索引

上文一開始就提到了，對于常規(guī)的case，優(yōu)化器默認的反向聚集索引掃描已經(jīng)是一個最優(yōu)解了，基于where條件建立的索引（create index idx_bcode_statue on TestTable01 (business_code,business_status)），默認情況下也用不上。但是對于非常規(guī)的case，比如上述BC147這種，某些業(yè)務代碼最近一段時間內(nèi)并沒有產(chǎn)生業(yè)務數(shù)據(jù)，再用默認的反向聚集索引掃描就不合理了，因為這個掃描會掃描出來一大批數(shù)據(jù)頁面，才能找到滿足條件的數(shù)據(jù)，因此這是一個不合理的執(zhí)行計劃。但同時，代碼層面無法判斷某個業(yè)務代碼在近期否產(chǎn)生了業(yè)務數(shù)據(jù)，也就無從得知那個參數(shù)使用默認的聚集索引掃描更高效。因此需要一種對于兩種case都適用的優(yōu)化方式，既然按照id做倒序排序查詢某個業(yè)務代碼的數(shù)據(jù)，那可以直接基于業(yè)務代碼和“倒序”Id的聯(lián)合索引

直接上代碼

create index idx_bcode_id_status on TestTable01(business_code, id desc, business_status)

基于上面idx_bcode_id_status 創(chuàng)建之后的執(zhí)行計劃如下：

對于數(shù)據(jù)分布均勻的case

對于基于時間戳最近沒有生成數(shù)據(jù)的case，一樣都會基于idx_bcode_id_status 這個索引做index seek

該索引的思路就是，基于business_code，然后加上自增Id的倒序方式，讓business_code的數(shù)據(jù)按照Id倒序的方式組織索引，其查詢的過程中會先掃描出來最新的數(shù)據(jù)頁。

反向索引優(yōu)化是否適合于MySQL

同時，該場景問題的優(yōu)化，不但可以應用在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;
-- 生成測試數(shù)據(jù)存儲過程
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);

總結(jié)

針對查詢創(chuàng)建索引的時候，不但要看where條件中字段的選擇性，同時要關(guān)注排序條件，因為忽略了排序條件的情況下，僅關(guān)注查詢where的篩選字段，如果直接用索引過濾出來數(shù)據(jù)，再排序后返回數(shù)據(jù)，可能是一個非常消耗資源的操作，因此優(yōu)化器不選擇這些索引也很正常，如果能夠通過索引建立與查詢語句排序一致的索引，才能適應于此類查詢。

轉(zhuǎn)自https://www.cnblogs.com/wy123/p/18229506

該文章在 2025/2/17 16:38:06 編輯過

關(guān)鍵字查詢

倒序

SQL

優(yōu)化

索引

相關(guān)文章

正在查詢...