活動中臺系統(tǒng)作為中臺項目非常注重系統(tǒng)性能和用戶體驗，數(shù)據(jù)庫系統(tǒng)性能問題會對應(yīng)用程序的性能和用戶體驗產(chǎn)生負(fù)面影響。慢查詢可能導(dǎo)致應(yīng)用程序響應(yīng)變慢、請求堆積、系統(tǒng)負(fù)載增加等問題，甚至引發(fā)系統(tǒng)崩潰或不可用的情況，因此，需要在數(shù)據(jù)庫系統(tǒng)中針對執(zhí)行緩慢的SQL查詢進(jìn)行優(yōu)化和改進(jìn)。本文主要介紹活動中臺系統(tǒng)針對慢SQL問題的實踐治理案例。

一、慢 SQL 的含義

1.1 慢 SQL 的含義

慢SQL是指執(zhí)行時間較長的SQL查詢或操作。真實的慢 SQL 通常會伴隨著大量的行掃描、臨時文件排序或者頻繁的磁盤 flush ，直接影響就是磁盤 IO 升高，讓正常的 SQL 變成了慢 SQL ，大面積執(zhí)行超時。

大家不要被慢查詢這個名字誤導(dǎo)，以為慢查詢?nèi)罩局粫涗?select 語句，其實也會記錄執(zhí)行時間超過了long_query_time設(shè)定的閾值的 insert、update 等 DML 語句。

1.2 慢 SQL 的危害

從業(yè)務(wù)的角度來看：慢 SQL 會導(dǎo)致產(chǎn)品用戶體驗差，會減低用戶對產(chǎn)品的好感度。

從數(shù)據(jù)庫的角度來看：慢 SQL 會影響數(shù)據(jù)庫的性能，每個 SQL 執(zhí)行都需要消耗一定的 I/O 資源。假設(shè)總資源是100，有一條慢 SQL 占用了30的資源共計1分鐘。那么在這1分鐘時間內(nèi)，其他 SQL 能夠分配的資源總量就是70，如此循環(huán)，當(dāng)資源分配完的時候，所有新的 SQL 執(zhí)行將會排隊等待。

二、慢SQL是怎么產(chǎn)生的？

【缺乏索引】：如果在查詢中涉及到的列沒有適當(dāng)?shù)乃饕瑪?shù)據(jù)庫系統(tǒng)可能需要執(zhí)行全表掃描來找到匹配的行，從而導(dǎo)致查詢變慢。

【查詢條件不當(dāng)】：查詢條件過于復(fù)雜、使用了不必要的 JOIN 操作、存在子查詢等，都可能導(dǎo)致查詢性能下降。

【數(shù)據(jù)量過大】：當(dāng)數(shù)據(jù)表中的數(shù)據(jù)量非常龐大時，即使有索引，查詢也可能變得緩慢。

【鎖等待】：如果查詢需要訪問被其他事務(wù)鎖定的資源，就會導(dǎo)致查詢阻塞，執(zhí)行時間變長。

【硬件資源不足】：數(shù)據(jù)庫服務(wù)器的硬件資源（如 CPU、內(nèi)存、磁盤）不足以支撐查詢的執(zhí)行，也會導(dǎo)致查詢變慢。

【不合適的數(shù)據(jù)庫設(shè)計】：數(shù)據(jù)庫表的設(shè)計不合理，如過度范式化、冗余數(shù)據(jù)等，會導(dǎo)致查詢性能下降。

【統(tǒng)計信息不準(zhǔn)確】：數(shù)據(jù)庫的統(tǒng)計信息不準(zhǔn)確會導(dǎo)致查詢優(yōu)化器做出錯誤的執(zhí)行計劃，影響查詢性能。

三、慢 SQL 治理實踐

3.1 問題分析和解決方案

通過監(jiān)控平臺發(fā)現(xiàn)，項目的慢 SQL 數(shù)量較多，每天可能會產(chǎn)生幾千甚至上萬的慢 SQL，這對系統(tǒng)性能和用戶體驗都有不小的影響，因此，優(yōu)化慢 SQL 問題值得被重視。我們從以下幾個方面進(jìn)行排查和分析：

