数据库设计最佳实践

数据库是应用程序的核心组成部分，良好的数据库设计能够提高性能、确保数据一致性并简化维护工作。本文将介绍数据库设计的基本原则、常见模式和最佳实践。

数据库设计原则

1. 规范化

规范化是通过减少数据冗余和提高数据一致性来优化数据库结构的过程。主要包括：

第一范式 (1NF)：确保每列都是原子性的，不可再分
第二范式 (2NF)：满足1NF，且非主键列完全依赖于整个主键
第三范式 (3NF)：满足2NF，且非主键列不依赖于其他非主键列

2. 反规范化

在某些情况下，为了提高查询性能，可以适当反规范化：

sql

-- 规范化设计
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100) UNIQUE
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  total DECIMAL(10, 2),
  order_date DATE,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 反规范化设计（添加冗余数据）
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  user_name VARCHAR(100), -- 冗余用户名，避免连接查询
  total DECIMAL(10, 2),
  order_date DATE,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

常见数据模式

用户和权限模型

sql

-- 用户表
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  avatar_url VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  is_active BOOLEAN DEFAULT TRUE
);

-- 角色表
CREATE TABLE roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 权限表
CREATE TABLE permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL,
  description TEXT,
  resource VARCHAR(50),
  action VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 用户角色关联表
CREATE TABLE user_roles (
  user_id INT,
  role_id INT,
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

-- 角色权限关联表
CREATE TABLE role_permissions (
  role_id INT,
  permission_id INT,
  PRIMARY KEY (role_id, permission_id),
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

电商产品模型

sql

-- 产品分类表
CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  parent_id INT,
  image_url VARCHAR(255),
  sort_order INT DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- 产品表
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  description TEXT,
  short_description VARCHAR(500),
  sku VARCHAR(100) UNIQUE NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  compare_price DECIMAL(10, 2),
  cost DECIMAL(10, 2),
  track_inventory BOOLEAN DEFAULT TRUE,
  weight DECIMAL(8, 2),
  category_id INT,
  featured_image_url VARCHAR(255),
  status ENUM('active', 'inactive', 'draft') DEFAULT 'draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 产品图片表
CREATE TABLE product_images (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  url VARCHAR(255) NOT NULL,
  alt_text VARCHAR(255),
  sort_order INT DEFAULT 0,
  is_featured BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- 产品属性表
CREATE TABLE attributes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  type ENUM('text', 'number', 'boolean', 'select', 'multiselect') NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 产品属性值表
CREATE TABLE product_attributes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  attribute_id INT NOT NULL,
  value TEXT,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (attribute_id) REFERENCES attributes(id) ON DELETE CASCADE
);

订单和支付模型

sql

-- 订单表
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_number VARCHAR(50) UNIQUE NOT NULL,
  user_id INT,
  status ENUM('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
  subtotal DECIMAL(10, 2) NOT NULL,
  tax_amount DECIMAL(10, 2) DEFAULT 0,
  shipping_amount DECIMAL(10, 2) DEFAULT 0,
  discount_amount DECIMAL(10, 2) DEFAULT 0,
  total DECIMAL(10, 2) NOT NULL,
  currency VARCHAR(3) DEFAULT 'USD',
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- 订单项表
CREATE TABLE order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  total DECIMAL(10, 2) NOT NULL,
  product_snapshot JSON, -- 保存产品快照，防止产品信息变更
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

-- 地址表
CREATE TABLE addresses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  type ENUM('billing', 'shipping'),
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  company VARCHAR(100),
  address_line1 VARCHAR(255) NOT NULL,
  address_line2 VARCHAR(255),
  city VARCHAR(100) NOT NULL,
  state VARCHAR(50),
  postal_code VARCHAR(20) NOT NULL,
  country VARCHAR(50) NOT NULL,
  phone VARCHAR(20),
  is_default BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 支付记录表
CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  method ENUM('credit_card', 'paypal', 'stripe', 'bank_transfer') NOT NULL,
  status ENUM('pending', 'processing', 'completed', 'failed', 'refunded') DEFAULT 'pending',
  transaction_id VARCHAR(100),
  gateway_response JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

索引策略

选择合适的索引

sql

-- 主键索引（自动创建）
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY
);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 普通索引（用于查询优化）
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- 复合索引（用于多列查询）
CREATE INDEX idx_orders_status_created_at ON orders(status, created_at);
CREATE INDEX idx_products_category_status ON products(category_id, status);

-- 部分索引（MySQL 8.0+）
CREATE INDEX idx_active_users ON users(id) WHERE is_active = TRUE;

避免过度索引

sql

-- 不推荐的索引（选择性低的列）
CREATE INDEX idx_orders_status ON orders(status); -- 如果status只有几个不同的值

-- 推荐的复合索引
CREATE INDEX idx_orders_status_created_at ON orders(status, created_at);

性能优化

查询优化

sql

-- 避免使用SELECT *
SELECT id, name, email FROM users WHERE id = 1;

-- 使用LIMIT限制结果数量
SELECT id, name FROM products WHERE category_id = 5 LIMIT 10;

-- 使用连接代替子查询（在适当情况下）
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- 使用适当的WHERE条件
SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2023-01-01';

-- 避免在WHERE子句中使用函数
-- 不推荐
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 推荐
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

表分区

sql

-- 按范围分区（MySQL 8.0+）
CREATE TABLE orders (
  id INT AUTO_INCREMENT,
  order_date DATE,
  total DECIMAL(10, 2),
  PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 按列表分区
CREATE TABLE users (
  id INT AUTO_INCREMENT,
  country VARCHAR(50),
  name VARCHAR(100),
  PRIMARY KEY (id, country)
)
PARTITION BY LIST COLUMNS(country) (
  PARTITION p_us VALUES IN ('USA', 'Canada'),
  PARTITION p_eu VALUES IN ('UK', 'Germany', 'France'),
  PARTITION p_other VALUES IN (DEFAULT)
);

数据完整性

约束

sql

-- NOT NULL 约束
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL
);

-- UNIQUE 约束
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE NOT NULL
);

-- FOREIGN KEY 约束
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- CHECK 约束（MySQL 8.0+）
CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10, 2) CHECK (price > 0),
  quantity INT CHECK (quantity >= 0)
);

