MySQLテーブル設計のための14のルール

Posted at 2025-04-07

1. テーブル設計時には、できるだけ以下の共通フィールドを含める

通常、テーブルには以下のフィールドが含まれるべきです：

id：主キー。テーブルには必ず主キーが必要です。必須。
create_time：作成日時。必須。
modified_time：更新日時。必須。レコードを更新する際にこのフィールドも更新します。
version：データレコードのバージョン番号。楽観的ロックに使用されることが多いが、必須ではありません。
modifier：更新者。必須ではありません。
creator：作成者。必須ではありません。

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

テーブル設計の際、すべてのフィールドにコメントを記載するべきです。特に列挙型フィールドについては、それぞれの列挙値の意味をコメントとして明記する必要があります。後で変更があった場合も、コメントを更新してください。

悪い例：

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_フィールド名
一意インデックス名：uk_フィールド名
通常のインデックス名：idx_フィールド名

4. 適切なフィールド型を選ぶ

テーブル設計時には、適切なフィールド型を選ぶ必要があります。たとえば：

可能な限り、より小さなストレージ容量の型を選びます。数値型であれば、tinyint、smallint、int、bigint の順に検討します。
金額などの小数は decimal 型を使用し、float や double の使用は禁止です。
文字列の長さがほぼ一定の場合は、char 固定長文字列型を使用します。
varchar は可変長文字列型で、ストレージを事前に確保しません。長さは 5000 文字を超えないようにします。
保存する値が非常に大きい場合は、フィールド型を text に変更し、別テーブルに分割して主キーで紐づけることを検討してください。
一つのテーブル内で varchar 型フィールドの合計長が 65535 バイトを超えないようにします。それ以上必要な場合は、TEXT や LONGTEXT 型を使用します。

5. 主キー設計は慎重に行う

主キー設計では、業務ロジックと直接的な関係を持たせない方が望ましいです。たとえば、ユーザー ID のような一意性を持つ業務上のフィールドを主キーに使う開発者もいますが、これは推奨されません。

主キーは、業務的な意味を持たない、一意で重複しない数字列であるべきです。たとえば：

UUID
AUTO_INCREMENT の自動増分主キー
雪花アルゴリズムによる ID 生成

などが適しています。

6. 適切なフィールド長を選ぶ

ここで質問です：データベースのフィールド長は「文字数」でしょうか？それとも「バイト数」でしょうか？

MySQL では、varchar や char 型の長さは「文字数」を表しますが、それ以外の型（例えば数値型）は「バイト数」を示します。

たとえば：

char(10) は文字数 10 文字
bigint(4) の (4) は表示幅の指定で、実際のバイト数は bigint の仕様（8 バイト）に依存します

テーブル設計では、フィールドの長さをよく検討する必要があります。たとえば、ユーザー名（5〜20 文字）を保存する場合、username varchar(32) のように、余裕を持った長さを設定するとよいでしょう。

フィールド長は、通常「2 の累乗」（2 の n 乗）に設定することが推奨されます。

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

物理削除とは？論理削除とは？

物理削除：データをハードディスクから完全に削除し、ストレージ容量を解放します。
論理削除：is_deleted などのフラグフィールドを追加し、そのデータが削除されたことをマークします。

物理削除の SQL 例（アカウント番号が「666」のデータを削除）：

delete from account_info_tab where account_no = '666';

論理削除の SQL 例：

update account_info_tab set is_deleted = 1 where account_no = '666';

なぜ論理削除を推奨し、物理削除を推奨しないのか？

物理削除されたデータは復旧が非常に困難
自動増分の主キーが連番でなくなる
重要な業務テーブルに対しては、物理削除は避け、ステータス変更などの方式で論理削除とするべき

8. 1 つのテーブルにフィールドを詰め込みすぎない

テーブルを作成する際、1 つのテーブルにフィールドを詰め込みすぎないようにしましょう。通常、20 列以内に収めるのが望ましいです。

フィールドが多すぎると、1 レコードのデータ量が大きくなり、クエリ効率が低下します。業務要件上どうしても多くの項目が必要な場合は、大きなテーブルを複数の小さなテーブルに分割し、それらを主キーで結びつけましょう。

特にフィールド数が非常に多い場合：

一部のフィールドを条件検索用の「概要テーブル」として切り出し
残りを詳細情報を保存する「詳細テーブル」に分離
これは主にパフォーマンスのためです

9. 可能な限り NOT NULL を使用してフィールドを定義する

特別な理由がない限り、フィールドは NOT NULL で定義することを推奨します。

その理由は：

NULL にすると「null ポインタ問題」が発生しやすい
NULL 値の保存には追加のストレージが必要で、比較演算が複雑になり、SQL の最適化が難しくなる
NULL 値によりインデックスが無効になる可能性がある
空文字や固定のデフォルト値でもアプリケーションロジックに影響しないなら、NOT NULL にしておいた方がよい

10. テーブル設計時に、どのフィールドにインデックスを張るか評価する

まず、テーブルのデータ量を評価してください。もし数百件程度しかないのであれば、インデックスは不要です。

それ以外の場合、検索条件として使用されるフィールドにはインデックスを検討すべきです。

ただし、インデックスは使いすぎに注意：

インデックスの数が多すぎると書き込み速度が低下します。1 テーブルあたり最大 5 個までが目安
区別しにくいフィールド（例：性別など）にはインデックスを付けても効果が薄い
MySQL の組込み関数を使った条件ではインデックスが無効になることがあります
複数のインデックスが必要な場合は複合インデックス（連結インデックス）で最適化可能です。さらに、カバリングインデックス、最左一致原則などのルールもあります

ユーザーテーブルの例：

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 の予約語の使用は避ける

データベース名、テーブル名、フィールド名などにMySQL の予約語を使うと、SQL 文でバッククォート（`）によるエスケープが必要になります。これにより、SQL の記述や SHELL スクリプトでの変数展開が非常に複雑になります。

そのため、予約語の使用は避けるべきです。例えば以下のようなキーワード：

select
interval
desc など

12. 一般的には INNODB ストレージエンジンを選択する

テーブル作成時にはストレージエンジンを指定する必要がありますが、通常は INNODB エンジンを選択します。

例外として、読み込みと書き込みの比率が 1%未満のケースでは MyISAM を検討する余地がありますが、基本的には INNODB を使用します。

13. 日時型の選択

テーブル設計時には、create_time や modified_time などの汎用的な時間フィールドを追加するのが一般的です。では、MySQL においてどの時間型を選べばよいでしょうか？

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 バイト。