SQL 规范

SQL 规范概述

SQL 研发规范是 SQL 质量保障的基础，是帮助 SQL 变得更加稳定的重要手段。在 SQL 运行阶段发现的 SQL 问题，可能已经对业务造成影响，且常用的 SQL 应急没法覆盖所有的 SQL 问题，比较被动。

常用的 SQL 应急手段包括 SQL 限流和 SQL 执行计划干预：

• SQL 限流：对 SQL 执行并发进行限制，对被限流 SQL 的业务本身是有损的，属于一种限制异常 SQL 来恢复数据库整体的手段。

• SQL 执行计划干预：SQL 执行阶段可能没有使用最优的索引，通常通过绑定 SQL 执行计划或者创建更合适的索引来恢复，绑定计划属于即时生效但不是所有 SQL 都有合适的索引，创建索引速度慢且可能 SQL 本身没法使用索引，如全表扫描 SQL。

所以，制定明确的 SQL 研发规范，可以将 SQL 问题的节点前移至研发阶段，解决代码中出现低级 SQL 错误、规避潜在 SQL 隐患。

SQL 规范包含两个维度：

• 质量：影响 SQL 执行性能的规范，减少低级 SQL 错误和低性能 SQL，规范通常具有普适性和一般性，如：多种全表扫描的 SQL 类型。

• 风格：影响 SQL 可读性的规范，便于 SQL 业务场景理解，辅助人为 SQL 问题排查，规范通常与业务场景相关，如：表、字段、索引的命名约定。

SQL 规范包含两种建议：

• 强制：表示必须遵守的规范，否则很可能会产生很大影响，比如全表扫描。

• 推荐：表示建议参考的规范，不遵守可能会产生影响，但是业务场景所需，比如多表联合更新。

[强制][质量] 禁止使用 SELECT *

说明

代码中如果直接使用 SELECT *，在后续表结构变更增加字段后，易引发代码层面的报错。

错误代码示例

先通过 SELECT * 获取所有数据，再拿到 phone_num。

<select id="getPhoneNum" parameterType="int" resultType="com.shadow.foretaste.entity.UserInfo">
        select * from user_info where id = #{id}
</select>

正确代码示例

直接通过列名 phone_num 获取对应的字段。

<select id="getPhoneNum" parameterType="int"  resultType="String">
    select phone_num  from user_info where id = #{id}
</select>

[强制][质量] 禁止使用全表扫描的 SQL

说明

SQL 全表扫描可能会带来很大的性能隐患，容易引发数据库瘫痪，禁止在代码中使用全表扫描的 SQL。

错误代码示例

错误示例 1：没有带 where 条件的 SQL

select 1 from a

错误示例 2：使用不等值过滤的 SQL

select 1 from a where b != ?
select 1 from a where b <> ?
select 1 from a where b not like ?
select 1 from a where b not in ?
select 1 from a where not exists ()

错误示例 3：使用右模糊匹配的 SQL

select 1 from a where b like %a
select 1 from a where b like %a%

[强制][质量] 禁止字段函数&运算操作、类型转换

说明

SQL where 过滤条件中字段禁止函数或运算符操作，会导致无法走上对应字段的索引。

禁止字段类型转换操作，会导致隐式转换无法走上对应字段的索引。

函数操作

#错误示例
select 1 from a where substr(name,1,3) = '123'

#正确示例
select 1 from a where name like '123%'

#错误示例
select 1 from a where data_format(create_time,'%b %d %Y %h:%i %p') = '2022-12-06 10:30:00'

#正确示例
select 1 from a where create_time = str_to_date('2022-12-06 10:30:00')

运算操作

##错误示例
select 1 from a where a*2 = 10

##正确示例
select 1 from a where a = 10/2

类型转换

#错误示例，b 字段定义为 varchar
select 1 from a where b = 123

#正确示例
select 1 from a where b = '123'

[强制][质量] 禁止谓词中使用 or 操作

说明

or 谓词无法走上两边字段的索引，会有一次全表扫描。

示例

#错误示例，同字段 or
select 1 from a where b = 123 or b = 456

#正确示例
select 1 from a where b in (123, 456)

#多字段 or
select 1 from a where b = 123 or c = 456

正确示例
select 1 from a where b = 123
union all
select 1 from a where c = 456

[强制&推荐][质量] 数据更新规范

说明

数据订正流程的 SQL 规范：

• 【强制】批量数据订正

  大批量数据订正，需要先 select 查询出需要订正行的主键，再拆分成批次批量订正。

• 【推荐】多表关联更新

  不建议多表关联的 update/delete 操作，可以拆分多个 SQL 按事务进行单表更新操作。

• 【推荐】多行 insert

  批量数据的 insert 写入建议合并为单条 insert 多个值，单条 insert 行数建议不超过 200 行。

[推荐][质量] in 谓词规范

说明

in 操作需要评估 in 后边的集合元素数量，推荐不超过 200 个。

[强制][质量] 对于可 NULL 字段使用过程中需要单独进行处理

说明

• 唯一索引：UK 索引中如果所包含的某一列可 NULL，该列为 NULL 值的数据将失去 UNIQUE 约束限制。
• WHERE 条件：NULL 值不参与 where 条件等值比较，如 =、!=、<>、in、like 等操作，当数值为 NULL 值时，where 条件极易产生不符合预期的结果。
• 聚合函数：部分聚合函数，如 count、distinct、sum 等，会跳过 NULL 值，产生不符合预期的结果。

