MySQL 千万数据量深分页优化，拒绝线上故障，springboot组件面试题

举个简单的例子，分析下 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 巨坑, 慎踩

---

以下言论可能会打破你对 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 这个参数在网上查询到有用资料比较少，大家如果测试过程中存在问题，可以加微信一起沟通

4.1 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;

±—±------------±---------±-----------±-----±--------------±-----±--------±-----±--------±---------±----------------------------+

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

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

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

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

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

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

最后

码字不易，觉得有帮助的可以帮忙点个赞，让更多有需要的人看到

又是一年求职季，在这里，我为各位准备了一套Java程序员精选高频面试笔试真题，来帮助大家攻下BAT的offer，题目范围从初级的Java基础到高级的分布式架构等等一系列的面试题和答案，用于给大家作为参考

以下是部分内容截图

！**

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

如果你觉得这些内容对你有帮助，可以添加V获取：vip1024b （备注Java）
[外链图片转存中…(img-4Sxo9839-1712030003884)]

最后

码字不易，觉得有帮助的可以帮忙点个赞，让更多有需要的人看到

又是一年求职季，在这里，我为各位准备了一套Java程序员精选高频面试笔试真题，来帮助大家攻下BAT的offer，题目范围从初级的Java基础到高级的分布式架构等等一系列的面试题和答案，用于给大家作为参考

以下是部分内容截图
[外链图片转存中…(img-E6GpFKGi-1712030003886)]