SQL FAQ

SQL 操作执行 FAQ

如何定位创建 PL 的错误？

可以通过 SHOW ERRORS 命令，查看创建存储过程时的错误信息。

如何分析 PL 的错误日志？

用户只需要关心返回给客户端的错误日志即可。这是由于 OceanBase 数据库会将 Resolve 尝试过程中留下的错误信息记录到日志中；并且如果 PL 中包含了 EXPECTION OTHERS 语句，则执行到此处时，会在日志中留下错误信息。因此，PL 错误日志中记录的信息是不准确的，可以忽略；用户只需要关心返回给客户端的错误日志即可。

如何查询已经创建的 PL 对象的源码？

可以通过查询 DBA_SOURCE、ALL_SOURCE 或 USER_SOURCE 视图来查询已经创建的 PL 对象的源码，其中，TEXT 列即为 PL 对象的源码。

MySQL 模式是否支持 INSERT ALL INTO 这种语法？

不支持。当前仅 Oracle 模式支持 INSERT ALL INTO 语法，MySQL 模式暂不支持 INSERT ALL INTO 语法。

SQL 引擎、事务引擎等对于租户而言，资源是如何分配和隔离的？

SQL 引擎和事务引擎都是区分租户的，不同租户之间完全隔离。其中：

• SQL 引擎的 Plan Cache 与事务引擎的锁是完全独立的。
• CPU：一个租户 SQL 线程的 CPU 占用，用于控制同时活跃的 SQL 线程。
• 内存：不同租户的 SQL 内存与事务内存分开管理，一个租户的内存耗光，不会影响另一个租户。
• 线程：不同租户的 SQL 引擎与事务引擎的线程完全独立，一个租户的线程挂起不会影响另一个租户。

OceanBase 数据库的 Batch 执行是什么？

当我们使用 JDBC 和 OceanBase 数据库进行交互时，把多次请求放进一个组，从而进行一次网络传输可完成多次请求，这被称为 Batch 执行，通常也被称为“批处理”。

为什么要使用 Batch 执行？

有时候我们会因为数据的一致性而使用 Batch 执行，但更多时候，使用 Batch 执行最大的好处是可以提升性能，这体现在以下几个方面：

• Batch 语句会被改写以提高性能；
• Batch 执行可以减少和数据库的交互次数；
• OceanBase 数据库在收到 Batch 执行时可以做一些优化工作，进一步提升性能。

JDBC 中哪些 class/object 可以实现 Batch 执行？

无论使用 Statement 还是 PrepareStatement 都可以实现 Batch 执行。

使用 Batch 执行需要设置什么 JDBC 配置属性？

• 从功能上说，使用 Batch 执行必须要设置 rewriteBatchedStatements=TRUE；
• 从实现和行为上说，useServerPrepStmts 会决定 Batch 执行的不同行为；
• 从性能上说，cachePrepStmt、prepStmtCacheSize、prepStmtCacheSqlLimit、maxBatchTotalParamsNum 都会对性能有所提升。

如下是有关配置属性的说明：

哪些 OceanBase 数据库的配置项和 Batch 执行有关？

如下配置项和 Batch 执行有关系：

Statement 和 PrepareStatement 在行为和使用方法上有什么不同？

• 使用 Statement 对象：

  conn = DriverManager.getConnection(obUrl);
  conn.setAutoCommit(false);
  Statement stmt = conn.createStatement();
  String SQL = "INSERT INTO test1 (c1, c2) VALUES (1, 'test11')";
  stmt.addBatch(SQL);
  String SQL = "INSERT INTO test1 (c1, c2) VALUES (2, 'test12')";
  stmt.addBatch(SQL);
  String SQL = "INSERT INTO test1 (c1, c2) VALUES (3, 'test13')";
  stmt.addBatch(SQL);
  int[] count = stmt.executeBatch();
  stmt.clearBatch();
  conn.commit();
  

