HybridDB · 最佳实践 · HybridDB 数据合并的方法与原理

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 引言刚开始使用HybridDB的用户,有个问的比较多的问题:如何快速做数据“合并”(Merge)?所谓“合并”,就是把数据新版本更新到HybridDB中。如果数据已经存在,则将它们替换为新版本;如果不存在,将它们插入数据库中。一般是离线的做这种数据合并,例如每天一次批量把数据更新到HybridDB中。也有客户需要实时的更新,即做到分钟级甚至秒级延迟。这里我们介绍一下HybridDB中数据合并的

引言

刚开始使用HybridDB的用户,有个问的比较多的问题:如何快速做数据“合并”(Merge)?所谓“合并”,就是把数据新版本更新到HybridDB中。如果数据已经存在,则将它们替换为新版本;如果不存在,将它们插入数据库中。一般是离线的做这种数据合并,例如每天一次批量把数据更新到HybridDB中。也有客户需要实时的更新,即做到分钟级甚至秒级延迟。这里我们介绍一下HybridDB中数据合并的方法和背后原理。

简单更新过程

无论怎么做数据合并,都是对数据的修改,即Update、Delete、Insert、Copy等操作。我们先要了解一下HybridDB中的数据更新过程。我们以用户发起一次Update操作为例(对列存表单行记录的更新),整个流程如下图所示。

pic

其中的步骤说明如下:

  1. 用户把Update的SQL请求发送到主节点;

  2. 主节点发起分布式事务,并对被Update的表加锁(HybridDB不允许并行的Update同一张表),然后把更新请求分发到对应的子节点。

  3. 子节点通过索引扫描,定位到要更新的数据,并更新数据。对于列存表,更新逻辑其实就是删除旧的数据行,并在表的尾端写入新的数据行。(列存表)被更新的数据页面会写入内存缓存区,对应的表文件长度的变化(因为尾端写入了数据,所以数据表对应的文件长度增大了)会写入日志(xlog文件)。

  4. 在Update命令结束前,内存中的被更新的数据页面和xlog日志,都要同步到Mirror节点。同步完成后,主节点结束分布式事务,返回用户执行成功的消息。

可以看出,整个过程的链条很长,SQL语句解析、分布式事务、锁,主节点子节点之间的连接建立、子节点与Mirror数据和日志同步等操作,都会耗费CPU或IO资源,同时拖慢整个请求的响应时间。因此,对于HybridDB来说,应该尽量避免单行数据的更新,而是尽量批量的更新数据,也就是尽量做到:

  • 尽量把更新放到一个SQL语句,减少语句解析、节点通信、数据同步等开销;

  • 尽量把更新放到一个事务,避免不必要的事务开销。

简而言之,就是数据的合并和更新,尽量以”成批“的形式进行。下面我们看看,如何批量的做数据更新。

批量Update

假如我们要Update很多独立数据行,怎么才能用一个SQL来实现呢?

我们假设有张表target_table需要做更新(称为目标表),这张表的定义如下。一般目标表都非常大,这里我们往target_table里面插入1千万数据。为了能快速更新,target_table上要有索引。这里我们定义了primary key,会隐含的创建一个唯一值索引(unique index)。

create table target_table(c1 int, c2 int, primary key (c1));

insert into target_table select generate_series(1, 10000000);

为了做批量的Update,需要用到中间表(Stage Table),其实就是为了更新数据临时创建的表。为了更新target_table的数据,可以先把新数据插入到中间表source_table中。然后,把新数据通过COPY命令OSS外部表等方式导入到source_table。这里为简单起见,我们直接插入一些数据。

create table source_table(c1 int, c2 int);

insert into source_table select generate_series(1, 100), generate_series(1,100);

source_table数据准备好后,执行下面的update set … from … where ..语句,即可实现批量的Update。注意,为了最大限度的使用到索引,在执行Update前,要使用set opitimzer=on启用ORCA优化器(如果不启用ORCA优化器,则需要执行set enable_nestloop = on才能使用到索引)。


set optimizer=on;

update target_table set c2 = source_table.c2 from source_table where target_table.c1= source_table.c1;

这种Update的执行计划如下:

