最も詳細な MySQ 設計および開発仕様書 [推奨コレクション]

次の記事では、MySQL の設計および開発仕様について最も詳細にまとめています。お役に立てば幸いです。

                                                                                                               

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

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

データベース オブジェクトはデータベースのコンポーネントであり、一般的なものには次のものがあります。テーブル、インデックス、ビュー、ダイアグラム、デフォルト、ルール、トリガー、ストアド プロシージャ、ユーザー待機。命名規則とは、データベース (SCHEMA)、テーブル (TABLE)、インデックス (INDEX)、制約 (CONSTRAINTS) などのデータベース オブジェクトの命名規則を指します。 [推奨事項:

mysql ビデオ チュートリアル ]

データベース オブジェクトのグローバル命名仕様

1. 命名には意味のある英単語を使用し、先頭にアンダースコアを付けます。区切り

2. 名前には、英文字で始まる英字、数字、アンダースコアのみを使用できます

3.backup、call、group などの MySQL 予約語の使用は避けてください, etc.

4. すべてのデータベース オブジェクトは小文字を使用します。実際、MySQL では大文字と小文字の区別を設定できます。統一性を確保するために、ここではすべての小文字の表現を標準化します。

#データベースの命名規則1. データベース名は 30 文字を超えてはなりません。

2. データベースの名前は通常、ライブラリの意味の略称を表すプロジェクト名になります (たとえば、IM プロジェクトのワークフロー データベースは im_flow など)。

3. データベースの作成時に、デフォルトの文字セットと照合ルール句を追加する必要があります。デフォルトの文字セットは UTF8 です (移行されたダンボは utf8mb4 を使用します)

4. 名前は小文字である必要があります。

テーブルの命名規則1. 通常のテーブル名は t_ で始まり、 t はテーブルを表し、命名規則は t module (モジュールの意味の省略形を含む) です。ユーザーモジュールの教育情報テーブルなどのテーブル（テーブルの意味の略称が含まれます）：t_user_eduinfo。

2. 一時テーブル (RD、QA、または DBA の学生が一時的なデータ処理に使用するテーブル)、命名規則: temp プレフィックス モジュール テーブルの日付サフィックス:

temp_user_eduinfo_20210719

3 、バックアップ テーブル (履歴データの保存とアーカイブ、または災害復旧データとして使用される) 命名規則、bak プレフィックス モジュール テーブル日付サフィックス:

bak_user_eduinfo_20210719

4. 同じモジュール内のすべてのテーブル同じプレフィックスを使用することができ、テーブル名はできる限り意味を表現します

5. 複数の単語はアンダースコアで区切られます_

6. 通常のテーブル名は 30 文字を超えてはなりません。 temp table と bak テーブルは状況に応じて異なりますので、できるだけ短くする必要があります。名前は小文字を使用する必要があります。

フィールドの命名仕様1。フィールドの命名が必要です。実際の意味を表す英単語または略語にする場合は、

service_ip、service_port

のようにアンダースコア _ を使用して単語を接続します。 2. テーブル間で同じ意味を持つフィールドは同じ名前にする必要があります。たとえば、テーブル a とテーブル b の両方に作成時刻があるため、create_time として統一する必要があります。矛盾すると混乱が生じます。

3. 複数の単語はアンダースコアで区切られます __

4. フィールド名は 30 文字以下で、名前は小文字にする必要があります

インデックスの命名仕様 1. 一意のインデックスは、uni フィールド名を使用して名前が付けられます:

create unique Index uni_uid on t_user_basic(uid)

。 2. idx フィールド名を使用して、一意でないインデックスに名前を付けます:

createindex idx_uname_mobile on t_user_basic(uname,mobile)

。 3. 複数の単語はアンダースコア _ で区切られます。

4. インデックス名は 50 文字を超えてはなりません。名前は小文字である必要があります。結合されたインデックス内のフィールドの数は多すぎてはなりません。そうしないと、クエリ効率の向上につながりません。

5. 複数の単語で構成される列名については、

test_contact

tablemember_id とfriend_id インデックス: idx_mid_fid。 6. 結合インデックスの左端のプレフィックスの原則を理解し、繰り返しインデックスを作成しないようにします。(a,b,c) が作成される場合、それは (a)、(a,b)、(a) を作成するのと同じです。 、b、c）。

ビューの命名規則1. ビュー名は、ビューを意味する v で始まります。完全な構造は、v ビューの内容の意味の省略形です。

