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

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 |

±------------±------------------------±-----------------------+

| 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 | | 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 的形式执行

3.3 书签记录

关于 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 巨坑, 慎踩

---

最后

小编在这里分享些我自己平时的学习资料，由于篇幅限制，pdf文档的详解资料太全面，细节内容实在太多啦，所以只把部分知识点截图出来粗略的介绍，每个小节点里面都有更细化的内容！

程序员代码面试指南 IT名企算法与数据结构题目最优解

这是” 本程序员面试宝典!书中对IT名企代码面试各类题目的最优解进行了总结，并提供了相关代码实现。针对当前程序员面试缺乏权威题目汇总这一-痛点， 本书选取将近200道真实出现过的经典代码面试题，帮助广“大程序员的面试准备做到万无一失。 “刷”完本书后，你就是“题王”!

《TCP-IP协议组(第4版)》

本书是介绍TCP/IP协议族的经典图书的最新版本。本书自第1版出版以来，就广受读者欢迎。

本书最新版进行」护元，以体境计算机网络技不的最新发展，全书古有七大部分共30草和7个附录:第一部分介绍一些基本概念和基础底层技术:第二部分介绍网络层协议:第三部分介绍运输层协议;第四部分介绍应用层协议:第五部分介绍下一代协议，即IPv6协议:第六部分介绍网络安全问题:第七部分给出了7个附录。

Java开发手册（嵩山版）

这个不用多说了，阿里的开发手册，每次更新我都会看，这是8月初最新更新的**（嵩山版）**

MySQL 8从入门到精通

本书主要内容包括MySQL的安装与配置、数据库的创建、数据表的创建、数据类型和运算符、MySQL 函数、查询数据、数据表的操作(插入、更新与删除数据)、索引、存储过程和函数、视图、触发器、用户管理、数据备份与还原、MySQL 日志、性能优化、MySQL Repl ication、MySQL Workbench、 MySQL Utilities、 MySQL Proxy、PHP操作MySQL数据库和PDO数据库抽象类库等。最后通过3个综合案例的数据库设计，进步讲述 MySQL在实际工作中的应用。

Spring5高级编程（第5版）

本书涵盖Spring 5的所有内容，如果想要充分利用这一领先的企业级 Java应用程序开发框架的强大功能，本书是最全面的Spring参考和实用指南。

本书第5版涵盖核心的Spring及其与其他领先的Java技术(比如Hibemate JPA 2.Tls、Thymeleaf和WebSocket)的集成。本书的重点是介绍如何使用Java配置类、lambda 表达式、Spring Boot以及反应式编程。同时，将与企业级应用程序开发人员分享一些见解和实际经验，包括远程处理、事务、Web 和表示层，等等。

JAVA核心知识点+1000道 互联网Java工程师面试题

企业IT架构转型之道 阿里巴巴中台战略思想与架构实战

本书讲述了阿里巴巴的技术发展史，同时也是-部互联网技 术架构的实践与发展史。

Spring及其与其他领先的Java技术(比如Hibemate JPA 2.Tls、Thymeleaf和WebSocket)的集成。本书的重点是介绍如何使用Java配置类、lambda 表达式、Spring Boot以及反应式编程。同时，将与企业级应用程序开发人员分享一些见解和实际经验，包括远程处理、事务、Web 和表示层，等等。

[外链图片转存中…(img-ctXZDO0j-1718772872760)]

JAVA核心知识点+1000道 互联网Java工程师面试题

[外链图片转存中…(img-7IceiBDV-1718772872760)]

[外链图片转存中…(img-lc1kcZSA-1718772872761)]

企业IT架构转型之道 阿里巴巴中台战略思想与架构实战

本书讲述了阿里巴巴的技术发展史，同时也是-部互联网技 术架构的实践与发展史。

[外链图片转存中…(img-M4wtoNU7-1718772872761)]