说明：内容均以mysql及InnoDB引擎 为目标

0：PrepareStatement



数据库收到客户端请求执行sql的流程

各阶段说明

1. 连接很重，应用程序要使用连接池。

2. 语法分析：sql构建语法树，语法是否有错在本阶段可检测出来。

3. 语义分析：等价转换，优化。

4. 执行引擎不是mysql的，mysql设计出来给外部扩展用的，这也是mysql更流行的原因（成功的原因，开放导致成功）。各家都可以实现不同的执行引擎，选择适合自己业务的引擎。



PrepareStatement重用

1：重用，提高效率

业务程序很多sql只是最终的参数值不同，语法分析，语义分析，执行计划方面可以重用。

PrepareStatement就将sql模板发过去，生成执行计划。

实际执行时传递参数值，效率更快。一个PrepareStatement可重复使用。

预编译结果：语句+占位符。

预编译的结果在数据库缓存了。

2：避免sql注入

提前进行了语法分析，传入的参数只能作为字符串匹配条件。

MyBatis 等Orm框架是用PrepareStatement执行的，速度快，防sql注入。

1：存储结构B+树

1.1 主键对应B+树存储

 1.1.1 存储结构      

      非叶子节点

      非叶子节点存放的是主键的值

      一个节点是一个Page,

      一个Page里面存放多个key(key+下一级相同key的节点位置），按key大小顺序排列。

      一个Page对应一个存储块，默认16K，可以设置（innodb_page_size)。

      如果用64位整数做主键，16key大约可放1000个key.

      同一层的非叶子节点间形成双向链表

     一个节点可存放多少key,就分多少叉。

      叶子节点

     节点内按主键key大小排列，存放多条记录的key，记录间连接双向链表。

每个key指向一条记录。（叶子内逻辑是两层，key 链表层，记录层）

节点间双向链表，按主键key排序



聚簇索引

数据库记录与索引存储在一起。

Mysql的数据库主键索引是聚簇索引，主键id与所在记录存在在一个B+树中，其它非主键索引不是聚簇索引。

聚簇索引



 1.1.2 树的特点

 特点：按页存，分叉多，层级少，检索快。

          由于每个非叶子节点都是按页存储，可以分很多叉。树的层级就很少，一般3层。

          比如按一个非叶子节点1000个key分叉。一个叶子节点存200个记录。

          3层节点可存记录：1000*1000*200=2亿条记录。

          容量：16k*1000*1000=16G. 

      查询举例

        如果3层树，前两层放到内存，根据主键查单个记录只需一次查盘。

        范围查询快。按主键排序方便。

 设计注意点

1.  主键递增--新增记录可避免页拆分。

2. 逻辑删除-可避免页拆分。

3. 单表容量的限制：根据b+树特点，单表记录条数亿级别，总容量几十G.超过性能下降。

4. 字段尽量小：能用小就尽量小，可以在一个页上放更多的记录，检索页快。

5. 拆出大字段：可单独用表存放，提高容量限制及检索速度。

1.2 非主键索引

      1：也用B+树存储

      2：叶子节点存放索引字段的key 及对应主键的值（可能是多个主键的值）。

     3：非主键索引检索先查索引的b+树，再查主键的b+树，查两遍树。

非主键索引不是聚簇索引。



1.3 索引使用

查询字段添加索引

根据b+树查，查几次就查到（B+树层次3-4级）。

无索引，需要遍历所有的页，性能差很多数量级。

索引不宜过多

添加索引或锁表（增删改都阻塞，连接不够用可能查也被阻塞-拿不到连接）

增删记录开销大。

用更小的数据类型创建索引。一页可存放更多索引信息。检索更快。



2：事务与锁

2.1 并发问题与隔离级别

2.1.1 并发问题

1. 脏读：读到其它事务未提交的数据，对应的事务后来回滚

2. 不可重复读：在一个事务内两次读取相同数据，后一次读到其他事务新提交的。

3. 幻读：同一个事务里两次执行select返回数据不一样，因为其他事务在执行insert|delete提交。

4. 丢失更新：事务A,B 同时执行读取记录X后，,然后分别更改，先提交的更改被覆盖。

2.1.2 隔离级别

1. RU:读未提交

2. RC：读已提交

3. RR：可重复读

4. Serialization：串行执行

2.2.3 对应关系

RU:不能解决并发问题任何一种

RC:可解决脏读

RR:可解决 脏读，不可重复读，幻读。-一般采用

S:可解决所有问题，但性能差，一般不使用。



2.2 解决丢失更新

从性能要求上，隔离级别不能采用串行模式。如何解决更新丢失问题

2.2.1 原子性更新sql

方案

select,读取值，然后对值操作 ，在update ,调整为一条sql，对于对数值字段的增减适用。

比如：消费总金额，人数总计数增加。

update t1 set f1=f1+n;

问题

适用场景有限。



2.2.2 悲观锁

方案

