Skip to main content

06小节：优惠券分发失败记录深分页优化

作者：程序员马丁

在线博客：https://nageoffer.com

note

热门项目实战社群，收获国内众多知名公司面试青睐，近千名同学面试成功！助力你在校招或社招上拿个offer。

优惠券分发失败记录深分页优化，元数据信息：

©版权所有 - 拿个offer-开源&项目实战星球专属学习项目，依据《中华人民共和国著作权法实施条例》和《知识星球产权保护》，严禁未经本项目原作者明确书面授权擅自分享至 GitHub、Gitee 等任何开放平台。违者将面临法律追究。

内容摘要：介绍了深分页的业务场景，同时呢介绍三种深分页的解决方案，其中书签记录的方式在应对深分页场景下性能最高，在模拟的 4000 万行记录中依然能够保持微秒查询性能。最后将分发失败的用户领券记录保存到了 Excel 中，并赋值到优惠券模板分发记录任务中。

课程目录如下所示：

  • 业务背景
  • 什么是深分页？
  • Git 分支
  • 生成测试数据复现深分页
  • 如何解决深分页？
  • 分发失败记录生成 Excel
  • 文末总结

业务背景​

在执行优惠券分发时，对于因库存不足或用户已领取过优惠券而导致的分发失败情况，我们会记录这些失败的分发记录。从企业的实际业务需求来看，这些记录可以通过后台分页查询，或者在分发完成后将失败记录写入 Excel 供运营人员查看。显然，后者更为优越，因为这些失败记录存储在数据库中价值不大，将其保存到 Excel 文件中不仅方便查看，还能节省数据库的存储空间。

我们还需要考虑一个问题：错误分发的情况会很多吗？实际上，这种情况并不常见。但只要是人为操作，总可能出现漏洞，例如未检查优惠券模板的库存，或者提取的用户数据有误（用户已领取过优惠券）。在这种极端情况下，我们需要通过分页的情况 limit offset, count 的形式进行按批次读取，例如下述 SQL：

select * from t_coupon_task_fail where batch_id = 'xxxxxx' limit 0, 1000;

然而，许多人常常忽视一个问题：当 MySQL 在处理分页很深的数据时，尤其是读取大数据量中的靠后部分时，会遇到深度分页问题。

什么是深分页？​

MySQL 的深分页问题指的是在使用分页查询时，随着页数的增加，查询的效率会显著降低，尤其是对于大量数据的表。这通常发生在使用 LIMIT 子句进行分页时，例如 LIMIT offset, count。当 offset 值很大时，MySQL 需要扫描和跳过大量的行，这会导致查询变慢。

对于 SELECT * FROM table LIMIT 1000000, 10 这样的查询，MySQL 会扫描前 1000000 条记录，然后丢弃它们，只返回接下来的 10 条。这意味着即使最终结果只包含 10 条记录，MySQL 仍然需要处理并丢弃大量的行，导致查询时间随着 offset 增大而增加。

Git 分支​

20240904_dev_coupon-distribute-v3_limit-page_ding.ma

生成测试数据复现深分页​

我们通过存储过程模拟 100 万的单个批次错误记录，确保数据有个偏真实的状态。主要是数据量小了模拟不出来深分页的效果。

DELIMITER $$

CREATE PROCEDURE insert_coupon_task_fail_records()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE batch_size INT DEFAULT 1000; -- 每批插入1000条记录
    DECLARE json_data TEXT;

    WHILE i <= 1000000 DO
        -- 开启一个事务
        START TRANSACTION;

        -- 执行批量插入
        SET @end = i + batch_size - 1;
        WHILE i <= @end DO
            SET json_data = CONCAT('{"key": "value_', i, '"}');
            INSERT INTO `t_coupon_task_fail` (`batch_id`, `json_object`)
            VALUES (1830889785603571980, json_data);
            SET i = i + 1;
        END WHILE;

        -- 提交事务
        COMMIT;
    END WHILE;
END$$

DELIMITER ;

通过执行这个存储过程，模拟出 100 万记录，大概执行在 10 秒以上。

CALL insert_coupon_task_fail_records();

我们先来看看执行查询前 10 条数据 SQL 执行时间是多久，根据执行返回结果来看，返回大概在 500 µs 左右。

select * from t_coupon_task_fail where batch_id = '1830889785603571980' limit 0, 10;

如果查询最后几条数据，那么这个 SQL 的平均效应时间是 600 ms，相对于前面的数据性能相差了近 1200 倍！这个数据是很恐怖的。

select * from t_coupon_task_fail where batch_id = '1830889785603571980' limit 999990, 10;

1 毫秒（ms） = 1000 微秒（µs）

如果我们多执行几次存储过程，让数据来到 500 万，查询性能来到了惊人的近 5 秒。所以，大家如果在项目中遇到类似于这种大数据量深分页的情况，一定要特别注意，稍微不小心就是性能深渊。

深分页主要问题不是读取某一次性能差，而是我们读取都后面的所有记录都很慢，越到后面越慢。举个例子，假设我们共需要去数据库读取 100 万数据，每次读取 1000 条，可能到 50 万就开始明显变慢了，最后就会演变成会慢 50万 / 1000 次。

如何解决深分页？​

为了方便大家很直观能理解不同深分页的性能差距，我们接下来的示例会按照 500 万数据的基数进行测试。

1. 子查询优化​

我们通过子查询的方式进行优化，优化语句如下：

mysql> select * from t_coupon_task_fail where id >=
    ->
    -> (SELECT id
    ->     FROM t_coupon_task_fail
    ->     WHERE batch_id = '1830889785603571980'
    ->     ORDER BY id
    ->     LIMIT 4999990, 1
    -> )
    ->
    -> limit 10;
+---------+---------------------+--------------------------+
| id      | batch_id            | json_object              |
+---------+---------------------+--------------------------+
| 4999991 | 1830889785603571980 | {"key": "value_999991"}  |
| 4999992 | 1830889785603571980 | {"key": "value_999992"}  |
| 4999993 | 1830889785603571980 | {"key": "value_999993"}  |
| 4999994 | 1830889785603571980 | {"key": "value_999994"}  |
| 4999995 | 1830889785603571980 | {"key": "value_999995"}  |
| 4999996 | 1830889785603571980 | {"key": "value_999996"}  |
| 4999997 | 1830889785603571980 | {"key": "value_999997"}  |
| 4999998 | 1830889785603571980 | {"key": "value_999998"}  |
| 4999999 | 1830889785603571980 | {"key": "value_999999"}  |
| 5000000 | 1830889785603571980 | {"key": "value_1000000"} |
+---------+---------------------+--------------------------+
10 rows in set (0.61 sec)

可以看到，同样的 SQL 语句，性能从近 5 秒下降到了 610 毫秒。

解锁付费内容，👉 戳​

Table of Contents