MySQL分页 (limit offset ,N)并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回 N 行,那当offset特别大的时候,效率就非常的低下。
比如 limit 9999990,10 则mysql 一共会查询 9999990+10条数据(一千万条)然后抛弃掉前边的9999990条数据,返回最后10条
优化思路:
控制返回的总页数
对超过特定阈值的页数进行 SQL改写。
未优化时普通sql
SELECT count(id) FROM `alarm_base`
SELECT * FROM alarm_base limit 9000000,25
优化方式一、索引覆盖+子查询优化
SELECT * FROM alarm_base WHERE id >=(SELECT id FROM alarm_base limit 9000000,1)
ORDER BY id limit 25
可能有小伙伴有疑问了,你子查询不也得查900_0000+1条数据吗?确实是这样,但查的仅仅只是900_0000+1条数据的Id,且Id还是主键
优化方式二、起始位置重定义
# 前提 1:(默认ID自增生成、升序排列)
# id> (pageIndex-1)*pageSize
# 前提二 2:(记住上次查找结果的主键位置,但只能一页一页分,跳页会有问题)
# 两个前提满足其一即可使用
# sql
SELECT * FROM alarm_base WHERE id>=2300316 limit 25
优化方式三、利用延迟关联或者子查询
正例:先快速定位需要获取的 id 段,然后再关联:
SELECT t1.* FROM 表 as t1, (select id from 表 where 条件 LIMIT 100000,20 ) as t2 where t1.id=t2.id
改写前:
改写后:
优化方式四、降级策略
配置limit的偏移量和获取数一个最大值,超过这个最大值,就返回空数据(超过这个值你已经不是在分页了,而是在刷数据了,如果确认要找数据,
应该输入合适条件来缩小范围,而不是一页一页分页。)
这种降级逻辑在ES中也有体现,当深分页数据大于10000时会报错。。。