記一次基于倒序索引的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_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的篩選字段，如果直接用索引過濾出來數據，再排序后返回數據，可能是一個非常消耗資源的操作，因此優化器不選擇這些索引也很正常，如果能夠通過索引建立與查詢語句排序一致的索引，才能適應于此類查詢。 轉自https://www.cnblogs.com/wy123/p/18229506 該文章在 2025/2/17 16:38:06 編輯過 |
關鍵字查詢
相關文章
正在查詢... 
|
400 186 1886
