OceanBase 批量插入（多值转换）提升性能测试

在应用开发中，我们通常会使用 JDBC 的 addBatch() 和 executeBatch() 来提升插入性能。OceanBase 支持将多条 INSERT INTO VALUES() 自动重写合并为 INSERT INTO VALUES(), () 的多值语句，从而大幅减少网络开销。可能因参数的配置，性能相差近百倍。

1. 核心开关：rewriteBatchedStatements
JDBC 驱动默认不会自动合并 SQL。你必须在数据库连接字符串（JDBC URL）中显式开启以下参数：
rewriteBatchedStatements=true

2. 如何验证是否生效？（精准排查 SQL）
在 OceanBase 4.x 版本中，我们可以通过查询 SQL 审计视图 gv$ob_sql_audit 来精准定位那些“伪装”成批量的单条插入

SELECT
    TENANT_NAME,
    USER_NAME,
    SQL_ID,
    min(SUBSTR(QUERY_SQL, 1, 80)) AS sql_preview,
    COUNT(*)                    AS exec_count,
    SUM(AFFECTED_ROWS)         AS total_rows,
    ROUND(SUM(ELAPSED_TIME) / 1000, 1)  AS total_ms,
    ROUND(AVG(ELAPSED_TIME) / 1000, 1)  AS avg_ms,
    MIN(FROM_UNIXTIME(REQUEST_TIME / 1000000)) AS first_seen,
    MAX(FROM_UNIXTIME(REQUEST_TIME / 1000000)) AS last_seen
FROM oceanbase.GV$OB_SQL_AUDIT
WHERE QUERY_SQL LIKE 'INSERT INTO big_order_table %'
  AND AFFECTED_ROWS = 1
  AND IS_BATCHED_MULTI_STMT = 0
  AND IS_INNER_SQL = 0
 AND REQUEST_TIME > (NOW() - 2 / 24)   -- 最近2小时
GROUP BY TENANT_NAME, USER_NAME, SQL_ID
HAVING COUNT(*) > 1
ORDER BY exec_count DESC
LIMIT 20;

is_batched_multi_stmt = 0：明确表示该 SQL 未作为批量多语句执行。但测试似乎并没生效
affected_rows = 1：说明每次执行仅插入了一行数据。

实战对比：性能差距有多大？

为了直观展示开启优化前后的差距，我们使用 Java 代码模拟两种场景（各插入 1000 条数据）：

场景 A：未开启优化（逐条插入）

配置：rewriteBatchedStatements=false 或在循环中直接调用 executeUpdate()。
表现：产生 1000 次网络往返。
预期耗时：数千毫秒甚至更高（取决于网络延迟）。

场景 B：开启优化（多值转换）

配置：rewriteBatchedStatements=true 并配合 addBatch() / executeBatch()。
表现：JDBC 驱动自动将 SQL 合并，大幅减少网络请求次数。
预期耗时：通常仅需几百毫秒。

-- 创建表

CREATE TABLE big_order_table (
    id NUMBER NOT NULL,
    order_no VARCHAR2(64) NOT NULL,
    user_id NUMBER NOT NULL,
    amount NUMBER(12, 2) NOT NULL,
    status NUMBER(1) DEFAULT 0 NOT NULL,
    create_time DATE DEFAULT SYSDATE NOT NULL,
    update_time DATE DEFAULT SYSDATE NOT NULL,
    PRIMARY KEY (id, create_time) -- 分区键 create_time 必须包含在主键中
) 
PARTITION BY RANGE (create_time) (
    PARTITION p202501 VALUES LESS THAN (TO_DATE('2025-02-01', 'YYYY-MM-DD')),
    PARTITION p202502 VALUES LESS THAN (TO_DATE('2025-03-01', 'YYYY-MM-DD')),
    PARTITION p202503 VALUES LESS THAN (TO_DATE('2025-04-01', 'YYYY-MM-DD')),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
); 

[admin@observer1 ~]$ obclient -h172.20.22.213 -P 2883 -uanbob@tantentname#clustername -pxxx -A -c
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 276513
Server version: OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient(ANBOB@orcldb)[ANBOB]> desc big_order_table
    -> ;
+-------------+--------------+------+------+---------+-------+
| FIELD       | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------------+--------------+------+------+---------+-------+
| ID          | NUMBER       | NO   | PRI  | NULL    | NULL  |
| ORDER_NO    | VARCHAR2(64) | NO   | NULL | NULL    | NULL  |
| USER_ID     | NUMBER       | NO   | NULL | NULL    | NULL  |
| AMOUNT      | NUMBER(12,2) | NO   | NULL | NULL    | NULL  |
| STATUS      | NUMBER(1)    | NO   | NULL | 0       | NULL  |
| CREATE_TIME | DATE         | NO   | PRI  | sysdate | NULL  |
| UPDATE_TIME | DATE         | NO   | NULL | sysdate | NULL  |
+-------------+--------------+------+------+---------+-------+
7 rows in set (0.172 sec)

