SQL数据库核心构成

对于 B+树而言，树的高度一般不超过 4 层，就 MySQL 的 InnoDB 存储引擎而言，一个结点默认的存储空间为 16Kb ( 可以通过这个命令查看SHOW GLOBAL STATUS like 'Innodb_page_size'; )， MySQL 的 InnoDB 存储引擎的索引一般用 bigint 存储，占用 8 个 byte，一个索引又会关联一个指向孩子结点的指针，这个指针占用 6 个 byte，也就是说结点中的一个关键字大概要用 14 byte 的空间，而一个结点的默认大小为 16kb ，那么一个结点可以存储关键的个数最多为 16384/14=1170 , 就相当于阶数 �=1170 ，那么对于一颗高度为 3 的 B+树而言保守估计可以存储 1170×1170×16=21902400 个关键字，也就是两千多万条记录，其中的 16 为假定每一个叶子结点包含的关键字的个数（由于包含 Data 指针，所以叶子结点可以容纳的关键字的个数会少一些），就这样我想你也看到了 B+树的强大了。3层的 B+树就可以存储两千多万的数据，牛逼不？

上面的B+数知识是oracle和mysql共用的,剩余的细节知识两者也是大差不差.下面B+树往下延伸的知识我放到oracle和mysql的文章专栏了.

二.SQL数据库的事务与并发

1.事务自动提交模式

MySQL 默认采用事务自动提交模式。也就是说，如果不显式使用START TRANSACTION语句来开始一个事务，那么一条单独的(查询,增加,删除,修改)的sql都会被当做一个事务自动提交。

不手动显示开启事务

手动显示开启事务

2.事务并发的四个问题

1)丢失更新

A 改--查 , B在A 的改--查中间改.

2)脏读

A 改--回滚 , B在A 的改--回滚中间读.

3)不可重复读

A 读--读 , B在A 的读--读中间改.

4)幻读

A count(*)读--count(*)读 , B在A 的count(*)读--count(*)读中间增.

上面四个问题是事务在并发情况下产生了不一致性问题,主要原因是破坏了事务的隔离性，

解决方法是通过并发控制来保证隔离性。并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，相当复杂。

因此,数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题

三.封锁

封锁协议是数据库自带的

是为了解决数据库的事务的并发出现的四种问题(丢失更新,脏读,不可重复读,幻读)

锁从性质上来说可以分为两类:共享锁和排他锁

锁从封锁的颗粒度来说可以分为三类:表锁,行锁,页锁

需要知道的是,共享锁和排他锁只是锁的性质不同,它们加在表上就是表锁,加在行上就是行锁

1.共享锁和排他锁

共享锁(S)---就是读锁,得到数据的共享锁可以读这个数据.

排他锁(X)---就是写锁,得到数据的排他锁可以读和写这个数据.

锁的兼容关系如下:

一个事务拿到了共享锁(S),其他事务可以拿到共享锁(S),不能拿到排他锁(X).

一个事务拿到了排他锁(X),其他事务不能拿到共享锁(S),不能拿到排他锁(X).

2.表锁

1)分类有三种

1.共享锁(S):当表上有共享锁(S)时,其他的事务可以拿到表的共享锁(S),可以对表中的数据进行读操作,但是不能拿到表的排他锁(X),不能对表中数据进行修改.

2.排他锁(X):当表上有排他锁(X)时,其他的事务不能拿到表的共享锁(S)和排他锁(X).也就不能对表的数据进行读和写.

3.AUTO-INC Locks 锁:自增锁

Auto-Inc Lock 是一个特殊的表级锁，用于自增列插入数据时使用。在插入一条数据的时候，需要在表上加个 Auto-Inc Lock，然后为自增列分配递增的值，在语句插入结束之后，再释放 Auto-Inc Lock。

在 MySQL 5.1.22 版本之后，又弄了个互斥量来进行自增减的累加。互斥量的性能高于 Auto-Inc Lock，因为 Auto-Inc Lock是语句插入完毕之后才释放锁，而互斥量是在语句插入的时候，获得递增值之后，就可以释放锁，所以性能更好。

但是我们还需要考虑主从的情况，由于并发插入的情况，基于 statement -based binlog 复制时，自增的值顺序无法把控，可能会导致主从数据不一致。

所以 MySQL 有个 innodb_autoinc_lock_mode 配置，一共有三个值：

