MySQL limit子句用法及优化(Limit Clause Optimization)

2024-06-19 20:12

本文主要是介绍MySQL limit子句用法及优化(Limit Clause Optimization),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

在MySQL中,如果只想获取select查询结果的一部分,可以使用limit子句来限制返回记录的数量,limit在获取到满足条件的数据量时即会立刻终止SQL的执行。相比于返回所有数据然后丢弃一部分,执行效率会更高。

文章目录

  • 一、limit子句用法示例
    • 1.1 基本用法
    • 1.2 limit和order by
      • 1.2.1 排序瓶颈优化
  • 二、limit分页优化
    • 2.1 延迟关联
    • 2.2 转换为位置查询
    • 2.3 记录偏移位置

一、limit子句用法示例

limit子句通常放在select查询的最后,语法是limit [offset,] rowcount :

  • limit m,n 返回偏移量为m之后的n条数据,即先获取m+n条记录,然后丢弃前面的m条,返回之后的n条记录
  • limit n 返回开头的n条数据,相当于limit 0, n

1.1 基本用法

新建一张测试表并填充几条数据:

create table test(
id int auto_increment primary key,
name varchar(32),
salary decimal(10,2));insert into test values(null, 'aaa',1000),(null, 'bbb',2000),(null, 'ccc',3000),(null, 'ddd',4000),(null, 'eee',5000),(null, 'fff',6000),(null, 'ggg',7000),(null, 'hhh',8000),(null, 'iii',9000);

在这里插入图片描述

limit 0会立刻返回一个空结果集,它通常用来检测SQL语法是否正确或者快速获取结果集的字段属性。limit n用来返回最先获取的n条记录,找到足够的记录时SQL就会停止执行并返回结果:

select * from test limit 3;

在这里插入图片描述

采用limit m,n的形式,就是跳过前面的m条记录,返回之后的n条记录:

select * from test limit 3,3;

在这里插入图片描述

如果只是想跳过开头的m条记录,只需要给n一个足够大的数字即可,例如跳过开头100条记录:limit 100, 9999999999

1.2 limit和order by

如果order by子句和limit子句同时出现,那么MySQL会先对结果进行排序,对排序后的结果集应用limit子句。例如查询工资最高的3个人(按salary列倒序排列后取前3条记录):

select * from test order by salary desc limit 3;

在这里插入图片描述
如果排序的列存在重复数据,例如本例返回3条数据,但是3,4,5条记录的salary列都是相同的(它们都可以排在第三),这时返回的结果集是不确定的,查询时需注意。

1.2.1 排序瓶颈优化

与order by子句配合使用时,虽然limit子句最终获取的结果集可能很小,但需要先对所有的数据进行排序,如果这个数据量很大,那么排序操作就会成为性能瓶颈。

如果你发现limit子句加上order by之后语句执行很慢,可以尝试通过在排序列上增加索引来消除这个排序操作。由于示例表很小,优化器倾向于走全表扫描,这里找一张更大的表test1来演示,表中约有2万多条数据。观察添加索引前后的执行计划:

explain select * from test1 order by salary desc limit 3;
create index idx_salary on test1(salary);
explain select * from test1 order by salary desc limit 3;

在这里插入图片描述
可以看到索引反向扫描替代了原来的排序操作,同时扫描的行数量从24032降低到了3。

二、limit分页优化

limit子句最常用场景就是数据分页,通过变更偏移量来对数据进行分页展示。例如第一页显示100条数据,limit子句就是limit 0,100。第二页是limit 100,100,第三页是limit 200,100…. 但是当页数非常大时,limit m,n 中被丢弃的m条数据可能成为性能瓶颈。

由于前m条数据(偏移量)是最终需要的丢弃的,它们的内容我们并不关心,因此优化的思路就是"避免查询前m条数据的内容"。

2.1 延迟关联

为了避免查询偏移量m条数据的内容,我们可以先通过索引获取的n条数据的偏移量/主键(而不是对全量数据进行排序),然后通过主键直接获取n条数据的内容。这种策略叫做"延迟关联"。

例如查询:

select * from test1 order by salary desc limit 10000,100;

通过延迟关联可以改写为:

select salary from test1
join ( select id from test1 order by salary desc limit 10000,100) d on d.id=test1.id;

如果salary列上有索引,那么获取id是不需要回表的,通过索引就可以获取n条数据的主键,随后再与主表关联,通过主键取出这n条数据内容。虽然SQL看起来稍微复杂了,但是它绕过了获取前m条数据内容这个步骤,当m值比较大时,性能提升是很明显的。

2.2 转换为位置查询

这种策略是根据排序条件预先计算每行记录的顺序编号并加上索引,例如在表中新增一列position(或者单独新增一张顺序表也可以),保存的是每一行位置顺序。这相当于分页排序已经预先执行了,而偏移操作就被转换成了索引范围扫描。

例如查询:

select * from test1 order by salary desc limit 10000,100;

通过位置查询可以改写为:

select * from test1 where position between 10001 and 10100;

position列是根据order by salary desc条件预先维护好的每一列的顺序编号,此后每次分页查询都不需要计算偏移量,而是被转换成了索引范围扫描(Index Range Scan)。

2.3 记录偏移位置

记录偏移位置的方法,就是当排序列存在顺序的情况下,每次查询后将其最后的值记录下来,然后作为下一次SQL查询的过滤条件。

假设首次查询如下(id列单调递增):

select * from test1 order by id limit 9900,100;

假设上面查询返回结果集的最大id为123456,程序可以将这个值单独记录下来,那么SQL:

select * from test1 order by id limit 10000,100;

就可以改写为:

select * from test1 where id>123456 order by id limit 100;

通过条件where id>123456就可以过滤掉前m条数据,但这种方法的缺陷就是它只能一页一页的顺序往后翻,不能跳转翻页,对比上面2种方法不够灵活。

这篇关于MySQL limit子句用法及优化(Limit Clause Optimization)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1076030

相关文章

Vue3 的 shallowRef 和 shallowReactive:优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉,那么对 shallowRef 和 shallowReactive 是否了解呢? 在编程和数据结构中,“shallow”(浅层)通常指对数据结构的最外层进行操作,而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素,而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层(Shallow) 定义

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

HDFS—存储优化(纠删码)

纠删码原理 HDFS 默认情况下,一个文件有3个副本,这样提高了数据的可靠性,但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码,采用计算的方式,可以节省约50%左右的存储空间。 此种方式节约了空间,但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件,都会执行此策略。 默认只开启对 RS-6-3-1024k

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

使用opencv优化图片(画面变清晰)

文章目录 需求影响照片清晰度的因素 实现降噪测试代码 锐化空间锐化Unsharp Masking频率域锐化对比测试 对比度增强常用算法对比测试 需求 对图像进行优化,使其看起来更清晰,同时保持尺寸不变,通常涉及到图像处理技术如锐化、降噪、对比度增强等 影响照片清晰度的因素 影响照片清晰度的因素有很多,主要可以从以下几个方面来分析 1. 拍摄设备 相机传感器:相机传

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间

MySQL高性能优化规范

前言:      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后,总结出了这篇! 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀,备份

[MySQL表的增删改查-进阶]

🌈个人主页:努力学编程’ ⛅个人推荐: c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构,刷题刻不容缓:点击一起刷题 🌙心灵鸡汤:总有人要赢,为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key: