MySQLデータベースの使用仕様の概要

導入：

MySQL データベースの仕様に関しては、皆さんも何らかのドキュメントを読んだことがあると思います。この記事では、ライブラリとテーブルの命名設計仕様から始まり、インデックス設計仕様まで、データベース関連の仕様を詳細にまとめ、最後に SQL の記述に関する提案を示します。これらの仕様はほとんどの企業に適用できると考えており、皆様が仕様に従って当社のデータベースを使用し、より高いパフォーマンスを実現していただければ幸いです。

ライブラリについて:

1. [必須] ライブラリ名は 32 文字以内で、英語の文字はすべて小文字にする必要があります。

2. [必須] ライブラリ名の形式は、ビジネス システム名_サブシステム名です。

3. [必須] ライブラリ名には英語の文字、数字、アンダースコアのみを使用でき、英語の文字で始まる必要があります。

4. [必須] データベースを作成するときに文字セットを明示的に指定する必要があり、文字セットは utf8 または utf8mb4 のみにすることができます。データベースを作成する SQL の例: Create database db1 default character set utf8;

5. [提案] 一時データベースとテーブルの名前には、先頭に tmp_ を付け、末尾に日付を付ける必要があります。バックアップ データベースとテーブルの名前には、先頭に bak_ を付け、末尾に日付を付ける必要があります。

テーブルについて

1. [必須] テーブル名と列名は 32 文字以内にする必要があります。テーブル名には、すべて小文字の文字、数字、アンダースコアのみを使用できます。

2. [必須] テーブル名はモジュール名と密接に関連している必要があります。同じモジュール内で使用されるテーブル名は、可能な限り統一されたプレフィックスを使用する必要があります。

3. [必須] テーブルを作成するときは、文字セットを utf8 または utf8mb4 として明示的に指定する必要があります。

4. [必須] 列名にキーワード (type、order など) を使用しないでください。

5. [必須] テーブルを作成するときは、テーブル ストレージ エンジンの種類を明示的に指定する必要があります。特別な要件がない場合は、常に InnoDB が使用されます。

6. [必須] テーブルを作成するときはコメントを含める必要があります。

7. [必須] 100 万行を超える大規模なテーブルの場合、ALTER TABLE 操作は DBA によって確認され、オフピーク時に実行される必要があります。複数の ALTER 操作は統合する必要があります。

alter table はテーブル ロックを生成し、期間中にテーブルへのすべての書き込みをブロックするため、ビジネスに大きな影響を与える可能性があります。

8. [提案] テーブル作成時の主キーについて: テーブルには主キーが必要です

（1）主キーはid、int型またはbigint型、auto_incrementである必要があります。unsigned型の使用をお勧めします。

（２）テーブル内の各行の主体を識別するフィールドを主キーに設定しないでください。user_id、order_idなどの他のフィールドに設定し、一意のキーインデックスを作成することをお勧めします。

主キーとして設定し、主キーの値をランダムに挿入すると、InnoDB で内部ページ分割が発生し、大量のランダム I/O が発生し、パフォーマンスが低下します。

9. [提案] 問題のチェックを容易にするために、コア テーブル (ユーザー テーブルなど) には行データの作成時刻フィールド create_time と最終更新時刻フィールド update_time が必要です。

10. [提案] テーブル内のすべてのフィールドには可能な限り NOT NULL 属性を持たせ、ビジネス側では必要に応じて DEFAULT 値を定義できます。

NULL 値を使用すると、各行が追加のストレージ領域を占有し、データ移行でエラーが発生しやすくなり、集計関数の計算結果がずれてしまうためです。

11. [提案] 中間テーブルは中間結果セットを格納するために使用され、その名前は tmp_ で始まる必要があります。バックアップ テーブルは、ソース テーブルのスナップショットをバックアップまたはキャプチャするために使用されます。名前は bak_ で始まる必要があります。中間テーブルとバックアップ テーブルは定期的にクリーンアップされます。

12. [デモンストレーション] より標準化されたテーブル作成ステートメント:

: : : : : : : : : : : : : : :

インデックスについて

1. [必須] InnoDB テーブルの主キーは id int/bigint auto_increment である必要があり、主キーの値を更新することはできません。

2. [必須] InnoDB および MyISAM ストレージ エンジン テーブルの場合、インデックス タイプは BTREE である必要があります。