0，只用 Auto-Inc Lock。
1，默认值，对于插入前已知插入行数的插入，用互斥量，对于插入前不知道具体插入数的插入，用 Auto-Inc Lock，这样即使基于 statement -based binlog 复制也是安全的。
2，只用互斥量。

2)引擎默认用表锁

Myisam

3)特点

1.对整张表加锁

2.开销小,加锁,解锁都需要性能开销

3.加锁快

4.无死锁

5.锁颗粒度大,发生锁冲突概率极高,并发性极低

4)什么时候用到表锁

平日的 update 、select 要用也是用行锁了，不可能用粒度粗的表锁。唯一能想到用上表锁的就是 DDL 语句了，比如 ALTER TABLE 的时候，应该锁定整个表，防止查询和修改，但是这个 server 已经提供了一个叫 MDL 的东西，即 Metadata Locks，所以已经用 MDL 来阻塞了，表锁也就排不上用场了。

真要用表锁，估计也就是数据恢复的时候，手动锁表还原数据了

3.行锁

1)分类

分类有下面几种:

共享锁(S),排他锁(X),

意向共享锁(IS)和意向排他锁(IX),

Record Lock锁(记录锁),

Gap Lock锁(范围锁,间隙锁),

Insert Intention Locks(插入意向锁)

next-key Lock锁(临键锁)

1.共享锁(S)和排他锁(X)上面提过就不说了

2.意向共享锁(IS)和意向排他锁(IX)

使用意向锁(Intention Locks)可以更容易地支持多粒度封锁。

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

意向锁在原来的 X/S 锁之上引入了 IX/IS，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定:

一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。

通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

各种锁的兼容关系如下:

3. Record Lock锁(记录锁)

只锁住一行记录,叫记录锁,其实就是共享锁(S)和排他锁(X)只锁住了一行数据记录.

注意,我们说的锁一行数据记录,它的原理其实是去锁这行数据的索引.

下面以两条 SQL 的执行为例，讲解一下 InnoDB 对于单行数据的加锁原理。

update user set age = 10 where id = 49;update user set age = 10 where name = 'Tom';

第一条 SQL 使用主键索引来查询，则只需要在 id = 49 这个主键索引上加上写锁；第二条 SQL 则使用二级索引来查询，则首先在 name = Tom 这个索引上加写锁，然后由于使用 InnoDB 二级索引还需再次根据主键索引查询，所以还需要在 id = 49 这个主键索引上加写锁，如上图所示。

也就是说使用主键索引需要加一把锁，使用二级索引需要在二级索引和主键索引上各加一把锁。

4.Gap Lock锁(范围锁,间隙锁)

锁住一个范围.比如select * from stu where id>1 and id < 5 for update,锁住id>1和id<5(不包括1和5本身)这个区间,保证这个区间不会有其他数据插入.

不同的间隙锁锁住的区域重叠了也不会冲突,因为间隙锁的唯一目的就是防止其他事务插入数据到间隙中，所以即使两个间隙锁要锁住相同的间隙也没有关系，因为它们的目的是一致的，所以不冲突。

5.插入意向锁(Insert Intention Locks)

它也是一类间隙锁，但是它不是锁定间隙，而是等待某个间隙。比如上面举例的 id = 4 的那个事务 C ，由于被间隙锁给阻塞了，所以事务 C 会生成一个插入意向锁，表明等待这个间隙锁的释放。

并且插入意向锁之间不会阻塞，因为它们的目的也是只等待这个间隙被释放，所以插入意向锁之间没有冲突。

插入意向锁并没有什么用,只是排个队等待别人释放间隙锁.

6.Next-key Lock锁 (临键锁)

相当于记录锁+范围锁,同时锁住记录本身+间隙,锁的是一个左开又闭的范围,

比如select * from stu where id>2 for update ,锁住了表中 id 范围为 (2, +∞] (包括2)的范围,

比如select * from stu where id>1 and id < 5 for update ,锁住id>1和id<5(包括1和5本身)这个区间,保证这个区间不会有其他数据插入.

2)引擎默认用行锁

innoDB

3)特点

1.对行数据加锁

2.性能开销大

3.加锁慢

4.有几率出现死锁

5.锁颗粒度最小,发生锁冲突概率最低,并发度最高

4.页锁

开销,加锁时间和锁粒度介于表锁和行锁之间,会出现死锁,并发处理能力一般(此锁不做介绍)

5.如何上锁

说明:正确的应该是说成拿到表或行的锁, 而不是说成给表和行加锁.虽然两种说法最后的结果都是一样的.

下面讲的还有其他锁,太深奥了,看不懂,下面就不举例介绍了.

