下载APP 编程狮,随时随地学编程
返回 首页

MySQL 教程

MySQL分页查询方法及优化

当数据库的数据量很大时,一次性查询结果就会变得很慢,为了提高查询效率,我们可以使用MySQL的分页查询功能。本文就为大家带来MySQL分页查询方法及优化。


推荐阅读:

21分钟MySQL入门教程

MySQL完整教程


分页查询方法:

在MySQL中,分页查询一般都是使用limit子句实现,limit子句声明如下:

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

LIMIT子句可以被用于指定 SELECT 语句返回的记录数。需注意以下几点:

1、第一个参数指定第一个返回记录行的偏移量

2、第二个参数指定返回记录行的最大数目

3、如果只给定一个参数:它表示返回最大的记录行数目

4、第二个参数为 -1 表示检索从某一个偏移量到记录集的结束所有的记录行

5、初始记录行的偏移量是0(而不是 1)


下面是一个应用实例:

select * from orders_history where type=8 limit 1000,10;

该条语句将会从表 orders_history 中查询第1000条数据之后的10条数据,也就是第1001条到第1010条数据。

数据表中的记录默认使用主键(一般为id)排序,上面的结果相当于:

select * from orders_history where type=8 order by id limit 10000,10;

三次查询时间分别为:

3040 ms

3063 ms

3018 ms

针对这种查询方式,下面测试查询记录量对时间的影响:

select * from orders_history where type=8 limit 10000,1;
select * from orders_history where type=8 limit 10000,10;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 10000,1000;
select * from orders_history where type=8 limit 10000,10000;

三次查询时间如下:

查询1条记录:3072ms 3092ms 3002ms

查询10条记录:3081ms 3077ms 3032ms

查询100条记录:3118ms 3200ms 3128ms

查询1000条记录:3412ms 3468ms 3394ms

查询10000条记录:3749ms 3802ms 3696ms

另外我还做了十来次查询,从查询时间来看,基本可以确定,在查询记录量低于100时,查询时间基本没有差距,随着查询记录量越来越大,所花费的时间也会越来越多。


针对查询偏移量的测试:

select * from orders_history where type=8 limit 100,100;
select * from orders_history where type=8 limit 1000,100;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 100000,100;
select * from orders_history where type=8 limit 1000000,100;

三次查询时间如下:

查询100偏移:25ms 24ms 24ms

查询1000偏移:78ms 76ms 77ms

查询10000偏移:3092ms 3212ms 3128ms

查询100000偏移:3878ms 3812ms 3798ms

查询1000000偏移:14608ms 14062ms 14700ms

随着查询偏移的增大,尤其查询偏移大于10万以后,查询时间急剧增加。

这种分页查询方式会从数据库第一条记录开始扫描,所以越往后,查询速度越慢,而且查询的数据越多,也会拖慢总查询速度。


使用子查询优化

这种方式先定位偏移位置的 id,然后往后查询,这种方式适用于 id 递增的情况。

select * from orders_history where type=8 limit 100000,1;

select id from orders_history where type=8 limit 100000,1;

select * from orders_history where type=8 and 
id>=(select id from orders_history where type=8 limit 100000,1) 
limit 100;

select * from orders_history where type=8 limit 100000,100;

4条语句的查询时间如下:

第1条语句:3674ms

第2条语句:1315ms

第3条语句:1327ms

第4条语句:3710ms

针对上面的查询需要注意:

1、比较第1条语句和第2条语句:使用 select id 代替 select * 速度增加了3倍

2、比较第2条语句和第3条语句:速度相差几十毫秒

3、比较第3条语句和第4条语句:得益于 select id 速度增加,第3条语句查询速度增加了3倍

这种方式相较于原始一般的查询方法,将会增快数倍。


使用 id 限定优化

这种方式假设数据表的id是连续递增的,则我们根据查询的页数和查询的记录数可以算出查询的id的范围,可以使用 id between and 来查询:

select * from orders_history where type=2 
and id between 1000000 and 1000100 limit 100;

查询时间:15ms 12ms 9ms

这种查询方式能够极大地优化查询速度,基本能够在几十毫秒之内完成。限制是只能使用于明确知道id的情况,不过一般建立表的时候,都会添加基本的id字段,这为分页查询带来很多便利。

还可以有另外一种写法:

select * from orders_history where id >= 1000001 limit 100;

当然还可以使用 in 的方式来进行查询,这种方式经常用在多表关联的时候进行查询,使用其他表查询的id集合,来进行查询:

select * from orders_history where id in
(select order_id from trade_2 where goods = 'pen')
limit 100;

这种 in 查询的方式要注意:某些 mysql 版本不支持在 in 子句中使用 limit。


关于数据表的id说明

一般情况下,在数据库中建立表的时候,每一张表强制添加 id 递增字段,这样更方便我们查询数据。

如果数据量很大,比如像订单这类,一般会推荐进行分库分表。这个时候 id 就不建议作为唯一标识了,而应该使用分布式的高并发唯一 id 生成器来生成,并在数据表中使用另外的字段来存储这个唯一标识。

首先使用范围查询定位 id (或者索引),然后再使用索引进行定位数据,即先 select id,然后在 select *;这样查询的速度将会提升好几倍。


原文地址:http://uusama.com/458.html


目录

MySQL 教程

MySQL 入门教程
MySQL 安装
MySQL 管理
MySQL PHP 语法
MySQL 连接
MySQL 创建数据库
MySQL 删除数据库
MySQL 选择数据库
MySQL 数据类型
MySQL 创建数据表
MySQL 删除数据表
MySQL 插入数据
MySQL 查询数据
MySQL where 子句
MySQL UPDATE 查询
MySQL DELETE 语句
MySQL LIKE 子句
MySQL 排序
MySQL 分组
MySQL 连接的使用
MySQL NULL 值处理
MySQL 正则表达式
MySQL 事务
MySQL ALTER命令
MySQL 索引
MySQL 临时表
MySQL 复制表
MySQL 元数据
MySQL 序列使用
MySQL 处理重复数据
MySQL 及 SQL 注入
MySQL 导出数据
MySQL 导入数据

MySQL函数

MySQL DATE_ADD() 函数
MySQL DATE_SUB() 函数
MySQL DATEDIFF() 函数
MySQL DATE_FORMAT() 函数
MySQL NOW() 函数
MySQL CURDATE() 函数
MySQL CURTIME() 函数
MySQL DATE() 函数
MySQL EXTRACT() 函数
MySQL 字符串连接CONCAT()函数
MySQL 字符串截取SUBSTRING()函数
MySQL 数学函数
mysql substr() 函数

MySQL 拓展阅读

15款优秀的mysql管理工具与应用程序推荐
ubuntu安装mysql数据库教程
mysql修改密码方法 mysql忘记密码怎么办
mysql front安装与使用教程
MySQL分页查询方法及优化
mysql通配符使用
MySQL交集和差集的实现方法
postgresql和mysql哪个好

关闭

MIP.setData({ 'pageTheme' : getCookie('pageTheme') || {'day':true, 'night':false}, 'pageFontSize' : getCookie('pageFontSize') || 20 }); MIP.watch('pageTheme', function(newValue){ setCookie('pageTheme', JSON.stringify(newValue)) }); MIP.watch('pageFontSize', function(newValue){ setCookie('pageFontSize', newValue) }); function setCookie(name, value){ var days = 1; var exp = new Date(); exp.setTime(exp.getTime() + days*24*60*60*1000); document.cookie = name + '=' + value + ';expires=' + exp.toUTCString(); } function getCookie(name){ var reg = new RegExp('(^| )' + name + '=([^;]*)(;|$)'); return document.cookie.match(reg) ? JSON.parse(document.cookie.match(reg)[2]) : null; }