千萬(wàn)級(jí)數(shù)據(jù)深分頁(yè)查詢(xún)SQL性能優(yōu)化實(shí)踐

2023-08-23 23:18:07 來(lái)源：博客園

一、系統(tǒng)介紹和問(wèn)題描述

如何在Mysql中實(shí)現(xiàn)上億數(shù)據(jù)的遍歷查詢(xún)？先來(lái)介紹一下系統(tǒng)主角：關(guān)注系統(tǒng)，主要是維護(hù)京東用戶和業(yè)務(wù)對(duì)象之前的關(guān)注關(guān)系；并對(duì)外提供各種關(guān)系查詢(xún)，比如查詢(xún)用戶的關(guān)注商品或店鋪列表，查詢(xún)用戶是否關(guān)注了某個(gè)商品或店鋪等。但是最近接到了一個(gè)新需求，要求提供查詢(xún)關(guān)注對(duì)象的粉絲列表接口功能。該功能的難點(diǎn)就是關(guān)注對(duì)象的粉絲數(shù)量過(guò)多，不少店鋪的粉絲數(shù)量都是千萬(wàn)級(jí)別，并且有些大V粉絲數(shù)量能夠達(dá)到上億級(jí)別。而這些粉絲列表數(shù)據(jù)目前全都存儲(chǔ)在Mysql庫(kù)中，然后通過(guò)業(yè)務(wù)對(duì)象ID進(jìn)行分庫(kù)分表，所有的粉絲列表數(shù)據(jù)分布在16個(gè)分片的256張表中。同時(shí)為了方便查詢(xún)粉絲列表，同一個(gè)業(yè)務(wù)對(duì)象的所有粉絲都會(huì)路由到同一張表中，每個(gè)表的數(shù)據(jù)量都能夠達(dá)到 2 億+。

二、解決問(wèn)題的思路和方法

數(shù)據(jù)庫(kù)表結(jié)構(gòu)示例如下：

(資料圖)

CREATE TABLE follow_fans_[0-255]  (    id bigint(11) NOT NULL AUTO_INCREMENT COMMENT "自增id",    biz_content   VARCHAR(50) DEFAULT NULL COMMENT "業(yè)務(wù)對(duì)象ID",    source        VARCHAR(50) DEFAULT NULL COMMENT "來(lái)源",    pin           VARCHAR(50) DEFAULT NULL COMMENT "用戶pin",    ext           VARCHAR(5000) DEFAULT NULL COMMENT "擴(kuò)展信息",    status        TINYINT(2) DEFAULT 1 COMMENT "狀態(tài)，0是失效，1是正常",    created_time  DATETIME DEFAULT NULL COMMENT "創(chuàng)建時(shí)間",    modified_time DATETIME DEFAULT NULL COMMENT "修改時(shí)間",    PRIMARY KEY(id),    UNIQUE INDEX uniq_biz_content_pin (biz_content, pin)  )  ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COMMENT = "關(guān)注粉絲表";

Limit實(shí)現(xiàn)

由于同一個(gè)業(yè)務(wù)對(duì)象的所有粉絲都保存到一張數(shù)據(jù)庫(kù)表中，對(duì)于分頁(yè)查詢(xún)列表接口，首先想到的就是用limit實(shí)現(xiàn)，對(duì)于粉絲數(shù)量很少的關(guān)注對(duì)象，查詢(xún)接口性能還不錯(cuò)。但是隨著關(guān)注對(duì)象的粉絲數(shù)量越來(lái)越多，接口查詢(xún)性能就會(huì)越來(lái)越慢。后來(lái)經(jīng)過(guò)接口壓測(cè)，當(dāng)業(yè)務(wù)對(duì)象粉絲列表數(shù)量達(dá)到幾十萬(wàn)級(jí)別的時(shí)候，查詢(xún)頁(yè)碼數(shù)量越大，查詢(xún)耗時(shí)越多。limit深分頁(yè)為什么會(huì)變慢？這就和sql的執(zhí)行計(jì)劃有關(guān)了，limit語(yǔ)句會(huì)先掃描offset+n行，然后再丟棄掉前offset行，返回后n行數(shù)據(jù)。也就是說(shuō)limit 100000,10，就會(huì)掃描100010行，而limit 0,10，只掃描10行。查詢(xún) sql 示例如下：

select  id,biz_content,pin FROM follow_fans_1 where biz_content = #{bizContent} order by id desc limit 10, 10;

方案優(yōu)點(diǎn)：實(shí)現(xiàn)簡(jiǎn)單，支持跳頁(yè)查詢(xún)。方案缺點(diǎn)：數(shù)據(jù)量變大時(shí)，隨著查詢(xún)頁(yè)碼的深入，查詢(xún)性能越來(lái)越差。標(biāo)簽記錄法