2. ビューが 1 つのテーブルのみから取得されている場合、それは v テーブル名です。複数のテーブルを関連付けてビューを生成する場合は、v とアンダースコア (_) を使用して複数のテーブル名を接続します。ビュー名は 30 文字を超えてはいけません。 30文字を超える場合は略語を使用してください。

3. 開発者は、特別なニーズがない限りビューを作成することを固く禁じられています。

4. 名前は小文字にする必要があります。

ストアド プロシージャの命名規則

1. ストアド プロシージャ名は sp で始まり、ストアド プロシージャ (ストレージ プロシージャ ) を意味します。複数の単語はアンダースコア (_) で接続されます。ストアド プロシージャの機能は、その名前に反映されている必要があります。ストアド プロシージャ名は 30 文字を超えてはなりません。

2. ストアド プロシージャの入力パラメータは i_ で始まり、出力パラメータは o_ で始まります。

3. 名前は小文字にする必要があります。

create procedure sp_multi_param(in i_id bigint,in i_name varchar(32),out o_memo varchar(100))

関数の命名規則

1. 関数名は関数を意味する func で始まります。その後、複数の単語をアンダースコア (_) で接続し、その機能を関数名に反映させます。関数名は 30 文字以内にしてください。

2. 名前は小文字にする必要があります。

create function func_format_date(ctime datetime)

トリガーの命名規則

1. トリガーは trig で始まり、trigger トリガーを意味します。

2. 基本部分では、トリガーに追加するテーブルについて説明します。トリガー名は 30 文字以内にしてください。

3. サフィックス (_i、_u、_d) は、トリガー条件のトリガー方法 (挿入、更新、または削除) を示します。

4. 名前は小文字にする必要があります。

DROP TRIGGER IF EXISTS trig_attach_log_d;CREATE TRIGGER trig_attach_log_d AFTER DELETE ON t_dept FOR EACH ROW;

制約の命名規則

1. 一意の制約: uk_table name_field name。 uk は UNIQUE KEY の略称です。たとえば、次のように、部門の部門名に一意制約を追加して、名前が重複しないようにします:

ALTER TABLE t_dept ADD CONSTRAINT un_name UNIQUE(name);

2. 外部キー制約: fk_table 名に続いて、外部キーが配置されているテーブル名が続きます。キーが特定され、対応するメイン テーブル名 (t_ を除く)。子テーブル名と親テーブル名はアンダースコア (_) で区切られます。以下の通り:

ALTER TABLE t_user ADD CONSTRAINT fk_user_dept FOREIGN KEY(depno) REFERENCES t_dept (id);

3. 非 null 制約: 特別な必要がない場合は、デフォルトですべてのフィールドを非 null にし、異なるデータ型にデフォルト値を与えることをお勧めします。

1 `id` int(11) NOT NULL,2 `name` varchar(30) DEFAULT '',3 `deptId` int(11) DEFAULT ,4 `salary` float DEFAULT NULL,

4. パフォーマンス上の理由から、特別な必要がない限り、外部キーを使用しないことをお勧めします。参照整合性はコードによって制御されます。これもプログラムの観点から整合性を管理するための常套手段ですが、注意しないとダーティなデータも生成されてしまいます。

5. 名前は小文字にする必要があります。

ユーザー命名規則

1. 運用環境で使用されるユーザー命名形式は code_application

2. 読み取り専用ユーザー命名規則は read_application

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

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

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

現在のデフォルト エンジンは、「default_storage_engine」のような変数を表示することで表示できます。主に MyISAM と InnoDB があり、バージョン 5.5 以降では、デフォルトで InnoDB エンジンが使用されます。クイズの練習をするにはここをクリックしてください。

基本的な違いは次のとおりです。

MyISAM 型はトランザクション処理などの高度な処理をサポートしませんが、InnoDB 型はサポートします。 MyISAM タイプのテーブルはパフォーマンスを重視しており、その実行速度は InnoDB タイプよりも高速ですが、トランザクション サポートは提供しません。一方、InnoDB はトランザクション サポートと外部データを提供します。キーやその他の高度なデータベース機能。

文字セットの選択

1. 特別な要件がない場合は、

utf8 または utf8mb4 を使用する必要があります。

中国では、中国語とさまざまな言語を完全にサポートする

utf8 形式を選択するのが最良の方法です。MySQL は 5.5 以降、utf8mb4 エンコーディングを追加しました。 #mb4 は ほとんどのバイト 4 を意味し、4 バイトの unicode と互換性があるように特別に設計されています。 したがって、

utf8mb4

