MySQL でテーブルを作成するときに知っておくべき重要なポイント

バックエンド開発者にとって、データベースへのアクセスは非常に重要です。

コア ユーザー データは通常、MySQL や Oracle などのデータベースに安全に保存されます。

日常のタスクには、ビジネス ニーズを満たすためにデータベースやテーブルの作成が含まれることがよくありますが、テーブルの作成頻度はさらに高くなります。

主要な詳細を無視すると、展開後のメンテナンスでコストのかかる問題が発生する可能性があるため、この記事ではテーブルの作成に焦点を当てます。

ところで、データベースの設計方法が不適切であると、同時実行性が高いときに API の応答が遅くなる可能性もあります。次の画像は、EchoAPI ツールを使用した API のパフォーマンス テストの結果を示しています。

今日は、データベースにテーブルを作成するための 18 のヒントについて説明します。

この記事で説明されている詳細の多くは、私自身の経験と仕事中に直面した課題に基づいたものであり、皆様のお役に立てれば幸いです。

1. ネーミング

テーブル、フィールド、インデックスを作成するときは、それらに適切な名前を付けることが非常に重要です。

1.1 意味のある名前

名前はテーブル、フィールド、インデックスの顔として機能し、第一印象を与えます。

良い名前は簡潔で自己記述的であり、コミュニケーションとメンテナンスが容易になります。

不適切な名前は曖昧でわかりにくく、混乱と不満を引き起こします。

悪い例:

abc、abc_name、name、user_name_123456789 のようなフィールド名には困惑するでしょう。

良い例:

フィールド名は user_name です。

注意事項: 名前も長すぎず、30 文字以内にするのが理想的です。

1.2 大文字と小文字の区別

視覚的に読みやすいため、名前には小文字を使用することをお勧めします。

悪い例:

PRODUCT_NAME、PRODUCT_name などのフィールド名は直感的ではありません。大文字と小文字が混在していると、読みにくくなります。

良い例:

フィールド名を product_name として使用すると、より快適に見えます。

1.3 セパレータ

多くの場合、理解を助けるために名前に複数の単語が含まれることがあります。

複数の単語の間にはどのような区切り文字を使用する必要がありますか?

悪い例:

productname、productName、product name、product@name などのフィールド名は推奨されません。

良い例:

product_name としてのフィールド名。

単語の間にはアンダースコア _ を使用することを強くお勧めします。

1.4 テーブル名

テーブル名には、意味のある簡潔な名前とビジネス接頭辞を使用することをお勧めします。

注文関連のテーブルの場合は、order_pay、order_pay_detail など、テーブル名の前に order_ を追加します。

製品関連のテーブルの場合は、product_spu、product_sku のように、product_ を先頭に追加します。

この方法は、同じビジネスに関連するテーブルを素早く分類するのに役立ちます。

さらに、非受注ビジネスで pay という名前のテーブルを作成する必要がある場合は、finance_pay として簡単に区別できるため、名前の競合を防ぐことができます。

1.5 フィールド名

フィールド名を使用すると、最大限の柔軟性が得られますが、混乱が生じやすくなります。

たとえば、あるテーブルでステータスを示すためにフラグを使用し、別のテーブルでステータスを使用すると、不整合が生じる可能性があります。

状態を表すためにステータスに標準化することをお勧めします。

テーブルが別のテーブルの主キーを使用する場合は、フィールド名の末尾に _id または _sys_no を追加します (例: product_spu_id または product_spu_sys_no)。

さらに、作成時刻を create_time 、変更時刻を update_time として標準化し、削除ステータスを delete_status に固定します。

他の共通フィールドも、より明確にするために、異なるテーブル間で統一した命名規則を維持する必要があります。

1.6 インデックス名

データベースには、主キー、通常のインデックス、一意のインデックス、複合インデックスなど、さまざまな種類のインデックスがあります。

テーブルには通常、id または sys_no という名前の主キーが 1 つあります。

通常のインデックスと複合インデックスでは、ix_ プレフィックス (ix_product_status など) を使用できます。

一意のインデックスには、ux_product_code などの ux_ プレフィックスを使用できます。

2. フィールドの種類

テーブルを設計する場合、フィールドの種類を十分に自由に選択できます。

時間形式のフィールドには、日付、日時、タイムスタンプなどを指定できます。

文字データ型には、varchar、char、text などが含まれます。

数値型は、int、bigint、smallint、tinyint で構成されます。

適切なフィールド タイプを選択することが重要です。