• 使用 PrepareStatement 对象：

  conn = DriverManager.getConnection(obUrl);
  conn.setAutoCommit(false);
  String SQL = "INSERT INTO TEST1 (C1, C2) VALUES (?, ?)";
  PreparedStatemen pstmt = conn.prepareStatement(SQL);
  int rowCount = 5, batchCount = 10;
  for (int k=1; k<=batchCount; k++) {
      for (int i=1; i<=rowCount; i++) {
          pstmt.setInt(1, (k*100+i));
          pstmt.setString(2, "test value");
          pstmt.addBatch();
      }
      int[] count = pstmt.executeBatch();
      pstmt.clearBatch();
  }
  conn.commit();
  pstmt.close();
  

下表列出了使用 PrepareStatement 和 Statement 对象时，Batch 执行的不同行为（前提是 rewriteBatchedStatements=TRUE ）：

使用 PrepareStatement 对象：

使用 Statement 对象：

OceanBase 数据库的 Batch 执行有哪些类型，分别对请求的优化处理有哪些？

从语句角度看，OceanBase 数据库的 Batch 执行针对 INSERT，UPDATE 和 DELETE 的处理是不同的，具体来说：

INSERT

• 场景1

  使用 PrepareStatement 对象：

  useServerPrepStmts	INSERT
  TRUE	多条 INSERT 语句的 VALUES 会以多个 “?” 的形式，拼接在一条 INSERT 语句的多个 VALUES 中，形如：INSERT INTO TEST1 VALUES (?), (?),...,(?)

  在场景1 中，OceanBase 服务器端会收到一次 INSERT 语句的 COM_STMT_PREPARE 请求（request_type=5）和 一次 INSERT 语句的 COM_STMT_EXECUTE 请求（request_type=6），从优化的角度看，有如下好处：

  • 只需要发生两次通讯就完成了 INSERT 语句的 Batch 执行。
  • PrepareStatement 天然的属性可以减少编译时间。
  • 假设后续有更多的 executeBatch，且设置了合理的 cachePrepStmts 及相关参数，可以减少 Prepare 请求（request_type=5）的次数，而只需要执行 execute 请求（request_type=6）。

• 场景2

  使用 PrepareStatement 对象：

  useServerPrepStmts	INSERT
  FALSE	多条 INSERT 语句的 VALUES 会以多个具体值的形式，拼接在一条 INSERT 语句的多个 VALUES 中，形如：INSERT INTO TEST1 VALUES (1), (2),...,(10)

  在场景2 中，OceanBase 服务器端会收到一次 INSERT 语句的 COM_QUERY 请求（request_type=2），从优化的角度看，有如下好处：

  • 只需要发生一次通讯就完成了 INSERT 语句的 Batch 执行。

• 场景3/4

  使用 Statement 对象：

  useServerPrepStmts	INSERT	场景
  TRUE	多条单独的 INSERT 语句用 “;” 拼接在一起	场景3
  FALSE	多条单独的 INSERT 语句用 “;” 拼接在一起	场景4

  在场景3/4中，OceanBase 服务器端会收到一个由多条 INSERT 语句用 “;” 拼接在一起的请求，并依次执行它们，所以也具有如下好处：

  • 只需要发生一次通讯就完成了 INSERT 语句的 Batch 执行。

UPDATE

使用 PrepareStatement 对象：

使用 Statement 对象：

• 如果没有开启 ob_enable_batched_multi_statement，场景1/2/3/4 的 UPDATE Batch 执行在 OceanBase Server 端都会被依次执行，不会有特别的优化。
• 如果开启了 ob_enable_batched_multi_statement，那么对于场景2/3/4 的 UPDATE Batch 执行，OceanBase Server 端会对格式一致的多条 UPDATE 语句当成一条语句进行解析，并根据对应的参数和数据分布，生成 batch physical plan，这可以有效的提升 Batch UPDATE 执行的效率。但在使用这个功能时需要开启显式事务。

DELETE 当前版本对于 Batch DELETE 语句没有优化效果。

如何在不同的场景下选择不同的配置？

下表列出了使用 PrepareStatement 和 Statement 对象时，Batch 执行的不同行为（前提是 rewriteBatchedStatements=TRUE ）：

使用 PrepareStatement 对象：

使用 Statement 对象：

