文档章节

Mysql 分页语句Limit用法

李有常
 李有常
发布于 2015/08/22 16:10
字数 1422
阅读 361
收藏 26
点赞 0
评论 0

1、Mysql的limit用法

 

在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能。

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

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1): 为了与 PostgreSQL 兼容,MySQL 也支持句法: LIMIT # OFFSET #。

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15  
  
//为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 -1:   
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.  
  
//如果只给定一个参数,它表示返回最大的记录行数目:   
mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行  
  
//换句话说,LIMIT n 等价于 LIMIT 0,n。

 

    【引用,路人乙:Mysql中limit的用法详解

 

2、Mysql的分页查询语句的性能分析

 

 

      MySql分页sql语句,如果和MSSQL的TOP语法相比,那么MySQL的LIMIT语法要显得优雅了许多。使用它来分页是再自然不过的事情了。

 

2.1 最基本的分页方式: 

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

在中小数据量的情况下,这样的SQL足够用了,唯一需要注意的问题就是确保使用了索引:

举例来说,如果实际SQL类似下面语句,那么在category_id, id两列上建立复合索引比较好:

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10

 

2.2 子查询的分页方式:

 

随着数据量的增加,页数会越来越多,查看后几页的SQL就可能类似:

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10

一言以蔽之,就是越往后分页,LIMIT语句的偏移量就会越大,速度也会明显变慢。

此时,我们可以通过子查询的方式来提高分页效率,大致如下:


SELECT * FROM articles WHERE  id >=  
 (SELECT id FROM articles  WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10

 

2.3 JOIN分页方式

 

SELECT * FROM `content` AS t1   
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2   
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

    经过我的测试,join分页和子查询分页的效率基本在一个等级上,消耗的时间也基本一致。

explain SQL语句:

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY <derived2> system NULL NULL NULL NULL 1  

1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where

2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index

 

----------------------------------------

 

为什么会这样呢?因为子查询是在索引上完成的,而普通的查询时在数据文件上完成的,通常来说,索引文件要比数据文件小得多,所以操作起来也会更有效率。

 

实际可以利用类似策略模式的方式去处理分页,比如判断如果是一百页以内,就使用最基本的分页方式,大于一百页,则使用子查询的分页方式。

【引用原文,energy1010的空间:MySql分页sql语句

 

 

3、Oracle分页查询语句

Oralce数据库 

从数据库表中第M条记录开始检索N条记录 

Sql代码  收藏代码

  1. SELECT * FROM (SELECT ROWNUM r,t1.* From 表名称 t1 where rownum < M + N) t2   

  2.  where t2.r >= M   

   例如从表Sys_option(主键为sys_id)中从第10条记录开始检索20条记录,语句如下: 

Sql代码  收藏代码

  1. SELECT * FROM (SELECT ROWNUM R,t1.* From Sys_option where rownum < 30 ) t2   

  2. Where t2.R >= 10   

  

3、MSSQLSERVER分页查询语句

 

SQL Server主要利用 SELECT TOP语句分页,具体方案,请参考

 

------------------------------------- 

 

分页方案一:(利用Not In和SELECT TOP分页) 

语句形式: 

 

Sql代码  收藏代码

  1. SELECT TOP 10 *   

  2. FROM TestTable   

  3. WHERE (ID NOT IN   

  4. (SELECT TOP 20 id   

  5. FROM TestTable   

  6. ORDER BY id))   

  7. ORDER BY ID   

   

Sql代码  收藏代码

  1. SELECT TOP 页大小 *   

  2. FROM TestTable   

  3. WHERE (ID NOT IN   

  4. (SELECT TOP 页大小*页数 id   

  5. FROM 表   

  6. ORDER BY id))   

  7. ORDER BY ID   

  8. SELECT TOP 页大小 *   

Sql代码  收藏代码

  1. FROM TestTable   

  2. WHERE (ID >   

  3. (SELECT MAX(id)   

  4. FROM (SELECT TOP 页大小*页数 id   

  5. FROM 表   

  6. ORDER BY id) AS T))   

  7. ORDER BY ID   

  

------------------------------------- 

 

分页方案二:(利用ID大于多少和SELECT TOP分页) 

语句形式: 

Sql代码  收藏代码

  1. SELECT TOP 10 *   

  2. FROM TestTable   

  3. WHERE (ID >   

  4. (SELECT MAX(id)   

  5. FROM (SELECT TOP 20 id   

  6. FROM TestTable   

  7. ORDER BY id) AS T))   

  8. ORDER BY ID   

  

------------------------------------- 

分页方案三:(利用SQL的游标存储过程分页) 

 

Sql代码  收藏代码

  1. create procedure XiaoZhengGe   

  2. @sqlstr nvarchar(4000), --查询字符串   

  3. @currentpage int, --第N页   

  4. @pagesize int --每页行数   

  5. as   

  6. set nocount on   

  7. declare @P1 int, --P1是游标的id   

  8. @rowcount int   

  9. exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output   

  10. select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页   

  11. set @currentpage=(@currentpage-1)*@pagesize+1   

  12. exec sp_cursorfetch @P1,16,@currentpage,@pagesize   

  13. exec sp_cursorclose @P1   

  14. set nocount off   

  

其它的方案:如果没有主键,可以用临时表,也可以用方案三做,但是效率会低。 

建议优化的时候,加上主键和索引,查询效率会提高。 

 

通过SQL 查询分析器,显示比较:我的结论是: 

分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句 

分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句 

分页方案三:(利用SQL的游标存储过程分页) 效率最差,但是最为通用 

 

在实际情况中,要具体分析。 

 

【引用:在SQL Server中通过SQL语句实现分页查询 

 


© 著作权归作者所有

共有 人打赏支持
李有常
粉丝 5
博文 103
码字总数 31154
作品 0
威海
后端工程师
mysql的limit经典用法及优化

用法一 SELECT .* FROM keyword_rank WHERE (advertiserid='59') LIMIT 2 OFFSET 1; 比如这个SQL ,limit后面跟的是2条数据,offset后面是从第1条开始读取。 用法二 SELECT .* FROM keyword...

drewin ⋅ 2013/09/24 ⋅ 3

数据库查询返回特定结果即分页查询

1 几种不同数据库的不同的分页写法: a mysql a) 查询前n条记录select * from table_name limit 0,nb) 查询第n条到第m条select * from table_name limit n,m b oracle a)查询前n条记录selec...

