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

プロフェッショナルな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 文字を超えることはできません。

ユーザーの命名規則

本番環境で使用されるユーザー命名形式はcod​​e_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 UPDATEREPLACE INTOINSERT IGNORE使用は固く禁じられています。

この記事は単なる出発点にすぎません。各チームには独自の開発および設計仕様があります。MySQL の開発および設計仕様はこれらに限定されません。この記事が皆さんのインスピレーションになれば幸いです。

以下もご興味があるかもしれません:
  • MYSQL データベースの命名と設計仕様
  • よくある MySQL 設計エラーをご存知ですか?

<<:  マウスで画像を動かすJavaScript

>>:  JavaScriptは行削除機能を備えたテーブルを動的に生成します

推薦する

docker pruneコマンドは、あまり使用されないデータを定期的にクリーンアップするために使用できます。

目次docker システム df docker システム プルーンNoneオブジェクトをクリーンアッ...

MySQL 5.7.9 シャットダウン構文例の詳細な説明

mysql-5.7.9 では、ついにシャットダウン構文が提供されます。以前は、MySQL データベー...

Linuxオンラインソフトウェアgccをオンラインでインストールする方法

Linux オンラインインストール関連コマンド: yum install: すべてインストールyum...

html+cssレイアウトの3つの方法(ナチュラルレイアウト/フローレイアウト/ポジショニングレイアウト)

1. 自然なレイアウト<br />レイアウトは変更せずに自動的に左揃えになります。 2....

chkconfig および systemctl コマンドを使用して Linux サービスを有効または無効にする方法

これは Linux 管理者にとって重要な (そして素晴らしい) トピックなので、誰もが Linux ...

Mysql が CPU を過剰に占有する場合の最適化方法 (必読)

Mysql が CPU を占有しすぎる場合、どこから最適化を開始すればよいでしょうか? CPU 使...

Linuxシステムはポート3306、8080などを外部に開放します。ファイアウォール設定の詳しい説明

多くの場合、Linux システムに Web サービス アプリケーション (Tomcat、Apache...

MySQL インデックス プッシュダウン (ICP) の簡単な理解と例

序文Index Condition Pushdown (ICP) は、MySQL 5.6 の新機能で...

ポータルサイトのフォーカス画像のデザインに関するいくつかの結論

フォーカス画像は、画像、テキスト、動的なインタラクティブ効果を統合したコンテンツを表示する方法です。...

JavaScript におけるシリアル操作と並列操作

目次1. はじめに2. es5メソッド3. 非同期関数のシリアル実行4. 非同期関数の並列実行5. ...

史上最も便利な Zookeeper サーバーの構築方法 (推奨)

ZooKeeperとはZooKeeper は、分散アプリケーションに効率的で可用性の高い分散調整サ...

JavaScript におけるイベント バブリング メカニズムの詳細な分析

バブリングとは何ですか? DOM イベント フローには、イベント キャプチャ ステージ、ターゲット ...

Docker クロスホストネットワークの実装 (手動)

1. Macvlan の紹介Macvlan が登場する前は、イーサネット カードに複数の IP ア...

Docker イメージのローカル Elasticsearch ポート操作へのアクセス

dockerスタックによってデプロイされたイメージサービスを使用すると、イメージを入力した後、理論的...

CSS スタイルの導入方法とその長所と短所の紹介

CSSを導入する3つの方法1. インラインスタイル利点: 書きやすく、重みがある 欠点: 構造とスタ...