jmaxzh/sdlc-mysql-design

MySQLテーブルとSQL設計

適用条件

• タスクまたはプロジェクトがMySQLをターゲットデータベースとして確立し、データモデリング、テーブル設計、SQL、インデックス、トランザクション、または同時実行制御を含む場合に適用します。明示的な呼び出しは不要です。
• SDLCワークフローとは独立して使用します。汎用的なデータベース作業にMySQLを選択したり、他のデータベースのみに関するタスクに適用したりしないでください。

使用方法

• 公式のMySQL 8.4セマンティクスを検証基準として使用しますが、デプロイされているバージョンを制限するものではありません。違いが重要となる場合、実際のターゲットバージョンで機能サポート、制約の適用、非推奨を確認してください。
• タスクに関連するビジネス上の意味、整合性要件、主要なアクセスパターン、データ規模と成長、同時実行性、一貫性要件を確立します。利用可能な既存の設計やワークロードの証拠を使用し、重要な情報が不足している場合は前提条件とその影響を明記します。
• トランザクションと同時実行のルールをローカルのInnoDBトランザクションに適用します。トランザクション内でのクエリ、CHECK制約、外部キー制約、ストアドプロシージャの禁止は、MySQLの制限ではなくプロジェクトの規約として明示的に特定します。
• レコードは1ミリ秒あたり最大1回しか変更されないと仮定します。この仮定により、updated_atが再利用されないバージョン識別子であることが保証されるわけではありません。同時実行制御に使用する前に、タイムスタンプチェックの条件を適用してください。
• タスクに関連するルールのみを適用します。ルールの明確化が必要な場合は、MySQL設計例の該当トピックを参照してください。デフォルトですべての例を読み込まないでください。

データモデリング

エンティティ、リレーションシップ、フィールド所有権

• 行の粒度: 行が表すビジネスオブジェクト、リレーションシップ、またはイベントを定義します。テーブル内で粒度を一貫させてください。例を参照してください。

• リレーションシップと一意性: 両側の最小および最大カーディナリティを定義します。1対多の参照は多側に格納し、一意の参照を使用して1対1のリレーションシップを最大1つの一致に制限し、多対多のリレーションシップにはジャンクションテーブルを使用します。同じオブジェクトの繰り返し関連付けについては、各関連付けのビジネス上の意味、ID、一意性スコープを定義します。例を参照してください。

• フィールド所有権: 値を決定するオブジェクト、リレーションシップ、またはイベントにフィールドを配置します。現在の事実を複製しないでください。履歴スナップショットはイベント時の値として保持し、現在の値の更新によって変更されないようにします。例を参照してください。

• 参照整合性: 参照対象が存在する必要があるかどうか、および参照を作成または変更する際に満たす必要がある状態を定義します。ターゲットが削除されたり無効になったりした場合に、既存の参照がどのように処理されるかを定義します。キーと制約および同時実行制御に基づいて、ビジネス書き込みフローを通じてこれらの要件を適用します。例を参照してください。

テーブル設計

名前とコメント

• データベース、テーブル、列には小文字、数字、アンダースコアを使用して名前を付けます。数字で始めたり、予約語や不明瞭な略語を使用したりしないでください。

• インデックスには、その列や目的に基づいて名前を付けます。uniq_およびidx_は一意のインデックスと通常のインデックスを識別するために使用できます。主インデックス名はPRIMARYです。

• 列の意味、状態値、または制約が変更された場合は、列コメントと関連する定義を更新します。

データ型

• 同じ値を参照または比較するために使用される列は、データ型と符号で互換性を保ちます。可能な場合は一貫した定義を使用します。

• 正確な金額は、明示的な精度とスケールを持つDECIMAL、または定義された最小単位の整数で表現します。通貨と丸めルールを定義します。保存、パラメータ、計算を通じて正確な表現を維持します。変換前に浮動小数点で計算しないでください。

• FLOAT、DOUBLE、またはDECIMALに非推奨のUNSIGNEDを設定しないでください。

• ブール値にはis_xxxとTINYINT UNSIGNEDを使用し、アプリケーション検証で値を0または1に制限します。

• CHARを使用する場合、その末尾スペースの処理がフィールドのビジネス上の意味と一致することを確認します。

• TIMESTAMPはセッションのタイムゾーンとUTCの間で変換します。DATETIMEは変換しません。必要に応じて、元のゾーン名またはオフセットを個別に保存します。

• 作成にはcreated_at、最終更新にはupdated_atを使用します。可変のビジネスレコードには両方が必要です。追加専用レコードには作成時間のみが必要です。監査のニーズに応じて、ジャンクションテーブルやその他のテーブルにそれらを追加します。

