最佳实践—如何优化数据全量抽取-阿里云开发者社区

开发者社区> -技术小能手-> 正文

最佳实践—如何优化数据全量抽取

简介: 本文介绍了在应用内通过代码高效抽取数据的方法。
+关注继续查看

简介

数据抽取是指通过代码或者数据导出工具,从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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Cassandra 最佳实践系列(1) - CQL QuickStart
Cassandra最佳实践之简单搭建以及使用cql
1453 0
MySQL两千万数据优化&迁移
最近有一张2000W条记录的数据表需要优化和迁移。2000W数据对于MySQL来说很尴尬,因为合理的创建索引速度还是挺快的,再怎么优化速度也得不到多大提升。不过这些数据有大量的冗余字段和错误信息,极不方便做统计和分析。
5630 0
面向海量数据的极致成本优化-云HBase的一体化冷热分离
随着业务的持续发展,业务数据库存储量会持续增长。通常数据量过亿时,就需要考虑选择扩展能力更好的NOSQL数据库如HBase,足够满足大多数业务的存储需求。然而,对于大量存储瓶颈类业务,存储成本依然是系统设计中需要关注的重中之重,本文介绍了一种全新的冷热分离一体化方案,0改造成本实现业务冷热分离
3417 0
MySQL海量数据处理及优化
什么是mysql MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品。
1118 0
Cassandra 最佳实践系列(1) - CQL 基本数据类型
Cassandra cql基本数据类型介绍
1184 0
1280
文章
0
问答
来源圈子
更多
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载