关系型数据库 - MySQL I

MySQL 数据库

MySQL 是一种关系型数据库。开源免费，并且方便扩展。在 Java 开发中常用于保存和管理数据。默认端口号 3306。

MySQL 数据库主要分为 Server 和存储引擎两部分，现在最常用的存储引擎是 InnoDB。

---

指令执行过程

MySQL 数据库接收到用户指令后，首先由 Server 负责对数据操作的分析、处理和优化，再交给存储引擎执行数据存取操作。

连接器

连接器负责用户登录数据库时的身份认证，校验账户密码。校验通过后连接器会连接到权限表，并读取该用户的所有权限。如果连接未断开，即使该用户权限被管理员修改也不受影响。

查询缓存

缓存 SELECT 语句以及返回的结果。收到查询语句会首先和缓存比对，如果相同就直接从查询缓存里返回数据。

更新表后，这个表上的所有的查询缓存都会被清空。这导致实际使用场景中查询缓存的作用非常少，在 MySQL 8.0 版本后移除。

分析器

如果查询语句未命中缓存，或者是更新语句，那么将由分析器负责分析 SQL 语句的用途。

1. 词法分析：提取关键字，提取 SQL 语句的关键元素，明确 SQL 语句的功能。

2. 语法分析：判断 SQL 语句是否正确，是否符合 MySQL 的语法。如果不符合语法则返回错误信息。

优化器

明确 SQL 语句功能后，由优化器负责选择尽可能最优的执行方案。比如多个索引的时候选择索引，多表查询的时候选择关联顺序。

执行器

确定执行方案后，由执行器负责校验该用户有没有权限，并交由存储引擎执行语句，然后从存储引擎返回数据。

---

存储引擎

实际执行对数据库数据的存取。目前 MySQL 默认使用 InnoDB 引擎。相比于过去使用 MyISAM 引擎，有以下几个优势：

1. 索引：数据文件本身是主索引。
2. 外键：支持外键。
3. 事务：添加本地日志，支持安全恢复；支持行级锁，提高并发度。
4. 并发：支持多版本并发控制，提升性能。

索引

存储结构

MySQL 数据库使用以下两种数据结构存储和查找数据：

