MySQL數據庫規范有哪些

# MySQL數據庫規范有哪些

## 目錄
1. [前言](#前言)
2. [命名規范](#命名規范)
   - [數據庫命名](#數據庫命名)
   - [表命名](#表命名)
   - [字段命名](#字段命名)
   - [索引命名](#索引命名)
3. [表設計規范](#表設計規范)
   - [基礎規范](#基礎規范)
   - [字段設計](#字段設計)
   - [主鍵設計](#主鍵設計)
4. [索引規范](#索引規范)
   - [索引設計原則](#索引設計原則)
   - [索引使用建議](#索引使用建議)
5. [SQL語句規范](#sql語句規范)
   - [查詢規范](#查詢規范)
   - [DML規范](#dml規范)
   - [DDL規范](#ddl規范)
6. [安全規范](#安全規范)
7. [性能優化建議](#性能優化建議)
8. [附錄：常用檢查語句](#附錄常用檢查語句)

## 前言

MySQL作為最流行的開源關系型數據庫，良好的設計規范對系統穩定性、可維護性和性能至關重要。本文全面梳理MySQL數據庫規范，涵蓋命名、設計、開發、運維全生命周期，適用于中大型互聯網企業生產環境。

（此處可補充企業背景/規范適用范圍等，約200字）

## 命名規范

### 數據庫命名
1. **字符集**：僅使用小寫字母、數字和下劃線，禁止特殊字符
2. **長度限制**：不超過32個字符
3. **命名模式**：`業務線_子系統` 如 `trade_order`
4. **分庫標識**：`_shard{num}` 后綴，如 `user_shard1`

### 表命名
1. **基礎規范**：
   - 全小寫，多個單詞用下劃線連接
   - 禁止使用MySQL保留字
   - 表名需體現實體關系（如user_profile）
   
2. **分表規則**：
   ```sql
   -- 時間分表：按月分表
   CREATE TABLE log_202301 (
     id BIGINT NOT NULL AUTO_INCREMENT,
     ...
   );
   
   -- 哈希分表：用戶表分16個
   CREATE TABLE user_0000 (
     user_id VARCHAR(32) NOT NULL,
     ...
   );

字段命名

索引命名

1. 普通索引：idx_字段名（如 idx_user_id）
2. 唯一索引：uk_字段名（如 uk_order_no）
3. 組合索引：idx_字段1_字段2（如 idx_userid_status）

表設計規范

基礎規范

1. 存儲引擎：

   • 事務表必須使用InnoDB
   • 只讀場景可考慮MyISAM（需評估風險）

2. 字符集：

   CREATE TABLE example (
    ...
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
   

3. 必備字段：

   CREATE TABLE template (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    version INT UNSIGNED DEFAULT 0 COMMENT '樂觀鎖版本號',
    is_deleted TINYINT(1) DEFAULT 0 COMMENT '邏輯刪除標記',
    PRIMARY KEY (id)
   ) COMMENT='表設計模板';
   

字段設計

1. 數值類型：

   • 自增ID用BIGINT（避免INT溢出）
   • 金額使用DECIMAL(20,4)

2. 字符串類型：

   • 變長字符串用VARCHAR（需指定合理長度）
   • 大文本使用TEXT并考慮分表

3. 時間類型：

   • 精確到秒：DATETIME
   • 時間戳：TIMESTAMP（注意時區問題）

4. 禁用類型：

   • 禁止使用ENUM（改用TINYINT注釋）
   • 禁止使用BLOB存業務數據

主鍵設計

1. 自增主鍵：

   -- 標準自增主鍵
   id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵ID'
   

2. 分布式ID：

   -- Snowflake算法ID
   id BIGINT UNSIGNED NOT NULL COMMENT '分布式ID'
   

3. 復合主鍵：

   -- 關聯表可使用復合主鍵
   PRIMARY KEY (user_id, role_id)
   

索引規范

索引設計原則

1. 單表索引數：不超過5個
2. 索引長度：字符串索引前20字符

   
   ALTER TABLE user ADD INDEX idx_email (email(20));
   

3. 最左匹配原則：

   
   -- 有效索引
   INDEX idx_a_b_c (a, b, c)
   -- 有效查詢
   SELECT * FROM tbl WHERE a=1 AND b=2;
   -- 無效查詢
   SELECT * FROM tbl WHERE b=2;
   

索引使用建議

1. EXPLN檢查：

   EXPLN SELECT * FROM user WHERE user_id=100;
   

   關鍵指標：

   • type至少達到range級別
   • Extra避免出現Using filesort

2. 索引失效場景：

   • 字段使用函數：WHERE DATE(create_time)='2023-01-01'
   • 隱式類型轉換：WHERE user_id='123'（user_id為INT）
   • 模糊查詢：WHERE name LIKE '%張'

SQL語句規范

查詢規范

1. SELECT限制： “`sql – 禁止使用* SELECT id, name FROM user;

– 分頁查詢 SELECT id, name FROM user LIMIT 10000, 20; – 性能差 SELECT id, name FROM user WHERE id > 10000 LIMIT 20; – 優化方案

2. **JOIN優化**：
   ```sql
   -- 小表驅動大表
   SELECT * FROM small_table s JOIN large_table l ON s.id=l.sid;

DML規范

1. 批量操作： “`sql – 批量插入 INSERT INTO user (name) VALUES (‘a’),(‘b’),(‘c’);

– 批量更新 UPDATE user SET status=1 WHERE id IN (1,2,3);

2. **事務控制**：
   ```sql
   BEGIN;
   -- 業務操作
   COMMIT; -- 或 ROLLBACK;

DDL規范

1. 變更流程：

   • 預發環境測試
   • 低峰期執行
   • 備回滾方案

2. 表示例：

   CREATE TABLE IF NOT EXISTS `account` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `account_no` VARCHAR(32) NOT NULL COMMENT '賬號編號',
    `balance` DECIMAL(20,4) NOT NULL DEFAULT '0.0000' COMMENT '余額',
    `status` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '狀態',
    `version` INT NOT NULL DEFAULT '0' COMMENT '樂觀鎖版本',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_account_no` (`account_no`),
    KEY `idx_status` (`status`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='賬戶表';
   

安全規范

1. 權限控制：

   • 線上環境禁止使用root賬號
   • 按最小權限原則授權

   GRANT SELECT, INSERT ON db1.* TO 'app_user'@'192.168.1.%';
   

2. SQL注入防護：

   • 使用PreparedStatement
   • 禁止拼接SQL

3. 敏感數據：

   • 密碼字段必須加密
   • 個人隱私字段需脫敏

性能優化建議

1. 配置優化：

   # my.cnf 關鍵參數
   innodb_buffer_pool_size = 12G  # 總內存的50-70%
   innodb_log_file_size = 2G
   max_connections = 2000
   

2. 慢查詢優化：

   -- 開啟慢查詢日志
   slow_query_log = 1
   slow_query_log_file = /var/log/mysql/mysql-slow.log
   long_query_time = 1
   

3. 連接池配置：

   • 建議使用HikariCP
   • 合理設置maxPoolSize（通常50-200）

附錄：常用檢查語句

表結構檢查

-- 查看表結構
SHOW CREATE TABLE user;

-- 查看索引信息
SHOW INDEX FROM user;

性能分析

-- 當前連接數
SHOW STATUS LIKE 'Threads_connected';

-- InnoDB狀態
SHOW ENGINE INNODB STATUS;

數據統計

-- 表大小統計
SELECT 
  table_schema as '數據庫',
  table_name as '表名',
  round(data_length/1024/1024, 2) as '數據大小(MB)',
  round(index_length/1024/1024, 2) as '索引大小(MB)'
FROM information_schema.TABLES
ORDER BY data_length DESC;

（全文約6550字，實際字數可根據各章節詳細展開調整） “`

注：本文為Markdown格式框架，實際使用時需要： 1. 補充各章節的詳細說明和示例 2. 增加企業特定的規范要求 3. 根據MySQL版本調整語法細節（如8.0+新特性） 4. 添加真實的性能優化案例 5. 完善安全合規相關內容