认为发生冲突概率大，查询前上锁，其他事务查的时候就会阻塞。

start transaction

v=select v from t1 where id=n for update ;//先加写锁，后续的需要排队

newV=f(v)

update t1 set v=newV where id=n

commit



问题：

对性能影响较大，读取后加写锁，导致其他事务读也会阻塞。

容易导致死锁

2.2.3 乐观锁

方案

认为发送冲突的概率小，读的时候不加锁，写的时候判断是否变更，采用CAS模式，提交。

如果变更重新读取。

比如可以添加一列版本字段，或就使用记录的变更时间戳字段。

while (!result){

{

开启事务

查询value,对应版本v1

计算

更新：update t1 set v=newV ,version=v1+1 where id =n and version=v1

}

问题

适用特定场景

如果更新的单个记录可以，如果是多种数据查询，运算的，更新的是多个记录，实现起来对业务侵入过大。



2.2.4 分布式锁

实际中使用比较多的一种。

方案

根据业务特性选择合理临界区，加分布式锁（入用redis加锁），更新后，提交事务，释放锁。

过程如下：

1. 加分布式锁

2. 开启事务

3. 查询

4. 更新

5. 提交事务

6. 释放分布式锁

顺序不能乱。



问题

1：防止节点故障，锁要有合适的有效期

2：确定合适的临界区。



有效期缓冲期短遇到数据库压力大导致的问题：

实际中遇到这样的情况，更新一般需要耗时几十毫秒，分布式锁用了3秒的有效期。实际中出现一次数据库压力大导致更新过程超过3秒，另一个请求进来查数据查到的是更新前的数据，然后更新，出现更新覆盖。

如果数据安全性较高，丢失更新影响较大，加长分布式锁的有效期（正常程序会自动释放，有效期主要为防止节点故障）。



合理临界区

1：不能过小:

临界区要包括对数据的更新及对数据更新的值有影响的查询，否则新请求查到的是老数据出现更新覆盖。

2：不能过大

如果一个请求更新多个数据，同时分布式服务下还要调用其他服务等耗时操作，如果对其他服务的调用不会导致本次更新的回滚，将对其他服务的调用放在临界区外，提交事务后，释放锁。再调用其他服务。

3：性能与安全冲突

如果数据更新后要调用其他服务，其他服务失败会导致事务回滚，分布式锁临界区必须包括调用其他服务的部分。但这样也会导致对程序的并行性影响较大。因此相同数据更新频繁的切更新成功依赖其他服务的不适应。

2.3 事务实现原理与特点

2.3.1 事务的要求

1：执行结果与串行化相同:

事务运行并行执行，但要满足执行结果跟串行化执行相同的。

mysql的主从同步的binglog 就是串行记录的多个事务。

因此本库的执行也要跟串行执行相同。

2：满足数据库事务的ACID特性。

A:原子性

要么不执行，要么全执行，即便宕机，重启后要能将执行部分的回滚。

C:一致性

满足表的各种设计约束。

I: 隔离性

2.1中介绍的并发问题与隔离级别。事务隔离用串行执行就都解决了，但一般用可重复读级别。

满足不出现：脏读，幻读，不可重复读。

D:持久性

一旦提交成功，不能丢失。

2.3.2 事务实现

mysql,InnoDB 通过以下机制实现事务，满足ACID特性。

背景-数据页缓存与刷盘

为了性能，数据库对更新或查询的页都会在内存中缓存。

比如：更新一个记录

先查询到记录所在页，加入缓存，更新，后续刷到磁盘。

内存中效率高，易丢失。

主要机制：RedoLog,UndoLog,MVCC(多版本），锁



1：RedoLog

日志的顺序写与数据页的定时刷盘

一次事务可能更新多个表的多个记录，对应数据库存储的多个物理页。如果每次更新都刷到磁盘，会有很多次随机写磁盘。磁盘的随机写性能是比较差的。

如果不刷到磁盘，断电会导致数据丢失。

Writ-Ahead:

内存中提交事务（更新内存中缓存数据），然后写日志（RedoLog）,记录具体更新内容。日志文件只顺序递增的。写的效率高，定期将内存中更新的页刷到磁盘。

如果系统发生故障，通过Redolog里的记录恢复提交的日志，回滚未提交的日志。



Redolog 本身什么时候刷到磁盘？

可配置，入1s一次（默认），每提交一次事务一次（最安全）



---真的突然断电，提交的事务有可能丢失。-日志与页都没有刷到磁盘上。

保证I/O写入的原子性：

日志文件写入磁盘时，可能写到中途断电，没一个日志单元结尾都有一个checksum，如果不完整，重启后丢弃不完整部分。

数据文件双写：数据页刷新到磁盘时，先写入临时文件，写成功后在拷贝。如果拷贝中失败，重启时可重新拷贝，如果写临时文件失败，丢弃临时文件，从事务日志恢复。保证数据页文件不会出现毁坏的文件。



Redolog 是递增的，在数据更新过程中不断写入（不是提交事务才写的）。如果事务回滚，会按之前的更新逐步生成逆操作，写入日志。

因此回滚事务的RedoLog 是： 更新1，更新2 ，更新2的逆操作，更新1的逆操作。



2：UndoLog与多版本

Undolog 备份在事务提交前修改的数据，如果有多个事务先后对同一个数据修改，同时有其他未提交的事务引用这个数据，就会记录数据的多个版本。

比如：

事务A:读数据（版本1），改数据（版本2），提交

事务B: 读数据（版本1）

事务C: 读数据（版本2）改数据（版本3） 提交

事务D 读数据(版本2）



事务B 引用的是版本1,事务D引用的是版本2，如果当前再有新的事务读取获取到的是最新的版本3.

MVCC实现可读写的隔离性。



3：锁

MVCC实现读写的隔离性。如果两个事务要同时修改一个数据(写并发）如何处理？根据事务的特点

要求事务并发执行的最终结果要跟串行执行一致，不能允许多个事务并发更改相同的数据，需要锁解决。

通过锁在避免并发写冲突。

锁的种类

从锁的特点上有：共享锁（读锁），排它锁（写锁），意向锁（共享|排它），自增锁（id自增）。

按锁的数据: 行锁，表锁。



意向锁说明：

事务A对行加了排它锁，其他事物就不能对表加排它锁。

事务A对表加了排它锁，其他事务就不能对表的任何一行再加排它锁。-修改表会阻塞所有的更改。



2.3.3 死锁与处理

2.3.3.1 死锁产生原因

逐步扩张，交叉修改

锁获取过程是逐步获取的（不断扩展），提交事务后全部释放。

事务A: 开启事务 获取X的锁,修改记录x, 获取Y的锁,修改记录y 提交事务

事务B: 开启事务 获取Y的锁 ,修改记录y, 获取X的锁 修改记录x 提交事务

由于锁是逐步扩张的，提交时才释放，如果两个事务同时按不同顺序修改两个数据会导致死锁。



2.3.3.2 数据库对死锁的处理：

MySQL有两种死锁处理方式：

1. 等待，直到超时（innodb_lock_wait_timeout=50s）。

2. 发起死锁检测，主动回滚一条事务，让其他事务继续执行（innodb_deadlock_detect=on）。

由于性能原因，一般都是使用死锁检测来进行处理死锁。



死锁检测

死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。

回滚

检测到死锁之后，选择插入更新或者删除的行数最少的事务回滚，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。

2.3.3.3 如何避免发生死锁

收集死锁信息：

1. 利用命令 SHOW ENGINE INNODB STATUS查看死锁原因。

2. 调试阶段开启 innodb_print_all_deadlocks，收集所有死锁日志。

减少死锁：

1. 使用事务，不使用 lock tables 。

2. 保证没有长事务。

3. 操作完之后立即提交事务，特别是在交互式命令行中。

4. 如果在用 (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE)，尝试降低隔离级别。

5. 修改多个表或者多个行的时候，将修改的顺序保持一致。

6. 创建索引，可以使创建的锁更少。

7. 最好不要用 (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE)。

8. 如果上述都无法解决问题，那么尝试使用 lock tables t1, t2, t3 锁多张表



2.4 总结

Redolog +UndoLog 实现事务的原子性与持久性

通过Undolog （多版本）实现数据的读写隔离性。

锁实现数据的写写互斥。

多版本+锁 保证了隔离性。



3：主从复制

3.1主从复制的原理

主库写binlog

收到客户端commit命令后，将更新逻辑写入binlog,然后提交事务。

binlog事务间串行。

binlog的刷盘策略：sync_binlog 可设置 0,1，n(数） ，1最安全，提交一个事务刷一次盘，性能不高。

一般是若干次提交后刷一次盘。



从库获取binlog,回放

主从间同步binlog文件，从库同步到后只本地回放。

3.2 一致性

3.2.1 binglog 与redolog 的一致性

解决机器进程故障后重启binlog,redolog的事务一致。

binlog与RedoLog一致性采用的是2阶段方案

1：准备阶段

提交事务前

内存中写入binlog-状态是为提交

innodb写入redolog

2:提交

客户端提交，

binlog 提交-刷盘

redolog 提交。

3：故障处理

redolog想binlog靠近。

如果binlog提交失败，redolog中没有提交，会执行回滚。

如果binlog提交，redolog没有提交，redolog发起提交。

3.2.2 主从一致性

为防止主库宕机后，从库接替主库。

1：同步复制

从库回放成功，主库在返回客户端成功。性能一般不能接受。

2：半同步

部分从库返回成功，主库返回客户端成功

3：异步

不等待从库的反馈。异步复制到从库。

参考：

书籍：软件架构设计-余春龙

课程：极客时间架构师训练营-李智慧

https://xie.infoq.cn/article/ced6e4bb6ca403bc87a769b59

https://www.cnblogs.com/hhthtt/p/10707541.html