数据库设计规范技能包
设计高效、可维护的数据库结构。
表设计原则
- 命名规范
表名：
-
小写字母 + 下划线
-
使用复数形式
-
示例：users , order_items , user_profiles
字段名：
-
小写字母 + 下划线
-
见名知意
-
示例：created_at , user_id , email_address
索引名：
-- 主键：pk_表名 PRIMARY KEY pk_users
-- 唯一索引：uk_表名_字段名 UNIQUE INDEX uk_users_email
-- 普通索引：idx_表名_字段名 INDEX idx_users_created_at
-- 外键：fk_表名_关联表名 FOREIGN KEY fk_orders_users
- 字段类型选择
数据类型 使用场景 示例
INT/BIGINT ID、数量、年龄 user_id BIGINT
VARCHAR 变长字符串 name VARCHAR(100)
CHAR 定长字符串 country_code CHAR(2)
TEXT 长文本 description TEXT
DECIMAL 金额、精确数值 price DECIMAL(10,2)
DATETIME 日期时间 created_at DATETIME
ENUM 固定选项 status ENUM('active','inactive')
BOOLEAN 布尔值 is_deleted BOOLEAN
注意：
-
避免使用 FLOAT/DOUBLE 存储金额
-
VARCHAR 长度设置合理（避免过大）
-
时间字段统一使用 DATETIME 或 TIMESTAMP
- 主键设计
推荐：
-- 自增ID（适合单机） id BIGINT AUTO_INCREMENT PRIMARY KEY
-- 雪花ID（适合分布式） id BIGINT PRIMARY KEY COMMENT '雪花ID'
-- UUID（分布式场景） id CHAR(36) PRIMARY KEY COMMENT 'UUID'
避免：
-
业务字段作为主键（如手机号、邮箱）
-
联合主键（复杂度高）
- 外键约束
使用外键的优势：
-
数据完整性保证
-
级联操作
不使用外键的场景：
-
高并发系统（性能考虑）
-
分库分表场景
-
微服务架构
-- 外键示例 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
索引设计
- 索引类型
主键索引：
PRIMARY KEY (id)
唯一索引：
UNIQUE INDEX uk_users_email (email)
普通索引：
INDEX idx_users_created_at (created_at)
联合索引（遵循最左前缀原则）：
INDEX idx_users_name_age (name, age) -- 可以走索引: WHERE name = 'xxx' -- 可以走索引: WHERE name = 'xxx' AND age = 25 -- 不走索引: WHERE age = 25
全文索引：
FULLTEXT INDEX ft_articles_content (content)
- 索引优化原则
何时创建索引：
-
✓ WHERE 子句常用字段
-
✓ ORDER BY 字段
-
✓ JOIN 关联字段
-
✓ 查询频率高的字段
何时避免索引：
-
✗ 数据重复度高的字段（如性别）
-
✗ 频繁更新的字段
-
✗ 小表（全表扫描更快）
索引失效场景：
-- ❌ 在索引列上使用函数 WHERE YEAR(created_at) = 2025
-- ✓ 改为 WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'
-- ❌ 模糊查询前导模糊 WHERE name LIKE '%张%'
-- ✓ 改为 WHERE name LIKE '张%'
-- ❌ 类型转换 WHERE user_id = '123' -- user_id 是 INT
-- ✓ 改为 WHERE user_id = 123
SQL 优化
- 查询优化
**避免 SELECT ***：
-- ❌ 不推荐 SELECT * FROM users;
-- ✓ 推荐 SELECT id, name, email FROM users;
使用 LIMIT：
SELECT id, name FROM users LIMIT 100;
避免 N+1 查询：
-- ❌ N+1 问题 SELECT * FROM orders; -- 查询 N 个订单 -- 然后循环查询每个订单的用户 SELECT * FROM users WHERE id = ?;
-- ✓ 使用 JOIN SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id = u.id;
- 分页优化
传统分页（大偏移量性能差）：
SELECT * FROM users LIMIT 100000, 20;
优化方案（使用 ID 范围）：
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 20;
使用覆盖索引：
SELECT id, name FROM users WHERE status = 'active' ORDER BY created_at LIMIT 20;
- COUNT 优化
避免 COUNT(*)：
-- ❌ 慢 SELECT COUNT(*) FROM users WHERE status = 'active';
-- ✓ 使用近似值（如缓存） -- 或者定期统计存到另一个表
范式设计
第一范式（1NF）
每个字段都是不可分割的原子值
第二范式（2NF）
消除部分依赖（非主键字段完全依赖主键）
第三范式（3NF）
消除传递依赖（非主键字段不依赖其他非主键字段）
反范式化
为了性能适当冗余数据：
-- 订单表冗余用户名（避免频繁JOIN） CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT, user_name VARCHAR(100), -- 冗余字段 total_amount DECIMAL(10,2) );
常用字段
标准字段
id BIGINT AUTO_INCREMENT PRIMARY KEY, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, is_deleted BOOLEAN DEFAULT FALSE, created_by BIGINT, updated_by BIGINT
乐观锁
version INT NOT NULL DEFAULT 0
软删除
deleted_at DATETIME NULL, is_deleted BOOLEAN DEFAULT FALSE
分库分表
垂直拆分
按业务模块拆分表
水平拆分
-- 按 user_id 取模 user_0, user_1, user_2, ...
-- 按时间分表 order_202501, order_202502, ...
最佳实践
-
必须字段：id, created_at, updated_at
-
统一字符集：utf8mb4
-
统一时区：UTC
-
避免 NULL：尽量使用 NOT NULL + DEFAULT
-
合理使用注释：COMMENT '字段说明'
-
定期备份：自动化备份机制
-
监控慢查询：开启 slow_query_log