欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL千萬(wàn)數(shù)據(jù)量深分頁(yè)優(yōu)化流程(拒絕線上故障)

 更新時(shí)間：2023年05月16日 09:24:19   作者：馬丁玩編程  
這篇文章主要為大家介紹了MySQL千萬(wàn)數(shù)據(jù)量深分頁(yè)優(yōu)化拒絕線上故障，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

引言

優(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)文章

最新評(píng)論