黑马程序员技术交流社区

标题: MySQL分页查询优化 [打印本页]

作者: 六记 时间: 2019-6-13 14:14
标题: MySQL分页查询优化
limit介绍limit用于强制 SELECT 语句返回指定的记录数。limit接受一个或两个数字参数。参数必须是一个整数常量。

如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
如果给定一个参数，它表示返回最大的记录行数目。

语法：

[SQL] 纯文本查看 复制代码

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

两个参数举例写法：

[SQL] 纯文本查看 复制代码

selete * from table limit 2,1;

# 等同于

selete * from table limit 1 offset 2;

offset：指定返回记录行的偏移量（初始记录行的偏移量是 0）
rows：指定返回记录行的最大数目

即：以上示例的含义是跳过2条数据，读取1条数据。

一个参数举例写法：

[SQL] 纯文本查看 复制代码

select * from sys_operation_log limit 10

查询前10笔数据，limit 10 等价于 limit 0,10
优化分析对于rows，即每页展示的数据，肯定是数据量越少，查询速度越快。通常控制在每页100条数据以内，查询速度相差不大。

对于offset，上面示例中的查询方式会从数据库第一条记录开始扫描，所以越往后，查询速度越慢。

举例来说明：

偏移量为100，查询所用的时间

偏移量为10000，查询所用的时间

我们可以看到，偏移量对查询速度有不小的影响，当偏移量大于10万以后，查询所用时间急剧增加。
优化方法由于上面示例中的查询是从数据库第一条记录开始扫描导致越往后的数据查询速度越慢，那么我们就要想办法避免他去做全表的扫描。

方法1：使用id限定

举例来说明：

使用下面的查询语句代替之前示例中的查询语句：

[SQL] 纯文本查看 复制代码

select * from sys_operation_log where id >= 10001 limit 100;

方法2：使用子查询

举例来说明：

[SQL] 纯文本查看 复制代码

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查询同理：

[SQL] 纯文本查看 复制代码

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 操作，查询时记录上次返回的主键，在获取下个数据段时直接定位过去。

作者: 张志辉 时间: 2019-6-13 14:21
[赞],点击[ http://pinyin.cn/e244613 ]查看表情

作者: liudongjie 时间: 2019-6-13 14:54

作者: Julien27 时间: 2019-6-13 19:49
总结完美，顺便看看Oracle

作者: think12 时间: 2019-6-13 19:50
谢大佬干货分享

作者: 爱学习的翟 时间: 2019-6-13 20:01

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2