史上最も包括的な MySQL 使用仕様の共有-mysql チュートリアル-php.cn

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

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

#データベース環境

dev: 開発環境

開発は読み取り、書き込み、変更が可能テーブル構造。開発者はテーブル構造とその中のデータを自由に変更できますが、他の開発同僚に影響を与えないようにする必要があります。

test: テスト環境

は開発者による読み取りと書き込みが可能であり、開発者はツールを使用してテーブル構造を変更できます。

online: オンライン環境

開発者は、オンライン環境でデータベース操作を直接実行することはできません。操作が必要な場合は、操作する DBA を見つけて作成する必要があります。圧力試験は禁止されています。

重要な問題は、各環境の MySQL サーバーに対応するユーザー権限が明確に分割され、識別可能であり、ビジネスシナリオなどを具体的に区別できる必要があることです。

#命名規則

基本的な命名規則

意味のある英語の語彙、単語を使用するアンダースコアで区切られています。 (ピンインは使用しないでください)

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

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

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

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

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

フィールド名

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

外部キーフィールドは、テーブル名_フィールド名を使用してその関連付けを示します。
テーブルの主キーは自動インクリメント型の id であることが一般的であり、他のテーブルの外部キーは xxx_id の形式で表現されます。
インデックスの名前付け

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

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

#テーブル設計仕様

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

## ではない理由を記述することをお勧めします。

#utf8 エンコーディングを使用する MySQL は、4 バイトの絵文字式のプレースホルダーを保存できません。クライアントによって入力された絵文字式をバックエンドプロジェクトで完全にサポートするには、エンコーディングを utf8mb4 にアップグレードすることが最善の解決策ですJDBC 接続文字列の場合、characterEncoding が utf8 に設定されているか、上記の設定を行っても正常に挿入できません。絵文字データの場合は、コード内で接続文字セットを utf8mb4 として指定する必要があります。すべてのテーブルとフィールドは、テーブルとフィールドの本当の意味を説明するためにコメント列属性を使用する必要があります。列挙値の場合は、このフィールドで使用されるすべてのコンテンツを定義することをお勧めします。説明では、テーブルの最初の ID フィールドは主キーである必要があり、自動的に拡張されます。非トランザクション内の条件としてコンテキストとしてのデータ転送は禁止されています。主キーステートメントの設計として varchar 型を使用することは禁止されています。

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

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

フィールドにデータを保存するために使用する記憶域スペースはできるだけ少なくしてください
int を使用できる場合は、char または varchar を使用しないでください
可能であればtinyint を使用してください。int は使用しないでください。
UNSIGNED を使用して、負でない値を格納します。
短いデータ型を使用します。たとえば、値の範囲が 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 はどう違いますか？

類似点:

TIMESTAMP 列の表示形式は DATETIME 列の表示形式と同じです。表示幅は 19 文字固定で、形式は YYYY-MM-DD HH:MM:SS です。

差異:

TIMESTAMP

日時は 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 を使用して格納する必要があります。

コメントがない場合、すべてのフィールドは NOT NULL に設定され、デフォルト値が設定されます;

データベースにプレーンテキストのパスワードを保存することは禁止されています
コメントがない場合、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を使用しないでください
非-デフォルトでは空の一意のキー
自動インクリメントまたは発行者を選択することをお勧めします

##重要 SQL にはインデックスを付ける必要があります。コア SQL はインデックスのカバーを優先します

ORDER BY、GROUP BY、および DISTINCT のフィールド
複数テーブル JOIN のフィールド

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

#注文番号、ユーザー ID など、より適切なフィルタリングプロパティを持つフィールドを選択し、前面に配置します。一般に、タイプやステータスなどのフィルタリングプロパティを持つフィールドを先頭に置くことはお勧めできません

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

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

##MYSQL のインデックスに関する制限

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

MYSQL は現在関数インデックスをサポートしていません

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

ステートメントの設計仕様

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

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

SQL インジェクションの可能性を低減します

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

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

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

「�」などの % 先行クエリを使用しないでください。

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

#ストアドプロシージャやトリガー、UDF、イベントなどの使用を避けます。

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

ビジネスの結合を減らし、 sacle out とシャーディング

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

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

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

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

インデックスが使用できません。

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

INSERT … ON DUPLICATE KEY UPDATE

REPLACE INTO 、 INSERT IGNORE 、INSERT INTO VALUES(),(),()

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)

ログイン後にコピー

拒否大きな SQL を小さな SQL に分割するin は 1000 を超えてはいけません

order by rand () の使用は禁止されています

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

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 サイトの他の関連記事を参照してください。