1.數(shù)據(jù)量

我們都知道，同樣的 SQL 語句，對于不同數(shù)據(jù)量的庫表，查詢效率也不一樣，當(dāng)數(shù)據(jù)量達(dá)到千萬級甚至上億，普通的查詢語句執(zhí)行時間可能也會超過一秒，進(jìn)而出現(xiàn)慢 SQL 問題。

經(jīng)過排查，發(fā)現(xiàn)不少庫表的數(shù)據(jù)量已經(jīng)達(dá)到千萬，個別分表的數(shù)據(jù)量已經(jīng)達(dá)到一億多，幾年前的歷史數(shù)據(jù)仍然保留，需要進(jìn)行人工清理。

針對數(shù)據(jù)量對 SQL 執(zhí)行帶來的影響，我們可以從三個方面解決：

（1）清理數(shù)據(jù)

最直接的方式就是將無效數(shù)據(jù)清理，很多幾年前的數(shù)據(jù)，幾乎沒有存儲的價值，可以直接提交刪除語句進(jìn)行清理掉，當(dāng)然，我們在刪除時要注意線上影響，避免一次性刪除太多數(shù)據(jù)，容易造成線上數(shù)據(jù)庫效率受到影響，對于單個分表可以采用分批刪除，每一批只刪除一個時間段；對于多個分表可以按照分表去刪除，盡量減小對線上環(huán)境的影響。

以活動中臺系統(tǒng)的答題活動為例，隨著答題活動幾年的運(yùn)行，數(shù)據(jù)庫已經(jīng)有大量的用戶數(shù)據(jù)，目前線上數(shù)據(jù)量很多到了千萬級，個別到了上億的數(shù)據(jù)量，這對線上訪問影響很大。

所以我們進(jìn)行手動清理數(shù)據(jù)，目前線上一共10張分表，考慮到對線上用戶業(yè)務(wù)的影響，我們通過兩方面減小影響：

一是分五次進(jìn)行清理，每次只清理2~3張分表；

二是將刪除語句轉(zhuǎn)化為根據(jù)主鍵刪除，大大提升 SQL 語句執(zhí)行的效率。

最終清理掉大量的活動數(shù)據(jù)，我們采用的刪除策略是將一年以前的歷史數(shù)據(jù)進(jìn)行刪除，這部分?jǐn)?shù)據(jù)已經(jīng)不會用到，這個時間范圍清理掉了大量的無效數(shù)據(jù)。

（2）分庫分表

手動清理數(shù)據(jù)雖然能解決問題，但是治標(biāo)不治本，而且對于一些特殊的活動，可能留下的數(shù)據(jù)量仍然很大，這個時候如果庫表的數(shù)據(jù)量仍然較大，且不能進(jìn)行刪除，就要考慮分庫分表是否足夠，可能之前出于業(yè)務(wù)考慮沒有進(jìn)行分表或者分表數(shù)量較少，這個時候就要考慮進(jìn)行更多的分表，以提升數(shù)據(jù)庫訪問的效率。這里如果可以的話最好設(shè)置分表策略，建立配置項，靈活擴(kuò)充分表，避免直接發(fā)版。

活動中臺系統(tǒng)會有一張單獨的路由表，記錄分表路由，便于快速查詢分表。

同時可以把路由相關(guān)配置直接建立在配置中心，無須發(fā)版就可以快速擴(kuò)分表?？梢愿鶕?jù)數(shù)據(jù)量大小創(chuàng)建10、20、50、100張分表。

（3）大數(shù)據(jù)量查詢移步ES數(shù)據(jù)庫

當(dāng)然，對于一些響應(yīng)要求比較高的業(yè)務(wù)需求，MySQL 數(shù)據(jù)庫的性能可能無法達(dá)到要求，這個時候可以考慮將數(shù)據(jù)存儲在 ElasticSearch 數(shù)據(jù)庫或者緩存當(dāng)中。

2.SQL 語句

