MySQL 千万数据量深分页优化，拒绝线上故障，2024年最新月薪20k+的Java面试都问些什么

3. 记录最大的更新时间进行存储，下次更新同步以此为条件

4. 以分页的形式获取数据，当前页数量加一，循环到最后一页

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

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

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

1. 采用游标、流式方案进行优化

2. 优化深分页性能，文章围绕这个题目展开

一、软硬件说明

---

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

个人整理了一些资料，有需要的朋友可以直接点击领取。

25大Java面试专题（附解析）

从0到1Java学习路线和资料

Java核心知识集

MySQL王者晋级之路

二、重新认识 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 越深，性能越差（多次强调）

图1 数据仅供参考

三、深分页优化

---

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

1. 子查询优化

2. 延迟关联

3. 书签记录

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

3.1 子查询优化

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

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 个就可以了

图2 数据仅供参考

3.2 延迟关联

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

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 |

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

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

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

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

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

如果你觉得这些内容对你有帮助，可以添加V获取：vip1024b （备注Java）

写在最后

作为一名即将求职的程序员，面对一个可能跟近些年非常不同的 2019 年，你的就业机会和风口会出现在哪里？在这种新环境下，工作应该选择大厂还是小公司？已有几年工作经验的老兵，又应该如何保持和提升自身竞争力，转被动为主动？

就目前大环境来看，跳槽成功的难度比往年高很多。一个明显的感受：今年的面试，无论一面还是二面，都很考验Java程序员的技术功底。

最近我整理了一份复习用的面试题及面试高频的考点题及技术点梳理成一份“Java经典面试问题（含答案解析）.pdf和一份网上搜集的“Java程序员面试笔试真题库.pdf”（实际上比预期多花了不少精力），包含分布式架构、高可扩展、高性能、高并发、Jvm性能调优、Spring，MyBatis，Nginx源码分析，Redis，ActiveMQ、Mycat、Netty、Kafka、Mysql、Zookeeper、Tomcat、Docker、Dubbo、Nginx等多个知识点高级进阶干货！

由于篇幅有限，为了方便大家观看，这里以图片的形式给大家展示部分的目录和答案截图！

Java经典面试问题（含答案解析）

阿里巴巴技术笔试心得

一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

nx等多个知识点高级进阶干货！**

由于篇幅有限，为了方便大家观看，这里以图片的形式给大家展示部分的目录和答案截图！
[外链图片转存中…(img-ZUJDM6KW-1712801216370)]

Java经典面试问题（含答案解析）

[外链图片转存中…(img-g4w6iW3c-1712801216371)]

阿里巴巴技术笔试心得

[外链图片转存中…(img-FMfsEb9L-1712801216371)]

一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
[外链图片转存中…(img-a0vQJoB4-1712801216371)]