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

42次阅读

共计 10122 个字符，预计需要花费 26 分钟才能阅读完成。

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

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

库里有一张耗材 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           | 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

重新认识 MySQL 分页

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

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

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

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

深分页优化

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

  1. 子查问优化
  2. 提早关联
  3. 书签记录

下面三点都能大大的晋升查问效率，核心思想就是让 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 巨坑, 慎踩

以下舆论可能会突破你对 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 执行过程：

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

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

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

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

ORDER BY 索引生效举例

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 等解决形式

参考资料：

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

正文完
后端
发表至： 后端
2022-06-17
 0
关于后端:面试突击24为什么wait和notify必须放在synchronized中
关于后端:elixir-0062-快速赋值小技巧
关于后端:一分钟学一个-Linux-命令-mkdir-和-touch
关于后端:CPSC-221-PrairieLearn