プロフェッショナルなMySQL開発設計仕様とSQL記述仕様

チーム開発のプロセスでは、プロジェクトの安定性、コードの効率性、管理の利便性のために、内部開発および設計仕様を策定することが重要です。

ここでは、テーブル設計仕様、フィールド設計仕様、SQL 記述仕様など、MySQL 開発設計仕様のリストを共有します。

データベースオブジェクトの命名規則

データベースオブジェクト

命名標準の対象は、データベース SCHEMA、テーブル TABLE、インデックス INDEX、制約 CONSTRAINTS などの命名規則を指します。

データベースオブジェクトの命名原則

名前には意味のある英語の単語を使用し、単語をアンダースコアで区切る
名前には英語の文字、数字、アンダースコアのみ使用できます
call、group などの MySQL 予約語の使用は避けてください。
すべてのデータベースオブジェクトは小文字を使用します

データベースの命名規則

データベース名は 30 文字を超えることはできません。
データベース名はプロジェクトの英語名または意味のある略語である必要があります
データベースを作成するときに、デフォルトの文字セットと照合句を追加する必要があります。デフォルトの文字セットは UTF8 です (移行されたダンボは utf8mb4 を使用します)
名前は小文字にしてください

テーブルの命名規則

同じモジュール内のテーブルでは、可能な限り同じプレフィックスを使用し、テーブル名はできるだけ表現力豊かなものにする必要があります。
複数の単語はアンダースコア (_) で区切られます。
テーブル名は30文字を超えることはできません
通常のテーブル名は、テーブルを意味するt_で始まります。命名規則は、t_モジュール名（または意味のある略語）_+テーブル名です。
一時テーブル (オペレーション、開発、またはデータベース担当者がデータ収集のために一時的に使用する中間テーブル) 命名規則: tmp プレフィックスと 8 桁の時刻サフィックスを追加します (tmp_test_user_20181109)
バックアップテーブル (DBA バックアップは履歴データを保存するための中間テーブルとして使用されます) の命名規則: bak プレフィックスと 8 桁の時刻サフィックスを追加します (bak_test_user_20181109)
名前は小文字にしてください

フィールドの命名規則

フィールド名は、実際の意味を表す英語の単語または略語である必要があり、単語はアンダースコア (_) で接続する必要があります。
テーブル間で同じ意味を持つフィールドには同じ名前が必要です
フィールド名は 30 文字を超えることはできません。

ユーザーの命名規則

本番環境で使用されるユーザー命名形式はcode_applicationです。
読み取り専用ユーザーの命名規則はread_applicationです。

データベースオブジェクトの設計仕様

ストレージエンジンの選択

特別な要件がない場合は、InnoDBストレージエンジンを使用する必要があります。

文字セットの選択

特別な要件がない場合は、utf8またはutf8mb4を使用する必要があります。

テーブル設計仕様

異なるアプリケーションに対応するデータベーステーブル間の関連付けは、可能な限り減らす必要があります。テーブルを関連付けるために外部キーを使用することはできません。これにより、コンポーネントに対応するテーブルの独立性が確保され、システムまたはテーブル構造を再構築できるようになります。
テーブル設計はシステム全体に対して行うのではなく、システムアーキテクチャ内のコンポーネントと各コンポーネントが扱う業務に基づいて行う必要があります。
テーブルにはPKが必要です
フィールドには1つの意味しかありません
テーブルには重複した列があってはなりません
複雑なデータ型（配列、カスタムなど）は許可されません
結合する必要があるフィールド（結合キー）のデータ型は、暗黙的な変換を避けるために完全に一貫している必要があります。
設計では、少なくとも第 3 正規形を満たし、データの冗長性を最小限に抑える必要があります。いくつかの特別なシナリオでは非正規化設計が許可されますが、冗長フィールドの設計はプロジェクトレビュー中に説明する必要があります。
TEXT フィールドは別のテーブルに配置し、PK を使用してメインテーブルに関連付ける必要があります。特別な必要がない限り、TEXT フィールドと BLOB フィールドは禁止されます。
期限切れのデータを定期的に削除（または転送）する必要があるテーブルは、テーブルを分割することで解決できます。
1 つのテーブル内のフィールドの数は多すぎないようにしてください。最大数は 50 を超えないようにすることをお勧めします。
MySQL が大きなテーブルを処理すると、パフォーマンスが大幅に低下し始めるため、1 つのテーブルの物理サイズを 16 GB に制限し、テーブル内のデータを 2000 万以内に制御することをお勧めします。
初期計画時にデータ量またはデータ増加が大きい場合は、テーブル分割戦略を設計レビューに含める必要があります。
特別な要件がない限り、パーティションテーブルの使用は固く禁じられています

フィールド設計仕様