型を過大評価すると (例: 1 から 10 までの値のみを格納するフィールドに bigint を使用する)、スペースが無駄になります。 tinyint で十分です。

逆に、過小評価すると (例: 18 桁の ID に int を使用する)、データ ストレージのエラーが発生します。

フィールド タイプを選択するためのいくつかの原則を次に示します。

• 通常のビジネス ニーズを満たしながら、小さいストレージ サイズから大きいストレージ サイズまで選択することを好みます。
• 固定または同様の文字列長には char を使用し、さまざまな長さには varchar を使用します。
• ブールフィールドにはビットを使用します。
• 列挙型フィールドには tinyint を使用します。
• 主キー フィールドには bigint を選択します。
• 通貨フィールドには 10 進数を使用します。
• 時間フィールドにはタイムスタンプまたは日時を使用します。

3. フィールド長

フィールド名を定義し、適切なフィールド タイプを選択した後、varchar(20) や bigint(20) などのフィールド長に焦点を移す必要があります。

varchar は長さの観点から何を示しますか?

答え: MySQL では、varchar と char は文字の長さを表しますが、他のほとんどの型はバイト長を表します。

たとえば、bigint(4) は、8 バイトのままのストレージ長ではなく、表示長を指定します。

zerofill プロパティが設定されている場合、4 バイト未満の数値は埋め込まれますが、埋め込まれた場合でも、基礎となるデータ ストレージは 8 バイトのままです。

4. フィールドの数

テーブルを設計するときは、フィールドの数を制限することが重要です。

テーブルに数十、場合によっては数百のフィールドがあり、データ量が多くなり、クエリ効率が低下しているのを見たことがあります。

この状況が発生した場合は、共通の主キーを保持しながら、大きなテーブルを小さなテーブルに分割することを検討してください。

経験則として、テーブルあたりのフィールド数は 20 未満に抑えてください。

5. 主キー

テーブルを設定するときに主キーを作成します。

主キーには本質的に主キー インデックスが付属しており、追加の検索が必要ないため、クエリがより効率的になります。

単一データベースでは、主キーは自動拡張のために AUTO_INCREMENT を使用できます。

分散データベース、特にシャード アーキテクチャの場合は、外部アルゴリズム (Snowflake など) を使用して、グローバルに一意な ID を確保することが最善です。

さらに、結合を減らし、将来の拡張を容易にするために、主キーをビジネス値から独立させてください。

ただし、ユーザー テーブルやユーザー拡張テーブルなどの 1 対 1 のリレーションシップの場合は、ユーザー テーブルの主キーを直接使用しても問題ありません。

6. ストレージエンジン

MySQL 8 より前は、デフォルトのストレージ エンジンは MyISAM でした。 MySQL 8 以降では、InnoDB になりました。

歴史的に、どのストレージ エンジンを選択するかについては多くの議論がありました。

MyISAM はインデックスとデータ ストレージを分離し、クエリのパフォーマンスを向上させますが、トランザクションと外部キーのサポートがありません。

InnoDB はクエリが若干遅いですが、トランザクションと外部キーをサポートしており、より堅牢になっています。

以前は、読み取りが多いシナリオには MyISAM を使用し、書き込みが多いシナリオには InnoDB を使用することが推奨されていました。

ただし、MySQL の最適化によりパフォーマンスの差が減少したため、追加の変更を加えずに MySQL 8 以降のデフォルトの InnoDB ストレージ エンジンを使用することをお勧めします。

7. NULL ではない

フィールドを作成するときは、フィールドを NULL にできるかどうかを決定します。

可能な限りフィールドを NOT NULL として定義することをお勧めします。

なぜ?

InnoDB では、NULL 値を保存するには余分なスペースが必要であり、インデックスの失敗につながる可能性もあります。

= を使用すると常に false が返されるため、NULL 値は IS NULL または IS NOT NULL を使用してのみクエリできます。

したがって、可能な限りフィールドを NOT NULL として定義します。

ただし、フィールドが NOT NULL として直接定義されており、入力中に値が忘れられた場合、データの挿入ができなくなります。

新しいフィールドが追加され、新しいコードをデプロイする前にスクリプトが実行され、デフォルト値がない場合にエラーが発生する場合、これは許容できる状況である可能性があります。

新しく追加された NOT NULL フィールドの場合、デフォルト値の設定が重要です:

ALTER TABLE product_sku ADD COLUMN brand_id INT(10) NOT NULL DEFAULT 0;

