𝑻𝒆𝒏𝑪𝒍𝒂𝒘正在头脑风暴···

𝑻𝒆𝒏𝑲𝒊𝑺𝒆𝒀𝒂の𝑨𝒈𝒆𝒏𝒕助手

𝑻𝒆𝒏-𝒇𝒍𝒂𝒔𝒉

数据库设计与优化实践

数据库设计是系统架构的核心，良好的设计直接影响应用性能和可维护性。

数据库设计原则

范式化理论

第一范式 (1NF)

-- 不符合 1NF
CREATE TABLE users_orders (
    user_id INT,
    name VARCHAR(100),
    orders TEXT  -- 存储多个订单ID: "1,2,3"
);

-- 符合 1NF
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

第二范式 (2NF)

-- 不符合 2NF（存在部分依赖）
CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- 依赖 product_id，不依赖 order_id
    quantity INT,
    price DECIMAL(10,2)
);

-- 符合 2NF
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2)
);

CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

第三范式 (3NF)

-- 不符合 3NF（存在传递依赖）
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT,
    dept_name VARCHAR(100)  -- 依赖 dept_id
);

-- 符合 3NF
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

反范式化

-- 有时需要反范式化提高性能
CREATE TABLE order_summary (
    order_id INT PRIMARY KEY,
    user_id INT,
    total_amount DECIMAL(10,2),
    item_count INT,  -- 冗余字段，避免重复计算
    created_at TIMESTAMP
);

-- 触发器维护冗余数据
CREATE TRIGGER update_order_summary
AFTER INSERT ON order_details
FOR EACH ROW
BEGIN
    UPDATE order_summary 
    SET total_amount = (
        SELECT SUM(quantity * unit_price) 
        FROM order_details 
        WHERE order_id = NEW.order_id
    ),
    item_count = (
        SELECT COUNT(*) 
        FROM order_details 
        WHERE order_id = NEW.order_id
    )
    WHERE order_id = NEW.order_id;
END;

索引设计

索引类型

-- 普通索引
CREATE INDEX idx_user_email ON users(email);

-- 唯一索引
CREATE UNIQUE INDEX idx_user_username ON users(username);

-- 复合索引
CREATE INDEX idx_order_user_date ON orders(user_id, order_date);

-- 部分索引
CREATE INDEX idx_active_users ON users(id) WHERE status = 'active';

-- 函数索引
CREATE INDEX idx_user_email_lower ON users(LOWER(email));

-- 全文索引
CREATE FULLTEXT INDEX idx_article_content ON articles(title, content);

索引优化策略

-- 最左前缀原则
-- 复合索引 (a, b, c) 可以支持：
-- WHERE a = ?
-- WHERE a = ? AND b = ?
-- WHERE a = ? AND b = ? AND c = ?
-- 但不支持 WHERE b = ? 或 WHERE c = ?

-- 覆盖索引
CREATE INDEX idx_order_covering ON orders(user_id, status, total_amount);
-- 这个索引可以完全满足查询：
-- SELECT user_id, status, total_amount FROM orders WHERE user_id = ?;

-- 索引选择性
SELECT COUNT(DISTINCT email) / COUNT(*) FROM users; -- 越接近 1 越好
-- 选择性高的字段适合建索引

查询优化

执行计划分析

-- MySQL 执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';

-- PostgreSQL 执行计划
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';

-- 关键信息：
-- type: ALL (全表扫描), index (索引扫描), ref (索引查找)
-- rows: 预估扫描行数
-- Extra: Using index, Using temporary, Using filesort

查询优化技巧

-- 避免 SELECT *
SELECT id, name, email FROM users WHERE status = 'active';

-- 使用 LIMIT 限制结果
SELECT id, name FROM users LIMIT 10 OFFSET 20;

-- 合理使用 JOIN
SELECT u.name, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

-- 子查询 vs JOIN
-- 不推荐：
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- 推荐：
SELECT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total > 1000
GROUP BY u.id;

-- 批量插入
INSERT INTO users (name, email) VALUES
('John', 'john@example.com'),
('Jane', 'jane@example.com'),
('Bob', 'bob@example.com');

数据库性能优化

连接池配置

# 数据库连接池配置 (Spring Boot)
spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      idle-timeout: 300000
      max-lifetime: 1200000
      connection-timeout: 20000
      leak-detection-threshold: 60000

分页优化

-- 传统分页（性能问题）
SELECT * FROM orders ORDER BY created_at LIMIT 10 OFFSET 1000000;

-- 优化分页（使用游标）
SELECT * FROM orders 
WHERE created_at > '2024-01-01 00:00:00' 
ORDER BY created_at 
LIMIT 10;

-- 使用 ID 游标
SELECT * FROM orders 
WHERE id > 1000000 
ORDER BY id 
LIMIT 10;

批量操作

