MySQL開発標準と使用スキルの概要

1. 命名規則

1. データベース名、テーブル名、フィールド名には小文字を使用し、アンダースコアで区切る必要があります。

a)MySQL には lower_case_table_names という設定パラメータがありますが、これは動的に変更できません。Linux システムのデフォルト値は 0 で、ライブラリ名とテーブル名は実際の状況に応じて保存され、大文字と小文字が区別されることを意味します。 1 の場合は、大文字と小文字を区別せずに小文字で保存されます。 2 の場合はそのまま保存しますが、小文字で比較します。

b) 大文字と小文字が混在すると、abc、Abc、ABC などの複数のテーブルが共存することがあり、混乱を招きやすくなります。

c) フィールド名は大文字と小文字が区別されて表示されますが、実際には使用されません。つまり、名前が同じで大文字と小文字が異なる 2 つのフィールドを作成することはできません。

d) 標準を統一するために、ライブラリ名、テーブル名、フィールド名には小文字を使用します。

2. ライブラリ名、テーブル名、フィールド名は 32 文字を超えてはなりません。

ライブラリ名、テーブル名、フィールド名は最大 64 文字までサポートされます。ただし、統一性、識別の容易さ、転送量の削減のため、32 文字までしか使用できません。

3. INNODB ストレージ エンジンを使用します。

INNODB エンジンは、MySQL バージョン 5.5 以降のデフォルト エンジンです。トランザクションと行レベルのロックをサポートし、データ回復機能、同時実行パフォーマンスが向上し、マルチコア、大容量メモリ、SSD などのハードウェアのサポートが向上しています。ホット データ バックアップなどをサポートしています。したがって、INNODB は MyISAM よりも明らかに優れています。

4. ライブラリ名、テーブル名、フィールド名には MySQL の予約語は使用できません。

ライブラリ名、テーブル名、フィールド名などの属性に予約語が含まれている場合、SQL ステートメントではバックティックを使用して属性名を引用符で囲む必要があり、これにより、シェル スクリプトでの SQL ステートメントの記述と変数のエスケープが非常に複雑になります。

5. パーティションテーブルの使用は禁止されています。

パーティション テーブルには、パーティション キーに関する厳しい要件があります。パーティション テーブルが大きくなると、DDL、シャーディング、および単一テーブルのリカバリを実行することが難しくなります。そのため、パーティションテーブルの使用は禁止されており、ビジネス側では手動の SHARDING が推奨されます。

6. 負でない値を格納するには UNSIGNED を使用することをお勧めします。

同じバイト数の場合、非負のストレージでは値の範囲が広くなります。たとえば、TINYINT の符号付き値は -128 ～ 127、符号なし値は 0 ～ 255 です。

7. IPV4 を保存するには INT UNSIGNED を使用することをお勧めします。

UNSINGED INT を使用して IP アドレスを保存すると 4 バイトが使用され、CHAR(15) を使用すると 15 バイトが使用されます。さらに、コンピューターは文字列よりも整数を高速に処理します。 IPv4 アドレスを保存するには、CHAR(15) ではなく INT UNSIGNED を使用し、MySQL 関数 inet_ntoa および inet_aton を使用して変換します。現在、IPv6 アドレスの変換関数はないため、DECIMAL または 2 つの BIGINT を使用して保存する必要があります。

例えば：

INET_ATON('209.207.224.40')を選択します。3520061480
INET_NTOA(3520061480)を選択します。209.207.224.40

8. ENUM 型の代わりに TINYINT を使用することを強くお勧めします。

ENUM 型で列挙値を変更または追加する必要がある場合、コストのかかるオンライン DDL が必要になります。ENUM 列の値に数値型が含まれている場合、デフォルト値について混乱が生じる可能性があります。

9. 大文字と小文字を区別する可変長文字列またはバイナリ コンテンツを格納するには、VARBINARY を使用します。

VARBINARY はデフォルトで大文字と小文字を区別し、文字セットの概念がなく、高速です。

10.INT型は4バイトの記憶域を占有する

たとえば、INT(4) は表示文字幅が 4 ビットであることを意味するだけで、格納長を意味するものではありません。数値型の括弧の後の数字は幅のみを示し、格納範囲とは関係ありません。たとえば、INT(3) はデフォルトで 3 桁を表示し、幅を埋めるためにスペースが追加されます。幅が制限を超える場合は、通常の表示になります。Python および Java クライアントにはこの機能はありません。

11. DATETIME と TIMESTAMP の使用を区別します。

年を保存するには、YEAR 型を使用します。日付を保存するには、DATE 型を使用します。 時間（秒単位の精度）を保存するには、TIMESTAMP 型を使用することをお勧めします。

