前言

閑魚服務(wù)端在做數(shù)據(jù)庫查詢時(shí)，對每一條SQL都需要仔細(xì)優(yōu)化，盡可能使延時(shí)更低，帶給用戶更好的體驗(yàn)。但是在生產(chǎn)中偶爾會有一些情況怎么優(yōu)化都無法滿足業(yè)務(wù)場景。本文通過對一條慢SQL的真實(shí)改造，介紹解決復(fù)雜查詢的一種思路，以及如何使得一條平均RT接近2s的SQL，最終耗時(shí)下降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操作，卻在項(xiàng)目中引起了慢SQL。在大數(shù)據(jù)量和高QPS的情況下，這條SQL的平均查詢RT已經(jīng)接近2s，并且此查詢還應(yīng)用在很多關(guān)鍵性的用戶場景下，已經(jīng)到了無法容忍的程度，需要對其進(jìn)行改造。

分析

索引

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

MySQL的索引采用的是B+樹，需要符合最左前綴匹配，分析SQL可以看出，由于存在多條in條件，雖然userid和status走了索引，但是status的范圍查找導(dǎo)致之后的索引失效。通過Using index condition也可以看出走了索引條件下推，只使用了部分索引，而Using filesort可以看出使用了文件排序，而沒有使用索引排序，查詢速度自然很慢。想從索引的角度是無法解決這條慢SQL的。

分庫分表

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

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

選型

搜索引擎

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

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

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

OLAP(AnalyticDB MySQL)

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

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

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

結(jié)論：對于當(dāng)前SQL，通過將查詢數(shù)據(jù)源改為ADB，替代直接讀取MySQL，可以有效提高查詢速度同時(shí)減少M(fèi)ySQL的讀壓力。

數(shù)據(jù)同步

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

雙寫

想保持?jǐn)?shù)據(jù)庫一致，可以在寫入MySQL之后，再寫入ADB中。

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

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

DTS

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

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

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

缺點(diǎn)：由于項(xiàng)目原因，不支持使用DTS，故沒有采用

監(jiān)聽binlog

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

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

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

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

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

數(shù)據(jù)同步

慢SQL

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

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

可是，整體來看，依然和預(yù)期有一些差距。考慮進(jìn)一步優(yōu)化。

• 建表優(yōu)化

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

前期建表時(shí)，已經(jīng)設(shè)置userid做為聚集列。

執(zhí)行計(jì)劃

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

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

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

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

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

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

什么時(shí)候索引會不高效呢？

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

• 要如何解決呢？

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

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

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

• 結(jié)果

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

添加之后如下圖所示，耗時(shí)在1s以內(nèi)的已經(jīng)達(dá)到了99.15%，且平均耗時(shí)也再次降低了一半。

• 參數(shù)優(yōu)化

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

再次咨詢ADB官方答疑，經(jīng)過排查，幫忙調(diào)小了block_size相關(guān)參數(shù)，減少撈取過多數(shù)據(jù)進(jìn)行掃描。

在ADB進(jìn)行數(shù)據(jù)讀取時(shí)，每次是按照block_size大小進(jìn)行讀入內(nèi)存進(jìn)行計(jì)算的，如果設(shè)置的過大，則可能會導(dǎo)致掃描的無用數(shù)據(jù)行數(shù)過多，從而耗費(fèi)時(shí)間。

對于參數(shù)的設(shè)置，官方不建議自行修改，而是讓其代為分析調(diào)試，以降低發(fā)生風(fēng)險(xiǎn)。

經(jīng)過這次優(yōu)化，效果還是很明顯的，執(zhí)行耗時(shí)在0.5秒以內(nèi)的占了99.67%，而耗時(shí)1s以內(nèi)的，已經(jīng)占比高達(dá)99.94%，符合預(yù)期目標(biāo)。

實(shí)時(shí)同步延時(shí)

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

• 提高運(yùn)行內(nèi)存

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

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

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

• 寫入SQL優(yōu)化

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

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

經(jīng)過優(yōu)化后，非高峰期延時(shí)在500ms以內(nèi)，高峰期最高延遲不到20s，也符合預(yù)期。

總結(jié)

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

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

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

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

最新評論