本文では、TDSQL-C for MySQLクラスタ作成後のSQL利用規約に関するご提案をご紹介します。
データベース基本設計規範
すべての文字の保存と表示は、utf-8またはutf8mb4エンコーディングで行われ、テーブルとフィールドにはコメント情報が必要です。
大規模トランザクションの使用をできるだけ避けてください。
説明：
例えば、1つのトランザクション内で複数のselectやupdate文を実行する場合、高頻度トランザクションではMySQLの並行処理能力に深刻な影響を与えます。トランザクションが保持するロックなどのリソースは、トランザクションがrollback/commitされるまで解放されないためです。ただし、データ書き込みの一貫性についても評価する必要があります。
データベース SQL クエリ規範
ORDER BY .. LIMIT クエリを使用する際は、インデックスによるクエリ文の最適化を優先的に検討し、実行効率を向上させます。
ORDER BY、GROUP BY、DISTINCTを使用したクエリ実行時には、where条件でフィルタリングされる結果セットを1000行以内に維持してください。さもないとクエリ効率が低下します。
ORDER BY、GROUP BY、DISTINCT ステートメントを使用する際は、インデックスを優先的に利用してソート済みデータを検索します。例：where a=1 order by b の場合、key(a,b) を利用できます。
JOINを使用した結合クエリの際は、where条件で同一テーブル上のインデックスをできるだけ十分に活用してください。
説明：
例えば、select t1.a, t2.b from t1,t2 where t1.a=t2.a and t1.b=123 and t2.c=4。
t1.cとt2.cフィールドが同じ場合、t1のインデックス(b,c)はbのみ使用されます。この場合、where条件のt2.c=4をt1.c=4に変更すると、完全なインデックスを利用できます。このような状況はフィールドの冗長設計（非正規化）で発生する可能性があります。
UNION ALLの使用を推奨し、UNIONの使用を減らします。データの重複排除が必要かどうかを検討する必要があります。
UNION ALLを使用するとデータの重複排除を行わないため、ソート操作が不要な分、UNIONを使用するよりも高速です。業務で重複排除の必要がない場合は、優先的にUNION ALLを使用します。
コードでページングクエリロジックを実装する際、COUNTが0の場合は直ちに返却し、後続のページングステートメントの実行を避けてください。
ビッグデータテーブルに対するCOUNT操作は時間がかかり、通常は秒単位の応答速度となります。テーブルへの頻繁なCOUNT操作は避けてください。頻繁なテーブルカウントが必要な場合は、専用のカウント用テーブルを導入してください。
結果が1件のみ返されることが確定している場合は、limit 1を使用します。データの正確性が保証される前提で結果セットの件数が確定できる場合、可能な限りlimitクエリを活用し、結果を可能な限り迅速に返すようにしてください。
DELETEおよびUPDATE文の効率を評価する際は、該当の文をSELECT文に変更してexplainを実行できます（explainコマンドはSQLクエリ文の実行計画とパフォーマンスのボトルネック分析に役立ちます）。ただし、SELECT文の頻繁な実行はデータベースのパフォーマンス低下を招くため、explainコマンドでSQLクエリ文を分析する際はSELECT文の実行回数を最小限に抑える必要があります。SQLクエリ文の分析時には、クエリ効率とデータベースパフォーマンスを総合的に考慮し、メリットとデメリットのバランスを取った上で最適なソリューションを選択してください。
TRUNCATE TABLEはDELETEよりも高速で、システムおよびログリソースの使用量も少ないです。削除対象のテーブルにトリガーが存在せず、かつテーブル全体を削除する場合は、TRUNCATE TABLEの使用を推奨します。
説明：
TRUNCATE TABLEは削除されたデータをログファイルに書き込みません。
TRUNCATE TABLEは、WHERE句なしのDELETE文と機能的には同じです。
TRUNCATE TABLEは他のDMLと同じトランザクション内に記述することはできません。
できるだけ否定クエリの使用を避け、フルテーブルスキャンを防止してください。
説明：
否定クエリとは、NOT、!=、<>、NOT EXISTS、NOT IN、NOT LIKEなどの否定演算子を使用することを指します。否定クエリを使用すると、インデックス構造を利用した二分探索が行えず、フルテーブルスキャンしか行えません。
3つ以上のテーブルに対するJOIN結合の実行は避けてください。JOIN対象のフィールドは、データ型を一致させる必要があります。
複数のテーブルを結合するクエリでは、結合対象のフィールドにインデックスが設定されていることを確認してください。複数テーブルの結合時には、結果セットが小さいテーブルを駆動表として選択し、他のテーブルとの結合に使用します。2つのテーブルを結合する場合でも、テーブルのインデックスやSQLのパフォーマンス状況を必ず確認してください。
データベースSQL開発規範
簡単なSQLについては、分割を優先的に検討します。
説明：
OR条件の場合：f_phone='10000' or f_mobile='10000'のように、各フィールドにインデックスが設定されていても、そのうちの1つしか使用されません。2つのSQLに分割するか、union allを使用することをお勧めします。
SQLで複雑な演算やビジネスロジックを実行する必要がある場合は、ビジネス層での実装を優先的に検討します。
ページング効率を向上させるため、合理的なページング方式を使用し、データ量が多い場合にはスキップページングを使用しないでください。
説明：
例えば、以下のようなページングクエリがあります：
SELECT * FROM table1 ORDER BY ftime DESC LIMIT 10000,10;
このページング方式は大量のI/Oを引き起こします。MySQLがプリフェッチ戦略を使用しているためです。
推奨するページング方式：前回のページングの境界値を渡す方式です。
SELECT * FROM table1 WHERE ftime < last_time ORDER BY ftime DESC LIMIT 10;
トランザクション内で更新文を使用する際は、主キーまたはユニークキーに基づいて行うようにしてください。そうしないとギャップロックが発生し、内部でロック範囲が拡大され、システムパフォーマンスの低下やデッドロックを引き起こす可能性があります。
外部キーとカスケードはできるだけ使用しないでください。外部キーの概念はアプリケーション層で処理します。
説明：
例えば、学生テーブルのstudent_idが主キーである場合、成績テーブルのstudent_idは外部キーとなります。もし学生テーブルのstudent_idを更新し、同時に成績テーブルのstudent_idの更新をトリガーする場合、これはカスケード更新となります。
外部キーとカスケード更新は単一サーバーの低同時実行環境には適しますが、分散型・高同時実行クラスタには適しません。
カスケード更新は強力なブロッキング操作であり、データベース更新ストームのリスクが存在します。また、外部キーはデータベースの挿入速度に影響を与えます。
in句の使用を減らし、in句の後に続く集合の要素数は500個以内に抑えてください。
データベースとのやり取り回数を減らすため、バッチSQL文を適度に採用できます。例：INSERT INTO … VALUES (XX),(XX),(XX)....(XX); ここでXXの個数は100個以内にすることをお勧めします。
ストアドプロシージャの使用は避けてください。ストアドプロシージャはデバッグや拡張が困難であり、移植性もありません。
トリガ、イベントスケジューラ、およびビューを使用してビジネスロジックを実装することは避けてください。これらのビジネスロジックはビジネス層で処理されるべきであり、データベースへの論理的依存を避けるためです。
暗黙的な型変換の使用はお避けください。
説明：
型変換ルールは具体的に次の通りです：
1. 2つのパラメータのいずれかがNULLの場合、比較結果もNULLとなります。例外ケースとして、<=>を使用して2つのNULLを比較すると1が返されます。これらのケースでは型変換を行う必要はありません。
2. 2つのパラメータが両方とも文字列である場合、文字列として比較され、型変換は行われません。
3. 2つのパラメータが両方とも整数である場合、整数として比較され、型変換は行われません。
4. 16進数の値と非数値を比較する場合、バイナリ文字列として扱われます。
5. パラメータがTIMESTAMPまたはDATETIMEで、もう一方のパラメータが定数の場合、定数はtimestampに変換されます。
6. 片方のパラメータがdecimal型の場合、もう一方のパラメータがdecimalまたは整数であれば整数をdecimalに変換して比較します。もう一方のパラメータが浮動小数点数の場合は、decimalを浮動小数点数に変換して比較されます。
7. その他の場合、両方のパラメータは浮動小数点数に変換されてから比較されます。
8. あるインデックスがstring型に設定されている場合、このフィールドがint型の値と比較されると、上述の第7項に該当します。
例えばf_phoneがvarchar型と定義されているにもかかわらず、where句でf_phone in (098890)のように使用されると、両パラメータは浮動小数点数として扱われます。この場合、string型から変換されたfloat型のため、MySQLはインデックスを使用できず、パフォーマンス問題が発生します。
一方、f_user_id ='1234567'のようなケースでは、上述の第2項に該当し、数字を文字列として直接比較します。
ビジネス上許容される場合、トランザクションに含まれるSQL文は少なければ少ないほど良く、5つを超えないようにすることを推奨します。なぜなら、長すぎるトランザクションはロックデータを長時間保持することにつながり、MySQLの内部キャッシュや接続の過剰消費などの問題を引き起こす可能性があるためです。
自然結合（natural join）の使用はお避けください。
説明：
自然結合は結合列を明示的に定義せず暗黙的に行われるため、理解困難や移植不可能な問題が発生します。
データベースインデックス設計指針
実際のビジネスニーズに基づき、インデックス最適化を利用できないorder byクエリ文の使用を減らします。Order by、group by、distinctといった文は比較的CPUリソースを消費します。
複雑なSQL文を扱う場合、既存のインデックスを優先的に参照して設計し、explainを実行して実行計画を確認し、インデックスを活用してより多くのクエリ制限条件を追加することができます。
SELECT、UPDATE、DELETEの新規文を使用する際は、必ずexplainで実行計画のインデックス使用状況を確認し、extra列に「Using File Sort」や「Using Temporary」が表示されるのをできるだけ避けてください。実行計画でスキャン行数が1000を超えた場合、本番環境への適用可否を評価する必要があります。毎日スローログの統計分析を実施し、スローログ文の対応を行ってください。
説明：
explain 解読：
type：ALL、index、range、ref、eq_ref、const、system、NULL（左から右へ、性能が低いものから高いものへ）。
possible_keys：MySQLがテーブル内でレコードを見つけるために使用できるインデックスを示します。クエリに関連するフィールドにインデックスが存在する場合、そのインデックスがリストされますが、必ずしもクエリで使用されるとは限りません。
key：MySQLが実際に使用するキー（インデックス）を示します。インデックスが選択されていない場合、キーはNULLとなります。MySQLにpossible_keys列のインデックスを強制的に使用または無視させたい場合、クエリでFORCE INDEX、USE INDEX、またはIGNORE INDEXを使用します。
ref：インデックス列の値を検索するために使用される列または定数。
rows：テーブルの統計情報およびインデックス使用状況に基づき、必要なレコードを見つけるために読み取る必要がある行数の推定値。
Extra：
Using temporary：MySQLが結果セットを格納するために一時テーブルを使用する必要があることを示します。ソートやグループ化クエリでよく見られます。
Using filesort：MySQLでインデックスを利用して完了できないソート操作は「ファイルソート」と呼ばれます。
Using index：インデックスを使用していることを示します。「Using index」のみが表示される場合、データテーブルにアクセスせず、インデックステーブルだけでクエリが完了したことを意味し、これはカバリングインデックスのケースです。「Using where」が同時に表示される場合は、インデックスを使用してレコードを検索・読み取りしていることを示しますが、この場合もインデックスは利用可能です。ただしデータテーブルへのアクセスが必要となります。
Using where：条件付きクエリを示します。テーブルの全データを読み取らない場合、またはインデックスだけで必要なデータをすべて取得できない場合に表示されます。type列がALLまたはindexであるにもかかわらずこの情報が表示されない場合は、誤ったクエリを実行して全データを返している可能性があります。
WHERE条件の列に関数を使用すると、インデックスが無効になります。
説明：
WHERE left(name, 5) = 'zhang'の場合、left関数によりnameのインデックスが無効になります。ビジネス要件に応じてこの条件を修正し、関数を使用しないようにしてください。返される結果セットが小さい場合、アプリケーション側で条件を満たす行のフィルタリング処理を行います。