MySQLテーブル設計：14の黄金律

1. テーブル設計時に含めるべき共通フィールド

一般的に、テーブルには以下のフィールドを含めるべきです。

• id: 主キー。すべてのテーブルは主キーを持つ必要があります。例外はありません。
• create_time: 作成時間 — 必須。
• modified_time: 最終更新時間 — 必須。レコードが更新されるたびに更新されるべきです。
• version: レコードのバージョン番号。通常、楽観的ロックに使用されます — オプション。
• modifier: レコードを最後に修正した人 — オプション。
• creator: レコードを作成した人 — オプション。

2. すべてのフィールドにコメントを付けること。特に列挙型の場合

テーブルを設計する際、すべてのフィールドにコメントを含める必要があります。これは、列挙型を使用するフィールドの場合に特に重要です。すべてのenum値はコメントにリストされている必要があります。後で変更が発生した場合は、コメントにも反映する必要があります。

悪い例:

CREATE TABLE order_tab (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNIQUE,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    payment_status VARCHAR(20) DEFAULT 'not_paid',
    version INT DEFAULT 0,
    created_time DATETIME,
    updated_time DATETIME,
    creator VARCHAR(255),
    modifier VARCHAR(255)
);

良い例:

CREATE TABLE order_tab (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '注文アイテムの一意の識別子、自動インクリメントされる主キー',
    order_id BIGINT UNIQUE COMMENT '注文のグローバル一意の識別子',
    user_id BIGINT NOT NULL COMMENT 'ユーザーの一意の識別子、ユーザーテーブルにリンク',
    total_amount DECIMAL(10, 2) NOT NULL COMMENT '注文の合計金額、小数点以下2桁まで',
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '注文のステータス、例：PENDING、COMPLETEDなど',
    payment_status VARCHAR(20) DEFAULT 'not_paid' COMMENT '支払いステータス、例：not_paid、paidなど',
    version INT DEFAULT 0 COMMENT '同時実行制御のための楽観的ロックのバージョン番号',
    created_time DATETIME COMMENT '注文作成のタイムスタンプ',
    updated_time DATETIME COMMENT '最終更新のタイムスタンプ',
    creator VARCHAR(255) COMMENT '注文の作成者 — 通常はユーザーまたはシステムのユーザー名',
    modifier VARCHAR(255) COMMENT '注文を最後に変更した人またはシステム'
);

3. 命名規則

テーブル名、フィールド名、インデックス名などに明確で一貫性のある命名規則を使用します。適切な命名は、可読性と理解度を向上させます。

たとえば、次のような名前は避けてください。

• acc_no、1_acc_no (悪い例)

代わりに、次のように使用します。

• account_no、account_number (良い例)

追加のルール:

• テーブルおよびフィールド名は大文字と小文字の区別なく、小文字または数字のみを使用します。
• 名前を数字で始めないでください。
• 省略形は避けてください。
• インデックスの命名規則:
  • 主キー: pk_<field_name>
  • ユニークインデックス: uk_<field_name>
  • レギュラーインデックス: idx_<field_name>

4. 適切なフィールド型を選択する

テーブルを設計する際は、最も適切なフィールド型を選択してください。

• ストレージを節約するために、最も小さい適切なデータ型を使用します — 例：tinyint、smallint、int、bigintの順に優先します。
• 金銭的な値には、floatまたはdoubleではなくdecimalを使用します。
• 文字列の長さが固定またはほぼ固定の場合は、charを使用します。
• 可変長の文字列にはvarcharを使用しますが、長さを5000未満に保ってください。
• 非常に大きな値の場合は、textを使用し、主キーでリンクされた別のテーブルに格納することを検討してください。
• テーブル内のすべてのvarcharフィールドの合計長は、65535バイトを超えてはなりません。必要に応じて、TEXTまたはLONGTEXT型を使用してください。

5. 合理的な主キーを設計する

主キーをビジネスロジックにバインドすることは避けてください。たとえば、ユーザーID（一意であっても）を主キーとして使用することはお勧めできません。代わりに、次のような意味のない一意の識別子を使用します。

• UUID
• 自動インクリメントされる主キー
• Snowflakeアルゴリズムによって生成された主キー

6. 適切なフィールド長を選択する

最初に質問させてください：データベースでは、フィールド長は文字長を表しますか、それともバイト長を表しますか？

MySQLでは:

• varcharおよびcharは文字長を指定します。
• その他の型は通常バイト長を指定します。

たとえば:

• char(10)は10文字を意味します。
• bigint(4)は表示幅（ストレージサイズではない）を指しますが、bigintは常に8バイトを取ります。

テーブルを設計する際は、フィールド長を慎重に検討してください。たとえば、ユーザー名フィールドが5〜20文字になると予想される場合は、username varchar(32)として定義できます。

ヒント：フィールド長は通常、2の累乗（つまり、2ⁿ）として設定するのが最適です。

7. 物理削除よりも論理削除を優先する

物理削除: データはディスクから完全に削除され、ストレージスペースが解放されます。

論理削除: is_deletedのようなフィールドを追加して、データを削除済みとしてマークします。

物理削除の例:

DELETE FROM account_info_tab WHERE account_no = '666';

論理削除の例:

UPDATE account_info_tab SET is_deleted = 1 WHERE account_no = '666';

論理削除を優先する理由

• 物理削除では、データの回復が困難です。
• 自動インクリメントされた主キーは非連続になります。
• コアビジネステーブルの場合、レコードを物理的に削除するよりも、ステータスフィールドを更新する方が適切です。

8. 単一のテーブルに多数​​のフィールドを含めることは避けてください

