MySQL——基础知识_联系的多向性-CSDN博客

03：幻读：
当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行修改，这种数据涉及到表中的全部数据行，同时，第二个事务也对这个表数据进行修改，这个修改是对表中新增/删除一条数据，那么操作第一个事务的用户发现表中的数据还没有修改的数据行，就好像发生了幻觉一样，这就是发生了幻读。
注：幻读和不可重复读都读取另一条已经提交的事务，所不同的是不可重复读查询的都是同一数据项，而幻读针对的是增加或减少了数据。

例：事务A开始；事务A读取count(*) = n；事务B插入m条数据，并提交；事务A再次读取count(*)= n+m，与上次读取的不同。

3. 数据库提供的四种隔离级别：

Read Uncommitted(读未提交)：

一个事务可以读取另一个未提交事务的数据

最低级别，什么情况都可能会发生。包括：脏读、不可重复度、幻读

Read Committed(读已提交)：

一个事务要等另一个事务提交后才能读取数据。

可避免脏读的发生，但还存在不可重复读和幻读。

Repeatable Read(可重复读)【MySQL默认隔离级别】：

就是在开始读取数据（事务开启）时，不再允许修改操作

可避免脏读、不可重复读的发生，但还存在幻读

Serializable(串行)：

是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

隔离级别	脏读	不可重复读	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复读	×	×	√
串行	×	×	×

注意：级别越高，执行效率就越低; 隔离级别的设置只对当前链接有效，对JDBC操作数据库来说，一个Connection对象相当于一个链接，只对该Connection对象设置的隔离级别只对该connection对象有效，与其它链接connection对象无关。

oracle数据库中，只支持seralizable(串行化)级别和Read committed();默认的是Read committed级别；

4. 数据表类型/MySQL的数据库引擎有什么

MyIASM、InnoDB、HEAP、ISAM、MERGE、DBD以及Gemeni(一般只知道前两者即可)

5. MyIASM和innoDB的区别，应用在什么场景

InooDB支持事务，而MyISAM不支持事务；

InnoDB支持行级锁，而MyISAM支持表级锁；

InnoDB支持MVCC，而MyISAM不支持；

InnoDB支持外键，而MyISAM不支持；

InnoDB不支持全文索引，而MyISAM支持；

InnoDB不能通过直接拷贝表文件的方法拷贝表到另外一台机器， myisam 支持；

InnoDB表支持多种行格式， myisam 不支持；

InnoDB是索引组织表， myisam 是堆表；

