您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 數據庫設計規範：命名、建模、類型、索引、DDL 全維度標準化指南

編輯：MySQL 資料庫相關知識

一、命名規範（強制）

對象	最大長度	字符集	風格	示例
資料庫	64	[a-z_]	小寫+下劃線	biz_order
表	64	[a-z_]	小寫+下劃線，模塊前綴	order_info、order_item
列	64	[a-z_]	小寫+下劃線，語義清晰	create_time、user_id
索引	64	[a-z_]	idx_表_列 / uk_表_列	idx_order_user_id、uk_user_mobile
別名	255	[a-z_]	小寫+下劃線	o、oi

命名空間建議：資料庫::表::列，如 biz_order::order_info::user_id。

二、通用列命名標準庫

業務含義	標準列名	類型	備註
主鍵 ID	id	BIGINT UNSIGNED	自增、無業務含義
名稱/標題	name	VARCHAR(128)
創建時間	create_time	DATETIME	默認 CURRENT_TIMESTAMP
更新時間	update_time	DATETIME	默認 CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
過期時間	expire_time	DATETIME	可空
狀態	status	TINYINT	0 正常、1 隱藏、2 刪除（邏輯刪除）
IP 地址	ip	CHAR(15) / VARBINARY(16)	IPv4 用 CHAR(15)，IPv6 用 VARBINARY(16)
資源/圖片 ID	resource_id	BIGINT UNSIGNED	關聯資源表
標簽	tag	VARCHAR(64)
類型	type	TINYINT	枚舉值在字典表維護

三、索引命名規範

普通索引：idx_表名_列名[_列名2]
唯一索引：uk_表名_列名[_列名2]
主鍵：隱式 PRIMARY，不另行命名。

四、特殊表前綴

日志表：log_，如 log_user_login、log_api_access。
模塊表：以模塊英文縮寫開頭，如 award_、award_exchange。
備份表：原表名_YYYYMMDD，如 order_info_20240115。

五、關聯鍵選擇原則

優先使用 username（或業務唯一鍵）關聯，而非 user_id。
原因：大多數查詢已攜帶 username，避免額外 JOIN user 表反查，減少 IO 與延遲。
例外：高頻更新、username 長度大、需屏蔽業務含義時用 user_id。

六、設計原則：性能優先，適度反範式

適度冗余，減少 JOIN：MySQL JOIN 性能弱於單表查詢，將高頻查詢所需列冗余到主表（如訂單表冗買家 nickname、賣家 shop_name）。
大字段垂直拆分：TEXT/BLOB/長 JSON 移至獨立表（order_detail、article_content），主表保持窄行，提升緩存命中與掃描速度。
選擇最小足夠類型：
- 數值：TINYINT(1B) SMALLINT(2B) MEDIUMINT(3B) INT(4B) BIGINT(8B)，能用小的不用大的。
- 字符：定長 CHAR(M) 適合長度固定（MD5、UUID、手機號），變長 VARCHAR(M) 適合不定長。5.0.3+ VARCHAR(M) 按字節數限制，utf8mb4 下 M=191 佔 764 字節。
- 大文本：TEXT 族（TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT）按需選擇，不能設默認值、索引需指定前綴長度。
- 避免 ENUM、SET：擴展需 DDL，改用 TINYINT + 字典表。
默認值與 NOT NULL：除 AUTO_INCREMENT、TEXT/BLOB、TIMESTAMP 外，**所有列必須顯式指定 DEFAULT**，並盡量 NOT NULL。NULL 額外佔 1 bit、索引統計複雜、COUNT(col) 不計 NULL。
固定長度表優化：若表無可變長列（無 VARCHAR/TEXT/BLOB），全表行長固定，MyISAM/InnoDB 掃描、恢復更快。可考慮將低頻變長列拆出或轉 CHAR。

七、數據類型深度避坑

CHAR vs VARCHAR 存儲行為差異

特性	CHAR(M)	VARCHAR(M)
存儲長度	固定 M 字符，不足右補空格	實際長度 + 1~2 字節長度前綴
尾部空格	讀取時自動去除	保留
最大 M (utf8mb4)	255 字符 (1020 字節)	16383 字符 (65532 字節)
索引	全長索引	可指定前綴長度

INT(M) 顯示寬度誤區

INT(4)、INT(10) **不限制取值範圍**，範圍永遠是 -2^31 ~ 2^31-1（有符號）或 0 ~ 2^32-1（無符號）。M 僅配合 ZEROFILL 填充前導零（如 INT(5) ZEROFILL 存 4 顯示 00004），生產禁用 ZEROFILL（隱式加 UNSIGNED、臨時表易出 Bug）。

八、索引設計鐵律

高頻 WHERE/JOIN/ORDER BY 列建索引。
低基數列（性別、狀態、類型）嚴禁單列索引，優化器不會用，甚至誤用導致全表掃描。
高頻更新列慎建索引：每次更新需同步修改 B+ 樹，放大寫入延遲。
不在 WHERE 出現的列不建索引。
聯合索引最左前綴原則：idx(a,b,c) 支援 a、a,b、a,b,c，不支援 b、c、b,c。
MySQL 索引限制速查：
- MyISAM 索引總長 ≤ 1000 字節。
- BLOB/TEXT 僅支援前綴索引。
- 不支援函數索引（INDEX ((col+1)) 無效，5.7+ 可用虛擬列繞過）。
- !=、<>、IS NULL、LIKE '%abc'、函數運算列、JOIN 條件類型不一致 → **全不走索引**。
- Hash 索引（MEMORY 引擎）僅支援 =/<=>/IN，不支援範圍/排序。

九、標準建表示例

CREATE TABLE `admin` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `role_id` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '角色ID',
  `username` CHAR(16) NOT NULL COMMENT '用戶名',
  `password` CHAR(33) NOT NULL COMMENT '密碼哈希',
  `acl` TEXT NOT NULL COMMENT '權限位圖JSON',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理員表';