一條慢SQL語句引發的改造之路

2022-03-14 19:02:11

前言

閒魚伺服器端在做資料庫查詢時，對每一條SQL都需要仔細優化，儘可能使延時更低，帶給使用者更好的體驗。但是在生產中偶爾會有一些情況怎麼優化都無法滿足業務場景。本文通過對一條慢SQL的真實改造，介紹解決複雜查詢的一種思路，以及如何使得一條平均RT接近2s的SQL，最終耗時下降30倍。

背景

先來看一條SQL

select id,userid,itemid,status,type,modifiedtime ···
from table1
where userid = 123 
      and status in (0,1,2) 
      and type in ('a','b','c') 
order by status,modifiedtime desc limit 0,20

查詢條件並不多，也不存在join操作，卻在專案中引起了慢SQL。在巨量資料量和高QPS的情況下，這條SQL的平均查詢RT已經接近2s，並且此查詢還應用在很多關鍵性的使用者場景下，已經到了無法容忍的程度，需要對其進行改造。

分析

索引

對於慢SQL，最先想到的就是查詢沒有走索引或索引失效導致的全表掃描。首先用explain對此SQL 進行分析。對於此表，已經建立了index(userid,status,type,modifiedtime)組合索引。

MySQL的索引採用的是B+樹，需要符合最左字首匹配，分析SQL可以看出，由於存在多條in條件，雖然userid和status走了索引，但是status的範圍查詢導致之後的索引失效。通過Using index condition也可以看出走了索引條件下推，只使用了部分索引，而Using filesort可以看出使用了檔案排序，而沒有使用索引排序，查詢速度自然很慢。想從索引的角度是無法解決這條慢SQL的。

分庫分表

阿里巴巴開發規約中提過，單錶行數超過500萬行或者單表容量超過2GB，推薦進行分庫分表。通常單表資料量如果過大，資料庫效能也會下降。對於億級資料量的表，單表將不足以支撐業務，需要採用分庫分表的方式來提升效能，此處也已經對userid取模進行了水平拆分，並不是問題所在。

結論：對於MySQL已經沒有可以優化的地方，只能從架構上的角度進行思考優化方案。

選型

搜尋引擎

對於複雜的資料查詢，很容易想到通過搜尋引擎進行查詢。搜尋引擎的資料分析即使面對多種複雜條件，也能達到毫秒級別的召回，穩定，可靠，快速，且門檻低，成本低。最常見的例如Elasticsearch。

和資料庫裡的B+樹所建立的組合索引不同，搜尋引擎的倒排索引，可以快速查詢符合單個條件的檔案ID，最後通過取交集的方式過濾出符合條件的結果，查詢速度上可以得到滿足。

雖然搜尋引擎可以容納大量的資料，也可以快速的召回，但是在構建索引的速度上確不盡人意，對於短時間大量的資料寫入，想要能在秒級實時存入並構建索引並召回搜尋引擎是無法保證的，可能在分鐘級別的延遲後才能查詢到結果。無法滿足當前場景。

OLAP(AnalyticDB MySQL)

傳統的關係型資料庫如MySQL，一般稱為聯機事務處理（OLTP,On-line Transaction Processing）。聯機分析處理（OLAP,On-line Analytical Processing）又稱為資料倉儲。OLAP專門為海量資料提供高速查詢能力，通常採用列式儲存，在讀取資料時，可以唯讀取指定的列進行過濾篩選，從而減少I/O，同時由於減少了讀取的資料總量，從而使快取中可以容納更多的資料行數，可以對海量的資料進行更快的計算。

雲原生資料倉儲AnalyticDB MySQL（簡稱ADB）是雲端託管的PB級高並行實時資料倉儲，專注於服務OLAP領域。採用關係模型進行資料儲存，可以使用SQL進行自由靈活的計算分析，無需預先建模。利用雲端的無縫伸縮能力，在處理百億條甚至更多量級的資料時真正實現毫秒級計算。支援高吞吐的資料實時增刪改、低延時的實時分析和複雜ETL，相容上下游生態工具，可用於構建企業級報表系統、資料倉儲和資料服務引擎。

本業務場景上存在巨量資料量計算和快速查詢場景，ADB在寫入效能，計算效能都能滿足要求。且ADB能直接相容MySQL資料庫語法，降低程式碼的改造和使用成本。

結論：對於當前SQL，通過將查詢資料來源改為ADB，替代直接讀取MySQL，可以有效提高查詢速度同時減少MySQL的讀壓力。

資料同步

選型好之後就是考慮如何將MySQL的資料同步進ADB，並保持資料庫一致性，這裡主要考慮增量資料如何同步。這裡提供如下三種思路。

雙寫

想保持資料庫一致，可以在寫入MySQL之後，再寫入ADB中。

優點：實現簡單，延時低。

缺點：修改的地方多，不符合開閉原則。增加系統複雜度，如果後期有程式碼只更新了MySQL而忘記新增寫入ADB的邏輯，則會導致資料庫不一致。同步寫入增加耗時，同時，如果更新ADB出錯時，也很難進行例外處理。

DTS

阿里雲資料傳輸（Data Transmission）DTS的資料同步功能旨在幫助使用者實現兩個資料來源之間的資料實時同步。資料同步功能可應用於異地多活、資料異地災備、本地資料災備、資料異地多活、跨境資料同步、查詢與報表分流、雲BI及實時資料倉儲等多種業務場景。

