数据库设计三要素：范式、权限与查询

目录

一、范式：把“好”结构写进表定义

二、用户与权限：把“谁能干什么”写进系统表

三、条件查询：把“要什么数据”写进 WHERE

四、综合案例：把范式、权限、查询串成一条线

五、结语：理论落地三板斧

一、范式：把“好”结构写进表定义

1.1 为什么要谈范式 把数据拆得合理，可以让更新更快、冗余更少、锁冲突更低；拆得过头，又会让查询变慢、Join 变多。因此，理解范式不是背定义，而是学会在“节省空间”与“查询性能”之间做权衡。

1.2 第一范式（1NF）：列不可再分 定义：关系中的所有字段值都是原子的，不可再拆。 示例： 错误设计：

CREATE TABLE t_order(
order_id INT PRIMARY KEY,
product_list VARCHAR(500) — "苹果,3;香蕉,2"
);

正确做法：

CREATE TABLE t_order_item(
order_id INT,
product_name VARCHAR(50),
qty INT,
PRIMARY KEY(order_id, product_name)
);

常见误区：

• 把 JSON、CSV 直接塞进一个字段，虽然满足了“物理上只有一个值”，但逻辑上仍违反 1NF，导致后续 WHERE、GROUP BY 无法走索引。

• 用 ENUM 存多选值（如 ENUM('A,B','B,C')），同样违反 1NF。

1.3 第二范式（2NF）：消除部分依赖 前提：必须先满足 1NF。 定义：非主键列必须完全依赖于“整个”主键，而不能只依赖主键的一部分。 示例：

CREATE TABLE t_score(
stu_id INT,
course_id INT,
stu_name VARCHAR(20), — 只依赖 stu_id
course_name VARCHAR(50), — 只依赖 course_id
score TINYINT,
PRIMARY KEY(stu_id, course_id)
);

stu_name 只跟 stu_id 有关，course_name 只跟 course_id 有关——存在部分依赖。拆表：

CREATE TABLE t_student(stu_id PK, stu_name);
CREATE TABLE t_course(course_id PK, course_name);
CREATE TABLE t_score(stu_id, course_id, score,
PRIMARY KEY(stu_id, course_id));

常见误区：

• 以为“只要有了自增主键就天然 2NF”。实际上，如果还有 (stu_id, course_id) 这样的联合业务主键，仍需要检查部分依赖。

1.4 第三范式（3NF）：消除传递依赖 前提：必须先满足 2NF。 定义：非主键列不能依赖于其他非主键列。 示例：

CREATE TABLE t_employee(
emp_id INT PRIMARY KEY,
dept_name VARCHAR(30),
dept_addr VARCHAR(100) — 依赖于 dept_name
);

dept_addr 通过 dept_name 传递依赖于 emp_id，违反 3NF。拆表：

CREATE TABLE t_dept(dept_name PK, dept_addr);
CREATE TABLE t_employee(emp_id PK, dept_name,
FOREIGN KEY(dept_name) REFERENCES t_dept);

常见误区：

• 过度追求 3NF 导致“一张业务表拆成十张”，查询时要 N 路 Join，性能雪崩。

• 数据仓库场景通常反范式化，故意冗余以换取分析速度。

1.5 小结：范式与反范式的平衡

• OLTP（交易型）系统：优先 3NF，降低更新异常。

• OLAP（分析型）系统：允许冗余、预聚合，范式可放宽到 2NF 甚至 1NF。

二、用户与权限：把“谁能干什么”写进系统表

2.1 MySQL 权限体系鸟瞰 MySQL 把“账号”与“权限”分开存储：

• 账号：user 表（user + host 组合为 PK）。

• 权限：分 global、db、table、column、routine、proxy 六个粒度，层层细化。

2.2 账号管理实战 （1）创建只读账号

CREATE USER 'report'@'10.%' IDENTIFIED BY 'Rpt#2025';
GRANT SELECT ON sales.* TO 'report'@'10.%';

（2）创建业务账号并限制连接数

CREATE USER 'app'@'192.168.%' IDENTIFIED BY 'App$2025'
WITH MAX_USER_CONNECTIONS 100;
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'192.168.%';

（3）回收权限、删除账号

REVOKE DELETE ON appdb.* FROM 'app'@'192.168.%';
DROP USER 'old_app'@'%';

2.3 权限粒度深入

• 全局：.

• 数据库：db_name.*

• 表：db_name.tbl_name

• 列：SELECT(col1,col2)

• 存储过程：EXECUTE ON PROCEDURE db_name.proc_name

• 代理：PROXY 'root'@'%'

2.4 密码策略与 SSL

SET GLOBAL validate_password.policy = STRONG;
CREATE USER 'secure_user'@'%'
IDENTIFIED BY 'Str0ng!@#'
REQUIRE SSL;

2.5 常见安全误区

• 把业务账号 host 设为 '%'，导致外网可连。

