分页sql

正常分页

LIMIT #offset#, #pageSize#

select * from sop_project_base order by id limit 1052,200

游标分页

WHERE cursor > #cursor# LIMIT #pageSize#


select * from sop_project_base where id>1052 order by id  limit 20

缓存子查询

SELECT * FROM `crm_account_info` WHERE id > (SELECT id FROM `crm_account_info` WHERE type=0 ORDER BY id LIMIT 899999,1) ORDER BY id LIMIT 10;

SELECT * FROM `crm_account_info` WHERE type=0 ORDER BY id LIMIT 899990,10；

覆盖索引

SELECT * FROM   crm_account_info AS a, (SELECT id FROM `crm_account_info` WHERE type=0 ORDER BY id LIMIT 900000,10) AS b WHERE a.id =b.id ;

反向查询

$ = SELECT count(1) FROM `crm_account_info` ；
SELECT * FROM `crm_account_info`  WHERE type=0 ORDER BY id desc LIMIT ($-9), 10;