mysql大数据分页查询优化方案

测试pepole表大小：10009000

方案1--按id查：每次分页查询携带上页最后一行数据的id，然后根据id查询，大于上一页最后一条的id。

-- id > 上页最后一行id
select * from pepole where id > 8000000 and age>10 limit 100
测试耗时：0.025s

优点：根据上页最后一条记录id查，效果佳。

缺点：只能连续分页查询，不能跳页。

方案2--子查询id：查询时当前id>(查询当前页第一行id) limit 分页大小

-- 子查询查出当前分页的首行id
select * from pepole where id >= (select id from pepole where time>202203 limit 8000000,1 ) limit 100
耗时：1.966s

优点：比常规分页写法能只快几倍。中等水平数据量，配合其他优化，一般查询能到5秒内。

缺点：优点不是很突出，并且子查询结果的id要有正序，否则id>=就不准了。

方案3--初始化记录每页最后一行id：使用用户变量给整个查询结果赋顺序号，然后跟分页大小求余，得出每页最后一行记录id，存起来，后续每次查询都可以拿到id，然后根据id查。

-- 1、定义变量，给每行数据设置序号，再按每页大小，求余计算出得出每页最后一行的id
select r.idx,r.id from (
	select (@idx := @idx + 1) as idx, a.id from (select id from pepole where age>10 ) a, (select @idx := 0) x
)r where r.idx%100 = 0
耗时：14.272s

-- 计算出来的每页最后一样id，缓存起来，例如redis：key=用户id+查询条件+页码，value=主键id

-- 2、删除变量
select @idx:=null
-- 用户变量注意并发情况，所以每次使用变量要自定义唯一

-- 3、根据页码获取缓存里的主键id查询
select * from pepole where id > 8000000 and age>10 limit 100
耗时：0.045s

缺点：初始化每页首行id，需要时间较多，还需要缓存起来。

优点：一旦初始化完成，每页都可便捷获取到id，查询速度是极快的。

适用场景：海量数据，接受查询初始化需要一定时间的。