8. 外部キー

MySQL の外部キーは、データの一貫性と整合性を確保するために役立ちます。

例:

CREATE TABLE class (
  id INT(10) PRIMARY KEY AUTO_INCREMENT,
  cname VARCHAR(15)
);

これにより、クラス テーブルが作成されます。

次に、それを参照する Student テーブルを構築できます。

CREATE TABLE student(
  id INT(10) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(15) NOT NULL,
  gender VARCHAR(10) NOT NULL,
  cid INT,
  FOREIGN KEY (cid) REFERENCES class(id)
);

ここで、student テーブルの cid は、class テーブルの id を参照します。

クラス内の対応する cid レコードを削除せずに学生のレコードを削除しようとすると、外部キー制約エラーが発生します。

外部キー制約が失敗します。

したがって、一貫性と完全性が維持されます。

外部キーは InnoDB ストレージ エンジンでのみ使用できることに注意してください。

If only two tables are linked, it might be manageable, but with several tables, deleting a parent record requires synchronously deleting many child records, which can impact performance.

Thus, for internet systems, it is generally advised to avoid using foreign keys to prioritize performance over absolute data consistency.

In addition to foreign keys, stored procedures and triggers are also discouraged due to their performance impact.

9. Indexes

When creating tables, beyond specifying primary keys, it’s essential to create additional indexes.

For example:

CREATE TABLE product_sku(
  id INT(10) PRIMARY KEY AUTO_INCREMENT,
  spu_id INT(10) NOT NULL,
  brand_id INT(10) NOT NULL,
  name VARCHAR(15) NOT NULL
);

This table includes spu_id (from the product group) and brand_id (from the brand table).

In situations that save IDs from other tables, a regular index can be added:

CREATE TABLE product_sku (
  id INT(10) PRIMARY KEY AUTO_INCREMENT,
  spu_id INT(10) NOT NULL,
  brand_id INT(10) NOT NULL,
  name VARCHAR(15) NOT NULL,
  KEY `ix_spu_id` (`spu_id`) USING BTREE,
  KEY `ix_brand_id` (`brand_id`) USING BTREE
);

Such indexes significantly enhance query efficiency.

However, do not create too many indexes as they can hinder data insertion efficiency due to additional storage requirements.

A single table should ideally have no more than five indexes.

If the number of indexes exceeds five during table creation, consider dropping some regular indexes in favor of composite indexes.

Also, when creating composite indexes, always apply the leftmost matching rule to ensure the indexes are effective.

For fields with high duplication rates (like status), avoid creating separate regular indexes. MySQL may skip the index and choose a full table scan instead if it’s more efficient.

I’ll address index inefficiency issues in a separate article later, so let’s hold off on that for now.

10. Time Fields

The range of types available for time fields in MySQL is fairly extensive: date, datetime, timestamp, and varchar.

Using varchar might be for API consistency where time data is represented as a string.

However, querying data by time ranges can be inefficient with varchar since it cannot utilize indexes.

Date is intended only for dates (e.g., 2020-08-20), while datetime and timestamp are suited for complete date and time.

There are subtle differences between them.

Timestamp: uses 4 bytes and spans from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07. It’s also timezone-sensitive.

Datetime: occupies 8 bytes with a range from 1000-01-01 00:00:00 to 9999-12-31 23:59:59, independent of time zones.

Using datetime to save date and time is preferable for its wider range.

As a reminder, when setting default values for time fields, avoid using 0000-00-00 00:00:00, which can cause errors during queries.

11. Monetary Fields

MySQL provides several types for floating-point numbers: float, double, decimal, etc.

Given that float and double may lose precision, it’s recommended to use decimal for monetary values.

Typically, floating numbers are defined as decimal(m,n), where n represents the number of decimal places, and m is the total length of both integer and decimal portions.

For example, decimal(10,2) allows for 8 digits before the decimal point and 2 digits after it.

12. JSON Fields

During table structure design, you may encounter fields needing to store variable data values.

For example, in an asynchronous Excel export feature, a field in the async task table may need to save user-selected query conditions, which can vary per user.

Traditional database fields don’t handle this well.

Using MySQL’s json type enables structured data storage in JSON format for easy saving and querying.

MySQL also supports querying JSON data by field names or values.

13. Unique Indexes

Unique indexes are frequently used in practice.

You can apply unique indexes to individual fields, like an organization’s code, or create composite unique indexes for multiple fields, like category numbers, units, specifications, etc.