1. B+ 树:(默认）适用于连续查询多条数据。
2. 哈希表：适用于查询单条数据。

索引类型

索引名称|索引类型|字段类型|备注 -|-|- PRIMARY KEY|主索引|主键|字段值不能重复，也不能为空。 INDEX|普通索引|自定义字段|无，效率低。 UNIQUE|唯一索引|自定义字段|字段值不能重复，效率高。 FULLTEXT|文本索引|自定义字段|无，用于文本检索。

• 主索引

在 InnoDB 存储引擎中数据文件本身就是主索引（聚簇索引）：数据以 B+ 树形式存储，根据主键值进行排序。

我们可以为其他字段建立辅助索引（非聚簇索引），以提高对字段的查询速度，但同时会降低表的更新速度。在辅助索引中记录主键值而不是字段地址：根据辅助索引查找后，仍需要根据主键值在主索引中查询数据。

• 组合索引

索引内可以包含多个字段，N 个字段的组合索引实际建立了 N 个索引。

对 a/b/c 三个字段建立的组合索引，实际会先在 a 索引中查找，再到 a/b 索引中查找，最后在 a/b/c 索引中查找。

视图

视图是一个虚拟表，不实际存储数据。其内容会通过查询其他表得到，在引用视图时动态生成。

1. 权限管理：表的权限管理不能限制到具体的行和列，但通过视图则可以限制用户能得到的结果集。
2. 数据独立：表的结构发生变化，不会对用户使用视图查询到的数据产生影响。

外键

从表通过外键关联到主表的主键，建立数据表之间的关系。

• 优点：保障数据的一致性和完整性。
• 缺点：增加数据之间的耦合度，难以集群。因此不推荐使用外键。

删除策略

对主表的数据进行 UPDATE/DELETE 操作时，将会影响到关联的从表。

外键模式	删除策略
限制	（默认）从表有相关数据时，主表不能更新/删除。
级 联	主表记录更新/删除时，从表相关记录也会被更新/删除。
设置 NULL	主表数据更新/删除时，从表相关记录的外键值被设为 NULL。
无操作	啥也不做

日志

当数据库数据发生更改时，用日志记录数据库操作。当发生错误或者冲突时，可以进行回滚。保证数据的一致性。

bin log 归档日志

最开始 MySQL 并没与 InnoDB 引擎，其他存储引擎只有通用的 bin 日志用来归档（位于 server 层）。

InnoDB 引擎完成主存数据更新后向执行器提交，由 bin 日志记录操作。如果主存数据已更新，且 bin 日志没有被写入时数据库崩溃，后续进行机器备份的时候就会丢失原有数据。这导致数据没有安全恢复的能力：一旦数据库发生异常重启，之前提交的记录都会丢失。

redolog 重做日志

MySQL 引入 InnoDB 引擎后，自带了 redo 日志。用于数据库发生异常重启时系统记录的恢复。

1. InnoDB 引擎完成主存数据更新但还未提交时，由 redo 日志记录操作并进入 prepare 状态。
2. InnoDB 引擎向执行器提交时，由 bin 日志记录操作。
3. 提交完成后执行器通知 InnoDB 引擎，redo 日志进入 commit 状态。

如果 bin 日志没有被写入时数据库崩溃，后续进行机器备份的时候就会按照 redo 日志恢复数据。

如果 bin 日志已经写完但 redo 日志还处于 prepare 状态时数据库崩溃。MySQL 会判断 redo 日志是否完整，如果完整就立即提交。否则再判断 bin 日志是否完整，如果完整就提交 redo 日志，不完整就回滚事务。这样就解决了数据一致性的问题。

---

事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。保障数据之间的同步。

事务特性 ACID

• 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
• 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发事务潜在问题

• 丢失修改

事务（T1）修改数据的过程中，另一个并发事务（T2）也修改了该数据。导致事务（T1）对数据的修改丢失。

• 脏读

事务（T1）修改数据但还未写入数据库时，另一个并发事务（T2）使用了该数据。导致事务（T2）读取数据可能是不正确的。

• 不可重复读

事务（T1）两次读取数据的过程中，另一个并发事务（T2）修改了该数据。导致事务（T1）两次读取数据的结果不同。

• 幻读

事务（T1）两次读取数据集合的过程中，另一个并发事务（T2）插入或删除了部分数据。导致事务（T1）两次读取数据的结果不同。

数据锁

存储引擎通过给数据加锁来保障事务性。MyISAM 引擎只支持表级锁，而 InnoDB 存储引擎支持行级锁和表级锁，默认为行级锁。

• 表级锁：对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。但触发锁冲突的概率最高，并发度低。

• 行级锁：只针对当前操作的数据行加锁。大大减少数据库操作的冲突，并发度高。但加锁的开销也最大，可能会出现死锁。

InnoDB支持三种行锁定方式：

• 间隙锁：锁定索引的记录间隙，确保索引记录的间隙不变。

Next-Key Lock 是行级锁和间隙锁的组合使用。当 InnoDB 扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。其他事务就不能在这个间隙修改或者插入记录。间隙锁是针对事务隔离级别为可重复读或以上级别，可以有效防止幻读的发生。

事务隔离级别

• READ-UNCOMMITTED（RU） 读取未提交

事务进行读操作时允许其他事务访问，事务进行写操作将会禁止其他事务写。

• READ-COMMITTED（RC） 读取已提交

事务进行读操作时允许其他事务访问，事务进行写操作将会禁止其他事务读写。

• REPEATABLE-READ（RR） 可重复读

事务进行读操作时会禁止其他事务写，事务进行写操作将会禁止其他事务读写。

• SERIALIZABLE 可串行化

事务进行读写操作时，都会禁止其他事务读写。

隔离级别	丢失修改	脏读	不可重复读	幻读
读取 - 未提交	×	√	√	√
已提交读	×	×	√	√
可重复读取	×	×	×	√
序列 化	×	×	×	×

1. InnoDB 存储引擎默认支持的隔离级别是 REPEATABLE-READ（RR） ，且 InnoDB 在该事务隔离级别下使用 Next-Key Lock 锁算法，可以避免幻读。
2. InnoDB 存储引擎在分布式事务情况下一般会用到 SERIALIZABLE 隔离级别。

---

MVCC 公司

MVCC 概念

MVCC 即多版本并发控制，维持一个数据的多个版本，使得读写操作没有冲突。从而提高数据库并发性能，做到即使有读写冲突时，也能不加锁非阻塞并发读。

是乐观锁的一整实现方式，就是每行都有版本号，保存时根据版本号决定是否成功。

读类型

• 当前读

  读取的是记录最新版本，同时会对读取的记录进行加锁保证其他并发事务不能修改 选择 Share Mode 中的 Lock （共享锁）， select for update ;更新、插入、删除

• 快照读

可能读到的是数据之前的历史版本.在很多情况下，避免了加锁操作，降低了开销；

像不加锁的select操作就是快照。但如果隔离级别是最高级串行化，快照读会退化成当前读。

MVCC 实现

在 InnoDB 存储引擎中，每行记录除了我们自定义的字段外，还会隐式记录：

1. 最近修改：记录创建这条记录/最后一次修改该记录的事务ID
2. 回滚指针：指向这条记录的上一个版本，存储于 rollback segment 里。
3. 隐藏主键：如果数据表没有主键，InnoDB 会自动产生一个自增的聚簇索引。
4. 删除标记：标记该记录是否已被删除。

事务进行快照读操作的时候生产的读视图（Read View），在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID（当每个事务开启时，都会被分配一个ID， 这个ID是递增的，所以最新的事务，ID值越大）。

InnoDB 会根据读取事务 ID 判断应该都什么时间段的数据。

在RC隔离级别下，是每个快照读都会生成并获取最新的Read View;而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是同一个Read View。

---

数据类型

字符串类型

常用的字符串类型有定长字符串 CHAR 和变长字符串 VARCHAR 两种，必须用数字注明可容纳的字符数。

• CHAR（n） 表示固定容纳 n 个字符，当少于 n 个字符时，会使用空格填充。
• VARCHAR（n） 表示最多容纳 n 个字符，当少于 n 个字符时不会补空。起始位和结束位需要额外 3 字节。

英文字母单个字符占 1 字节。汉字单个字符 UTF-8 编码占 3 字节，GBK 编码占 2 字节。

对于长字符串数据可以用 TEXT 或 BLOB 类型存储，固定占用 65535 字节。其中 TEXT 保存文本格式，BLOB 保存二进制格式。如果需要存储更短或更长的字符串类型数据，可以使用 TEXT/BLOB 的扩充类型：如 TINYTEXT、MEDIUMTEXT 和 LONGTEXT。

类型名称	大小（字节）	数据库类型	JAVA 类型
字符 （n）	N（0-255）	煳	字符串
VARCHAR（瓦查尔）	N+3（0-65535）	瓦查尔	字符串
发短信	65535	瓦查尔	字符串
斑点	65535	斑点	字节

CHAR 类型最大只能容纳 255 字节数据，已不推荐使用。目前 VARCHAR 支持容纳最大 65535 字节数据，且长度不固定能有效节省数据库空间，推荐尽量使用 VARCHAR 数据类型取代 TEXT。

数据类型不支持 MySQL 内存临时表，进行排序等操作必须在磁盘中进行。尽量不要使用，一定要用建议单独建表。

整型 & 浮点型

布尔型

布尔型数据用 BIT 表示。

类型名称	大小（字节）	取值	JAVA 类型
位	1	0 或 1	布尔

整型

整形数据一般用 INT/INTEGER 表示，固定占用 4 字节。如果需要存储更短或更长的整型数据，可以使用 INT 的扩充类型：如 TINYINT、SMALLTEXT 和 MEDIUMTEXT 和 BIGINT。

在声明整型数据时也可以注明显示位宽，如 int（n）。在整形数据不足 n 位时会自动补零，几乎没有任何用处。

类型名称	大小（字节）	表示范围	JAVA 类型
TINYINT 公司	1	(-128，127)(0，255)	整数
SMALLINT	2	(-32 768，32 767)(0，65 535)	整数
MEDIUMINT	3	(-8 388 608，8 388 607)(0，16 777 215)	整数
INT/整数	4	(-2 147 483 648，2 147 483 647)(0，4 294 967 295)	整数
BIGINT	8	非常大	BigInteger （大整数）

浮点型

常用的浮点型数据类型有 FLOAT/DOUBLE ，FLOAT 类型固定占用 4 字节，DOUBLE 类型固定占用 8 字节。但 FLOAT/DOUBLE 只是近似存储，在数据库中我们常用 DECIMAL 类型记录金额，在数据库中实际以字符串形式存储，以确保不会产生任何误差。

• DECIMAL（M，D） M 表示最大位数，D 表示小数点右侧的位数。如 DECIMAL（5,2） ，小数点前 3 位，小数点后 2 位。

类型名称	大小（字节）	表示范围	JAVA 类型
浮	4		浮
双	8		双
十进制 （M，D）	M + 2		BigDecimal 小数

日期类型

java.sql 包内有专用 Java 类型匹配，注意数据类型必须是 ，而不是 。java.sql.Datejava.util.Date

类型	大小（字节）	格式	表示范围	JAVA 类型
年	1	YYYY	1901/2155	日期
日期	3	YYYY-MM-DD	1000-01-01/9999-12-31	日期
时间	3	HH:MM:SS	-838:59:59'/'838:59:59	时间
时间戳	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00/2038-1-19 11:14:07	时间戳
日期时间	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00/9999-12-31 23:59:59	时间戳

枚举 & 集合

create table tab(  gender enum('male','remale','secret')            -- gender 属性为枚举类型
);  insert into tab values ('remale');  select * from tab where gender=2;                  -- 两者等价select * from tab where gender= 'remale';复制到剪贴板错误复制

记录字符串，但底层数据实际以2个字节的整型（smallint）保存。

在已知的值中进行单选。最大数量为65535.按保存的位置顺序，从1开始逐一递增。

NULL值的索引是NULL。空字符串错误值的索引值是0。

create table tab(  gender set('male','remale','secret')            -- gender 属性为集合类型
);  insert into tab values ('male', 'remale');  复制到剪贴板错误复制

记录字符串，但底层数据实际以8个字节的整型（bigint）保存。

在已知的值中进行多选。最多有 64 个成员。

-- 查询 flag 字段包含 a,b 的字段 mysql -> select * from table_name where FIND_IN_SET('a,d', flag);