3. [推奨事項] 主キーの名前は pk_ で始まり、一意キーは uniq_ または uk_ で始まり、共通インデックスは idx_ で始まります。すべての名前は小文字の形式で、フィールドの名前または略語をサフィックスとして使用します。

4. [提案] 1 つのテーブル上のインデックスの数は 8 を超えることはできません。

5. [提案] インデックスを作成するときは、結合インデックスを作成し、最も識別力の高いフィールドを先頭に配置することを検討してください。たとえば、userid 列の独自性は、select count(distinct userid) によって計算できます。

6. [提案] 複数テーブル結合の SQL では、結合実行効率を最大化するために、駆動テーブルの結合列にインデックスがあることを確認します。

7. [提案] テーブルを作成したりインデックスを追加したりするときは、テーブル内に冗長なインデックスがないことを確認してください。
MySQLの場合、テーブル内にkey(a,b)がすでに存在する場合、key(a)は冗長なインデックスであるため削除する必要があります。

SQL ライティング

1. [必須] プログラム側のSELECT文では特定のフィールド名を指定する必要があります。 * の記述は禁止です。

2. [必須] プログラム側の挿入文に特定のフィールド名を指定します。 insert into t1 values(…) のように記述しないでください。

3. [必須] 静的テーブルまたは小さなテーブル (100 行未満) を除き、DML ステートメントには where 条件があり、インデックス検索を使用する必要があります。

4. [必須] where 条件の等号の両側のフィールド タイプは一致している必要があります。一致していない場合はインデックスを使用できません。

5. [必須] WHERE 句では、検索に完全にあいまいな LIKE 条件のみを使用することはできません。他の等価または範囲のクエリ条件が必要です。そうしないと、インデックスは使用できません。

6. [必須] インデックス列では関数や式を使用しないでください。そうしないと、インデックスを利用できなくなります。たとえば、length(name)='Admin' または user_id+2=10023 などです。

7. [提案] insert into…values(XX),(XX),(XX)… ここでのXXの値は5000を超えてはなりません。
値が多すぎるとすぐにオンラインになりますが、マスターとスレーブの同期が遅延する原因になります。

8. [提案] SELECT 文では UNION を使用しないでください。UNION ALL を使用することをお勧めします。UNION 句の数は 5 に制限されています。
union all では重複排除が不要なため、データベース リソースが節約され、パフォーマンスが向上します。

9. [必須] DB間の結合ステートメントは禁止されています。

10. [提案] サブクエリの使用は推奨されません。サブクエリ SQL をプログラムと組み合わせた複数のクエリに分割するか、サブクエリの代わりに結合を使用することをお勧めします。

11. [提案] オンライン環境では、5 つ以上のテーブルを結合しないでください。

12. [提案] 複数テーブルの結合では、他のテーブルを結合するための駆動テーブルとして、結果セットが小さいテーブルを選択するようにしてください。

13. [提案] データをバッチ処理で操作する場合、トランザクションの処理間隔を制御し、必要なスリープを行う必要があります。

14. [提案] トランザクションが長すぎるとデータが長時間ロックされ、MySQL の内部キャッシュや接続が過剰に消費されるなどの問題が発生するため、トランザクションには 5 個以下の SQL ステートメントを含める必要があります。

15. [提案] トランザクション内の更新ステートメントは、update … where id=XX; のように、可能な限り主キーまたは一意のキーに基づいて作成する必要があります。
そうしないと、ギャップ ロックが生成され、内部的にロック範囲が拡大され、システム パフォーマンスが低下し、デッドロックが発生します。

16. [提案] order by の使用を減らし、可能であればビジネス部門と連絡を取り、ソートを避けるようにするか、ソートをプログラムの最後に移動してください。 order by、group by、distinct などのステートメントは CPU を大量に消費するため、データベースの CPU リソースは極めて貴重です。

17. [提案] order by、group by、distinct などの SQL ステートメントでは、インデックスを使用してソートされたデータを直接取得するようにしてください。たとえば、a=1 の場合、order by b には key(a,b) を使用できます。

18. [提案] order by、group by、distinct を含むステートメントの場合、where 条件でフィルタリングされた結果セットを 1,000 行以内に抑えてください。そうしないと、SQL が非常に遅くなります。

上記はMySQLデータベース使用仕様の詳細な内容です。MySQL使用仕様の詳細については、123WORDPRESS.COMの他の関連記事に注目してください。