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のフルスキャンは、大きなパフォーマンスの懸念をもたらし、データベースの停止を引き起こしやすいため、コード内でのフルスキャン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データベースのリーダー切り替え後、異なる実行プランを持つserver上で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の第一順位のインデックスです。

  #正しくインデックスをバインド
  <!-- 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;