触发器

sql

-- 自动更新时间戳触发器
DELIMITER //
CREATE TRIGGER before_users_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  SET NEW.updated_at = CURRENT_TIMESTAMP;
END//
DELIMITER ;

-- 产品库存更新触发器
DELIMITER //
CREATE TRIGGER after_order_item_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
  UPDATE products 
  SET quantity = quantity - NEW.quantity 
  WHERE id = NEW.product_id;
END//
DELIMITER ;

迁移和版本控制

数据库迁移脚本示例

sql

-- 001_create_users_table.sql
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 002_add_is_active_to_users.sql
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

-- 003_add_user_roles_table.sql
CREATE TABLE roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE user_roles (
  user_id INT,
  role_id INT,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

备份和恢复

备份策略

bash

# 全量备份
mysqldump -u username -p database_name > backup_$(date +%Y%m%d).sql

# 仅备份数据
mysqldump -u username -p --no-create-info database_name > data_$(date +%Y%m%d).sql

# 仅备份结构
mysqldump -u username -p --no-data database_name > structure_$(date +%Y%m%d).sql

# 压缩备份
mysqldump -u username -p database_name | gzip > backup_$(date +%Y%m%d).sql.gz

恢复数据库

bash

# 恢复数据库
mysql -u username -p database_name < backup_20230701.sql

# 从压缩备份恢复
gunzip < backup_20230701.sql.gz | mysql -u username -p database_name

安全考虑

敏感数据加密

sql

-- 使用AES加密敏感数据
INSERT INTO users (username, email, password_hash, ssn)
VALUES (
  'john_doe',
  'john@example.com',
  'hashed_password',
  AES_ENCRYPT('123-45-6789', 'encryption_key')
);

-- 查询时解密
SELECT username, email, AES_DECRYPT(ssn, 'encryption_key') AS ssn
FROM users
WHERE id = 1;

最小权限原则

sql

-- 创建只读用户
CREATE USER 'readonly'@'%' IDENTIFIED BY 'password';
GRANT SELECT ON database_name.* TO 'readonly'@'%';

-- 创建应用用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'app_user'@'%';

-- 创建管理员用户
CREATE USER 'admin'@'%' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON database_name.* TO 'admin'@'%';

总结

良好的数据库设计是应用程序成功的基石。通过遵循规范化原则、选择合适的数据模式、优化索引和查询、确保数据完整性以及实施适当的安全措施，我们可以构建出高效、可靠且安全的数据存储系统。

记住，数据库设计是一个迭代过程，需要根据实际需求和性能反馈不断调整和优化。定期审查数据库设计，清理不再需要的索引和表，保持数据库的健康状态。

继续探索数据库设计的更多实践，构建稳健的后端系统！

数据库设计最佳实践 ​

数据库设计原则 ​

1. 规范化 ​

2. 反规范化 ​

常见数据模式 ​

用户和权限模型 ​

电商产品模型 ​

订单和支付模型 ​

索引策略 ​

选择合适的索引 ​

避免过度索引 ​

性能优化 ​

查询优化 ​

表分区 ​

数据完整性 ​

约束 ​

触发器 ​

迁移和版本控制 ​

数据库迁移脚本示例 ​

备份和恢复 ​

备份策略 ​

恢复数据库 ​

安全考虑 ​

敏感数据加密 ​

最小权限原则 ​

总结 ​