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

一、系統介紹和問題描述

如何在 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 的查詢優化器會幫助優化成索引可以識別的形式。

  1. 儘量選擇區分度高德列作爲索引，區分度公式 count(distinct col)/count(*)，表示字段不重複的比例。

  2. 索引列不能使用函數或參與計算，不能進行類型轉換，否則索引會失效。

  3. 儘量擴展索引，不要新建索引。

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

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

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/giw4tqGRlpQAW3ZF2rVQWg

猜你喜歡