配置 JDBC 和 OBServer 实现 Batch DML 最佳性能的最佳实践

应用场景

在数据库应用中，当相同的 SQL 语句被频繁执行但参数各异时，可以通过优化策略实现批量执行。此过程需要客户端和 OBServer 端的协同。在客户端，可以通过调整 JDBC 参数，将 SQL 语句批量发送至 OBServer；而在服务端，OBServer 会对这些批量 SQL 进行改写，生成对应的批量执行计划。在某一端进行优化可能会带来一定的性能提升，但同时在客户端和 OBServer 端进行优化会更有效，能够全方位提高系统性能和响应速度。

这种优化方式能显著提高性能，主要体现在以下几个方面：

1. JDBC 驱动能够一次性发送多条 SQL 语句，从而节省网络请求的开销。

2. OBServer 对包含多条语句的 SQL 进行改写，生成批量执行计划，减少了参数化、查找计划缓存及构建和析构 SQL 执行上下文的开销，从多次减少为一次。

3. 借助批量执行计划，OBServer 存储引擎也能优化批量操作，例如将数据批量写入 memtable，以及批量检查行锁和主键冲突。

本篇文章将阐明相关概念、不同使用模式下的配置方法，以及如何确认批量执行优化的实际应用。

面向读者

• 解决方案架构师
• DBA

关键概念

Multi Queries

Multi Queries 是 MySQL 模式下 JDBC 批量发送 SQL 的协议，它通过分号（“;”）将多条 SQL 语句连接在一起。这种方式可以减少客户端与服务端之间的通信次数，从而在一定程度上提升性能。尽管可以自由拼接 SQL 语句，如下面的示例所示，OBServer 端仍然会按照单条 SQL 的方式逐一执行：

-- 虽然是使用了multi queries，由于操作不同的表，这些语句不会在服务端进行批量执行
update t1 set b=2 where a=1;
update t2 set b=3 where a=2;
delete from t1 where a=3;

以下示例可以成功进行批量执行优化：

-- 同表的多条更新语句
update t1 set b=1 where a=1;
update t1 set b=2 where a=2;
update t1 set b=3 where a=3;
-- 同表的多条插入语句
insert into t1 select * from t2 where t2.c1 = 1;
insert into t1 select * from t2 where t2.c1 = 2;
-- 多条删除语句
delete from t1 where a = 1;
delete from t1 where a = 2;
delete from t1 where a = 3;

如果使用预编译语句（PreparedStatement, PS），格式如下：

prepare_stmt; // 定义 SQL 语句模板
addBatch();   // 绑定参数
...
addBatch();
executeBatch(); // 执行所有批量参数的语句

Array Binding

Array binding 是 Oracle 模式下的 JDBC 批量发送 SQL 的协议，仅在 PS 协议下使用。JDBC 驱动将 SQL 语句与参数拆分，将相同列类型的参数组合为数组发送到 OBServer，其用法与 MySQL 模式下的 PS 协议类似，本章节将不做赘述。

Multi Values Insert

使用 insert into t1 values(x), (y), (z)...; 语句插入多条数据，此语句本身可实现批量执行，因此执行性能较优。

如何配置

配置 OBServer

无论是 MySQL 模式还是 Oracle 模式，OBServer 的配置保持一致，与批量执行优化相关的配置项如下：

• ALTER SYSTEM SET ob_enable_batched_multi_statement = true;
• SET GLOBAL _nlj_batching_enabled = true;

ob_enable_batched_multi_statement 为租户级别配置项。设置 ob_enable_batched_multi_statement 后，OBServer 能够为通过 Multi queries 或 Array binding 方式发送的批量 SQL 生成执行计划。

_nlj_batching_enabled 为租户变量。设置 _nlj_batching_enabled 将启用 Nested Loop Join 的 Group Rescan 优化，尽管与 SQL 批量执行关系不大，但生成的批量执行计划中常常会利用这一优化，因此建议开启。

这两项配置都需要在租户下执行。

配置 JDBC

在应用程序通过数据库 URL 连接数据库时，可以设置相应的 JDBC 参数，例如：

conn=jdbc:oceanbase://xxx.xxx.xxx.xxx:2883/?rewriteBatchedStatements=TRUE&allowMultiQueries=TRUE&useLocalSessionState=TRUE&useUnicode=TRUE&characterEncoding=utf-8&socketTimeout=3000000&connectTimeout=60000&user=xxx@tenant&password=****

