Oracle存储过程配合Spring Boot父子表批量读写性能优化问询

插入性能过慢优化 #

• 消除逐行插入的往返开销
  你当前每插入一条父子记录都单独调用一次存储过程，每次调用对应一次独立的数据库网络往返，70条数据就会产生上百次交互，这是插入耗时高的核心原因。
  解决方案：使用Oracle自定义集合类型批量传参，搭配FORALL语法实现批量插入，将整个写入流程的数据库交互次数降到1次。
  示例实现：
  首先定义数据库层级的集合类型：

-- 父表行对象
CREATE OR REPLACE TYPE parent_obj AS OBJECT (
    field_one VARCHAR2(255)
    -- 补齐其余父表字段
);
-- 父表集合类型
CREATE OR REPLACE TYPE parent_list AS TABLE OF parent_obj;

-- 子表行对象
CREATE OR REPLACE TYPE child_obj AS OBJECT (
    field_one VARCHAR2(255),
    parent_id NUMBER
    -- 补齐其余子表字段
);
-- 子表集合类型
CREATE OR REPLACE TYPE child_list AS TABLE OF child_obj;

新增批量插入存储过程：

PROCEDURE batchInsertParentChild(
    i_parent_list IN parent_list,
    i_child_list IN child_list,
    o_success_flag OUT NUMBER
) AS
BEGIN
    -- 批量插入父表
    FORALL i IN 1..i_parent_list.COUNT
        INSERT INTO PARENT (id, insert_time, field_one)
        VALUES (ROW_SEQUENCE.nextval, systimestamp, i_parent_list(i).field_one);

    -- 批量插入子表
    FORALL i IN 1..i_child_list.COUNT
        INSERT INTO CHILD (id, insert_time, field_one, parent_id)
        VALUES (ROW_SEQUENCE.nextval, systimestamp, i_child_list(i).field_one, i_child_list(i).parent_id);

    o_success_flag := 1;
END;

Java层使用Spring JDBC的SqlArrayValue封装集合参数传入即可，无需循环调用单条插入接口。

• 调整序列缓存
  如果ROW_SEQUENCE的缓存值设置过小（默认仅20），高频调用时会产生序列缓存争用，拖慢插入速度。执行以下命令调整缓存大小即可：

ALTER SEQUENCE ROW_SEQUENCE CACHE 1000;

查询性能过慢优化 #

• 消除N+1查询开销
  你当前先查询父表全量数据，再循环每条父记录单独调用子表查询接口，存在典型的N+1查询问题，每次循环都产生一次额外的数据库往返，是查询慢的核心原因。
  解决方案：新增关联查询存储过程，一次性返回所有符合条件的父子数据，将查询交互次数降到1次。
  示例存储过程：

PROCEDURE readParentWithChild(
    i_field_one IN VARCHAR2,
    o_parent_cursor OUT SYS_REFCURSOR,
    o_child_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    -- 返回所有符合条件的父记录
    OPEN o_parent_cursor FOR 
        SELECT * FROM PARENT WHERE field_one = i_field_one;

    -- 一次性返回所有对应父记录的子数据
    OPEN o_child_cursor FOR 
        SELECT c.* FROM CHILD c
        INNER JOIN PARENT p ON c.parent_id = p.id
        WHERE p.field_one = i_field_one;
END;

Java层拿到两个结果集后，先将子记录按parent_id分组，再遍历父列表关联对应子数据即可，无需循环调用子表查询接口。

• 新增查询索引
  检查是否有以下两个索引，缺失的话创建即可大幅提升查询过滤速度：

-- 父表查询字段索引
CREATE INDEX idx_parent_field_one ON PARENT(field_one);
-- 子表关联字段索引
CREATE INDEX idx_child_parent_id ON CHILD(parent_id);

• Spring JDBC调用优化
  你当前每次调用存储过程都新建SimpleJdbcCall对象，该对象初始化时会查询数据库存储过程元数据，产生额外开销。可以将SimpleJdbcCall设置为单例，在Bean初始化阶段预创建完成，避免每次调用重复初始化。

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