MySQL InnoDB 300+w数据 offset分页方案优化 实践

原理

当offset特别大时，这条语句的执行效率会明显减低，而且效率是随着offset的增大而降低的。
原因为：
MySQL并不是跳过offset行，而是取offset+N行，然后放弃前offset行，返回N行，当offset特别大，然后单条数据也很大的时候，每次查询需要获取的数据就越多，自然就会很慢。
MySQL分页查询offset过大，Sql优化经验
求求你不要再用offset和limit了

方案SQL

-- 分页取300w条数据后的100条
select * from test_table where ENABLED = 1 limit 100 offset 3000000;
select id from test_table where ENABLED = 1 limit 100 offset 3000000;
-- 分页取300w条数据后的10条
select * from test_table where ENABLED = 1 limit 10 offset 3000000;
select id from test_table where ENABLED = 1 limit 10 offset 3000000;
-- 使用between
select * from test_table where ENABLED = 1 and id between 3000000 and 3000100;
-- 使用limit1 查询id起始位置
set @startId = (select id from test_table where ENABLED = 1 limit 1 offset 3000000);
set @endId = (select id from test_table where ENABLED = 1 limit 1 offset 3000100);
select * from test_table where ENABLED = 1 and id between @startId  and @endId;
-- 记录下上次分页后最后一条的id 直接使用id > 上次最后一个id作为条件进行分页
select * from test_table where ENABLED = 1 and id > 3000000 limit 100;

执行耗时记录

select * from test_table where ENABLED = 1 limit 100 offset 3000000;

select id from test_table where ENABLED = 1 limit 100 offset 3000000;

select * from test_table where ENABLED = 1 limit 10 offset 3000000;

select id from test_table where ENABLED = 1 limit 10 offset 3000000;

-- 使用limit1 查询id起始位置
set @startId = (select id from test_table where ENABLED = 1 limit 1 offset 3000000);
set @endId = (select id from test_table where ENABLED = 1 limit 1 offset 3000100);
select * from test_table where ENABLED = 1 and id between @startId  and @endId;

王者归来

-- 使用between
select * from test_table  where ENABLED = 1 and id between 3000000 and 3000100;

between必须id连续 处理方案 保存上次结果最后一个id 下次查询起始使用最后一个id作为条件

-- 记录下上次分页后最后一条的id 直接使用id > 上次最后一个id作为条件进行分页
select * from test_table where ENABLED = 1 and id > 3000000 limit 100;

单独查询id作为字表

select * from test_table  a, (select id from test_table where ENABLED = 1 order by id desc limit 10 offset 3000000) b where a.id = b.id

结论

• limit分页大小 不会影响查询耗时
• offset越大 查询越慢
• select * 比 select <索引字段> 更耗时
• between 每次分页数据大小不固定 但是速度确实最快