DATETIME と TIMESTAMP はどちらも秒単位の精度です。TIMESTAMP は 4 バイトしかないのに対し、DATETIME は 8 バイトあるため、TIMESTAMP が推奨されます。同時に、TIMESTAMP は自動割り当てと自動更新の特性を備えています。注: バージョン 5.5 以前では、テーブルに複数のタイムスタンプ列がある場合、自動更新機能を持つことができるのは最大 1 つの列のみです。

TIMESTAMP の自動割り当て属性を使用するにはどうすればよいでしょうか?

a) 自動初期化と自動更新:

列1 TIMESTAMP デフォルト CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP

b) 自動的に初期化する:

列1 タイムスタンプ デフォルト CURRENT_TIMESTAMP

c) 自動更新、初期値は 0 です。

列1 TIMESTAMP 更新時のデフォルト 0 CURRENT_TIMESTAMP

d) 初期値は0です。

列1 タイムスタンプ デフォルト 0

12. すべてのフィールドは NOT NULL として定義されます。

a) テーブルの各行ごとに、各 NULL 列を識別するための追加のスペースが必要です。

b) B ツリー インデックスは NULL 値を格納しないため、インデックス フィールドが NULL になる可能性がある場合、インデックスの効率が低下します。

c) NULL値の代わりに0、特殊値、または空の文字列を使用することをお勧めします。

MySQL の使用に関するヒント

1. 大きなフィールドとアクセス頻度の低いフィールドを別々のテーブルに分割して保存し、ホット データとコールド データを分離します。

キャッシュの有効利用に役立ち、無駄なコールド データの読み取りを防ぎ、ディスク IO を削減し、ホット データがメモリ内に常駐するようにして、キャッシュ ヒット率を向上させます。

2. プレーンテキストのパスワードをデータベースに保存することは禁止されています。

パスワードを保存するには暗号化された文字列を使用し、パスワードが復号化されないようにします。パスワードのセキュリティを確保するには、ランダム文字列とソルトを使用します。

3. テーブルには主キーが必要です。主キーとして UNSIGNED 自動インクリメント列を使用することをお勧めします。

テーブルに主キーがない場合、INNODB はデフォルトで非表示の主キー列を設定します。主キーのないテーブルではデータ行を見つけるのが非常に難しく、行ベースのレプリケーションの効率も低下します。

4. 冗長なインデックスは禁止されています。

インデックス作成は、メンテナンスの負担と IO 圧力を増大させる諸刃の剣です。 (a,b,c)、(a,b)、後者は冗長なインデックスです。プレフィックス インデックスを使用すると、高速化を実現し、メンテナンスの負担を軽減できます。

5. 重複したインデックス作成は禁止されています。

主キー a;一意のインデックス a;重複したインデックスはメンテナンスの負担を増やし、メリットをもたらさずにディスク領域を占有します。

6. 「性別」などのカーディナリティの低い列にはインデックスを作成しないでください。

ほとんどのシナリオでは、低カーディナリティ列のインデックスを使用した正確な検索は、インデックスなしの完全なテーブルスキャンに比べて利点がなく、IO 負荷が増加します。

7. IO を削減し、ソートを回避するためにカバーリング インデックスを適切に使用します。

カバーリング インデックスは、インデックスから必要なすべてのフィールドを取得できるため、2 回目の検索のためにテーブルに戻る必要がなくなり、IO を節約できます。

INNODB ストレージ エンジンでは、セカンダリ インデックス (非主キー インデックス、補助インデックス、セカンダリ インデックスとも呼ばれる) は行アドレスを直接格納するのではなく、主キー値を格納します。

ユーザーがセカンダリ インデックスに含まれていないデータ列をクエリする必要がある場合、ユーザーは最初にセカンダリ インデックスを通じてプライマリ キー値を見つけ、次にプライマリ キーを通じて他のデータ列をクエリする必要があるため、クエリを 2 回実行する必要があります。カバーリング インデックスでは、必要なすべてのデータを 1 つのインデックスで取得できるため、より効率的です。

たとえばSELECT email，uid FROM user_email WHERE uid=xx 。uid が主キーでない場合は、index(uid, email) としてインデックスを追加してパフォーマンスを向上させることができます。

8. OR の代わりに IN を使用します。 SQL ステートメントの IN 句に含まれる値の数は多すぎてはならず、1000 未満である必要があります。

IN は範囲検索です。MySQL は検索前に IN リストの値を内部的にソートするため、OR よりも効率的です。

9. 表の文字セットは UTF8 を使用しますが、必要に応じて UTF8MB4 文字セットを使用するように申請できます。

a) UTF8 文字セットは、中国語の文字を格納するのに 3 バイト、英語の文字を格納するのに 1 バイトを占有します。

