史上最も包括的な MySQL 使用仕様の共有

[関連する学習の推奨事項: mysql チュートリアル ]

最近、データベース関連の操作が多く含まれており、会社の既存の仕様は次のとおりです。あまり包括的ではありません。インターネット上のさまざまな専門家の関連基準に従って、私自身の使用のためにいくつかの標準的な使用方法をまとめました。修正していただければ幸いです。

#データベース環境

dev: 開発環境

test: テスト環境

online: オンライン環境

#命名規則

• には英文字、数字、アンダースコアのみを使用でき、英文字で始まります。
• ライブラリ、テーブル、フィールドにはすべて小文字を使用し、キャメルケースの名前付けは使用しないでください。
• ORACLE および MySQL の予約語 (desc など) やキーワード (index など) は使用しないでください。
• 名前は 32 文字を超えることはできません。名前の意味を理解する必要があります。動詞の代わりに名詞を使用することをお勧めします。
• データベースとデータ テーブルでは接頭辞を使用する必要があります
• 一時データベースとテーブルの名前は、プレフィックスである tmp で始まり、日付がサフィックスである必要があります。
• バックアップ ライブラリとテーブルは、プレフィックスとして bak であり、日付がサフィックスである必要があります
• # #すべてのライブラリ、テーブル、フィールドが小文字で使用されるのはなぜですか?

MySQL では、データベースとテーブルはディレクトリとその配下のファイルに対応します。したがって、オペレーティング システムの区別によって、データベースとテーブルの名前付けの大文字と小文字の区別が決まります。

Windows では大文字と小文字は区別されません。

Linux における大文字と小文字の規則
• データベース名とテーブル名は厳密に大文字と小文字が区別されます。
• テーブルの別名は厳密に大文字と小文字が区別されます。
• 列名と列の別名は、どのような場合でも大文字と小文字が区別されません。
• 変数名も厳密に大文字と小文字が区別されます。
• キャメル ケースの名前付けが設定されている場合、問題を解決するにはどうすればよいですか? MySQL 設定ファイル my.ini に lower_case_table_names = 1 を追加する必要があります。
• 
  
テーブルの名前付け

同じモジュール内のテーブルには、できる限り同じプレフィックスを使用し、テーブル名は次のような意味を表現する必要があります。できるだけ。すべてのログ テーブルは log で始まります__

フィールド名

実際の意味を表す英語の単語または略語。ブール値の意味を持つフィールドには、is_ という接頭辞が付けられ、その後に動詞の過去分詞が続きます。

テーブル間で同じ意味を持つフィールドには同じ名前を付ける必要があります。テーブル間で同じ意味を持つフィールドには、テーブル名_フィールド名からモジュールのプレフィックスを除いた名前が付けられます。
• 外部キー フィールドは、テーブル名_フィールド名を使用してその関連付けを示します。
• テーブルの主キーは自動インクリメント型の id であることが一般的であり、他のテーブルの外部キーは xxx_id の形式で表現されます。
• インデックスの名前付け

一意でないインデックスには、「idx_フィールド名_フィールド名[_フィールド名]」に従って名前を付ける必要があります

一意のインデックスは、「uniq_field name_field name[_field name]」に従って名前を付ける必要があります。
• 
  
制約の名前付け

#主キー制約: pk_テーブル名。

一意の制約: uk_table name_field name。 (アプリケーションには同時に一意性チェック ロジックが必要です。)
• 
  #テーブル設計仕様

