在MySQL中使用LIMIT进行分页的方法
今天看一个水友说他的MySQL现在变的很慢。问什么情况时。说单表超过2个G的一个MyISAM。真垃圾的回答方式。
简单答复：换一个强劲的服务器。换服务器很管用的：）
………
最终让取到慢查询：
SELECT * FROM pw_gbook WHERE uid='N' ORDER BY postdate DESC LIMIT N,N; SELECT * FROM pw_gbook WHERE uid='N' ORDER BY postdate DESC LIMIT N,N;
如：
SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20; SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20;
看到这个语句我都吐血了（BT的PHPWIND分页啊，这个语句是PHP初学者写出来的还正常，但PHPWIND那么成熟的社区了还有这样的问题）。
我这里简单说一下LIMIT的原理。这里以LIMIT N,M为基础：LIMIT首先要找查N+M行，然后从N行处，取M行。那么这样的SQL对一次查询1275500一个操作应该是一个昂贵的开销。对于LIMIT这类的优化，第一个目标就是让N变的尽可能的小或是不用。
怎么才能使这个N尽可能小呢。我们能做的其实就是用相对的值，给分页一个提示。如现在我们看的是第5页，看完看想看第6页，第6页同样显示是20条记录。我们就可以想到，以这个例子为准：我们可以肯定的是第6页的日值应小于第5页的，如果第5页的最小日值为：2009-11-4，那我们就可以用：
SELECT * FROM pw_gbook WHERE uid='48' and postdate<'2009-11-1' ORDER BY postdate DESC LIMIT 20; SELECT * FROM pw_gbook WHERE uid='48' and postdate<'2009-11-1' ORDER BY postdate DESC LIMIT 20;
这样来查询第6页的内容。同样对于查看第4页的内容（假设第5页的最大日期为：2009-11-3）则第4页的内容为：
SELECT * FROM pw_gbook WHERE uid='48' and postdate>'2009-11-3' ORDER BY postdate DESC LIMIT 20; SELECT * FROM pw_gbook WHERE uid='48' and postdate>'2009-11-3' ORDER BY postdate DESC LIMIT 20;
这是一个基本的思想。接下来讨论一下怎么展现的问题。
再说一下这种业务的SQL怎么实现：对于分页的展示可以用多用类型。这里说三种常用的类型：
第一种：显示“上一页” “下一页”这种类型
这种方式相对简单也就出现了我们看到那种SQL不思考的写法。合理的做法：
第一页：
SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 20; SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 20;
第二页：根据第一页的postdate进行查询如：
SELECT * FROM pw_gbook WHERE uid='48' and postdate<'2009-11-3' ORDER BY postdate DESC LIMIT 20;
SELECT * FROM pw_gbook WHERE uid='48' and postdate<'2009-11-3' ORDER BY postdate DESC LIMIT 20;
为什么说这个简单呢，这个不存在跳页的问题。接下来这种就存在一个跳页的问题了。
第二种：显示 “ 1,2,3,4,5…”
第一页： 还是以第一页的方式实现：
SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 20;
SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 20;
第二页：和原来一样。如果跳页，如从第二页跳到第5页，这里有一个第二页的最小日期为：2009-11-3（假设值，可以由第二页的程序查询得到），第二到第5，差2页，每页20条记录，那么就可以用：
SELECT * FROM pw_gbook WHERE uid='48' and postdate<'2009-11-3' ORDER BY postdate DESC LIMIT 40，20; SELECT * FROM pw_gbook WHERE uid='48' and postdate<'2009-11-3' ORDER BY postdate DESC LIMIT 40，20;
看到这里明白为什么大型网站的分页不是一下标识出来完了，让都能点了吧。也不会给你一个框让你输入一个页跳过去了。如果跳的页面过多，也就存在N值过大的问题了。所以要想办法必免。
第三种：显示 “1，2，3，4，5，…. 末页” 或是 “首页，<<100,101,102,103 >>末页”
这里有一个特殊的一地方：
别的页面的跳转的上面一样。这里就加一个末页，这里又分两种情况，如果知道最后一页是多少页，也就知道了前一页的最小日期（分页提示值），这样就可以用上面的方法查看最后一页的内容（会出现不足20条的现象），另一种，我就不知道最后是第几页，我就是想看看最后什么样子，那么就可以用（一定是显示20条）：
SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate ASC limit 20; SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate ASC limit 20;
首页这里就不在说了。
具体怎么实现搞明白了，就可以做PHP代码的修改了。稍稍修改一下，就会带来意想不到的效果。
这里只是一个通用的分页处理方法。不同的业务有可能还有不同的方法处理。如果在条件可能和情况可以考用:between … and .. 带代替limit分页操作。
第三种方法：
简单的逻辑转换。
SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20; SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20;
转换成：
SELECT * FROM pw_gbook WHERE id>1275480 and uid='48' ORDER BY postdate DESC LIMIT 20; SELECT * FROM pw_gbook WHERE id>1275480 and uid='48' ORDER BY postdate DESC LIMIT 20;
相关推荐
-
mysql 分页优化解析
如果你的数据量有几十万条,用户又搜索一些很通俗的词,然后要依次读最后几页重温旧梦.mysql该很悲壮的不停操作硬盘. 所以,可以试着让mysql也存储分页,当然要程序配合.(这里只是提出一个设想,欢迎大家一起讨论) ASP的分页:在ASP系统中有Recordset对象来实现分页,但是大量数据放在内存中,而且不知道什么时候才失效(请ASP高手指点). SQL数据库分页:用存储过程+游标方式分页,具体实现原理不是很清楚,设想如果用一次查询就得到需要的结果,或者是id集,需要后续页时只要按照结果中的I
-
mysql+php分页类（已测）
复制代码 代码如下: <?php /* mysql_pager.class.php 三个参数. mysql_query()的结果, url变量page, 您要的每页记录数 例子在这个文件底部 淡水河边整理测试 */ class mysql_pager { // define properties var $page; var $result; var $results_per_pa
-
MySQL分页分析原理及提高效率
MySQL分页分析原理及提高效率 PERCONA PERFORMANCE CONFERENCE 2009上,来自雅虎的几位工程师带来了一篇"Efficient Pagination Using MySQL"的报告,有很多亮点,本文是在原文基础上的进一步延伸. 首先看一下分页的基本原理: MySQL> explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20\G ***************** 1. row *
-
mysql分页原理和高效率的mysql分页查询语句
以前我在mysql中分页都是用的 limit 100000,20这样的方式,我相信你也是吧,但是要提高效率,让分页的代码效率更高一些,更快一些,那我们又该怎么做呢? 第一部分:看一下分页的基本原理: 复制代码 代码如下: mysql explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20***************** 1. row **************id: 1select_type: SIMPLEtable: m
-
MySQL 百万级分页优化(Mysql千万级快速分页)
以下分享一点我的经验 一般刚开始学SQL的时候,会这样写 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000, 10; 但在数据达到百万级的时候,这样写会慢死 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000000, 10; 也许耗费几十秒 网上很多优化的方法是这样的 复制代码 代码如下: SELECT * FROM table WHERE id >= (SELECT id FROM
-
mysql limit分页优化方法分享
同样是取10条数据 select * from yanxue8_visit limit 10000,10 和 select * from yanxue8_visit limit 0,10 就不是一个数量级别的. 网上也很多关于limit的五条优化准则,都是翻译自MySQL手册,虽然正确但不实用.今天发现一篇文章写了些关于limit优化的,很不错. 文中不是直接使用limit,而是首先获取到offset的id然后直接使用limit size来获取数据.根据他的数据,明显要好于直接使用limit.这
-
oracle,mysql,SqlServer三种数据库的分页查询的实例
MySql: MySQL数据库实现分页比较简单,提供了 LIMIT函数.一般只需要直接写到sql语句后面就行了.LIMIT子 句可以用来限制由SELECT语句返回过来的数据数量,它有一个或两个参数,如果给出两个参数, 第一个参数指定返回的第一行在所有数据中的位置,从0开始(注意不是1),第二个参数指定最多返回行数.例如:select * from table WHERE - LIMIT 10; #返回前10行select * from table WHERE - LIMIT 0,10; #返回前
-
php+mysql分页代码详解
复制代码 代码如下: <?php $perpagenum = 10;//定义每页显示几条 $total = mysql_fetch_array(mysql_query("select count(*) from a"));//查询数据库中一共有多少条数据 $Total = $total[0]; // $Totalpage = ceil($Total/$perpagenum);//上舍,取整 if(!i
-
在MySQL中使用LIMIT进行分页的方法
今天看一个水友说他的MySQL现在变的很慢.问什么情况时.说单表超过2个G的一个MyISAM.真垃圾的回答方式. 简单答复:换一个强劲的服务器.换服务器很管用的:) --- 最终让取到慢查询: SELECT * FROM pw_gbook WHERE uid='N' ORDER BY postdate DESC LIMIT N,N; SELECT * FROM pw_gbook WHERE uid='N' ORDER BY postdate DESC LIMIT N,N; 如: S
-
mysql中的limit用法有哪些(推荐)
SELECT * FROM 表名 limit m,n; SELECT * FROM table LIMIT [offset,] rows; 1.m代表从m+1条记录行开始检索,n代表取出n条数据.(m可设为0) 如:SELECT * FROM 表名 limit 6,5; 表示:从第7条记录行开始算,取出5条数据 2.值得注意的是,n可以被设置为-1,当n为-1时,表示从m+1行开始检索,直到取出最后一条数据. 如:SELECT * FROM 表名 limit 6,-1; 表示:取出第6条记录行以
-
mysql、mssql及oracle分页查询方法详解
本文实例讲述了mysql.mssql及oracle分页查询方法.分享给大家供大家参考.具体分析如下: 分页查询在web开发中是最常见的一种技术,最近在通过查资料,有一点自己的心得 一.mysql中的分页查询 注: m=(pageNum-1)*pageSize;n= pageSize; pageNum是要查询的页码,pageSize是每次查询的数据量, 方法一: select * from table order by id limit m, n; 该语句的意思为,查询m+n条记录,去掉前m条,返
-
MySQL百万级数据量分页查询方法及其优化建议
数据库SQL优化是老生常谈的问题,在面对百万级数据量的分页查询,又有什么好的优化建议呢?下面将列举了一些常用的方法,供大家参考学习! 方法1: 直接使用数据库提供的SQL语句 语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N 适应场景: 适用于数据量较少的情况(元组百/千级) 原因/缺点: 全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从结果集的M位置处取出N条输出,其余
-
查找MySQL中查询慢的SQL语句方法
如何在mysql查找效率慢的SQL语句呢?这可能是困然很多人的一个问题,MySQL通过慢查询日志定位那些执行效率较低的SQL 语句,用--log-slow-queries[=file_name]选项启动时,mysqld 会写一个包含所有执行时间超过long_query_time 秒的SQL语句的日志文件,通过查看这个日志文件定位效率较低的SQL .下面介绍MySQL中如何查询慢的SQL语句 一.MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句 1,slow_query_log 这
-
MySQL 中如何归档数据的实现方法
目录 什么是pt-archiver 安装 简单入门 实现原理 批量归档 不同归档参数之间的速度对比 其它常见用法 (1)删除数据 (2)将数据归档到文件中 如何避免主从延迟 常用参数 总结 归档,在 MySQL 中,是一个相对高频的操作. 它通常涉及以下两个动作: 迁移.将数据从业务实例迁移到归档实例. 删除.从业务实例中删除已迁移的数据. 在处理类似需求时,都是开发童鞋提单给 DBA,由 DBA 来处理. 于是,很多开发童鞋就好奇,DBA 都是怎么执行归档操作的?归档条件没有索引会锁表吗?安全
-
MySQL中创建表的三种方法汇总
目录 CREATE TABLE CREATE TABLE … LIKE CREATE TABLE … SELECT 总结 SQL 标准使用 CREATE TABLE 语句创建数据表:MySQL 则实现了三种创建表的方法,支持自定义表结构或者通过复制已有的表结构来创建新表,本文给大家分别介绍一下这些方法的使用和注意事项. CREATE TABLE CREATE TABLE 语句的基本语法如下: CREATE TABLE [IF NOT EXISTS] table_name ( column1 da
-
mysql中使用instr进行模糊查询方法介绍
在mysql中使用内部函数instr,可代替传统的like方式查询,并且速度更快. instr 函数,第一个参数是字段,第二个参数是要查询的串,返回串的位置,第一个是1,如果没找到就是0. 例如,查询字段name中带"军"的名字,传统的方法是: select name from 用户表 where name like `%军%'; 用instr的方法: select name from 用户表 where instr('name','军'); 或: select name from 用
-
mysql中合并两个字段的方法分享
例如:select (a+b) as c from 表 类型也一致都是varchar型的,可就是显示不正确. 直到concat() MySQL中concat函数 下面通过一个例子介绍MySQL中的concat函数的使用方法,比如select concat('11','22','33'). MySQL中concat函数 使用方法: CONCAT(str1,str2,-) 返回结果为连接参数产生的字符串.如有任何一个参数为NULL ,则返回值为 NULL. 注意: 如果所有参数均为非二进制字符串,则
-
mysql中datetime类型设置默认值方法
通过navicat客户端修改datetime默认值时,遇到了问题. 数据库表字段类型datetime,原来默认为NULL,当通过界面将默认值设置为当前时间时,提示"1067-Invalid default value for 'CREATE_TM'",而建表的时候,则不会出现这个问题,比如建表语句: CREATE TABLE `app_info1` ( `id` bigint(21) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID', `a