四.三种级别的封锁协议

1. 一级封锁协议

解决事务并发的(丢失更新)问题

2.二级封锁协议

解决事务并发的(丢失更新)和(脏读)问题

3.三级封锁协议

解决事务并发的(丢失更新)和(脏读)和(不可重复读)问题

4.两段锁协议(解决幻读问题)

解决事务并发的(丢失更新)和(脏读)和(不可重复读)和(幻读)问题

两段锁协议是对三级封锁协议的优化,它在三级封锁协议的基础上,允许事务没有结束也可以释放锁.

加锁和解锁分为两个阶段进行。

就是事务运行分两个阶段

加锁阶段:事务执行开始,然后不断的加锁,但是途中不能释放锁.

解锁阶段:事务不断的释放锁,但是途中不能再加锁,直到事务结束.

可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。

事务遵循两段锁协议是保证可串行化调度的充分条件。例如以下操作满足两段锁协议，它是可串行化调度。

lock-x(A)...lock-s(B)...lock-s(C)...unlock(A)...unlock(C)...unlock(B)

但不是必要条件，例如以下操作不满足两段锁协议，但是它还是可串行化调度。

lock-x(A)...unlock(A)...lock-s(B)...unlock(B)...lock-s(C)...unlock(C)

5.可串行化调度(解决幻读问题)

解决事务并发的(丢失更新)和(脏读)和(不可重复读)和(幻读)问题

什么是串行化,就是只有当A事务完全执行完毕,成功提交后,B事务才能执行,就是事务一个一个的执行.

可串行化调度就是允许在A事务执行的时候B事务也可以执行,但是要经过合理的调度,保证最终A事务和B事务的结果都是跟串行化一样是正确无误的.

百度解释:多个事务的并发执行是正确的，当且仅当其结果与按某一次序串行化执行这些事务时的结果相同。

优点:可串行化调度能保证并发的事务调度方式既能满足数据一致性需求，又能提高并发事务的执行效率。

五.事务的隔离级别

1.读未提交(read uncommitted)

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

只能解决(丢失更新)

2.读已提交(read committed)

允许一个事务可以读取另一个已经提交事务的数据

只能解决(丢失更新)和(脏读)问题

3.可重复读取(Repeatable Read)

在一个事务内部多次读取同一个数据对象,结果一致

只能解决(丢失更新)和(脏读)和(不可重复读)问题

4.可串行化(SERIALIZABLE)

强制事务串行执行,可以解决(丢失更新)和(脏读)和(不可重复读)和(幻读)问题。

数据库针对于事务的封锁协议	事务隔离级别	丢失更新	脏读	不可重复读	幻读
一级封锁协议	读未提交	×	√	√	√
二级封锁协议	读已提交	×	×	√	√
三级封锁协议	可重复读	×	×	×	√
两段锁协议+可串行化调度	可串行化	×	×	×	×

六.MVCC

MVCC是多版本并发控制 Multi-Version Concurrent Contrl。

SQL数据库都有MVCC,只不过它们的实现方式略有不同,但是总体思路和下面讲的内容是一样的.

它是SQL数据库中的提高并发性能的一种方式，配合Undo log 和版本链，替代锁，去实现提交读和可重复读这两种隔离级别。

下面以mysql的InnoDB引擎的mvcc为例

1.必须知道的mvcc的特点

1.mysql和oracle都默认开启使用了mvcc.是自带的, 这个关不掉,不用也得用.

2.mysql和oracle两者实现mvcc的方式虽然有点差别,但是核心设计方式都是一样的.

3.mvcc是不加锁的,所有加锁的都不是mvcc的内容.

4.mvcc就一个作用,就是只能实现快照读的事务并发隔离

5.因为只有(脏读/不可重复读)是快照读的问题,所以mvcc只能解决这两种问题.所以mvcc只能实现RC和RR这两种隔离级别.

2.简单描述mvcc怎么做的

每次select操作会访问数据库中的版本链记录，其他事务可以修改这条记录，而select根据当前隔离级别去版本链中找到对应的版本记录，实现了读-写，写-读的并发执行，提升了系统的性能。

3.当前读

当前读: 特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
-- 所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加共享锁外，其他的操作，都加的是排它锁。

4.快照读

简单的select操作，属于快照读，不加锁。不加读锁读历史版本

5.事务的ID

事务的id是数据库自动给事务的.是一个自增数字,每开启一个新事物,这个数字就+1,然后给这个新的事务,当做这个新事务的id.