テーブル エンジンは実際のアプリケーションに依存します。シナリオ、ログとレポート クラス テーブルには myisam を使用し、トランザクション、監査、および金額に関連するテーブルには innodb エンジンを使用することをお勧めします。説明がない場合、テーブルの作成時に innodb エンジンが使用されます デフォルトの使用は utf8mb4 文字セットで、データベースの並べ替えルールは utf8mb4_general_ci を使用します (データベース定義ではデフォルトが使用されるため、データ テーブルはを定義することはできなくなりましたが、保険のために、文字セットが utf8 ではなく、ソート ルールが utf8_general_ci

説明がない場合、テーブルには create_time フィールドとmodify_timeフィールドが含まれている必要があります。つまり、テーブルには作成時間と変更時間を記録するフィールドが含まれている必要があります。

説明がない場合、テーブルはデータが削除されたかどうかを示すために使用される is_del が含まれている必要があります。データベース データの物理的な削除は原則として許可されません。

• フィールドにデータを保存するために使用する記憶域スペースはできるだけ少なくしてください
• int を使用できる場合は、char または varchar を使用しないでください
• 可能であればtinyint を使用してください。int は使用しないでください。
• UNSIGNED を使用して、負でない値を格納します。
#ENUM 型と SET 型の使用は推奨されません。代わりに TINYINT を使用してください。
• 短いデータ型を使用します。たとえば、値の範囲が 0 ～ 80 の場合は、TINYINT UNSIGNED を使用します
• 格納精度 浮動小数点数では、FLOAT および DOUBLE ではなく DECIMAL を使用する必要があります。
• 特殊な状況を除き、時刻フィールドには int を使用して unix_timestamp を記録します。
• YEAR 型を使用して年を格納します。
• 日付を保存するには DATE 型を使用します。
• TIMESTAMP は 4 バイトを使用し、DATETIME は 8 バイトを使用するため、時刻 (秒単位の精度) を保存するには TIMESTAMP タイプを使用することをお勧めします。
• IPV4 を保存するには INT UNSIGNED を使用することをお勧めします。
• TEXT 型や BLOB 型はできるだけ使用しないでください。
• 画像やファイルなどをデータベースに保存するために VARBINARY 型や BLOB 型を使用することは禁止されています。他の保存方法 (TFS/SFS) を使用することをお勧めします。MySQL はポインタ情報のみを保存します。
• 単一レコードのサイズは 8k (列長 (中国語)_3 (UTF8) 列長 (英語)_1) を超えることは禁止されています。

と timestamp はどう違いますか？

類似点:

差異:

4 バイトのストレージ、時間範囲: 1970-01-01 08:00:01 ~ 2038- 01-19 11:14:07 値は UTC 形式で保存されますが、これにはタイム ゾーンの変換が必要です。保存時に現在のタイム ゾーンが変換され、取得時に現在のタイム ゾーンに逆変換されます。
• 日時は 8 バイトで保存され、時間範囲は 1000-01-01 00:00:00 ～ 9999-12-31 23:59:59
• 実際の形式で保存され、時間とは関係ありません。ゾーン
• 
  

#TIMESTAMP の自動割り当て属性を使用するにはどうすればよいですか? 現在時刻を ts のデフォルト値として設定します: ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP。行が更新されると、ts の値を更新します: ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP。

1 と 2 を組み合わせることができます: ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。

INT UNSIGNED を使用して IP を保存するにはどうすればよいですか?
char(15) の代わりに INT UNSIGNED を使用して ipv4 アドレスを保存し、MySQL 関数 inet_ntoa および inet_aton を通じて変換します。現在、IPv6 アドレスには変換機能がないため、DECIMAL または 2 つの bigINT を使用して格納する必要があります。

• データベースにプレーン テキストのパスワードを保存することは禁止されています
• コメントがない場合、is_hot や is_deleted などのすべてのフィールド ブール型フィールドはデフォルト値を設定し、0 に設定する必要があります;
• コメントがない場合、並べ替えフィールド order_id が配置されますプログラムではデフォルトで降順;
• プラスチック定義 長さは追加されません。たとえば、INT[4]
• 
  

の代わりに INT が使用されます。 ##INT[M].M 値は何を意味しますか? 数値型括弧の後の数字は幅を示すだけであり、格納範囲とは関係がないことに注意してください。 INT(4) と INT(10) の値の範囲は、それぞれ (-9999 ～ 9999) と (-9999999999 ～ 9999999999) であると考えている人が多いですが、これは間違いです。実際、整数の M 値を ZEROFILL 属性と組み合わせて使用​​すると、列の値の幅を同じにすることができます。 INT[M] の M の値がどのようなものであっても、その値の範囲は (符号付きの場合は -2147483648 ～ 2147483647)、(符号なしの場合は 0 ～ 4294967295) です。

表示幅は、列内に保存できる値の範囲を制限しません。また、列の指定された幅を超える値の表示を制限しません。オプションの拡張属性 ZEROFILL と組み合わせて使用​​すると、デフォルトの補助スペースはゼロに置き換えられます。例: INT(5) ZEROFILL として宣言された列の場合、値 4 は 00004 として取得されます。表示幅を超える値を整数列に格納すると、MySQL は複雑な結合用の一時テーブルを生成するときに問題が発生することに注意してください。これは、このような場合、MySQL はデータが元の列幅に収まると考えるためです。数値カラムの ZEROFILL、MySQL カラムに UNSIGNED 属性を自動的に追加します。

VARBINARY を使用して、大文字と小文字を区別する可変長文字列を格納する

CHAR を使用する場合と VARCHAR を使用する場合は何ですか? CHAR 型と VARCHAR 型は似ていますが、保存と取得の方法が異なります。また、最大長と末尾のスペースが保持されるかどうかという点でも異なります。 CHAR 型および VARCHAR 型に対して宣言された長さは、保存する最大文字数を表します。たとえば、CHAR(30) は 30 文字を占めることができます。

CHAR 列の長さは、テーブルの作成時に宣言された長さに固定されます。長さは 0 ～ 255 の任意の値にすることができます。 CHAR 値を保存するときは、指定された長さになるまで右側にスペースを埋め込みます。 CHAR 値を取得すると、末尾のスペースが削除されます。保存または取得中に大文字と小文字の変換は実行されません。

VARCHAR 列の値は可変長文字列です。長さは 0 ～ 65,535 の値で指定できます。 (VARCHAR の最大有効長は、最大行サイズと使用される文字セットによって決まります。全体の最大長は 65,532 バイトです)。 CHAR と比較すると、VARCHAR 値は、必要な文字数と、長さを記録するための 1 バイトのみを保存します (宣言された列の長さが 255 を超える場合は、2 バイトが使用されます)。 VARCHAR 値はパディングなしで保存されます。標準 SQL に準拠して、値の保存および取得時に末尾のスペースが保持されます。

char は、ユーザー パスワードの MD5 ハッシュ値を保存するのに適しており、その長さは常に同じです。また、固定長の行は断片化が起こりにくいため、頻繁に変更される値の場合は varchar よりも char の方が適しており、非常に短い列の場合は varchar よりも char の方が効率的です。 char(1) 文字列は、シングルバイト文字セットの場合 1 バイトのみを占有しますが、varchar(1) 文字列は 2 バイトを占有します。これは、1 バイトが長さ情報の格納に使用されるためです。

インデックス設計仕様

MySQL のクエリ速度は適切なインデックス設計に依存するため、インデックスは高いパフォーマンスを実現するために重要です。適切なインデックスを使用するとクエリの速度が向上します (UPDATE および DELETE の速度を含みます。MySQL は行を含むページをメモリにロードしてから UPDATE または DELETE 操作を実行します)。一方、不合理なインデックスを使用すると速度が低下します。 MySQL のインデックス検索は、新華辞典のピンインと部首の検索に似ており、ピンインと部首のインデックスが存在しない場合は、ページをめくるしか検索できません。 MySQL クエリでインデックスを使用できない場合、MySQL はテーブル全体のスキャンを実行し、大量の IO を消費します。インデックスの目的: 重複排除、位置決めの高速化、ソートの回避、インデックスの上書き。

カバリング インデックスとは

InnoDB ストレージ エンジンでは、セカンダリ インデックス (非主キー インデックス) は行アドレスを直接格納せず、主キーの値を格納します。 。ユーザーがセカンダリ インデックスに含まれていないデータ列をクエリする必要がある場合は、まずセカンダリ インデックスを通じて主キー値を検索し、次に主キーを通じて他のデータ列をクエリする必要があります。 2回質問される。カバリング インデックスの概念は、クエリはインデックス内で完了でき、カバリング インデックスの方が効率的であり、主キー クエリは自然なカバリング インデックスであるということです。インデックスを合理的に作成し、クエリ ステートメントを合理的に使用すると、カバリング インデックスを使用する場合のパフォーマンスが向上します。たとえば、SELECT email,uid FROM user_email WHERE uid=xx の場合、uid が主キーでない場合は、パフォーマンスを向上させるために適切な場合にインデックスをindex(uid,email) として追加できます。

インデックスの基本仕様

• インデックス数の制御、1 つのテーブル内のインデックスの数は 5 を超えないこと、および 1 つのテーブル内のフィールドの数インデックスは 5 を超えてはなりません。
• データ密度と分散の包括的な評価
• クエリと更新率を考慮する
  

なぜインデックスが多すぎてはいけないのかテーブル？

InnoDB のセカンダリ インデックスはストレージに b ツリーを使用するため、UPDATE、DELETE、INSERT 時に b ツリーを調整する必要があり、インデックスが多すぎると更新速度が遅くなります。

文字列にはプレフィックス インデックスを使用します。プレフィックス インデックスの長さは 8 文字を超えてはなりません。プレフィックス インデックスを優先することをお勧めします。必要に応じて、疑似列を追加してインデックスを作成できます。

BLOB/テキストおよびその他のフィールドのインデックスを作成しないでください。また、大きなフィールドのインデックスを作成しないでください。インデックスが多くの記憶領域を占有することになります。

プレフィックス インデックスとは何ですか?

率直に言うと、プレフィックス インデックスはテキストの最初の数文字 (具体的には、インデックスの作成時に指定された文字数) にインデックスを付けるため、作成されるインデックスが小さくなります。のほうが速いです。プレフィックス インデックスを使用すると、インデックス ファイルのサイズが効果的に削減され、インデックス作成速度が向上します。ただし、プレフィックス インデックスには欠点もあります。MySQL では、ORDER BY または GROUP BY でプレフィックス インデックスを使用できず、カバー インデックスとしても使用できません。

プレフィックス インデックスを確立するための構文: ALTER TABLE table_name ADD KEY(column_name(prefix_length));

主キーのガイドライン

• テーブルには主キーが必要です
• 頻繁に更新される列を使用しないでください
• 文字列列を選択しないようにしてください
• UUID MD5 HASHを使用しないでください
• 非-デフォルトでは空の一意のキー
• 自動インクリメントまたは発行者を選択することをお勧めします
  

DELETE ステートメントの UPDATE、WHERE 条件列
• ORDER BY、GROUP BY、および DISTINCT のフィールド
• 複数テーブル JOIN のフィールド
• 
  

最大の差別化フィールドを前面に配置します

• インデックスは左プレフィックスの原則に基づいています。結合インデックス (a,b,c) が確立されると、クエリ条件は (a) または (a,b) または (a,b,c) を含む場合にのみ使用できます。インデックスを使用する場合、(a, c) は列 a のインデックスを使用できるのは (a, c) を条件として使用しているため、このとき a の戻り列が多すぎないように注意する必要があります。そうしないとステートメント設計に無理が生じ、(b, c) はインデックスを使用できなくなります。
##合理的に結合インデックスを作成します (冗長性を避けるため)。(a,b,c) は (a)、(a,b)、(a,b,c) と同等です
• 
  #インデックスタブ

• 「gender」などのカーディナリティの低い列にインデックスを作成しないでください
• インデックス付きの列に対して数学演算や関数演算を実行しないでください
• 一般的に使用される小さなテーブルにインデックスを付けないでください
• 外部キーは使用しないようにしてください
• 外部キーは参照整合性を保護するために使用され、ビジネス側で実装できます
• 親テーブルと子テーブルに対する操作相互に影響し、可用性が低下します
## オンライン DDL に関する INNODB 独自の制限
• 
  

##合計MYISAM ストレージ エンジンのインデックスの長さは 1000 バイトを超えることはできません BLOB および TEXT タイプの列はプレフィックス インデックスのみを作成できます

等しくない (!= または <> を使用する場合) )、MYSQL はインデックスを使用できません。
関数操作 (abs (column) など) を使用してフィールドをフィルタリングした後、MYSQL はインデックスを使用できません。
JOIN ステートメント内の結合条件フィールドのタイプが一致しない場合、MYSQL はインデックスを使用できません。
LIKE 操作を使用する場合、条件がワイルドカード (「�c…」など) で始まる場合、MYSQL はインデックスを使用できません。索引。
非同等のクエリを使用する場合、MYSQL はハッシュ インデックスを使用できません。

