Paging Cache(深分页性能优化)

更新时间:

云原生数据仓库 AnalyticDB MySQL 版Paging Cache功能,通过缓存机制提高了使用LIMITOFFSETORDER BY的大数据量分页查询效率,可以解决深分页查询的性能问题和资源瓶颈。本文介绍了如何在分页查询中使用Paging Cache功能。

前提条件

集群的内核版本为3.2.3及以上版本。

说明
  • 查看企业版基础版湖仓版集群的内核版本,请执行SELECT adb_version();。如需升级内核版本,请联系技术支持。

  • 查看和升级数仓版集群的内核版本,请参见查看和升级版本

什么是Paging Cache

“深分页”为什么会有性能问题

在电商平台上,用户希望根据销量或综合评分对商品排序,以便优先查看优质商品。为了提高页面响应速度,避免一次性加载大量数据导致的性能问题,应用程序通常采用分页方式展示结果。

在数据库中,一种常见的方法是按照特定列排序后,通过LIMITOFFSET来实现分页查询。其中,LIMIT指定每页显示的数据条数,OFFSET指定了每页数据的起始偏移。假设每页展示100条记录,则查询第一页的数据的SQL语句为:SELECT * FROM t_order ORDER BY id LIMIT 0, 100。对于更深的页面,比如第10001页(即跳过前1000000条记录),SQL语句如下:SELECT * FROM t_order ORDER BY id LIMIT 1000000, 100

当大数据量分页查询中,请求的页面较深时,全局排序开销和频繁的回表开销会导致数据库性能急剧下降。尤其在分布式数据仓库AnalyticDB for MySQL中,这个问题更为复杂。为了减少不同节点间Shuffle(数据重新分配)的数据量,往往每个存储节点(Worker)会先在本地做一次TopN计算,再由单存储节点对每个Worker的执行结果聚合,排序后返回最终的结果集。

image

为了确保最终结果的准确性,需要将改写后的SQL语句分配给每个Worker执行。例如,查询第10001页,每个Worker收到的查询为:SELECT * FROM t_order ORDER BY id LIMIT 0, 1000100。原本只要100条数据,最后却要由单节点对1000100×Worker条数据全局排序。

排序的数据量随分页深度线性增长,性能也会急剧下降。这对内存和CPU资源都是极大的负担,并且增加了发生内存溢出的风险,便产生了“深分页”问题。

Paging Cache,即深分页性能优化,是AnalyticDB for MySQL为了解决深分页的性能问题推出的一种缓存策略。首次发起某个分页查询请求时,会从数据库查询数据并将查询结果存储在临时表中,后续相同Pattern的分页查询可以直接读取缓存表,避免了重复执行排序操作。这不仅有效解决了深分页查询的性能问题,还避免了因ORDER BY引发的内存溢出问题。此外,AnalyticDB for MySQL还会根据淘汰策略自动清理不再被使用的 Paging Cache,确保资源得到合理利用。

适用于以下场景。

  • 大数据量的数据导出

    在做大量数据导出时,一次性读取过多数据容易造成客户端不稳定,往往会通过分页查询的方式分批拉取结果。由于在分布式环境下,直接使用LIMITOFFSET的方式并不会保证数据以确定的顺序被处理,这可能导致同一页的数据在每次查询时返回不同的结果。所以,需要通过显式增加 ORDER BY保证数据导出时的不重不漏。使用Paging Cache可以去掉本身并不是业务必须的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,即可直接从缓存表中读取数据,无需再次访问数据库。

限制条件

去掉LIMITOFFSET子句后,分页查询的数据行数需少于1亿行。

说明

若查询的数据行数超过1亿行,请提交工单联系技术支持调整数据行数限制。

启用方法

通过以下任意一种Hint为查询启用Paging Cache功能。

  • paging_id=<paging_id>

    paging_id用来标识一组相关的分页查询(即一组Pattern相同,仅LIMITOFFSET参数不同的分页查询)。客户端需生成一个Unique ID来唯一标识一组分页查询的缓存。

    • 当查询的paging_id不存在时,会生成缓存。

    • 当查询的paging_id存在,且查询Patternpaging_id对应的查询Pattern匹配时,查询会命中缓存;

    • 当查询的paging_id存在,但查询Patternpaging_id对应的查询Pattern不匹配时,查询会报错。您可以通过查询已有的缓存确认paging_id是否已被使用。

    说明

    paging_id命名规则:以字母或下划线(_)开头,可包含字母、数字以及下划线(_),长度为1127个字符。不能包含引号、感叹号(!)和空格,不能是SQL保留字

    以下示例表示使用Paging Cache功能分页查询结果的IDpaging123

    /*paging_id=paging123*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;
  • paging_cache_enabled=true

    采用这种方式,不需要频繁修改Hint。服务端基于SQL语句的Pattern,在去除LIMITOFFSET子句后,自动生成一个paging_id来标识是否属于同一组分页查询。

    由于该方法依赖于Pattern匹配,其灵活性会受到一定限制。如果相同Pattern(排除LIMITOFFSET子句)的查询缓存不存在,则会生成缓存。如果相同Pattern(排除LIMITOFFSET子句)的查询已生成缓存,则会直接查询缓存。

    示例如下:

    /*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;

测试结果:

单并发执行,整个导出过程,普通分页查询平均RT54391ms。开启Paging Cache深分页性能优化后,平均RT525ms。性能提升约103,同时CPU和内存使用率大幅降低。

该结果说明使用Paging Cache深分页性能优化,不仅极大地缩短了数据导出过程中的平均响应时间,而且有效地降低了系统的CPU和内存负担。

image.png