0
  • 聊天消息
  • 系統(tǒng)消息
  • 評(píng)論與回復(fù)
登錄后你可以
  • 下載海量資料
  • 學(xué)習(xí)在線課程
  • 觀看技術(shù)視頻
  • 寫(xiě)文章/發(fā)帖/加入社區(qū)
會(huì)員中心
創(chuàng)作中心

完善資料讓更多小伙伴認(rèn)識(shí)你,還能領(lǐng)取20積分哦,立即完善>

3天內(nèi)不再提示

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

京東云 ? 來(lái)源:京東零售 曹志飛 ? 作者:京東零售 曹志飛 ? 2024-10-30 10:49 ? 次閱讀

作者:京東零售 曹志飛

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

如何在Mysql中實(shí)現(xiàn)上億數(shù)據(jù)的遍歷查詢?先來(lái)介紹一下系統(tǒng)主角:關(guān)注系統(tǒng),主要是維護(hù)京東用戶和業(yè)務(wù)對(duì)象之前的關(guān)注關(guān)系;并對(duì)外提供各種關(guān)系查詢,比如查詢用戶的關(guān)注商品或店鋪列表,查詢用戶是否關(guān)注了某個(gè)商品或店鋪等。但是最近接到了一個(gè)新需求,要求提供查詢關(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í)為了方便查詢粉絲列表,同一個(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è)查詢列表接口,首先想到的就是用limit實(shí)現(xiàn),對(duì)于粉絲數(shù)量很少的關(guān)注對(duì)象,查詢接口性能還不錯(cuò)。但是隨著關(guān)注對(duì)象的粉絲數(shù)量越來(lái)越多,接口查詢性能就會(huì)越來(lái)越慢。后來(lái)經(jīng)過(guò)接口壓測(cè),當(dāng)業(yè)務(wù)對(duì)象粉絲列表數(shù)量達(dá)到幾十萬(wàn)級(jí)別的時(shí)候,查詢頁(yè)碼數(shù)量越大,查詢耗時(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行。查詢 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è)查詢。

?方案缺點(diǎn):數(shù)據(jù)量變大時(shí),隨著查詢頁(yè)碼的深入,查詢性能越來(lái)越差。

標(biāo)簽記錄法

Limit深分頁(yè)問(wèn)題的本質(zhì)原因就是:偏移量(offset)越大,mysql就會(huì)掃描越多的行,然后再拋棄掉,這樣就導(dǎo)致查詢性能的下降。所以我們可以采用標(biāo)簽記錄法,就是標(biāo)記一下上次查詢到哪一條了,下次再來(lái)查的時(shí)候,從該條開(kāi)始往下掃描。具體做法方式是,查詢粉絲列表中按照自增主鍵ID倒序查詢,查詢結(jié)果中返回主鍵ID,然后查詢?nèi)雲(yún)⒅性黾觤axId參數(shù),該參數(shù)需要透?jìng)魃弦淮握?qǐng)求粉絲列表中最后一條記錄主鍵ID,第一次查詢時(shí)可以為空,但是需要查詢下一頁(yè)時(shí)就必傳。最后根據(jù)查詢時(shí)返回的行數(shù)是否等于 10 來(lái)判斷整個(gè)查詢是否可以結(jié)束。優(yōu)化后的查詢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è)碼查詢深入的性能下降問(wèn)題;經(jīng)過(guò)接口壓測(cè),千萬(wàn)級(jí)數(shù)據(jù)量時(shí),前 N-1頁(yè)查詢耗時(shí)可以控制在幾十毫秒內(nèi)。

?方案缺點(diǎn):只能支持按照頁(yè)碼順序查詢,不支持跳頁(yè),而且僅能保證前 N-1 頁(yè)的查詢性能;如果最后一頁(yè)的表中行數(shù)量不滿 10 條時(shí),引擎不知道何時(shí)終止查詢,只能遍歷全表,所以當(dāng)表中數(shù)據(jù)量很大時(shí),還是會(huì)出現(xiàn)超時(shí)情況。

區(qū)間限制法

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

查詢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;

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

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

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

1.調(diào)用查詢粉絲列表方法時(shí)首先查詢緩存minId;

2.如果緩存minId 為空,則創(chuàng)建異步任務(wù)去執(zhí)行select min(id) 查詢表中的 minId,然后回寫(xiě)緩存,該異步任務(wù)執(zhí)行時(shí)間可能會(huì)很長(zhǎng),可以單獨(dú)設(shè)置超時(shí)時(shí)間。

3.如果緩存minId不為空,則在查詢sql中拼接查詢條件id >={minId},從而保證查詢最后一頁(yè)時(shí)不會(huì)超時(shí)。