Batch INSERT 场景1/2 能更有效的发挥 Batch 执行的性能，是推荐的配置，也就是使用如下配置：

• 场景1

  • JDBC 对象：PrepareStatement 对象

  • Server 端参数：

    _ob_enable_prepared_statement=TRUE
    

  • JDBC 配置属性：

    rewriteBatchedStatements=TRUE
    useServerPrepStmts=TRUE
    cachePrepStmts=TRUE
    prepStmtCacheSize=<根据实际情况>
    prepStmtCacheSqlLimit=<根据实际情况>
    maxBatchTotalParamsNum=<根据实际情况>
    

• 场景2

  • JDBC 对象：PrepareStatement 对象

  • JDBC 配置属性：

    rewriteBatchedStatements=TRUE
    useServerPrepStmts=FALSE
    

Batch UPDATE 场景2/3/4 使用了文本协议进行通讯，因此都能利用到多 UPDATE 语句批处理的功能，是推荐的配置，也就是使用如下配置：

• 场景2

  • JDBC 对象：PrepareStatement 对象

  • Server 端参数：

    ob_enable_batched_multi_statement=TRUE
    _enable_static_typing_engine=TRUE
    

  • Server 端变量：

    _enable_dist_data_access_service=1
    

  • JDBC 配置属性：

    rewriteBatchedStatements=TRUE
    useServerPrepStmts=FALSE
    allowMultiQueries=TRUE
    --设置这个是为了避免 JDBC 驱动不同版本间的行为差异
    

• 场景3/4

  • JDBC 对象：Statement 对象

  • Server 端参数：

    ob_enable_batched_multi_statement=TRUE
    _enable_static_typing_engine=TRUE
    

  • Server 端变量：

    _enable_dist_data_access_service=1
    

  • JDBC 配置属性：

    rewriteBatchedStatements=TRUE
    allowMultiQueries=TRUE
    --设置这个是为了避免 JDBC 驱动不同版本间的行为差异
    

如何查看 OceanBase 数据库的 Batch 执行是否生效？

最常用的方法就是通过 gv$sql_audit 来观察 Batch 执行是否生效，以下分几种场景举例：

• 场景1 的 Batch INSERT 如果生效，会在 gv$sql_audit 中看到如下记录：

    query_sql: insert into test_multi_queries (c1, c2) values (?, ?)
  request_type: 5
    ps_stmt_id: 1
  
    query_sql: insert into test_multi_queries (c1, c2) values (?, ?),(?, ?),(?, ?)
  request_type: 5
    ps_stmt_id: 2
  
    query_sql: insert into test_multi_queries (c1, c2) values (?, ?),(?, ?),(?, ?)
  request_type: 6
    ps_stmt_id: 2
  

• 场景2 的 Batch INSERT 如果生效，会在 gv$sql_audit 中看到如下记录：

  query_sql: insert into test_multi_queries (c1, c2) values (1, 'PreparedStatement; rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true'),(2, 'PreparedStatement; rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true'),(3, 'PreparedStatement; rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true')
  

• 场景2 的 Batch UPDATE 如果生效，会在 gv$sql_audit 中看到如下记录：

              query_sql: update test2 set c2='batch update1' where c1=1;update test2 set c2='batch update2' where c1=2;update test2 set c2='batch update3' where c1=3
              ret_code: 0
  is_batched_multi_stmt: 1
  

  注意

  如果 ret_code = -5787 ，这表明 Batch UPDATE 没有生效，需要根据上述说明查找原因。

Batch 执行时，executeBatch 方法返回的值是多少？

executeBatch 方法被调用后，会返回一个整型数组 int []。对于 Batch INSERT 和 Batch UPDATE 来说：

• 如果在 OceanBase 客户端最终是依次执行的，那么这个数组会返回 Batch 中每一个 Operation 所修改的行数。
• 如果在 OceanBase 客户端最终是作为一个整体执行的，比如 JDBC 驱动把多条 INSERT 语句改成了一个 INSERT 语句的多个 values（场景1/2）；又比如 UPDATE 语句作为一个 Batch physical plan 被执行（场景2），那么这个数组的每个元素会返回 -2，表示执行成功但更新行数未知。

