1、MySQL 深度分页如何优化
模仿百度、谷歌方案(前端业务控制),业务层面限制跨度比较大的跳页
类似于分段。我们给每次只能翻100页、超过一百页的需要重新加载后面的100页。这样就解决了每次加载数量数据大 速度慢的问题了;
记录每次取出的最大id, 然后where id > 最大id;
通过in获取id;
join方式 + 覆盖索引(推荐)
select * from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id)
如果对于有where 条件,又想走索引用limit的,必须设计一个索引,将where 放第一位,limit用到的主键放第2位,而且只能select 主键!
select id from test where pid = 1 limit 100000,10;`
创建索引:`alter table test add index idx_pid_id(pid, id)
查询偏移量过大的场景我们称为深度分页,这会导致查询性能较低,例如:
# MySQL 在无法利用索引的情况下跳过1000000条记录后,再获取10条记录
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10
(1)范围查询
当可以保证 ID 的连续性时,根据 ID 范围进行分页
# 查询指定 ID 范围的数据
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id
# 也可以通过记录上次查询结果的最后一条记录的ID进行下一页的查询:
SELECT * FROM t_order WHERE id > 100000 LIMIT 10
(2)子查询
(子查询的结果会产生一张新表,会影响性能,应该尽量避免大量使用子查询,这种方法只适用于 ID 是正序的,在复杂分页场景,往往需要通过过滤条件,筛选到符合条件的 ID,此时的 ID 是离散且不连续的)
先查询出 limit 第一个参数对应的主键值,再根据这个主键值再去过滤并 limit
# 通过子查询来获取 id 的起始值,把 limit 1000000 的条件转移到子查询
SELECT * FROM t_order WHERE id >= (SELECT id FROM t_order limit 1000000, 1) LIMIT 10;
(3)INNER JOIN 延迟关联
延迟关联的优化思路,跟子查询的优化思路其实是一样的:都是把条件转移到主键索引树,然后减少回表。
SELECT t1.* FROM t_order t1
INNER JOIN (SELECT id FROM t_order limit 1000000, 1) t2
ON t1.id >= t2.id
LIMIT 10;
(4)覆盖索引
索引中已经包含了所有需要获取的字段的查询方式称为覆盖索引。
覆盖索引的好处:
1、避免 InnoDB 表进行索引的二次查询,也就是回表操作
InnoDB 是以聚集索引的顺序来存储的,对于 InnoDB 来说,二级索引在叶子节点中所保存的是行的主键信息,如果是用二级索引查询数据的话,在查找到相应的键值后,还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中,二级索引的键值中可以获取所有的数据,避免了对主键的二次查询 ,减少了 IO 操作,提升了查询效
2、可以把随机 IO 变成顺序 IO 加快查询效率
由于覆盖索引是按键值的顺序存储的,对于 IO 密集型的范围查找来说,对比随机从磁盘读取每一行的数据 IO 要少的多,因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。
不过,当查询的结果集占表的总行数的很大一部分时,可能就不会走索引了,自动转换为全表扫描。当然了,也可以通过 FORCE INDEX
来强制查询优化器走索引,但这种提升效果一般不明显
(5)上es
1、from,size浅分页
"浅"分页可以理解为简单意义上的分页。它的原理很简单,就是查询前20条数据,然后截断前10条,只返回10-20的数据。这样其实白白浪费了前10条的查询,其中,from定义了目标数据的偏移值,size定义当前返回的数目
原理:
因为es是基于分片的,假设有5个分片,from=100,size=10。则会根据排序规则从5个分片中各取回100条数据数据,然后汇总成500条数据后选择最后面的10条数据。
2、scroll 深分页
from+size查询在10000-50000条数据(1000到5000页)以内的时候还是可以的,但是如果数据过多的话,就会出现深分页问题。
为了解决上面的问题,elasticsearch提出了一个scroll滚动的方式。
scroll 类似于sql中的cursor,使用scroll,每次只能获取一页的内容,然后会返回一个scroll_id。根据返回的这个scroll_id可以不断地获取下一页的内容,所以scroll并不适用于有跳页的情景。
注意:请求的接口不再使用索引名了,而是 _search/scroll,其中GET和POST方法都可以使用。
scroll删除:
根据官方文档的说法,scroll的搜索上下文会在scroll的保留时间截止后自动清除,但是我们知道scroll是非常消耗资源的,所以一个建议就是当不需要了scroll数据的时候,尽可能快的把scroll_id显式删除掉
3、search_after 深分页
scroll 的方式,官方的建议不用于实时的请求(一般用于数据导出),因为每一个 scroll_id 不仅会占用大量的资源,而且会生成历史快照,对于数据的变更不会反映到快照上。
search_after 分页的方式是根据上一页的最后一条数据来确定下一页的位置,同时在分页请求的过程中,如果有索引数据的增删改查,这些变更也会实时的反映到游标上。但是需要注意,因为每一页的数据依赖于上一页最后一条数据,所以无法跳页请求。
为了找到每一页最后一条数据,每个文档必须有一个全局唯一值,官方推荐使用 _uid 作为全局唯一值,其实使用业务层的 id 也可以。