但是在上述方案中,如果表中的數(shù)據(jù)量達(dá)到上億級(jí)別時(shí),第二步的異步獲取minId任務(wù)還是會(huì)存在超時(shí)的風(fēng)險(xiǎn),從而導(dǎo)致查詢最后一頁(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,從而大大減少了在查詢粉絲列表時(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ì)影響接口的整體查詢性能。

?方案優(yōu)點(diǎn):避免了數(shù)據(jù)量變大時(shí),頁(yè)碼查詢深入的性能下降問(wèn)題;經(jīng)過(guò)接口壓測(cè),千萬(wàn)級(jí)數(shù)據(jù)量時(shí),從第一頁(yè)到最后一頁(yè)都控制在幾十毫秒內(nèi)。

?方案缺點(diǎn):只能支持按照頁(yè)碼順序和主鍵ID倒序查詢,不支持跳頁(yè)查詢,并且還需要依賴大數(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)化建議:

查詢條件一定要有索引

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

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

1.如果表定義了主鍵,則主鍵索引就是聚簇索引;

2.如果沒(méi)有定義主鍵,則第一個(gè)非空的唯一索引列是聚簇索引;

3.如果沒(méi)有唯一索引,則創(chuàng)建一個(gè)隱藏的row-id列作為聚簇索引。主鍵索引查詢非??欤梢灾苯佣ㄎ恍杏涗?。

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

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

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

1.最左前綴匹配原則,mysql會(huì)一直向右匹配直到遇到范圍查詢(>、<、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)整。

2.=和in可以亂序,比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意順序,mysql的查詢優(yōu)化器會(huì)幫助優(yōu)化成索引可以識(shí)別的形式。

3.盡量選擇區(qū)分度高德列作為索引,區(qū)分度公式count(distinct col)/count(*),表示字段不重復(fù)的比例。

4.索引列不能使用函數(shù)或參與計(jì)算,不能進(jìn)行類(lèi)型轉(zhuǎn)換,否則索引會(huì)失效。

5.盡量擴(kuò)展索引,不要新建索引。

減少查詢字段,避免回表查詢

回表查詢就是先定位主鍵值,在根據(jù)主鍵值定位行記錄,需要掃描兩遍索引。 解決方案:只需要在一顆索引樹(shù)上能夠獲取SQL所需要的所有列數(shù)據(jù),則無(wú)需回表查詢,速度更快??梢詫⒁樵兊淖侄危⒌铰?lián)合索引里去,這就是索引覆蓋。查詢sql在進(jìn)行explain解析時(shí),Extra字段為Using Index時(shí),則觸發(fā)索引覆蓋。沒(méi)有觸發(fā)索引覆蓋,發(fā)生了回表查詢時(shí),Extra字段為Using Index condition。

審核編輯 黃宇

聲明:本文內(nèi)容及配圖由入駐作者撰寫(xiě)或者入駐合作網(wǎng)站授權(quán)轉(zhuǎn)載。文章觀點(diǎn)僅代表作者本人,不代表電子發(fā)燒友網(wǎng)立場(chǎng)。文章及其配圖僅供工程師學(xué)習(xí)之用,如有內(nèi)容侵權(quán)或者其他違規(guī)問(wèn)題,請(qǐng)聯(lián)系本站處理。 舉報(bào)投訴
  • SQL
    SQL
    +關(guān)注

    關(guān)注

    1

    文章

    753

    瀏覽量

    44002
  • 定位
    +關(guān)注

    關(guān)注

    5

    文章

    1281

    瀏覽量

    35293