6.ReadView

RC级别下,事务每次快照读都会自动生成一个ReadView.

RR级别下,事务只在第一次快照读自动生成一个ReadView,后面再快照读会复用此ReadView.

快照读的一种形式，MySQL执行MVCC提取数据的依据；本质是一个数据结构，其中包括几下几个部分：
1. m_ids：当前活跃的事务ID集合（开启但未提交的事务ID）；
2. min_trx_ids:最小活跃事务ID，小于它说明事务已经提交；
3. max_trx_ids:预分配的事务ID（下一个事务开启分配的事务ID），即当前最大事务ID+1；大于它说明该事物是ReadView生成之后才开启；
4. creator_trx_id:ReadView创建者的事务编号；

7.快照

一条行数据当被修改(insert,update,delete)时,都会把原来的数据记录下来生成一个快照,记录到undo log 日志的版本链中.

8.版本链

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

9.行隐藏的字段

MySQL数据库中，每条数据除肉眼可见的数据之外，还有几个隐藏字段：
1.db_trx_id:表示最近一次对本记录行作修改（insert | update）的事务ID。至于delete操作，InnoDB认为是一个update操作，不过会更新一个另外的删除位，将行表示为deleted。并非真正删除。
2.db_roll_pointer:回滚指针，指向指向这条数据的上一个版本记录；
3.db_row_id:随着新行插入而单调递增的行ID。理解：当表没有主键或唯一非空索引时，innodb就会使用这个行ID自动产生聚簇索引。如果表有主键或唯一非空索引，聚簇索引就不会包含这个行ID了。这个DB_ROW_ID跟MVCC没关系。

10.mvcc实现RC和RR的唯一区别

实际上，RC和RR两个隔离级别，实现的主要区别在于ReadView的时机不一样，RC隔离级别下，每次快照读都会生成一个ReadView；而RR级别下，只有在第一次快照读时生成ReadView，后面会延续使用；事务根据生成的ReadView去版本链中寻找自己能够访问的数据，最后得到所读的数据；

11.寻找可读数据的规则

事务是如何根据生成的ReadView去版本链中寻找自己能够访问的数据的
判断规则如下：
1.当前记录修改操作事务ID 等于 ReadView创建事务ID ? 等于说明是自身事务修改的数据，可以访问；
2.判断 trx_id < min_trx_id(2) ? 如果小于说明事务提交了，可以访问；
3. 判断trx_id >= max_trx_id(5) ? 成立说明该事物是在ReadView生成之后才开启的，不允许访问，不成立继续向下判断；
4. 判断min_trx_id <= trx_id < max_trx_id,成立则在m_ids中对比，如果不存在说明事务已经提交，可以访问；
5. 不满足以上条件，根据回滚指针依次往前一个版本数据查找，直到找到满足条件的数据；

12.Undo log

Undo log中存储的是老版本数据。
大多数对数据的变更操作包括 insert/update/delete，在InnoDB里，undo log分为如下两类：
①insert undo log : 事务对insert新记录时产生的undo log, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。
②update undo log : 事务对记录进行delete和update操作时产生的undo log，不仅在事务回滚时需要，快照读也需要，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被purge线程删除。

如下图:事务A的改动的这行数据的版本快照记录在undo log 中,现在事务回滚用不到它,而且也没有readview用到它了,因此这个事物A的版本快照就会被purge线程删除.

Purge线程

为了实现InnoDB的MVCC机制，删除操作都只是设置一下旧记录的deleted_bit，并不真正将旧记录删除。
为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。purge线程自己也维护了一个read view，如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

13.记录行修改的具体流程

注意:(删除,新增,修改)都被看做是修改

假设有一条记录行如下，字段有Name和Honor，值分别为"curry"和"mvp"，最新修改这条记录的事务ID为1。

（1）现在事务A（事务ID为2）对该记录的Honor做出了修改，将Honor改为"fmvp"：

①事务A先对该行加排它锁
②然后把该行数据拷贝到undo log中，作为旧版本
③拷贝完毕后，修改该行的Honor为"fmvp"，并且修改DB_TRX_ID为2（事务A的ID）, 回滚指针指向拷贝到undo log的旧版本。（然后还会将修改后的最新数据写入redo log）
④事务提交，释放排他锁

（2）接着事务B（事务ID为3）修改同一个记录行，将Name修改为"iguodala"：