INT: 特別な必要がない場合は、整数を格納するために UNSIGNED INT 型を使用します。整数フィールドの後の数字は表示の長さを表します
DATETIME: 時間 (時間、分、秒) の正確さが求められるすべてのフィールドでは、TIMESTAMP 型ではなく DATETIME 型を使用する必要があります。
VARCHAR: すべての動的長さの文字列は VARCHAR 型を使用します。ステータスなどの限定カテゴリのフィールドも、実際の意味を明確に表現できる文字列を使用し、INT; VARCHAR(N) などの数値に置き換えないでください。N はバイト数ではなく文字数を表します。たとえば、VARCHAR(255) は最大 255 文字 (英語の文字、中国語の文字、特殊文字などを含む) を保存できます。ただし、MySQL テーブル内のすべての VARCHAR フィールドの最大長は 65535 バイトであり、保存される文字数は選択した文字セットによって決まるため、N はできるだけ小さくする必要があります。たとえば、UTF8 では文字を格納するのに最大 3 バイトが必要なので、3 バイトを占める文字を格納する場合、varchar は 21845 文字を超えてはなりません。同時に、ソートや一時テーブルの作成などのメモリ操作を実行するときに、N の長さを使用してメモリを要求します。 (特別な必要がない限り、原則として単一のvarcharフィールドに255文字以上を含めることはできません)
TEXT: すべての MySQL データベースは UTF8 文字セットを使用するため、TEXT 型は文字数が 20,000 を超える可能性がある場合にのみ文字データの保存に使用できます。 TEXT タイプを使用するすべてのフィールドは、元のテーブルから分離し、元のテーブルの主キーとともに別のテーブルに保存する必要があります。特別な必要がない限り、開発者は MEDIUMTEXT、TEXT、および LONGTEXT 型を使用することを固く禁じられています。
正確な浮動小数点データを保存するには、DECIMAL を使用する必要があり、FLOAT と DOUBLE は固く禁止されています。
特別な必要がない限り、開発者は BLOB 型を使用することを固く禁じられています。
特別な必要がない場合は、フィールドにNOT NULL属性を使用することをお勧めします。NULLの代わりにデフォルト値を使用できます。
自動インクリメントフィールドの型は整数で、UNSIGNED である必要があります。推奨される型は INT または BIGINT です。自動インクリメントフィールドは、主キーまたは主キーの一部である必要があります。

インデックス設計仕様

インデックスは、選択性の高い列に作成する必要があります。選択性は次のように計算されます: select count(distinct(col_name))/count(*) from tb_name;結果が 0.2 未満の場合、この列にインデックスを作成することは推奨されません。そうでない場合、SQL の実行速度が低下する可能性が高くなります。
複合インデックスの最初のフィールドは、where 条件に含める必要があります。複合インデックスを形成する必要がある複数のフィールドの場合は、選択性の高いフィールドを先頭に配置することをお勧めします。
外部キーは許可されません
テキストタイプのフィールドにインデックスを作成する必要がある場合は、プレフィックスインデックスを使用する必要があります。
理論的には、1 つのテーブルのインデックスの数は 5 以内に制御される必要があります。大量のテーブルを頻繁に挿入または更新する場合は、作成するインデックスの数をできるだけ少なくする必要があります。
カバーインデックスを形成するには、インデックスの後に ORDER BY、GROUP BY、および DISTINCT フィールドを追加する必要があります。
他のタイプのインデックスの代わりに Btree インデックスを使用するようにしてください。

制約設計仕様

PK は順序付けされ、意味を持たず、開発者が可能な限りカスタマイズし、可能な限り短くし、自動増分シーケンスを使用する必要があります。
テーブルに PK に加えて一意制約がある場合は、データベースにプレフィックスとして「uidx_」を使用して一意制約インデックスを作成できます。
PK フィールドは更新できません。
外部キー制約の作成は禁止されています。外部キー制約はアプリケーションによって制御されます。
特に要求されない限り、すべてのフィールドには null 以外の制約、つまりnot null制約を追加する必要があります。
特に要求されない限り、すべてのフィールドにはデフォルト値が必要です。

SQL ライティング標準

select *使用は避けてください。結合ステートメントでselect *を使用すると、インデックスにアクセスするだけで完了するクエリが、テーブルに戻ってデータを取得する必要が生じる可能性があります。
where条件なしでselect * from tableを使用することは固く禁じられています。
MySQL でテキスト型フィールドを格納する場合、他の共通フィールド型のフィールドで構成されるレコードと一緒に格納されず、読み取り効率自体も共通フィールドブロックほど良くありません。テキストフィールドを取得する必要がなく、 select *を使用する場合、同じ機能を完了する SQL はより多くの IO を消費し、追加された部分の IO 効率も低下します。
抽出されたフィールドでは関連関数を使用できますが、 now() 、 rand() 、 sysdate() 、 current_user()などの結果が不確実な関数の使用は避けてください。Where 条件のフィルター条件フィールドでは、データ型変換関数を含むすべての関数を使用することは固く禁じられています。
接続されたすべての SQL ステートメントはJoin ... On ...メソッドを使用して接続する必要があり、通常の Where 条件メソッドを使用して直接接続することはできません。外部結合 SQL ステートメントの場合、 Left Join Onの Join メソッドを使用できます。また、すべての外部結合はRight JoinではなくLeft Joinとして記述する必要があります。
アプリケーションがデータをランダムに表示するために並べ替えを使用しないことを明示的に要求しない限り、すべてのページングクエリステートメントには並べ替え条件が必要です。
WHERE 条件では、インデックス列に対する数学的または関数的な演算は固く禁止されています。
or をin() / unionに置き換え、 in の数が 300 未満であることを確認します。
ファジープレフィックスクエリに % プレフィックスを使用することは固く禁じられています。例: select id,val from table where val like '%name';ただし、% ファジーサフィックスクエリは使用できます。例: select id,val from table where val like 'name%'
INSERT ON DUPLICATE KEY UPDATE 、 REPLACE INTO 、 INSERT IGNORE使用は固く禁じられています。