b) UTF8 は統一され普遍的なため、トランスコード中に文字化けするリスクはありません。

c) EMOJ などの絵文字を保存する必要がある場合は、UTF8MB4 文字セットの使用を申請できます。

10. UNION の代わりに UNION ALL を使用します。

UNION ALL では結果セットをソートする必要はありません。

11. order by rand() を使用しないでください。

order by rand() はテーブルに疑似列を追加し、rand() 関数を使用して各データ行の rand() 値を計算し、行に基づいて並べ替えます。通常、これによりディスク上に一時テーブルが生成されるため、非常に非効率的です。まず rand() 関数を使用してランダムな主キー値を取得し、次に主キーを通じてデータを取得することをお勧めします。

12. ページング効率を向上させるために、適切なページング方法を使用することをお勧めします。

次のようなページングステートメントがある場合:

SELECT * FROM テーブル ORDER BY TIME DESC LIMIT 10000, 10;

MySQL は先読み戦略を使用するため、このページング方法では大量の IO が発生します。

推奨されるページング方法:

SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC LIMIT 10.
SELECT * FROM テーブル 内部 JOIN (SELECT id FROM テーブル ORDER BY TIME LIMIT 10000，10) as t
USING(id)

13.SELECTは必要なフィールドのみを取得します。SELECT *は禁止されています。

ネットワーク帯域幅の消費を削減します。

カバーリングインデックスを効果的に使用できます。

テーブル構造を変更してもプログラムにはほとんど影響はありません。

14. SQL では、now()、rand()、sysdate()、current_user() などの結果が不確実な関数の使用を避けてください。

ステートメント レベルのレプリケーション シナリオでは、マスター スレーブ データが不整合になります。不確実な値を持つ関数によって生成された SQL ステートメントは、QUERY CACHE を使用できません。

15. 適切なデータベースおよびテーブル シャーディング戦略を採用します。たとえば、倉庫が 1,000 個でテーブルが 10 個、倉庫が 10 個でテーブルが 100 個などです。

適切なデータベースとテーブルのシャーディング戦略を採用すると、ビジネス開発の後期段階でデータベースを迅速に水平分割するのに役立ちます。同時に、データベース シャーディングは MySQL のマルチスレッド レプリケーション機能を効果的に活用できます。

16. データベースとのやり取りの回数を減らし、バッチ SQL ステートメントを使用するようにします。

次のステートメントを使用して、DB とのやり取りの数を減らします。

a) 重複キー更新時に挿入...

b) に置き換える

c) 無視を挿入

d) VALUES() に挿入する

17. 大規模なトランザクションを回避するために、複雑な SQL を複数の小さな SQL に分割します。

シンプルな SQL では、MySQL の QUERY CACHE を簡単に使用でき、特に MyISAM のテーブル ロック時間を短縮し、マルチコア CPU を使用できます。

18. 同じテーブルに対する複数の変更操作は、1 つの操作に結合する必要があります。

ほとんどの MySQL テーブル変更操作ではテーブルのロックと再構築が必要であり、テーブルをロックするとオンライン ビジネスに影響します。この影響を軽減するには、テーブルに対する複数の変更操作を 1 つの操作に組み合わせる必要があります。たとえば、テーブルtにフィールドbを追加し、既存のフィールドaaにインデックスを作成するには、

通常のアプローチは 2 つのステップに分かれています。

テーブルtを変更し、列bをvarchar(10)に追加します。

次にインデックスを追加します。

テーブル t を変更し、インデックス idx_aa(aa) を追加します。

正しい方法は次のとおりです。

テーブルtを変更し、列b varchar(10)を追加し、インデックスidx_aa(aa)を追加します。

19. ストアド プロシージャ、トリガー、ビュー、カスタム関数などの使用は避けてください。

これらの高度な機能にはパフォーマンスの問題があり、多くの未知のバグがあります。ビジネス ロジックをデータベースに配置すると、データベース DDL、スケール アウト、シャーディングなどが難しくなります。

20. スーパー権限を持つアプリケーションアカウントは禁止されています。

安全第一。スーパー権限により読み取り専用が無効になり、追跡が困難な多くの奇妙な問題が発生します。

21. MySQL データベースにビジネス ロジックを保存しないでください。

データベースは複雑で変更が遅いステートフル サービスです。ビジネス ロジックをデータベースに配置すると、ビジネスの急速な発展が制限されます。ロジックとストレージの分離を実現するために、ビジネスロジックをあらかじめフロントエンドまたはミドルロジック層に配置し、データベースをストレージ層として使用することをお勧めします。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。これについてもっと知りたい場合は、次のリンクをご覧ください。