Java 程序测试

注：每个跑2次，以第二次为主

D:\code\objava>set CLASSPATH=.;D:\code\objava\oceanbase-client-2.4.1.jar 


D:\code\objava>javac SingleInsertSimulation.java


D:\code\objava>java SingleInsertSimulation
1000条单条插入模拟完成，总耗时: 40678 毫秒

D:\code\objava>java SingleInsertSimulation
1000条单条插入模拟完成，总耗时: 40082 毫秒

D:\code\objava>javac BatchInsertSimulation.java

D:\code\objava>java BatchInsertSimulation
1000条批量插入（多值转换）模拟完成，总耗时: 638 毫秒

D:\code\objava>java BatchInsertSimulation
1000条批量插入（多值转换）模拟完成，总耗时: 445 毫秒

-- single insert
obclient(root@sys)[oceanbase]> select  TENANT_NAME,
    ->     USER_NAME,
    ->     SQL_ID,
    ->     SUBSTR(QUERY_SQL, 1, 120) AS sql_preview,
    -> affected_rows,
    ->     is_batched_multi_stmt
    -> FROM oceanbase.GV$OB_SQL_AUDIT
    -> WHERE UPPER(QUERY_SQL) LIKE 'INSERT INTO big_order_table %'
    -> limit 20;
+-------------+-----------+----------------------------------+-------------------------------------------------------------------------------------------------------------+---------------+-----------------------+
| TENANT_NAME | USER_NAME | SQL_ID                           | sql_preview                                                                                                 | affected_rows | is_batched_multi_stmt |
+-------------+-----------+----------------------------------+-------------------------------------------------------------------------------------------------------------+---------------+-----------------------+
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (1, 'ORD1', 10001, 9.569977793884465)    |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (2, 'ORD2', 10002, 238.15989080280454)   |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (3, 'ORD3', 10003, 75.09388124505112)    |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (4, 'ORD4', 10004, 611.6730286672937)    |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (5, 'ORD5', 10005, 934.3615452257852)    |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (6, 'ORD6', 10006, 211.23171080543813)   |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (7, 'ORD7', 10007, 8.683751814191453)    |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (8, 'ORD8', 10008, 733.2058879553168)    |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (9, 'ORD9', 10009, 557.6355709321513)    |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (10, 'ORD10', 10010, 696.0138484372108)  |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (11, 'ORD11', 10011, 579.5026994640237)  |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (12, 'ORD12', 10012, 604.7078848102716)  |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (13, 'ORD13', 10013, 266.0851178301534)  |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (14, 'ORD14', 10014, 570.8929319839917)  |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (15, 'ORD15', 10015, 763.402950645369)   |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (16, 'ORD16', 10016, 755.9114384386044)  |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (17, 'ORD17', 10017, 281.11333141624084) |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (18, 'ORD18', 10018, 222.66982242355337) |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (19, 'ORD19', 10019, 75.20134745551066)  |             1 |                     0 |
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (20, 'ORD20', 10020, 656.6989630222167)  |             1 |                     0 |
+-------------+-----------+----------------------------------+-------------------------------------------------------------------------------------------------------------+---------------+-----------------------+
20 rows in set (1.030 sec) 

obclient(root@sys)[oceanbase]> SELECT
    ->     TENANT_NAME,
    ->     USER_NAME,
    ->     SQL_ID,
    ->     min(SUBSTR(QUERY_SQL, 1, 80)) AS sql_preview,
    ->     COUNT(*)                    AS exec_count,
    ->     SUM(AFFECTED_ROWS)         AS total_rows,
    ->     ROUND(SUM(ELAPSED_TIME) / 1000, 1)  AS total_ms,
    ->     ROUND(AVG(ELAPSED_TIME) / 1000, 1)  AS avg_ms,
    ->     MIN(FROM_UNIXTIME(REQUEST_TIME / 1000000)) AS first_seen,
    ->     MAX(FROM_UNIXTIME(REQUEST_TIME / 1000000)) AS last_seen
    -> FROM oceanbase.GV$OB_SQL_AUDIT
    -> WHERE QUERY_SQL LIKE 'INSERT INTO big_order_table %'
    ->   AND AFFECTED_ROWS = 1
    ->   AND IS_BATCHED_MULTI_STMT = 0
    ->   AND IS_INNER_SQL = 0
    ->  AND REQUEST_TIME > (NOW() - 2 / 24)   -- 最近2小时
    -> GROUP BY TENANT_NAME, USER_NAME, SQL_ID
    -> HAVING COUNT(*) > 1
    -> ORDER BY exec_count DESC
    -> LIMIT 20;
