msql分页

分页案例

-- 1. count(*) 解疑
select count(email) from myshop.ecs_users;  -- 不统计null
select count(1) from myshop.ecs_users; -- 不解析内容
select count(*) from myshop.ecs_users; -- 标准SQL

-- 2. 分页（查你需要的字段，不要*）
select * from myshop.ecs_order_info order by order_id limit 1,100;
select * from myshop.ecs_order_info order by order_id limit 4000000,100;

-- 递增ID，连续不中断
select * from myshop.ecs_order_info o where o.order_id between 4000000 and 4000100;

-- 递增ID， 不连续
select * from myshop.ecs_order_info o where o.order_id 
>=
(select order_id from myshop.ecs_order_info order by order_id limit 4000000,1) limit 100;

-- 无序读取

SELECT * FROM myshop.ecs_users u where u.last_login_time >= 1590076800 order by u.last_login_time,u.user_id limit 1, 10;

SELECT * FROM myshop.ecs_users u where u.last_login_time >= 1590076800 order by u.last_login_time,u.user_id limit 200000, 10;

最优
SELECT * FROM myshop.ecs_users u , 
( 
    SELECT user_id FROM myshop.ecs_users u where u.last_login_time >= 1590076800 order by u.last_login_time,u.user_id limit 200000, 10 
) u1 where u1.user_id = u.user_id order by u.user_id;