如何解决 SQL 查询“大小账号”的问题

• 什么是“大小账号”问题：

  “大小账号”问题是指在SQL语句解析和执行过程中，由于相同的SQL ID对应不同的执行计划，导致不同查询条件下的性能表现差异显著。例如，当某条SQL语句被参数化后，计划缓存中可能存在一个不适合所有情况的执行计划。这会造成以下影响：

  当系统首次执行某条SQL（如select * from items where store = 'taobao';）时，执行计划（p1）可能是全表扫描，适用于选择率极低情况。

  如果之后执行另一条参数化SQL（如select * from items where store = 'xiaomaibu';），且选择率很高，原本的计划p1可能就不再是最优方案，导致性能下降。

• 如何识别“大小账号” SQL：

  识别“大小账号” SQL 的关键是监控逻辑读行数、影响行数和返回行数的波动。具体的识别规则包括：

  • 逻辑读行数的波动超过 1000 行。
  • 影响行数的波动超过 1000 行。
  • 返回行数的波动超过 10000 行。

  用户可以通过手动统计 v$ob_sql_audit 等视图的执行记录和统计信息来识别出存在“大小账号”的SQL。

• 如何解决“大小账号” SQL 的问题：

  • 关闭计划缓存

    1. 单条 SQL 关闭 plan cache：

       • 使用 USE_PLAN_CACHE hint，例如：

         SELECT /*+ USE_PLAN_CACHE(none) */ * FROM items WHERE store = 'taobao';
         

       • 或设置会话变量 ob_enable_plan_cache：

         SET ob_enable_plan_cache = 0;
         

    上述关闭缓存计划这种方式均需对应用程序中的 SQL 语句进行修改。如果问题是在应用上线后才被发现，则必须发布新版本的应用程序以解决该问题。您可以参考通过使用 SQL Outline 绑定 Hint 的方式，由数据库管理员为 SQL 语句添加控制。

    2. 使用 SQL Outline 绑定 Hint： 通过 SQL outline 的方式，由 DBA 控制 SQL 的执行计划。

       CREATE OUTLINE otl_no_plan_cache1
       ON select /*+ USE_PLAN_CACHE(NONE) */ * from items where store = 'taobao';
       # 或使用 SQL_ID; SQL_ID可以从 V$OB_SQL_AUDIT 等视图中查到
       CREATE OUTLINE otl_idx_c2
       ON 'ED570339F2C856BA96008A29EDF04C74'
       USING HINT /*+ USE_PLAN_CACHE(NONE) */;
       

• 开启 SQL Plan Management (SPM)

  在 OceanBase V4.2.1 及之后版本中，建议开启 SPM。该机制在某些情况下能够缓解“大小账号”问题，特别是在多个执行计划中存在一个可以接受的单一计划时。SPM 自动演进会保证在“清空 Plan Cache” 时计划会有正向演进的效果，改善执行性能。

• 解决“大小账号” SQL 带来的性能下降问题

  • 人工调优方式：在没有适合的单一执行计划时，可以通过以下方式进行人工调优：

    • 加索引：通过构建索引来提升查询性能。
    • cursor_sharing_exact hint：对大账号添加这个hint，以便让查询得到独立的执行计划。

  • 限制资源消耗：为了防止“大账号”查询影响系统性能，可以采用以下方法来限制资源消耗：

    1. 最大并发数控制：使用 MAX_CONCURRENT hint ，例如：

       CREATE OUTLINE otl_sql_throttle1 ON 'SQL_ID' USING HINT /*+ MAX_CONCURRENT(10) */;
       

    2. SQL级资源隔离：将大账号查询绑定到资源组，限制其执行中的资源消耗，示例：

       CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
           ATTRIBUTE => 'column',
           VALUE => 'items.store = \'taobao\'',
           CONSUMER_GROUP => 'slow_group');
       

       在 OceanBase V4.3.3 及之后版本，您可以通过 Hint 的方式，给特定的 SQL 语句指定资源组：

       select /*+ RESOURCE_GROUP('slow_group') */ * from items where store = 'taobao';
       

    3. 大查询任务自动隔离：通过配置项 large_query_threshold 来判定大查询，并限制其 CPU 占用，以确保小查询得到优先处理。如果同时有大查询和小查询，大查询最多占用 30% 的租户工作线程，30% 这个百分比值可以通过配置项 large_query_worker_percentage 来设置。

