千萬級數據深分頁查詢SQL性能優化實踐

来源:https://www.cnblogs.com/jingdongkeji/archive/2023/08/23/17650823.html
-Advertisement-
Play Games

最近接到了一個新需求,要求提供查詢關註對象的粉絲列表介面功能。該功能的難點就是關註對象的粉絲數量過多,不少店鋪的粉絲數量都是千萬級別,並且有些大V粉絲數量能夠達到上億級別 ...


一、系統介紹和問題描述

如何在Mysql中實現上億數據的遍歷查詢?先來介紹一下系統主角:關註系統,主要是維護京東用戶和業務對象之前的關註關係;並對外提供各種關係查詢,比如查詢用戶的關註商品或店鋪列表,查詢用戶是否關註了某個商品或店鋪等。但是最近接到了一個新需求,要求提供查詢關註對象的粉絲列表介面功能。該功能的難點就是關註對象的粉絲數量過多,不少店鋪的粉絲數量都是千萬級別,並且有些大V粉絲數量能夠達到上億級別。而這些粉絲列表數據目前全都存儲在Mysql庫中,然後通過業務對象ID進行分庫分表,所有的粉絲列表數據分佈在16個分片的256張表中。同時為了方便查詢粉絲列表,同一個業務對象的所有粉絲都會路由到同一張表中,每個表的數據量都能夠達到 2 億+。

二、解決問題的思路和方法

資料庫表結構示例如下:

