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：HINTのバインドを必須にする場合

  idx_taskno_acount_dt_gorder_logid は 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;