MySQL数据库规范有哪些

发布时间：2021-06-24 14:16:00 作者：Leah
来源：亿速云阅读：200

# 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,
     ...
   );

字段命名

字段类型	命名规范示例	说明
主键	id	自增BIGINT
外键	user_id	关联表名+id
布尔类型	is_deleted	前缀is/has
时间类型	create_time	后缀_time/_at
枚举字段	order_status	需注释说明枚举值含义

索引命名

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

表设计规范

基础规范

存储引擎：
- 事务表必须使用InnoDB
- 只读场景可考虑MyISAM（需评估风险）

字符集：

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

必备字段：

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='表设计模板';

字段设计

数值类型：
- 自增ID用BIGINT（避免INT溢出）
- 金额使用DECIMAL(20,4)
字符串类型：
- 变长字符串用VARCHAR（需指定合理长度）
- 大文本使用TEXT并考虑分表
时间类型：
- 精确到秒：DATETIME
- 时间戳：TIMESTAMP（注意时区问题）
禁用类型：
- 禁止使用ENUM（改用TINYINT注释）
- 禁止使用BLOB存业务数据

主键设计

自增主键：

-- 标准自增主键
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID'

分布式ID：

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

复合主键：

-- 关联表可使用复合主键
PRIMARY KEY (user_id, role_id)

索引规范

索引设计原则

单表索引数：不超过5个

索引长度：字符串索引前20字符


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

最左匹配原则：


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

索引使用建议

EXPLN检查：
```
EXPLN SELECT * FROM user WHERE user_id=100;
```
关键指标：
- type至少达到range级别
- Extra避免出现Using filesort
索引失效场景：
- 字段使用函数：WHERE DATE(create_time)='2023-01-01'
- 隐式类型转换：WHERE user_id='123'（user_id为INT）
- 模糊查询：WHERE name LIKE '%张'

SQL语句规范

查询规范

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规范

批量操作： “`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规范

变更流程：
- 预发环境测试
- 低峰期执行
- 备回滚方案

表示例：

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='账户表';

安全规范

权限控制：
- 线上环境禁止使用root账号
- 按最小权限原则授权
```
GRANT SELECT, INSERT ON db1.* TO 'app_user'@'192.168.1.%';
```
SQL注入防护：
- 使用PreparedStatement
- 禁止拼接SQL
敏感数据：
- 密码字段必须加密
- 个人隐私字段需脱敏

性能优化建议

配置优化：

# my.cnf 关键参数
innodb_buffer_pool_size = 12G  # 总内存的50-70%
innodb_log_file_size = 2G
max_connections = 2000

慢查询优化：

-- 开启慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

连接池配置：
- 建议使用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. 完善安全合规相关内容