大家好,又见面了,我是你们的朋友全栈君。
mysql分页查询总结
mysql提供分页的功能:
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
LIMIT子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)。下面,我们针对特例对mysql分页查询进行总结。
最简单的用法就是:
select * from table limit ?,?
这种是最简单的limit分页查询。配合where条件使用:
select * from table where column > ? order by id limit ?,?
上边这种情况,建议在column和id建立复合索引比较好。
以上两种情况,对于小数据量分页查询时,这样的sql就足够用了。但是对于百万级以上的数据表,如果使用上边的sql 的话,越往后limit语句的偏移量越来越大,查询就会变得越来越慢。类似于:
select * from `user` where `cate`=’陕西’ order by id limit 100000,10
为了避免这种查询,我们可以通过子查询的方式来提高查询效率。
select * from `user` where id >=(select * from `user` where `cate`=’陕西’ order by id limit 100000,1) and `cate`=’陕西’ limit 10
通过explain我们可以看出直接limit查询和通过子查询的差距:
直接limit查询:
type
possible_keys
key
key_len
ref
rows
Extra
ALL
(NULL)
(NULL)
(NULL)
(NULL)
4076607
子查询分页查询:
type
possible_keys
key
key_len
ref
rows
Extra
PRIMARY
range
PRIMARY
PRIMARY
4
2038331
Using where
SUBQUERY
index
(NULL)
PRIMARY
4
4076663
Using index
可以看出,通过子查询的方式,子查询是在索引上进行的,而普通的查询是在数据文件上进行的。 通常来说,索引文件要比数据文件小的多,所以操作索引文件更直接高效。
此外,还可以通过join分页方式
SELECT * FROM `user` AS t1
JOIN (SELECT id FROM `user` ORDER BY id LIMIT 100000, 1) AS t2
WHERE t1.id <= t2.id ORDER BY t1.id LIMIT 10;
join分页和子查询分页的效率基本在一个等级上。(但是,子查询需要在内存中建立临时表,查询完毕后,MySQL需要撤销这些临时表。通过join可以避免这种情况)在分页查询前,可以进行判断,如果是在限定页数内,就使用基本分页查询,大于则使用子查询分页处理。
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/149158.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...