前言

閑魚服務端在做數據庫查詢時，對每一條SQL都需要仔細優(yōu)化，盡可能使延時更低，帶給用戶更好的體驗。但是在生產中偶爾會有一些情況怎么優(yōu)化都無法滿足業(yè)務場景。本文通過對一條慢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的。

分庫分表

阿里巴巴開發(fā)規(guī)約中提過，單表行數超過500萬行或者單表容量超過2GB，推薦進行分庫分表。通常單表數據量如果過大，數據庫性能也會下降。對于億級數據量的表，單表將不足以支撐業(yè)務，需要采用分庫分表的方式來提升性能，此處也已經對userid取模進行了水平拆分，并不是問題所在。

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

選型

搜索引擎

對于復雜的數據查詢，很容易想到通過搜索引擎進行查找。搜索引擎的數據分析即使面對多種復雜條件，也能達到毫秒級別的召回，穩(wěn)定，可靠，快速，且門檻低，成本低。最常見的例如Elasticsearch。

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

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

OLAP(AnalyticDB MySQL)

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

云原生數據倉庫AnalyticDB MySQL（簡稱ADB）是云端托管的PB級高并發(fā)實時數據倉庫，專注于服務OLAP領域。采用關系模型進行數據存儲，可以使用SQL進行自由靈活的計算分析，無需預先建模。利用云端的無縫伸縮能力，在處理百億條甚至更多量級的數據時真正實現(xiàn)毫秒級計算。支持高吞吐的數據實時增刪改、低延時的實時分析和復雜ETL，兼容上下游生態(tài)工具，可用于構建企業(yè)級報表系統(tǒng)、數據倉庫和數據服務引擎。

本業(yè)務場景上存在大數據量計算和快速查詢場景，ADB在寫入性能，計算性能都能滿足要求。且ADB能直接兼容MySQL數據庫語法，降低代碼的改造和使用成本。

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

數據同步

選型好之后就是考慮如何將MySQL的數據同步進ADB，并保持數據庫一致性，這里主要考慮增量數據如何同步。這里提供如下三種思路。

雙寫

想保持數據庫一致，可以在寫入MySQL之后，再寫入ADB中。

優(yōu)點：實現(xiàn)簡單，延時低。

缺點：修改的地方多，不符合開閉原則。增加系統(tǒng)復雜度，如果后期有代碼只更新了MySQL而忘記添加寫入ADB的邏輯，則會導致數據庫不一致。同步寫入增加耗時，同時，如果更新ADB出錯時，也很難進行異常處理。

DTS

阿里云數據傳輸（Data Transmission）DTS的數據同步功能旨在幫助用戶實現(xiàn)兩個數據源之間的數據實時同步。數據同步功能可應用于異地多活、數據異地災備、本地數據災備、數據異地多活、跨境數據同步、查詢與報表分流、云BI及實時數據倉庫等多種業(yè)務場景。

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

優(yōu)點：穩(wěn)定，高效，基本是最合適的解決方案

缺點：由于項目原因，不支持使用DTS，故沒有采用

監(jiān)聽binlog

通過監(jiān)聽MySQL的binlog，可以對數據變更做統(tǒng)一的處理。在此處，可以通過監(jiān)聽新增刪改消息進行對ADB進行寫入操作。

由于ADB全面兼容MySQL語法，所以新增和刪除可以使用如下語法進行統(tǒng)一處理

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

結論：增量數據的同步，最終采用在binlog處做統(tǒng)一收口，通過異步寫入，不會影響用戶體驗，也能可以自定義重試方法，保證同步的可用性。其他項目如果可以，盡量考慮使用DTS。

實時同步處理完成后，可以再進行數據離線同步將存量數據導入，導入時忽略主鍵沖突的數據，導入完成后，新庫就可以正常使用了。ADB支持多種數據導入工具，詳情可以參考用戶文檔。

數據同步

慢SQL

