面試官: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_timeid都存儲在二級索引中,我們獲取這兩列值不需要回表,所以我們創建一個偏移量為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
更多相關文章
  • 通過編程實現PDF轉PPT的功能,可以自動化轉換過程,減少手動操作的工作量,並根據需要進行批量轉換。將PDF文件轉換為PPT文檔後,可以利用PPT的豐富功能和動畫效果,達到更好的演示效果。 在Java中,我們可以使用第三方庫Spire.PDF for Java來將PDF轉換為PowerPoint文檔 ...
  • “中國資料庫市場已進入關鍵階段,2024年或是分水嶺!” “目前,國內資料庫產品數量接近300款,我們真的需要這麼多資料庫嗎?”面對這個問題,華為雲資料庫業務CTO蘇光牛不假思索地給出了他的見解: “不僅是中國市場,全球範圍內,也不需要如此多的商業資料庫。” 他進一步預測,隨著市場的自然淘汰,未來三 ...
  • 這篇筆記咱主要記錄了Hadoop執行Job時可能出現的 /bin/java: No such file or directory 以及 java.lang.reflect.InaccessibleObjectException 問題。 ...
  • Redis的Java客戶端-Jedis 在Redis官網中提供了各種語言的客戶端,地址:https://redis.io/docs/clients/ 其中Java客戶端也包含很多: 標記為❤的就是推薦使用的java客戶端,包括: Jedis和Lettuce:這兩個主要是提供了Redis命令對應的AP ...
  • 轉載:【PostgreSQL架構】PostgreSQL的最佳群集高可用性方案-騰訊雲開發者社區-騰訊雲 (tencent.com) 如果您的系統依賴PostgreSQL資料庫並且您正在尋找HA的集群解決方案,我們希望提前告知您這是一項複雜的任務,但並非不可能實現。 我們將討論一些解決方案,您可以從中 ...
  • 1 月 31 日,InfoQ 極客傳媒合作伙伴年度盛典在北京舉辦,圍繞“有被 Q 到”主題精彩展開。GreatSQL受邀參會,與諸多IT行業伙伴相聚一堂,共築開發者生態,共話技術前沿與商業創新。 會議期間,InfoQ 為全國技術行業做出突出貢獻的企業和項目進行了頒獎。GreatSQL 開源資料庫社區 ...
  • 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
  • 在C#中使用SQL Server實現事務的ACID(原子性、一致性、隔離性、持久性)屬性和使用資料庫鎖(悲觀鎖和樂觀鎖)時,你可以通過ADO.NET的SqlConnection和SqlTransaction類來實現。下麵是一些示例和概念說明。 實現ACID事務 ACID屬性是事務處理的四個基本特征, ...
  • 我們在《SqlSugar開發框架》中,Winform界面開發部分往往也用到了自定義的用戶控制項,對應一些特殊的界面或者常用到的一些局部界面內容,我們可以使用自定義的用戶控制項來提高界面的統一性,同時也增強了使用的便利性。如我們Winform界面中用到的分頁控制項、附件顯示內容、以及一些公司、部門、菜單的下... ...
  • 在本篇教程中,我們學習瞭如何在 Taurus.MVC WebMVC 中進行數據綁定操作。我們還學習瞭如何使用 ${屬性名稱} CMS 語法來綁定頁面上的元素與 Model 中的屬性。通過這些步驟,我們成功實現了一個簡單的數據綁定示例。 ...
  • 是在MVVM中用來傳遞消息的一種方式。它是在MVVMLight框架中提供的一個實現了IMessenger介面的類,可以用來在ViewModel之間、ViewModel和View之間傳遞消息。 Send 接受一個泛型參數,表示要發送的消息內容。 Register 方法用於註冊某個對象接收消息。 pub ...
  • 概述:在WPF中,通過EventHandler可實現基礎和高級的UI更新方式。基礎用法涉及在類中定義事件,併在UI中訂閱以執行更新操作。高級用法藉助Dispatcher類,確保在非UI線程上執行操作後,通過UI線程更新界面。這兩種方法提供了靈活而可靠的UI更新機制。 在WPF(Windows Pre ...
  • 概述:本文介紹了在C#程式開發中如何利用自定義擴展方法測量代碼執行時間。通過使用簡單的Action委托,開發者可以輕鬆獲取代碼塊的執行時間,幫助優化性能、驗證演算法效率以及監控系統性能。這種通用方法提供了一種便捷而有效的方式,有助於提高開發效率和代碼質量。 在軟體開發中,瞭解代碼執行時間是優化程式性能 ...
  • 概述:Cron表達式是一種強大的定時任務調度工具,通過配置不同欄位實現靈活的時間規定。在.NET中,Quartz庫提供了簡便的方式配置Cron表達式,實現精準的定時任務調度。這種靈活性和可擴展性使得開發者能夠根據需求輕鬆地制定和管理定時任務,例如每天備份系統日誌或其他重要操作。 Cron表達式詳解 ...
  • 概述:.NET提供多種定時器,如System.Windows.Forms.Timer適用於UI,System.Web.UI.Timer用於Web,System.Diagnostics.Timer用於性能監控,System.Threading.Timer和System.Timers.Timer用於一般 ...
  • 問題背景 有同事聯繫我說,在生產環境上,訪問不了我負責的common服務,然後我去檢查common服務的health endpoint, 沒問題,然後我問了下異常,timeout導致的System.OperationCanceledException。那大概率是客戶端的問題,會不會是埠耗盡,用ne ...
  • 前言: 在本篇 Taurus.MVC WebMVC 入門開發教程的第四篇文章中, 我們將學習如何實現數據列表的綁定,通過使用 List<Model> 來展示多個數據項。 我們將繼續使用 Taurus.Mvc 命名空間,同時探討如何在視圖中綁定並顯示一個 Model 列表。 步驟1:創建 Model ...