MySQL データベースの鉄則 (要約)

適切なデータベース仕様は、ソフトウェア実装の複雑さを軽減し、通信コストを削減するのに役立ちます。この鉄則は主に、データベースとテーブルの構築、インデックスの作成、SQL の記述、ORM マッピングなどの処理規則をカバーしています。

1. データベース構築の鉄則

-	鉄のルール	レベル	述べる
文字セット	utf-8を使用してください。式を保存する場合は、保存に utf8mb4 を使用します。	執行
並べ替えルール	utf8_general_ci の使用	執行

2. テーブル作成の鉄則

-	鉄のルール	レベル	述べる
注記	フィールド注釈は必須です。	執行
コーディング	utf-8を使用してください。式を保存する場合は、保存に utf8mb4 を使用します。	執行
概念フィールド	名前は is_xx にする必要があります。データ型は unsigned tinyint (はいの場合は 1、いいえの場合は 0) です。たとえば、is_deleted (削除された場合は 1、削除されていない場合は 0) です。	執行	フィールドは、負でない場合は符号なしでなければなりません。
テーブル名、フィールド名	使用できるのは小文字、アンダースコア、または数字のみです。名前の先頭にアンダースコアまたは数字を使用することはできません。2 つのアンダースコアの間に数字のみを含めることはできません。予約語は禁止されています。テーブル名には複数形の名詞は使用できません。	執行
データベース名とテーブル名の命名	ライブラリ名はアプリケーション名と可能な限り一貫性を持たせ、テーブル名はビジネス名_テーブル関数で命名する必要があります。	執行
インデックスの命名	主キーインデックスでは pk_field 名が使用され、一意のインデックスでは uk_field 名が使用され、共通インデックスでは idx_field 名が使用されます。	執行	pk_は主キー、uk_は一意キー、idx_はインデックスです。
小数点型	データ型は 10 進数です。浮動小数点数と倍精度浮動小数点数は使用禁止です。浮動小数点数と倍精度浮動小数点数は精度が落ちます。保存されたデータ範囲が 10 進数の範囲を超える場合は、データを整数と 10 進数に分割して別々に保存することをお勧めします。	執行
varchar型	varchar は、記憶域を事前割り当てしない可変長文字列であり、長さは 5,000 文字を超えてはなりません。長さが 5,000 を超える場合は、テキストを使用します (別のテーブルを作成し、それに対応する主キーを使用して、他のフィールドのインデックス効率に影響を与えないようにします)。	執行
テーブル名には3つのフィールドが必要です	id (データ型は unsigned bigint、単一テーブル増分、ステップサイズは 1)、gmt_create、gmt_modified (アクティブ作成時刻、パッシブ更新時刻、データ型は datetime)。	執行
フィールド冗長性	フィールドは適切に冗長化できますが、データの一貫性を考慮する必要があります。冗長フィールドは、1) 頻繁に変更されないこと、2) テキストフィールドはもちろん、varchar の非常に長いフィールドではないことが必要です。	推薦する
サブライブラリとサブテーブル	1 つのテーブル内の行数が 500 万を超える場合、または 1 つのテーブルの容量が 2 GB を超える場合にのみ、データベースとテーブルをシャードすることをお勧めします。	推薦する

適切な文字格納長を設定すると、データベースのテーブルスペースとインデックスの格納領域を節約できるだけでなく、さらに重要なことに、検索速度が向上します。

3. インデックス作成の鉄則

-	鉄のルール	レベル
ユニークインデックス	ビジネス用語で一意のフィールドは、複数のフィールドの組み合わせであっても一意にインデックス付けする必要があります。ユニークインデックスは挿入速度に影響するため、この損失は無視できますが、クエリ速度が大幅に向上します。また、アプリケーション層で非常に完全な検証制御を行ったとしても、ユニークインデックスがない限り、マーフィーの法則に従ってダーティデータが必然的に生成されます。	執行
参加する	3 つ以上のテーブルを結合することは禁止されています。結合するフィールドのデータ型は一貫している必要があります。複数のテーブルをクエリする場合は、関連するフィールドにインデックスがあることを確認してください。2 つのテーブルを結合する場合でも、テーブルインデックスと SQL パフォーマンスに注意してください。	執行
varchar フィールドにインデックスを作成する	インデックスの長さを指定する必要があります。フィールド全体をインデックスする必要はありません。インデックスの長さは、実際のテキストの区別に基づいて決定できます。インデックスの長さと判別性は矛盾しています。一般的に、文字列データの場合、長さが 20 のインデックスの判別性は 90% 以上になります。判別性を判断するには、count(distinct left(列名, インデックスの長さ))/count(*) を使用します。	執行
ページ検索はあいまいさを禁止する	ページ検索はぼやけたままにしたり、完全にぼやけたりすることはできません。必要な場合は、検索エンジンを使用して解決してください。禁止理由: インデックスファイルは B-Tree の左端のプレフィックスマッチング機能を持っています。左側の値が決定されていない場合、このインデックスは使用できません。	執行
並び替え	シナリオによる順序がある場合は、インデックスの順序に注意してください。 order by の最後のフィールドは複合インデックスの一部であり、クエリのパフォーマンスに影響する file_sort 状況を回避するためにインデックス複合順序の最後に配置されます。肯定的な例: where a=? かつ b=? order by c; インデックスは a_b_c として作成する必要があります。否定的な例: インデックスに範囲検索がある場合、where a>10 order by b; のようにインデックス順序は使用できません。インデックス a_b はソートできません。	推薦する

