数据库设计mysql篇
介绍
数据库设计是软件开发和系统架构的核心环节，本文介绍表设计、表事务。
表设计
1、三大范式
第一范式：字段存储原子性
表中每个字段都是原子的，只存储单一值，不可再分。
如联系方式字段正常不可同时存储手机号及邮箱，需拆分为两个字段。
第二范式：非主属性应完全依赖于主属性
满足第一范式的前提下，表必须有主键（或唯一标识），且非主键字段必须完全依赖于整个主键，不能只依赖于主键的一部分（针对复合主键）。
如设计一张订单明细表order_details(order_id, product_id, product_name, quantity, unitprice)，主键为复合主键（order_id，product_id）,其中product_name其实只跟product_id有关，product_name属于冗余字段，后续如果修改product_name，订单明细表需要同时更新一遍。
应该要拆分成两个表：order_details(order_id, product_id, quantity, unitprice)（主键order_id、product_id），products(product_id，product_name)（主键product_id）
第三范式：所有非主键字段之间，不能存在传递依赖
满足第二范式的前提下，所有非主键字段之间，不能存在传递依赖。即非主键字段不能依赖于其他非主键字段。
如学生表students(student_id, student_name, department_id, department_name, department_location)，主键为student_id，但是department_name和department_location依赖于非主键department_id，department_id依赖于student_id，即出现了非主键字段依赖于非主键字段。
应该要拆分成两个表：students(student_id, student_name, department_id)（主键student_id），departments(department_id，department_name，department_location)（主键department_id）
第三.五范式（巴斯-科德范式）：解决主属性之间依赖问题
巴斯-科德范式是一种比第三范式更严格的数据库规范化形式，核心要求是：任何能够决定其他属性的属性集（决定因素）本身必须是候选键（唯一键），即：若X能决定其他属性，则X必须能唯一标识整个记录（元组），若违反此规则，会导致数据冗余和操作异常（插入、更新、删除异常）。
案例：假设某企业的配件管理表如下：
| 仓库号 (WNO) | 配件号 (PNO) | 职工号 (ENO) | 库存量 (QNT) |
|---|---|---|---|
| WH01 | P100 | E001 | 50 |
| WH01 | P200 | E001 | 30 |
| WH02 | P100 | E002 | 40 |
| WH02 | P300 | E003 | 60 |
依赖关系分析：通过仓库号+配件号或职工号+配件号，均可唯一识别一行。
- ENO->WNO: 一个职工仅属于一个仓库，一个仓库可以有多个职工，通过职工可以得出所属的仓库。
- WNO+PNO->ENO: 一个仓库+配件由专门职工管理，通过仓库和配件可以得出职工
- WNO+PNO->QNT: 一个仓库+配件决定了库存量，通过仓库加配件可以算出当前仓库这个配件的库存量。
- ENO+PNO->QNT: 职工+配件决定这个配件的当前仓库的库存量
其中仓库号+配件号、职工号+配件号都是主属性（均属于候选键）、库存量是非主属性，这里的非主属性（库存量）直接依赖于候选键，所以是满足第三范式要求。但是候选键字段之间存在传递依赖，如通过仓库号+配件号可以得出职工号，但是通过职工号可以得出相同的多行仓库号（因为一个职工可以在一个仓库中管理多个配件），这违反了巴斯-科德范式（任何能够决定其他属性的属性集（决定因素）本身必须是候选键（唯一键），即：若X能决定其他属性，则X必须能唯一标识整个记录）
为满足巴斯-科德范式，将原表拆分为两个新表，确保每个表的决定因素都是候选键
职工-仓库分配表：通过候选键职工号唯一确定仓库号
| 职工号 (ENO) | 仓库号 (WNO) |
|---|---|
| E001 | WH01 |
| E002 | WH02 |
| E003 | WH02 |
职工-配件库存表 ：通过候选键职工号+配件号唯一确定库存量
| 职工号 (ENO) | 配件号 (PNO) | 库存量 (QNT) |
|---|---|---|
| E001 | P100 | 50 |
| E001 | P200 | 30 |
| E002 | P100 | 40 |
| E003 | P300 | 60 |
分表后仓库信息只存储一次，消除了冗余，并带来如下便利
- 插入新仓库：直接向
职工-仓库分配表插入(ENew, WH03)，无需配件记录5。 - 职工调仓：只需修改
职工-仓库分配表中的1条记录8。 - 删除配件：仅影响
职工-配件库存表表，不丢失职工所属仓库信息。
2、反范式化设计场景
只有在明确性能瓶颈且规范化设计无法满足时才考虑反范式化，如下场景：
- 读密集型应用(OLAP): 频繁的复杂查询连接多个表开销大。
- 报表需求: 需要快速生成包含多个关联信息的报表。
- 特定高频查询优化。
反范式化手段可以通过冗余字段或设计汇总表/物化视图，要注意需要确保冗余数据与原数据同步（通过应用逻辑、触发器或定期任务），增加开发和维护复杂度。容易导致更新异常。
3、其他注意事项
1、表名、字段名命名：
应该使用有意义的英文单词，多个单词使用下划线连接或使用驼峰命名，避免使用数据库的保留字命名，否则会导致后续执行sql语句时报错被认为是保留字。添加必要的注释，解释其含义和用途。
2、外键：
慎重添加外键，虽然可以建立表与表之间的关系，确保引用完整性，保证数据的一致性，但可能影响写性能（锁、级联操作）。在极高并发或分布式系统有时在应用层控制，并不需要外键。
3、数据类型：
最好选择能精确存储数据的最小数据类型
如TINYINT代替INT存状态码：
-
TINYINT占1字节，有符号：-128 到 127，无符号：0 到 255，适合如订单状态：0=未支付, 1=已支付, 2=已取消
-
INT占4字节，有符号：-2,147,483,648 到 2,147,483,647，无符号：0 到 4,294,967,295，适合自增主键（如
id INT AUTO_INCREMENT），大范围数值（如商品库存量、用户积分）
VARCHAR(n)代替CHAR(n)存变长字符串
VARCHAR(n)变长存储（实际长度 + 内容），适合存储长度变化大的数据（如用户名、地址），最大长度：65,535 字节（受行大小限制）
-- 如存储邮箱平均长度20字节，
-- 虽然定义了100但根据内容实际存储：20字节 + 1字节长度前缀
email VARCHAR(100) NOT NULL
CHAR(n)定长存储（固定占用 n 字节），始终占用 n 字节（自动用空格填充），适合存储长度固定的数据（如 MD5 哈希值、国家代码）
-- 存储国家代码（固定2字符）
country_code CHAR(2) NOT NULL -- 始终占用2字节
4、避免null :
除非必要，否则字段应该定义为not null。null是需要额外的存储空间，且后续的查询逻辑会更复杂（需要 is null）
5、日期时间：
使用专门的日期时间类型（DATE, TIME, DATETIME, TIMESTAMP），避免字符串存储。
表事务
1、介绍
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
2、事务特性
1、四大特性介绍
ACID
- 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
- 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
- 隔离性（Isolation）：数据库提供的隔离机制，并发执行的事务之间相互隔离，一个事务的执行不应影响其他事务。通过隔离级别实现。
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。即使系统故障也不会丢失。
实现这四大特性充分利用了缓冲池和数据页，**缓冲池 (buffer pool)**是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度，缓存池中的数据属于数据页的内存副本，**数据页 (page)**是InnoDB存储引擎的磁盘管理的最小单元（为磁盘文件（.ibd 文件）），每个页的大小默认为16KB，页中存储的是行数据、存储表数据、索引、回滚undo log 等。读写速度较慢，取决于磁盘IO。
2、原子性和一致性分析
通过回滚日志undo log、重做日志redo log保证数据的原子性和一致性。
undo log即回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和多版本并发控制（MVCC–Multi-Version Concurrency Control）。undo log 对比redo log 不一样，undo log记录逻辑日志，redo log记录物理日志（redo log称为重做日志，用于重做已提交但未刷新到硬盘的事务）。
使用场景如下：
- 当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录用于后续回滚，反之亦然。
- 当 update 一条记录时，它记录一条对应相反的 update 记录。当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。
- 系统崩溃重启后扫描Redo Log，重做已提交但未刷盘的事务。
- 系统崩溃重启后扫描Undo Log, 回滚未提交的事务。
3、隔离性分析
（1）脏读、不可重复读、幻读问题
为什么需要保证隔离性，因为并发事务产生时容易产生的一些隔离性问题，如：脏读、不可重复读、幻读。
**脏读：**指一个事务读到另外一个事务还没有提交的数据，如下图
**不可重复读：**指一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
**幻读：**一个事务查询没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了"幻影行"。
（2）配置隔离级别
查看事务隔离级别（MySQL 8.0+ 版本），MySQL 默认全局隔离级别为 REPEATABLE-READ（可重复读）
-- 查看当前会话隔离级别（变量名已更新）
SELECT @@transaction_isolation;-- 查看全局隔离级别
SELECT @@global.transaction_isolation;-- 输出如 REPEATABLE-READ
修改事务隔离级别的方法
-- 当前会话
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置为读已提交
-- 全局（需 SUPER 权限）
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 修改全局默认
配置文件永久生效
在mysql自定义配置my.cnf添加如下内容
transaction_isolation=READ-COMMITTED
隔离级别分类如下
| 隔离级别 | 说明 | 脏读 | 不可重复读 | 幻读 | 适用场景 |
|---|---|---|---|---|---|
| Read uncommitted（读未提交） | 最低级别，事务可以读到其他事务未提交的修改即脏读， 性能最好，一致性最差，几乎不用。 | √ | √ | √ | 统计类分析（非精确场景）：如实时监控数据，允许短暂数据不一致。明确接受脏数据 |
| Read committed（读已提交） | 大多数数据库默认级别，事务只能读到其他事务已提交的修改， 避免了脏读，但可能出现不可重复读（同一个事务内两次读同一数据结果不同）， 也可能出现幻读（同一事务两次查询返回的行数不同） | × | √ | √ | 高并发写入+读操作为主：如新闻网站评论系统（用户读取最新已发布评论）。OLTP系统：订单状态更新（如电商下单后读取库存，允许其他事务修改库存）。推荐默认级别（多数业务场景平衡点）。 |
| Repeatable Read（可重复读） | 保证当前事务无法读取其他事务的未提交修改即避免了脏读。为每个事务提供静态数据快照，这个快照读使多次读取同一数据的结果是一致的即避免了不可重复读， 但仍可能出现幻读(本质为写操作幻读)。这是MySQL InnoDB默认级别，并通过Next-Key Locking避免了幻读(select … for update)。详细见下文解析 | × | × | √ | 财务核算：如月末统计账户余额，要求事务内数据稳定。一致性要求高的读操作：用户资产查询（避免两次查询余额不一致）。MySQL默认级别，适合需要强一致读的场景。 |
| Serializable（串行化） | 最高级别。强制事务串行执行，避免了所有并发问题（脏读、不可重复读、幻读）。性能最差。读写互斥、写写互斥（类似全表级锁竞争） | × | × | × | 谨慎使用，仅用于对一致性要求极端严格的场景。银行转账：双方账户余额必须100%精准，不允许任何并发干扰，票务系统座位锁定**：票务系统座位锁定**，99%的业务场景不应使用Serializable，用Repeatable Read + 显式锁或乐观锁替代，在保证安全的同时赢得性能。 |
Repeatable Read（可重复读）出现幻读举例：快照读避免幻读，但其他事务新增了数据，当前事务在做UPDATE/DELETE****可能影响其他事务新增的数据 → 本质是写操作幻读。如下举例
-- 事务A
BEGIN;
SELECT * FROM orders WHERE amount > 100; -- 返回2条 (快照ReadView)-- 事务B提交：INSERT INTO orders(amount) VALUES(200);
SELECT * FROM orders WHERE amount > 100; -- 仍返回2条（快照读不幻读）
UPDATE orders SET status=1 WHERE amount > 100; -- 意外修改了事务B新增的行！
MySQL InnoDB引擎默认锁机制为Next-Key Locking，在可重复读隔离级别下，通过Next-Key Locking可以避免幻读, 底层自动用SELECT ... FOR UPDATE 语句触发 Next-Key Locking 机制，这是解决幻读的核心手段，开发中无需显示的加for update，如下各种情况的锁定机制：
| 索引性质 | 查询类型 | 锁定机制 | 锁定范围详解 | 示例 SQL |
|---|---|---|---|---|
| 唯一索引 | 等值查询 (=) | 行锁 (Record Lock)，命中唯一索引，Next-Key Locking降级为行锁 | 仅锁定匹配行 （如 id=5 的行）其他事务：可修改id≠5的行 可插入id≠5的行 禁止修改/删除id=5的行 | 类似：SELECT * FROM users WHERE id=5 FOR UPDATE |
| 唯一索引 | 未命中记录 | 间隙锁 (Gap Lock)，未命中唯一索引，Next-Key Locking降级为间隙锁 | 锁定值所在区间 （如查询 id=6 不存在时锁 (5,7)）其他事务：可修改/删除id=5或7及其他值的行 禁止在5和7之间插入新数据，可在其他范围插入数据 | 类似：SELECT * FROM users WHERE id=6 FOR UPDATE |
| 普通索引 (非唯一索引) | 等值查询 (=) | Next-Key Lock | 锁定匹配行 + 前后间隙 （如 amount=100 时锁 (50,100] + (100,200)）其他事务：可修改/删除amount=50或200及其他值的行 禁止在50和200间插入新数据，可在其他范围插入数据 禁止修改amount=100的行 | 类似：SELECT * FROM orders WHERE amount=100 FOR UPDATE |
| 普通索引 (非唯一索引) | 范围查询 (>) | Next-Key Lock | 锁定大于条件的区间 （如 time>'2023-01-01'：锁 ('2023-01-01', +∞`）其他事务：禁止在>'2023-01-01’区间插入 可修改/删除/插入≤’2023-01-01’的数据 | 类似：SELECT * FROM logs WHERE time > ‘2023-01-01’ FOR UPDATE` |
| 普通索引 (非唯一索引) | 范围查询 (<) | Next-Key Lock | 锁定从-∞到条件值的区间 （如 time<'2023-06-01'： 锁 (-∞,2023-06-01)）其他事务：禁止在<'2023-06-01’区间插入和修改 可修改/删除/插入≥’2023-06-01’的数据 | 类似：SELECT * FROM logs WHERE time < '2023-06-01' FOR UPDATE |
| 普通索引 (非唯一索引) | 范围查询 (BETWEEN) | Next-Key Lock | 锁定整个范围+边界间隙 （如 id BETWEEN 5 AND 10： 锁 (4,5] + (5,10] + (10,11)）其他事务：禁止在(4,11)插入 禁止修改5-10行 可修改/删除<=4或>=11的数据 | 类似：SELECT * FROM items WHERE id BETWEEN 5 AND 10 FOR UPDATE |
| 普通索引 (非唯一索引) | 未命中记录 | 间隙锁 (Gap Lock)，普通索引未命中记录降级为间隙锁 | 锁定值所在区间 （如查询 price=150 不存在时锁 (100,200)）其他事务：可修改/删除price=100或200及其他行 禁止在(100,200)插入新数据 可在其他区间插入 | 类似：SELECT * FROM products WHERE price=150 FOR UPDATE |
| 无索引 | 任何查询 | 全表间隙锁 | 锁定整个表的所有间隙： 所有记录加行锁 、所有间隙加Gap Lock 等效全表锁定 (-∞, +∞)其他事务：禁止任何插入 禁止修改/删除扫描到的行 可修改未扫描行（仅限快照读） 并发性能严重影响 | 类似：SELECT * FROM employees WHERE name='Alice' FOR UPDATE |
（3）隔离级别原理分析
通过版本控制MVCC和排他锁保证数据隔离性
锁：排他锁（如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁）。
MVCC(Multi-Version Concurrency Control)：多版本并发控制，指维护一个数据的多个版本，使得读写操作没有冲突。
MVCC的具体实现，主要依赖于数据库记录中的隐式字段、回滚日志、读视图（readView）
- 隐式字段
| 隐藏字段 | 说明 |
|---|---|
| DB_TRX_ID | 最近修改事务 ID，记录插入这条记录或最后一次修改该记录的事务 ID。 |
| DB_ROLL_PTR | 回滚指针，指向这条记录的上一个版本，用于配合 undo log 指向上一个版本。 |
| DB_ROW_ID | 隐藏主键，如果表结构没有指定主键，InnoDB 会自动生成该隐藏字段作为主键。 |
- 回滚日志（undo log）
回滚日志（undo log），在 insert、update、delete 的时候产生的便于数据回滚的日志。
当 insert 的时候，产生的 undo log 日志只在回滚时需要，在事务提交后，可被立即删除。
当 update、delete 的时候，产生的 undo log 日志不仅在回滚时需要，MVCC 版本访问也需要，不会立即被删除，不同事务或相同事务对同一条记录进行修改，会导致该记录的 undo log 生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录，如下
- 读视图（readview）
ReadView（读视图） 是快照读 SQL 执行时 MVCC 提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。
快照读
简单的 SELECT（不加锁）就是快照读，快照读读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
- Read Committed（读已提交）：每次 SELECT，都生成一个快照读。
- Repeatable Read（可重复读）：开启事务后第一个 SELECT 语句才是快照读的地方。
当前读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：
select ... lock in share mode（共享锁）、select ... for update、update、insert、delete（排他锁）都是一种当前读。
ReadView 的四个核心字段
| 字段 | 含义 |
|---|---|
| m_ids | 当前活跃的事务 ID 集合（即生成 ReadView 时尚未提交的事务 ID 列表） |
| min_trx_id | 最小活跃事务 ID（即 m_ids 中的最小值） |
| max_trx_id | 预分配事务 ID，当前最大事务 ID + 1（事务 ID 自增分配） |
| creator_trx_id | ReadView 创建者的事务 ID（即创建该读视图的事务 ID） |
不同的隔离级别，生成 ReadView 的时机不同：
READ COMMITTED（读已提交）：在事务中每一次执行快照读时生成 ReadView。
REPEATABLE READ（可重复读）：仅在事务中第一次执行快照读时生成 ReadView，后续复用该 ReadView。
4、持久性分析
事务一旦提交（Commit），它对数据库的改变就是永久性的，即使系统崩溃、断电，数据也不会丢失。事务提交时，数据可能还在内存缓冲区（Buffer Pool）中，尚未写入磁盘。若此时崩溃，内存数据丢失，导致提交的事务数据丢失。
当缓冲池中的数据已一定频率刷新到磁盘时，突然宕机了，这时需要借助redo log来进行数据恢复。保证了事务的持久性。
重做日志（redo log），记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。在事务提交时，优先将数据的修改操作记录到重做日志（顺序写入磁盘），确保即使数据未刷盘，也能通过日志恢复数据。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页（修改过的数据页）到磁盘，在发生错误时可进行数据恢复使用。