CREATE TABLE follow_fans_[0-255]
  (
    id bigint(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
    biz_content   VARCHAR(50) DEFAULT NULL COMMENT '業務對象ID',
    source        VARCHAR(50) DEFAULT NULL COMMENT '來源',
    pin           VARCHAR(50) DEFAULT NULL COMMENT '用戶pin',
    ext           VARCHAR(5000) DEFAULT NULL COMMENT '擴展信息',
    status        TINYINT(2) DEFAULT 1 COMMENT '狀態,0是失效,1是正常',
    created_time  DATETIME DEFAULT NULL COMMENT '創建時間',
    modified_time DATETIME DEFAULT NULL COMMENT '修改時間',
    PRIMARY KEY(id),
    UNIQUE INDEX uniq_biz_content_pin (biz_content, pin)
  )
  ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COMMENT = '關註粉絲表';


Limit實現

由於同一個業務對象的所有粉絲都保存到一張資料庫表中,對於分頁查詢列表介面,首先想到的就是用limit實現,對於粉絲數量很少的關註對象,查詢介面性能還不錯。但是隨著關註對象的粉絲數量越來越多,介面查詢性能就會越來越慢。後來經過介面壓測,當業務對象粉絲列表數量達到幾十萬級別的時候,查詢頁碼數量越大,查詢耗時越多。limit深分頁為什麼會變慢?這就和sql的執行計劃有關了,limit語句會先掃描offset+n行,然後再丟棄掉前offset行,返回後n行數據。也就是說limit 100000,10,就會掃描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;


  • 方案優點:實現簡單,支持跳頁查詢。
  • 方案缺點:數據量變大時,隨著查詢頁碼的深入,查詢性能越來越差。

標簽記錄法

Limit深分頁問題的本質原因就是:偏移量(offset)越大,mysql就會掃描越多的行,然後再拋棄掉,這樣就導致查詢性能的下降。所以我們可以採用標簽記錄法,就是標記一下上次查詢到哪一條了,下次再來查的時候,從該條開始往下掃描。具體做法方式是,查詢粉絲列表中按照自增主鍵ID倒序查詢,查詢結果中返回主鍵ID,然後查詢入參中增加maxId參數,該參數需要透傳上一次請求粉絲列表中最後一條記錄主鍵ID,第一次查詢時可以為空,但是需要查詢下一頁時就必傳。最後根據查詢時返回的行數是否等於 10 來判斷整個查詢是否可以結束。優化後的查詢sql參考如下:

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


  • 方案優點:避免了數據量變大時,頁碼查詢深入的性能下降問題;經過介面壓測,千萬級數據量時,前 N-1頁查詢耗時可以控制在幾十毫秒內。
  • 方案缺點:只能支持按照頁碼順序查詢,不支持跳頁,而且僅能保證前 N-1 頁的查詢性能;如果最後一頁的表中行數量不滿 10 條時,引擎不知道何時終止查詢,只能遍歷全表,所以當表中數據量很大時,還是會出現超時情況。

區間限製法

標簽記錄法最後一頁查詢超時就是因為不知道何時終止查詢,所以我們可以提供一個區間限制範圍來告訴引擎查詢到此結束。

查詢sql再次優化後參考如下:

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;


由於查詢時需要帶上 minId 參數,所以在執行查詢粉絲列表之前,我們就需要先把 minId 查詢出來,查詢 sql 參考如下:

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


由於表中數據量太大,每個表中總數據量都是上億級別,導致第一步查詢 minId就直接超時了,根本沒有機會去執行第二步。但是考慮到上一個查詢方案只有最後一頁才會查詢超時,前N-1頁查詢根本用不到 minId 作為區間限制。所以當表中數據量很大時,通常從第一頁到最後一頁查詢之間會存在一定的時間差。我們就可以正好去利用這個時間差去非同步查詢minId,然後將查詢出來的minId存儲到緩存中,考慮到這個 minId 可能會被刪除,可以設置一定的過期時間。最後優化後的查詢流程如下:

  1. 調用查詢粉絲列表方法時首先查詢緩存minId;
  2. 如果緩存minId 為空,則創建非同步任務去執行select min(id) 查詢表中的 minId,然後回寫緩存,該非同步任務執行時間可能會很長,可以單獨設置超時時間。
  3. 如果緩存minId不為空,則在查詢sql中拼接查詢條件id >={minId},從而保證查詢最後一頁時不會超時。

但是在上述方案中,如果表中的數據量達到上億級別時,第二步的非同步獲取minId任務還是會存在超時的風險,從而導致查詢最後一頁粉絲列表出現超時。所以我們又引入了離線數據計算任務,通過在大數據平臺離線計算獲取每個biz_content下的minId,然後將計算結果minId推送到緩存中。為了保證minId能夠及時更新,我們可以自由設置該離線任務的執行周期,比如每周執行一次。通過大數據平臺的離線計算minId,從而大大減少了在查詢粉絲列表時執行 select min(id)的業務資料庫壓力。只有當緩存沒有命中的時候才去執行 select min(id),通常這些緩存沒有命中的 minId 也都是一些被離線任務遺漏的少量數據,不會影響介面的整體查詢性能。

  • 方案優點:避免了數據量變大時,頁碼查詢深入的性能下降問題;經過介面壓測,千萬級數據量時,從第一頁到最後一頁都控制在幾十毫秒內。
  • 方案缺點:只能支持按照頁碼順序和主鍵ID倒序查詢,不支持跳頁查詢,並且還需要依賴大數據平臺離線計算和額外的緩存來存儲 minId。

三、對SQL優化治理的思考

通過對以上三種方案的探索實踐,發現每一種方案都有自己的優缺點和它的適用場景,我們不能脫離實際業務場景去談方案的好壞。所以我們要結合實際的業務環境以及表中數據量的大小去綜合考慮、權衡利弊,然後找到更適合的技術方案。以下是總結的幾條SQL優化建議:

查詢條件一定要有索引

索引主要分為兩大類,聚簇索引和非聚簇索引,可以通過 explain 查看 sql 執行計劃判斷查詢是否使用了索引。

聚簇索引 (clustered index):聚簇索引的葉子節點存儲行記錄,InnoDB必須要有且只有一個聚簇索引:

  1. 如果表定義了主鍵,則主鍵索引就是聚簇索引;
  2. 如果沒有定義主鍵,則第一個非空的唯一索引列是聚簇索引;
  3. 如果沒有唯一索引,則創建一個隱藏的row-id列作為聚簇索引。主鍵索引查詢非常快,可以直接定位行記錄。

非聚簇索引 (secondary index):InnoDB非聚簇索引的葉子節點存儲的是行記錄的主鍵值,而MyISAM葉子節點存儲的是行指針。 通常情況下,需要先遍歷非聚簇索引獲得聚簇索引的主鍵ID,然後在遍歷聚簇索引獲取對應行記錄。

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

可以參考以下幾點索引原則:

  1. 最左首碼匹配原則,mysql會一直向右匹配直到遇到範圍查詢(>、<、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的順序可以任意調整。
  2. =和in可以亂序,比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意順序,mysql的查詢優化器會幫助優化成索引可以識別的形式。
  3. 儘量選擇區分度高德列作為索引,區分度公式count(distinct col)/count(*),表示欄位不重覆的比例。
  4. 索引列不能使用函數或參與計算,不能進行類型轉換,否則索引會失效。
  5. 儘量擴展索引,不要新建索引。

減少查詢欄位,避免回表查詢

回表查詢就是先定位主鍵值,在根據主鍵值定位行記錄,需要掃描兩遍索引。 解決方案:只需要在一顆索引樹上能夠獲取SQL所需要的所有列數據,則無需回表查詢,速度更快。可以將要查詢的欄位,建立到聯合索引里去,這就是索引覆蓋。查詢sql在進行explain解析時,Extra欄位為Using Index時,則觸發索引覆蓋。沒有觸發索引覆蓋,發生了回表查詢時,Extra欄位為Using Index condition。

作者:京東零售 曹志飛

來源:京東雲開發者社區 轉載請註明來源


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • [toc] ### 1.文件操作 #### 1.1 創建文件 ``` shell # touch+文件名 # 例子: # 創建一個文件 touch hello.c # 創建多個文件 touch hello.c hi.c ``` #### 1.2 刪除文件 ``` shell # rm+文件名 # 例 ...
  • 因為我用的是windows伺服器,因此需要一臺虛擬機,用來安裝centos,虛擬機的安裝網上好多教程,這裡不做過多介紹 這次同樣是按步操作 在本地伺服器創建下載目錄 -> 將yum文件下載到本地 -> 在遠程伺服器上創建目錄 -> 上傳文件到遠程伺服器目錄 -> 使用命令安裝yum到伺服器上 這次的 ...
  • # 任務與協程 ## 區別 > 一個程式可以只有任務、只有協程、二者都有,但不可以通過隊列/信號量互相傳遞數據 ## 任務特點 1. 任務之間可以互相獨立 2. 每個任務分配自己的堆棧,提高了RAM使用率 3. 操作簡單、按優先順序搶占式執行 4. 搶占容易導致重入(執行任務時被其他線程或進程調用了) ...
  • ![](https://img2023.cnblogs.com/blog/3076680/202308/3076680-20230822115524099-438612716.png) # 1. 在執行語句之前,會先檢查下列事項 ## 1.1. 是否有許可權執行該語句 ## 1.2. 是否有許可權訪問指 ...
  • 排序查詢:select 欄位列表 from [表名] order by [欄位名1] [asc升序/desc降序,預設值為升序],[欄位名2] [排序方式];//欄位名1為優先順序排序,如果欄位名1有相同的,再以欄位名2排序 聚合函數: count 統計數量(一般不選null的列) max 最大值 m ...
  • ## 1、字元集概述 - Oracle語言環境的描述包括三部分:language、territory、characterset(語言、地域、字元集) - language:主要指定伺服器消息的語言,提示信息顯示中文還是英文 - territory:主要指定伺服器的數字和日期的格式 - charact ...
  • 在日常工作中,有時會遇到一次性往頁面中插入大量數據的場景,在數棧的[離線開發](https://www.dtstack.com/dtinsight/batchworks?src=szsm)(以下簡稱離線)產品中,就有類似的場景。本文將通過分享一個實際場景中的[前端開發](https://www.dt ...
  • 第14屆中國資料庫技術大會(DTCC2023)上,華為雲資料庫GaussDB首席架構師馮柯對華為雲GaussDB資料庫的高級壓縮技術進行了詳細的解讀。 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...