プリコンパイルされたステートメントを使用します

パラメータのみを渡します。SQL を渡すよりも効率的です。ステートメント

解析は 1 回で、複数回使用します
• SQL インジェクションの可能性を低減します
• 
  
暗黙的な変換を回避します

インデックスの失敗の原因になります。

プレフィックス インデックスを最大限に活用してください

左端のプレフィックスである必要があります

2 つ使用することはできません同時に範囲条件を指定します
• 「�」などの % 先行クエリを使用しないでください。
• 
  
not in などの否定クエリを使用しないでください。 /like

インデックスを使用できないため、テーブル全体のスキャンが行われます

テーブル全体のスキャンにより、バッファ プールの使用率が低下します
• 
  #ストアド プロシージャやトリガー、UDF、イベントなどの使用を避けます。

データベースに最適な機能を実行させます

• 未解決のバグを避ける
• 
  大きなテーブルの JOIN の使用を避ける

MySQL が最も優れているのは、単一テーブルのプライマリ キー/セカンダリ インデックス クエリJOIN より多くのメモリを消費し、一時テーブルを生成します

データベースでの数学的演算を回避します

MySQL は数学的演算や論理的判断が苦手です。

• 
  データベースとのやり取りの回数を減らします。

INSERT … ON DUPLICATE KEY UPDATE