-- 批量删除
DELETE FROM orders WHERE status = 'cancelled' AND created_at < '2023-01-01'
LIMIT 1000; -- 分批删除，避免长事务

-- 批量更新
UPDATE products 
SET price = price * 0.9 
WHERE category = 'electronics';

-- 使用临时表优化大数据操作
CREATE TEMPORARY TABLE temp_updates (
    product_id INT,
    new_price DECIMAL(10,2)
);

INSERT INTO temp_updates VALUES (1, 99.99), (2, 149.99);

UPDATE products p
JOIN temp_updates t ON p.id = t.product_id
SET p.price = t.new_price;

分库分表

水平分表

-- 按用户 ID 分表
CREATE TABLE orders_0 LIKE orders;
CREATE TABLE orders_1 LIKE orders;
-- ... orders_9

-- 应用层路由
function getTableSuffix(userId) {
    return userId % 10;
}

// 查询示例
SELECT * FROM orders_5 WHERE user_id = 12345;

垂直分库

-- 用户库
-- users, user_profiles, user_preferences

-- 订单库  
-- orders, order_details, order_status

-- 产品库
-- products, categories, product_attributes

读写分离

# MyBatis 读写分离配置
spring:
  datasource:
    master:
      url: jdbc:mysql://master-db:3306/app
      username: master_user
      password: master_pass
    slave:
      url: jdbc:mysql://slave-db:3306/app
      username: slave_user
      password: slave_pass
      
@ReadOnly
public List<Order> getOrdersByUser(Long userId) {
    // 读操作走从库
}

@WriteOnly  
public void createOrder(Order order) {
    // 写操作走主库
}

缓存策略

查询缓存

-- MySQL 查询缓存
SET GLOBAL query_cache_size = 67108864; -- 64MB
SET GLOBAL query_cache_type = ON;

-- 检查缓存状态
SHOW STATUS LIKE 'Qcache%';

-- 注意：查询缓存在 MySQL 8.0 中已移除

应用层缓存

// Redis 缓存示例
@Service
public class ProductService {
    
    @Cacheable(value = "products", key = "#id")
    public Product getProductById(Long id) {
        return productRepository.findById(id);
    }
    
    @CacheEvict(value = "products", key = "#product.id")
    public void updateProduct(Product product) {
        productRepository.save(product);
    }
    
    @CacheEvict(value = "products", allEntries = true)
    public void clearProductCache() {
        // 清空所有产品缓存
    }
}

监控和维护

性能监控

-- MySQL 性能监控
SHOW PROCESSLIST;  -- 查看当前连接
SHOW ENGINE INNODB STATUS;  -- InnoDB 状态
SHOW VARIABLES LIKE 'innodb%';  -- InnoDB 配置

-- 慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- 2秒以上为慢查询

-- 分析表统计信息
ANALYZE TABLE products;
ANALYZE TABLE orders;

-- 优化表
OPTIMIZE TABLE products;

定期维护

-- 清理过期数据
DELETE FROM log_entries WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 重建索引
ALTER TABLE orders REBUILD INDEX idx_user_date;

-- 检查表完整性
CHECK TABLE orders;
REPAIR TABLE orders;

-- 备份策略
mysqldump --single-transaction --routines --triggers app_db > backup.sql

安全考虑

权限管理

-- 创建应用用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';

-- 最小权限原则
GRANT SELECT, INSERT, UPDATE ON app_db.orders TO 'app_user'@'%';
GRANT SELECT ON app_db.products TO 'app_user'@'%';

-- 限制连接
CREATE USER 'report_user'@'192.168.1.%' IDENTIFIED BY 'password';

数据加密

-- 字段级加密
CREATE TABLE sensitive_data (
    id INT PRIMARY KEY,
    credit_card VARBINARY(255),
    ssn VARBINARY(255)
);

-- 使用应用层加密
// Java 加密示例
String encrypted = AES.encrypt(creditCardNumber, secretKey);

最佳实践总结

合理设计表结构，遵循范式化原则
创建合适的索引，避免过度索引
定期监控性能，及时发现慢查询
使用连接池，避免频繁创建连接
实施缓存策略，减少数据库压力
定期备份，确保数据安全
权限最小化，提高安全性

[!tip]
使用 EXPLAIN 分析查询执行计划
定期优化表结构和索引
监控数据库性能指标
建立完善的备份恢复机制

数据库设计与优化实践

数据库设计原则

范式化理论

第一范式 (1NF)

第二范式 (2NF)

第三范式 (3NF)

反范式化

索引设计

索引类型

索引优化策略

查询优化

执行计划分析

查询优化技巧

数据库性能优化

连接池配置

分页优化

批量操作

分库分表

水平分表

垂直分库

读写分离

缓存策略

查询缓存

应用层缓存

监控和维护

性能监控

定期维护

安全考虑

权限管理

数据加密

最佳实践总结

参考资料