MySQL limit N offset M 速度慢?来实际体验下

直接开始

有一张表:trade_user,表结构如下:


mysql> desc trade_user;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | bigint unsigned  | NO   | PRI | NULL    | auto_increment |
| name       | varchar(20)      | NO   | MUL | NULL    |                |
| email      | longtext         | YES  |     | NULL    |                |
| age        | tinyint unsigned | YES  |     | NULL    |                |
| birthday   | datetime         | YES  |     | NULL    |                |
| created_at | datetime         | YES  |     | NULL    |                |
| updated_at | datetime         | YES  |     | NULL    |                |
| id_no      | char(18)         | NO   |     |         |                |
+------------+------------------+------+-----+---------+----------------+
8 rows in set (0.02 sec)

COPY

表行数


mysql> select count(*) from trade_user;
+----------+
| count(*) |
+----------+
|  3536655 |
+----------+
1 row in set (0.60 sec)

COPY

无索引limit n offset m

OFFSET 0:limit 10 offset 0

 select  * from trade_user order by email limit 10 offset 0;

COPY

执行耗时:1.41 秒

file

OFFSET 1万: limit 10 offset 10000


select  * from trade_user order by email limit 10 offset 10000;

COPY

执行耗时: 1.68秒

file

OFFSET:10万:limit 10 offfet 100000


select  * from trade_user order by email limit 10 offset 100000;

COPY

执行耗时:1.89秒

file

OFFSET:100万:limit 10 offset 1000000


select  * from trade_user order by email limit 10 offset 1000000;

COPY

执行耗时:4.06秒

file

OFFSET:200万:limit 10 offset 2000000


select  * from trade_user order by email limit 10 offset 2000000;

COPY

执行耗时:9.07秒

file

有索引limit n offset m

trade_user表的name列有一个普通索引。

OFFSET 0:limit 10 offset 0

 select  * from trade_user order by name limit 10 offset 0;

COPY

执行耗时:0.01 秒

file

OFFSET 200万:limit 10 offset 2000000

 select  * from trade_user order by name limit 10 offset 2000000;

COPY

执行耗时:7.21 秒

file

为什么?

OFFSET越大,MySQL扫描行数越多:


+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | SIMPLE      | trade_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3447992 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

COPY


| EXPLAIN

| -> Limit/Offset: 10/2000000 row(s)  (cost=359470 rows=10) (actual time=8737..8737 rows=10 loops=1)
    -> Sort row IDs: trade_user.`name`, limit input to 2000010 row(s) per chunk  (cost=359470 rows=3.45e+6) (actual time=2552..8699 rows=2e+6 loops=1)
        -> Table scan on trade_user  (cost=359470 rows=3.45e+6) (actual time=0.0247..1870 rows=3.54e+6 loops=1)
 |
1 row in set (8.74 sec)

COPY

总结

在对大表进行 LIMIT 和 OFFSET 操作时,随着偏移量(OFFSET)的增加,性能会显著下降。由于 MySQL 必须首先跳过 OFFSET 之前的所有行,才能获取到 LIMIT 指定的数据量,因此当 OFFSET 值较大时,这会导致显著的性能开销,尤其是在没有对排序列添加索引的情况下。通过性能测试得出,即使 LIMIT 的值相同,越大的 OFFSET 会使 MySQL 扫描的行数越多,因此执行时间越长。

性能测试的总结如下:

  • 当没有索引支持 ORDER BY 子句时,即使是小范围的 LIMIT 查询,随着 OFFSET 的增加,查询性能会急剧下降。从测试结果来看,相同的 LIMIT 值下,OFFSET 值为 0 时查询耗时为 1.41 秒,而 OFFSET 值为 200 万时耗时增加到了 9.07 秒。
  • 当存在索引支持 ORDER BY 子句时,查询性能显著提升,OFFSET 为 0 时耗时只需 0.01 秒。这表明,有索引的情况下,小 OFFSET 值查询的性能提升非常明显。但即使有索引支持,大 OFFSET 值仍然会导致较高的性能开销,如 OFFSET 值为 200 万时耗时为 7.21 秒。
  • 测试中观察到的性能差异主要是由于 MySQL 在未使用索引的情况下需要对所有数据进行全表扫描,并使用文件排序来找到 ORDER BY 子句中指定的顺序,然后才能跳过 OFFSET 指定的行。

