如何优化数据全量抽取

本文介绍了在应用内通过代码高效抽取数据的方法。

简介

数据抽取是指通过代码或者数据导出工具,从PolarDB-X中批量读取数据的操作。主要包括以下场景:

  • 通过数据导出工具将数据全量抽取到下游系统。PolarDB-X支持多种数据导出工具,更多内容请参考数据导入导出

  • 在应用内处理数据或者批量的将查询结果返回给用户浏览时,不能依赖外部工具,必须在应用内通过代码完成数据全量抽取。

本文主要介绍在应用内通过代码高效抽取数据的方法,根据是否一次性读取全量数据,分为全量抽取和分页查询。

全量抽取场景

全量抽取使用的SQL通常不包含表的拆分键,以全表扫描的方式执行,随着读取数据量的增加,数据抽取操作的执行时间线性增长。为了避免占用过多网络/连接资源,可以使用HINT直接下发查询语句,从物理分片中拉取数据。以下示例采用JAVA代码编写,完整使用方法参考 NODE HINT

public static void extractData(Connection connection, String logicalTableName, Consumer<ResultSet> consumer)
    throws SQLException {
    final String topology = "show topology from {0}";
    final String query = "/*+TDDL:NODE({0})*/select * from {1}";
    try (final Statement statement = connection.createStatement()) {
        final Map<String, List<String>> partitionTableMap = new LinkedHashMap<>();
        // Get partition id and physical table name of given logical table
        try (final ResultSet rs = statement.executeQuery(MessageFormat.format(topology, logicalTableName))) {
            while (rs.next()) {
                partitionTableMap.computeIfAbsent(rs.getString(2), (k) -> new ArrayList<>()).add(rs.getString(3));
            }
        }
        // Serially extract data from each partition
        for (Map.Entry<String, List<String>> entry : partitionTableMap.entrySet()) {
            for (String tableName : entry.getValue()) {
                try (final ResultSet rs = statement
                    .executeQuery(MessageFormat.format(query, entry.getKey(), tableName))) {
                    // Consume data
                    consumer.accept(rs);
                }
            }
        }
    }
}

分页查询场景

向用户展示列表信息时,需要分页来提高页面的加载效率,避免返回过多冗余信息,用于处理分页显示需求的查询,称为分页查询。关系型数据库没有直接提供分段返回表中数据的能力,高效的实现分页查询,还需要结合数据库本身的特点来设计查询语句。

以MySQL为例,分页查询最直观的实现方法,是使用limit offset,pageSize来实现,例如如下查询:

select * from t_order where user_id = xxx order by gmt_create, id limit offset, pageSize

因为gmt_create可能重复,所以order by时应加上id,保证结果顺序的确定性。

说明 该方案在表规模较小的时候,能够正常运行。当t_order表增长到十万级,随着页数增加,执行速度明显变慢,可能降到几十毫秒的量级,如果数据量增长到百万级,则耗时达到秒级,数据量继续增长,耗时最终会变得不可接受。

问题分析

假设我们在user_id, gmt_create上创建了局部索引,由于只有user_id上的条件,每次需要扫描的总数据量为offset + pageSize ,随着offset的增大逐渐接近全表扫描,导致耗时增加。并且在分布式数据库中,全表排序的吞吐无法通过增加DN数量来提高。

改进方案1

每次获取下一页记录时,指定从上次结束的位置继续往后取,这样不需要设置offset ,能够避免出现全表扫描的情况。看一个按id进行分页查询的例子:

select * from t_order where id > lastMaxId order by id limit pageSize

第一次查询不指定条件,后续查询则传入前一次查询的最大id,在执行时,数据库首先在索引上定位到lastMaxId的位置,然后连续返回pageSize条记录即可,非常高效。

说明 当id为主键或者唯一键时,改进方案1可以达到分页查询的效果,也有不错的性能。但缺点也比较明显,当id上有重复值时,可能会漏掉部分记录。

改进方案2

MySQL支持通过 Row Constructor Expression 实现多列比较的语义(PolarDB-X同样支持)。

(c2,c3) > (1,1) 
等价于 
c2 > 1 OR ((c2 = 1) AND (c3 > 1))

因此,可以用下面的方法实现分页查询语义:

select * from t_order 
where user_id = xxx and (gmt_create, id) > (lastMaxGmtCreate, lastMaxId)
order by user_id, gmt_create, id limit pageSize

第一次查询不指定条件,后续查询则传入前一次查询的最大gmt_create和id,通过Row Constructor Expression正确处理gmt_create存在重复的情况。

说明 示例中,为了提高查询性能,我们在user_id和gmt_create上建立联合索引,并在order by中加入user_id提示优化器可以通过索引来消除排序。由于Row Constructor Expression包含null值会导致表达式求值结果为null,当存在null值时需要使用OR表达式。PolarDB-X目前只在Row Constructor Expression仅包含拆分键时才将其用于分区裁剪,其他场景同样需要使用OR表达式。

结合上述分析,给出一个PolarDB-X上分页查询的最佳实践:

-- lastMaxGmtCreate is not null 
select * from t_order 
where user_id = xxx 
and (
      (gmt_create > lastMaxGmtCreate) 
      or ((gmt_create = lastMaxGmtCreate) and (id > lastMaxId))
    )
order by user_id, gmt_create, id limit pageSize
-- lastMaxGmtCreate is null
select * from t_order 
where user_id = xxx 
and (
      (gmt_create is not null)
      or (gmt_create is null and id > lastMaxId)
    )
order by user_id, gmt_create, id limit pageSize

results matching ""

    No results matching ""