MySQL数据库表设计原则与聚合函数

数据库表设计基础原则

第一范式（1NF）解析

第一范式要求确保数据原子性，消除重复组。具体表现为：

1. 每个字段只能存储单一值
2. 每列需要保持数据类型一致
3. 消除重复的列

问题案例：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    items VARCHAR(200)  -- 存储多个商品ID，如 "A001,B005,C012"
);

优化方案：

CREATE TABLE orders (
    order_id INT PRIMARY KEY
);

CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id VARCHAR(10),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

第二范式（2NF）实践

要求满足1NF且消除部分依赖，确保每个非主属性完全依赖于主键。

典型违反场景：

CREATE TABLE sales (
    sale_id INT,
    product_id INT,
    product_name VARCHAR(50),
    quantity INT,
    PRIMARY KEY (sale_id, product_id)
);

改进方案：

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50)
);

CREATE TABLE sales (
    sale_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (sale_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

第三范式（3NF）实现

在满足2NF基础上消除传递依赖，确保非主属性之间没有依赖关系。

问题表结构：

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    dept_id INT,
    dept_location VARCHAR(50)
);

优化方案：

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    location VARCHAR(50)
);

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

实体关系建模

关系类型实现

一对一关系

典型应用场景：用户表与隐私信息表

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE user_private (
    user_id INT PRIMARY KEY,
    ssn VARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

一对多关系

订单与订单项的实现：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE
);

CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

多对多关系

学生选课系统实现：

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    title VARCHAR(100)
);

CREATE TABLE enrollment (
    student_id INT,
    course_id INT,
    enroll_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

聚合函数深度应用

COUNT函数变体

-- 统计总记录数
SELECT COUNT(*) FROM orders;

-- 统计非空值数量
SELECT COUNT(ship_date) FROM orders;

-- 统计唯一值数量
SELECT COUNT(DISTINCT customer_id) FROM orders;

SUM函数实践

-- 简单求和
SELECT SUM(amount) FROM payments;

-- 条件求和
SELECT SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) 
FROM orders;

-- 多列求和
SELECT SUM(quantity * unit_price) AS total_value 
FROM order_items;

AVG函数注意事项

-- 基础平均值计算
SELECT AVG(rating) FROM product_reviews;

-- 排除极端值
SELECT AVG(rating) 
FROM (
    SELECT rating 
    FROM product_reviews
    WHERE rating BETWEEN 3 AND 5
) filtered_ratings;

-- 加权平均值
SELECT SUM(score * weight) / SUM(weight) AS weighted_avg
FROM exam_scores;

MAX/MIN函数技巧

-- 获取最新订单日期
SELECT MAX(order_date) FROM orders;

-- 获取最早注册用户
SELECT MIN(registration_date) FROM users;

-- 结合其他字段查询
SELECT * FROM products 
WHERE price = (SELECT MAX(price) FROM products);

高级聚合应用

GROUP BY子句优化

-- 多字段分组
SELECT department_id, job_title, COUNT(*)
FROM employees
GROUP BY department_id, job_title;

-- 使用表达式分组
SELECT YEAR(order_date) AS order_year, COUNT(*)
FROM orders
GROUP BY order_year;

-- WITH ROLLUP扩展
SELECT department_id, job_title, COUNT(*)
FROM employees
GROUP BY department_id, job_title WITH ROLLUP;

HAVING条件过滤

-- 筛选总销售额超过10000的客户
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING total > 10000;

-- 组合条件筛选
SELECT product_id, AVG(rating) AS avg_rating
FROM reviews
GROUP BY product_id
HAVING COUNT(*) >= 5 AND avg_rating >= 4.0;

索引设计建议

1. 为WHERE和JOIN条件字段创建索引
2. 对排序字段建立索引
3. 复合索引遵循最左前缀原则
4. 避免过度索引写频繁的表

-- 创建复合索引示例
CREATE INDEX idx_orders_date_status 
ON orders (order_date, status);

-- 分析索引使用情况
EXPLAIN SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

性能优化策略

1. 避免SELECT * 查询
2. 合理使用LIMIT分页
3. 优化子查询为JOIN操作
4. 定期分析表统计信息
5. 使用覆盖索引减少IO

-- 查询优化示例
SELECT o.order_id, c.name 
FROM orders o
JOIN customers c USING (customer_id)
WHERE o.status = 'shipped'
ORDER BY o.order_date DESC
LIMIT 100;

事务处理实践

START TRANSACTION;

UPDATE accounts 
SET balance = balance - 100 
WHERE account_id = 123;

UPDATE accounts 
SET balance = balance + 100 
WHERE account_id = 456;

COMMIT;

设计模式推荐

1. 软删除模式

ALTER TABLE users ADD COLUMN is_deleted TINYINT DEFAULT 0;

2. 审计追踪模式

CREATE TABLE audit_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(50),
    record_id INT,
    action_type ENUM('INSERT','UPDATE','DELETE'),
    action_time DATETIME,
    user_id INT
);

3. 多租户架构

ALTER TABLE all_tables ADD COLUMN tenant_id INT NOT NULL;