两张数据库表批量插入性能差异原因排查求助

两张表位于同一数据库、同一文件组，使用相同资源，通过Apache NIFI的相同参数化流以每批次1000条记录的方式插入数据，但性能差异显著：

• rdm表插入速率接近30000条/秒，最终存入140万条记录
• plb表插入速率不足1000条/秒，仅存入10.8万条记录，耗时却更长

已尝试禁用并删除plb表的自引用外键约束fk_plb_plb_cancel，但性能无任何改善。现需排查表定义中可能导致此性能差异的原因。

两张表的创建SQL如下：

rdm表定义 #

create table rdm
(
    id                integer                        not null,
    fk_plc            integer                        not null,
    booklet_sequence  smallint                       not null,
    payment_sequence  smallint                       not null,
    due_date          date                           not null,
    amount            bigint                         not null,
    save_amount       bigint                         not null,
    amount            bigint                         not null, -- 注意：此处存在重复字段定义，属于笔误
    fk_plc_delete     integer sparse,
    fk_lpy_delete     integer sparse,
    etl_date          datetime default sysdatetime() not null,
    constraint pk_rdm primary key (id),
    constraint uk_rdm_payment_sequence unique (fk_plc, payment_sequence)
);

alter table rdm add constraint fk_rdm_plc foreign key (fk_plc) references plc(id);
alter table rdm add constraint fk_rdm_plc_delete foreign key (fk_plc_delete) references plc(id);
create index ix_rdm_plc_delete on rdm (fk_plc_delete);

plb表定义 #

create table plb
(
    id             integer                        not null,
    operation_date date                           not null,
    amount         bigint                         not null,
    fk_pbt         integer                        not null,
    fk_plc         integer                        not null,
    fk_rdm         integer,
    fk_lpy         integer sparse,
    cover_end_date date,
    fk_plb_cancel  integer sparse,
    etl_date       datetime default sysdatetime() not null,
    constraint pk_plb primary key (id)
);

alter table plb  add constraint fk_plb_plc foreign key (fk_plc) references plc (id);
create index ix_plb_plc on plb (fk_plc);

alter table plb add constraint fk_plb_rdm foreign key (fk_rdm) references rdm (id);
create index ix_plc_rdm on plb (fk_rdm); -- 注意：索引命名应为ix_plb_rdm，属于拼写笔误

alter table plb add constraint fk_plb_lpy foreign key (fk_lpy) references lpy(id);
create index ix_plc_lpy on plb (fk_lpy); -- 注意：索引命名应为ix_plb_lpy，属于拼写笔误

alter table plb add constraint fk_plb_plb_cancel foreign key (fk_plb_cancel) references plb (id);
alter table plb nocheck constraint fk_plb_plb_cancel;
create index ix_plc_plb_cancel on plb (fk_plb_cancel); -- 注意：索引命名应为ix_plb_plb_cancel，属于拼写笔误

alter table plb add constraint ck_plb_pbt check (fk_pbt in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10));
alter table plb add constraint fk_plb_pbt foreign key (fk_pbt) references plb_type (id);
alter table plb nocheck constraint fk_plb_pbt;

• 索引与约束的累积开销差异：
  plb表的索引、外键约束、检查约束数量远多于rdm表：

  • rdm仅包含1个主键、1个唯一约束、2个外键、1个额外索引，无检查约束
  • plb包含1个主键、3个有效外键（删除自引用约束后）、4个索引、1个检查约束
    每插入一条记录，plb需要维护更多索引结构，同时验证多个外键和检查约束，这些操作的累积开销会大幅降低插入速率。

• 外键关联的锁竞争：
  plb表的fk_plb_rdm外键关联rdm表的主键，而rdm表正以极高速率插入数据。插入plb时需要检查rdm中是否存在对应ID，此时会对rdm的主键索引加共享锁，而rdm的高频插入会产生大量排他锁，两者之间的锁竞争会导致plb的插入操作频繁等待，进而拖慢整体速率。

• 检查约束的验证开销：
  plb表的ck_plb_pbt检查约束会对每条插入记录的fk_pbt值进行范围验证，虽然逻辑简单，但批量插入十万级记录时，累积的验证开销不可忽视，而rdm表无此类约束。

• 外键NOCHECK状态的误区：
  虽然plb的fk_plb_pbt和原自引用约束设置了nocheck，但该设置仅对已有数据生效，新插入的记录仍然会触发外键验证，因此无法减少插入时的约束检查开销。

内容的提问来源于stack exchange，提问作者Amir Pashazadeh