insert on conflict - 合并写 （消除不必要更新）

标签

PostgreSQL , 合并写 , insert on conflict , 不必要更新

背景

合并更新的应用非常广泛，存在则更新，不存在则写入。

但是在某些场景中，存在并不一定要更新，原因是新的内容可能和老的内容完全一致。这种更新操作是完全没有必要的。

因为PG是多版本的，更新会产生新的TUPLE版本，如果这种没必要的更新很多，只会给数据库带来额外的负担同时影响性能。特别体现在批量操作中。

例如电商场景中，运营人员会将需要操作的一批店铺导入到操作表，在操作表中再进行数据的操作。导入动作就是一个合并写的动作，每一批导入的店铺可能在操作表中已存在并且记录完全没有变化，如果不注意INSERT INTO ON CONFLICT语法的使用，会导致写入放大。

例子

1、新建测试表

create table tbl(  
  c1 int,   
  c2 int,   
  c3 int,   
  c4 int,   
  c5 timestamp,   
  unique (c1,c2)  
);  

2、使用普通的insert into on conflict合并写入，存在写入放大

insert into tbl   
  select id,id,1,random(),now() from generate_series(1,1000000) t(id)   
  on conflict(c1,c2)   
  do update   
  set   
  c3=excluded.c3,c4=excluded.c4,c5=excluded.c5;  

每一次操作都会更新所有记录

INSERT 0 1000000  
  
INSERT 0 1000000  
  
INSERT 0 1000000  

3、优化方法，加入更新条件，避免未变化的记录被更新

例如当c3,c4没有变化时，不更新。

  where  
  tbl.c3 is distinct from excluded.c3 or  
  tbl.c4 is distinct from excluded.c4;  

SQL如下

insert into tbl   
  select id,id,1,random(),now() from generate_series(1,1000000) t(id)   
  on conflict(c1,c2)   
  do update   
  set   
  c3=excluded.c3,c4=excluded.c4,c5=excluded.c5  
  where  
  tbl.c3 is distinct from excluded.c3 or  
  tbl.c4 is distinct from excluded.c4;  

此时每次更新的就是那些真正发生了变化的记录

INSERT 0 500172  
  
INSERT 0 500383  
  
INSERT 0 500664  

参考

https://www.postgresql.org/docs/10/static/sql-insert.html

[ WITH [ RECURSIVE ] with_query [, ...] ]  
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]  
    [ OVERRIDING { SYSTEM | USER} VALUE ]  
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }  
    [ ON CONFLICT [ conflict_target ] conflict_action ]  
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]  
  
where conflict_target can be one of:  
  
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]  
    ON CONSTRAINT constraint_name  
  
and conflict_action is one of:  
  
    DO NOTHING  
    DO UPDATE SET { column_name = { expression | DEFAULT } |  
                    ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |  
                    ( column_name [, ...] ) = ( sub-SELECT )  
                  } [, ...]  
              [ WHERE condition ]  

is distinct from是一种不等于的用法，其中包括NULL值的处理(认为null is not distinct from null is TRUE)。两个NULL值返回FALSE，一个NULL值返回TRUE。

null is distinct from null 返回false

null is distinct from nonnull 返回true

nonnull is distinct from nonnull 根据实际的VALUE来判断是否相等，相等返回false，不相等返回true。

当要判断NULL时，这个比=操作符好用，使用=等操作符时，通常底层函数是strict的，所以当操作数包含NULL值时，操作符的结果也返回NULL而不是我们想要的true或false。