は utf8 のスーパーセットであり、エンコードを utf8mb4 に変更する以外に他の変換は必要ありません。もちろん、スペースを節約するには、通常は utf8 を使用するだけで十分です。 次のスクリプトを使用して、データベースのエンコード形式を表示できます

1 SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';2 -- 或3 SHOW VARIABLES Like '%char%';

テーブル設計仕様1. 異なるデータベース テーブルに対応するデータベース テーブル間コンポーネントに対応するテーブル間の独立性を確保し、システムまたはテーブル構造を再構築する可能性を提供するために、関連付けは可能な限り最小限に抑える必要があり、テーブルの関連付けに外部キーを使用することは許可されません。現在の業界の慣行には、一般的に参照整合性のプログラムによる制御が含まれます。

2. テーブル設計の観点から見ると、データベース設計はシステム全体ではなく、システムアーキテクチャのコンポーネントの分割に基づいて行う必要があり、データベース設計はシステムアーキテクチャに基づいて行う必要があります。各コンポーネントが処理する業務。

3. テーブルには PK が必要です。主キーの利点は、一意の識別、効果的な参照、効率的な検索であるため、一般的には主キー フィールドを設けるようにしてください。

4. フィールドは 1 つの意味のみを表します。

5. テーブルには重複する列があってはなりません。

6. 複雑なデータ型 (配列、カスタムなど) の使用は禁止されており、

Json

型の使用は状況に応じて異なります。 7.

join

を必要とするフィールド (結合キー) のデータ型は、暗黙的な変換を避けるために完全に一貫している必要があります。たとえば、関連するフィールドはすべて int 型です。 8. 設計は少なくとも 3 番目のパラダイムを満たし、データの冗長性を最小限に抑える必要があります。一部の特殊なシナリオでは非正規化設計が可能ですが、冗長フィールドの設計についてはプロジェクト レビュー時に説明する必要があります。

9、TEXT フィールドは大量のテキストとして保存されるため、独立したテーブルに配置し、PK を使用してメイン テーブルに関連付ける必要があります。特に必要がない限り、TEXT フィールドと BLOB フィールドは禁止されています。

10. 期限切れデータを定期的に削除(または移行)する必要があるテーブルは、テーブルを分割することで解決できますが、運用頻度の低い履歴データは2/8ルールに従って履歴テーブルに移行するという考え方です。 、時間に応じて または、ID を切り取りポイントとして使用します。

11. 1 つのテーブル内のフィールドの数は多すぎてはならず、最大でも 50 を超えないようにすることをお勧めします。幅が広すぎるテーブルもパフォーマンスに大きな影響を与えます。

12. MySQL が大きなテーブルを処理すると、パフォーマンスが大幅に低下し始めるため、単一テーブルの物理サイズを 16GB に制限し、テーブル内のデータ行数を制御することをお勧めします。 2000W以内。

業界のルールでは、2000 W を超えるとパフォーマンスが大幅に低下し始めるということです。ただし、この値は柔軟であり、実際の状況に基づいてテストすることができます。たとえば、Alibaba の標準は 50​​0 W ですが、Baidu の標準は確かに 2000 W です。実際、テーブルの幅が広いかどうか、および 1 行のデータが占めるスペースがすべて影響します。

13. 初期の計画でデータの量またはデータの増加が大きい場合は、設計レビュー中にテーブル分割戦略を追加する必要があります。データ分割方法を分析する特別な記事が後ほど用意されます。垂直分割 分割 (データベースの垂直分割とテーブルの垂直分割)、水平分割 (データベースの分割とデータベース内のテーブルの分割);

14. 特別な要件がない限り、パーティション テーブルの使用は固く禁止されています

フィールド設計仕様

1, INT: 特別な必要がない場合は、整数値を格納するために UNSIGNED INT 型を使用します。整数フィールドの後の数字は表示長を表します。たとえば、id int(11) NOT NULL

2, DATETIME: 時間に対して正確である必要があるすべてのフィールドには # を使用します (時、分、秒) ##DATETIME、TIMESTAMP タイプは使用しないでください。

TIMESTAMP の場合、書き込まれた時刻を現在のタイムゾーンから UTC (協定世界時) に変換して保存します。クエリを実行すると、クライアントの現在のタイムゾーンに変換されて返されます。 DATETIME の場合、変更は行われず、入力と出力は基本的に同じです。

さらに

DATETIME保存範囲も比較的広いです:

timestamp保存できる時間範囲は次のとおりです: '1970-01- 01 00: 00:01.000000」から「2038-01-19 03:14:07.999999」まで。

datetime保存できる時間の範囲は、「1000-01-01 00:00:00.000000」から「9999-12-31 23:59:59.999999」です。