ADB數據同步完成后，立刻開始進行切流了千分之一開始驗證效果，平均執(zhí)行耗時果然有所減小。

平均執(zhí)行耗時僅有100ms，耗時分布統(tǒng)計如下，一秒以內的查詢已經占到了98.31%

可是，整體來看，依然和預期有一些差距?？紤]進一步優(yōu)化。

• 建表優(yōu)化

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

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

執(zhí)行計劃

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

可以看出有兩條優(yōu)化項

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

• 那么ADB的索引是什么樣的呢？

經過了解，OLAP場景下需要支持任意維度查詢，傳統(tǒng)的OLTP單列或組合索引難以滿足該需求。ADB中的玄武采用了自適應列級自動索引技術，針對字符串、數字、文本、JSON、向量等列類型都有自動配置的索引數據結構，并且可以做到列級索引任意維度組合檢索、多路漸進流式歸并，大幅提升了數據過濾性能。

目前索引類型主要有：倒排索引（字符型字段）、BKD-Tree索引（數值型字段）和Bitmap索引。同時索引的性能主要受數據分布特征影響，包括：cardinality（散列程度），范圍查詢的記錄數/表記錄數。

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

查看文檔，我們知道了，ADB會默認對所有列建立索引(可以在建表是選擇對某些列不建索引)。但是有些列由于其區(qū)分度不高，走了索引反而可能不高效。

• 要如何解決呢？

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

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

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

• 結果

在去添加hint語法去除了type和statu索引后，果然有了顯著提升

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

• 參數優(yōu)化

經過上述優(yōu)化完后，依然有0.86%的SQL需要耗時1s以上

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

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

對于參數的設置，官方不建議自行修改，而是讓其代為分析調試，以降低發(fā)生風險。

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

實時同步延時

在某些用戶高峰期，實時同步的寫入隊列出現(xiàn)了大量延時，最多延時高達10分鐘。

• 提高運行內存

查看gc情況，發(fā)現(xiàn)在高峰期，由于數據大量涌入，頻繁出現(xiàn)gc，甚至很多fullGC。

fullGC引起STW，對于系統(tǒng)的延遲會造成很大影響。

在將內存擴大至2048M后，fullGC不再出現(xiàn)，youngGC的頻率也下降了很多。不過延遲依然存在。

• 寫入SQL優(yōu)化

前期對于每條binlog變更消息單獨進行寫入處理，想要讓其速度更快，使用批量操作的方式，將獲取到的消息，拼接成一個list后由mybatis的foreach語法統(tǒng)一拼接成一條SQL后執(zhí)行，減少和數據庫間的交互，同時也將吞吐量提升一個量級。

</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>

經過優(yōu)化后，非高峰期延時在500ms以內，高峰期最高延遲不到20s，也符合預期。

總結

本文通過對一條慢SQL的分析，介紹了如何從架構的角度進行選型來解決問題。之后又從實際使用時的數據同步方式，以及使用過后的問題調優(yōu)，給大家展示了一次改造的完整經過。在實際生產中遇到的問題往往是不同的，這里僅通過介紹使用AnalyticDB的方式進行解決，僅供參考。

事實上，由于MySQL的innodb引擎，同一條SQL即使非常復雜，第一次查詢走數據文件，在第二次查詢時會走buffer_pool，查詢速度通常也會很快，但是對于用戶的首次加載體驗將會非常不友好。

雖然目前引入ADB只解決了一條慢SQL，但可以預見的，對于此表往后仍然會出現(xiàn)很多MySQL無法解決的復雜查詢，通過ADB都可以解決。除了查詢外也可以用于數據分析，在面對海量數據時也能有流暢的體驗。ADB的功能非常強大，這里對其的使用和了解，也僅僅是冰山一角，對其有興趣的讀者可以去官網查看相關文檔。

到此這篇關于一條慢SQL語句引發(fā)的改造的文章就介紹到這了,更多相關慢SQL語句改造內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家

最新評論