SQL 调优 FAQ

数据库物理设计降低查询性能

查询的性能很大程度上取决于数据库的物理设计，包括所访问对象的 Schema 信息等。例如，对于二级索引，如果所需的投影列没有包括在索引列之中，则需要使用回表的机制访问主表，查询的代价会增加很多。此时，可以考虑将用户的投影列加入到索引列中，构成所谓的"覆盖索引"，避免回表访问。

系统负载影响单条 SQL 的响应时间

系统的整体负载除了会影响系统的整体吞吐量，也会引起单条 SQL 的响应时间变化。OceanBase 数据库的 SQL 引擎采用队列模型，针对用户请求，如果可用线程全部被占用，则新的请求需要在请求队列中排队，直到某个线程完成当前请求。请求在队列中的排队时间可以在 (G)V$OB_SQL_AUDIT 中看到。

代价模型缺陷导致的执行计划选择错误

OceanBase 数据库内建的代价模型是服务器的固有逻辑，最佳的执行计划依赖此代价模型。因此，一旦出现由代价模型导致的计划选择错误，用户只能通过执行计划绑定来确保选择"正确"的执行计划。

客户端路由与服务器之间出现路由反馈逻辑错误

obproxy 的一个主要功能是将 SQL 查询路由到恰当的服务器节点。具体来说，如果用户查询没有指定使用弱一致性读属性，Proxy 需要将其路由到所涉及的表（或具体分区）的主节点上，以避免服务器节点之前的二次转发；否则，Proxy 会根据预先设置好的规则将其转发到恰当的节点。 由于 Proxy 与服务器之间采用松耦合的方式，Proxy 上缓存的数据物理分布信息刷新可能不及时，导致错误的路由选择。可能导致路由信息变化的场景有：

• 负载均衡导致重新选主
• 网络不稳导致服务器间重新选主
• 由服务器上下线、轮转合并等导致的重新选主

当在 SQL Audit 或执行计划缓存中发现有大量远程执行时，需要考虑是否与上述场景吻合。客户端与服务器之间有路由反馈逻辑，一旦发生错误，客户端会主动刷新数据物理分布信息，随后路由的选择也将恢复正常。

索引监控 FAQ

索引监控默认启动，会不会影响性能？

会影响到，但默认（SAMPLED）模式下影响很小，几乎可以忽略。

采样模式下的数据是否准确 ？例如查询了 1 次，是否一定会被记录下来？

不一定，采样的目的是为了过滤部分数据，因此某次查询的记录可能会被丢弃。

备库的索引监控是只统计自己还是同步主库的?

统计是租户级的，备库可以查到主库同步的数据，但不能写入。

分区交换 FAQ

Oceanbase 数据库 Oracle 模式下，分区表和非分区表都创建了同样的全局索引，为什么分区交换报错，是索引不匹配？

Oceanbase 数据库 Oracle 模式下，在创建索引时，如果不添加关键字 local，就会默认创建全局存储的索引，分区交换的前提是分区表的局部存储的索引和非分区表的全部索引一一匹配，否则不能进行分区交换。

为什么不能交换二级分区表的一级分区？

当前版本 Oceanbase 数据库的一级分区只是逻辑概念，实际存储层是按照二级分区组织 Tablet，所以对于二级分区表来说，要求二级分区是 Range/Range Columns 分区，对一级分区键格式不要求。

复制表 FAQ

什么是复制表？

复制表是 OceanBase 数据库支持的一种特殊表，这种表可以在任意一个副本上读到数据的最新修改。推荐应用于写入频率较低、对读操作延迟和负载均衡要求较高的业务场景。

复制表适用场景？

适用场景一：写入频率较低、读操作延迟和负载均衡要求高的场景