OceanBase 数据库会依据 JDBC 驱动连接时的租户名称判断运行模式（MySQL 或 Oracle）。

MySQL 模式配置

在 MySQL 模式下，JDBC 参数的配置建议如下：

conn=jdbc:oceanbase://xxx.xxx.xxx.xxx:2883/?allowMultiQueries=TRUE&rewriteBatchedStatements=TRUE&useLocalSessionState=TRUE&useUnicode=TRUE&characterEncoding=utf-8&socketTimeout=3000000&connectTimeout=60000&user=xxx@tenant&password=****

• allowMultiQueries：默认为 FALSE，必须设置为 TRUE，以允许使用分号连接的多语句文本格式。对使用文本协议的场景，只需开启此配置即可实现批量优化。
• rewriteBatchedStatements：默认为 FALSE，若使用 PS 协议，则必须设置为 TRUE，以在执行 executeBatch() 时将多条语句改写为分号连接的多语句格式。

在 MySQL 模式下，建议将这两个配置均设为 TRUE，以便在使用文本或 PS 协议时均可实现批量优化。

特例说明：开启 rewriteBatchedStatements 后，对于多条 multi queries 插入语句（无论使用文本协议或 PS 协议），JDBC 驱动会将其改写为一条 Multi Values Insert 语句。

Oracle 模式配置

在 Oracle 模式下，JDBC 参数的配置建议如下：

conn=jdbc:oceanbase://xxx.xxx.xxx.xxx:2883/?useArrayBinding=TRUE&useServerPrepStmts=TRUE&user=xxx@tenant&password=****

• useArrayBinding：默认为 FALSE，需设置为 TRUE，并且在使用该设置时，必须禁止自动提交（即将 autoCommit 设置为 FALSE），以启用 array binding 协议，仅在 PS 协议下生效。在执行 executeBatch() 时，参数会以数组形式发送到 OBServer。
• useServerPrepStmts：默认为 FALSE，设置为 TRUE 后将在服务器端进行 PrepareStatement，只有同时设置此参数，array binding 协议才会生效。

在 Oracle 模式下，建议这两个配置均设为 TRUE。

如何确认走到了批量优化

除了正确配置 OBServer 和 JDBC 外，SQL 语句还需符合以下要求：

1. 在 Multi Queries 中，每条 SQL 必须具备高度一致性，即同构，以有效利用批量优化，从而确保高性能。此外，常量参数的类型也需保持一致，以避免类型转换，这有助于顺利生成最优的执行计划，进而提升查询的执行效率。

2. 必须使用显式事务，通过设置 autocommit=0 或使用 BEGIN 开启事务。由于批量优化后，所有语句将作为一个整体执行，若其中一条出错则整体回滚，这与普通 multi queries 的行为不同。

3. 一些复杂的 SQL 可能无法改写为批量执行形式，例如使用 Hash join 或包含 case...when 语句等。这些情况无法一一列举。因此，即便其他优化条件满足，OBServer 端未能实现批量执行也是正常的。

要确认 OBServer 是否实现了批量执行优化，可以通过以下方式：

1. 如果对批量语句的改写原理较为了解，可以直接查看执行计划。通常，批量执行计划会使用 NLJ 算子连接由参数组组成的虚拟视图及原有的 Join order。

2. 通过 trace_id 在视图 gv$ob_sql_audit 中查找 sql_id，然后再使用这个 sql_id 来查询执行计划的批量优化信息：

   -- 使用 trace_id 查找对应的 sql_id
   SELECT sql_id FROM gv$ob_sql_audit WHERE trace_id = 'your_trace_id';
   -- 使用查到的 sql_id 查询批量优化信息
   SELECT is_batched_multi_stmt FROM gv$OB_PLAN_CACHE_PLAN_STAT WHERE SQL_ID='xxxx';
   

   如果输出为 1，则表示实现了批量优化。

3. 也可以在视图 gv$ob_plan_cache_stat 中直接查找执行语句的匹配项，例如：

   SELECT is_batched_multi_stmt FROM gv$OB_PLAN_CACHE_PLAN_STAT WHERE statement LIKE 'update t1 set c1=%';
   

相关文档

• OceanBase 连接方式概述
• OceanBase JDBC 连接池配置示例
• OceanBase 数据库 URL