①事务B先对该行加排它锁
②然后把该行数据拷贝到undo log中，作为旧版本
③拷贝完毕后，修改该行Name为"iguodala"，并且修改DB_TRX_ID为3（事务B的ID）, 回滚指针指向拷贝到undo log最新的旧版本。
④事务提交，释放排他锁

14.MVCC扩展细节

细节1:

一行数据有很多版本快照,但是只有最新版的快照(事务已经成功提交的)是真实存在的,之前的版本快照都不是真实存在的,用到的话是从最新版的快照根据undo log的操作记录倒推出来的,也就是说undo log主要记录了数据的逻辑变化，比如一条INSERT语句，对应一条DELETE的undo log，对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态。

细节2:

mysql的事务已提交的一行的数据存在磁盘中, 而这行数据的历史记录数据存在undo log日志中

细节3:

我通过代码测试验证得知,事务没有提交之前,它对行数据所做的任何增删改都是对外不可见的,都是在事务自己的事务空间里.

细节4:delete行数据的删除

先说结论:不会物理删除,而是会把这行数据的删除位标记为delete.

delete删除一行数据A,发生的4个过程

1.复制一份这行数据A存进undo log日志中,以便后面回滚或者恢复数据.

2.在这行数据A的删除位标记为delete.

3.有一个垃圾链表,专门记录被删除数据的空间位置,在垃圾链表中记录这行数据A的空间位置,把这行数据A占用的空间标记为可重用空间,

4.之后有新插入行数据的时候,比如,子叶节点有4个数据,id分别是3,5,6,9,那么这个时候你把id=5的这行数据A删除了,插入进来一个id=4的,那么就会把这行数据A进行物理删除,把空间腾出来给新插入的id=4的行数据.

为什么数据库要这么设计

因为如果直接物理删除的话,那么这行数据的空间就被空出来了,那么,数据库的索引结构B+数强大的平衡算法检测到这个空间没有数据后,为了数的平衡性,就会把其他的数据往这个空间移动,那么就会造成整个索引结构的变化,会产生大量的磁盘IO,这是很耗费时间并且影响性能的.

细节5:在可重复读的隔离级别下,mvcc是真的防止幻读了吗

先说结论:

在可重复读的隔离级别下,mvcc只能很大程度上避免幻读,但是不能完全解决幻读.

下面说的都是在在可重复读的隔离级别下的.

1.快照读是怎么避免幻读的

mvcc使用Read View+undo log解决了快照读的幻读问题.

2.当前读是怎么避免幻读的

我们假设select ... for update当前读是不会加锁的（实际上是会加锁的），在做一遍实验。

这时候，事务 B 插入的记录，就会被事务 A 的第二条查询语句查询到（因为是当前读），这样就会出现前后两次查询的结果集合不一样，这就出现了幻读。

所以，Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题，就引出了Next-key Lock锁 (临键锁=记录锁+间隙锁)。（额外提一句，读提交隔离级别，是没有临键锁的，只有记录锁）

假设，表中有一个范围 id 为（3，5）临键锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

比如select * from stu where id>2 for update ,锁住了表中 id 范围为 (2, +∞] (包括2)的范围

举个具体例子，场景如下：

事务 A 执行了这条当前读语句后，就在对表中的记录加上 id 范围为 (2, +∞] 的 next-key lock（next-key lock 是间隙锁+记录锁的组合）。

然后，事务 B 在执行插入语句的时候，判断到插入的位置被事务 A 加了 next-key lock，于是事物 B 会生成一个插入意向锁，同时进入等待状态，直到事务 A 提交了事务。这就避免了由于事务 B 插入新记录而导致事务 A 发生幻读的现象。

3.幻读被彻底解决了吗？

可重复读隔离级别下虽然很大程度上避免了幻读，但是还是没有能完全解决幻读。

我举例两个可重复读隔离级别发生幻读现象的场景。

第一个发生幻读现象的场景

在可重复读隔离级别下，事务 A 第一次执行普通的 select 语句时生成了一个 ReadView，之后事务 B 向表中新插入了一条 id = 5 的记录并提交。接着，事务 A 对 id = 5 这条记录进行了更新操作，在这个时刻，这条新记录的 trx_id 隐藏列的值就变成了事务 A 的事务 id，之后事务 A 再使用普通 select 语句去查询这条记录时就可以看到这条记录了，于是就发生了幻读。

第二个发生幻读现象的场景

除了上面这一种场景会发生幻读现象之外，还有下面这个场景也会发生幻读现象。

T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。

T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；

T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select ... for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

再说一下结论:MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。