假设我们表的结构如下。
create table user (
id int PRIMARY KEY,
username varchar,
.......
sort int,
created_at int,
)
方案一：此方案适用于web端,需要详细的页数
1.对表的count(*)建立缓存,增删时进行字段增减
2.对order by 字段建立索引(除创建时间，创建时间完全可用id代替)
3.先查询出分页的所有id，在由ID查询关联出的数据
4.视情况可对 对应页数的数据进行缓存
例:redis建立表的count: get user_count;
select * from user u1,(select id from user order by id asc limit 20 offset 0) u2 where u1.id = u2.id;
但是当offset的数值越大，查询效率就越慢，因为mysql需要扫描的行数就越多。我们可对某页(比如说10000页)以后的数据进行缓存，并且定时更新。这样的方案就是页码靠后的数据更新会不及时
方案二：适用于app或h5端，不需要跳页，只需要加载下一页或上一页数据的业务场景，当前也可以结合上面的方法使用，跳页使用方案一，翻页使用方案二
我们可以记录/让前端传递 order by 字段的值
select * from user order by sort asc,id asc limit 20;
假设第一页最后一条数据为 (sort:10, id:20)
下一页的sql语句可为：
select * from user where id >20 and sort > 10 order by sort asc,id asc limit 20;
这样的查询语句查询页码大的数据时就可以根据索引过滤掉无效的数据，效率会提高很多
上面的sql语句也可以优化成
select * from user u1,(select id from user where id >20 and sort > 10 order by sort asc,id asc limit 20) u2 where
u1.id = u2.id
但是这样的方案是有局限性的，不能用where
此外还有些小技巧
1.前端做提前加载下一页，点击下一页的时候直接渲染数据就可以了
2.可对表进行分页，例如列表页只需显示 标题、图片....可将这些字段单独为一张表，其他数据放在另一张表中。(此方案需要预估以后产品可能会显示的字段或者说需要十分确定哪些字段是不用显示在列表页中的)
3.列表页的图片一般是缩略图，如果确定表行数不是特别大的时候，可单独为一个字段，以base64格式存储，前端加载图片时就无需进行网络请求
