MySQL分页查询--LIMIT、OFFSET

limit简介

LIMIT 是 MySQL 中的一个特殊关键字，用于指定查询结果从哪条记录开始显示，一共显示多少条记录。

LIMIT 关键字有两种使用方式，即指定初始位置、与 OFFSET 组合使用

用法一：获取结果集的特定部分

limit中文可翻译为限制、界限，在MySQL中，limit可以用来获取结果集的特定部分，即结果集的起始位置往下查询的行数。

例如想要查询表中的前5条数据就可以使用下面的语句：

SELECT * FROM `user` LIMIT 5;

查询结果如下：

用法二：分页查询

limit可以和offset结合实现分页查询，例如查询从第四条数据开始往下的三条数据：

SELECT * FROM `user` LIMIT 3 OFFSET 3;

可以通过调整limit和offset的值来控制查询数据的页数和每页查询的条数

SELECT * FROM `user` LIMIT (PageSize) OFFSET (Page * PageSize)

其中PageSize代表每页需要的数据条数，Page代表要查询的是第几页（默认从第0页开始）

注意

• LIMIT n 表示每页显示n条数据，OFFSET n 表示从第n+1条数据开始显示
• OFFSET不能单独使用，如果没有使用LIMIT，那么OFFSET将没有意义，因为没有指定返回记录的数量限制
• SQL语句中LIMIT 要放在 OFFSET前面，LIMIT和OFFSET要放在整个SQL语句的最末尾

会出现的问题–深分页

问题描述

深分页指的是翻页请求中的页码数非常大,OFFSET数值非常大的情况。如果直接使用LIMIT offset/ limit的方式进行分页,那么在OFFSET超过10000时,性能会明显下降。

原因

延迟增加

原因是LIMIT语句会先获取符合条件的offset+n行数据，然后再丢弃掉前offset行，返回后n行数据。也就是说limit 10000,10，就会扫描100010行，而limit 0,10，只扫描10行。这里需要回表10010次，大量的时间都在回表这个上面。

内存消耗

MySQL需要将整个结果集加载到内存中进行排序和筛选，如果结果集很大，可能会导致内存不足的问题。

磁盘IO

如果结果集无法完全放入内存中，MySQL可能会将部分数据写入临时文件进行排序和筛选，这会增加磁盘IO的负载。

解决方法

优化的重点就在减少回表上，采用的方法是子查询+覆盖索引；例如：

select * from users where status ='1' order by id limit 10000, 10;

使用子查询的方法，将条件语句（包括where，order by，limit，offset等）抽出来放到子查询语句中，查找符合条件的该表中对应数据的唯一索引字段，作为一个单独的中间表，再将该表与原总表进行连表查询，这样在筛选时就不需要从所有满足条件的数据中进行淘汰了

可以将上面的语句修改为：

select u.*
from users as u, (select u.id
                  from users as u where u.status = '1' 
                  order by u.id limit 10 offset 10000) as middle_table
where u.id = middle_table.id