• UPDATE … WHERE ID IN(10,20,50, …)
• ページングの合理的な使用

ページングによって表示されるページ数を制限します。遅延関連付けを使用すると、前のページと次のページのみをクリックできます

次のようなページング ステートメントがあるとします。 SELECT * FROM table ORDER BY id LIMIT 10000, 10 MySQL で LIMIT OFFSET が処理される方法は、OFFSET LIMIT のデータをすべて取り出すことであるためです。 、次にOFFSETを削除し、一番下のLIMITに戻ります。したがって、OFFSET 値が大きい場合、MySQL のクエリ パフォーマンスは非常に低くなります。 id > n: id ​​> n を使用する方法には制限がありますが、id が連続しない問題については、最後の id を同時に渡すことで解決できます。ページをめくる。

http://example.com/page.php?last=100 
select * from table where id<100 order by id desc limit 10 
//上一页 
 http://example.com/page.php?first=110 
select * from table where id>110 order by id desc limit 10

この方法の最大の欠点は、閲覧中に挿入/削除操作がある場合、ページめくりが更新されず、総ページ数が新しいカウントに基づいて計算される可能性があることです( *)、最後に、一部のレコードはアクセスできない可能性があります。この問題を解決するには、引き続き現在のページ番号を導入し、最後のページめくり以降のレコードの総数に影響を与える挿入/削除操作があるかどうかを確認し、それらをキャッシュします。

