優(yōu)化項目代碼過程中發(fā)現(xiàn)一個千萬級數(shù)據(jù)深分頁問題扒最，緣由是這樣的
庫里有一張耗材 MCS_PROD 表，通過同步外部數(shù)據(jù)中臺多維度數(shù)據(jù)华嘹，在系統(tǒng)內(nèi)部組裝為單一耗材產(chǎn)品吧趣，最終同步到 ES 搜索引擎
MySQL 同步 ES 流程如下：
- 通過定時任務的形式觸發(fā)同步，比如間隔半天或一天的時間頻率
- 同步的形式為增量同步耙厚，根據(jù)更新時間的機制强挫，比如第一次同步查詢 >= 1970-01-01 00:00:00.0
- 記錄最大的更新時間進行存儲，下次更新同步以此為條件
- 以分頁的形式獲取數(shù)據(jù)薛躬，當前頁數(shù)量加一俯渤，循環(huán)到最后一頁
在這里問題也就出現(xià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)
表結(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)
通過測試同學幫忙造了 500w 左右數(shù)據(jù)量
mysql> SELECT COUNT(*) FROM MCS_PROD;
+----------+
| count(*) |
+----------+
| 5100000 |
+----------+
1 row in set (1.43 sec)
SQL 語句如下
因為功能需要滿足 增量拉取的方式，所以會有數(shù)據(jù)更新時間的條件查詢指巡，以及相關 查詢排序（此處有坑）
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 語句返回指定的記錄數(shù)。LIMIT 接收一個或兩個數(shù)字參數(shù)厌处，參數(shù)必須是一個整數(shù)常量
如果給定兩個參數(shù)，第一個參數(shù)指定第一個返回記錄行的偏移量岁疼，第二個參數(shù)指定返回記錄行的最大數(shù)
舉個簡單的例子阔涉，分析下 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 在回表查詢聚簇索引的數(shù)據(jù)上椭住，而這 100000 次隨機 I/O 查詢數(shù)據(jù)不會出現(xiàn)在結(jié)果集中
如果系統(tǒng)并發(fā)量稍微高一點，每次查詢掃描超過 100000 行字逗，性能肯定堪憂京郑，另外 LIMIT 分頁 OFFSET 越深，性能越差（多次強調(diào)）
三葫掉、深分頁優(yōu)化
關于 MySQL 深分頁優(yōu)化常見的大概有以下三種策略：
- 子查詢優(yōu)化
- 延遲關聯(lián)
- 書簽記錄
上面三點都能大大地提升查詢效率些举，核心思想就是讓 MySQL 盡可能掃描更少的頁面，獲取需要訪問的記錄后再根據(jù)關聯(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í)行計劃得知俭厚，子查詢 table m1 查詢是用到了索引户魏。首先在 索引上拿到了聚集索引的主鍵 ID 省去了回表操作，然后第二查詢直接根據(jù)第一個查詢的 ID 往后再去查 10 個就可以了
3.2 延遲關聯(liá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 書簽記錄
關于 LIMIT 深分頁問題叼丑，核心在于 OFFSET 值，它會 導致 MySQL 掃描大量不需要的記錄行然后拋棄掉
我們可以先使用書簽 記錄獲取上次取數(shù)據(jù)的位置扛门，下次就可以直接從該位置開始掃描鸠信，這樣可以 避免使用 OFFEST
假設需要查詢 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)
好處是很明顯的尖飞，查詢速度超級快症副，性能都會穩(wěn)定在毫秒級店雅，從性能上考慮碾壓其它方式
不過這種方式局限性也比較大，需要一種類似連續(xù)自增的字段贞铣，以及業(yè)務所能包容的連續(xù)概念闹啦，視情況而定
上圖是阿里云 OSS Bucket 桶內(nèi)文件列表，大膽猜測是不是可以采用書簽記錄的形式完成
四辕坝、ORDER BY 巨坑, 慎踩
以下言論可能會打破你對 order by 所有 美好 YY
先說結(jié)論吧窍奋，當 LIMIT OFFSET 過深時，會使 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)
先來說一下這個 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 中的數(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碎紊，排序會在內(nèi)存中完成。如果數(shù)據(jù)量過大樊诺，內(nèi)存放不下仗考，則會利用磁盤臨時文件排序
針對 SORT_BUFFER_SIZE 這個參數(shù)在網(wǎng)上查詢到有用資料比較少，大家如果測試過程中存在問題词爬，可以加微信一起溝通
4.1 ORDER BY 索引失效舉例
OFFSET 100000 時秃嗜，通過 key Extra 得知，沒有使用磁盤臨時文件排序顿膨，這個時候把 OFFSET 調(diào)整到 500000
涼涼夜色為你思念成河锅锨，化作春泥呵護著你... 一首涼涼送給寫這個 SQL 的同學，發(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 表示在索引之外恋沃，需要額外進行外部的排序動作橡类，性能必將受到嚴重影響
所以我們應該 結(jié)合相對應的業(yè)務邏輯避免常規(guī) LIMIT OFFSET，采用 # 深分頁優(yōu)化 章節(jié)進行修改對應業(yè)務
結(jié)言
最后有一點需要聲明下芽唇，MySQL 本身并不適合單表大數(shù)據(jù)量業(yè)務
因為 MySQL 應用在企業(yè)級項目時顾画，針對庫表查詢并非簡單的條件，可能會有更復雜的聯(lián)合查詢匆笤，亦或者是大數(shù)據(jù)量時存在頻繁新增或更新操作研侣，維護索引或者數(shù)據(jù) ACID 特性上必然存在性能犧牲
如果設計初期能夠預料到庫表的數(shù)據(jù)增長，理應構(gòu)思合理的重構(gòu)優(yōu)化方式炮捧，比如 ES 配合查詢庶诡、分庫分表、TiDB 等解決方式
