面試官：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 微服務框架
    前言 微服務架構已經成為搭建高效、可擴展系統的關鍵技術之一，然而，現有許多微服務框架往往過於複雜，使得我們普通開發者難以快速上手並體驗到微服務帶了的便利。為瞭解決這一問題，於是作者精心打造了一款最接地氣的 .NET 微服務框架，幫助我們輕鬆構建和管理微服務應用。 本框架不僅支持 Consul 服務註 ...
  • 基於surging的木舟IOT平臺如何添加網路組件
    先看一下效果吧: 如果不會寫動畫或者懶得寫動畫，就直接交給Blend來做吧; 其實Blend操作起來很簡單，有點類似於在操作PS，我們只需要設置關鍵幀，滑鼠點來點去就可以了，Blend會自動幫我們生成我們想要的動畫效果. 第一步：要創建一個空的WPF項目 第二步：右鍵我們的項目，在最下方有一個，在B ...
  • Prism:框架介紹與安裝
    Prism:框架介紹與安裝 什麼是Prism? Prism是一個用於在 WPF、Xamarin Form、Uno 平臺和 WinUI 中構建鬆散耦合、可維護和可測試的 XAML 應用程式框架 Github https://github.com/PrismLibrary/Prism NuGet htt ...
  • 不可不知的WPF畫筆（Brush）
    在WPF中，屏幕上的所有內容，都是通過畫筆（Brush）畫上去的。如按鈕的背景色，邊框，文本框的前景和形狀填充。藉助畫筆，可以繪製頁面上的所有UI對象。不同畫筆具有不同類型的輸出（ 如：某些畫筆使用純色繪製區域，其他畫筆使用漸變、圖案、圖像或繪圖）。 ...
  • .NET 8 高併發微服務電商系統實戰
    前言 嗨，大家好！推薦一個基於 .NET 8 的高併發微服務電商系統，涵蓋了商品、訂單、會員、服務、財務等50多種實用功能。 項目不僅使用了 .NET 8 的最新特性，還集成了AutoFac、DotLiquid、HangFire、Nlog、Jwt、LayUIAdmin、SqlSugar、MySQL、 ...
  • 基於surging 如何利用peerjs進行語音視頻通話
    本文主要介紹攝像頭（相機）如何採集數據，用於類似攝像頭本地顯示軟體，以及流媒體數據傳輸場景如傳屏、視訊會議等。 攝像頭採集有多種方案，如AForge.NET、WPFMediaKit、OpenCvSharp、EmguCv、DirectShow.NET、MediaCaptre（UWP），網上一些文章以及 ...
  • [WPF]數據綁定時為何會出現StringFormat失效
    前言 Seal-Report 是一款.NET 開源報表工具，擁有 1.4K Star。它提供了一個完整的框架，使用 C# 編寫，最新的版本採用的是 .NET 8.0 。 它能夠高效地從各種資料庫或 NoSQL 數據源生成日常報表，並支持執行複雜的報表任務。 其簡單易用的安裝過程和直觀的設計界面，我們 ...
  • NET Core 多身份校驗與策略模式
    背景需求： 系統需要對接到XXX官方的API，但因此官方對接以及管理都十分嚴格。而本人部門的系統中包含諸多子系統，系統間為了穩定，程式間多數固定Token+特殊驗證進行調用，且後期還要提供給其他兄弟部門系統共同調用。 原則上：每套系統都必須單獨接入到官方，但官方的接入複雜，還要官方指定機構認證的證書 ...
  • C#自定義控制項—轉換開關
    本文介紹下電腦設備關機的情況下如何通過網路喚醒設備，之前電源S狀態 電腦Power電源狀態- 唐宋元明清2188 - 博客園 (cnblogs.com) 有介紹過遠程喚醒設備，後面這倆天瞭解多了點所以單獨加個隨筆 設備關機的情況下，使用網路喚醒的前提條件： 1. 被喚醒設備需要支持這WakeOnL ...
  • .NET 8.0 前後分離快速開發框架
    前言 大家好，推薦一個.NET 8.0 為核心，結合前端 Vue 框架，實現了前後端完全分離的設計理念。它不僅提供了強大的基礎功能支持，如許可權管理、代碼生成器等，還通過採用主流技術和最佳實踐，顯著降低了開發難度，加快了項目交付速度。 如果你需要一個高效的開發解決方案，本框架能幫助大家輕鬆應對挑戰，實 ...
所有分類