• 使用 GRANT ALL PRIVILEGES ON .，给应用账号最高权限。

• 忘记定期跑 FLUSH PRIVILEGES，导致改表后权限未生效（MySQL 8.0 之后已自动 flush）。

三、条件查询：把“要什么数据”写进 WHERE

3.1 查询执行链路回顾 SQL → 解析器 → 优化器 → 执行器 → 存储引擎。条件查询的优劣直接影响优化器能否使用索引、能否做 ICP（Index Condition Pushdown）。

3.2 单表条件 （1）范围与等值

SELECT * FROM orders
WHERE order_date BETWEEN '2025-07-01' AND '2025-07-31'
AND status = 'PAID';

索引建议：联合索引 (status, order_date)。

（2）模糊匹配

SELECT * FROM products WHERE name LIKE '苹果%';

• % 在前会导致索引失效，除非使用全文索引。

（3）NULL 判断

SELECT * FROM users WHERE deleted_at IS NULL;

MySQL 里 NULL 不等于任何值，包括 NULL 本身。

3.3 多表条件 （1）INNER JOIN + WHERE

SELECT o.order_id, u.user_name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.amount > 1000;

（2）LEFT JOIN 的 WHERE 陷阱

SELECT u.user_name, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000; — 把 LEFT JOIN 变成 INNER JOIN

正确写法：把条件放进 JOIN 子句或用 WHERE o.id IS NULL。

3.4 子查询与派生表

SELECT * FROM products
WHERE category_id IN (
SELECT id FROM categories WHERE name = '手机'
);

MySQL 5.7 之前 IN 子查询可能物化，8.0 支持 semi-join 优化。

3.5 HAVING 与聚合

SELECT user_id, COUNT(*) AS cnt
FROM orders
GROUP BY user_id
HAVING cnt >= 5;

HAVING 在 GROUP BY 之后过滤，与 WHERE 的语义不同。

3.6 联合索引与最左前缀

ALTER TABLE orders ADD INDEX idx_status_amount(status, amount);
SELECT * FROM orders WHERE amount = 100; — 无法用到索引

必须包含最左列 status，才能命中联合索引。

3.7 函数与隐式转换

SELECT * FROM orders WHERE DATE(create_time) = '2025-08-14';

对列做函数会导致索引失效。改写：

SELECT * FROM orders
WHERE create_time >= '2025-08-14 00:00:00'
AND create_time < '2025-08-15 00:00:00';

3.8 分页与深翻页

SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20;

深翻页性能差，可改为“记住上一页最大 id”的游标分页：

SELECT * FROM orders WHERE id < ? ORDER BY id DESC LIMIT 20;

四、综合案例：把范式、权限、查询串成一条线

场景：做一个电商订单报表系统，要求

• 数据不冗余（范式）

• 报表账号只能读（权限）

• 支持按日期、状态、金额多条件查询（查询）

4.1 表设计（满足 3NF）

CREATE TABLE t_user(
id BIGINT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE
);

CREATE TABLE t_product(
id BIGINT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2)
);

CREATE TABLE t_order(
id BIGINT PRIMARY KEY,
user_id BIGINT,
order_time DATETIME,
status ENUM('UNPAID','PAID','CLOSED'),
amount DECIMAL(10,2),
INDEX idx_user_time(user_id, order_time),
INDEX idx_status_amount(status, amount)
);

CREATE TABLE t_order_item(
order_id BIGINT,
product_id BIGINT,
qty INT,
PRIMARY KEY(order_id, product_id),
FOREIGN KEY(order_id) REFERENCES t_order(id)
);

4.2 权限配置

— 只读账号
CREATE USER 'bi_ro'@'10.10.%' IDENTIFIED BY 'BI#2025';
GRANT SELECT ON report.* TO 'bi_ro'@'10.10.%';
— 创建视图屏蔽敏感列
CREATE VIEW report.v_order AS
SELECT o.id, o.order_time, o.status, o.amount, u.name AS user_name
FROM t_order o
JOIN t_user u ON o.user_id = u.id;
GRANT SELECT ON report.v_order TO 'bi_ro'@'10.10.%';

4.3 查询示例 需求：2025 年 8 月已付款订单，金额 > 1000，按金额降序取前 20。

SELECT id, order_time, amount, user_name
FROM report.v_order
WHERE status = 'PAID'
AND order_time BETWEEN '2025-08-01' AND '2025-08-31'
AND amount > 1000
ORDER BY amount DESC
LIMIT 20;

执行计划（EXPLAIN）显示：

• type = range，key = idx_status_amount

• Extra = Using where; Using filesort（金额排序无法利用索引，需回表） 若排序性能瓶颈明显，可在 (status, amount DESC) 建联合索引。

五、结语：理论落地三板斧

把范式、权限、查询三条线串在一起，就能把“写得对”“管得住”“跑得快”同时做到。愿你在下一个需求评审会上，既能指出同事表的冗余，也能在 DBA 面前优雅地拼一把索引。