GaussDB(DWS)中UPSERT特性用法总结

华为云博客 技术博客 21610 5

GaussDB(DWS)中UPSERT特性用法总结

 1 简介

  在关系型数据库中，upsert是一个组合词，即当往表中插入记录时，如果记录已经存在则更新或忽略，否则插入新纪录，更新的策略需要按照更新指定的表达式计算结果，而为了使用该特性需要使用相应语法。本文主要介绍了GaussDB(DWS)中UPSERT的用法、语法约束和行列存的区别等。 

2 语法介绍

  GaussDB(DWS)的Upsert特性支持PG和MySQL两种语法，具体语法差异如下图所示。

数据库

MySQL

PostgreSQL

Gauss(DWS)

冲突忽略语法

INSERT IGNORE INTO

INSERT INTO ON CONFLICT DO NOTHING

两种都支持

冲突更新语法

INSERT INTO ON DUPLICATE KEY UPDATE

INSERT INTO ON CONFLICT DO UPDATE SET

两种都支持

  注：GaussDB(DWS)只支持唯一或者主键约束冲突忽略。

2.1 MySql语法

  冲突忽略语法：INSERT IGNORE INTO。

  冲突更新语法：INSERT INTO ON DUPLICATE KEY UPDATE。 

INSERT IGNORE INTO t1 values(1,1,1);

INSERT INTO t1 values(1,1,1) ON DUPLICATE KEY UPDATE c =values(c) + t1.c;

 

  • UPDATE字句中的values，表示因冲突而本该排除的数据行中某一列的值，里面只能是列名、复合类型的某一列。
  • UPDATE的表达式目前只支持+-*/%双目运算符、+-单目运算符、TYPECAST、比较操作符。values(c) 外层不能添加函数。如果需要，使用c形式。（见2.2 PG语法）。

2.2 PG语法

  冲突忽略语法：INSERT INTO ON CONFLICT DO NOTHING。

  冲突更新语法：INSERT INTO ON CONFLICT DO UPDATE SET。

INSERT INTO t1 values(1,1,1) ON CONFLICT(a) DO NOTHING; 

INSERT INTO t1 values(1,1,1) ON CONFLICT(a) DO UPDATE SET c = excluded.c; 


  • UPDATE 子句中表达式支持：
  1. DEFAULT，表是该列的DEFAULT值，如果没有指定则为NULL。
  2. 常量或带有表达式的常量，如100，upper(‘a’)。
  3. 带有表达式的表目标列，如upper(colName), upper(table_name. colName)。
  4. 以上两种构成的表达式语句。
  • EXCLUDED与MySQL语法中values功能相同，不同的是EXCLUDED支持表达式。支持EXCLUDED引用列或带有表达式的EXCLUDED引用列，如 colName, upper(EXCLUDED. colName)。EXCLUDED. colName表示该行对应的column_name列的值。
  • CONFLICT[colName] 可以指定含有唯一约束的列名,因此包含colName的唯一索引（不管顺序）都会被推断为arbiter index，并用于冲突更新。如果推断不成功，则报错。
  • 也通过CONFLICT ON CONSTRAINT unique_index_name 指定含有唯一约束的索引名,如索引不含有唯一约束或不是插入table的索引则报错。

INSERT INTO t1 values(1,1,1) ON CONFLICT ON CONSTRAINT a_unique_index DO UPDATE SET c = excluded.c; 
  • 执行策略冲突或更行。当做DO NOTHING冲突忽略时，CONFLICT后可以不指定唯一索引，如果有多个约束，当其中一个约束冲突，插入就会忽略。当做DO UPDATE时，CONFLICT后必须指定唯一索引。

3 行列存upsert区别

  在GaussDB中行列存的UPSERT的实现方式是不一样，行存是先检测冲突再插入，列存是先插入再检测冲突。因此理论上来说，在相同场景下，若插入的数据冲突率较高，行存的UPSERT性能优于列存，若冲突率较低，则列存的UPSERT性能更好（注意：优于列存结构的特殊性，当在列存表上执行UPSERT时，建议开启DELTA表，若DELTA表关闭会导致并发性能较差和空间膨胀的问题）。

4 已知死锁场景和解决办法

  若业务中有大并发和高冲突率的upsert业务场景时，有可能出现分布式死锁，具体原理如下图：

  当T1向DN1中插入了数据1，T2向DN2中插入了数据2时，若T1向DN2中插入数据2，需要等T1事务结束，此时当T2向DN1插入数据1时，要等T1事务结束，因此发生分布式死锁。

  该类死锁场景，在PG上也同样存在，属于已知场景，若发生在单节点上会被检测到并解锁，但由于目前GaussDB没有分布式死锁自动检测并解锁的工具，因此会出现分布式死锁并影响业务性能。若存在该业务场景且发现插入性能明显变差时，可通过pgxc_deadlock视图检测是否出现分布式死锁，如下图：


  若发现产生分布式死锁，可采取以下方法：

(1)业务端避免大量重复数据的插入，比如去重

(2)减小锁超时的时间。


（完）