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規範には2つの側面が含まれます：

• 品質：SQL実行パフォーマンスに影響を与える規範で、低レベルなSQLエラーや低パフォーマンスなSQLを減らします。規範は通常、普遍性と一般性を持ちます。例：複数の全表スキャンのSQLタイプなどです。

• スタイル：SQLの可読性に影響を与える規範で、SQLの業務シナリオの理解を容易にし、人為的なSQL問題の調査を支援します。規範は通常、業務シナリオに関連しています。例：テーブル、フィールド、インデックスの命名規則などです。

SQL規範には2種類の推奨事項が含まれます：

• 必須：遵守しなければならない規範を示します。遵守しない場合、大きな影響が生じる可能性が高いです。例：全表スキャンなどです。

• 推奨：参照することを推奨する規範を示します。遵守しない場合、影響が生じる可能性がありますが、業務シナリオ上必要です。例：複数テーブルの連携更新などです。

[必須][品質] 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によるフルテーブルスキャンは、重大なパフォーマンス上のリスクを伴い、データベースのダウンを引き起こす可能性があるため、コード内での使用を禁止します。

エラーコード例

エラー例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データが1つ存在します。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が異なる実行計画を持つサーバーで実行された場合、結果に差異が生じる。
• order by 句の列に複数行の同じ値が存在すると、実行毎に異なる結果が返される。

非一意な列を使用した order by ソートは避けることを推奨します。やむを得ない場合は、ソート列の後にunique属性を持つ列を追加することができます。

[必須＆推奨][品質] BUFFERテーブルでは条件を指定しないLIMIT 1の使用を禁止し、HINTのバインドを推奨する

説明

キューイングテーブル（bufferテーブルとも呼ばれる）は、業務上の使用シナリオに由来する名称で、「bufferのようにテーブルを使用する」という意味です。つまり、テーブル全体のデータにおいて更新や追加・削除が大部分を占める場合を指します。bufferテーブルの使用には、以下の2つの制限があります：

• 【必須】条件を指定しない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：強制バインドヒントを使用する場合

  idx_taskno_acount_dt_gorder_logidはt taskNoの第1順位のインデックスです。

  #正しくインデックスをバインドする
  <!-- 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：WHERE句を含むLIMIT 1を使用する場合

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