select * from table where id >= (select id from table order by id limit #offset#, 1)

• 一時テーブルの生成を避けるために EXPLAIN 診断を使用してください
• EXPLAIN ステートメント(MySQL クライアントで実行される) MySQL が SELECT ステートメントを実行する方法に関する情報を取得できます。 SELECT ステートメントで EXPLAIN を実行すると、SELECT ステートメントの実行時に MySQL がインデックス、フル テーブル スキャン、一時テーブル、ソート、その他の情報を使用するかどうかを知ることができます。 MySQL がテーブル全体のスキャン、一時テーブルの使用、ソートなどを実行しないようにしてください。詳細については公式ドキュメントを参照してください。
• union の代わりに Union all を使用します
• union all と Union の違いは何ですか?
• union キーワードと Union all キーワードはどちらも 2 つの結果セットを 1 つにマージしますが、使用方法と効率の点で異なります。
  
Union はテーブルのリンク後に重複レコードをフィルターで除外するため、テーブルのリンク後、生成された結果セットを並べ替え、重複レコードを削除して、結果を返します。例:

select * from test_union1 
union select * from test_union2

この SQL は、実行時に最初に 2 つのテーブルの結果を取り出し、次にソート スペースを使用して重複レコードを並べ替えて削除し、最後に結果セットを返します。テーブル データが大きい場合は、ディスクが処理に使用される可能性があります。

そして、union all は 2 つの結果を単純に結合して返します。このように、返された 2 つの結果セットに重複データがある場合、返された結果セットには重複データが含まれます。

从效率上说，union all要比union快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用union all，如下：

select * from test_union1 union all select * from test_union2

•  程序应有捕获SQL异常的处理机制
• 禁止单条SQL语句同时更新多个表
• 不使用select * ，SELECT语句只获取需要的字段
• 消耗CPU和IO、消耗网络带宽
• 无法使用覆盖索引
• 减少表结构变更带来的影响
• 因为大，select/join 可能生成临时表
• UPDATE、DELETE语句不使用LIMIT
• INSERT语句必须显式的指明字段名称，不使用INSERT INTO table()
• INSERT语句使用batch提交（INSERT INTO table VALUES(),(),()……），values的个数不超过500
• 统计表中记录数时使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1) 备注：仅针对Myisam
• 数据更新建议使用二级索引先查询出主键，再根据主键进行数据更新
• 禁止使用跨库查询
• 禁止使用子查询，建议将子查询转换成关联查询
• 针对varchar类型字段的程序处理，请验证用户输入，不要超出其预设的长度；
  