+-------------+-----------+----------------------------------+----------------------------------------------------------------------------------+------------+------------+----------+--------+--------------------------+--------------------------+
| TENANT_NAME | USER_NAME | SQL_ID                           | sql_preview                                                                      | exec_count | total_rows | total_ms | avg_ms | first_seen               | last_seen                |
+-------------+-----------+----------------------------------+----------------------------------------------------------------------------------+------------+------------+----------+--------+--------------------------+--------------------------+
| orcldb      | ANBOB     | DCF49995B8D469EE6B3CF17FF607BC97 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (1, 'ORD1', 1 |       2000 |       2000 |   2601.1 |    1.3 | 2026-05-19 09:20:17.7286 | 2026-05-19 09:56:18.8656 |
+-------------+-----------+----------------------------------+----------------------------------------------------------------------------------+------------+------------+----------+--------+--------------------------+--------------------------+
1 row in set (0.494 sec)

-- 批量 insert

obclient(root@sys)[oceanbase]> select  TENANT_NAME,
    ->     USER_NAME,
    ->     SQL_ID,
    ->     SUBSTR(QUERY_SQL, 1, 120) AS sql_preview,
    -> affected_rows,
    ->     is_batched_multi_stmt
    -> FROM oceanbase.GV$OB_SQL_AUDIT
    -> WHERE UPPER(QUERY_SQL) LIKE 'INSERT INTO big_order_table %'
    -> and AFFECTED_ROWS >1
    -> limit 20;
+-------------+-----------+----------------------------------+--------------------------------------------------------------------------------------------------------------------------+---------------+-----------------------+
| TENANT_NAME | USER_NAME | SQL_ID                           | sql_preview                                                                                                              | affected_rows | is_batched_multi_stmt |
+-------------+-----------+----------------------------------+--------------------------------------------------------------------------------------------------------------------------+---------------+-----------------------+
| orcldb      | ANBOB     | 163A4DDEBE2CADA05745A558BCFB45B0 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ? |          1000 |                     0 |
| orcldb      | ANBOB     | 163A4DDEBE2CADA05745A558BCFB45B0 | INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ? |          1000 |                     0 |
+-------------+-----------+----------------------------------+--------------------------------------------------------------------------------------------------------------------------+---------------+-----------------------+
2 rows in set (0.394 sec)

obclient(root@sys)[oceanbase]> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 5.7.25-OceanBase-v4.2.5.3 |
+---------------------------+
1 row in set (0.004 sec)

附： java代码

single insert

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class SingleInsertSimulation {
    // 模拟没有开启 rewriteBatchedStatements 的 JDBC URL
    // 即使写了 addBatch，如果没开这个参数，底层依然是单条发送
    private static final String URL = "jdbc:oceanbase://172.20.22.xxx:2883/?pool=false&" +
            "useSSL=false&rewriteBatchedStatements=false"; 
    private static final String USER = "anbob@orcldb#tantent1";
    private static final String PASSWORD = "password1";

    public static void main(String[] args) {
        
        String sql = "INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (?, ?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            conn.setAutoCommit(false);
 // 记录开始时间
 long startTime = System.currentTimeMillis();

            // 模拟插入 1000 条数据
            for (int i = 1; i <= 1000; i++) {
                pstmt.setLong(1, i);
                pstmt.setString(2, "ORD" + i);
                pstmt.setLong(3, 10000L + i);
                pstmt.setDouble(4, Math.random() * 1000);
                
                // ❌ 现象模拟：在循环内直接执行，或者使用 addBatch 但未开启 rewrite 参数
                // 这会导致发送 1000 次网络请求，每次都是单条 INSERT
                pstmt.executeUpdate(); 
            }
            conn.commit();
// 记录结束时间并计算耗时
long endTime = System.currentTimeMillis();
System.out.println("1000条单条插入模拟完成，总耗时: " + (endTime - startTime) + " 毫秒");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

batch insert

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BatchInsertSimulation {
    // 开启 rewriteBatchedStatements=true，触发 JDBC 驱动的多值转换优化
    private static final String URL = "jdbc:oceanbase://172.20.22.xxx:2883/?pool=false&" +
            "useSSL=false&rewriteBatchedStatements=true&useServerPrepStmts=true";
    private static final String USER = "anbob@orcldb#tantent1";
    private static final String PASSWORD = "password1";

    public static void main(String[] args) {
        String sql = "INSERT INTO big_order_table (id, order_no, user_id, amount) VALUES (?, ?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            conn.setAutoCommit(false);

            // 记录开始时间
            long startTime = System.currentTimeMillis();

            for (int i = 1; i <= 1000; i++) {
                pstmt.setLong(1, i);
                pstmt.setString(2, "ORD" + i);
                pstmt.setLong(3, 10000L + i);
                pstmt.setDouble(4, Math.random() * 1000);
                // 仅将 SQL 加入批处理队列，不发起网络请求
                pstmt.addBatch();
            }
            
            // 统一执行批处理，驱动会自动将其合并为多值 INSERT 发送给数据库
            pstmt.executeBatch();
            conn.commit();

            // 记录结束时间并计算耗时
            long endTime = System.currentTimeMillis();
            System.out.println("1000条批量插入（多值转换）模拟完成，总耗时: " + (endTime - startTime) + " 毫秒");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

— over —

实战对比：性能差距有多大？

Leave a Comment Cancel reply