MySQL千萬數據量深分頁優(yōu)化流程(拒絕線上故障)

更新時間：2023年05月16日 09:24:19 作者：馬丁玩編程

這篇文章主要為大家介紹了MySQL千萬數據量深分頁優(yōu)化拒絕線上故障，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪

引言

優(yōu)化項目代碼過程中發(fā)現一個千萬級數據深分頁問題，緣由是這樣的

庫里有一張耗材 MCS_PROD 表，通過同步外部數據中臺多維度數據，在系統(tǒng)內部組裝為單一耗材產品，最終同步到 ES 搜索引擎

MySQL 同步 ES 流程如下：

通過定時任務的形式觸發(fā)同步，比如間隔半天或一天的時間頻率
同步的形式為增量同步，根據更新時間的機制，比如第一次同步查詢 >= 1970-01-01 00:00:00.0
記錄最大的更新時間進行存儲，下次更新同步以此為條件
以分頁的形式獲取數據，當前頁數量加一，循環(huán)到最后一頁

在這里問題也就出現了，MySQL 查詢分頁 OFFSET 越深入，性能越差，初步估計線上 MCS_PROD 表中記錄在 1000w 左右

如果按照每頁 10 條，OFFSET 值會拖垮查詢性能，進而形成一個 "性能深淵"

同步類代碼針對此問題有兩種優(yōu)化方式：

采用游標、流式方案進行優(yōu)化
優(yōu)化深分頁性能，文章圍繞這個題目展開

軟硬件說明

MySQL VERSION

mysql>?select?version();
+-----------+
|?version()?|
+-----------+
|?5.7.30????|
+-----------+
1?row?in?set?(0.01?sec)

表結構說明

借鑒公司表結構，字段、長度以及名稱均已刪減

mysql>?DESC?MCS_PROD;
+-----------------------+--------------+------+-----+---------+----------------+
|?Field?????????????????|?Type?????????|?Null?|?Key?|?Default?|?Extra??????????|
+-----------------------+--------------+------+-----+---------+----------------+
|?MCS_PROD_ID???????????|?int(11)??????|?NO???|?PRI?|?NULL????|?auto_increment?|
|?MCS_CODE??????????????|?varchar(100)?|?YES??|?????|?????????|????????????????|
|?MCS_NAME??????????????|?varchar(500)?|?YES??|?????|?????????|????????????????|
|?UPDT_TIME?????????????|?datetime?????|?NO???|?MUL?|?NULL????|????????????????|
+-----------------------+--------------+------+-----+---------+----------------+
4?rows?in?set?(0.01?sec)

通過測試同學幫忙造了 500w 左右數據量

mysql>?SELECT?COUNT(*)?FROM?MCS_PROD;
+----------+
|?count(*)?|
+----------+
|??5100000?|
+----------+
1?row?in?set?(1.43?sec)

SQL 語句如下

因為功能需要滿足 增量拉取的方式，所以會有數據更新時間的條件查詢，以及相關 查詢排序（此處有坑）

SELECT
?MCS_PROD_ID,
?MCS_CODE,
?MCS_NAME,
?UPDT_TIME
FROM
?MCS_PROD
WHERE
?UPDT_TIME?>=?'1970-01-01?00:00:00.0'?ORDER?BY?UPDT_TIME
LIMIT?xx,?xx

重新認識 MySQL 分頁

LIMIT 子句可以被用于強制 SELECT 語句返回指定的記錄數。LIMIT 接收一個或兩個數字參數，參數必須是一個整數常量

如果給定兩個參數，第一個參數指定第一個返回記錄行的偏移量，第二個參數指定返回記錄行的最大數

舉個簡單的例子，分析下 SQL 查詢過程，掌握深分頁性能為什么差