Limit深分頁(yè)問(wèn)題的本質(zhì)原因就是：偏移量（offset）越大，mysql就會(huì)掃描越多的行，然后再拋棄掉，這樣就導(dǎo)致查詢(xún)性能的下降。所以我們可以采用標(biāo)簽記錄法，就是標(biāo)記一下上次查詢(xún)到哪一條了，下次再來(lái)查的時(shí)候，從該條開(kāi)始往下掃描。具體做法方式是，查詢(xún)粉絲列表中按照自增主鍵ID倒序查詢(xún)，查詢(xún)結(jié)果中返回主鍵ID，然后查詢(xún)?nèi)雲(yún)⒅性黾觤axId參數(shù)，該參數(shù)需要透?jìng)魃弦淮握?qǐng)求粉絲列表中最后一條記錄主鍵ID，第一次查詢(xún)時(shí)可以為空，但是需要查詢(xún)下一頁(yè)時(shí)就必傳。最后根據(jù)查詢(xún)時(shí)返回的行數(shù)是否等于 10 來(lái)判斷整個(gè)查詢(xún)是否可以結(jié)束。優(yōu)化后的查詢(xún)sql參考如下：

select id,biz_content,pin FROM follow_fans_1 where biz_content = #{bizContent} and id < #{lastId} order by id desc limit 10;

方案優(yōu)點(diǎn)：避免了數(shù)據(jù)量變大時(shí)，頁(yè)碼查詢(xún)深入的性能下降問(wèn)題；經(jīng)過(guò)接口壓測(cè)，千萬(wàn)級(jí)數(shù)據(jù)量時(shí)，前 N-1頁(yè)查詢(xún)耗時(shí)可以控制在幾十毫秒內(nèi)。方案缺點(diǎn)：只能支持按照頁(yè)碼順序查詢(xún)，不支持跳頁(yè)，而且僅能保證前 N-1 頁(yè)的查詢(xún)性能；如果最后一頁(yè)的表中行數(shù)量不滿 10 條時(shí)，引擎不知道何時(shí)終止查詢(xún)，只能遍歷全表，所以當(dāng)表中數(shù)據(jù)量很大時(shí)，還是會(huì)出現(xiàn)超時(shí)情況。區(qū)間限制法

標(biāo)簽記錄法最后一頁(yè)查詢(xún)超時(shí)就是因?yàn)椴恢篮螘r(shí)終止查詢(xún)，所以我們可以提供一個(gè)區(qū)間限制范圍來(lái)告訴引擎查詢(xún)到此結(jié)束。

查詢(xún)sql再次優(yōu)化后參考如下：

select id,biz_content,pin FROM follow_fans_1 where biz_content = #{bizContent} and id < #{lastId} and id >={minId} order by id desc limit 10;

由于查詢(xún)時(shí)需要帶上 minId 參數(shù)，所以在執(zhí)行查詢(xún)粉絲列表之前，我們就需要先把 minId 查詢(xún)出來(lái)，查詢(xún) sql 參考如下：

select min(id) from follow_fans_1 where biz_content = #{bizContent}

由于表中數(shù)據(jù)量太大，每個(gè)表中總數(shù)據(jù)量都是上億級(jí)別，導(dǎo)致第一步查詢(xún) minId就直接超時(shí)了，根本沒(méi)有機(jī)會(huì)去執(zhí)行第二步。但是考慮到上一個(gè)查詢(xún)方案只有最后一頁(yè)才會(huì)查詢(xún)超時(shí)，前N-1頁(yè)查詢(xún)根本用不到 minId 作為區(qū)間限制。所以當(dāng)表中數(shù)據(jù)量很大時(shí)，通常從第一頁(yè)到最后一頁(yè)查詢(xún)之間會(huì)存在一定的時(shí)間差。我們就可以正好去利用這個(gè)時(shí)間差去異步查詢(xún)minId，然后將查詢(xún)出來(lái)的minId存儲(chǔ)到緩存中，考慮到這個(gè) minId 可能會(huì)被刪除，可以設(shè)置一定的過(guò)期時(shí)間。最后優(yōu)化后的查詢(xún)流程如下：

調(diào)用查詢(xún)粉絲列表方法時(shí)首先查詢(xún)緩存minId；如果緩存minId 為空，則創(chuàng)建異步任務(wù)去執(zhí)行select min(id) 查詢(xún)表中的 minId,然后回寫(xiě)緩存，該異步任務(wù)執(zhí)行時(shí)間可能會(huì)很長(zhǎng)，可以單獨(dú)設(shè)置超時(shí)時(shí)間。如果緩存minId不為空，則在查詢(xún)sql中拼接查詢(xún)條件id >={minId}，從而保證查詢(xún)最后一頁(yè)時(shí)不會(huì)超時(shí)。

