Paging Cache(深分页性能优化)
云原生数据仓库 AnalyticDB MySQL 版Paging Cache功能,通过缓存机制提高了使用LIMIT
、OFFSET
和ORDER BY
的大数据量分页查询效率,可以解决深分页查询的性能问题和资源瓶颈。本文介绍了如何在分页查询中使用Paging Cache功能。
前提条件
集群的内核版本为3.2.3及以上版本。
查看企业版、基础版或湖仓版集群的内核版本,请执行
SELECT adb_version();
。如需升级内核版本,请联系技术支持。查看和升级数仓版集群的内核版本,请参见查看和升级版本。
什么是Paging Cache
Paging Cache,即深分页性能优化,是AnalyticDB for MySQL为了解决深分页的性能问题推出的一种缓存策略。首次发起某个分页查询请求时,会从数据库查询数据并将查询结果存储在临时表中,后续相同Pattern的分页查询可以直接读取缓存表,避免了重复执行排序操作。这不仅有效解决了深分页查询的性能问题,还避免了因ORDER BY
引发的内存溢出问题。此外,AnalyticDB for MySQL还会根据淘汰策略自动清理不再被使用的 Paging Cache,确保资源得到合理利用。
适用于以下场景。
使用方法
配置缓存数据库
使用Paging Cache功能缓存查询结果前,建议先指定一个数据库来存储分页查询的临时缓存表。若未指定数据库,则会在当前连接的内部数据库下存储临时缓存表。临时缓存表会在Paging Cache创建时自动生成。
缓存数据库不能设置为外部数据库(External Database)。
以下示例指定数据库paging_cache作为缓存数据库。您也可以指定其他任意数据库。
SET ADB_CONFIG PAGING_CACHE_SCHEMA=paging_cache;
在分页查询中启用Paging Cache功能
当多个分页查询具有相同的查询模式(Pattern)时,可以通过在查询语句前添加Hint来利用Paging Cache功能以提高性能。首次发起某个分页查询请求时,添加Hint,会生成一个临时缓存表来存储分页查询结果。后续相同Pattern的分页查询,只需在查询语句前添加相同的Hint,即可直接从缓存表中读取数据,无需再次访问数据库。
限制条件
去掉LIMIT
和OFFSET
子句后,分页查询的数据行数需少于1亿行。
若查询的数据行数超过1亿行,请提交工单联系技术支持调整数据行数限制。
启用方法
通过以下任意一种Hint为查询启用Paging Cache功能。
paging_id=<paging_id>
paging_id
用来标识一组相关的分页查询(即一组Pattern相同,仅LIMIT
和OFFSET
参数不同的分页查询)。客户端需生成一个Unique ID来唯一标识一组分页查询的缓存。当查询的
paging_id
不存在时,会生成缓存。当查询的
paging_id
存在,且查询Pattern与paging_id
对应的查询Pattern匹配时,查询会命中缓存;当查询的
paging_id
存在,但查询Pattern与paging_id
对应的查询Pattern不匹配时,查询会报错。您可以通过查询已有的缓存确认paging_id
是否已被使用。
说明paging_id
命名规则:以字母或下划线(_)开头,可包含字母、数字以及下划线(_),长度为1到127个字符。不能包含引号、感叹号(!)和空格,不能是SQL保留字。以下示例表示使用Paging Cache功能分页查询结果的ID是
paging123
。/*paging_id=paging123*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;
paging_cache_enabled=true
采用这种方式,不需要频繁修改Hint。服务端基于SQL语句的Pattern,在去除
LIMIT
和OFFSET
子句后,自动生成一个paging_id
来标识是否属于同一组分页查询。由于该方法依赖于Pattern匹配,其灵活性会受到一定限制。如果相同Pattern(排除LIMIT和OFFSET子句)的查询缓存不存在,则会生成缓存。如果相同Pattern(排除LIMIT和OFFSET子句)的查询已生成缓存,则会直接查询缓存。
示例如下:
/*paging_cache_enabled=true*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;
如果遇到缓存生成失败,则必须先清理分页查询缓存,再重新发起分页查询请求以生成新的缓存数据。
查询已有的缓存
获取当前集群中所有的分页缓存信息,包括但不限于paging_id、缓存大小、缓存状态等。
SELECT * FROM INFORMATION_SCHEMA.KEPLER_PAGING_CACHE_STATUS_MERGED;
设置缓存最大个数
缓存的最大个数,默认值为32。
SET ADB_CONFIG PAGING_CACHE_MAX_TABLE_COUNT=32;
总个数超限后,再创建缓存系统会报错。报错信息如下:
Paging cache count exceeds the limit. Please clean up unused caches or increase the related parameter using SET ADB_CONFIG PAGING_CACHE_MAX_TABLE_COUNT=xxx.
请根据报错提示信息清理不再使用的缓存或调整缓存的最大数量。
设置缓存有效时间
您可以设置缓存有效时间。超过有效时间后,缓存失效,后续同一Pattern的查询会重新访问数据库查询数据,并更新缓存表。缓存有效时间的单位为秒(s),通常应用在报表并发控制场景中。
以paging_cache_validity_interval=300
为例,标识了缓存有效时间为300秒,即缓存在生成300秒之后失效。示例如下:
/*paging_cache_enabled=true, paging_cache_validity_interval=300*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;
清理分页查询缓存
在使用Paging Cache缓存分页查询结果时,缓存数据会保存在AnalyticDB for MySQL热存储空间中。如果不再使用缓存,可以清理缓存以节省存储空间。
手动清理
指定Pattern的分页查询缓存
设置
paging_cache_enabled=true,invalidate_paging_cache=true
,清理SQL Pattern对应分页查询的缓存结果。示例如下:
/*paging_cache_enabled=true,invalidate_paging_cache=true*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;
指定
paging_id
的分页查询缓存指定
paging_id
,删除该paging_id
标识的分页查询缓存。示例如下:
CLEAN_PAGING_CACHE paging123;
说明paging_id
可以通过查询已有的缓存获取。
自动清理
指定缓存的过期时间阈值,删除指定时间内未访问的分页查询缓存。过期时间阈值的默认值为3600,单位为秒(s),即1小时未被访问则会被自动清除。
SET ADB_CONFIG PAGING_CACHE_EXPIRATION_TIME=3600;
常见报错
Paging cache prepare failed, and cache is not available
完整报错示例如下:
Paging cache prepare failed, and cache is not available. Please use /*paging_cache_enabled=true,invalidate_paging_cache=true*/ to clean the unavailable cache or set a specific pagingId with /*paging_id=xxx*/ to gen a new cache. Note that the old and new cache data may be inconsistent.
报错原因:使用Paging Cache功能查询过程中,可能会遇到一些异常情况,例如节点重启、节点扩缩容等。如果分页查询命中了生成失败的缓存,服务端默认不会重新查询数据库,也不会自动重新生成新的缓存,而是会抛出异常。
解决方法:如果遇到此异常,重新生成缓存后并不保证数据一致性。数据导出场景下,建议清理已经导出的数据和不可用的缓存后,重新生成缓存。其他场景下,建议清理不可用的缓存或指定新的paging_id
,重新生成缓存。
性能对比
基于TPC-H 100G的数据集,评估数据导出场景中Paging Cache对分页查询的优化效果。
本次测试导出数据100万条,每页10万条数据,第一页的分页SQL如下:
-- 普通分页查询,不使用Paging Cache功能
SELECT * FROM lineitem ORDER BY l_orderkey,l_linenumber LIMIT 0,100000;
-- 分页查询,使用了Paging Cache功能(本例是在数据导出场景,去掉了本身并不是业务必须的ORDER BY)
/*paging_cache_enabled=true*/ SELECT * FROM lineitem LIMIT 0,100000;
测试结果:
单并发执行,整个导出过程,普通分页查询平均RT为54391ms。开启Paging Cache深分页性能优化后,平均RT在525ms。性能提升约103倍,同时CPU和内存使用率大幅降低。
该结果说明使用Paging Cache深分页性能优化,不仅极大地缩短了数据导出过程中的平均响应时间,而且有效地降低了系统的CPU和内存负担。