1、分页查询要order by id,这样才能用到索引。
2、当数据量很大时,查询后面的页码偏移量很大,消耗的时间相应增多,如每页十行,查询1001页,偏移为 limit 10000,10 以下的sql语句就不适用了
select * from content order by id desc limit 10000,10;
可以使用子查询来改善:
select * from content where id<=(select id from content order by id desc limit 10000, 1) order by id desc limit 10;
或者连接查询:
select t1.* from content t1 join (select id from content order by id desc limit 10000, 1) as t2 on t1.id<t2.id order by t1.id desc limit 10;