數(shù)據(jù)庫的數(shù)據(jù)量是重要的影響因素，但是 SQL 語句本身更會影響執(zhí)行的效率，規(guī)范的 SQL 語句是避免慢 SQL 的前提。根據(jù)下圖可以看出，SQL 的執(zhí)行順序為：

• 首先執(zhí)行 from、join 來確定表之間的連接關(guān)系，得到初步的數(shù)據(jù)。
• 然后利用 where 關(guān)鍵字后面的條件對符合條件的語句進(jìn)行篩選。
• from & join&where：用于確定要查詢的表的范圍，涉及到哪些表。

那么根據(jù)這個執(zhí)行順序，我們來看下 SQL 執(zhí)行過程中常見的問題有哪些?

（1）查詢字段

我們在項目中查詢最常用的是返回整個 DO 層對象，但其實很多時候我們只需要其中幾個字段甚至一個字段，這個時候查詢整個對象是很不劃算的，比如下面這個例子，該語句在數(shù)據(jù)庫管理器中的執(zhí)行結(jié)果如下，執(zhí)行時間為9269ms。

select * from a where id = 0;

然而我們只是想查詢指定活動下的單個字段，這個時候可以不需要返回其他字段，只返回需要的字段，優(yōu)化后示例如下，執(zhí)行時間為4104ms，明顯執(zhí)行時間變短了。

select result from a where id = 0;

（2）索引問題

索引在數(shù)據(jù)庫查詢中是一個很重要的影響因素，走不到索引和走到索引是有很大的區(qū)別的。但是索引有弊也有利：

優(yōu)點

• 提高查詢語句的執(zhí)行效率，減少 IO 操作的次數(shù)
• 創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性
• 加了索引的列會進(jìn)行排序，在使用分組和排序子句進(jìn)行查詢時，可以顯著減少查詢中分組和排序的時間

缺點

• 索引需要占物理空間
• 創(chuàng)建索引和維護(hù)索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加
• 當(dāng)對表中的數(shù)據(jù)進(jìn)行增刪改查時，索引也要動態(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的更新效率
• 所以合理的設(shè)置索引并利用索引是高效執(zhí)行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 '結(jié)果測試字段',
PRIMARY KEY (`id`),
KEY `test` (`test`),
) COMMENT='記錄表';

這是活動平臺系統(tǒng)紀(jì)錄的數(shù)據(jù)表，其中【result】是唯一的，這里可以看到索引沒有涉及【result】，如果我們根據(jù)這個字段去查詢唯一記錄，看下執(zhí)行結(jié)果如何：

耗時4286ms，這里根據(jù)建表語句可以看到有索引【KEY test (test)】，如果我們根據(jù)索引字段來查詢該條記錄的話，看下執(zhí)行結(jié)果如何：

可以看到只需要16ms，索引對數(shù)據(jù)庫效率的提升至關(guān)重要。我們可以根據(jù) explain 關(guān)鍵字來解析一下 SQL 語句，比如剛才的案例，我們來看下相應(yīng)的結(jié)果：

可以看到，直接根據(jù)非索引字段【result】查詢，type 字段值為【ALL】，代表遍歷全表，掃描行數(shù)為3144273，如果帶上索引字段【test】，type 字段值為【ref】，代表匹配到單行記錄值，掃描行數(shù)為1，顯然通過索引查詢效率得到了極大的提升。對于索引的使用會出現(xiàn)以下幾種常見類型：

ALL：全表掃描，表示 MySQL 將遍歷整個表以滿足查詢條件。這通常是效率最低的訪問類型，應(yīng)盡量避免。

index：索引全掃描，表示 MySQL 將遍歷整個索引以滿足查詢條件，而不是遍歷整個表。雖然比全表掃描效率要高，但仍然需要遍歷索引的每一行。

range：范圍掃描，表示 MySQL 使用了索引的一部分來滿足查詢條件，例如使用了索引的某個范圍。這通常發(fā)生在有范圍查詢條件時，例如使用了 WHERE 子句中的 BETWEEN、>、< 等操作符。