但是在上述方案中，如果表中的數(shù)據(jù)量達(dá)到上億級(jí)別時(shí)，第二步的異步獲取minId任務(wù)還是會(huì)存在超時(shí)的風(fēng)險(xiǎn)，從而導(dǎo)致查詢(xún)最后一頁(yè)粉絲列表出現(xiàn)超時(shí)。所以我們又引入了離線數(shù)據(jù)計(jì)算任務(wù)，通過(guò)在大數(shù)據(jù)平臺(tái)離線計(jì)算獲取每個(gè)biz_content下的minId,然后將計(jì)算結(jié)果minId推送到緩存中。為了保證minId能夠及時(shí)更新，我們可以自由設(shè)置該離線任務(wù)的執(zhí)行周期，比如每周執(zhí)行一次。通過(guò)大數(shù)據(jù)平臺(tái)的離線計(jì)算minId，從而大大減少了在查詢(xún)粉絲列表時(shí)執(zhí)行 select min(id）的業(yè)務(wù)數(shù)據(jù)庫(kù)壓力。只有當(dāng)緩存沒(méi)有命中的時(shí)候才去執(zhí)行 select min(id)，通常這些緩存沒(méi)有命中的 minId 也都是一些被離線任務(wù)遺漏的少量數(shù)據(jù)，不會(huì)影響接口的整體查詢(xún)性能。

方案優(yōu)點(diǎn)：避免了數(shù)據(jù)量變大時(shí)，頁(yè)碼查詢(xún)深入的性能下降問(wèn)題；經(jīng)過(guò)接口壓測(cè)，千萬(wàn)級(jí)數(shù)據(jù)量時(shí)，從第一頁(yè)到最后一頁(yè)都控制在幾十毫秒內(nèi)。方案缺點(diǎn)：只能支持按照頁(yè)碼順序和主鍵ID倒序查詢(xún)，不支持跳頁(yè)查詢(xún)，并且還需要依賴(lài)大數(shù)據(jù)平臺(tái)離線計(jì)算和額外的緩存來(lái)存儲(chǔ) minId。三、對(duì)SQL優(yōu)化治理的思考

通過(guò)對(duì)以上三種方案的探索實(shí)踐，發(fā)現(xiàn)每一種方案都有自己的優(yōu)缺點(diǎn)和它的適用場(chǎng)景，我們不能脫離實(shí)際業(yè)務(wù)場(chǎng)景去談方案的好壞。所以我們要結(jié)合實(shí)際的業(yè)務(wù)環(huán)境以及表中數(shù)據(jù)量的大小去綜合考慮、權(quán)衡利弊，然后找到更適合的技術(shù)方案。以下是總結(jié)的幾條SQL優(yōu)化建議：

查詢(xún)條件一定要有索引

索引主要分為兩大類(lèi)，聚簇索引和非聚簇索引，可以通過(guò) explain 查看 sql 執(zhí)行計(jì)劃判斷查詢(xún)是否使用了索引。

聚簇索引 (clustered index)：聚簇索引的葉子節(jié)點(diǎn)存儲(chǔ)行記錄，InnoDB必須要有且只有一個(gè)聚簇索引：

如果表定義了主鍵，則主鍵索引就是聚簇索引；如果沒(méi)有定義主鍵，則第一個(gè)非空的唯一索引列是聚簇索引；如果沒(méi)有唯一索引，則創(chuàng)建一個(gè)隱藏的row-id列作為聚簇索引。主鍵索引查詢(xún)非常快，可以直接定位行記錄。

非聚簇索引（secondary index)：InnoDB非聚簇索引的葉子節(jié)點(diǎn)存儲(chǔ)的是行記錄的主鍵值，而MyISAM葉子節(jié)點(diǎn)存儲(chǔ)的是行指針。通常情況下，需要先遍歷非聚簇索引獲得聚簇索引的主鍵ID，然后在遍歷聚簇索引獲取對(duì)應(yīng)行記錄。

正確使用索引，防止索引失效

可以參考以下幾點(diǎn)索引原則：

最左前綴匹配原則，mysql會(huì)一直向右匹配直到遇到范圍查詢(xún)（>、<、between、like）就停止匹配，比如 a=1 and b=2 and c>3 and d=4 ，如果建立了（a,b,c,d)順序的索引，d是用不到索引的，如果建立（a,b,d,c)的索引則都可以用到，a、b、d的順序可以任意調(diào)整。=和in可以亂序，比如 a=1 and b=2 and c=3 建立（a,b,c)索引可以任意順序，mysql的查詢(xún)優(yōu)化器會(huì)幫助優(yōu)化成索引可以識(shí)別的形式。盡量選擇區(qū)分度高德列作為索引，區(qū)分度公式count(distinct col)/count(*)，表示字段不重復(fù)的比例。索引列不能使用函數(shù)或參與計(jì)算，不能進(jìn)行類(lèi)型轉(zhuǎn)換，否則索引會(huì)失效。盡量擴(kuò)展索引，不要新建索引。減少查詢(xún)字段，避免回表查詢(xún)

回表查詢(xún)就是先定位主鍵值，在根據(jù)主鍵值定位行記錄，需要掃描兩遍索引。解決方案：只需要在一顆索引樹(shù)上能夠獲取SQL所需要的所有列數(shù)據(jù)，則無(wú)需回表查詢(xún)，速度更快。可以將要查詢(xún)的字段，建立到聯(lián)合索引里去，這就是索引覆蓋。查詢(xún)sql在進(jìn)行explain解析時(shí)，Extra字段為Using Index時(shí)，則觸發(fā)索引覆蓋。沒(méi)有觸發(fā)索引覆蓋，發(fā)生了回表查詢(xún)時(shí)，Extra字段為Using Index condition。