数据库分页查询
2022-11-21
数据库分页查询
数据库分页语句使用limit语法来实现。除了基础的limit用法，还有一些优化的方法。
LIMIT基础用法
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
参数1表示偏移量，参数2表示返回记录行的最大数目。
需要注意：初始偏移量是0而不是1。
举例：
SELECT * FROM table LIMIT 5,10; # 检索记录行 6-15
SELECT * FROM table LIMIT 95,-1; # 检索记录行 96-last
SELECT * FROM table LIMIT 5; # 检索前 5 个记录行 （LIMIT n 等价于 LIMIT 0,n）
select * from table LIMIT 4 offset 9; # 返回从第9行开始的4条记录 （此时第1个参数表示最多返回几行记录，offset后面的第2个参数表示从第4行开始）
# 返回第pageNum页,每页条数为numPerPage 条数据的sql语句写法为
select * from table limit (pageNum-1)*numPerPage ,numPerPage # 方法1
select * from table limit numPerPage offset (pageNum-1)*numPerPage # 方法2
优化分页查询方法
由于基础的分页查询方式会从数据库第一条记录开始扫描，所以当LIMIT的offset较大时，查询速度会越来越慢。下面列举一些优化的分页查询方法。
参考：
https://www.w3cschool.cn/mysql/mysql-xilz2oy6.html
https://segmentfault.com/a/1190000008859706
使用子查询优化
SELECT * FROM articles WHERE id >=
(SELECT id FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10;
SELECT * FROM news WHERE id IN (SELECT id FROM news ORDER BY id limit (page * (per_page - 1)), per_page);
即，使用id来直接定位。但不知道具体id是多少，所以需要用子查询来先获取。
使用 id 限定优化
select * from orders_history where type=2 and id between 1000000 and 1000100 limit 100;
select * from orders_history where id >= 1000001 limit 100;
前提是知道具体id是多少：这种方式假设数据表的id是连续递增的，则我们根据查询的页数和查询的记录数可以算出查询的id的范围。
优化总结
为了保证index索引列连续，可以为每个表加一个自增字段（作为id），并且加上索引。
需要提醒的是，所有的分页查询条件都必须建立索引，并且不能有 TEXT 字段。MySQL 的索引字段中，VARCHAR(32) 我都觉得很大了， TEXT 字段简直是不能容忍的，不怕死的可以自己试试。 （https://www.mywaiting.com/weblogs/pagination-with-mysql/）
参考链接
https://www.cnblogs.com/duhuo/p/4245209.html
https://www.w3cschool.cn/mysql/mysql-xilz2oy6.html