index_merge：索引合并，表示 MySQL 使用了多個索引來滿足查詢條件，然后將結(jié)果合并。這通常發(fā)生在查詢中有多個條件，每個條件可以使用不同的索引來訪問數(shù)據(jù)。

unique_subquery：唯一子查詢，表示 MySQL 使用了子查詢來獲取唯一的結(jié)果，并且子查詢使用了唯一索引。

const：常量，表示 MySQL 使用了常量表來獲取結(jié)果，這通常發(fā)生在查詢條件中包含了常量值。

ref：引用，表示 MySQL 使用了非唯一索引來掃描表，通常發(fā)生在查詢中使用了單個索引列作為條件。

（3）聯(lián)表查詢

【JOIN】關(guān)鍵詞在項目的日常查詢中可能會遇到，對于一些管理臺項目可能要求較低，可以作為日常開發(fā)查詢語句，但是對于面向用戶的項目，由于數(shù)據(jù)量較大，往往需要避免聯(lián)合查詢的使用。以下面這個案例來分析：

select * from a left join b on a.id = b.id where a.id = 0;

這個SQL是聯(lián)合多張表進(jìn)行聯(lián)合查詢，還有其他查詢條件，執(zhí)行得出結(jié)果時間為1432ms，超過1秒則為慢 SQL，這里聯(lián)合兩張表，如果相關(guān)的數(shù)據(jù)量較大，則執(zhí)行速度會較慢。我們可以將 SQL 拆分為兩個語句執(zhí)行，拆為以下兩個 SQL 分步執(zhí)行：

select * from a where id = 0;
select * from b where id = 0;

分步獲取數(shù)據(jù)庫結(jié)果后再進(jìn)行聚合，分步執(zhí)行結(jié)果分別為728ms和744ms，聯(lián)合查詢拆分為簡單查詢可以有效減少慢 SQL，同時可以提高SQL查詢的復(fù)用性。

（4）條件查詢

在項目的日常開發(fā)中，SQL 語句切忌使用復(fù)雜查詢，這會對數(shù)據(jù)庫造成較大的壓力。下面這個例子就是使用了比較復(fù)雜的條件查詢：

select * from a where id in (select id from b) and time > '2024-03-29';

使用查詢條件的同時還嵌套了查詢語句，除此之外還夾雜了聯(lián)合查詢，語句已經(jīng)比較復(fù)雜，我們來看下查詢結(jié)果：

可以看到執(zhí)行時間非常緩慢，達(dá)到12648ms，這個SQL的背景是用于業(yè)務(wù)對賬，雖然使用的是離線數(shù)據(jù)庫，但是仍然不能忽視它的風(fēng)險，每天大量的執(zhí)行這種復(fù)雜 SQL，還是對業(yè)務(wù)有一定的風(fēng)險影響?？紤]到業(yè)務(wù)價值單一，復(fù)用性不高，我們可以直接冗余一份數(shù)據(jù)到單獨的表里，避免復(fù)雜查詢，直接一步到位解決對賬帶來的慢 SQL 問題。

3. 整體策略

清理完數(shù)據(jù)庫的無效數(shù)據(jù)，優(yōu)化完 SQL 語句本身，可能還是會出現(xiàn)慢 SQL 問題，這個時候我們要考慮下，是否可以優(yōu)化整體的數(shù)據(jù)庫交互策略，以活動中臺系統(tǒng)的數(shù)據(jù)清理慢 SQL 為例。

活動中臺系統(tǒng)創(chuàng)建了一個定時任務(wù)，每天凌晨執(zhí)行一次，對數(shù)據(jù)庫的無效歷史數(shù)據(jù)進(jìn)行統(tǒng)一清理，具體刪除哪些庫表、什么時間段、什么條件都由配置項靈活控制，配置項示例如下：

"分表數(shù)量"：7,
"表名"：table,
"條件"：condition

包括分表的數(shù)量、要刪除數(shù)據(jù)的表名、查詢的條件信息，查詢條件對應(yīng)SQL語句中的【where】信息。執(zhí)行流程如下圖：