• 監査タイムスタンプにはDATETIME(3)、ミリ秒精度、北京時間を使用し、接続タイムゾーンは+08:00とします。

• 監査タイムスタンプをNOT NULL DEFAULT CURRENT_TIMESTAMP(3)に設定し、updated_atにはON UPDATE CURRENT_TIMESTAMP(3)を追加します。データベースに管理させ、アプリケーションで冗長に割り当てないでください。

キーと制約

• InnoDBテーブルの安定した短い主キーを明示的に定義します。セカンダリインデックスレコードには主キー列が含まれるため、広い主キーはセカンダリインデックスのコストを増加させます。

• 必須フィールドにはNOT NULLをマークします。

• CHECKは使用しないでください。範囲、列挙型、フィールドの組み合わせは、すべてのアプリケーション書き込みエントリポイントで検証し、無効な書き込みを拒否します。例を参照してください。

• ビジネスの一意性は、挿入前のクエリではなく、一意のインデックスを使用して適用します。一意のインデックスは複数のNULL値を許可します。

• べき等性識別子の一意性は、該当するビジネス操作とテナントスコープ内で、参加するすべての列をNOT NULLにして適用します。

• 外部キー制約やカスケードは使用しないでください。ビジネス書き込みフローを通じて参照整合性を維持します。

文字セットと照合順序

• テキストにはutf8mb4を使用します。

• 同じ値を参照または比較するために使用されるテキスト列は、文字セットと照合順序で互換性を保ちます。可能な場合は一貫した定義を使用します。

• 照合順序を変更する前に、等価性の変更と、一意性に違反する新たな重複値を確認します。

SQLプログラミング

• ストアドプロシージャは使用しないでください。アプリケーションコードが分岐、コミット、ロールバックを制御します。

クエリ

• 安定したビジネスクエリでは必要な列を明示的に選択します。SELECT *は避けてください。

• ページネーションや単一行選択に決定論的な順序が必要な場合は、行を一意に順序付けるソートキーの組み合わせを使用します。

• すべてのソートキーをカーソルに含めます。同時のソートキー変更により、ページをまたぐ欠落/重複が発生する可能性があります。カーソルページネーションは、一貫したページをまたぐスナップショットを保証しません。

データ変更

• INSERT IGNOREやUPDATE IGNOREを使用して、失敗すべきエラーを隠蔽しないでください。

• UPDATE/DELETEが空のパラメータや動的述語の欠落により、全テーブル操作に悪化するのを防ぎます。LIMITはビジネススコープ条件の代わりにはなりません。

• 上記の監査タイムスタンプ定義では、updated_atは行の内容が変更されると自動的に変更されます。すべての更新ステートメントの実行時間を記録するわけではありません。

• 通常の更新をREPLACE INTOに置き換えないでください。主キー/一意キーの競合では、その置換セマンティクスにより、未指定のフィールドがデフォルトにリセットされ、削除/挿入動作がトリガーされる可能性があります。

式と関数

• 比較型を一致させてください。暗黙的な変換によって重要なビジネス比較が決定されないようにします。

• 長さチェックでは、バイト、文字、ユーザーが認識できる書記素を区別します。LENGTHはバイト数をカウントし、CHAR_LENGTHは文字数をカウントしますが、表示可能な書記素はカウントしません。

• NOT INで使用されるセットにNULLが含まれている場合、メンバーに一致しない値がUNKNOWNになる可能性があります。これを確認済みの非メンバーシップとして解釈しないでください。

• COUNT(DISTINCT col)はNULLを除外します。その複数列形式は、参加するすべての列が非NULLの場合にのみ、個別の組み合わせをカウントします。

• SUMは空の入力またはすべてNULLの入力に対してNULLを返します。GROUP BYがグループを形成しない場合、その行は返されません。ビジネスルールが空の集計をゼロとして定義する場合にのみ、ゼロに置き換えてください。

• 半開区間の時間間隔を優先します。開始を含み、終了を除外します。保存された値と同じ時間基準で、ビジネスタイムゾーンで境界を計算します。

トランザクションと一貫性

トランザクション管理

• トランザクション内のステートメント: クエリ、ビジネス検証、書き込み準備は明示的なトランザクション外で実行します。トランザクション内では、必要なデータ変更とトランザクション制御のみを許可します。通常のクエリ、ロッククエリ、サブクエリを含む書き込み内のクエリコンポーネントを禁止します。例を参照してください。

• トランザクション接続: 一緒に成功する必要がある書き込みは、同じ接続、同じトランザクション内で実行します。接続の切り替え、非同期実行、伝播設定はその境界を維持する必要があります。例を参照してください。

