mysql limit分页用法讲解

1.情景展示

在Oracle当中，使用rownum来进行分页；

而在mysql中，是没有rownum的，需要通过limit来实现。

2.用法介绍

limit [offset,]rows

limit后面可以跟一个参数，也可以跟两个参数；

格式一：

limit rows

rows代表行数，表示：获取前rows行数据。

等价于：

limit 0, rows

格式二：

limit offset,rows

offset代表偏移量，rows代表行数，表示：获取偏移量后面的rows行数据，相当于查询的是：

offset<rownum≤rows

或offset+1≤rownum≤rows。

偏移量的初始值为0（不是1），这就是可以解释，为什么格式一当中的两种方式一样的原因啦。

举例子：

-- 获取表前5行数据
SELECT 1 FROM meta_theme ORDER BY THEMEID LIMIT 5;
SELECT 1 FROM meta_theme ORDER BY THEMEID LIMIT 0,5;

获取第6-10行数据

分析：

6-10行，也就是10-6+1=5，共5行数据；

由于limit第一个参数的偏移量不在查询范围内，所以，要想第6行包含在内，需要：6-1=5。

使用limit的问题，就在进行分页查询的时候，有点绕。

知道了limit偏移量的特性之后，我们可以发现：

要想，将第一行数据包含在内，就需要限制limit的第一个参数值必须为0；

否则的话，我们进行分页时，将会将以第一条数据遗漏，比方说：

分页虽然从数量量没有错，但是，此时已经将第一条数据排除在外了。

所以，需要保证，limit的第一页数据包含第一行记录，也就是limit需要从0开始。

LIMIT (pageIndex-1)*pageSize, pageSize

pageSize起始值为1。

注意事项：

limit 后面只能跟数字，不能跟其它

-- 错误用法1：limit后面跟 字符串转数字函数
LIMIT CAST('10' AS SIGNED)
-- 错误用法2：limit后面跟 数字计算
LIMIT 20-10

3.性能分析

基本用法

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

在中小数据量的情况下，这样的SQL足够用了；

我们可以使用了复合索引来提升查询速度（where的限制条件与order by的排序条件组合成联合索引）；

SELECT THEMEID,2 test FROM meta_theme where STATUS=1 ORDER BY THEMEID LIMIT 0,5;

在这里，为了提升查询速度，我们就可以给字段STATUS+THEMEID，创建索引。

PS：表主键是唯一索引。

子查询分页

随着数据量的增加，页数会越来越多，越往后分页，LIMIT语句的偏移量就会越大，速度也会明显变慢；

可以通过子查询的方式来提高分页效率。

-- 查询第10001-10010条数据
SELECT
	THEMENAME,
	THEMECODE 
FROM
	meta_theme 
WHERE
	THEMEID >= ( SELECT THEMEID FROM meta_theme WHERE THEMELEVEL = 5 ORDER BY THEMEID LIMIT 10000, 1 ) 
	LIMIT 10

自连接

也可以通过自连接的方式来提高分页效率。

SELECT t1.THEMEID, THEMENAME, THEMECODE FROM meta_theme t1,( SELECT THEMEID FROM meta_theme ORDER BY THEMEID DESC LIMIT 10, 1 ) t2 
WHERE
	t1.THEMEID < t2.THEMEID 
ORDER BY
	t1.THEMEID DESC 
	LIMIT 10;

小结：

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