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_，如order_pay、order_pay_detail。

對於與產品相關的表，前綴為product_，例如product_spu、product_sku。

這種做法有助於快速將與同一業務相關的表格分類在一起。

另外，如果非訂單業務可能需要建立名為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。

常規索引和複合索引可以使用 ix_ 前綴，例如 ix_product_status。

唯一索引可以使用ux_前綴，如ux_product_code。

2. 字段類型

設計表格時，選擇欄位類型有足夠的自由度。

時間格式欄位可以是日期、日期時間或時間戳記等

字元資料型別包括varchar、char、text等

數字型別包括 int、bigint、smallint 和tinyint。

選擇合適的欄位類型至關重要。

高估型別（例如，僅儲存 1 到 10 之間的值的欄位使用 bigint）會浪費空間； tinyint 就足夠了。

相反，低估（例如使用int作為18位元ID）會導致資料儲存失敗。

以下是選擇欄位類型的一些原則：

• 在滿足正常業務需求的情況下優先選擇小型儲存空間，從小到大進行選擇。
• 使用 char 表示固定或相似的字串長度，使用 varchar 表示不同的長度。
• 對布林欄位使用位元。
• 枚舉欄位使用tinyint。
• 主鍵欄位選擇bigint。
• 貨幣欄位使用小數。
• 時間欄位使用時間戳記或日期時間。

3. 字段長度

定義欄位名稱並選擇適當的欄位類型後，重點應該轉移到欄位長度，例如 varchar(20) 或 bigint(20)。

varchar 的長度表示什麼－位元組還是字元？

答案：在MySQL中，varchar和char代表字元長度，而大多數其他類型代表位元組長度。

例如bigint(4)指定的是顯示長度，而不是儲存長度，仍然是8個位元組。

如果設定了zerofill屬性，小於4位元組的數字將被填充，但即使填充，底層資料儲存仍為8位元組。

4. 欄位數量

設計表格時，限製欄位數量至關重要。

我看過有幾十個甚至上百個欄位的表，導致資料量大，查詢效率低。

如果發生這種情況，請考慮將大表拆分為較小的表，同時保留公共主鍵。

根據經驗，請將每個表格的欄位數量保持在 20 以下。

5. 主鍵

設定表時建立主鍵。

主鍵本質上帶有主鍵索引，使查詢更加高效，因為它們不需要額外的查找。

在單一資料庫中，主鍵可以使用AUTO_INCREMENT自動成長。

對於分散式資料庫，特別是在分片架構中，最好使用外部演算法（如 Snowflake）來確保全域唯一的 ID。

此外，保持主鍵與業務值無關，以減少耦合並方便將來的擴充。

但是，對於一對一的關係，例如使用者表和使用者擴充表，直接使用使用者表中的主鍵是可以接受的。

6. 儲存引擎

MySQL 8之前，預設儲存引擎是MyISAM；從 MySQL 8 開始，現在是 InnoDB。

歷史上，關於選擇哪種儲存引擎存在著許多爭論。

MyISAM 將索引和資料儲存分離，增強了查詢效能，但缺乏對事務和外鍵的支援。

InnoDB 雖然查詢速度稍慢，但支援事務和外鍵，使其更加健壯。

先前建議對於讀取密集的場景使用 MyISAM，對於寫密集的場景使用 InnoDB。

但是，MySQL 中的最佳化減少了效能差異，因此建議在 MySQL 8 及更高版本中使用預設的 InnoDB 儲存引擎，無需任何額外修改。

7. 不為空

建立欄位時，決定是否可以為NULL。

建議盡可能將欄位定義為 NOT NULL。

為什麼？

在 InnoDB 中，儲存 NULL 值需要額外的空間，而且它們也會導致索引失敗。

NULL 值只能使用 IS NULL 或 IS NOT NULL 來查詢，因為使用 = 總是傳回 false。

因此，只要可行，請將欄位定義為 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)
);

這將建立一個類別表。

然後，可以建立一個引用它的學生表：

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)
);

這裡，學生表中的cid引用了班級表中的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中文網其他相關文章！