首页 星云 工具 资源 星选 资讯 热门工具
:

PDF转图片 完全免费 小红书视频下载 无水印 抖音视频下载 无水印 数字星空

面试官:limit 100w,10为什么慢?如何优化?

编程知识
2024年09月05日 18:54

在 MySQL 中,limit X,Y 的查询中,X 值越大,那么查询速度也就越慢,例如以下示例:

  • limit 0,10:查询时间大概在 20 毫秒左右。
  • limit 1000000,10:查询时间可能是 15 秒左右(1秒等于 1000 毫秒),甚至更长时间。

所以,可以看出,limit 中 X 值越大,那么查询速度都越慢。

这个问题呢其实就是 MySQL 中典型的深度分页问题。那问题来了,为什么 limit 越往后查询越慢?如何优化查询速度呢?

为什么limit越来越慢?

在数据库查询中,当使用 LIMIT x, y 分页查询时,如果 x 值越大,查询速度可能会变慢。这主要是因为数据库需要扫描和跳过 x 条记录才能返回 y 条结果。随着 x 的增加,需要扫描和跳过的记录数也增加,从而导致性能下降。

例如 limit 1000000,10 需要扫描 1000010 行数据,然后丢掉前面的 1000000 行记录,所以查询速度就会很慢。

优化手段

对于 MySQL 深度分页比较典型的优化手段有以下两种:

  1. 起始 ID 定位法:使用最后查询的 ID 作为起始查询的 ID。
  2. 索引覆盖+子查询

1.起始ID定位法

起始 ID 定位法指的是 limit 查询时,指定起始 ID。而这个起始 ID 是上一次查询的最后一条 ID。例如上一次查询的最后一条数据的 ID 为 6800000,那我们就从 6800001 开始扫描表,直接跳过前面的 6800000 条数据,这样查询的效率就高了,具体实现 SQL 如下:

select name, age, gender
from person
where id > 6800000 -- 核心实现 SQL
order by id limit 10;

其中 id 字段为表的主键字段。

为什么起始ID查询效率高呢?

因此这种查询是以上一次查询的最后 ID 作为起始 ID 进行查询的,而上次的 ID 已经定位到具体的位置了,所以只需要遍历 B+ 树叶子节点的双向链表(主键索引的底层数据结构)就可以查询到后面的数据了,所以查询效率就比较高,如下图所示:

如果上次查询结果为 9,之后再查询时,只需要从 9 之后再遍历 N 条数据就能查询出结果了,所以效率就很高。

优缺点分析

这种查询方式,只适合一页一页的数据查询,例如手机 APP 中刷新闻时那种瀑布流方式。

但如果用户是跳着分页的,例如查询完第 1 页之后,直接查询第 250 页,那么这种实现方式就不行了。

2.索引覆盖+子查询

此时我们为了查询效率,可以使用索引覆盖加子查询的方式,具体实现如下。

假设,我们未优化前的 SQL 如下:

select name, age, gender
from person
order by createtime desc 
limit 1000000,10;

在以上 SQL 中,createtime 字段创建了索引,但查询效率依然很慢,因为它要取出 100w 完整的数据,并需要读取大量的索引页,和进行频繁的回表查询,所以执行效率会很低。

此时,我们可以做以下优化:

SELECT p1.name, p1.age, p1.gender
FROM person p1
JOIN (
    SELECT id FROM person ORDER BY createtime desc LIMIT 1000000, 10
) AS p2 ON p1.id = p2.id;

相比于优化前的 SQL,优化后的 SQL 将不需要频繁回表查询了,因为子查询中只查询主键 ID,这时可以使用索引覆盖来实现。那么子查询就可以先查询出一小部分主键 ID,再进行查询,这样就可以大大提升查询的效率了。

索引覆盖(Index Coverage)是一种数据库查询优化技术,它指的是在执行查询时,数据库引擎可以直接从索引中获取所有需要的数据,而不需要再回表(访问主键索引或者表中的实际数据行)来获取额外的信息。这种方式可以减少磁盘 I/O 操作,从而提高查询性能。

课后思考

你还知道哪些深度分页的优化手段呢?欢迎评论区留下你的答案。

本文已收录到我的面试小站 www.javacn.site,其中包含的内容有:Redis、JVM、并发、并发、MySQL、Spring、Spring MVC、Spring Boot、Spring Cloud、MyBatis、设计模式、消息队列等模块。

