Skip to main content

SQL 开发规范

说明：

本小节内容仅供参考，只是 “推荐” 用户做的内容，并不 “强制” 用户必须遵循。大家根据真实业务需求决定是否采取这些建议即可。

SELECT 语句开发规范​

  • 使用到分区表的 SQL 语句，其过滤谓词中尽量带上分区键，避免不必要的全表扫描。

  • order by 查询语句中，order by 的字段建议能够保证唯一性或者组合唯一。

    • 对非唯一字段排序，由于分布式数据库与单机数据库实现方式出入较大，结果可能会出现不稳定的情况。这里的 “不稳定” 指的是，在分布式数据库中，order by c1 的结果可能是：

      c1c2
          1        a    
      1b
      2c
      2d

      但也可能是：

      c1c2
          1        b    
      1a
      2d
      2c

  • 不推荐使用 in 操作。带 in 的子查询尽量改写成 join。若实在无法避免，需要仔细评估 in 后边的集合元素数量，控制在 100 个之内。

  • 如无去重需求，不建议使用 UNION，推荐使用 UNION ALL。UNION ALL 可以减少不必要的排序开销。

  • count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 值的行。要根据业务需求，选择合适的聚合方式。

  • 不推荐使用非同类型的列进行比较或 join，可能会因为出现隐式类型转换而导致无法利用索引的问题。如无法避免，尽量通过 cast 或者 convert 等方式进行必要的显式类型转换。

  • 不推荐使用 where (c1, c2) = ('abc', 'def') 这种写法。首先这不是标准 SQL 的写法，只在部分数据库中支持，可移植性差。其次即使被部分数据库支持，一般也无法利用索引，也无法利用各种优化，导致查询效率大幅降低。建议把上述过滤条件改写为：where c1 = 'abc' and c2 = 'def' 。

  • 在设置并行度的时候，最佳并行度为表的分片数，次佳并行度为使用 auto dop 自动生产的并行度。

  • 不推荐读锁 SELECT ... for update (with cs)。如果事务较大，高并发下容易导致锁等待影响业务。

  • SELECT 语句推荐指定具体的字段名称，只写必要的列。尽量不要写成 " SELECT * " 的形式，这样写有几个问题：

    • 一是可能会增加查询分析器解析成本，同时也会产生额外的 IO 成本。
    • 二是可能会造成一些不必要的索引回表动作，导致查询性能下降。
    • 三是在表中增减字段时，容易造成与上下游的 resultMap 配置不一致的问题。

DML 语句开发规范​

  • INSERT 语句推荐指定具体的字段名称（column_name），不建议写成 INSERT INTO TBL VALUES(......) 形式。否则在表中增减字段时，容易出现一些非预期的情况。

  • 语句对数据的修改效果只有在提交事务时才永久生效。单个 DML 语句也可以是一个事务，事务是否自动提交可以通过 autocommit 系统变量进行配置，默认是一条 DML 一个事务，并会自动提交。

  • 不带条件更新的时候，数据量较大时，会有大事务产生，可能由于事务超时导致执行失败。

    • UPDATE 语句，建议使用 WHERE 条件来控制修改的行数，确保事务不要太大。
    • DELETE 语句，建议带上 WHERE 条件分批删除。或者通过 TRUNCATE TABLE 这个 DDL 删除全表数据，可以大幅提高删除数据的性能（但 TRUNCATE 是 DDL，不能放在其他事务内）。
    • 实在不行，可以通过调大相关的超时时间（ob_query_timeout 和 ob_trx_timeout），来保证大事务成功执行。

  • 频繁通过 DML 语句插入、删除和更新的表，可能会造成 Buffer 表问题，可通过开启自适应合并或者设置 table_mode 来减缓该问题，详见：OceanBase 官网文档。

DDL 语句开发规范​

  • 建议用户了解 OceanBase 的 Online DDL 和 Offline DDL 列表。Offline DDL 执行时会锁表，表上无法执行其他 DML 操作。

  • DDL 操作建议在业务低峰时段进行，以降低对系统的影响。

Loading...