MySQL千萬數(shù)據(jù)量深分頁優(yōu)化流程(拒絕線上故障)
引言
優(yōu)化項(xiàng)目代碼過程中發(fā)現(xiàn)一個(gè)千萬級(jí)數(shù)據(jù)深分頁問題，緣由是這樣的
庫里有一張耗材 MCS_PROD 表，通過同步外部數(shù)據(jù)中臺(tái)多維度數(shù)據(jù)，在系統(tǒng)內(nèi)部組裝為單一耗材產(chǎn)品，最終同步到 ES 搜索引擎
MySQL 同步 ES 流程如下：
- 通過定時(shí)任務(wù)的形式觸發(fā)同步，比如間隔半天或一天的時(shí)間頻率
- 同步的形式為增量同步，根據(jù)更新時(shí)間的機(jī)制，比如第一次同步查詢 >= 1970-01-01 00:00:00.0
- 記錄最大的更新時(shí)間進(jìn)行存儲(chǔ)，下次更新同步以此為條件
- 以分頁的形式獲取數(shù)據(jù)，當(dāng)前頁數(shù)量加一，循環(huán)到最后一頁
在這里問題也就出現(xiàn)了，MySQL 查詢分頁 OFFSET 越深入，性能越差，初步估計(jì)線上 MCS_PROD 表中記錄在 1000w 左右
如果按照每頁 10 條，OFFSET 值會(huì)拖垮查詢性能，進(jìn)而形成一個(gè) "性能深淵"
同步類代碼針對(duì)此問題有兩種優(yōu)化方式：
- 采用游標(biāo)、流式方案進(jìn)行優(yōu)化
- 優(yōu)化深分頁性能，文章圍繞這個(gè)題目展開
軟硬件說明
MySQL VERSION
mysql>?select?version(); +-----------+ |?version()?| +-----------+ |?5.7.30????| +-----------+ 1?row?in?set?(0.01?sec)
表結(jié)構(gòu)說明
借鑒公司表結(jié)構(gòu)，字段、長(zhǎng)度以及名稱均已刪減
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)
通過測(cè)試同學(xué)幫忙造了 500w 左右數(shù)據(jù)量
mysql>?SELECT?COUNT(*)?FROM?MCS_PROD; +----------+ |?count(*)?| +----------+ |??5100000?| +----------+ 1?row?in?set?(1.43?sec)
SQL 語句如下
因?yàn)楣δ苄枰獫M足 增量拉取的方式，所以會(huì)有數(shù)據(jù)更新時(shí)間的條件查詢，以及相關(guān) 查詢排序（此處有坑）
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
重新認(rèn)識(shí) MySQL 分頁
LIMIT 子句可以被用于強(qiáng)制 SELECT 語句返回指定的記錄數(shù)。LIMIT 接收一個(gè)或兩個(gè)數(shù)字參數(shù)，參數(shù)必須是一個(gè)整數(shù)常量
如果給定兩個(gè)參數(shù)，第一個(gè)參數(shù)指定第一個(gè)返回記錄行的偏移量，第二個(gè)參數(shù)指定返回記錄行的最大數(shù)
舉個(gè)簡(jiǎn)單的例子，分析下 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)
簡(jiǎn)單說明下上面 SQL 執(zhí)行過程：
- 首先查詢了表 MCS_PROD，進(jìn)行過濾 UPDT_TIME 條件，查詢出展示列（涉及回表操作）進(jìn)行排序以及 LIMIT
- LIMIT 100000, 1 的意思是掃描滿足條件的 100001 行，然后扔掉前 100000 行
MySQL 耗費(fèi)了 大量隨機(jī) I/O 在回表查詢聚簇索引的數(shù)據(jù)上，而這 100000 次隨機(jī) I/O 查詢數(shù)據(jù)不會(huì)出現(xiàn)在結(jié)果集中
如果系統(tǒng)并發(fā)量稍微高一點(diǎn)，每次查詢掃描超過 100000 行，性能肯定堪憂，另外 LIMIT 分頁 OFFSET 越深，性能越差（多次強(qiáng)調(diào)）
圖1 數(shù)據(jù)僅供參考
深分頁優(yōu)化
關(guān)于 MySQL 深分頁優(yōu)化常見的大概有以下三種策略：
- 子查詢優(yōu)化
- 延遲關(guān)聯(lián)
- 書簽記錄
上面三點(diǎn)都能大大的提升查詢效率，核心思想就是讓 MySQL 盡可能掃描更少的頁面，獲取需要訪問的記錄后再根據(jù)關(guān)聯(lián)列回原表查詢所需要的列
子查詢優(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)
根據(jù)執(zhí)行計(jì)劃得知，子查詢 table m1 查詢是用到了索引。首先在 索引上拿到了聚集索引的主鍵 ID 省去了回表操作，然后第二查詢直接根據(jù)第一個(gè)查詢的 ID 往后再去查 10 個(gè)就可以了
圖2 數(shù)據(jù)僅供參考
延遲關(guān)聯(lián)
"延遲關(guān)聯(lián)" 深分頁優(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í)行
書簽記錄
關(guān)于 LIMIT 深分頁問題，核心在于 OFFSET 值，它會(huì) 導(dǎo)致 MySQL 掃描大量不需要的記錄行然后拋棄掉
我們可以先使用書簽 記錄獲取上次取數(shù)據(jù)的位置，下次就可以直接從該位置開始掃描，這樣可以 避免使用 OFFEST
假設(shè)需要查詢 3000000 行數(shù)據(jù)后的第 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)
好處是很明顯的，查詢速度超級(jí)快，性能都會(huì)穩(wěn)定在毫秒級(jí)，從性能上考慮碾壓其它方式
不過這種方式局限性也比較大，需要一種類似連續(xù)自增的字段，以及業(yè)務(wù)所能包容的連續(xù)概念，視情況而定
上圖是阿里云 OSS Bucket 桶內(nèi)文件列表，大膽猜測(cè)是不是可以采用書簽記錄的形式完成
ORDER BY 巨坑, 慎踩
以下言論可能會(huì)打破你對(duì) order by 所有 美好 YY
先說結(jié)論吧，當(dāng) LIMIT OFFSET 過深時(shí)，會(huì)使 ORDER BY 普通索引失效（聯(lián)合、唯一這些索引沒有測(cè)試）
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)
先來說一下這個(gè) ORDER BY 執(zhí)行過程：
- 初始化 SORT_BUFFER，放入 MCS_PROD_ID,MCS_CODE,MCS_NAME,UPDT_TIME 四個(gè)字段
- 從索引 UPDT_TIME 找到滿足條件的主鍵 ID，回表查詢出四個(gè)字段值存入 SORT_BUFFER
- 從索引處繼續(xù)查詢滿足 UPDT_TIME 條件記錄，繼續(xù)執(zhí)行步驟 2
- 對(duì) SORT_BUFFER 中的數(shù)據(jù)按照 UPDT_TIME 排序
- 排序成功后取出符合 LIMIT 條件的記錄返回客戶端
按照 UPDT_TIME 排序可能在內(nèi)存中完成，也可能需要使用外部排序，取決于排序所需的內(nèi)存和參數(shù) SORT_BUFFER_SIZE
SORT_BUFFER_SIZE 是 MySQL 為排序開辟的內(nèi)存。如果排序數(shù)據(jù)量小于 SORT_BUFFER_SIZE，排序會(huì)在內(nèi)存中完成。如果數(shù)據(jù)量過大，內(nèi)存放不下，則會(huì)利用磁盤臨時(shí)文件排序
針對(duì) SORT_BUFFER_SIZE 這個(gè)參數(shù)在網(wǎng)上查詢到有用資料比較少，大家如果測(cè)試過程中存在問題，可以加微信一起溝通
ORDER BY 索引失效舉例
OFFSET 100000 時(shí)，通過 key Extra 得知，沒有使用磁盤臨時(shí)文件排序，這個(gè)時(shí)候把 OFFSET 調(diào)整到 500000
涼涼夜色為你思念成河，化作春泥呵護(hù)著你... 一首涼涼送給寫這個(gè) SQL 的同學(xué)，發(fā)現(xiàn)了 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 表示在索引之外，需要額外進(jìn)行外部的排序動(dòng)作，性能必將受到嚴(yán)重影響
所以我們應(yīng)該 結(jié)合相對(duì)應(yīng)的業(yè)務(wù)邏輯避免常規(guī) LIMIT OFFSET，采用 # 深分頁優(yōu)化 章節(jié)進(jìn)行修改對(duì)應(yīng)業(yè)務(wù)
結(jié)言
最后有一點(diǎn)需要聲明下，MySQL 本身并不適合單表大數(shù)據(jù)量業(yè)務(wù)
因?yàn)?MySQL 應(yīng)用在企業(yè)級(jí)項(xiàng)目時(shí)，針對(duì)庫表查詢并非簡(jiǎn)單的條件，可能會(huì)有更復(fù)雜的聯(lián)合查詢，亦或者是大數(shù)據(jù)量時(shí)存在頻繁新增或更新操作，維護(hù)索引或者數(shù)據(jù) ACID 特性上必然存在性能犧牲
如果設(shè)計(jì)初期能夠預(yù)料到庫表的數(shù)據(jù)增長(zhǎng)，理應(yīng)構(gòu)思合理的重構(gòu)優(yōu)化方式，比如 ES 配合查詢、分庫分表、TiDB 等解決方式
以上就是MySQL千萬數(shù)據(jù)量深分頁優(yōu)化拒絕線上故障的詳細(xì)內(nèi)容，更多關(guān)于MySQL千萬數(shù)據(jù)深分頁優(yōu)化的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
允許遠(yuǎn)程用戶訪問mysql服務(wù)sql語句
本節(jié)主要介紹了如何允許遠(yuǎn)程用戶訪問mysql服務(wù)，本例授權(quán)192.168.14.1 主機(jī)的cakephp用戶訪問cakephp數(shù)據(jù)庫2014-07-07
解決遠(yuǎn)程連接MySQL報(bào)錯(cuò)：2003 - Can‘t connect to&nb
這篇文章主要給大家介紹了解決遠(yuǎn)程連接MySQL報(bào)錯(cuò)：2003 - Can‘t connect to MySQL server on ‘X.X.X.X‘ (10060 “Unknown error“)問題的方案,文中有詳細(xì)的解決步驟,需要的朋友可以參考下2023-09-09
MySQL8.0開啟遠(yuǎn)程連接權(quán)限的方法步驟
MySQL8.0設(shè)置遠(yuǎn)程訪問權(quán)限,找了一圈都沒找到一個(gè)適用的,索性自己寫一個(gè),這篇文章主要給大家介紹了關(guān)于MySQL8.0開啟遠(yuǎn)程連接權(quán)限的方法步驟,需要的朋友可以參考下2022-06-06
MySQL學(xué)習(xí)必備條件查詢數(shù)據(jù)
這篇文章主要介紹了MySQL學(xué)習(xí)必備條件查詢數(shù)據(jù)，首先通過利用where語句可以對(duì)數(shù)據(jù)進(jìn)行篩選展開主題相關(guān)內(nèi)容，具有一定的參考價(jià)值，需要的小伙伴可以參考一下，希望對(duì)你有所幫助2022-03-03
MySQL的默認(rèn)編碼方式是?拉丁文,如果想要設(shè)置一些漢字的數(shù)據(jù).可能會(huì)報(bào)錯(cuò).這篇文章中主要介紹了解決這個(gè)問題的方法，需要的朋友可以參考一下2023-04-04
mysql root密碼的重設(shè)方法（親測(cè)可用）
這篇文章主要介紹了如何重設(shè)mysql root密碼,需要的朋友可以參考下2014-02-02
這篇文章主要給大家分享的是MySQL語句匯總整理，圍繞MySQL語句的相關(guān)資料對(duì)其進(jìn)行整理，具有一定的參考價(jià)值，需要的小伙伴可以參考一下，希望對(duì)你有所幫助2021-12-12
JOIN操作是SQL查詢中至關(guān)重要的部分,它能夠?qū)⒍鄠€(gè)表中的數(shù)據(jù)根據(jù)指定的條件組合起來,本文主要介紹了MySQL中JOIN算法的具體使用,感興趣的可以了解一下2024-08-08
本文主要介紹了MySQL索引失效之隱式轉(zhuǎn)換的問題，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-01-01
本文主要介紹了MySQL中varchar(n) 中n最大取值為多少2024-08-08