=> explain update target_table set c2 = source_table.c2 from source_table where target_table.c1= source_table.c1;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Update  (cost=0.00..586.10 rows=25 width=1)
   ->  Result  (cost=0.00..581.02 rows=50 width=26)
         ->  Redistribute Motion 4:4  (slice1; segments: 4)  (cost=0.00..581.02 rows=50 width=22)
               Hash Key: public.target_table.c1
               ->  Assert  (cost=0.00..581.01 rows=50 width=22)
                     Assert Cond: NOT public.target_table.c1 IS NULL
                     ->  Split  (cost=0.00..581.01 rows=50 width=22)
                           ->  Nested Loop  (cost=0.00..581.01 rows=25 width=18)
                                 Join Filter: true
                                 ->  Table Scan on source_table  (cost=0.00..431.00 rows=25 width=8)
                                 ->  Index Scan using target_table_pkey on target_table  (cost=0.00..150.01 rows=1 width=14)
                                       Index Cond: public.target_table.c1 = source_table.c1

可以看到,HybridDB“聪明”的选择了索引。但是,如果往source_table里面加入更多数据,优化器会认为使用Nest Loop关联方法+索引扫描,不如不使用索引高效,而是会选取Hash关联方法+表扫描方式执行。例如:

postgres=> insert into source_table select generate_series(1, 1000), generate_series(1,1000);
INSERT 0 1000
postgres=> analyze source_table;
ANALYZE
postgres=> explain update target_table set c2 = source_table.c2 from source_table where target_table.c1= source_table.c1;
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Update  (cost=0.00..1485.82 rows=275 width=1)
   ->  Result  (cost=0.00..1429.96 rows=550 width=26)
         ->  Assert  (cost=0.00..1429.94 rows=550 width=22)
               Assert Cond: NOT public.target_table.c1 IS NULL
               ->  Split  (cost=0.00..1429.93 rows=550 width=22)
                     ->  Hash Join  (cost=0.00..1429.92 rows=275 width=18)
                           Hash Cond: public.target_table.c1 = source_table.c1
                           ->  Table Scan on target_table  (cost=0.00..477.76 rows=2500659 width=14)
                           ->  Hash  (cost=431.01..431.01 rows=275 width=8)
                                 ->  Table Scan on source_table  (cost=0.00..431.01 rows=275 width=8)

上述批量的Update方式,减少了SQL编译、节点间通信、事务等开销,可以大大提升数据更新性能并减少对资源的消耗。

批量Delete

对于Delete操作,采用和上述批量Update类似的中间表,然后使用下面的带有“Using”子句的Delete来实现批量删除:

delete from target_table using source_table where target_table.c1 = source_table.c1;

可以看到,这种批量的Delete同样使用了索引。

explain delete from target_table using source_table where target_table.c1 = source_table.c1;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Delete (slice0; segments: 4)  (rows=50 width=10)
   ->  Nested Loop  (cost=0.00..41124.40 rows=50 width=10)
         ->  Seq Scan on source_table  (cost=0.00..6.00 rows=50 width=4)
         ->  Index Scan using target_table_pkey on target_table  (cost=0.00..205.58 rows=1 width=14)
               Index Cond: target_table.c1 = source_table.c1

利用Delete + Insert做数据合并

回到本文刚开始的问题,如何实现批量的数据合并?做数据合并时,我们先把待合入的数据放入中间表中。如果我们预先知道待合入的数据,在目标表中都已经有对应的数据行,即我们通过Update语句即可实现数据合入。但多数情况下,待合入的数据中,一部分是在目标表中已存在记录的数据,还有一部分是新增的,目标表中没有对应记录。这时候,使用一次批量的Delete + 一次批量的Insert即可:

set optimizer=on;

delete from target_table using source_table where target_table.c1 = source_table.c1;

insert into target_table select * from source_table;

利用Values()表达式做实时更新

使用中间表,需要维护中间表生命周期。有的用户想实时的批量更新数据到HybridDB,即持续性的同步数据或合并数据到HybridDB。如果采用上面的方法,需要反复的创建、删除(或Truncate)中间表。其实,可以利用Values表达式,达到类似中间表的效果,但不用维护表。方法是先将待更新的数据拼成一个Values表达式,然后按如下方式执行Update或Delete:

update target_table set c2 = t.c2 from (values(1,1),(2,2),(3,3),…(2000,2000)) as t(c1,c2) where target_table.c1=t.c1

delete from target_table using (values(1,1),(2,2),(3,3),…(2000,2000)) as t(c1,c2) where target_table.c1 = t.c1

注意,使用set optimizer=on;set enable_nestloop=on;都可以生成使用索引的查询计划。比较复杂的情形,比如索引字段有多个、涉及分区表等,必须要使用ORCA优化器才能匹配上索引。

总结

上面我们简单介绍了HybridDB的批量数据合并和更新的最佳实践。利用这些方法,无论是在每天一次或多次的ETL操作,还是实时更新数据的场景,都可以把HybridDB的数据更新效率充分发挥出来。

目录
相关文章
|
存储 负载均衡 NoSQL
MongoDB·最佳实践·count不准原因分析
背景 一般来说,除了由于secondary延迟可能造成查询secondary节点数据不准以外,关于count的准确性问题,在MongoDB4.0官方文档中有这么一段话On a sharded cluster, db.
|
存储 缓存 关系型数据库
PolarDB存储原理与实践
在阿里云开源的趋势下,PolarDB已经成功运用到很多企业案例中。
PolarDB存储原理与实践
|
SQL 存储 关系型数据库
PolarDB-X 1.0-最佳实践-如何高效扫描数据
PolarDB-X支持高效的数据扫描方式,并支持在全表扫描时使用聚合函数进行统计汇总。本文介绍如何高效扫描PolarDB-X数据。
322 0
|
存储 监控 关系型数据库
POLARDB · 最佳实践 · POLARDB不得不知道的秘密
前言 POLARDB作为阿里云下一代关系型云数据库,自去年9月份公测以来,收到了不少客户的重点关注,今年5月份商业化后,许多大客户开始陆续迁移业务到POLARDB上,但是由于POLARDB的很多默认行为与RDS MySQL兼容版不一样,导致很多用户有诸多使用上的困惑,本来总结了几点,给大家答疑解惑。
5139 0
|
NoSQL
MongoDB · 引擎特性 · 事务实现解析
MongoDB 4.0 引入的事务功能,支持多文档ACID特性,例如使用 mongo shell 进行事务操作 > s = db.getMongo().startSession() session { "id" : UUID("3bf55e90-5e88-44aa-a59e-a30f777f1d89") } > s.
1981 0
|
SQL 监控 Go
MSSQL · 应用案例 · 日志表设计优化与实现
摘要 这篇文章从日志表问题引入、日志表的共有特性、日志表的设计需求、设计思路以及设计详细实现的角度,阐述了在SQL Server数据库中如何最优化设计日志表来降低系统资源的占用和提高系统吞吐量。问题引入 在平时与客户服务与交流过程中,我们不止一次的被客人问及这样的场景:我们现在面临如何设计SQL Server日志表方案,如何最优化设计数据库日志记录表。
1472 0
|
SQL 监控 关系型数据库
动态输出(ToB海量日志转换业务) - 阿里云HybridDB for PostgreSQL最佳实践
标签 PostgreSQL , UDF , 动态格式 , format , JOIN , OSS外部表 背景 有一些业务需要将数据归类动态的输出,比如一些公共日志服务,所有用户的日志都被统一的按格式记录到一起,但是每个最终用户关心的字段都不一样,甚至每个用户对数据转换的需求都不一样。
2383 0
|
监控 数据库
HybridDB · 稳定性 · HybridDB如何优雅的处理Out Of Memery问题
前言 你是否遇到过数据库服务器的Out Of Memory(OOM)现象?就是数据库的进程把操作系统内存耗尽,触发操作系统对数据库进程执行Kill -9操作。操作系统对某个数据库进程的Kill,会导致整个数据库实例所有实例重启,所有连接会断开,造成一定时间的数据库不可用。OOM对数据库服务影响较大,应该尽量避免。 在我们的HybridDB for PG 云服务中,也可能遇到用户实例耗尽所有可用
2436 0