/database-design

name: database-design
description: 数据库设计规范与优化指南
version: 1.0.0
category: development
triggers:
  - database design
  - 数据库设计
  - SQL 优化
  - 表设计
  - 数据库规范
scriptPath: check-db.sh
scriptType: bash
autoExecute: true
scriptTimeout: 10

数据库设计规范技能包

设计高效、可维护的数据库结构。

表设计原则

1. 命名规范

**表名**：

• 小写字母 + 下划线
• 使用复数形式
• 示例：`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

2. 字段类型选择

| 数据类型 | 使用场景 | 示例 | |---------|---------|------| | **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

3. 主键设计

**推荐**：

-- 自增ID（适合单机）
id BIGINT AUTO_INCREMENT PRIMARY KEY

-- 雪花ID（适合分布式）
id BIGINT PRIMARY KEY COMMENT '雪花ID'

-- UUID（分布式场景）
id CHAR(36) PRIMARY KEY COMMENT 'UUID'

**避免**：

• 业务字段作为主键（如手机号、邮箱）
• 联合主键（复杂度高）

4. 外键约束

**使用外键的优势**：

• 数据完整性保证
• 级联操作

**不使用外键的场景**：

• 高并发系统（性能考虑）
• 分库分表场景
• 微服务架构

-- 外键示例
FOREIGN KEY (user_id) 
  REFERENCES users(id) 
  ON DELETE CASCADE 
  ON UPDATE CASCADE

索引设计

1. 索引类型

**主键索引**：

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)

2. 索引优化原则

**何时创建索引**：

• ✓ 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 优化

1. 查询优化

**避免 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;

2. 分页优化

**传统分页**（大偏移量性能差）：

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;

3. 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, ...

最佳实践

1. **必须字段**：id, created_at, updated_at 2. **统一字符集**：utf8mb4 3. **统一时区**：UTC 4. **避免 NULL**：尽量使用 NOT NULL + DEFAULT 5. **合理使用注释**：COMMENT '字段说明' 6. **定期备份**：自动化备份机制 7. **监控慢查询**：开启 slow_query_log