收藏 人收藏

    評(píng)論

    相關(guān)推薦

    2017雙11技術(shù)揭秘—TDDL/DRDS 的類(lèi) KV 查詢優(yōu)化實(shí)踐

    摘要: 性能優(yōu)化是企業(yè)級(jí)應(yīng)用永恒的話題,關(guān)系型數(shù)據(jù)庫(kù)查詢優(yōu)化更是如此。在前臺(tái)核心業(yè)務(wù)場(chǎng)景中,類(lèi) KeyValue
    發(fā)表于 12-29 14:29

    JPA分頁(yè)查詢的常用方法

    JPA分頁(yè)查詢與條件分頁(yè)查詢
    發(fā)表于 10-23 17:10

    數(shù)據(jù)庫(kù)設(shè)計(jì)及開(kāi)發(fā)規(guī)范之sql性能優(yōu)化

    數(shù)據(jù)庫(kù)設(shè)計(jì)及開(kāi)發(fā)規(guī)范,sql性能優(yōu)化
    發(fā)表于 05-08 10:58

    基于KingView的SQL數(shù)據(jù)查詢設(shè)計(jì)_楊洋

    基于KingView的SQL數(shù)據(jù)查詢設(shè)計(jì)_楊洋
    發(fā)表于 01-17 19:57 ?0次下載

    SQL優(yōu)化器原理 - 查詢優(yōu)化器綜述

    摘要:?本文主要是對(duì)數(shù)據(jù)庫(kù)查詢優(yōu)化器的一個(gè)綜述,包括查詢優(yōu)化器分類(lèi)、查詢
    發(fā)表于 07-24 17:38 ?302次閱讀
    <b class='flag-5'>SQL</b><b class='flag-5'>優(yōu)化</b>器原理 - <b class='flag-5'>查詢</b><b class='flag-5'>優(yōu)化</b>器綜述

    百億級(jí)數(shù)據(jù)分表后 怎樣才能分頁(yè)查詢

    比如分頁(yè)查詢的問(wèn)題。一般我們把分表的字段稱(chēng)作shardingkey,比如訂單表按照用戶ID作為shardingkey,那么如果查詢條件中不帶用戶ID查詢怎么做
    的頭像 發(fā)表于 11-19 15:43 ?1453次閱讀
    百億<b class='flag-5'>級(jí)數(shù)據(jù)</b>分表后 怎樣才能<b class='flag-5'>分頁(yè)</b><b class='flag-5'>查詢</b>

    SQL查詢優(yōu)化是怎么回事

    查詢 (Subquery)的優(yōu)化一直以來(lái)都是 SQL 查詢優(yōu)化中的難點(diǎn)之一。 關(guān)聯(lián)子查詢的基本
    的頭像 發(fā)表于 02-01 13:55 ?1993次閱讀
    <b class='flag-5'>SQL</b>子<b class='flag-5'>查詢</b><b class='flag-5'>優(yōu)化</b>是怎么回事

    MyBatis流式查詢輕松幫你解決分頁(yè)慢的問(wèn)題

    結(jié)果。流式查詢的好處是能夠降低內(nèi)存使用。 如果沒(méi)有流式查詢,我們想要從數(shù)據(jù)庫(kù)取 1000 萬(wàn)條記錄而又沒(méi)有足夠的內(nèi)存時(shí),就不得不分頁(yè)查詢,而
    的頭像 發(fā)表于 08-04 15:52 ?4133次閱讀

    如何優(yōu)化MySQL百萬(wàn)數(shù)據(jù)分頁(yè)問(wèn)題

    我們?nèi)粘W?b class='flag-5'>分頁(yè)需求時(shí),一般會(huì)用limit實(shí)現(xiàn),但是當(dāng)偏移量特別大的時(shí)候,查詢效率就變得低下。本文將分四個(gè)方案,討論如何優(yōu)化MySQL百萬(wàn)數(shù)據(jù)
    的頭像 發(fā)表于 04-06 15:12 ?1846次閱讀

    sql優(yōu)化常用的幾種方法

    前言 1.慢SQL優(yōu)化思路。 1.1 慢查詢日志記錄慢SQL 1.2 explain查看分析SQL的執(zhí)行計(jì)劃 1.3 profile 分析執(zhí)
    的頭像 發(fā)表于 11-14 15:04 ?3622次閱讀

    一文終結(jié)SQL查詢優(yōu)化

    查詢(Subquery)的優(yōu)化一直以來(lái)都是 SQL 查詢優(yōu)化中的難點(diǎn)之一。關(guān)聯(lián)子查詢的基本執(zhí)行
    的頭像 發(fā)表于 04-28 14:19 ?694次閱讀
    一文終結(jié)<b class='flag-5'>SQL</b>子<b class='flag-5'>查詢</b><b class='flag-5'>優(yōu)化</b>

    sql語(yǔ)句where條件查詢

    的細(xì)節(jié),包括使用的操作符、條件的組合、多張表的查詢、條件的性能優(yōu)化等方面。 首先,讓我們了解一下WHERE子句中常用的操作符。在SQL中,常用的操作符包括: 比較操作符: 等于(=):
    的頭像 發(fā)表于 11-23 11:28 ?1061次閱讀

    oracle執(zhí)行sql查詢語(yǔ)句的步驟是什么

    Oracle數(shù)據(jù)庫(kù)是一種常用的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng),具有強(qiáng)大的SQL查詢功能。Oracle執(zhí)行SQL查詢
    的頭像 發(fā)表于 12-06 10:49 ?859次閱讀

    SQL改寫(xiě)消除相關(guān)子查詢實(shí)踐

    GaussDB (DWS) 根據(jù)子查詢SQL 語(yǔ)句中的位置把子查詢分成了子查詢、子鏈接兩種形式。
    的頭像 發(fā)表于 12-27 09:51 ?420次閱讀

    數(shù)據(jù)從業(yè)者必知必會(huì)的Hive SQL調(diào)優(yōu)技巧

    數(shù)據(jù)從業(yè)者必知必會(huì)的Hive SQL調(diào)優(yōu)技巧 摘要 :在大數(shù)據(jù)領(lǐng)域中,Hive SQL被廣泛應(yīng)用于數(shù)據(jù)倉(cāng)庫(kù)的
    的頭像 發(fā)表于 09-24 13:30 ?127次閱讀