4. SQLを書く際の鉄則

-	鉄のルール	レベル
カウント（*）	count() の代わりに count(列名) または count(定数) を使用しないでください。count() は、SQL92 で定義されている行をカウントするための標準構文です。これはデータベースとは関係がなく、NULL または非 NULL とも関係ありません。 count(*) は値が NULL である行をカウントしますが、count(列名) は値が NULL である行をカウントしません。	執行
count(個別の列)	NULL を除いて、この列内の一意の行の数をカウントします。列の 1 つがすべて NULL の場合、他の列に異なる値があっても、count(distinct col1, col2) は 0 を返すことに注意してください。	執行
合計(列)	列内のすべての値がNULLの場合、count(col)は0を返しますが、sum(col)はNULLを返します。そのため、sum()を使用する場合はNPEに注意する必要があります。次の方法に従うことで NPE 問題を回避できます: select if(isnull(sum(g)), 0, sum(g)) from table;	執行
NULLです	isnull() を使用して、NULL 値かどうかを確認します。 NULL は任意の値と NULL を比較します。	執行
ページングクエリロジック	count が 0 の場合、後続のページングステートメントの実行を回避するために直接戻る必要があります。	執行
外部キーとカスケード	外部キーとカスケードは禁止されており、すべての外部キーの概念はアプリケーション層で解決する必要があります。理由: 外部キーとカスケードは、分散型の高同時実行クラスターには適していません。カスケード更新は強力にブロックされ、データベース更新ストームのリスクがあります。外部キーは、データベースの挿入速度に影響します。	執行
ストアドプロシージャ	ストアドプロシージャの使用は禁止されています。ストアドプロシージャはデバッグや拡張が難しく、移植性もありません。	執行
データ修正	データを修正する場合 (特にレコードの削除または変更) は、誤って削除されないように最初に選択し、すべてが正しいことを確認してから更新ステートメントを実行する必要があります。	執行
で	可能であれば in 演算を避けてください。避けられない場合は、 in 後のコレクション内の要素数を 1000 以内に制御する必要があります。	推薦する
テーブルを切り捨てる	truncate table は使用しないでください。truncate table は delete よりも高速で、使用するシステムリソースとログリソースが少なくなります。ただし、truncate にはトランザクションがなく、トリガーをトリガーしないため、事故が発生する可能性があります。したがって、開発コードではこのステートメントを使用しないでください。	参照する

5. ORM マッピングの鉄則

-	鉄のルール	レベル
テーブルクエリ	クエリのフィールドリストとして * を使用することは禁止されています。必須フィールドは明確にする必要があります。	執行
ポジョ	POJO クラスのブール属性は is を使用して追加できませんが、データベースフィールドは is を使用して追加する必要があり、そのためには resultMap 内のフィールドと属性間のマッピングが必要です。	執行
戻りパラメータ	resultClass を戻りパラメータとして使用することは禁止されています。すべてのクラス属性名がデータベースフィールドと 1 対 1 で対応している場合でも、定義する必要があります。逆に、各テーブルには対応する属性が必要です。理由: メンテナンスを容易にするために、フィールドと DO クラスを結合するマッピング関係を構成します。	執行
戻りパラメータ	クエリ結果セットの出力として HashMap または HashTable を直接使用することは禁止されています。原因: 属性値のタイプが制御不能です。	執行
sql.xml 構成パラメータ	sql.xml 構成パラメータには #{}、#param# を使用します。${} は SQL インジェクションの影響を受けやすいため、${} は使用しないでください。	執行
クエリリスト	Mybatis が提供する queryForList(String statementName, int start, int size) の使用は禁止されています。理由: 実装方法は、データベース内の statementName に対応する SQL ステートメントのすべてのレコードを取得し、subList を通じて start と size のサブコレクションを取得することです。	執行
更新時間	データベーステーブルレコードを更新するときは、レコードの変更時刻も同時に更新する必要があります。	執行
データベーステーブルレコードを更新する	大規模で包括的なデータ更新インターフェースを記述しないでください (POJO クラスを渡します)。 SQL を実行するときは、変更されていないフィールドを更新しないでください。理由: エラーが発生しやすく、非効率的であり、binlog ストレージが増加するためです。	推薦する
@トランザクション	@Transactional トランザクションを乱用しないでください。トランザクションはデータベースの QPS に影響します。さらに、トランザクションが使用される場合、キャッシュロールバック、検索エンジンロールバック、メッセージ補正、統計修正など、さまざまなロールバックソリューションを考慮する必要があります。	参照する
Mybatis 動的 SQL タグ	< isEqual> の compareValue は、属性値 (通常は数値) と比較される定数であり、等しい場合に対応する SQL ステートメントが実行されることを意味します。< isNotEmpty> は、空でも null でもない場合に実行されることを意味します。< isNotNull> は、null でない場合に実行されることを意味します。	参照する