王小明123 ⋅ 2013/06/08 ⋅ 0

常见mysql语句使用小结

原生的mysql操作语句,从基础上讲,还是比较简单的,但是其实做一般的二次开发,能够手写大部分原生的mysql语句,已经足够进行使用了。一般用多了框架的,反而会觉得写原生mysql很不方便,实...

熊猫88 ⋅ 2015/11/14 ⋅ 0

PHP+MySQL分页原理

1、SQL语句中的limit用法 SELECT * FROM table …… limit 开始位置 , 操作条数 (开始位置从0开始) SELECT * FROM table …… limit 0 , 20 (取前面20条) SELECT * FROM table …… li...

方赛 ⋅ 2014/07/23 ⋅ 0

mysql limit 用法-分页

在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能。 LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数...

Oscarfff ⋅ 2016/09/02 ⋅ 0

MySQL高效分页解决方案集

很久以前的一次面试中,被面试官问到这个问题,由于平时用到的分页方法不多,只从索引、分表、使用子查询精准定位偏移以外,没有使用到其它方法。 后来在看其它博客看到了一些不同的方案,也...

杨太化 ⋅ 2015/10/13 ⋅ 0

mysql--------大数据量分页sql语句优化

分页程序原理很简单,这里就不多说了,本篇文章主要说的是在数据表记录量比较大的情况下,如何将分页SQL做到更优化,让MySQL执行的更快的方法。 一般的情况下,我们的分页SQL语句是这样的: ...

切切歆语 ⋅ 01/25 ⋅ 0

如何优化Mysql千万级快速分页

