存储引擎

show engines;

5.7版本所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB ⽀持事务。

show table status like "table_name" ;

mysql 数据库的 user 表是 MyISAM 存储结构

MyISAM 性能好

MyISAM和InnoDB区别

MyISAM采⽤表级锁(table-level locking)。

InnoDB⽀持⾏级锁(row-level locking)和表级锁,默认为⾏级锁

MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，⽽且提供了⼤量的特性，

包括全⽂索引、压缩、空间函数等，但MyISAM不⽀持事务和⾏级锁，⽽且最⼤的缺陷就是崩溃

后⽆法安全恢复。不过，5.5版本之后，MySQL引⼊了InnoDB（事务性数据库引擎），MySQL

5.5版本后默认的存储引擎为InnoDB。

InnoDB 优点

  • 支持行级别锁

  • 支持事务和崩溃后恢复

  • 支持外键

  • … 等

字符集

MySQL采⽤的是类似继承的⽅式指定字符集的默认值，每个数据库以及每张数据表都有⾃⼰的默认值，他们逐层继承。⽐如：某个库中所有表的默认字符集将是该数据库所指定的字符集（这些表在没有指定字符集的情况下，才会采⽤默认字符集）

索引

索引是存储引擎用于提高数据库表的访问速度的一种「数据结构」。

有BTree索引 和 哈希索引, 单条优先的时候选择哈希索引， 否则选择 Btree 索引。

B树中的B+Tree 在两种方式下的实现方式不同

  • MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，⾸先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“⾮聚簇索引”。

  • InnoDB: 其数据⽂件本身就是索引⽂件。相⽐MyISAM，索引⽂件和数据⽂件是分离的，其表数据⽂件本身就是按B+Tree组织的⼀个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据⽂件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。⽽其余的索引都作辅助索引。辅助索引的data域存储相应记录主键的值⽽不是地址，这也是和MyISAM不同的地⽅。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再⾛⼀遍主索引。 因此，在设计表的时候，不建议使⽤过⻓的字段作为主键，也不建议使⽤⾮单调的字段作为主键，这样会造成主索引频繁分裂。 PS：整理⾃《Java⼯程师修炼之道》

缓存

mysql 8.0 版本前会有缓存的概念, 直接返回缓存中的结果

事务

事务四大特征

  • A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；

  • C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；

  • I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；

  • D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

单条 SQL 默认是事务的， 称为*隐式事务*。

多条之间使用 BEGIN; COMMIT; 包括

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- ROLLBACK回滚事务
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

事务控制语句：

  • BEGIN 或 START TRANSACTION 显式地开启一个事务；

  • COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

  • ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

  • SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

  • RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

  • ROLLBACK TO identifier 把事务回滚到标记点；

  • SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

事务隔离

并发事务带来的问题

  • 脏读 另一个事务读到了没提交的数据

  • 丢失修改 两个事务同时修改数据造成某一个丢失

  • 不可重复读 一个事务读取到了另一个事务修改前后的两个不同值或不同数量的数据

  • 幻读 select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入

MySQL 不同级别对应的并发事务问题

Isolation Level 脏读（Dirty Read） 不可重复读（Non Repeatable Read） 幻读（Phantom Read）
Read Uncommitted 读取未提交 Yes Yes Yes
Read Committed 读取已提交 - Yes Yes
Repeatable Read 可重复读（MySQL 默认） - - Yes
Serializable 可串行化 - - -

默认情况下， MySQL 会出现幻读的问题， 但是进行了解决

快照读和当前读

在读未提交隔离级别下，快照是什么时候生成的？

没有快照，因为不需要，怎么读都读到最新的。不管是否提交

在读已提交隔离级别下，快照是什么时候生成的？

SQL语句开始执行的时候。

在可重复读隔离级别下，快照是什么时候生成的？

事务开始的时候

1.在默认隔离级别下，select 语句默认是快照读

select a from t where id = 1
复制代码

2.select 语句加锁是当前读

# 共享锁
select a from t where id = 1 lock in share mode;

#排他锁
select a from t where id = 1 for update;
复制代码

3.update 语句是当前读

update t set a = a + 1;

innodb 解决幻读问题

MVCC 和 next-key Lock

  • RR隔离级别下间隙锁才有效，RC隔离级别下没有间隙锁；

  • RR隔离级别下为了解决“幻读”问题：“快照读”依靠MVCC控制 Multi-Version Concurrency Control，“当前读”通过间隙锁和行锁解决；

  • 间隙锁和行锁合称next-key lock(存在 key 就行锁， 不存在就间隙锁)，每个next-key lock是前开后闭区间；

  • 间隙锁的引入，可能会导致同样语句锁住更大的范围，影响并发度