テーブルを設計する際は、フィールドの数を制限するようにしてください — 通常は20以下。

フィールドが多すぎると、次のようになります。

• 大きな行サイズ
• クエリのパフォーマンスが低下します。

ビジネスロジックで多くのフィールドが必要な場合は、同じ主キーを持つより小さなテーブルに大きなテーブルを分割することを検討してください。

テーブルに多数のフィールドがある場合は、次のものに分離することを検討してください。

• 「クエリ条件」テーブル（高速フィルタリング用）
• 「詳細」テーブル（完全なコンテンツ用） パフォーマンスを向上させるため。

9. 可能な限りNOT NULLを使用する

特定の理由がない限り、フィールドをNOT NULLとして定義することをお勧めします。

なぜ？

• NullPointerExceptionの問題を防ぎます。
• NULLは追加のストレージスペースを占有します。
• NULLとの比較はより複雑になり、クエリの最適化を妨げます。
• NULL値はインデックスの失敗を引き起こす可能性があります。
• フィールドを空の文字列または定数に安全にデフォルト設定できる場合は、NOT NULLとして定義する必要があります。

10. どのフィールドにインデックスが必要かを評価する

まず、データセットのサイズを評価します。テーブルに数百行しかない場合、インデックスは必要ない場合があります。

一般に、フィールドがクエリ条件で頻繁に使用される場合は、インデックスが必要です。ただし、インデックスの過剰使用は避ける必要があります。

• 作成するインデックスが多すぎないようにしてください — テーブルあたり5つ以下に保ってください。
  • インデックスが多すぎると、挿入および更新操作が遅くなります。
• カーディナリティの低いフィールド（例：性別）にはインデックスを付けないでください。
• インデックス付きフィールドで組み込みのMySQL関数を使用するなど、インデックスの失敗を引き起こすケースに注意してください。
• インデックスの数を減らすには、**複合（複数列）**インデックスを検討してください。
  • カバリングインデックスなどの手法を使用し、leftmost prefixルールに従ってください。

ユーザーテーブルの例:

CREATE TABLE user_info_tab (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  `modifed_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

user_idまたはnameでクエリを実行する可能性が高く、user_idが一意であるため、次のようなインデックスを定義できます。

CREATE TABLE user_info_tab (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  `modifed_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  UNIQUE KEY un_user_id (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

11. MySQLの予約語の使用を避ける

データベース名、テーブル名、またはフィールド名に予約語が含まれている場合、SQLステートメントではバッククォート（`）で囲む必要があります。これにより、SQLの記述が複雑になり、特にスクリプトを作成したり、シェル変数を使用したりする場合に、エラーのリスクが増加します。

したがって、select、interval、descなどのMySQL予約語の使用は避けてください。

12. InnoDBストレージエンジンを優先する

テーブルを作成するときは、ストレージエンジンを選択する必要があります。一般に、読み取り/書き込み比率が1％未満でない限り、InnoDBが推奨されます。その場合は、MyISAMを検討できます。

13. 適切な時間タイプの選択

テーブルを設計する場合、通常、create_time、modified_timeなどの共通の時間関連フィールドを含めます。では、どのMySQL時間タイプを使用する必要がありますか？

主なオプションは次のとおりです。

• date: yyyy-mm-dd形式で日付値を格納します。範囲：1000-01-01から9999-12-31。サイズ：3バイト。
• time: hh:mm:ss形式で時間値を格納します。範囲：-838:59:59から838:59:59。サイズ：3バイト。
• datetime: yyyy-mm-dd hh:mm:ss形式で日付と時刻を格納します。範囲：1000-01-01 00:00:00から9999-12-31 23:59:59。サイズ：8バイト。タイムゾーンに依存しません。
• timestamp: 日付と時刻をタイムスタンプ（yyyymmddhhmmss）として格納します。範囲：1970-01-01 00:00:01から2038-01-19 03:14:07。サイズ：4バイト。タイムゾーンに依存します。
• year: yyyy形式で年の値を格納します。範囲：1901から2155。サイズ：1バイト。

推奨：datetime型を使用して日付と時刻を格納することをお勧めします。範囲が広く、タイムゾーンに依存しないためです。

14. セキュリティに関する考慮事項

• データ暗号化: ユーザーのパスワードなどの機密情報は、暗号化された形式で保存する必要があります。
• データマスキング: 電話番号やメールアドレスなどの個人識別情報（PII）については、プライバシーとコンプライアンスを向上させるために、データマスキングを適用します。

---

私たちはLeapcellです。バックエンドプロジェクトをホストするための最適な選択肢です。

Leapcellは、Webホスティング、非同期タスク、およびRedis向けの次世代サーバーレスプラットフォームです。

多言語サポート

• Node.js、Python、Go、またはRustで開発します。

無制限のプロジェクトを無料でデプロイ

• 使用量に対してのみ支払い — リクエストも料金もありません。

他に類を見ない費用対効果

• アイドル料金なしの従量課金制。
• 例：25ドルで、平均応答時間60msで694万リクエストをサポート。

合理化された開発者エクスペリエンス

• 簡単なセットアップのための直感的なUI。
• 完全に自動化されたCI / CDパイプラインとGitOps統合。
• 実用的な洞察のためのリアルタイムのメトリックとロギング。

容易なスケーラビリティと高パフォーマンス

• 高い同時実行を簡単に処理するための自動スケーリング。
• 運用のオーバーヘッドはゼロ — 構築に集中するだけです。

ドキュメントで詳細をご覧ください。

Xでフォローしてください：@LeapcellHQ