MySQL 千萬數(shù)據(jù)量深分頁優(yōu)化，拒絕線上故障！

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

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

MySQL 同步 ES 流程如下：

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

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

如果按照每頁 10 條，OFFSET 值會(huì)拖垮查詢性能，進(jìn)而形成一個(gè) "性能深淵"

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

  1. 采用游標(biāo)、流式方案進(jìn)行優(yōu)化
  2. 優(yōu)化深分頁性能，文章圍繞這個(gè)題目展開
image

一、軟硬件說明

MySQL VERSION

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

表結(jié)構(gòu)說明

借鑒公司表結(jié)構(gòu)，字段、長度以及名稱均已刪減

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)

通過測試同學(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è)簡單的例子，分析下 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í)行過程：

  1. 首先查詢了表 MCS_PROD，進(jìn)行過濾 UPDT_TIME 條件，查詢出展示列（涉及回表操作）進(jìn)行排序以及 LIMIT
  2. 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ù)僅供參考.png

三、深分頁優(yōu)化

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

  1. 子查詢優(yōu)化
  2. 延遲關(guān)聯(lián)
  3. 書簽記錄

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

3.1 子查詢優(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ù)僅供參考.png

3.2 延遲關(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í)行

3.3 書簽記錄

關(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)

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

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

image.png

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

四、ORDER BY 巨坑, 慎踩

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

先說結(jié)論吧，當(dāng) LIMIT OFFSET 過深時(shí)，會(huì)使 ORDER BY 普通索引失效（聯(lián)合、唯一這些索引沒有測試）

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í)行過程：

  1. 初始化 SORT_BUFFER，放入 MCS_PROD_ID,MCS_CODE,MCS_NAME,UPDT_TIME 四個(gè)字段
  2. 從索引 UPDT_TIME 找到滿足條件的主鍵 ID，回表查詢出四個(gè)字段值存入 SORT_BUFFER
  3. 從索引處繼續(xù)查詢滿足 UPDT_TIME 條件記錄，繼續(xù)執(zhí)行步驟 2
  4. 對 SORT_BUFFER 中的數(shù)據(jù)按照 UPDT_TIME 排序
  5. 排序成功后取出符合 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í)文件排序

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

4.1 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é)合相對應(yīng)的業(yè)務(wù)邏輯避免常規(guī) LIMIT OFFSET，采用 # 深分頁優(yōu)化 章節(jié)進(jìn)行修改對應(yīng)業(yè)務(wù)

結(jié)言

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

因?yàn)?MySQL 應(yīng)用在企業(yè)級項(xiàng)目時(shí)，針對庫表查詢并非簡單的條件，可能會(huì)有更復(fù)雜的聯(lián)合查詢，亦或者是大數(shù)據(jù)量時(shí)存在頻繁新增或更新操作，維護(hù)索引或者數(shù)據(jù) ACID 特性上必然存在性能犧牲

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

原文鏈接：https://mp.weixin.qq.com/s/i3wLeCSxqWKrTwgtfelumQ

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容