面試官：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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 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 ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類