MVCC机制（Multi-Version Concurrency Control，即多版本并发控制）

快照读就是每一行数据中额外保存两个隐藏的列，插入这个数据行时的版本号，删除这个数据行时的版本号（可能为空），滚动指针(指向 undo log 中用于事务回滚的日志记录)。

  • 插入

    事务 ID 作为版本号

  • 删除

    原数据行的删除版本号设置为当前事务的 ID，然后根据原数据行生成一条 INSERT 语句，写入 undo log，用于事务执行失败时回滚。delete 操作实际上不会直接删除，而是将 delete 对象打上 delete flag，标记为删除，最终的删除操作是 purge 线程完成的。会将数据行的删除版本号设置为当前的事务的 ID，这样后面的事务 B 即便查到这行数据由于事务 B 的 ID>删除版本号，也会忽略这条数据。

  • 更新

    所以执行更新操作时，其实是会将原数据行的删除版本号设置为当前事务的 ID，生成一条 INSERT 语句，写入 undo log，用于事务执行失败时回滚。插入一条新的数据，将事务的 ID 作为数据行的的创建版本号。

  • 查询

    1. 如果该行数据没有被加行锁中的 X 锁（也就是没有其他事务对这行数据进行修改），那么直接读取数据（前提是数据的版本号<=当前事务版本号的数据,不然不会放到查询结果集里面）。

    2. 该行数据被加了行锁 X 锁（也就是现在有其他事务对这行数据进行修改），那么读数据的事务不会进行等待，而是回去 undo log 端里面读之前版本的数据（这里存储的数据本身是用于回滚的），在可重复读的隔离级别下，从 undo log 中读取的数据总是事务开始时的快照数据(也就是版本号小于当前事务 ID 的数据)，在提交读的隔离级别下，从 undo log 中读取的总是最新的快照数据。

    排它锁(X锁)和共享锁(S锁)：

加锁规则

加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

  1. 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。

  2. 原则 2：查找过程中访问到的对象才会加锁。

  3. 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

  4. 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

  5. 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

MySQL 锁机制 排它锁(X锁)和共享锁(S锁)：

  1. 共享锁 lock in share mode S 锁
- 允许其它事务也增加共享锁读取

- 不允许其它事物增加排他锁(`for update`)

- 当事务同时增加共享锁时候，事务的更新必须等待先执行的事务commit后才行，如果同时并发太大可能很容易造成死锁
  1. 排他锁 for update X 锁
- 事务之间不允许其它排他锁或共享锁读取，修改更不可能

- 一次只能有一个排他锁执行commit之后，其它事务才可执行

**不允许其它事务增加共享或排他锁读取。修改是惟一的，必须等待前一个事务commit，才可**

SELECT ... FOR SHARE和SELECT ... FOR UPDATE：

SELECT ... FOR SHARE/LOCK IN SHARE MODE只会锁定扫描过程中使用的索引里的记录行，即如果你的查询正好使用了覆盖索引，那么只有这个索引里的记录行会被锁定，主键索引的记录行是不会被锁定的。

SELECT ... FOR UPDATE除了会锁定扫描过程中使用的索引里的记录行，相关的其他索引的记录行也会被锁定。换句话说就算你使用了覆盖索引，但是主键索引里的记录行也会被锁定。而又因为主键索引就已经包含了所有字段，那么就相当于锁定表的整行记录。

MySQL在可重复读个里级别下（默认），才会启用间隙锁。你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。

undo log

undo_log 是一种逻辑日志，是旧数据的备份。有两个作用，用于事务回滚和为 MVCC 提供老版本的数据。

可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录。加锁后会备份数据， 用于回滚

数据库范式-数据库逻辑设计

1NF是对属性的**原子性**，要求属性具有原子性，不可再分解；

2NF是对记录的**唯一性，要求记录有唯一标识，即实体的唯一性，即不存在部分依赖**；某一列不能重复

表：学号、课程号、姓名、学分;

正确做法:

学生：Student(学号, 姓名)；课程：Course(课程号, 学分)；选课关系：StudentCourse(学号, 课程号, 成绩)。

如果一个关系属于第二范式,并且在**两个(或多个)非主键属性之间不存在函数依赖**。(非主键属性之间的函数依赖也称为传递依赖),那么这个关系属于第三范式。

3NF是对字段的**冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖**；

表: 学号, 姓名, 年龄, 学院名称, 学院电话

正确做法：

学生：(学号, 姓名, 年龄, 所在学院)；学院：(学院，学院名称， 电话)。

2NF和3NF的区别

  • 2NF依据是非主键列是否完全依赖于主键，还是依赖于主键的一部分。

  • 3NF依据是非主键列是直接依赖于主键，还是直接依赖于非主键。