ただし、タイムゾーンをまたがるビジネスなどの特殊な状況では、

TIMESTAMP の方が適しています。

3、

VARCHAR: すべての動的長文字列は VARCHAR タイプを使用します。これは、ステータスなどの限られたカテゴリのフィールドに似ています。文字列は INT などの数字に置き換えないでください。VARCHAR(N)、

N はバイト数ではなく文字数を表します。たとえば、

VARCHAR(255) は、最大 255 文字を保存できます (文字には、英字、漢字、特殊文字などが含まれます)。ただし、MySQL テーブル内のすべての VARCHAR フィールドの最大長は 65535 バイトであり、格納される文字の数は選択した文字セットによって決まるため、N はできるだけ小さくする必要があります。

UTF8 が文字を格納するのに最大 3 バイトを必要とする場合、長さが 3 バイトを占める文字を格納する場合、varchar は 21845 文字を超えてはなりません。同時に、ソートや一時テーブルの作成などのメモリ操作を実行するときに、N の長さがメモリに適用されるために使用されます。 (特別な必要がない場合、原則として、単一の

varchar 型フィールドは 255 文字を超えることはできません)

4,

TEXT: 数値が指定されている場合のみすべての MySQL データベースは UTF8 文字セットを使用するため、文字数は 20,000 を超える場合があります。その場合のみ、文字データの保存に TEXT 型を使用できます。

TEXT 型を使用するすべてのフィールドは、大きなテキスト フィールドから分離するために、元のテーブルから分割し、元のテーブルの主キーとは別に保存用の別のテーブルに形成する必要があります。 。特別な必要がない場合は、MEDIUMTEXT、TEXT、LONGTEXT タイプ

5 を使用しないでください。正確な浮動小数点データを保存するには、

DECIMAL を使用する必要がありますが、FLOAT と DOUBLE の使用は固く禁止されています。

6. 特別な必要がない場合は、

BLOB タイプを使用しないでください。

7. 特別な必要がない場合は、# を使用することをお勧めします。フィールドの ##NOT NULL

属性。NULL8 の代わりにデフォルト値を使用できます。自動インクリメント フィールド タイプは整数であり、## である必要があります。 #UNSIGNED

。推奨されるタイプは

INT または BIGINT で、自動インクリメント フィールドは主キーまたは主キーの一部である必要があります。

インデックス設計仕様

1. インデックスの区別

インデックスは、より高いインデックス選択性 (識別) を持つ列に作成する必要があり、[計算] を選択します。選択性の方法は次のとおりです:

selecttivity = count(distinct c_name)/count(*)

; 識別結果が 0.2 未満の場合、この列にインデックスを作成することはお勧めできません。 SQL の実行速度を低下させます。

2. 一番左のプレフィックス に従ってください。

对于确定需要组成组合索引的多个字段，设计时建议将选择性高的字段靠前放。使用时，组合索引的首字段，必须在where条件中，且需要按照最左前缀规则去匹配。

3、禁止使用外键，可以在程序级别来约束完整性

4、Text类型字段如果需要创建索引，必须使用前缀索引

5、单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表，应尽量少建索引，索引建立的原则理论上是多读少写的场景。

6、ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面，形成覆盖索引

7、正确理解和计算索引字段的区分度，文中有计算规则，区分度高的索引，可以快速得定位数据，区分度太低，无法有效的利用索引，可能需要扫描大量数据页，和不使用索引没什么差别。

8、正确理解和计算前缀索引的字段长度，文中有判断规则，合适的长度要保证高的区分度和最恰当的索引存储容量，只有达到最佳状态，才是保证高效率的索引。

9、联合索引注意最左匹配原则：必须按照从左到右的顺序匹配，MySQL会一直向右匹配索引直到遇到范围查询(>、between、like)然后停止匹配。

如：depno=1 and empname>'' and job=1 如果建立(depno,empname,job)顺序的索引，job是用不到索引的。

10、应需而取策略，查询记录的时候，不要一上来就使用*，只取需要的数据，可能的话尽量只利用索引覆盖，可以减少回表操作，提升效率。

11、正确判断是否使用联合索引（上面联合索引的使用那一小节有说明判断规则），也可以进一步分析到索引下推（IPC），减少回表操作，提升效率。

12、避免索引失效的原则：禁止对索引字段使用函数、运算符操作，会使索引失效。这是实际上就是需要保证索引所对应字段的”干净度“。

13、避免非必要的类型转换，字符串字段使用数值进行比较的时候会导致索引无效。