mysql>?SELECT?MCS_PROD_ID,MCS_CODE,MCS_NAME?FROM?MCS_PROD?WHERE?(UPDT_TIME?>=?'1970-01-01?00:00:00.0')?ORDER?BY?UPDT_TIME?LIMIT?100000,?1;
+-------------+-------------------------+------------------+---------------------+
|?MCS_PROD_ID?|?MCS_CODE????????????????|?MCS_NAME?????????|?UPDT_TIME???????????|
+-------------+-------------------------+------------------+---------------------+
|??????181789?|?XA601709733186213015031?|?尺、橈骨LC-DCP骨板?|?2020-10-19?16:22:19?|
+-------------+-------------------------+------------------+---------------------+
1?row?in?set?(3.66?sec)
mysql>?EXPLAIN?SELECT?MCS_PROD_ID,MCS_CODE,MCS_NAME?FROM?MCS_PROD?WHERE?(UPDT_TIME?>=?'1970-01-01?00:00:00.0')?ORDER?BY?UPDT_TIME?LIMIT?100000,?1;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+
|?id?|?select_type?|?table????|?partitions?|?type??|?possible_keys?|?key????????|?key_len?|?ref??|?rows????|?filtered?|?Extra?????????????????|
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+
|??1?|?SIMPLE??????|?MCS_PROD?|?NULL???????|?range?|?MCS_PROD_1????|?MCS_PROD_1?|?5???????|?NULL?|?2296653?|???100.00?|?Using?index?condition?|
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+
1?row?in?set,?1?warning?(0.01?sec)

簡單說明下上面 SQL 執(zhí)行過程：

首先查詢了表 MCS_PROD，進行過濾 UPDT_TIME 條件，查詢出展示列（涉及回表操作）進行排序以及 LIMIT
LIMIT 100000, 1 的意思是掃描滿足條件的 100001 行，然后扔掉前 100000 行

MySQL 耗費了 大量隨機 I/O 在回表查詢聚簇索引的數據上，而這 100000 次隨機 I/O 查詢數據不會出現在結果集中

如果系統(tǒng)并發(fā)量稍微高一點，每次查詢掃描超過 100000 行，性能肯定堪憂，另外 LIMIT 分頁 OFFSET 越深，性能越差（多次強調）

圖1 數據僅供參考

深分頁優(yōu)化

關于 MySQL 深分頁優(yōu)化常見的大概有以下三種策略：

子查詢優(yōu)化
延遲關聯
書簽記錄

上面三點都能大大的提升查詢效率，核心思想就是讓 MySQL 盡可能掃描更少的頁面，獲取需要訪問的記錄后再根據關聯列回原表查詢所需要的列

子查詢優(yōu)化

子查詢深分頁優(yōu)化語句如下：

mysql>?SELECT?MCS_PROD_ID,MCS_CODE,MCS_NAME?FROM?MCS_PROD?WHERE?MCS_PROD_ID?>=?(?SELECT?m1.MCS_PROD_ID?FROM?MCS_PROD?m1?WHERE?m1.UPDT_TIME?>=?'1970-01-01?00:00:00.0'?ORDER?BY?m1.UPDT_TIME?LIMIT?3000000,?1)?LIMIT?1;
+-------------+-------------------------+------------------------+
|?MCS_PROD_ID?|?MCS_CODE????????????????|?MCS_NAME???????????????|
+-------------+-------------------------+------------------------+
|?????3021401?|?XA892010009391491861476?|?金屬解剖型接骨板T型接骨板A?|
+-------------+-------------------------+------------------------+
1?row?in?set?(0.76?sec)
mysql>?EXPLAIN?SELECT?MCS_PROD_ID,MCS_CODE,MCS_NAME?FROM?MCS_PROD?WHERE?MCS_PROD_ID?>=?(?SELECT?m1.MCS_PROD_ID?FROM?MCS_PROD?m1?WHERE?m1.UPDT_TIME?>=?'1970-01-01?00:00:00.0'?ORDER?BY?m1.UPDT_TIME?LIMIT?3000000,?1)?LIMIT?1;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+--------------------------+
|?id?|?select_type?|?table????|?partitions?|?type??|?possible_keys?|?key????????|?key_len?|?ref??|?rows????|?filtered?|?Extra????????????????????|
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+--------------------------+
|??1?|?PRIMARY?????|?MCS_PROD?|?NULL???????|?range?|?PRIMARY???????|?PRIMARY????|?4???????|?NULL?|?2296653?|???100.00?|?Using?where??????????????|
|??2?|?SUBQUERY????|?m1???????|?NULL???????|?range?|?MCS_PROD_1????|?MCS_PROD_1?|?5???????|?NULL?|?2296653?|???100.00?|?Using?where;?Using?index?|
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+--------------------------+
2?rows?in?set,?1?warning?(0.77?sec)