整體上看刪除策略很通用，條件配置靈活，可以同時應(yīng)對不同分表、不同查詢條件等。但是線上運(yùn)行發(fā)現(xiàn)會產(chǎn)生大量的慢SQL，可能每天就會產(chǎn)生幾千條。

主要原因如下：

• 會出現(xiàn)聯(lián)表查詢的情況，數(shù)據(jù)量較大的表會出現(xiàn)執(zhí)行時間超過一秒的 SQL 語句；
• 刪除策略中時間是重要的因素，但是時間往往不會設(shè)置為索引字段，所以很難充分利用索引；
• 刪除策略會掃描所有分表，但是很多分表可能全部掃描也沒有需要刪除的數(shù)據(jù)，會出現(xiàn)無效執(zhí)行的情況。

針對以上策略，我們進(jìn)行了綜合改進(jìn)，主要措施有以下幾條：

• 避免聯(lián)表查詢，盡可能的拆分為簡單 SQL 執(zhí)行；
• 不要從時間維度直接出發(fā)，而是從活動維度出發(fā)，如果一個活動結(jié)束時間較長，那么這個活動相關(guān)的數(shù)據(jù)自然不再需要，可以直接刪除；
• 利用分表路由信息，從活動本身出發(fā)快速路由到相關(guān)分表，減少 SQL 語句的無效執(zhí)行。

修改完的配置項如下：

"分表數(shù)量"：7,
"表名"：table,
"條件"：condition,
"刪除數(shù)量"：1,
"刪除策略":1,
"開始時間":"",
"結(jié)束時間":""

包括分表的數(shù)量、要刪除數(shù)據(jù)的表名、查詢條件、每次刪除的數(shù)量、開始時間和結(jié)束時間，在這個時間范圍內(nèi)的活動都符合條件，整體上看刪除策略更為通用，條件配置更加靈活。優(yōu)化的刪除策略流程圖如下：

從圖中可以看到，我們保留原有的刪除策略，避免個別數(shù)據(jù)表沒有活動信息和分表信息，只能單純根據(jù)時間掃描刪除；除此之外，對于有活動信息的數(shù)據(jù)，我們會先查詢活動，根據(jù)活動去刪除，如果數(shù)據(jù)表是根據(jù)活動進(jìn)行分表，則直接查詢路由信息，并刪除指定分表的數(shù)據(jù)即可，也就是圖中的第三種策略，線上大部分的數(shù)據(jù)都會按照第三種策略執(zhí)行，如果有活動信息但是未按照活動進(jìn)行分表，可能按照用戶等維度進(jìn)行分表，則可以根據(jù)活動信息進(jìn)行刪除，也可以有效避免慢 SQL 的出現(xiàn)。

3.2 治理效果

經(jīng)過對慢 SQL 的專項治理，活動中臺系統(tǒng)的慢 SQL 數(shù)量由幾千個，穩(wěn)定在了兩位數(shù)，有效減少了慢SQL的數(shù)量，進(jìn)一步提升了系統(tǒng)穩(wěn)定性。

四、經(jīng)驗總結(jié)

• 治理慢 SQL 的根本是從源頭避免慢 SQL，項目組內(nèi)部必須達(dá)成高度一致，根據(jù)編碼規(guī)范進(jìn)行前置避免慢 SQL 的出現(xiàn)。
• 離線數(shù)據(jù)庫不能成為忽視慢 SQL 問題的原因，仍然會有影響線上業(yè)務(wù)的風(fēng)險，數(shù)據(jù)庫實例如果是混合部署的方式，可能離線庫所在的機(jī)器有其他業(yè)務(wù)的主庫，而且如果從庫延遲嚴(yán)重會影響主從故障切換。
• 數(shù)據(jù)庫的合理設(shè)計不能依賴后期重構(gòu)，一開始就要盡可能的考慮充分。
• 慢 SQL 治理過程中出現(xiàn)的問題可以及時復(fù)盤，避免團(tuán)隊其他成員繼續(xù)踩坑。