mysql> select 1 = 1,1 = null,1 is null, null is null ;
+-------+----------+-----------+--------------+
| 1 = 1 | 1 = null | 1 is null | null is null |
+-------+----------+-----------+--------------+
|     1 |     NULL |         0 |            1 |
+-------+----------+-----------+--------------+

错误代码示例

存在如下表。

CREATE TABLE `test_null` (
  `id` int primary key,
  `a` int NOT NULL,
  `b` int DEFAULT NULL
  UNIQUE KEY `idx_a_b` (`a`, `b`)
)

• 错误代码示例 1：UK 中存在 NULL 值

  id 为 1 和 2 的数据 a 和 b 的数值虽然均为（1，NULL）但是在数据库层面不认为 NULL=NULL，所以虽然有 UK 约束，但是 id 为 1、2 的数据可以共存。

  id 为 1 和 3 的数据 a 和 b 的数值虽然 a 的值相同均为 1，但 b 的值分别为 NULL 和 2，所以符合 UK 约束。

  +------+------+------+
  | id   | a    | b    |
  +------+------+------+
  | 1    | 1    | NULL |
  | 2    | 1    | NULL |
  | 3    | 1    | 2    |
  ----------------------
  

• 错误代码示例 2：WHERE 条件中的列存在 NULL 值

  根据 a!=1 来筛选，仅筛选出 id 为 3 的数据，遗漏 id 为 2 的数据，因为 NULL!=1 判断为 NULL，不为布尔值 true 或 false。

  根据 a not in (1,3) 来筛选，并不能筛选出 id 为 2 的值，因为 NULL not in (1，3) 判断为 NULL，不为布尔值 true 或 false。

  mysql> select * from test_null;
  +------+------+
  | id   | a    |
  +------+------+
  |    1 |    1 |
  |    2 | NULL |
  |    3 |    3 |
  +------+------+
  
  mysql> select * from test_null where a != 1;
  +------+------+
  | id   | a    |
  +------+------+
  |    3 |    3 |
  +------+------+
  
  mysql> select * from test_null where a not in (1,3);
  Empty set
  

• 错误代码示例 3：带有 NULL 值的列进行聚合函数

  如下图，a 列中存在一条 NULL 数据，select count(*) 返回条数 3 条，select count(a) 返回条数 2 条。

  select * from test_null;
  +------+------+
  | id   | a    |
  +------+------+
  |    1 |    1 |
  |    2 | NULL |
  |    3 |    3 |
  +------+------+
  3 rows in set (0.01 sec)
  
  mysql> select count(*) from test_null;
  +----------+
  | count(*) |
  +----------+
  |        3 |
  +----------+
  1 row in set (0.02 sec)
  
  mysql> select count(a) from test_null;
  +----------+
  | count(a) |
  +----------+
  |        2 |
  +----------+
  1 row in set (0.01 sec)
  

[推荐][质量] 避免使用非唯一列进行 order by 排序

代码中禁止使用非唯一列进行 order by 排序，该行为会在 OceanBase 数据库中产生非稳定的返回结果从而影响业务。

不遵守该规约，可能导致以下问题：

• OceanBase 数据库切主后，SQL 在不同执行计划的 server 上执行返回结果存在差异。
• order by 后的列存在多行相同的值，每次执行会返回不同的结果。

建议避免使用非唯一列进行 order by 排序，如必须使用可在排序列后增加具有 unique 属性的列。

[强制&推荐][质量] BUFFER 表禁止使用不带条件的 limit 1，建议绑定 HINT

说明

Queuing 表（又称 buffer 表）是业务上因使用场景而诞生的名称，意为业务上"像使用 buffer 一样使用一张表"，即全表数据有大比例的更新或者增删。对于 buffer 表，使用中有如下两条限制：

• 【强制】禁止使用不带条件的 limit 1。
• 【推荐】建议绑定 HINT，防止执行计划抖动导致的异常。

错误代码示例

• 错误代码示例 1：未绑定 hint

  #未绑定正确 HINT
  <!-- mapped statement for IbatisAccountLogCacheDAO.findNeedCacheBackAccountNo -->
  <select id="MS-ACCOUNT-LOG-CACHE-FIND-NEED-CACHE-BACK-ACCOUNT-NO-TABLE-INDEX-OB" resultMap="RM-CACHE-BACK-ACCOUNT-CNT-OB">
  <![CDATA[
      select TRANS_ACCOUNT as account_no, count(1) as cnt
      from IW_ACCOUNT_LOG_CACHE t where (TASK_NO = (- 1)) group by trans_account
  ]]>
  </select>
  

• 错误代码示例 2：使用未带条件的 limit 1

  select * from iw_account_log_cace limit 1;
  

正确代码示例

• 正确代码示例 1：强制绑定 hint

  idx_taskno_acount_dt_gorder_logid 为 taskNo 第一顺位的索引。

  #正确绑定索引
  <!-- mapped statement for IbatisAccountLogCacheDAO.findNeedCacheBackAccountNo -->
  <select id="MS-ACCOUNT-LOG-CACHE-FIND-NEED-CACHE-BACK-ACCOUNT-NO-TABLE-INDEX-OB" resultMap="RM-CACHE-BACK-ACCOUNT-CNT-OB">
  <![CDATA[
      select /*+ INDEX(t idx_taskno_acount_dt_gorder_logid)*/ TRANS_ACCOUNT as account_no, count(1) as cnt
      from IW_ACCOUNT_LOG_CACHE t where (TASK_NO = (- 1)) group by trans_account
  ]]>
  </select>
  

• 正确代码示例 2：使用 limit 1 带有 where 条件

  select * from iw_account_log_cace where task_no = -1 limit 1;