根據執(zhí)行計劃得知，子查詢 table m1 查詢是用到了索引。首先在 索引上拿到了聚集索引的主鍵 ID 省去了回表操作，然后第二查詢直接根據第一個查詢的 ID 往后再去查 10 個就可以了

圖2 數據僅供參考

延遲關聯

"延遲關聯" 深分頁優(yōu)化語句如下：

mysql>?SELECT?MCS_PROD_ID,MCS_CODE,MCS_NAME?FROM?MCS_PROD?INNER?JOIN?(SELECT?m1.MCS_PROD_ID?FROM?MCS_PROD?m1?WHERE?m1.UPDT_TIME?>=?'1970-01-01?00:00:00.0'?ORDER?BY?m1.UPDT_TIME?LIMIT?3000000,?1)?AS??MCS_PROD2?USING(MCS_PROD_ID);
+-------------+-------------------------+------------------------+
|?MCS_PROD_ID?|?MCS_CODE????????????????|?MCS_NAME???????????????|
+-------------+-------------------------+------------------------+
|?????3021401?|?XA892010009391491861476?|?金屬解剖型接骨板T型接骨板A?|
+-------------+-------------------------+------------------------+
1?row?in?set?(0.75?sec)
mysql>?EXPLAIN?SELECT?MCS_PROD_ID,MCS_CODE,MCS_NAME?FROM?MCS_PROD?INNER?JOIN?(SELECT?m1.MCS_PROD_ID?FROM?MCS_PROD?m1?WHERE?m1.UPDT_TIME?>=?'1970-01-01?00:00:00.0'?ORDER?BY?m1.UPDT_TIME?LIMIT?3000000,?1)?AS??MCS_PROD2?USING(MCS_PROD_ID);
+----+-------------+------------+------------+--------+---------------+------------+---------+-----------------------+---------+----------+--------------------------+
|?id?|?select_type?|?table??????|?partitions?|?type???|?possible_keys?|?key????????|?key_len?|?ref???????????????????|?rows????|?filtered?|?Extra????????????????????|
+----+-------------+------------+------------+--------+---------------+------------+---------+-----------------------+---------+----------+--------------------------+
|??1?|?PRIMARY?????|?<derived2>?|?NULL???????|?ALL????|?NULL??????????|?NULL???????|?NULL????|?NULL??????????????????|?2296653?|???100.00?|?NULL?????????????????????|
|??1?|?PRIMARY?????|?MCS_PROD???|?NULL???????|?eq_ref?|?PRIMARY???????|?PRIMARY????|?4???????|?MCS_PROD2.MCS_PROD_ID?|???????1?|???100.00?|?NULL?????????????????????|
|??2?|?DERIVED?????|?m1?????????|?NULL???????|?range??|?MCS_PROD_1????|?MCS_PROD_1?|?5???????|?NULL??????????????????|?2296653?|???100.00?|?Using?where;?Using?index?|
+----+-------------+------------+------------+--------+---------------+------------+---------+-----------------------+---------+----------+--------------------------+
3?rows?in?set,?1?warning?(0.00?sec)

思路以及性能與子查詢優(yōu)化一致，只不過采用了 JOIN 的形式執(zhí)行

書簽記錄

關于 LIMIT 深分頁問題，核心在于 OFFSET 值，它會 導致 MySQL 掃描大量不需要的記錄行然后拋棄掉

我們可以先使用書簽 記錄獲取上次取數據的位置，下次就可以直接從該位置開始掃描，這樣可以 避免使用 OFFEST

假設需要查詢 3000000 行數據后的第 1 條記錄，查詢可以這么寫

