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

最新推荐文章于 2024-09-14 14:52:53 发布
最新推荐文章于 2024-09-14 14:52:53 发布
阅读量993 收藏 10
点赞数 16
分类专栏： 程序员 文章标签： mysql android adb
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2301_76348171/article/details/137961568
版权

通过测试同学帮忙造了 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 |
±--------------±------------------------±-----------------±--------------------+
1 row in set (3.66 sec)

mysql> EXPLAIN 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;
±—±------------±---------±-----------±------±--------------±-----------±--------±-----±--------±---------±----------------------+
| 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 越深，性能越差（多次强调）

图1 数据仅供参考

深分页优化

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

  1. 子查询优化
  2. 延迟关联
  3. 书签记录

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

子查询优化

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

mysql> SELECT MCS_PROD_ID_A,MCS_CODE_B,MCS_NAME_C FROM MCS_PROD WHERE MCS_PROD_ID >= ( SELECT m1.MCS_PROD_ID_A 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_A | MCS_CODE_B | MCS_NAME_C |
±--------------±------------------------±-----------------------+
| 3021401 | XA892010009391491861476 | 金属解剖型接骨板T型接骨板A |
±--------------±------------------------±-----------------------+
1 row in set (0.76 sec)

mysql> EXPLAIN SELECT MCS_PROD_ID_A,MCS_CODE_B,MCS_NAME_C FROM MCS_PROD WHERE MCS_PROD_ID_A >= ( SELECT m1.MCS_PROD_ID_A 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 个就可以了

图2 数据仅供参考

延迟关联

“延迟关联” 深分页优化语句如下：

mysql> SELECT MCS_PROD_ID_A,MCS_CODE_B,MCS_NAME_C FROM MCS_PROD INNER JOIN (SELECT m1.MCS_PROD_ID_A 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_A);
±--------------±------------------------±-----------------------+
| MCS_PROD_ID_A | MCS_CODE_B | MCS_NAME_C |
±--------------±------------------------±-----------------------+
| 3021401 | XA892010009391491861476 | 金属解剖型接骨板T型接骨板A |
±--------------±------------------------±-----------------------+
1 row in set (0.75 sec)

mysql> EXPLAIN SELECT MCS_PROD_ID_A,MCS_CODE_B,MCS_NAME_C FROM MCS_PROD INNER JOIN (SELECT m1.MCS_PROD_ID_A 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 | | 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_A,MCS_CODE_B,MCS_NAME_C FROM MCS_PROD WHERE MCS_PROD_ID_A < 3000000 ORDER BY UPDT_TIME LIMIT 1;
±--------------±------------------------±--------------------------------+
| MCS_PROD_ID_A | MCS_CODE_B | MCS_NAME_C |
±--------------±------------------------±--------------------------------+
| 127 | XA683240878449276581799 | 股骨近端-1螺纹孔锁定板（纯钛）YJBL01 |
±--------------±------------------------±--------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT MCS_PROD_ID_A,MCS_CODE_B,MCS_NAME_C FROM MCS_PROD WHERE MCS_PROD_ID_A < 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)

好处是很明显的，查询速度超级快，性能都会稳定在毫秒级，从性能上考虑碾压其它方式

不过这种方式局限性也比较大，需要一种类似连续自增的字段，以及业务所能包容的连续概念，视情况而定

image

上图是阿里云 OSS Bucket 桶内文件列表，大胆猜测是不是可以采用书签记录的形式完成

ORDER BY 巨坑, 慎踩

以下言论可能会打破你对 order by 所有 美好 YY

先说结论吧，当 LIMIT OFFSET 过深时，会使 ORDER BY 普通索引失效（联合、唯一这些索引没有测试）

mysql> EXPLAIN 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 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_A,MCS_CODE_B,MCS_NAME_C,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_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 500000, 1;
±—±------------±---------±-----------±-----±--------------±-----±--------±-----±--------±---------±----------------------------+
自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。img

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

img

Java核心架构进阶知识点

面试成功其实都是必然发生的事情，因为在此之前我做足了充分的准备工作，不单单是纯粹的刷题，更多的还会去刷一些Java核心架构进阶知识点，比如：JVM、高并发、多线程、缓存、Spring相关、分布式、微服务、RPC、网络、设计模式、MQ、Redis、MySQL、设计模式、负载均衡、算法、数据结构、kafka、ZK、集群等。而这些也全被整理浓缩到了一份pdf——《Java核心架构进阶知识点整理》，全部都是精华中的精华，本着共赢的心态，好东西自然也是要分享的

image

image

image

内容颇多，篇幅却有限，这就不在过多的介绍了，大家可根据以上截图自行脑补
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！
[外链图片转存中…(img-Rs7irVXD-1713504061667)]

[外链图片转存中…(img-uJWPozoO-1713504061669)]

内容颇多，篇幅却有限，这就不在过多的介绍了，大家可根据以上截图自行脑补
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！

2301_76348171
关注
专栏目录
互联网寒冬！MySQL-千万数据量深分页优化，拒绝线上故障
m0_46608024的博客
08-05 123
结言 软硬件说明 MySQL VERSION mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.30 | +-----------+ 1 row in set (0.01 sec) 表结构说明 借鉴公司表结构，字段、长度以及名称均已删减 mysql> DESC MCS_PROD; +-----------------------+--------------+------+----..
数据查询深分页优化方案
冰河的专栏
06-08 549
最近不少小伙伴在实际工作过程中，遇到了单表大数据量分页的问题，问我怎么优化分页查询。其实，这就是典型的深分页问题。今天趁着周末，给大家整理一些在深分页场景的简单处理方案。
MySQL千万级数据分页的优化
最新发布
Leighteen
09-14 638
MySQL千万级数据分页的优化
MySQL深度分页优化
JavaMa的博客
05-16 6835
Mysql深度分页优化 1. 背景 mysql使用select * from table limit offset, rows分页在深度分页的情况下, 性能急剧下降。 例如：select * 的情况下直接⽤limit 600000，10 扫描的是约60万条数据，并且是需要回表 60W次，也就是说⼤部分性能都耗在随机访问上，到头来只⽤到10条数据(总共取600010条数据只留10条记录) 2. 优化 2.1 业务层面限制跨度比较大的跳页 百度和Google方案: [外链图片转存失败,源站可能有防盗链机制,
MySQL 千万数据量深分页优化, 拒绝线上故障！
Long-Tai
12-25 2816
前言 优化项目代码过程中发现一个千万级数据深分页问题，缘由是这样的 库里有一张耗材 MCS_PROD 表，通过同步外部数据中台多维度数据，在系统内部组装为单一耗材产品，最终同步到 ES 搜索引擎 MySQL 同步 ES 流程如下： 通过定时任务的形式触发同步，比如间隔半天或一天的时间频率 同步的形式为增量同步，根据更新时间的机制，比如第一次同步查询 >= 1970-01-01 00:00:00.0 记录最大的更新时间进行存储，下次更新同步以此为条件 以分页的形式获取数据，当前页数量加一，循环..
深分页优化总结
qq_32979219的博客
08-08 3002
深分页
MySQL-千万数据量深分页优化，拒绝线上故障，Java中级工程师面试题及答案
m0_60635084的博客
09-08 137
* 书签记录 ORDER BY 巨坑，慎踩 ORDER BY 索引失效举例 结言 软硬件说明 MySQL VERSION mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.30 | +-----------+ 1 row in set (0.01 sec) 表结构说明 借鉴公司表结构，字段、长度以及名称均已删减 mysql> DESC MCS_PROD; +------.
MySQL 千万数据量深分页优化，拒绝线上故障
m0_63550462的博客
10-31 223
优化项目代码过程中发现一个千万级数据深分页问题，缘由是这样的 库里有一张耗材 MCS_PROD 表，通过同步外部数据中台多维度数据，在系统内部组装为单一耗材产品，最终同步到 ES 搜索引擎 MySQL 同步 ES 流程如下： 通过定时任务的形式触发同步，比如间隔半天或一天的时间频率 同步的形式为增量同步，根据更新时间的机制，比如第一次同步查询 >= 1970-01-01 00:00:00.0 记录最大的更新时间进行存储，下次更新同步以此为条件 以分页的形式获取数据，当前页数量加一，循环到最
MySQL 千万数据量深分页优化，拒绝线上故障，腾讯T4架构师
2401_83621136的博客
04-04 789
同步的形式为增量同步，根据更新时间的机制，比如第一次同步查询 >= 1970-01-01 00:00:00.0记录最大的更新时间进行存储，下次更新同步以此为条件以分页的形式获取数据，当前页数量加一，循环到最后一页在这里问题也就出现了，，初步估计线上 MCS_PROD 表中记录在 1000w 左右如果按照每页 10 条，OFFSET 值会拖垮查询性能，进而形成一个一、软硬件说明借鉴公司表结构，字段、长度以及名称均已删减通过测试同学帮忙造了 500w 左右数据量因为功能需要满足。
深度分页问题分析和优化
huhu123444的博客
03-12 8841
目录数据集深度分页存在的问题深度分页优化 数据集 test_db下载 :https://gitee.com/lzjcnb/test_db 数据集安装：mysql -uroot -p -t < employees.sql 深度分页存在的问题 查询表employess表结果集中第[N,N+m]跳记录，先查看下数据集大小： 分别查询[10000,10003]三条记录和[100000,100003]三条记录，并观察执行时间。 mysql> select * from employees limit 1
mysql分页之limit优化技巧
03-20
本文档针对mysql分页之limit慢的问题，使用联合索引在大数据量的情况下优化limit分页的性能
MySQL深分页，limit 100000,10 优化
qq_44700578的博客
06-05 1673
MySQL深分页，limit 100000,10 优化
分页（优化）
越努力越幸运
08-14 990
首先来说一下${param.name} 调用pager-taglib.jar包 只需要传入每页显示的行数pagesize,总信息行数count,再传一个路径连接数据库。 &lt;td height="30"&gt; &lt;!-- 分页属性设置 --&gt; &lt;jsp:include page="/comeon/pg.jsp"&gt; &lt;jsp:...
【教3妹学mysql】复杂的深度分页问题优化
kangbin825的专栏
09-03 646
2哥，你有没有看到新闻，上海地铁6号线有女子穿内衣在地铁内拍照。：什么？还有这事，我还经常坐6号线呢，我怎么就没遇到呢。：你得了吧，这种不雅的低俗行为，应该坚决抵制。：是的，我也就是开个玩笑，3妹别激动。这种行为明显扰乱了公共秩序。：嗯！！！话说2哥你在干嘛呢。：在学习Mysql, 遇到了一个深度分页的问题，再想如何解决。：我也要一起学习下~ 教我教我。
如何优化深分页limit（深入精讲）
越努力越幸运。
01-03 1205
其中，offset 表示起始偏移量，row_count 表示要返回的行数。在执行 SELECT 查询时，MySQL首先会先扫描整个表或使用索引，找到所有符合 WHERE 条件的记录。这个过程需要将所有记录都读入内存，然后根据 LIMIT 子句的指定返回查询结果集中的一部分。例如以下语句表示从第 10001 条数据开始返回 10 条数据。从上面结果可以得出结论：数据量越大，花费时间越长。从上面结果可以得出结论：偏移量越大，花费时间越长。
MYSQL深度分页的常见优化方案
白云
02-28 2523
MYSQL深度分页的常见优化方案
MySQL深度分页的问题及优化方案：千万级数据量如何快速分页
热门推荐
少侠露飞的学习笔记
09-07 1万+
字节跳动面试官问我海量数据分页如何展示
如何优化深分页limit 1000000
越努力越幸运。
01-03 623
深分页问题是 MySQL 中常见的性能问题，当你尝试获取大量数据的后续页面时，性能会显著下降。这是因为 MySQL 需要先扫描到指定的偏移量，然后再返回数据。例如，你可以提供搜索功能，让用户快速找到他们需要的数据，而不是浏览大量的页面。如果你的应用程序可以记住上次查询的最后一个 ID，那么你可以使用 WHERE 子句来避免扫描大量数据。覆盖索引可以让 MySQL 在索引中获取所有需要的数据，而无需回表查询。这是因为 MySQL 需要先扫描 1000000 行数据，然后再返回后面的 10 行数据。
camtasia2022
04-30
Camtasia 2022是一款广受欢迎的屏幕录制和视频编辑软件，它可以帮助用户轻松地录制和编辑视频。Camtasia 2022具有直观的界面和丰富的功能，包括视频剪辑、特效、转场、音频处理等，还支持将录制内容制作成教程、演示文稿、培训视频等不同类型的视频。此外，Camtasia 2022还具有强大的导出功能，可以将视频输出为各种格式，如MP4、AVI、WMV、MOV等。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值