閒魚技術 - 一條慢SQL引發的改造－鑽石舞台

前言

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

背景

先來看一條SQL

select id,userid,itemid,status,type,modifiedtime ···from table1where 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，也符合預期。