关于后端:MySQL-千万数据库深分页查询优化拒绝线上故障

文章首发在公众号（龙台的技术笔记），之后同步到 segmentfault 和集体网站：xiaomage.info

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

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

MySQL 同步 ES 流程如下：

通过定时工作的模式触发同步，比方距离半天或一天的工夫频率
同步的模式为增量同步，依据更新工夫的机制，比方第一次同步查问 >= 1970-01-01 00:00:00.0
记录最大的更新工夫进行存储，下次更新同步以此为条件
以分页的模式获取数据，当前页数量加一，循环到最初一页

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

如果依照每页 10 条，OFFSET 值会拖垮查问性能，进而造成一个 “ 性能深渊 ”

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

采纳游标、流式计划进行优化
优化深分页性能，文章围绕这个题目开展

文章目录如下：

软硬件阐明
重新认识 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           | 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 左右数据量

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

SQL 语句如下

因为性能须要满足 增量拉取的形式 ，所以会有数据更新工夫的条件查问，以及相干 查问排序（此处有坑）

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

LIMIT 子句能够被用于强制 SELECT 语句返回指定的记录数。LIMIT 接管一个或两个数字参数，参数必须是一个整数常量

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

举个简略的例子，剖析下 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 执行过程：

首先查问了表 MCS_PROD，进行过滤 UPDT_TIME 条件，查问出展现列（波及回表操作）进行排序以及 LIMIT
LIMIT 100000, 1 的意思是扫描满足条件的 100001 行，而后扔掉前 100000 行

MySQL 消耗了 大量随机 I/O 在回表查问聚簇索引的数据上，而这 100000 次随机 I/O 查问数据不会呈现在后果集中

如果零碎并发量略微高一点，每次查问扫描超过 100000 行，性能必定堪忧，另外 LIMIT 分页 OFFSET 越深，性能越差（屡次强调）

对于 MySQL 深分页优化常见的大略有以下三种策略：

子查问优化
提早关联
书签记录

下面三点都能大大的晋升查问效率，核心思想就是让 MySQL 尽可能扫描更少的页面，获取须要拜访的记录后再依据关联列回原表查问所须要的列

子查问深分页优化语句如下：

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)

依据执行打算得悉，子查问 table m1 查问是用到了索引。首先在 索引上拿到了汇集索引的主键 ID 省去了回表操作，而后第二查问间接依据第一个查问的 ID 往后再去查 10 个就能够了

“ 提早关联 ” 深分页优化语句如下：

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)

思路以及性能与子查问优化统一，只不过采纳了 JOIN 的模式执行

对于 LIMIT 深分页问题，外围在于 OFFSET 值，它会 导致 MySQL 扫描大量不须要的记录行而后摈弃掉

咱们能够先应用书签 记录获取上次取数据的地位 ，下次就能够间接从该地位开始扫描，这样能够 防止应用 OFFEST

假如须要查问 3000000 行数据后的第 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)

益处是很显著的，查问速度超级快，性能都会稳固在毫秒级，从性能上思考碾压其它形式

不过这种形式局限性也比拟大，须要一种相似间断自增的字段，以及业务所能容纳的间断概念，视状况而定

上图是阿里云 OSS Bucket 桶内文件列表，大胆猜想是不是能够采纳书签记录的模式实现

以下舆论可能会突破你对 order by 所有 美妙 YY

先说论断吧，当 LIMIT OFFSET 过深时，会使 ORDER BY 一般索引生效（联结、惟一这些索引没有测试）

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 执行过程：

初始化 SORT_BUFFER，放入 MCS_PROD_ID,MCS_CODE,MCS_NAME,UPDT_TIME 四个字段
从索引 UPDT_TIME 找到满足条件的主键 ID，回表查问出四个字段值存入 SORT_BUFFER
从索引处持续查问满足 UPDT_TIME 条件记录，继续执行步骤 2
对 SORT_BUFFER 中的数据依照 UPDT_TIME 排序
排序胜利后取出合乎 LIMIT 条件的记录返回客户端

依照 UPDT_TIME 排序可能在内存中实现，也可能须要应用内部排序，取决于排序所需的内存和参数 SORT_BUFFER_SIZE

SORT_BUFFER_SIZE 是 MySQL 为排序开拓的内存 。如果排序数据量小于 SORT_BUFFER_SIZE，排序会在内存中实现。如果数据量过大，内存放不下， 则会利用磁盘临时文件排序

针对 SORT_BUFFER_SIZE 这个参数在网上查问到有用材料比拟少，大家如果测试过程中存在问题，能够加微信一起沟通

OFFSET 100000 时，通过 key Extra 得悉，没有应用磁盘临时文件排序，这个时候把 OFFSET 调整到 500000

一首凉凉送给写这个 SQL 的同学，发现了 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 示意在索引之外，须要额定进行内部的排序动作，性能必将受到重大影响

所以咱们应该 联合绝对应的业务逻辑防止惯例 LIMIT OFFSET，采纳 # 深分页优化 章节进行批改对应业务

最初有一点须要申明下，MySQL 自身并不适宜单表大数据量业务

因为 MySQL 利用在企业级我的项目时，针对库表查问并非简略的条件，可能会有更简单的联结查问，亦或者是大数据量时存在频繁新增或更新操作，保护索引或者数据 ACID 个性上必然存在性能就义

如果设计初期可能预料到库表的数据增长，理当构思正当的重构优化形式，比方 ES 配合查问、分库分表、TiDB 等解决形式

参考资料：

《高性能 MySQL 第三版》
《MySQL 实战 45 讲》

软硬件阐明

重新认识 MySQL 分页

深分页优化

子查问优化

提早关联

书签记录

ORDER BY 巨坑, 慎踩

ORDER BY 索引生效举例

结言