mysql>?SELECT?MCS_PROD_ID,MCS_CODE,MCS_NAME?FROM?MCS_PROD?WHERE?MCS_PROD_ID?<?3000000?ORDER?BY?UPDT_TIME?LIMIT?1;
+-------------+-------------------------+---------------------------------+
|?MCS_PROD_ID?|?MCS_CODE????????????????|?MCS_NAME????????????????????????|
+-------------+-------------------------+---------------------------------+
|?????????127?|?XA683240878449276581799?|?股骨近端-1螺紋孔鎖定板（純鈦）YJBL01?|
+-------------+-------------------------+---------------------------------+
1?row?in?set?(0.00?sec)
mysql>?EXPLAIN?SELECT?MCS_PROD_ID,MCS_CODE,MCS_NAME?FROM?MCS_PROD?WHERE?MCS_PROD_ID?<?3000000?ORDER?BY?UPDT_TIME?LIMIT?1;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|?id?|?select_type?|?table????|?partitions?|?type??|?possible_keys?|?key????????|?key_len?|?ref??|?rows?|?filtered?|?Extra???????|
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|??1?|?SIMPLE??????|?MCS_PROD?|?NULL???????|?index?|?PRIMARY???????|?MCS_PROD_1?|?5???????|?NULL?|????2?|????50.00?|?Using?where?|
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1?row?in?set,?1?warning?(0.00?sec)

好處是很明顯的，查詢速度超級快，性能都會穩(wěn)定在毫秒級，從性能上考慮碾壓其它方式

不過這種方式局限性也比較大，需要一種類似連續(xù)自增的字段，以及業(yè)務所能包容的連續(xù)概念，視情況而定

上圖是阿里云 OSS Bucket 桶內文件列表，大膽猜測是不是可以采用書簽記錄的形式完成

ORDER BY 巨坑, 慎踩

以下言論可能會打破你對 order by 所有 美好 YY

先說結論吧，當 LIMIT OFFSET 過深時，會使 ORDER BY 普通索引失效（聯合、唯一這些索引沒有測試）

mysql>?EXPLAIN?SELECT?MCS_PROD_ID,MCS_CODE,MCS_NAME,UPDT_TIME?FROM?MCS_PROD?WHERE?(UPDT_TIME?>=?'1970-01-01?00:00:00.0')?ORDER?BY?UPDT_TIME?LIMIT?100000,?1;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+
|?id?|?select_type?|?table????|?partitions?|?type??|?possible_keys?|?key????????|?key_len?|?ref??|?rows????|?filtered?|?Extra?????????????????|
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+
|??1?|?SIMPLE??????|?MCS_PROD?|?NULL???????|?range?|?MCS_PROD_1????|?MCS_PROD_1?|?5???????|?NULL?|?2296653?|???100.00?|?Using?index?condition?|
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+
1?row?in?set,?1?warning?(0.00?sec)

先來說一下這個 ORDER BY 執(zhí)行過程：

初始化 SORT_BUFFER，放入 MCS_PROD_ID,MCS_CODE,MCS_NAME,UPDT_TIME 四個字段
從索引 UPDT_TIME 找到滿足條件的主鍵 ID，回表查詢出四個字段值存入 SORT_BUFFER
從索引處繼續(xù)查詢滿足 UPDT_TIME 條件記錄，繼續(xù)執(zhí)行步驟 2
對 SORT_BUFFER 中的數據按照 UPDT_TIME 排序
排序成功后取出符合 LIMIT 條件的記錄返回客戶端

按照 UPDT_TIME 排序可能在內存中完成，也可能需要使用外部排序，取決于排序所需的內存和參數 SORT_BUFFER_SIZE

SORT_BUFFER_SIZE 是 MySQL 為排序開辟的內存。如果排序數據量小于 SORT_BUFFER_SIZE，排序會在內存中完成。如果數據量過大，內存放不下，則會利用磁盤臨時文件排序

針對 SORT_BUFFER_SIZE 這個參數在網上查詢到有用資料比較少，大家如果測試過程中存在問題，可以加微信一起溝通

ORDER BY 索引失效舉例

OFFSET 100000 時，通過 key Extra 得知，沒有使用磁盤臨時文件排序，這個時候把 OFFSET 調整到 500000

涼涼夜色為你思念成河，化作春泥呵護著你... 一首涼涼送給寫這個 SQL 的同學，發(fā)現了 Using filesort

