MySQL-千万数据量深分页优化，拒绝线上故障！

前言

---

优化项目代码过程中发现一个千万级数据深分页问题，缘由是这样的

库里有一张耗材 MCS_PROD 表，通过同步外部数据中台多维度数据，在系统内部组装为单一耗材产品，最终同步到 ES 搜索引擎

MySQL 同步 ES 流程如下：

1. 通过定时任务的形式触发同步，比如间隔半天或一天的时间频率
2. 同步的形式为增量同步，根据更新时间的机制，比如第一次同步查询 >= 1970-01-01 00:00:00.0
3. 记录最大的更新时间进行存储，下次更新同步以此为条件
4. 以分页的形式获取数据，当前页数量加一，循环到最后一页

在这里问题也就出现了，MySQL 查询分页 OFFSET 越深入，性能越差，初步估计线上 MCS_PROD 表中记录在 1000w 左右

如果按照每页 10 条，OFFSET 值会拖垮查询性能，进而形成一个 "性能深渊"

同步类代码针对此问题有两种优化方式：

1. 采用游标、流式方案进行优化
2. 优化深分页性能，文章围绕这个题目展开

文章目录如下：

• 软硬件说明
• 重新认识 MySQL 分页
• 深分页优化
  • 子查询优化
  • 延迟关联
  • 书签记录
• ORDER BY 巨坑，慎踩
  • ORDER BY 索引失效举例
• 结言

软硬件说明

---

MySQL VERSION

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

表结构说明

借鉴公司表结构，字段、长度以及名称均已删减

mysql> DESC MCS_PROD;
+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| MCS_PROD_ID_A         | int(11)      | NO   | PRI | NULL    | auto_increment |
| MCS_CODE_B            | varchar(100) | YES  |     |         |                |
| MCS_NAME_C            | varchar(500) | YES  |     |         |                |
| UPDT_TIME             | datetime     | NO   | MUL | NULL    |                |
+-----------------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

通过测试同学帮忙造了 500w 左右数据量

mysql> SELECT COUNT(*) FROM MCS_PROD;
+----------+
| count(*) |
+----------+
|  5100000 |
+----------+
1 row in set (1.43 sec)

SQL 语句如下

因为功能需要满足 增量拉取的方式，所以会有数据更新时间的条件查询，以及相关 查询排序（此处有坑）

SELECT
	MCS_PROD_ID_A,
	MCS_CODE_B,
	MCS_NAME_C,
	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 语句返回指定的记录数。LIMIT 接收一个或两个数字参数，参数必须是一个整数常量

如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数

举个简单的例子，分析下 SQL 查询过程，掌握深分页性能为什么差

mysql> SELECT MCS_PROD_ID_A,MCS_CODE_B,MCS_NAME_C FROM MCS_PROD WHERE (UPDT_TIME >= '1970-01-01 00:00:00.0') ORDER BY UPDT_TIME LIMIT 100000, 1;
+---------------+-------------------------+------------------+---------------------+
| MCS_PROD_ID_A | MCS_CODE_B              | MCS_NAME_C       | UPDT_TIME           |
+---------------+-------------------------+------------------+---------------------+
|      181789   | XA601709733186213015031 | 尺、桡骨LC-DCP骨板 | 2020-10-19 16:22:19 |
+---------------+-----------------