From:https://www.cnblogs.com/vipstone/p/18399160
本文地址: http://www.shuzixingkong.net/article/1775
0评论
提交 加载更多评论
其他文章 光影精灵10 Win1+Ubuntu18.04 双系统 踩坑记录
前言 第二年准备报名智能车了,当然还是创意组别。刚好买了今年新出的电脑光影精灵10,我想着也给它安一个双系统。但是没想到,相比于之前那个老电脑,新电脑的新硬件和驱动问题远比老电脑麻烦的多。 在经历了一系列踩坑和疯狂查资料之后,加上重装系统N次,终于成功下车。遂把过程记录下来,以备后续不时之需&#12
光影精灵10 Win1+Ubuntu18.04 双系统  踩坑记录
从Workload中优雅隔离Pod
线上集群中,业务跑着跑着,突然发现有个Pod上出现大量错误日志,其他的Pod是正常的,该如何处理呢? 直接删除Pod? 这样不便于保留现场,可能会影响判断问题的根因 让业务方忍一会,先排查下问题? 会被喷死 最好的方案是既让Pod停止接收流量,又保留Pod 思路: 停止接收流量 停止接收流量这个动作
项目经理的时间管理秘籍,你真的了解吗?
前言 深圳大学为何能与985并肩,却尚未踏入“双一流”行列? 在高手如云的中国高等教育界,深圳大学如同一匹黑马,迅速崛起,其综合实力和国际影响力已不逊色于部分985高校。然而,令人好奇的是,这样一所实力派大学,为何至今还未被评为“双一流”高校呢? 1、实力不容小觑 深圳大学,这所年轻的学府,自198
1000多天我开发了一个免费的跨浏览器的书签同步、阅读排版、任意网页标注插件
自我介绍 大家好,我是阿浩,一位后端开发工程师,同时也略懂前端技术。业余时间,我开发了这款小工具——《藏趣云》。 我为什么要写这么个工具 需求来源于我自己,因为我是做开发的。我日常都会使用多款浏览器来测试项目,一次需要登录多个账号,寻找各种测试地址链接。之前因为电脑磁盘损坏、重装系统等原因,而且因为
1000多天我开发了一个免费的跨浏览器的书签同步、阅读排版、任意网页标注插件 1000多天我开发了一个免费的跨浏览器的书签同步、阅读排版、任意网页标注插件 1000多天我开发了一个免费的跨浏览器的书签同步、阅读排版、任意网页标注插件
Python 版本管理工具选择与 Pyenv 使用说明
Python 版本管理工具的主要作用是帮助开发者在同一台机器上管理多个 Python 版本和环境。这对于开发和部署不同项目非常有用,因为不同项目可能依赖不同的 Python 版本或者不同的包版本。具体来说,Python 版本管理工具应有以下功能: (1)避免依赖冲突,不同的项目可能依赖不同版本的库,
一次Java性能调优实践【代码+JVM 性能提升70%】
这是我第一次对系统进行调优,涉及代码和JVM层面的调优。如果你能看到最后的话,或许会对你日常的开发有帮助,可以避免像我一样,犯一些低级别的错误。本次调优的代码是埋点系统中的报表分析功能,小公司,开发结束后,没有Code Review环节,所以下面某些问题,也许在Code Review环节就可以避免。
一次Java性能调优实践【代码+JVM 性能提升70%】 一次Java性能调优实践【代码+JVM 性能提升70%】 一次Java性能调优实践【代码+JVM 性能提升70%】
Falcon Mamba: 首个高效的无注意力机制 7B 模型
Falcon Mamba 是由阿布扎比的 Technology Innovation Institute (TII) 开发并基于 TII Falcon Mamba 7B License 1.0 的开放获取模型。该模型是开放获取的,所以任何人都可以在 Hugging Face 生态系统中 这里 使用它
Falcon Mamba: 首个高效的无注意力机制 7B 模型 Falcon Mamba: 首个高效的无注意力机制 7B 模型
逻辑回归模型
核心:线性回归+sigmoid映射。 一、概述 逻辑回归模型(Logistic Regression,LR),由名称上来看,似乎是一个专门用于解决回归问题的模型,事实上,该模型更多地用于解决分类问题,尤其是二分类问题。这并不矛盾,因为逻辑回归直接输出的是一个连续值,我们将其按值的大小进行切分,不足一
逻辑回归模型 逻辑回归模型