MySQL数据库

1、特性

MySQL 是一个开源的关系型数据库管理系统（RDBMS），使用结构化查询语言（SQL）来管理和操作数据库。MySQL 是由 Oracle Corporation 开发和维护的，广泛用于许多应用程序和网站，尤其是在 Web 开发中。它支持大规模的数据库操作，有良好的性能、可靠性和安全性。MySQL 的一些关键特性包括：

1. 关系型：数据以表格形式存储，表之间可以通过主键和外键建立关系。
2. 开源：MySQL 在 GNU 通用公共许可证下发布，允许用户自由使用和修改。
3. 跨平台：可以在多种操作系统上运行，包括 Windows、Linux 和 macOS。
4. 支持事务：可以通过支持 ACID（原子性、一致性、隔离性、持久性）来管理数据库事务。
5. 高性能：设计上优化了查询效率，能够处理大量数据。

在 Java 应用程序中，数据库通常承担着以下几个重要角色：

1. 数据存储：数据库是持久化存储的地方，应用程序可以将生成的数据（如用户信息、交易记录等）存储在数据库中，以供后续访问和处理。

2. 数据检索：应用程序可以使用 SQL 查询从数据库中检索所需数据，通过使用 JDBC（Java Database Connectivity）等 API 进行连接和执行操作。

3. 数据管理：数据库允许对数据执行 CRUD 操作（创建、读取、更新、删除），以便管理应用程序的业务数据。

4. 并发处理：数据库通过事务管理和锁机制来处理多个用户和应用程序同时对数据的访问，确保数据的一致性和完整性。

5. 数据安全：数据库可以实现用户访问控制和权限管理，保护敏感数据不被未授权访问。

6. 数据分析与报告：应用程序可以从数据库中提取数据进行分析，并生成报告，以支持业务决策。

在 Java 中，开发人员通常使用 JDBC 或者更高级的框架如 Hibernate、Mybatis、JPA等来与数据库进行交互，以实现更高效的数据管理和操作。

2、库表设计

数据库设计中使用的三大范式（Normalization）是用于减少数据冗余和提高数据完整性的标准化方法。三大范式的定义如下：

第一范式（1NF）

定义：一个表中的每一列都应当只包含原子值，即不能再分的值，且列的值都应该是同类型的。

如何理解：确保每个字段都只能存储一个值，而不是多个值或列表。

示例：
假设我们有一个学生表，以下是不符合第一范式的设计：

学生ID	姓名	课程
1	张三	数学, 语文, 英语
2	李四	物理

为了符合第一范式，我们应该将“课程”列拆分为多行：

学生ID	姓名	课程
1	张三	数学
1	张三	语文
1	张三	英语
2	李四	物理

第二范式（2NF）

定义：一个表必须符合第一范式，并且每个非主属性必须完全依赖于主键。

如何理解：每个非主属性不能只依赖于主键的一部分。

示例：
我们继续沿用上面的学生示例。如果我们假设“课程”不是主键，而“成绩”是，且成绩只依赖于课程而非学生ID：

学生ID	课程	成绩
1	数学	90
1	语文	85
2	物理	95

为了符合第二范式，我们需要将表拆分，以便确保每个非主属性（成绩）完全依赖于主键（学生ID和课程的组合）：

1. 学生表：

学生ID	姓名
1	张三
2	李四

1. 课程成绩表：

学生ID	课程	成绩
1	数学	90
1	语文	85
2	物理	95

第三范式（3NF）

定义：一个表必须符合第二范式，并且每个非主属性不能传递依赖于主键。

如何理解：非主属性之间不应存在依赖关系。

示例：
设想我们有一个教师表，其中包含教师的身份信息和课程信息：

教师ID	教师姓名	教师地址	课程
1	王老师	地址A	数学
2	李老师	地址B	语文

在这个表中，“教师地址”是依赖于“教师姓名”，而不是直接依赖于主键“教师ID”，这违反了第三范式。

为了符合第三范式，我们可以将数据拆分为两个表：

1. 教师表：

教师ID	教师姓名	教师地址
1	王老师	地址A
2	李老师	地址B

1. 课程表：

教师ID	课程
1	数学
2	语文

在这个例子中，所有的非主属性（教师姓名、教师地址）现在完全依赖于主键（教师ID），并且没有传递依赖。