分表规范

单表一到两年内数据量超过500w或数据容量超过10G考虑分表，需提前考虑历史数据迁移或应用自行删除历史数据，采用等量均衡分表或根据业务规则分表均可。要分表的数据表必须与DBA商量分表策略

• 用HASH进行散表，表名后缀使用十进制数，下标从0开始
• 按日期时间分表需符合YYYY[MM][dd][HH]格式
• 采用合适的分库分表策略。例如千库十表、十库百表等
• 禁止使用分区表，分区表对分区键有严格要，分区表在表变大后执行DDL、SHARDING、单表恢复等都变得更加困难。
• 拆分大字段和访问频率低的字段，分离冷热数据
  

行为规范

• 批量导入、导出数据必须提前通知DBA协助观察
• 禁止在线上从库执行后台管理和统计类查询
• 禁止有super权限的应用程序账号存在
• 产品出现非数据库导致的故障时及时通知DBA协助排查
• 推广活动或上线新功能必须提前通知DBA进行流量评估
• 数据库数据丢失，及时联系DBA进行恢复
• 对单表的多次alter操作必须合并为一次操作
• 不在MySQL数据库中存放业务逻辑
• 重大项目的数据库方案选型和设计必须提前通知DBA参与
• 对特别重要的库表，提前与DBA沟通确定维护和备份优先级
• 不在业务高峰期批量更新、查询数据库其他规范
• 提交线上建表改表需求，必须详细注明所有相关SQL语句
  

其他规范

日志类数据不建议存储在MySQL上，优先考虑Hbase或OceanBase，如需要存储请找DBA评估使用压缩表存储。

相关图文教程：mysql数据库图文教程

以上が史上最も包括的な MySQL 使用仕様の共有の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。