• 暗黙のコミット: ビジネス書き込みの間にSTART TRANSACTIONでネストされたトランザクションをシミュレートしたり、autocommitを0から1に切り替えたりしないでください。どちらも暗黙的にコミットします。ALTER TABLEやTRUNCATE TABLEなどのDDLは、ビジネス書き込みトランザクションの外に保ちます。例を参照してください。

• 障害処理: 必須の書き込みエラーやビジネス条件が満たされない場合、後続の書き込みを停止し、トランザクション全体をロールバックします。失敗したステートメントが以前の書き込みを元に戻すと想定しないでください。デッドロックやロックタイムアウト後は、完全なロールバックを確実に行い、安全な場合にのみ、有限の再試行制限でトランザクション全体を再試行します。不確実なコミットについては、最初にべき等性識別子を使用して確認します。例を参照してください。

• 外部操作: ローカルトランザクションは、外部呼び出しやメッセージ送信をアトミックにコミット/ロールバックしません。例を参照してください。

同時実行制御

• ビジネス条件付き更新: 書き込み時のビジネスフィールド述語を優先します。トランザクション外で読み取ったデータから新しい値を計算する場合は、関連する元の値または有効なバージョン識別子をチェックする述語を含めて、同時変更の上書きを回避します。1つの条件付きステートメントで十分な場合は、明示的なトランザクションを追加しないでください。例を参照してください。

• タイムスタンプチェック: 適切なビジネスフィールドがない場合、関連するすべての変更がupdated_atを変更し、検証中に古い値を再利用できない場合にのみ、元のupdated_atと一致させます。読み取り、書き込み、シリアル化、バインディングでミリ秒を保持します。それ以外の場合は、再利用されないバージョン識別子または別の同時実行メカニズムを使用します。例を参照してください。

• 参照整合性: 条件付き更新、一意性、および必要なトランザクションを使用して、ターゲットの削除/無効化に対する参照の作成/変更を調整します。例を参照してください。

• 更新結果: ドライバーが一致した行数または変更された行数のどちらを報告するかを判断し、必要な書き込みが成功したかどうかを評価します。満たされていない述語は成功ではありません。変更された行数がゼロであることだけでは、述語が満たされていないことを証明できません。例を参照してください。

• 書き込み順序: 競合するトランザクションは、一貫したテーブル/レコードの順序を使用する必要があります。SQLテキストの順序は、実際のロック順序を確立しません。例を参照してください。

データベースセキュリティ

• アプリケーションデータはSQLに連結するのではなく、パラメータとしてバインドします。

• 動的なテーブル/列名とソート方向は、コード許可リストを通じてSQL構造にマッピングします。値のプレースホルダーはそれらを置き換えることはできません。

パフォーマンス最適化

• リクエストラウンドトリップ: 正確性と全体的なパフォーマンスを維持しながら、ビジネス操作あたりのデータベース要求を削減します。

インデックス最適化

• インデックスの再利用と変更: 主要なクエリ全体でインデックスを再利用します。既存のパスでパフォーマンス要件を満たしている場合、新しいSQL/述語のためにインデックスを追加しないでください。マージまたは置換する前に、他のクエリと一意の制約を確認します。例を参照してください。

• カーディナリティの低いフィールド: 個別の値が少ないという理由だけでインデックスを拒否しないでください。ターゲット分散、組み合わせフィルター、順序付け、カバリングの利点、テーブルルックアップ、書き込みコストを検討します。例を参照してください。

• プレフィックスインデックス: 実際のデータから文字列プレフィックスの長さを選択します。一意のプレフィックスインデックスは、プレフィックスを共有する個別の完全な値を拒否する可能性があり、完全な値の一意性を置き換えることはできません。例を参照してください。

• 複合インデックス: フィルタリング、左プレフィックスの再利用、順序付け、グループ化のバランスを取ります。等価列は通常、範囲列の前に置きます。選択性によって普遍的に順序付けしないでください。例を参照してください。

• ソートコスト: filesortだけではインデックスが悪いことを意味しません。追加されたインデックスのソート、スキャン、メンテナンスコストを比較します。例を参照してください。

• インデックス付き列と式: 日次フィルタリングには、直接的な時間範囲述語を優先します。式が必要な場合は、該当する機能インデックスまたは生成列インデックスを評価します。例を参照してください。

• 先頭ワイルドカード: 通常のB-treeインデックスは、一般的に先頭ワイルドカード検索を効果的な範囲スキャンに変換できません。全文検索または専用検索は、元の一致セマンティクスを保持する必要があります。例を参照してください。

クエリ最適化

• バッチクエリ: マージ可能な行ごとのクエリを結合に置き換えます。

…

[翻訳のため省略]