在绿豆芽的时候和同事讨论过关于分页的问题，今天看见小马哥的分页逻辑，之前的作业帮的朋友讨论过分页的问题，这种高频出现的问题有必要整理一下，就像做数学题一样，要有一个自己的错题库，时不时翻阅，先理论，再实践。
select a, b, c from t1 limit 10000,10;
看似只查询了10条记录，实际这条SQL是先读取100010条数据，然后抛弃了前10000条记录，然后读取掉后面10条想要的数据，因此要查询一张大表比较靠后的数据，执行效率是非常低的
两种分页场景的优化
- 主键自增连续排序的分页查询
# 很适合前端传来最大的一个id，来代替 id > 后面的值
select * from t1 where id > 9900 limit 2;
# 不适用场景1，表中某些记录可能被删除，主键空缺，导致结果不一致
delete from t1 where id = 10;
select * from t1 limit 9900,2; -- 99002,99003
select * from t1 where id > 9900 limit 2; -- 99001,99002
# 不适用场景2，原SQL是order by非主键的字段，按照上述方法结果也会不一致
- 非主键字段排序的分页查询
select * from t1 order by a limit 9900,2;
即使a字段有索引，很可能也不会用，因为扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高，所以优化器放弃使用所用，优化的关键是让排序时返回的字段尽可能少，可以让排序和分页操作先查找到对应的记录
select * from t1 f inner join (select id from t1 order by a limit 9900,2) g on f.id = g.id;
所以说，一个表的主键是自增的，但是中间有部分记录被删除了，也就是主键不连续，也可以用刚才的思路优化
select * from t1 f inner join (select id from t1 order by a limit 9900,2) g on f.id = g.id;
表名:order_history 描述:某个业务的订单历史数据 主要字段:ungigned int id, tinyint(4) int type 字段情况:37个字段，不包含text,最大varchar(500),字段为索引，且递增
# 关于严格精准计数的优化方案，就是之前总结的一个事务中加一个表一个字段计数
select count(*) from order_history; -- 8903ms 82323ms 8401ms
# 默认按照id升序排序
select * from orders_history where type = 8 limit 10000,10; -- 3040ms,3063ms,3018ms
# limit对比
select * from orders_history where type = 8 limit 10000,1; -- 3072ms,3092ms,3002ms
select * from orders_history where type = 8 limit 10000,10; -- 3081ms,3077ms,3032ms
select * from orders_history where type = 8 limit 10000,100; -- 3118ms,3200ms,3128ms
select * from orders_history where type = 8 limit 10000,1000; -- 3412ms,3468ms,3394ms
select * from orders_history where type = 8 limit 10000,10000; -- 3749ms,3802ms,3696ms
关于limit，查询量少于100条，基本无时间差距，随着查询的记录量越来越大花费时间越来越多
# offset对比
select * from orders_history where type = 8 limit 100,100; -- 25ms,24ms,24ms
select * from orders_history where type = 8 limit 1000,10; -- 78ms,76ms,77ms
select * from orders_history where type = 8 limit 10000,10; -- 3092ms,3212ms,3128ms
select * from orders_history where type = 8 limit 100000,10; -- 3878ms,3812ms,3798ms
select * from orders_history where type = 8 limit 100000,10; -- 14608ms,14062ms,14700ms
关于limit，结果很明显了
使用子查询优化
select * from orders_history where type = 8 limit 100000,1; -- 3674ms
select id from orders_history where type = 8 limit 100000,1; -- 1315ms
select * from orders_history where type = 8 and id >=
(select id from orders_history where type = 8 limit 100000,1) limit 100; -- 1327ms;
select * from orders_history where type = 8 limit 100000,100; -- 3710ms
- sql1和sql2对比：使用select id 比 select * 速度快了3倍
- sql2和sql3对比：只相差了几十ms
- sql3和sql4对比：得益于select id 速度快了3倍
使用id限定优化:id连续自增(大表分库分表时不建议id为唯一标识，应该使用分布式的高并发唯一id生成器来生成，并在数据表中使用另外一个字段存储)
select * from orders_history where type = 8 and in between 100000 and 1000100 limit 100; -- 15ms 12ms 9ms
select * from order_history where id >= 1000001 limit 100;
select * from order_history where id in (
select order_id from trade_2 where gooods = 'pen'
) limit 100;
使用临时表优化，但是不属于查询优化了，在id限定优化中，id是需要连续递增的，但在一些场景下，比如历史表，出现缺失的时候，可以使用临时存储的表来记录分页的id，使用分页的id进行in查询，千万表的数据也得起飞
回归主题，看一下今天这个SQL
select
id, user_id
from
x_table
where
id > $min_id
and
identity_type = 2 and plus_status = 1 and join_time < $end_time and user_id > 0
order by
id asc
limit
1000;
这个表是id自增的，按id排序的，所以说小马哥对
