活動中臺系統慢 SQL 治理實踐
活動中臺系統作為中臺項目非常注重系統性能和用戶體驗，數據庫系統性能問題會對應用程序的性能和用戶體驗產生負面影響。慢查詢可能導致應用程序響應變慢、請求堆積、系統負載增加等問題，甚至引發系統崩潰或不可用的情況，因此，需要在數據庫系統中針對執行緩慢的SQL查詢進行優化和改進。本文主要介紹活動中臺系統針對慢SQL問題的實踐治理案例。
一、慢 SQL 的含義
1.1 慢 SQL 的含義
慢SQL是指執行時間較長的SQL查詢或操作。真實的慢 SQL 通常會伴隨著大量的行掃描、臨時文件排序或者頻繁的磁盤 flush ，直接影響就是磁盤 IO 升高，讓正常的 SQL 變成了慢 SQL ，大面積執行超時。
大家不要被慢查詢這個名字誤導，以為慢查詢日志只會記錄 select 語句，其實也會記錄執行時間超過了long_query_time設定的閾值的 insert、update 等 DML 語句。
1.2 慢 SQL 的危害
從業務的角度來看：慢 SQL 會導致產品用戶體驗差，會減低用戶對產品的好感度。
從數據庫的角度來看：慢 SQL 會影響數據庫的性能，每個 SQL 執行都需要消耗一定的 I/O 資源。假設總資源是100，有一條慢 SQL 占用了30的資源共計1分鐘。那么在這1分鐘時間內，其他 SQL 能夠分配的資源總量就是70，如此循環，當資源分配完的時候，所有新的 SQL 執行將會排隊等待。
二、慢SQL是怎么產生的？
【缺乏索引】：如果在查詢中涉及到的列沒有適當的索引，數據庫系統可能需要執行全表掃描來找到匹配的行，從而導致查詢變慢。
【查詢條件不當】：查詢條件過于復雜、使用了不必要的 JOIN 操作、存在子查詢等，都可能導致查詢性能下降。
【數據量過大】：當數據表中的數據量非常龐大時，即使有索引，查詢也可能變得緩慢。
【鎖等待】：如果查詢需要訪問被其他事務鎖定的資源，就會導致查詢阻塞，執行時間變長。
【硬件資源不足】：數據庫服務器的硬件資源（如 CPU、內存、磁盤）不足以支撐查詢的執行，也會導致查詢變慢。
【不合適的數據庫設計】：數據庫表的設計不合理，如過度范式化、冗余數據等，會導致查詢性能下降。
【統計信息不準確】：數據庫的統計信息不準確會導致查詢優化器做出錯誤的執行計劃，影響查詢性能。
三、慢 SQL 治理實踐
3.1 問題分析和解決方案
通過監控平臺發現，項目的慢 SQL 數量較多，每天可能會產生幾千甚至上萬的慢 SQL，這對系統性能和用戶體驗都有不小的影響，因此，優化慢 SQL 問題值得被重視。我們從以下幾個方面進行排查和分析：
1.數據量
我們都知道，同樣的 SQL 語句，對于不同數據量的庫表，查詢效率也不一樣，當數據量達到千萬級甚至上億，普通的查詢語句執行時間可能也會超過一秒，進而出現慢 SQL 問題。
經過排查，發現不少庫表的數據量已經達到千萬，個別分表的數據量已經達到一億多，幾年前的歷史數據仍然保留，需要進行人工清理。
針對數據量對 SQL 執行帶來的影響，我們可以從三個方面解決：
（1）清理數據
最直接的方式就是將無效數據清理，很多幾年前的數據，幾乎沒有存儲的價值，可以直接提交刪除語句進行清理掉，當然，我們在刪除時要注意線上影響，避免一次性刪除太多數據，容易造成線上數據庫效率受到影響，對于單個分表可以采用分批刪除，每一批只刪除一個時間段；對于多個分表可以按照分表去刪除，盡量減小對線上環境的影響。
以活動中臺系統的答題活動為例，隨著答題活動幾年的運行，數據庫已經有大量的用戶數據，目前線上數據量很多到了千萬級，個別到了上億的數據量，這對線上訪問影響很大。
所以我們進行手動清理數據，目前線上一共10張分表，考慮到對線上用戶業務的影響，我們通過兩方面減小影響：
一是分五次進行清理，每次只清理2~3張分表；
二是將刪除語句轉化為根據主鍵刪除，大大提升 SQL 語句執行的效率。
最終清理掉大量的活動數據，我們采用的刪除策略是將一年以前的歷史數據進行刪除，這部分數據已經不會用到，這個時間范圍清理掉了大量的無效數據。
（2）分庫分表
手動清理數據雖然能解決問題，但是治標不治本，而且對于一些特殊的活動，可能留下的數據量仍然很大，這個時候如果庫表的數據量仍然較大，且不能進行刪除，就要考慮分庫分表是否足夠，可能之前出于業務考慮沒有進行分表或者分表數量較少，這個時候就要考慮進行更多的分表，以提升數據庫訪問的效率。這里如果可以的話最好設置分表策略，建立配置項，靈活擴充分表，避免直接發版。
活動中臺系統會有一張單獨的路由表，記錄分表路由，便于快速查詢分表。
同時可以把路由相關配置直接建立在配置中心，無須發版就可以快速擴分表。可以根據數據量大小創建10、20、50、100張分表。
（3）大數據量查詢移步ES數據庫
當然，對于一些響應要求比較高的業務需求，MySQL 數據庫的性能可能無法達到要求，這個時候可以考慮將數據存儲在 ElasticSearch 數據庫或者緩存當中。
2.SQL 語句
數據庫的數據量是重要的影響因素，但是 SQL 語句本身更會影響執行的效率，規范的 SQL 語句是避免慢 SQL 的前提。根據下圖可以看出，SQL 的執行順序為：
- 首先執行 from、join 來確定表之間的連接關系，得到初步的數據。
- 然后利用 where 關鍵字后面的條件對符合條件的語句進行篩選。
- from & join&where：用于確定要查詢的表的范圍，涉及到哪些表。
那么根據這個執行順序，我們來看下 SQL 執行過程中常見的問題有哪些?
（1）查詢字段
我們在項目中查詢最常用的是返回整個 DO 層對象，但其實很多時候我們只需要其中幾個字段甚至一個字段，這個時候查詢整個對象是很不劃算的，比如下面這個例子，該語句在數據庫管理器中的執行結果如下，執行時間為9269ms。
select * from a where id = 0;然而我們只是想查詢指定活動下的單個字段，這個時候可以不需要返回其他字段，只返回需要的字段，優化后示例如下，執行時間為4104ms，明顯執行時間變短了。
select result from a where id = 0;（2）索引問題
索引在數據庫查詢中是一個很重要的影響因素，走不到索引和走到索引是有很大的區別的。但是索引有弊也有利：
優點
- 提高查詢語句的執行效率，減少 IO 操作的次數
- 創建唯一性索引，可以保證數據庫表中每一行數據的唯一性
- 加了索引的列會進行排序，在使用分組和排序子句進行查詢時，可以顯著減少查詢中分組和排序的時間
缺點
- 索引需要占物理空間
- 創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加
- 當對表中的數據進行增刪改查時，索引也要動態的維護，這樣就降低了數據的更新效率
- 所以合理的設置索引并利用索引是高效執行SQL的重要因素。
以下面這個案例來分析：
CREATE TABLE `table_test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`test` varchar(128) NOT NULL DEFAULT '' COMMENT '索引測試字段',
............
`result` varchar(128) NOT NULL DEFAULT '' COMMENT '結果測試字段',
PRIMARY KEY (`id`),
KEY `test` (`test`),
) COMMENT='記錄表';這是活動平臺系統紀錄的數據表，其中【result】是唯一的，這里可以看到索引沒有涉及【result】，如果我們根據這個字段去查詢唯一記錄，看下執行結果如何：
耗時4286ms，這里根據建表語句可以看到有索引【KEY test (test)】，如果我們根據索引字段來查詢該條記錄的話，看下執行結果如何：
可以看到只需要16ms，索引對數據庫效率的提升至關重要。我們可以根據 explain 關鍵字來解析一下 SQL 語句，比如剛才的案例，我們來看下相應的結果：
可以看到，直接根據非索引字段【result】查詢，type 字段值為【ALL】，代表遍歷全表，掃描行數為3144273，如果帶上索引字段【test】，type 字段值為【ref】，代表匹配到單行記錄值，掃描行數為1，顯然通過索引查詢效率得到了極大的提升。對于索引的使用會出現以下幾種常見類型：
ALL：全表掃描，表示 MySQL 將遍歷整個表以滿足查詢條件。這通常是效率最低的訪問類型，應盡量避免。
index：索引全掃描，表示 MySQL 將遍歷整個索引以滿足查詢條件，而不是遍歷整個表。雖然比全表掃描效率要高，但仍然需要遍歷索引的每一行。
range：范圍掃描，表示 MySQL 使用了索引的一部分來滿足查詢條件，例如使用了索引的某個范圍。這通常發生在有范圍查詢條件時，例如使用了 WHERE 子句中的 BETWEEN、>、< 等操作符。
index_merge：索引合并，表示 MySQL 使用了多個索引來滿足查詢條件，然后將結果合并。這通常發生在查詢中有多個條件，每個條件可以使用不同的索引來訪問數據。
unique_subquery：唯一子查詢，表示 MySQL 使用了子查詢來獲取唯一的結果，并且子查詢使用了唯一索引。
const：常量，表示 MySQL 使用了常量表來獲取結果，這通常發生在查詢條件中包含了常量值。
ref：引用，表示 MySQL 使用了非唯一索引來掃描表，通常發生在查詢中使用了單個索引列作為條件。
（3）聯表查詢
【JOIN】關鍵詞在項目的日常查詢中可能會遇到，對于一些管理臺項目可能要求較低，可以作為日常開發查詢語句，但是對于面向用戶的項目，由于數據量較大，往往需要避免聯合查詢的使用。以下面這個案例來分析：
select * from a left join b on a.id = b.id where a.id = 0;這個SQL是聯合多張表進行聯合查詢，還有其他查詢條件，執行得出結果時間為1432ms，超過1秒則為慢 SQL，這里聯合兩張表，如果相關的數據量較大，則執行速度會較慢。我們可以將 SQL 拆分為兩個語句執行，拆為以下兩個 SQL 分步執行：
select * from a where id = 0;
select * from b where id = 0;分步獲取數據庫結果后再進行聚合，分步執行結果分別為728ms和744ms，聯合查詢拆分為簡單查詢可以有效減少慢 SQL，同時可以提高SQL查詢的復用性。
（4）條件查詢
在項目的日常開發中，SQL 語句切忌使用復雜查詢，這會對數據庫造成較大的壓力。下面這個例子就是使用了比較復雜的條件查詢：
select * from a where id in (select id from b) and time > '2024-03-29';使用查詢條件的同時還嵌套了查詢語句，除此之外還夾雜了聯合查詢，語句已經比較復雜，我們來看下查詢結果：
可以看到執行時間非常緩慢，達到12648ms，這個SQL的背景是用于業務對賬，雖然使用的是離線數據庫，但是仍然不能忽視它的風險，每天大量的執行這種復雜 SQL，還是對業務有一定的風險影響。考慮到業務價值單一，復用性不高，我們可以直接冗余一份數據到單獨的表里，避免復雜查詢，直接一步到位解決對賬帶來的慢 SQL 問題。
3. 整體策略
清理完數據庫的無效數據，優化完 SQL 語句本身，可能還是會出現慢 SQL 問題，這個時候我們要考慮下，是否可以優化整體的數據庫交互策略，以活動中臺系統的數據清理慢 SQL 為例。
活動中臺系統創建了一個定時任務，每天凌晨執行一次，對數據庫的無效歷史數據進行統一清理，具體刪除哪些庫表、什么時間段、什么條件都由配置項靈活控制，配置項示例如下：
"分表數量"：7,
"表名"：table,
"條件"：condition包括分表的數量、要刪除數據的表名、查詢的條件信息，查詢條件對應SQL語句中的【where】信息。執行流程如下圖：
整體上看刪除策略很通用，條件配置靈活，可以同時應對不同分表、不同查詢條件等。但是線上運行發現會產生大量的慢SQL，可能每天就會產生幾千條。
主要原因如下：
- 會出現聯表查詢的情況，數據量較大的表會出現執行時間超過一秒的 SQL 語句；
- 刪除策略中時間是重要的因素，但是時間往往不會設置為索引字段，所以很難充分利用索引；
- 刪除策略會掃描所有分表，但是很多分表可能全部掃描也沒有需要刪除的數據，會出現無效執行的情況。
針對以上策略，我們進行了綜合改進，主要措施有以下幾條：
- 避免聯表查詢，盡可能的拆分為簡單 SQL 執行；
- 不要從時間維度直接出發，而是從活動維度出發，如果一個活動結束時間較長，那么這個活動相關的數據自然不再需要，可以直接刪除；
- 利用分表路由信息，從活動本身出發快速路由到相關分表，減少 SQL 語句的無效執行。
修改完的配置項如下：
"分表數量"：7,
"表名"：table,
"條件"：condition,
"刪除數量"：1,
"刪除策略":1,
"開始時間":"",
"結束時間":""包括分表的數量、要刪除數據的表名、查詢條件、每次刪除的數量、開始時間和結束時間，在這個時間范圍內的活動都符合條件，整體上看刪除策略更為通用，條件配置更加靈活。優化的刪除策略流程圖如下：
從圖中可以看到，我們保留原有的刪除策略，避免個別數據表沒有活動信息和分表信息，只能單純根據時間掃描刪除；除此之外，對于有活動信息的數據，我們會先查詢活動，根據活動去刪除，如果數據表是根據活動進行分表，則直接查詢路由信息，并刪除指定分表的數據即可，也就是圖中的第三種策略，線上大部分的數據都會按照第三種策略執行，如果有活動信息但是未按照活動進行分表，可能按照用戶等維度進行分表，則可以根據活動信息進行刪除，也可以有效避免慢 SQL 的出現。
3.2 治理效果
經過對慢 SQL 的專項治理，活動中臺系統的慢 SQL 數量由幾千個，穩定在了兩位數，有效減少了慢SQL的數量，進一步提升了系統穩定性。
四、經驗總結
- 治理慢 SQL 的根本是從源頭避免慢 SQL，項目組內部必須達成高度一致，根據編碼規范進行前置避免慢 SQL 的出現。
- 離線數據庫不能成為忽視慢 SQL 問題的原因，仍然會有影響線上業務的風險，數據庫實例如果是混合部署的方式，可能離線庫所在的機器有其他業務的主庫，而且如果從庫延遲嚴重會影響主從故障切換。
- 數據庫的合理設計不能依賴后期重構，一開始就要盡可能的考慮充分。
- 慢 SQL 治理過程中出現的問題可以及時復盤，避免團隊其他成員繼續踩坑。