通過資料同步功能，可以將MySQL中的資料同步至ADB中，其中MySQL可以是RDS MySQL、其他雲廠商或線上IDC自建MySQL或者ECS自建MySQL。

優點：穩定，高效，基本是最合適的解決方案

缺點：由於專案原因，不支援使用DTS，故沒有采用

監聽binlog

通過監聽MySQL的binlog，可以對資料變更做統一的處理。在此處，可以通過監聽新增刪改訊息進行對ADB進行寫入操作。

由於ADB全面相容MySQL語法，所以新增和刪除可以使用如下語法進行統一處理

insert into ··· on dumplicate key update ···

結論：增量資料的同步，最終採用在binlog處做統一收口，通過非同步寫入，不會影響使用者體驗，也能可以自定義重試方法，保證同步的可用性。其他專案如果可以，儘量考慮使用DTS。

實時同步處理完成後，可以再進行資料離線同步將存量資料匯入，匯入時忽略主鍵衝突的資料，匯入完成後，新庫就可以正常使用了。ADB支援多種資料匯入工具，詳情可以參考使用者檔案。

資料同步

慢SQL

ADB資料同步完成後，立刻開始進行切流了千分之一開始驗證效果，平均執行耗時果然有所減小。

平均執行耗時僅有100ms，耗時分佈統計如下，一秒以內的查詢已經佔到了98.31%

可是，整體來看，依然和預期有一些差距。考慮進一步優化。

建表優化

聚集列：在ADB中，資料儲存支援按一列或多列進行排序（先按第一列排序，第一列相同情況下使用第二列排序），以保證該列中值相同或相近的資料儲存在磁碟同一位置，這樣的列稱之為聚集列。當以聚集列為查詢條件時，相比未設定聚集列的查詢，SQL語句的存取I/O將減少數百倍。

前期建表時，已經設定userid做為聚集列。

執行計劃

ADB自帶的執行計劃分析工具，可以進行SQL診斷，找了一條耗時長的SQL範例進行分析。

可以看出有兩條優化項

第一條先不考慮，由於各個欄位都需要使用，所以不能刪除，所以從第二條索引過濾不高效進行分析

那麼ADB的索引是什麼樣的呢？

經過了解，OLAP場景下需要支援任意維度查詢，傳統的OLTP單列或組合索引難以滿足該需求。ADB中的玄武採用了自適應列級自動索引技術，針對字串、數位、文字、JSON、向量等列型別都有自動設定的索引資料結構，並且可以做到列級索引任意維度組合檢索、多路漸進流式歸併，大幅提升了資料過濾效能。

目前索引型別主要有：倒排索引（字元型欄位）、BKD-Tree索引（數值型欄位）和Bitmap索引。同時索引的效能主要受資料分佈特徵影響，包括：cardinality（雜湊程度），範圍查詢的記錄數/表記錄數。

什麼時候索引會不高效呢？

檢視檔案，我們知道了，ADB會預設對所有列建立索引(可以在建表是選擇對某些列不建索引)。但是有些列由於其區分度不高，走了索引反而可能不高效。

要如何解決呢？

ADB提供了查詢級別關閉特定欄位的過濾條件下推能力。針對某個查詢，使用Hint關閉某些欄位的過濾條件下推。只對使用了Hint的查詢生效，其他查詢不受影響。

通過在SQL前直接拼接加上如下語句即可。

/*+ filter_not_pushdown_columns=[${database}.${tableName}:${col1Name}|${col2Name}] */

結果

在去新增hint語法去除了type和statu索引後，果然有了顯著提升

新增之後如下圖所示，耗時在1s以內的已經達到了99.15%，且平均耗時也再次降低了一半。

引數優化

經過上述優化完後，依然有0.86%的SQL需要耗時1s以上

再次諮詢ADB官方答疑，經過排查，幫忙調小了block_size相關引數，減少撈取過多資料進行掃描。

在ADB進行資料讀取時，每次是按照block_size大小進行讀入記憶體進行計算的，如果設定的過大，則可能會導致掃描的無用資料行數過多，從而耗費時間。

對於引數的設定，官方不建議自行修改，而是讓其代為分析偵錯，以降低發生風險。

經過這次優化，效果還是很明顯的，執行耗時在0.5秒以內的佔了99.67%，而耗時1s以內的，已經佔比高達99.94%，符合預期目標。

實時同步延時

在某些使用者高峰期，實時同步的寫入佇列出現了大量延時，最多延時高達10分鐘。

提高執行記憶體

檢視gc情況，發現在高峰期，由於資料大量湧入，頻繁出現gc，甚至很多fullGC。

fullGC引起STW，對於系統的延遲會造成很大影響。

在將記憶體擴大至2048M後，fullGC不再出現，youngGC的頻率也下降了很多。不過延遲依然存在。

寫入SQL優化

前期對於每條binlog變更訊息單獨進行寫入處理，想要讓其速度更快，使用批次操作的方式，將獲取到的訊息，拼接成一個list後由mybatis的foreach語法統一拼接成一條SQL後執行，減少和資料庫間的互動，同時也將吞吐量提升一個量級。

</insert>
    insert into table1 (id,itemid,userid,······)
    values
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.id},#{item.itemId},#{item.userId},······
    </foreach>
    on DUPLICATE key update
    item_id=values(item_id),······
</insert>

經過優化後，非高峰期延時在500ms以內，高峰期最高延遲不到20s，也符合預期。