3、存储引擎

 mysql推荐使用的引擎有两种：MyISAM 和 InnoDB

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。提供了大量的特性，包括压缩表、空间数据索引等。MySQL 在 5.5 版本之前，默认使用 MyISAM 引擎。

• 不支持事务。
• 不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入(CONCURRENT INSERT)。
• 可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。
• 如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

InnoDB

• 是 MySQL 5.5 版本之后默认使用的事务型存储引擎。
• 实现了四个标准的隔离级别，默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下，通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻影读。
• 主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。
• 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。
• 支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

区别

• 事务: InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。

• 并发: MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。

• 外键: InnoDB 支持外键。

• 备份: InnoDB 支持在线热备份。

• 崩溃恢复: MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

• 其它特性: MyISAM 支持压缩表和空间数据索引。

4、事务

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

ACID

•  原子性(atomicity)：
  • 指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。
• 一致性(consistency)：
  • 指所有事务在对一个相同数据进行相同操作时它的结果应该是一致的。
• 隔离型(isolation)：
  • 一个事务所做的修改在最终提交以前，对其它事务是不可见的。做到不同事务之间互不干扰。
• 持久性(durability)：
  • 指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，其他操作和数据库故障不应该对其有任何影响。

并发一致性问题

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题。 

丢失修改

T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。

读脏数据

T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。

不可重复读

T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

幻影读

T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

产生并发不一致性问题主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性。并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，相当复杂。数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题。

隔离级别

• 未提交读(READ UNCOMMITTED)：事务中的修改，即使没有提交，对其它事务也是可见的。
• 提交读(READ COMMITTED)：一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
• 可重复读(REPEATABLE READ)：保证在同一个事务中多次读取同样数据的结果是一样的。
• 可串行化(SERIALIZABLE)：强制事务串行执行。

---

隔离级别	脏读	不可重复读	幻影读
未提交读	√	√	√
提交读	×	√	√
可重复读（mysql默认）	×	×	√
可串行化	×	×	×

多版本并发控制

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

版本号

• 系统版本号: 是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
• 事务版本号: 事务开始时的系统版本号。

隐藏的列

MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号:

• 创建版本号: 指示创建一个数据行的快照时的系统版本号；
• 删除版本号: 如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了。

Undo 日志

MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行(Record)的所有快照连接起来。

实现过程

以下实现过程针对可重复读隔离级别。

当开始新一个事务时，该事务的版本号肯定会大于当前所有数据行快照的创建版本号，理解这一点很关键。

1. SELECT

多个事务必须读取到同一个数据行的快照，并且这个快照是距离现在最近的一个有效快照。但是也有例外，如果有一个事务正在修改该数据行，那么它可以读取事务本身所做的修改，而不用和其它事务的读取结果一致。

把没有对一个数据行做修改的事务称为 T，T 所要读取的数据行快照的创建版本号必须小于 T 的版本号，因为如果大于或者等于 T 的版本号，那么表示该数据行快照是其它事务的最新修改，因此不能去读取它。除此之外，T 所要读取的数据行快照的删除版本号必须大于 T 的版本号，因为如果小于等于 T 的版本号，那么表示该数据行快照是已经被删除的，不应该去读取它。

2. INSERT

将当前系统版本号作为数据行快照的创建版本号。

3. DELETE

将当前系统版本号作为数据行快照的删除版本号。

4. UPDATE

将当前系统版本号作为更新前的数据行快照的删除版本号，并将当前系统版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 DELETE 后执行 INSERT。

快照读与当前读

1. 快照读

使用 MVCC 读取的是快照中的数据，这样可以减少加锁所带来的开销。

select * from table ...;

2. 当前读

读取的是最新的数据，需要加锁。以下第一个语句需要加 S 锁，其它都需要加 X 锁。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert;
update;
delete;

Next-Key Locks

Next-Key Locks 是 MySQL 的 InnoDB 存储引擎的一种锁实现。

MVCC 不能解决幻读的问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读(REPEATABLE READ)隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。

Record Locks

锁定一个记录上的索引，而不是记录本身。

如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。

Gap Locks

锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。

SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

Next-Key Locks

它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。例如一个索引包含以下值: 10, 11, 13, and 20，那么就需要锁定以下区间:

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)