DB2含主键表插入100万行过慢问题咨询及优化需求

我有一张DB2表map_card_number：当表包含主键时，插入100万行数据耗时5分钟；移除主键后，插入相同100万行仅需11秒。具体细节如下：

1. 无主键的建表语句 #

DROP TABLE IF EXISTS MAP_CARD_NUMBER;
CREATE TABLE MAP_CARD_NUMBER
    (
        KEY_OF_MAP   VARCHAR(255) NOT NULL,
        VALUE_OF_MAP VARCHAR(1024) NOT NULL,
        UNIQUE (KEY_OF_MAP)
    );

2. 带主键的建表语句 #

DROP TABLE IF EXISTS MAP_CARD_NUMBER;
CREATE TABLE MAP_CARD_NUMBER
    (
        ID  INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
        KEY_OF_MAP   VARCHAR(255) NOT NULL,
        VALUE_OF_MAP VARCHAR(1024) NOT NULL,
        PRIMARY KEY (ID),
        UNIQUE (KEY_OF_MAP)
    );

3. 填充数据的主存储过程 #

从含100万行的customer表查询数据，遍历每行将card_number中的'41000'替换为'32888'，调用子存储过程存入map_card_number表：

CREATE OR REPLACE PROCEDURE POPULATE_CC_MAP()
    LANGUAGE SQL
BEGIN
    DECLARE v_card_number VARCHAR(25);
    DECLARE v_value VARCHAR(4000);
    DECLARE v_counter INT DEFAULT 0;

    FOR row AS cur1 CURSOR WITH HOLD FOR
        SELECT card_number
        FROM customer
        ORDER BY id
        DO
            SET v_counter = v_counter + 1;          
            SET v_card_number = row.card_number;
            SET v_value = REPLACE(v_card_number,'41000','32888');

            CALL map_put_card_number(v_card_number, v_value);      
           
            IF v_counter = 10000 THEN
              COMMIT;
              SET v_counter = 0;
            END IF;  

        END FOR;
END;

4. 插入数据的子存储过程 #

负责将键值存入表：

CREATE OR REPLACE PROCEDURE MAP_PUT_CARD_NUMBER(IN p_key VARCHAR(255), IN p_value VARCHAR(1024))
BEGIN
    MERGE INTO map_card_number AS SOURCE
    USING (VALUES (p_key, p_value)) AS merge (key_of_map, value_of_map)
    ON SOURCE.KEY_OF_MAP = merge.key_of_map
    WHEN NOT MATCHED THEN
        INSERT (key_of_map, value_of_map)
        VALUES (p_key, p_value);
END;

问题1：为何添加主键后插入速度大幅变慢？ #

1. 多索引维护开销：带主键时表存在两个索引（主键ID的B树索引 + KEY_OF_MAP的唯一约束索引），每次插入都要同时更新这两个索引，而无主键场景仅需维护唯一约束索引，IO和CPU开销直接翻倍。
2. 自增ID的锁与生成开销：主键ID是自增IDENTITY列，虽然是顺序插入，但单条高频插入时，每次申请新ID、更新主键索引页都会引发锁竞争；如果ID生成未启用缓存，还会增加额外的系统调用成本。
3. 单条操作的累积开销：当前通过循环调用子存储过程执行单条MERGE，每条语句都要经历解析、执行、索引更新的完整流程，带主键时两次索引更新的成本被逐行放大，效率远低于批量操作。

问题2：需要做哪些调整，才能让带主键的插入速度接近无主键的情况？ #

1. 替换单条操作为批量操作

• 取消逐行调用子存储过程的逻辑，改用批量MERGE。例如在主存储过程中收集一批数据后一次性执行：

  CREATE OR REPLACE PROCEDURE POPULATE_CC_MAP()
      LANGUAGE SQL
  BEGIN
      DECLARE v_counter INT DEFAULT 0;
      DECLARE key_arr VARCHAR(255) ARRAY[10000];
      DECLARE value_arr VARCHAR(1024) ARRAY[10000];
  
      FOR row AS cur1 CURSOR WITH HOLD FOR
          SELECT card_number
          FROM customer
          ORDER BY id
          DO
              SET v_counter = v_counter + 1;
              SET key_arr[v_counter] = row.card_number;
              SET value_arr[v_counter] = REPLACE(row.card_number,'41000','32888');
  
              IF v_counter = 10000 THEN
                  MERGE INTO map_card_number AS SOURCE
                  USING (
                      SELECT key_arr[i], value_arr[i]
                      FROM UNNEST(key_arr, value_arr) AS t(k, v)
                  ) AS merge (key_of_map, value_of_map)
                  ON SOURCE.KEY_OF_MAP = merge.key_of_map
                  WHEN NOT MATCHED THEN
                      INSERT (key_of_map, value_of_map)
                      VALUES (merge.key_of_map, merge.value_of_map);
                  COMMIT;
                  SET v_counter = 0;
              END IF;
          END FOR;
      IF v_counter > 0 THEN
          MERGE INTO map_card_number AS SOURCE
          USING (
              SELECT key_arr[i], value_arr[i]
              FROM UNNEST(key_arr, value_arr) AS t(k, v)
              WHERE i <= v_counter
          ) AS merge (key_of_map, value_of_map)
          ON SOURCE.KEY_OF_MAP = merge.key_of_map
          WHEN NOT MATCHED THEN
              INSERT (key_of_map, value_of_map)
              VALUES (merge.key_of_map, merge.value_of_map);
          COMMIT;
      END IF;
  END;
  

• 更高效的方式是直接用INSERT ... SELECT替代循环，完全避免逐行处理：

  INSERT INTO map_card_number (key_of_map, value_of_map)
  SELECT card_number, REPLACE(card_number,'41000','32888')
  FROM customer
  WHERE NOT EXISTS (
      SELECT 1 FROM map_card_number m 
      WHERE m.key_of_map = customer.card_number
  );
  COMMIT;
  

2. 优化主键索引与IDENTITY列

• 为IDENTITY列设置缓存，减少ID生成的锁开销：

  ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (CACHE 1000)
  

• 建表时为主键索引设置PCTFREE 0，自增主键是顺序插入，无需预留空间给后续更新，减少索引页分裂：

  CREATE TABLE MAP_CARD_NUMBER
      (
          ID  INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (CACHE 1000),
          KEY_OF_MAP   VARCHAR(255) NOT NULL,
          VALUE_OF_MAP VARCHAR(1024) NOT NULL,
          PRIMARY KEY (ID) INCLUDE (KEY_OF_MAP, VALUE_OF_MAP) PCTFREE 0,
          UNIQUE (KEY_OF_MAP)
      );
  

3. 调整事务与日志策略

• 增大提交批次（比如调整为50000行），减少事务提交次数，降低日志写入开销（需确保日志空间充足）。
• 批量插入前临时禁用索引日志（仅适用于无并发的批量导入场景）：

  ALTER INDEX PK_MAP_CARD_NUMBER ACTIVATE NOT LOGGED INITIALLY;
  -- 执行批量插入
  ALTER INDEX PK_MAP_CARD_NUMBER DEACTIVATE NOT LOGGED INITIALLY;
  

4. 移除不必要的存储过程调用

直接在主过程中执行MERGE逻辑，避免子存储过程调用的上下文切换开销，减少额外的执行成本。

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