Unique indexes on individual fields are straightforward, but for composite unique indexes, if any field is NULL, the uniqueness constraint may fail.

Another common issue is having unique indexes while still producing duplicate data.

Due to its complexity, I’ll elaborate on unique index issues in a later article.

When creating unique indexes, ensure that none of the involved fields contain NULL values to maintain their uniqueness.

14. Character Set

MySQL supports various character sets, including latin1, utf-8, utf8mb4, etc.

Here’s a table summarizing MySQL character sets:

Character Set	Description	Encoding Size	Notes
latin1	Encounters encoding issues; rarely used in real projects	1 byte	Limited support for international characters
utf-8	Efficient in storage but cannot store emoji	3 bytes	Suitable for most text but lacks emoji support
utf8mb4	Supports all Unicode characters, including emoji	4 bytes	Recommended for modern applications

It’s advisable to set the character set to utf8mb4 during table creation to avoid potential issues.

15. Collation

When creating tables in MySQL, the COLLATE parameter can be configured.

For example:

CREATE TABLE `order` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(20) COLLATE utf8mb4_bin NOT NULL,
  `name` VARCHAR(30) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_code` (`code`),
  KEY `un_code_name` (`code`,`name`) USING BTREE,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

The collation determines how character sorting and comparison are conducted.

Character collation depends on the character set, which for utf8mb4 would also start with utf8mb4_. Common types include utf8mb4_general_ci and utf8mb4_bin.

The utf8mb4_general_ci collation is case-insensitive for alphabetical characters, while utf8mb4_bin is case-sensitive.

This distinction is important. For example, if the order table contains a record with the name YOYO and you query it using lowercase yoyo under utf8mb4_general_ci, it retrieves the record. Under utf8mb4_bin, it will not.

Choose collation based on the actual business needs to avoid confusion.

16. Large Fields

Special attention is warranted for fields that consume substantial storage space, such as comments.

A user comment field might require limits, like a maximum of 500 characters.

Defining large fields as text can waste storage, thus it’s often better to use varchar for better efficiency.

For much larger data types, like contracts that can take up several MB, it may be unreasonable to store directly in MySQL.

Instead, such data could be stored in MongoDB, with the MySQL business table retaining the MongoDB ID.

17. Redundant Fields

To enhance performance and query speed, some fields can be redundantly stored.

For example, an order table typically contains a userId to identify users.

However, many order query pages also need to display the user ID along with the user’s name.

If both tables are small, a join is feasible, but for large datasets, it can degrade performance.

In that case, creating a redundant userName field in the order table can resolve performance issues.

While this adjustment allows direct querying from the order table without joins, it requires additional storage and may lead to inconsistency if user names change.

Therefore, carefully evaluate if the redundant fields strategy fits your particular business scenario.

18. Comments

When designing tables, ensure to add clear comments for tables and associated fields.

For example:

CREATE TABLE `sys_dept` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` VARCHAR(30) NOT NULL COMMENT 'Name',
  `pid` BIGINT NOT NULL COMMENT 'Parent Department',
  `valid_status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Valid Status: 1=Valid, 0=Invalid',
  `create_user_id` BIGINT NOT NULL COMMENT 'Creator ID',
  `create_user_name` VARCHAR(30) NOT NULL COMMENT 'Creator Name',
  `create_time` DATETIME(3) DEFAULT NULL COMMENT 'Creation Date',
  `update_user_id` BIGINT DEFAULT NULL COMMENT 'Updater ID',
  `update_user_name` VARCHAR(30)  DEFAULT NULL COMMENT 'Updater Name',
  `update_time` DATETIME(3) DEFAULT NULL COMMENT 'Update Time',
  `is_del` TINYINT(1) DEFAULT '0' COMMENT 'Is Deleted: 1=Deleted, 0=Not Deleted',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `index_pid` (`pid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='Department';

Detailed comments clarify the purpose of tables and fields.

Particularly for fields representing statuses (like valid_status), it immediately conveys the intent behind the data, such as indicating valid versus invalid.

Avoid situations where numerous status fields exist without comments, leading to confusion about what values like 1, 2, or 3 signify.

Initially, one might remember, but after a year of operation, it’s easy to forget, potentially leading to significant pitfalls.

Thus, when designing tables, meticulous commenting and regular updates of these comments are essential.

That wraps up the technical section of this article,If you have a different opinion, let me know?.

以上がMySQL でテーブルを作成するときに知っておくべき重要なポイントの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。