为了优化大表的 LIMIT 和 OFFSET 查询:

  • 避免使用大的 OFFSET 值,特别是在没有对 ORDER BY 的字段进行索引优化的情况下。
  • 考虑使用 "keyset pagination" 或 "seek method" 方法,即通过跟踪上一次检索的最后一个记录的标识,来避免使用 OFFSET
  • 确保 ORDER BY 中的列上有适当的索引以提高排序和检索效率。
  • 尽量减少查询结果中的列数,只取需要的列。
  • 如果有可能,调整应用逻辑以减少数据量,或将常用查询结果进行缓存。

大表不用使用大OFFSET。

参考

MySQL limit N offset M 速度慢?来实际体验下 – 小厂程序员

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/532834.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

记一次IP访问MySQL失败多次被自动锁定导致无法连接问题,解决方法只需一条SQL。

👩🏽‍💻个人主页:阿木木AEcru 🔥 系列专栏:《Docker容器化部署系列》 《Java每日面筋》 💹每一次技术突破,都是对自我能力的挑战和超越。 前言 今天下午还在带着耳机摸鱼&#xff…

Linux CentOS 安装 MySQL 服务教程

Linux CentOS 安装 MySQL 服务教程 1. 查看系统和GNU C库(glibc)版本信息 1.1 查询机器 glibc 版本信息 glibc,全名GNU C Library,是大多数Linux发行版中使用的C库,为系统和应用程序提供核心的API接口。在Linux系统中,特别是在…

动态规划-入门理解

一、什么情况可以使用动态规划 动态规划 最优子结构 重叠子问题 转移方程 最优子结构:保证能从局部解推出全局解,也就是保证能够写出转移方程 重叠子问题:说明暴力解法太耗时,我们可以使用动态规划进行优化 转移方程&#xff…

自用---