MyISAM适合：(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。

InnoDB适合：(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且行锁定的机会比较大的情况。

为什么MyISAM会比Innodb 的查询速度快。

INNODB在做SELECT的时候，要维护的东西比MYISAM引擎多很多；
1）数据块，INNODB要缓存，MYISAM只缓存索引块，这中间还有换进换出的减少；
2）innodb寻址要映射到块，再到行，MYISAM 记录的直接是文件的OFFSET，定位比INNODB要快
3）INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护

6. 锁的类型

（1）行级锁和表级锁：

在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡。

并发程度：锁粒度越小，发生锁争用的可能越小，系统并发程度越高。适合写操作或修改操作较多的场景

系统开销：锁粒度越小，开销约大。锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。

（2）读写锁 / 共享锁和排它锁

排它锁（Exclusive），简写为 X 锁，又称写锁。可以对对象A进行读取和更新，加锁期间其他事务不能访问对象A
共享锁（Shared），简写为 S 锁，又称读锁。可以对对象A进行读取，但不能更新；期间，其他事务可以对A加读锁。

（3）意向锁（是一种表级锁）

Intention Locks 解决这种情况：在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加写锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。

引入意向锁之后，如果事务T想对表A 加表级锁，需要先检查意向锁，再检查行级锁。

一个事务在获得某个数据行对象的读锁之前，必须先获得表的意向读锁或者更强的锁；
一个事务在获得某个数据行对象的写锁之前，必须先获得表的意向写锁。

-	X 写锁	IX 意向写锁	S 读锁	IS 意向读锁
X 写锁	×	×	×	×
IX 意向写锁	×	√	×	√
S 读锁	×	×	√	√
IS 意向读锁	×	√	√	√

任意 IS/IX 锁之间都是兼容的，因为它们只是表示想要对表加锁，而不是真正加锁；
S 锁只与 S 锁和 IS 锁兼容，也就是说事务 T 想要对数据行加 S 锁，其它事务可以已经获得对表或者表中的行的 S 锁。

（4）封锁协议

一级封锁协议：事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。

可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。

二级封锁协议：在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。（读取完，不一定事务已经结束）

可以解决脏读问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。

三级封锁协议：在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。

可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。

（5）MySQL的隐式与显示锁定

innoDB默认隐式锁定，会根据隔离级别在需要的时候自动加锁，并且所有的锁都是在同一时刻被释放；也可以使用命令

SELECT ... LOCK In SHARE MODE;
SELECT ... FOR UPDATE;

（6）InnoDB有三种行锁的算法

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，那么就需要锁定以下区间：

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

7. MVCC

多版本并发控制 Multi-Version Concurrency Control 是乐观锁的一种实现方式（通过版本号控制）

MVCC 是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现读已提交和可重复读这两种隔离级别。

而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。原因是MVCC的创建版本和删除版本只要在事务提交后才会产生。

可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

版本号

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

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

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

基本原理

MVCC的实现，通过保存数据在某个时间点的快照（快照编号）来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。

根据事务开始的时间不同，在同一个时刻不同事务看到的相同表里的数据可能是不同的。一个事务无法看到版本号比自己的事务版本号高的数据

每行数据都存在一个版本，每次数据更新时都更新该版本。
修改时Copy出当前版本随意修改，各个事务之间无干扰。
保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）

增删改查

1. SELECT

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

事务T执行select操作：

所要读取的数据行快照的创建版本号必须小于等于 T 的版本号，因为如果大于 T 的版本号，那么表示该数据行快照是其它事务的最新修改，因此不能去读取它。

所要读取的数据行快照的删除版本号必须是未定义或者大于 T 的版本号，因为如果小于等于 T 的版本号，那么表示该数据行快照是已经被删除的，不应该去读取它。如果大于T的版本号，说明这条记录在T开始之后有另一个事务删除了它，此时T是可以读取的。

2. INSERT

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

3. DELETE

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

4. UPDATE

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

例： testmvcc(id,name)

insert into testmvcc values(1,"test"); 记录的创建版本号即当前事务版本号

Mysql中MVCC的使用及原理详解

update table set name= 'new_value' where id=1; 先标记旧的那行记录为已删除，并且删除版本号是事务版本号; 然后插入一行新的记录, 新记录创建版本号是当前事务版本号

Mysql中MVCC的使用及原理详解

delete from table where id=1;把事务版本号作为删除版本号

Mysql中MVCC的使用及原理详解

8. 关系数据库设计理论

三个范式：

（1）属性不可分

数据库表中的字段都是单一属性的，不可再分(保持数据的原子性)；

（2）非主属性完全依赖于主属性（主键）

每行数据能够被主键唯一区分

（3）不包含其他表中的非主键信息，即不存在冗余信息。确保数据表中的每一列数据都和主键直接相关

在满足第二范式的基础上，在实体中不存在其他实体中的非主键属性，传递函数依赖于主键属性，确保数据表中的每一列数据都和主键直接相关，而不能间接相关(表中字段[非主键]不存在对主键的传递依赖)

9. ER图

Entity-Relationship，有三个组成部分：实体、属性、联系。

用来进行关系型数据库系统的概念设计。

实体的三种联系

包含一对一，一对多，多对多三种。

如果 A 到 B 是一对多关系，那么画个带箭头的线段指向 B；
如果是一对一，画两个带箭头的线段；
如果是多对多，画两个不带箭头的线段。

下图的 Course 和 Student 是一对多的关系。

表示出现多次的关系

一个实体在联系出现几次，就要用几条线连接。

下图表示一个课程的先修关系，先修关系出现两个 Course 实体，第一个是先修课程，后一个是后修课程，因此需要用两条线来表示这种关系。

联系的多向性

虽然老师可以开设多门课，并且可以教授多名学生，但是对于特定的学生和课程，只有一个老师教授，这就构成了一个三元联系。

表示子类

用一个三角形和两条线来连接类和子类，与子类有关的属性和联系都连到子类上，而与父类和子类都有关的连到父类上。

10 什么是存储过程？用什么来调用？

答：存储过程是一个预编译的SQL 语句。

优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。

如果某次操作需要执行多次SQL ，使用存储过程比单纯SQL 语句执行要快。可以用一个命令对象exec来调用存储过程。

11 什么是触发器？触发器的作用？

答：触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。

它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

12 存储过程与触发器的区别

答：触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。

触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。