面試官：Mysql千萬級大表如何進行深度分頁優化？

来源:https://www.cnblogs.com/coderacademy/p/17994562
-Advertisement-
Play Games

本文介紹瞭如何通過子查詢優化深度分頁查詢，以減少回表操作帶來的性能損耗。傳統的深度分頁查詢往往會面臨嚴重的性能問題，尤其在處理大量數據時更是如此。 ...


背景

假如有一張千萬級的訂單表，這張表沒有採用分區分表，也沒有使用ES等技術，分頁查詢進行到一定深度分頁之後（比如1000萬行後）查詢比較緩慢，我們該如何進行優化？

數據準備

訂單表結構如下：

CREATE TABLE `t_order` (  
    `id` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',  
    `order_no` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '訂單編號',  
    `customer_no` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '客戶編號',  
    `order_status` TINYINT ( 4 ) NOT NULL DEFAULT 0 COMMENT '訂單狀態',  
    `warehouse_code` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '發貨地倉庫編碼',  
    `country` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '收貨人國家',  
    `state` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收貨人州',  
    `city` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收貨人城市',  
    `street` VARCHAR ( 256 ) NOT NULL DEFAULT '' COMMENT '收貨人街道',  
    `zip_code` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收貨人郵編',  
    `contact_email` VARCHAR ( 128 ) NOT NULL DEFAULT '' COMMENT '收貨人郵箱',  
    `contact_name` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收貨人姓名',  
    `contact_mobile` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收貨人手機號',  
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',  
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',  
    `deleted` TINYINT ( 2 ) NOT NULL DEFAULT 0 COMMENT '是否已被刪除',  
    PRIMARY KEY ( `id` ),  
    KEY `idx_customer` ( `customer_no`, `deleted` ),  
    KEY `idx_create_time` ( `create_time`, `deleted` )  
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT = '銷售訂單表';

其中Mysql版本為8.0。我們使用Python腳本向表中插入2000萬條數據。

import pymysql  
from faker import Faker  
import random  
from datetime import datetime  
from concurrent.futures import ThreadPoolExecutor  
  
# MySQL 連接配置  
db_config = {  
   'host': 'your_database_host', 
   'user': 'your_database_user', 
   'password': 'your_database_password', 
   'database': 'your_database_name'
}  
  
# 創建 MySQL 連接  
conn = pymysql.connect(**db_config)  
cursor = conn.cursor()  
  
# 使用 Faker 生成模擬數據  
fake = Faker() 

# 獲取國家下發貨倉庫編碼  
def generate_warehousecode(country):  
    if country == "US":  
        return "US-"+random.choice(["WEST", "EAST", "MIDDLE", "SOUTH", "NORTH"])+"-0" + str(random.choice([1, 2, 3, 4, 5]))  
    else:  
        return country + "00" + str(random.choice([1, 2, 3, 4, 5]))

# 插入 t_order 表數據（多線程併發，每個線程插入1萬條，共2000個線程）  
def insert_data_thread(thread_id):  
    # 創建 MySQL 連接  
    conn = pymysql.connect(**db_config)  
    cursor = conn.cursor()  
  
    order_data = []  
    for _ in range(10000):  
        order_no = "OC"+ fake.uuid4()[:12]  # 取前16位  
        customer_no = fake.uuid4()[:16]
        order_status = random.choice([1, 2, 3, 4, 5])  
        country = random.choice(  
            ["CA", "US", "MX", "JP", "UK", "TR", "DE", "ES", "FR", "IT", "NL", "PL", "SE", "BR", "CN"])  
        warehouse_code = generate_warehousecode(country)  
        state = fake.uuid4()[:16]  
        city = fake.uuid4()[:16]  
        street = fake.uuid4()  
        zip_code = fake.uuid4()[:6]  
        contact_email = fake.email()  
        contact_name = fake.name()  
        contact_mobile = fake.phone_number()  
        create_time = fake.date_time_between(start_date=datetime(2019, 1, 1), end_date=datetime.now())  
        update_time = create_time  
        deleted = 0  # 預設未刪除  
  
        cursor.execute("""  
            INSERT INTO t_order (                order_no, customer_no, order_status, warehouse_code, country,                state, city, street, zip_code, contact_email, contact_name,                contact_mobile, create_time, update_time, deleted            )            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)        """, (order_no, customer_no, order_status, warehouse_code, country,  
              state, city, street, zip_code, contact_email, contact_name,  
              contact_mobile, create_time, update_time, deleted))  
  
        order_data.append((cursor.lastrowid, order_no, customer_no, create_time))  # 保存插入的行的 ID  
    # 提交 t_order 數據插入  
    conn.commit()  
    print(thread_id+ "已經跑完10000條數據。。。。。。。。。")  
    # 關閉資料庫連接  
    cursor.close()  
    conn.close()  
  
# 使用 ThreadPoolExecutor 併發插入  
with ThreadPoolExecutor(max_workers=10) as executor:  # 可以根據需要調整最大線程數  
    executor.map(insert_data_thread, range(500))

image.png

問題復現

導出數據時我們需要按照時間倒序。所以我們先執行以下sql查詢前100條

select * FROM t_order ORDER BY create_time desc LIMIT 100;

共花費210ms。執行計劃如下：
image.png

然後我們繼續執行sql，我們從第100萬行開始取100條數據：

select * FROM t_order ORDER BY create_time desc LIMIT 1000000,100;

此時耗時3分2秒，耗時明顯增加。執行計劃如下：
image.png
image.png
由執行計劃看，此時索引已經失效了。。。。

我們繼續執行sql，從第1000萬行開始取100條數據：

select * FROM t_order ORDER BY create_time desc LIMIT 10000000,100;

此時耗時4分14秒，時間真是太長了，執行計劃如下：
image.png

image.png

後面還有接近1000萬條數據沒有取出來，直接就廢了。

原因分析

當我們使用 LIMIT offset, count 查詢語句進行深度分頁查詢時，例如 LIMIT 10000000,100 ，會發生以下過程：

  1. MySQL首先會根據給定條件從相應的索引樹中查找m+n條記錄。對於聚集索引來說，它直接找到需要的結果即丟棄前offset條數據，返回count條數據並返回；而對於二級索引，則可能涉及回表操作。
  2. 如果使用的是二級索引，在查到m+n條記錄後還需要通過這些記錄所關聯的主鍵ID去聚集索引里再次搜索出完整的行數據，然後再丟棄掉前offset條數據，返回count條數據。因此在這個過程中可能會產生大量的“回表”操作，這將導致性能下降。

我們藉助B+ Tree Visualization演示一下這張表的索引結構：

  • 聚集索引（主鍵ID）

聚集索引（主鍵ID）

  • 二級索引（idx_create_time）

二級索引（create_time）

以上述例子來說，當我們查詢LIMIT 10000000,100時，它會先從二級索引中查詢10000000+100條記錄對應的ID，然後再用這些記錄的ID去聚集索引中查詢ID對應的記錄，然後捨棄掉前10000000條數據，返回後100條數據。

所以當offset+count量很大時，Mysql的執行器認為全表掃描的性能更由於使用索引，所以也導致索引失效。所以我們要做的儘可能的減少回表的記錄數量。

解決方案

使用子查詢

我們改造sql，通過一個子查詢按照create_time倒排，獲取第offset + 1條記錄的最新的create_time，create_time直接從二級索引上可以獲取，不會進行回表，然後我們再根據這個create_time傳遞到主查詢時，取100條數據，即回表數據也僅僅只有count條即100條數據，大大減少了回表的記錄數量。

SELECT * FROM t_order  
WHERE create_time <= (
SELECT create_time FROM t_order ORDER BY create_time desc LIMIT 1000000,1
)
ORDER BY create_time desc LIMIT 100;

查詢第100萬時耗時556毫秒。

執行結果，執行計劃
image.png
image.png
可以看出主查詢以及子查詢都使用到了索引，回表查詢的數據記錄數也大大減少。

繼續查詢到第1000萬行時耗時接近6秒。
執行結果，執行計劃

image.png

image.png
可以看出主查詢以及子查詢都使用到了索引，回表查詢的數據記錄數也大大減少。

這種方式需要create_time 的分佈是相對均勻的，否則可能會導致某個時間段內的數據較多，影響查詢性能。

INNER JOIN

我們改造sql，create_time跟id都存儲在二級索引中，我們獲取這兩列值不需要回表，所以我們創建一個偏移量為offset，個數為count並且包含create_time以及id的臨時表，臨時表中數據不需要回表。然後再跟自身通過主鍵ID進行關聯，僅需要回表count條數據，大大減少了回表的記錄格式。同時也使用了主鍵索引關聯，效率也大大提高。

SELECT torder.* FROM  t_order torder
	INNER JOIN (
	SELECT id FROM t_order ORDER BY create_time DESC LIMIT 1000000,100 
	) tmp ON torder.id = tmp.id 
ORDER BY
	create_time DESC

查詢第100萬時耗時260毫秒。
執行結果，執行計劃。
image.png

image.png

繼續查詢到第1000萬行時耗時接近2秒
執行結果，執行計劃
image.png
image.png

這種方式要保證INNER JOIN使用了合適的索引。

SEARCH AFTER

每次查詢都保留上次的最小的create_time，然後下次查詢只查詢比上一頁的create_time小的數據。單表查詢，並且使用索引，回表數據少，不需要子查詢以及關聯查詢，查詢效率高。類似ES的SEARCH AFTER的查詢方式。

-- 我們模擬連續分頁到第1000000頁，最小的一條數據的create_time
SELECT * FROM t_order  
ORDER BY create_time ASC  LIMIT 1000000, 1

SELECT * FROM t_order  
WHERE create_time <= '2023-01-22 00:00:00' 
ORDER BY create_time desc LIMIT 100;

查詢第100萬時耗時142毫秒。

執行結果，執行計劃
image.png
image.png

繼續查詢到第1000萬行時耗時244毫秒

執行結果，執行計劃
image.png

image.png

當然該種方式缺點也很明顯：只能支持連續分頁，不能支持獲取隨意頁的數據。

其他方案

  • 限制查詢範圍： 在需求層面，可以限制只能查詢前100頁數據，或者規定只能獲取某個時間段內的數據，從而避免深度分頁。
  • 水平分表：考慮將數據按照某個維度進行水平分表，以減小單表的數據量
  • 使用ES，Hive，ClickHouse等OLAP方案

本文已收錄於我的個人博客：碼農Academy的博客，專註分享Java技術乾貨，包括Java基礎、Spring Boot、Spring Cloud、Mysql、Redis、Elasticsearch、中間件、架構設計、面試題、程式員攻略等


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

-Advertisement-
Play Games
更多相關文章
  • 兩行Java代碼實現PDF轉PowerPoint
    通過編程實現PDF轉PPT的功能，可以自動化轉換過程，減少手動操作的工作量，並根據需要進行批量轉換。將PDF文件轉換為PPT文檔後，可以利用PPT的豐富功能和動畫效果，達到更好的演示效果。 在Java中，我們可以使用第三方庫Spire.PDF for Java來將PDF轉換為PowerPoint文檔 ...
  • 對話蘇光牛：國內資料庫市場已進入關鍵轉折點，2024年或是分水嶺
    “中國資料庫市場已進入關鍵階段，2024年或是分水嶺！” “目前，國內資料庫產品數量接近300款，我們真的需要這麼多資料庫嗎？”面對這個問題，華為雲資料庫業務CTO蘇光牛不假思索地給出了他的見解： “不僅是中國市場，全球範圍內，也不需要如此多的商業資料庫。” 他進一步預測，隨著市場的自然淘汰，未來三 ...
  • 【斬蟲】Hadoop中作業執行剛開始就掛掉的兩種情況
    這篇筆記咱主要記錄了Hadoop執行Job時可能出現的 /bin/java: No such file or directory 以及 java.lang.reflect.InaccessibleObjectException 問題。 ...
  • Redis的Java客戶端-Jedis
    Redis的Java客戶端-Jedis 在Redis官網中提供了各種語言的客戶端，地址：https://redis.io/docs/clients/ 其中Java客戶端也包含很多： 標記為❤的就是推薦使用的java客戶端，包括： Jedis和Lettuce：這兩個主要是提供了Redis命令對應的AP ...
  • 【PostgreSQL架構】PostgreSQL的最佳群集高可用性方案
    轉載：【PostgreSQL架構】PostgreSQL的最佳群集高可用性方案-騰訊雲開發者社區-騰訊雲 (tencent.com) 如果您的系統依賴PostgreSQL資料庫並且您正在尋找HA的集群解決方案，我們希望提前告知您這是一項複雜的任務，但並非不可能實現。 我們將討論一些解決方案，您可以從中 ...
  • GreatSQL榮獲2023 InfoQ“技術生態構建獎” 助力行業變革之路
    1 月 31 日，InfoQ 極客傳媒合作伙伴年度盛典在北京舉辦，圍繞“有被 Q 到”主題精彩展開。GreatSQL受邀參會，與諸多IT行業伙伴相聚一堂，共築開發者生態，共話技術前沿與商業創新。 會議期間，InfoQ 為全國技術行業做出突出貢獻的企業和項目進行了頒獎。GreatSQL 開源資料庫社區 ...
  • SQL 語法基礎重溫
    SQL的相關語法記錄 【連接】 相關內容參考： 一張圖看懂 SQL 的各種 join 用法_51CTO博客_sql join用法 SQL FULL OUTER JOIN 關鍵字 | 菜鳥教程 (runoob.com) 一文講懂SQL外連接OUTER JOIN - 知乎 (zhihu.com) INN ...
  • 【相容認證】白鯨開源與銀河麒麟高級伺服器操作系統成功通過測試
    2024年1月2日，北京白鯨開源科技有限公司（以下簡稱"白鯨開源"）榮幸宣佈，白鯨開源旗下產品 WhaleStudio V2.4 已成功通過與麒麟軟體有限公司旗下的銀河麒麟高級伺服器操作系統產品的相容性測試。 麒麟軟體有限公司的銀河麒麟高級伺服器操作系統(飛騰版)V10和銀河麒麟高級伺服器操作系統( ...
一周排行
    -Advertisement-
    Play Games
  • （開源）都進來！簡單易懂、功能強大的許可權+可視化流程管理系統
    1、預覽地址：http://139.155.137.144:9012 2、qq群:801913255 一、前言 隨著網路的發展，企業對於信息系統數據的保密工作愈發重視，不同身份、角色對於數據的訪問許可權都應該大相徑庭。 列如 1、不同登錄人員對一個數據列表的可見度是不一樣的，如數據列、數據行、數據按鈕 ...
  • 設計模式-C#實現簡單工廠模式
    前言 上一篇文章寫瞭如何使用RabbitMQ做個簡單的發送郵件項目，然後評論也是比較多，也是準備去學習一下如何確保RabbitMQ的消息可靠性，但是由於時間原因，先來說說設計模式中的簡單工廠模式吧！ 在瞭解簡單工廠模式之前，我們要知道C#是一款面向對象的高級程式語言。它有3大特性，封裝、繼承、多態。 ...
  • Nodify學習 一：介紹與使用
    Nodify學習 一：介紹與使用 - 可樂_加冰 - 博客園 (cnblogs.com) Nodify學習 二：添加節點 - 可樂_加冰 - 博客園 (cnblogs.com) 介紹 Nodify是一個WPF基於節點的編輯器控制項，其中包含一系列節點、連接和連接器組件，旨在簡化構建基於節點的工具的過程 ...
  • 使用C#/.NET解析Wiki百科數據實現獲取歷史上的今天
    創建一個webapi項目做測試使用。 創建新控制器，搭建一個基礎框架，包括獲取當天日期、wiki的請求地址等 創建一個Http請求幫助類以及方法，用於獲取指定URL的信息 使用http請求訪問指定url，先運行一下，看看返回的內容。內容如圖右邊所示，實際上是一個Json數據。我們主要解析 大事記 部 ...
  • .NET科普：.NET簡史、.NET Standard以及C#和.NET Framework之間的關係
    最近在不少自媒體上看到有關.NET與C#的資訊與評價，感覺大家對.NET與C#還是不太瞭解，尤其是對2016年6月發佈的跨平臺.NET Core 1.0，更是知之甚少。在考慮一番之後，還是決定寫點東西總結一下，也回顧一下.NET的發展歷史。 首先，你沒看錯，.NET是跨平臺的，可以在Windows、 ...
  • Nodify學習 二：添加節點
    Nodify學習 一：介紹與使用 - 可樂_加冰 - 博客園 (cnblogs.com) Nodify學習 二：添加節點 - 可樂_加冰 - 博客園 (cnblogs.com) 添加節點（nodes） 通過上一篇我們已經創建好了編輯器實例現在我們為編輯器添加一個節點 添加model和viewmode ...
  • 論如何直接用EF Core實現創建更新時間、用戶審計，自動化樂觀併發、軟刪除和樹形查詢（上）
    前言 資料庫併發，數據審計和軟刪除一直是數據持久化方面的經典問題。早些時候，這些工作需要手寫複雜的SQL或者通過存儲過程和觸發器實現。手寫複雜SQL對軟體可維護性構成了相當大的挑戰，隨著SQL字數的變多，用到的嵌套和複雜語法增加，可讀性和可維護性的難度是幾何級暴漲。因此如何在實現功能的同時控制這些S ...
  • C# 使用模式匹配的好處,因為好用所以推薦~
    類型檢查和轉換：當你需要檢查對象是否為特定類型，並且希望在同一時間內將其轉換為那個類型時，模式匹配提供了一種更簡潔的方式來完成這一任務，避免了使用傳統的as和is操作符後還需要進行額外的null檢查。 複雜條件邏輯：在處理複雜的條件邏輯時，特別是涉及到多個條件和類型的情況下，使用模式匹配可以使代碼更 ...
  • 推薦一種批量判斷文件是否存在的快速辦法
    在日常開發中，我們經常需要和文件打交道，特別是桌面開發，有時候就會需要載入大批量的文件，而且可能還會存在部分文件缺失的情況，那麼如何才能快速的判斷文件是否存在呢？如果處理不當的，且文件數量比較多的時候，可能會造成卡頓等情況，進而影響程式的使用體驗。今天就以一個簡單的小例子，簡述兩種不同的判斷文件是否... ...
  • 論如何直接用EF Core實現創建更新時間、用戶審計，自動化樂觀併發、軟刪除和樹形查詢（中）
    前言 資料庫併發，數據審計和軟刪除一直是數據持久化方面的經典問題。早些時候，這些工作需要手寫複雜的SQL或者通過存儲過程和觸發器實現。手寫複雜SQL對軟體可維護性構成了相當大的挑戰，隨著SQL字數的變多，用到的嵌套和複雜語法增加，可讀性和可維護性的難度是幾何級暴漲。因此如何在實現功能的同時控制這些S ...
所有分類