当表的数据量不大，访问又特别频繁的情况下，使用普通表，数据会集中在一个节点，形成热点，影响性能，可以选择用复制表。

典型复制表场景：

• 配置表，业务通过该表读取配置信息。
• 金融场景中的存储汇率的表，该表不会实时更新，每天只更新一次。
• 银行分行或者网点信息表，该表很少新增记录项。

复制表最佳实践：

1. 创建复制表时，建议按需选择，而不是在租户中创建大量复制表。
2. 写入复制表数据时，不建议复制表的写跟读在同一个事务中。
3. 查询复制表时，如果有 JOIN 查询，则按照普通表 JOIN 复制表的顺序设计查询（Query）SQL。

适用场景二：业务上不能设置为分区表、同时频繁参与跟分区表 JOIN 的场景

出于业务逻辑考虑，某些表不能拆分为分区表或者没有必要拆分（比如查询没有明显的分区条件），但它又频繁的跟分区表有关联关系。为了减少不必要的跨机分布式查询，可以将这类表设置为复制表，每个节点都支持强读，不管分区表的分区的主副本在哪个机器上，它都可以在同机跟复制表的副本做表连接。

复制表误用场景？

误用场景一：为了避免两个分区表的跨节点连接，将其中一个分区表设成复制表

为了提升性能，无脑将 Query 中的分区表设置为复制表，并不科学，这些表不一定是复制表适用场景，可能达不到预期的效果。

• 表存在频繁的写入操作，复制表的写是有性能代价的，需要同步到所有副本，理论上同步是并发的，如果有个别节点，因为网络等原因，同步非常慢，会导致整个复制表的写性能下降。
• 如果复制表数据量非常大，复制表在每个节点都有副本，会占用大量的存储空间。

误用场景二： 事务中含有复制表的写和读

复制表的纯写事务或者纯读事务，都是推荐的，只需要关注业务是否能接受写入延迟。

如果事务中同时存在复制表的写和读，因为需要读 Leader，则会导致事务中所有复制表都退化为普通表的性能，无法发挥复制表可以读取本地副本的优势。

误用场景三：复制表 JOIN 普通表

ODP（obproxy）对多表 JOIN 的路由规则是，按照解析到的第一张表路由，如果是复制表，会随机选择一个副本路由。如果此节点恰好不是普通表的 Leader，则会发生远程路由，影响性能。

如果业务中涉及复制表跟普通分区表的 JOIN，建议调整 JOIN 顺序为普通分区表 JOIN 复制表，按照普通分区表路由，按预期生成本地计划，优化性能。

误用场景四：复制表设置为分区表

复制表本身就是各个节点都会创建副本的，不需要再设置为分区表。

自增列 FAQ

什么情况下需要将自增列数据类型设置为 BIGINT？

• 业务本身数据增长快，保留数据周期长。
• 业务不在意建表使用 BIGINT 还是 INT。
• Leader 打散，机器切主的概率变大（例如宕机、random 负载均衡等），自增列数值跳变的概率变大。
• NOORDER 模式，需要显式指定自增列值。

什么情况下允许自增列数据类型保持为 INT？

• 业务数据量本身远小于 INT 上限。
• 从 MySQL 迁移的业务，需要使用 INT 类型，否则应用可能存在兼容性问题。
• 单机场景，切主的场景很少。
• 有一定的监控运维能力，自增值个数接近上限时进行运维处理，如重建表导数、或 INT 改 BIGINT（从 V4.2.2 版本开始，列类型由 INT 修改为 BIGINT 为 Online DDL 操作）等。

什么情况下可以将自增列改为 NOORDER？

• 当用户无自增列表级有序需求，并期望优化高并发操作的性能时，可以将 ORDER 修改为 NOORDER。
• 当用户有自增列表级有序需求，但是 Leader 都在一个 OBServer 上时，且期望优化高并发操作的性能时，可以将 ORDER 修改为 NOORDER。

什么情况下可以改小自增列 auto_increment_cache_size？

• 跳变问题比较突出。
• 业务流量很低。
• 性能不敏感。
• 对性能有要求，但是为单机模式，Leader 集中在 1 个节点。