零、环境配置 keil代码补全 keil pack包 cubemx配置安装包 一、LED cubemx配置PD2引脚为输出模式 uint16_t led_value 0x00; void led_set(uint8_t led_dis) {HAL_GPIO_WritePin(GPIOC,GPIO_PIN_All,GPIO_PIN_SET);HAL_GPIO_WritePin(GPIOC,led_dis<<8,GPIO_PIN_R…

03-JAVA设计模式-桥接模式

桥接模式 什么是桥接模式 桥接模式&#xff08;Bridge Pattern&#xff09;是一种将抽象与实现解耦的设计模式&#xff0c;使得二者可以独立变化。在桥接模式中&#xff0c;抽象部分与实现部分通过一个桥接接口进行连接&#xff0c;从而允许抽象部分和实现部分独立演化。 场…

服务器docker应用一览

文章目录 一、简单需求二、业务流程三、运行效果四、实现过程1. 前提2. 源码3.核心代码4. 项目打包5、部署 一、简单需求 现有某云主机服务器&#xff0c;用来做项目演示用&#xff0c;上面运行了docker应用&#xff0c;现希望有一总览页面&#xff0c;用来展示部署的应用。 …

微信小程序实现输入appid跳转其他小程序

前言 本文记录wx.navigateToMiniProgram打开另一个小程序API使用方法&#xff0c;并封装为组件。 wxml 部分 输入框用来记录appid&#xff0c;按钮用来查询并跳转。 <view class"container"><input class"input" placeholder"请输入要查…

Linux: softirq 简介

文章目录 1. 前言2. softirq 实现2.1 softirq 初始化2.1.1 注册各类 softirq 处理接口2.1.2 创建 softirq 处理线程 2.2 softirq 的 触发 和 处理2.1.1 softirq 触发2.1.2 softirq 处理2.1.2.1 在 中断上下文 处理 softirq2.1.2.2 在 ksoftirqd 内核线程上下文 处理 softirq 3.…

Facial Micro-Expression Recognition Based on DeepLocal-Holistic Network 阅读笔记

中科院王老师团队的工作&#xff0c;用于做微表情识别。 摘要&#xff1a; Toimprove the efficiency of micro-expression feature extraction,inspired by the psychological studyof attentional resource allocation for micro-expression cognition,we propose a deep loc…

HTTP与HTTPS:深度解析两种网络协议的工作原理、安全机制、性能影响与现代Web应用中的重要角色

HTTP (HyperText Transfer Protocol) 和 HTTPS (Hypertext Transfer Protocol Secure) 是互联网通信中不可或缺的两种协议&#xff0c;它们共同支撑了全球范围内的Web内容传输与交互。本文将深度解析HTTP与HTTPS的工作原理、安全机制、性能影响&#xff0c;并探讨它们在现代Web…

[leetcode]remove-duplicates-from-sorted-list-ii

. - 力扣&#xff08;LeetCode&#xff09; 给定一个已排序的链表的头 head &#xff0c; 删除原始链表中所有重复数字的节点&#xff0c;只留下不同的数字 。返回 已排序的链表 。 示例 1&#xff1a; 输入&#xff1a;head [1,2,3,3,4,4,5] 输出&#xff1a;[1,2,5]示例 2&…

百度OCR身份证识别C++离线SDKV3.0 C#对接

百度OCR身份证识别C离线SDKV3.0 C#对接 目录 说明 效果 问题 项目 代码 下载 说明 自己根据SDK封装了动态库&#xff0c;然后C#调用。 SDK 简介 本 SDK 适应于于 Windows 平台下的⾝份证识别系统,⽀持 C接⼜开发的 SDK,开发者可在VS2015 下⾯进⾏开发&#xff08;推荐…

爬虫+RPC+js逆向---直接获取加密值

免责声明:本文仅做技术交流与学习,请勿用于其它违法行为;如果造成不便,请及时联系... 目录 爬虫RPCjs逆向---直接获取加密值 target网址: 抓包 下断点 找到加密函数 分析参数 RPC流程 一坨: 二坨: 运行py,拿到加密值 爬虫RPCjs逆向---直接获取加密值 target网址: 优志…

Django+Celery框架自动化定时任务开发

本章介绍使用DjCelery即DjangoCelery框架开发定时任务功能&#xff0c;在Autotestplat平台上实现单一接口自动化测试脚本、业务场景接口自动化测试脚本、App自动化测试脚本、Web自动化测试脚本等任务的定时执行、调度、管理等&#xff0c;从而取代Jenkins上的定时执行脚本和发送…

R语言复现:轨迹增长模型发表二区文章 | 潜变量模型系列(2)

培训通知 Nhanes数据库数据挖掘&#xff0c;快速发表发文的利器&#xff0c;你来试试吧&#xff01;欢迎报名郑老师团队统计课程&#xff0c;4.20直播。 案例分享 2022年9月&#xff0c;中国四川大学学者在《Journal of Psychosomatic Research》&#xff08;二区&#xff0c;I…

南京航空航天大学-考研科目-513测试技术综合 高分整理内容资料-01-单片机原理及应用分层教程-单片机有关常识部分

系列文章目录 高分整理内容资料-01-单片机原理及应用分层教程-单片机有关常识部分 文章目录 系列文章目录前言总结 前言 单片机的基础内容繁杂&#xff0c;有很多同学基础不是很好&#xff0c;对一些细节也没有很好的把握。非常推荐大家去学习一下b站上的哈工大 单片机原理及…

AI大模型引领未来智慧科研暨ChatGPT自然科学高级应用

以ChatGPT、LLaMA、Gemini、DALLE、Midjourney、Stable Diffusion、星火大模型、文心一言、千问为代表AI大语言模型带来了新一波人工智能浪潮&#xff0c;可以面向科研选题、思维导图、数据清洗、统计分析、高级编程、代码调试、算法学习、论文检索、写作、翻译、润色、文献辅助…

大数据基础学习

目录 一.什么是大数据二.数据处理技术分类&#xff08;OLAP vs OLTP&#xff09;OLAP&#xff08;Online Analytical Processing&#xff09;OLTP&#xff08;Online Transaction Processing&#xff09;区别联系 三.储存的方式&#xff08;列式 vs 行式&#xff09;行式存储列…

【Vue】webpack polyfilling 报错

1. 出现问题描述 npm run serve 项目时报错 ERROR Failed to compile with 1 error 10:33:22 ├F10: AM┤ error in ./src/router/routes.js Module not found: Error: Cant resolve path in /U…

Harmony鸿蒙南向驱动开发-SDIO

SDIO&#xff08;Secure Digital Input and Output&#xff09;由SD卡发展而来&#xff0c;与SD卡统称为MMC&#xff08;MultiMediaCard&#xff09;&#xff0c;二者使用相同的通信协议。SDIO接口兼容以前的SD卡&#xff0c;并且可以连接支持SDIO接口的其他设备。 运作机制 …