MySQL千萬(wàn)數(shù)據(jù)量深分頁(yè)優(yōu)化流程(拒絕線上故障)
引言
優(yōu)化項(xiàng)目代碼過(guò)程中發(fā)現(xiàn)一個(gè)千萬(wàn)級(jí)數(shù)據(jù)深分頁(yè)問(wèn)題,緣由是這樣的
庫(kù)里有一張耗材 MCS_PROD 表,通過(guò)同步外部數(shù)據(jù)中臺(tái)多維度數(shù)據(jù),在系統(tǒng)內(nèi)部組裝為單一耗材產(chǎn)品,最終同步到 ES 搜索引擎
MySQL 同步 ES 流程如下:
- 通過(guò)定時(shí)任務(wù)的形式觸發(fā)同步,比如間隔半天或一天的時(shí)間頻率
- 同步的形式為增量同步,根據(jù)更新時(shí)間的機(jī)制,比如第一次同步查詢 >= 1970-01-01 00:00:00.0
- 記錄最大的更新時(shí)間進(jìn)行存儲(chǔ),下次更新同步以此為條件
- 以分頁(yè)的形式獲取數(shù)據(jù),當(dāng)前頁(yè)數(shù)量加一,循環(huán)到最后一頁(yè)
在這里問(wèn)題也就出現(xiàn)了,MySQL 查詢分頁(yè) OFFSET 越深入,性能越差,初步估計(jì)線上 MCS_PROD 表中記錄在 1000w 左右
如果按照每頁(yè) 10 條,OFFSET 值會(huì)拖垮查詢性能,進(jìn)而形成一個(gè) "性能深淵"
同步類代碼針對(duì)此問(wèn)題有兩種優(yōu)化方式:
- 采用游標(biāo)、流式方案進(jìn)行優(yōu)化
- 優(yōu)化深分頁(yè)性能,文章圍繞這個(gè)題目展開
軟硬件說(shuō)明
MySQL VERSION
mysql>?select?version(); +-----------+ |?version()?| +-----------+ |?5.7.30????| +-----------+ 1?row?in?set?(0.01?sec)
表結(jié)構(gòu)說(shuō)明
借鑒公司表結(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)
通過(guò)測(cè)試同學(xué)幫忙造了 500w 左右數(shù)據(jù)量
mysql>?SELECT?COUNT(*)?FROM?MCS_PROD; +----------+ |?count(*)?| +----------+ |??5100000?| +----------+ 1?row?in?set?(1.43?sec)
SQL 語(yǔ)句如下
因?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 分頁(yè)
LIMIT 子句可以被用于強(qiáng)制 SELECT 語(yǔ)句返回指定的記錄數(shù)。LIMIT 接收一個(gè)或兩個(gè)數(shù)字參數(shù),參數(shù)必須是一個(gè)整數(shù)常量
如果給定兩個(gè)參數(shù),第一個(gè)參數(shù)指定第一個(gè)返回記錄行的偏移量,第二個(gè)參數(shù)指定返回記錄行的最大數(shù)
舉個(gè)簡(jiǎn)單的例子,分析下 SQL 查詢過(guò)程,掌握深分頁(yè)性能為什么差
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)單說(shuō)明下上面 SQL 執(zhí)行過(guò)程:
- 首先查詢了表 MCS_PROD,進(jìn)行過(guò)濾 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),每次查詢掃描超過(guò) 100000 行,性能肯定堪憂,另外 LIMIT 分頁(yè) OFFSET 越深,性能越差(多次強(qiáng)調(diào))
圖1 數(shù)據(jù)僅供參考
深分頁(yè)優(yōu)化
關(guān)于 MySQL 深分頁(yè)優(yōu)化常見的大概有以下三種策略:
- 子查詢優(yōu)化
- 延遲關(guān)聯(lián)
- 書簽記錄
上面三點(diǎn)都能大大的提升查詢效率,核心思想就是讓 MySQL 盡可能掃描更少的頁(yè)面,獲取需要訪問(wèn)的記錄后再根據(jù)關(guān)聯(lián)列回原表查詢所需要的列
子查詢優(yōu)化
子查詢深分頁(yè)優(yōu)化語(yǔ)句如下:
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è)優(yōu)化語(yǔ)句如下:
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)化一致,只不過(guò)采用了 JOIN 的形式執(zhí)行
書簽記錄
關(guān)于 LIMIT 深分頁(yè)問(wèn)題,核心在于 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í),從性能上考慮碾壓其它方式
不過(guò)這種方式局限性也比較大,需要一種類似連續(xù)自增的字段,以及業(yè)務(wù)所能包容的連續(xù)概念,視情況而定
上圖是阿里云 OSS Bucket 桶內(nèi)文件列表,大膽猜測(cè)是不是可以采用書簽記錄的形式完成
ORDER BY 巨坑, 慎踩
以下言論可能會(huì)打破你對(duì) order by 所有 美好 YY
先說(shuō)結(jié)論吧,當(dāng) LIMIT OFFSET 過(guò)深時(shí),會(huì)使 ORDER BY 普通索引失效(聯(lián)合、唯一這些索引沒(méi)有測(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)
先來(lái)說(shuō)一下這個(gè) ORDER BY 執(zhí)行過(guò)程:
- 初始化 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ù)量過(guò)大,內(nèi)存放不下,則會(huì)利用磁盤臨時(shí)文件排序
針對(duì) SORT_BUFFER_SIZE 這個(gè)參數(shù)在網(wǎng)上查詢到有用資料比較少,大家如果測(cè)試過(guò)程中存在問(wèn)題,可以加微信一起溝通
ORDER BY 索引失效舉例
OFFSET 100000 時(shí),通過(guò) key Extra 得知,沒(méi)有使用磁盤臨時(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è)優(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ì)庫(kù)表查詢并非簡(jiǎn)單的條件,可能會(huì)有更復(fù)雜的聯(lián)合查詢,亦或者是大數(shù)據(jù)量時(shí)存在頻繁新增或更新操作,維護(hù)索引或者數(shù)據(jù) ACID 特性上必然存在性能犧牲
如果設(shè)計(jì)初期能夠預(yù)料到庫(kù)表的數(shù)據(jù)增長(zhǎng),理應(yīng)構(gòu)思合理的重構(gòu)優(yōu)化方式,比如 ES 配合查詢、分庫(kù)分表、TiDB 等解決方式
以上就是MySQL千萬(wàn)數(shù)據(jù)量深分頁(yè)優(yōu)化拒絕線上故障的詳細(xì)內(nèi)容,更多關(guān)于MySQL千萬(wàn)數(shù)據(jù)深分頁(yè)優(yōu)化的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
MySQL刪除數(shù)據(jù),表文件大小依然沒(méi)變的原因
這篇文章主要介紹了MySQL刪除數(shù)據(jù),表文件大小依然沒(méi)變的原因,幫助大家更好的理解MySQL中的數(shù)據(jù)表,感興趣的朋友可以了解下2020-10-10CentOS7下二進(jìn)制安裝mysql 5.7.23
這篇文章主要為大家詳細(xì)介紹了CentOS7下二進(jìn)制安裝mysql 5.7.23,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2019-06-06mysql創(chuàng)建用戶并賦予用戶權(quán)限詳細(xì)操作教程
這篇文章主要給大家介紹了關(guān)于mysql創(chuàng)建用戶并賦予用戶權(quán)限詳細(xì)操作的相關(guān)資料,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-12-12MySQL數(shù)據(jù)庫(kù)主從復(fù)制與讀寫分離
大家好,本篇文章主要講的是MySQL數(shù)據(jù)庫(kù)主從復(fù)制與讀寫分離,感興趣的同學(xué)趕快來(lái)看一看吧,對(duì)你有幫助的話記得收藏一下,方便下次瀏覽2021-12-12MySQL修改表結(jié)構(gòu)操作命令總結(jié)
這篇文章主要介紹了MySQL修改表結(jié)構(gòu)操作命令總結(jié),包含如刪除列、添加列、修改列、添加主鍵、刪除主鍵、添加唯一索引、添加普通索引等內(nèi)容,需要的朋友可以參考下2014-12-12