mysql>?EXPLAIN?SELECT?MCS_PROD_ID,MCS_CODE,MCS_NAME,UPDT_TIME?FROM?MCS_PROD?WHERE?(UPDT_TIME?>=?'1970-01-01?00:00:00.0')?ORDER?BY?UPDT_TIME?LIMIT?500000,?1;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
|?id?|?select_type?|?table????|?partitions?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows????|?filtered?|?Extra???????????????????????|
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
|??1?|?SIMPLE??????|?MCS_PROD?|?NULL???????|?ALL??|?MCS_PROD_1????|?NULL?|?NULL????|?NULL?|?4593306?|????50.00?|?Using?where;?Using?filesort?|
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
1?row?in?set,?1?warning?(0.00?sec)

Using filesort 表示在索引之外，需要額外進行外部的排序動作，性能必將受到嚴重影響

所以我們應該 結合相對應的業(yè)務邏輯避免常規(guī) LIMIT OFFSET，采用 # 深分頁優(yōu)化 章節(jié)進行修改對應業(yè)務

結言

最后有一點需要聲明下，MySQL 本身并不適合單表大數據量業(yè)務

因為 MySQL 應用在企業(yè)級項目時，針對庫表查詢并非簡單的條件，可能會有更復雜的聯合查詢，亦或者是大數據量時存在頻繁新增或更新操作，維護索引或者數據 ACID 特性上必然存在性能犧牲

如果設計初期能夠預料到庫表的數據增長，理應構思合理的重構優(yōu)化方式，比如 ES 配合查詢、分庫分表、TiDB 等解決方式

以上就是MySQL千萬數據量深分頁優(yōu)化拒絕線上故障的詳細內容，更多關于MySQL千萬數據深分頁優(yōu)化的資料請關注腳本之家其它相關文章！

您可能感興趣的文章:

允許遠程用戶訪問mysql服務sql語句
本節(jié)主要介紹了如何允許遠程用戶訪問mysql服務，本例授權192.168.14.1 主機的cakephp用戶訪問cakephp數據庫
2014-07-07
解決遠程連接MySQL報錯：2003 - Can‘t connect to&nb
這篇文章主要給大家介紹了解決遠程連接MySQL報錯：2003 - Can‘t connect to MySQL server on ‘X.X.X.X‘ (10060 “Unknown error“)問題的方案,文中有詳細的解決步驟,需要的朋友可以參考下
2023-09-09
MySQL8.0開啟遠程連接權限的方法步驟
MySQL8.0設置遠程訪問權限,找了一圈都沒找到一個適用的,索性自己寫一個,這篇文章主要給大家介紹了關于MySQL8.0開啟遠程連接權限的方法步驟,需要的朋友可以參考下
2022-06-06
MySQL學習必備條件查詢數據
這篇文章主要介紹了MySQL學習必備條件查詢數據，首先通過利用where語句可以對數據進行篩選展開主題相關內容，具有一定的參考價值，需要的小伙伴可以參考一下，希望對你有所幫助
2022-03-03
MySQL解決字符集編碼問題
MySQL的默認編碼方式是?拉丁文,如果想要設置一些漢字的數據.可能會報錯.這篇文章中主要介紹了解決這個問題的方法，需要的朋友可以參考一下
2023-04-04
mysql root密碼的重設方法（親測可用）
這篇文章主要介紹了如何重設mysql root密碼,需要的朋友可以參考下
2014-02-02
MySQL語句匯總整理
這篇文章主要給大家分享的是MySQL語句匯總整理，圍繞MySQL語句的相關資料對其進行整理，具有一定的參考價值，需要的小伙伴可以參考一下，希望對你有所幫助
2021-12-12
MySQL中JOIN算法的具體使用
JOIN操作是SQL查詢中至關重要的部分,它能夠將多個表中的數據根據指定的條件組合起來,本文主要介紹了MySQL中JOIN算法的具體使用,感興趣的可以了解一下
2024-08-08
MySQL索引失效之隱式轉換的問題
本文主要介紹了MySQL索引失效之隱式轉換的問題，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2022-01-01
MySQL中varchar(n) 中n最大取值為多少
本文主要介紹了MySQL中varchar(n) 中n最大取值為多少
2024-08-08