14、模糊查询’%value%’会使索引无效，变为全表扫描，因为无法判断扫描的区间，但是’value%’是可以有效利用索引。

15、索引覆盖排序字段，这样可以减少排序步骤，提升查询效率

16、尽量的扩展索引，非必要不新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

举例子：比如一个品牌表，建立的的索引如下，一个主键索引，一个唯一索引

1 PRIMARY KEY (`id`),2 UNIQUE KEY `uni_brand_define` (`app_id`,`define_id`)

当你同事业务代码中的检索语句如下的时候，应该立即警告了，即没有覆盖索引，也没按照最左前缀原则：

1 select brand_id,brand_name from  ds_brand_system where status=?  and define_id=?  and app_id=?

建议改成如下：

1 select brand_id,brand_name from  ds_brand_system where app_id=? and define_id=?  and  status=?

约束设计规范

1、PK应该是有序并且无意义的，由开发人员自定义，尽可能简短，并且是自增序列。

2、表中除PK以外,还存在唯一性约束的,可以在数据库中创建以“uk_”作为前缀的唯一约束索引。

3、PK字段不允许更新。

4、禁止创建外键约束，外键约束由程序控制。

5、如无特殊需要，所有字段必须添加非空约束，即not null。

6、如无特殊需要，所有字段必须有默认值。

SQL使用规范

select 检索的规范性

1、尽量避免使用select *，join语句使用select *可能导致只需要访问索引即可完成的查询需要回表取数。

一种是可能取出很多不需要的数据，对于宽表来说，这是灾难；一种是尽可能避免回表，因为取一些根本不需要的数据而回表导致性能低下，是很不合算。

2、严禁使用 select * from t_name ，而不加任何where条件，道理一样，这样会变成全表全字段扫描。

3、MySQL中的text类型字段存储：

3.1、不与其他普通字段存放在一起,因为读取效率低，也会影响其他轻量字段存取效率。

3.2、如果不需要text类型字段，又使用了select *，会让该执行消耗大量io，效率也很低下

4. 関連関数を使用してフィールドを抽出できますが、now()、rand()、sysdate() などは避けてください。結果が不確実な関数については、Where 条件のフィルター条件フィールドでデータ型変換関数を含む関数を使用することは固く禁止されています。多数の計算と変換は非効率を引​​き起こしますが、これはインデックスにも記載されています。

5. すべてのページング クエリ ステートメントには並べ替え条件が必要です。そうしないと、簡単に混乱が生じます。

6. in()/union を使用して or を置き換えます、効率は向上します。in の数は 300

7 未満であることに注意してください。ファジー プレフィックス クエリに % プレフィックスを使用することは固く禁止されています。例: select a,b,c from t_name where a like '%name'; 次のような % ファジー サフィックス クエリを使用できます: select a,b from t_name where a like 'name%';

8. サブクエリの使用を避け、join 操作にサブクエリを最適化できます

通常、サブクエリは in 句内にあり、サブクエリは単純な SQL (# を除く) ##union、group by、order by、limit 句) を使用すると、サブクエリを関連クエリに変換して最適化できます。

サブクエリのパフォーマンスが低下する理由:

· サブクエリの結果セットはインデックスを使用できません。通常、サブクエリの結果セットは一時テーブルに保存されます。メモリ一時テーブルにもディスク一時テーブルにもインデックスがないため、クエリのパフォーマンスはある程度影響を受けます。

· 特に比較的大きな結果セットを返すサブクエリの場合、クエリのパフォーマンスへの影響が大きくなります。

サブクエリは大量の一時テーブルを生成しますがインデックスは生成しないため、CPU リソースと IO リソースを大量に消費し、大量のテーブルが生成されます。遅いクエリの。

#操作の正規化

1. フィールド リストなしで INSERT ステートメントを使用することは禁止されています

例:

insert into 値 ('a','b','c'); insert into t_name(c1,c2,c3) 値 ('a','b', 'c'); 。

2. 大規模なバッチ書き込み操作 (

UPDATE、DELETE、INSERT) はバッチで複数回実行する必要があります

· 大規模なバッチ操作は、マスター/スレーブ間の重大な遅延を引き起こす可能性があり、特にマスター/スレーブ モードでは、slaveslave# が必要であるため、大規模なバッチ操作はマスター/スレーブ間の重大な遅延を引き起こす可能性があります。データ同期のための ##master の binlog 内のログ。

·

binlogログが row 形式の場合、大量のログが生成されます

以上が最も詳細な MySQ 設計および開発仕様書 [推奨コレクション]の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。