如果面试官问你如何优化mysql分页查询，请把这篇文章甩给他！

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

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

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

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

如果你需要这些资料，可以添加V获取：vip1024b （备注Java）

正文

• • 创建数据表

• 添加数据

• 查询过慢的原因

• limit分页的原理

• 1.最大id查询法

• 2.BETWEEN … AND

• 3.limit id

• 4.延迟关联（个人推荐）

• 5.分表查询

• • 水平分表

• 垂直分表

• 冷热表

• 索引

• 总结

在我们日常开发中，分页查询是必不可少的，可以说每干后端程序猿大部分时间都是CURD，所以分页的查询也接触的不少，你们都是怎么实现的呢？前不久的一段时间，我的一个同事突然找我寻求帮助，他说他写的sql查询太慢了，问我能不能帮他优化一下那条查询语句，经过一段时间的优化，我们成功的将原来8秒一条的sql成功优化到了不到一秒，然而想到知识应该学会分享，所以我今天打算写出这个优化过程，可以让更多的程序猿可以看到。

为什么想到了优化分页查询

===========================================================================

同事：hi boy ，让我们一起来探讨一下理想如何？

握草，我一听就觉得事情不对，肯定没好事，走过去一看，同事对我说，他维护的这张日志表数据已经超过500w了，可能是测试的同事在做压力测试，导致了数据库的用户操作日志记录一下子就突破了几百万，现在，同事写的分页查询速度已经很慢很慢了，当查询的记录越靠后的时候，查询时间越久，果不其然，没过多久，我们就在禅道上看到了测试大哥提交的bug：查看1000页以后的日志返回速度极慢，这是被迫优化啊，好尴尬。

如何优化

===================================================================

问题是找到了，那是因为当数据库存放的记录过大的时候，查询也靠后的记录速度越慢，为什么查询越靠后的记录就越慢呢？

我这里主要介绍mysql的分页优化，sqlserver、Oracle可以参考思想，还是回归之前的那个问题，我们来看看问什么查询会慢？我们需要实现准备好数据表以及记录。

创建数据表

---

DROP TABLE IF EXISTS user;

CREATE TABLE user (

id bigint(20) NOT NULL AUTO_INCREMENT,

u_name varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘用户名’,

u_password varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘密码’,

u_mail varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘邮箱’,

u_phone varchar(11) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘手机’,

u_sex tinyint(1) DEFAULT NULL COMMENT ‘性别’,

u_headImg varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘头像’,

PRIMARY KEY (id)

) ENGINE=InnoDB AUTO_INCREMENT=4762599 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

添加数据

---

我在数据表中添加了100w张三、100w李四、100w王五、200赵六的用户，所以表中一共500w数据，添加数据的方式采用的存储过程。

BEGIN

declare i int; # declare语句是在复合语句中声明变量的指令

set i=1;

while i<=2000000 do

insert into user(u_name,u_password,u_mail,u_phone,u_sex,u_headImg) values(‘赵六’,‘000000’,‘zhaoliu@163.com’,‘18800000000’,0,‘oss.file.com/images/zhaoliu.png’);

set i=i+1;

end while;

END

查询过慢的原因

---

数据表和记录都已经准备好了，现在我们就需要来排查为什么分页查询页码越靠后查询速度越慢，我们先来看一个普通的分页查询：

select * from user order by id desc limit 100,10;

这是查询第十页的数据，我相信大部分的人在写分页查询的时候都是这么写的，其中100：偏移量，意思就是说从哪里之后就是我需要的数据据，10：表示需要查询多少条记录，这个就是mysql的分页查询语法，你能看出这条sql存在什么问题吗？乍一看好像没啥问题，真的是这样吗？我们来看几个例子。

1：偏移量=0

select * from user order by id desc limit 0,10;

查询时间

2.偏移量=1000

select * from user order by id desc limit 1000,10;

查询时间

3.偏移量=10000

select * from user order by id desc limit 10000,10;

查询结果

就算偏移量达到了10000，我们的查询速度还是很快的，这说明这条sql就没有任何问题了吗？既然这样，我就让你们看一下当偏移量达到200w的时候，会发生什么事情?

4.偏移量= 400w

select * from user order by id desc limit 4000000,10;

查询结果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YeQGpug3-1584850193772)(

也就是说偏移量越大，查询的时候就越久，这是问什么呢？明明都是从查询10条记录，为什么偏移量越大，查询时间越久呢？

limit分页的原理

---

为什么会慢？我们不妨先猜测一下，像函数、扫描记录过多等等都会影响查询的速度，很显然这里我们并没有使用到函数，所以这会不会是扫描的记录过多呢？

这个就和limit有关了，你们知道limit是如何实现分页的吗？我们使用wxplain关键字来分别打印一下偏移量=0、1000、10000、400w的查询详情。

1：偏移量=0

2.偏移量=1000

3.偏移量=10000

4.偏移量= 400w

我们先来解释一下这些字段分别是什么意思

id:标识符

select_type:查询的类型。

table:结果表

partitions:匹配的分区

type:表的连接类型

possible_keys:也许会使用的索引

key:实际使用的索引

key_len:索引字段的长度

ref:列与索引的比较

rows:扫描出的行数

filtered:按表条件过滤的行百分比

Extra:执行情况的描述和说明

最后

针对最近很多人都在面试，我这边也整理了相当多的面试专题资料，也有其他大厂的面经。希望可以帮助到大家。

最新整理面试题

上述的面试题答案都整理成文档笔记。也还整理了一些面试资料&最新2021收集的一些大厂的面试真题

最新整理电子书

最新整理大厂面试文档

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Java）

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

[外链图片转存中…(img-iy2tb9uB-1713321626105)]

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Java）
[外链图片转存中…(img-wU49g3i3-1713321626105)]

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