MySQL分页查询优化

>>强大,10k+点赞的 SpringBoot 后台管理系统竟然出了详细教程!

limit介绍

limit用于强制 SELECT 语句返回指定的记录数。limit接受一个或两个数字参数。参数必须是一个整数常量。
  • 如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。
  • 如果给定一个参数,它表示返回最大的记录行数目。
语法:
1
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
两个参数举例写法:
1
2
3
selete * from table limit 2,1;
# 等同于
selete * from table limit 1 offset 2;
  • offset:指定返回记录行的偏移量(初始记录行的偏移量是 0)
  • rows:指定返回记录行的最大数目
即:以上示例的含义是跳过2条数据,读取1条数据。
一个参数举例写法:
1
select * from sys_operation_log limit 10
查询前10笔数据,limit 10 等价于 limit 0,10

优化分析

对于rows,即每页展示的数据,肯定是数据量越少,查询速度越快。通常控制在每页100条数据以内,查询速度相差不大。
对于offset,上面示例中的查询方式会从数据库第一条记录开始扫描,所以越往后,查询速度越慢。
举例来说明:
偏移量为100,查询所用的时间
偏移量为10000,查询所用的时间
我们可以看到,偏移量对查询速度有不小的影响,当偏移量大于10万以后,查询所用时间急剧增加。

优化方法

由于上面示例中的查询是从数据库第一条记录开始扫描 导致越往后的数据查询速度越慢,那么我们就要想办法避免他去做全表的扫描。
方法1:使用id限定
举例来说明:
使用下面的查询语句代替之前示例中的查询语句
1
select * from sys_operation_log where id >= 10001 limit 100;
查询所用时间
由 0.3s 变为了0.001s,速度得到了飞升!但是注意,以上查询的优化适用于表设置为主键id自增的情况下。如果不是,需要先排序,如下:
1
select * from sys_operation_log where id >= 10001 ORDER BY id ASC limit 100;
方法2:使用子查询
举例来说明:
1
2
3
4
5
6
7
SELECT
    *
FROM
    sys_operation_log
WHERE
    id >= (SELECT id FROM sys_operation_log LIMIT 10000,1)
LIMIT 100;
查询所用的时间
由 0.3s 变为了0.07s ,速度得到了显著的提升。同样依赖于主键递增,如果数据有缺失,也可以考虑使用临时表记录分页使用的id,然后使用该id进行查询。
使用join查询同理:
1
2
3
4
5
6
SELECT
    *
FROM
    sys_operation_log as o1 join (SELECT id FROM sys_operation_log ORDER BY id LIMIT 10000,100) as o2
 
where o1.id = o2.id
运行效率和子查询差不多。
总体思路:避免数据量大的时候扫描过多的数据,可以使用有索引的列或主键进行order by 操作,查询时记录上次返回的主键,在获取下个数据段时直接定位过去。