看例子: 数 据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text, id 是逐渐,vtype是tinyint,vtype是索引。这是一个基本的新闻系统的简单模型。现在往里...

上品好礼生活馆 ⋅ 2014/09/18 ⋅ 1

mysql 优化实例(百万级数据)

MySql 这个数据库绝对是适合dba级的高手去玩的,一般做一点1万篇新闻的小型系统怎么写都可以,用xx框架可以实现快速开发。可是数据量到了10万,百万至千万,他的性能还能那么高吗?一点小小的...

tantexian ⋅ 2016/04/22 ⋅ 0

mysql分页与分页性能查询优化

mysql分页就直接使用limit进行操作,limit如果我们直接不加任何处理可能数据大了就会很卡的。 一. 最常见MYSQL最基本的分页方式: select from content order by id desc limit 0, 10 在中小...

mikiyonney ⋅ 2015/04/20 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

到底会改名吗?微软GVFS 改名之争

微软去年透露了 Git Virtual File System(GVFS)项目,GVFS 是 Git 版本控制系统的一个开源插件,允许 Git 处理 TB 规模的代码库,比如 270 GB 的 Windows 代码库。该项目公布之初就引发了争...

linux-tao ⋅ 28分钟前 ⋅ 0

笔试题之Java基础部分【简】【二】

1.静态变量和实例变量的区别 在语法定义上的区别:静态变量前要加static关键字,而实例变量前则不加。在程序运行时的区别:实例变量属于某个对象的属性,必须创建了实例对象,其中的实例变...

anlve ⋅ 45分钟前 ⋅ 0

Lombok简单介绍及使用

官网 通过简单注解来精简代码达到消除冗长代码的目的 优点 提高编程效率 使代码更简洁 消除冗长代码 避免修改字段名字时忘记修改方法名 4.idea中安装lombnok pom.xml引入 <dependency> <grou...

to_ln ⋅ 今天 ⋅ 0

【转】JS浮点数运算Bug的解决办法

37.5*5.5=206.08 (JS算出来是这样的一个结果,我四舍五入取两位小数) 我先怀疑是四舍五入的问题,就直接用JS算了一个结果为:206.08499999999998 怎么会这样,两个只有一位小数的数字相乘,怎...

NickSoki ⋅ 今天 ⋅ 0

table eg

user_id user_name full_name 1 zhangsan 张三 2 lisi 李四 `` ™ [========] 2018-06-18 09:42:06 星期一½ gdsgagagagdsgasgagadsgdasgagsa...

qwfys ⋅ 今天 ⋅ 0

一个有趣的Java问题

先来看看源码: public class TestDemo { public static void main(String[] args) { Integer a = 10; Integer b = 20; swap(a, b); System.out......

linxyz ⋅ 今天 ⋅ 0

十五周二次课

十五周二次课 17.1mysql主从介绍 17.2准备工作 17.3配置主 17.4配置从 17.5测试主从同步 17.1mysql主从介绍 MySQL主从介绍 MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主...

河图再现 ⋅ 今天 ⋅ 0

docker安装snmp rrdtool环境

以Ubuntu16:04作为基础版本 docker pull ubuntu:16.04 启动一个容器 docker run -d -i -t --name flow_mete ubuntu:16.04 bash 进入容器 docker exec -it flow_mete bash cd ~ 安装基本软件 ......

messud4312 ⋅ 今天 ⋅ 0

OSChina 周一乱弹 —— 快别开心了,你还没有女友呢。

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @莱布妮子 :分享吴彤的单曲《好春光》 《好春光》- 吴彤 手机党少年们想听歌,请使劲儿戳(这里) @clouddyy :小萝莉街上乱跑,误把我认错成...

小小编辑 ⋅ 今天 ⋅ 9

Java 开发者不容错过的 12 种高效工具

Java 开发者常常都会想办法如何更快地编写 Java 代码,让编程变得更加轻松。目前,市面上涌现出越来越多的高效编程工具。所以,以下总结了一系列工具列表,其中包含了大多数开发人员已经使用...

jason_kiss ⋅ 昨天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部