一、困之背景

技术源于生活，又高于生活。

首先，让我们来还原一下前方战线不知名记者的在河南采访境遇，虚构场景对话内容如下：

记者问：大爷，要说咋个“MySQL”，你觉得是个啥？

大爷答：啥，妈呀傻球啦！啥库跑路的不是俺，别踩啦。

记者愁：？？？？？？

从上面记者与大爷的对话，我们可以了解到大爷在日常开发使用MySQL时，所遭遇的围城之困，下面让我们来一步一步为大爷排忧解惑，突破MySQL之境。

二、困之架构

2.1、连接层

负责处理客户端的连接以及权限的认证

2.2、服务层

MySQL的查询语句在服务层内，经过查询缓存、分析器、优化器和执行器模块，进行词法语法分析、执行计划优化、缓存以及内置函数的实现和操作存储引擎，返回具体结果

2.3、存储引擎层

负责数据的存储和提供读写接口，复制硬件设备的数据存储

三、困之锁

3.1、锁之说

锁是计算机协调多个进程或线程并发访问某一资源的机制（保证系统数据访问的一致性）。

3.2、表锁

MyISAM和InnoDB都支持表锁。开销小，加锁快；不会出现死锁；锁粒度最大，发生锁冲突的概率最高，并发度最低

3.3、页锁

粒度介于行级锁和表级锁中间的一种锁，表示对页进行加锁。

3.4、行锁

MyISAM不支持行锁，InnoDB支持行锁。开销大，加锁慢；会出现死锁；锁粒度最小，发生锁冲突的概率最低，并发度最高

3.5、排他锁（Exclusive Locks）

排他锁，也称X写锁，允许获取排他锁的事务进行读写数据，阻止其他事务获取相同数据集的读锁和写锁

若事务a对数据对象data加上X锁，事务a可以读data也可以修改data；其他事务不能再对data加任何锁，直到a释放data上的锁为止。

InnoDB的行锁是通过给索引项加锁实现的，这种行锁的实现特点意味着：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁

3.6、共享锁（Share Locks）

共享锁，又称S读锁，获取行数据的共享锁的事务可以进行行数据的读取，但是不能修改；其他事物也可以获取该行数据的共享锁，但是不能获取排他锁。

事务a对数据对象data加了S锁，则事务a可以读data但不能修改data；其他事务只能再对data加S锁，而不能加X锁，直到事务a释放data上的S锁；这保证了其他事务可以读data，但在事务a释放data上的S锁之前不能对data做任何修改

3.7、场景案例

MySQL InnoDB引擎默认的修改数据语句：update、delete、insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁。

可以使用select …for update（加排他锁），使用select … lock in share mode（加共享锁）；所以加过排他锁的数据行在其他事务中是不能修改的，也不能通过for update或lock in share mode的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制

案例一
1、创建无索引的表
create table tf_arr_member_test(id int,name varchar(10)) engine=innodb;
insert into tf_arr_member_test values(1,'itbigbird1'),(2,'itbigbird2'),(3,'itbigbird3'),(4,'itbigbird4');
2、关闭自动提交事务
set autocommit=0;
3、两个线程操作
①不加锁
线程a：select * from tf_arr_member_test where id = 1; -- 查询成功
线程b：select * from tf_arr_member_test where id = 2; -- 查询成功
②加写锁
线程a：select * from tf_arr_member_test where id = 1 for update; -- 查询成功
线程b：select * from tf_arr_member_test where id = 2 for update; -- 查询堵塞
b线程查询id=2被阻塞，说明无索引加的表锁，a线程提交事务b才能返回结果
4、给tf_arr_member_test的id列加索引
alter table tf_arr_member_test add index idx_id(id);
5、两个线程操作
加写锁
线程a：select * from tf_arr_member_test where id = 1 for update; -- 查询成功
线程b：select * from tf_arr_member_test where id = 2 for update; -- 查询成功
a，b线程分别查询不同记录加锁，查询成功，说明有索引加的行锁
结论：查询列带索引，加行锁；不带锁，加表锁。
案例二
①读锁
线程a：select * from tf_arr_member_test where id=1 lock in share mode; --操作成功
线程b：select * from tf_arr_member_test where id=1 lock in share mode; --操作成功
某一线程持有一数据行的读锁，其他线程也可以对该数据行加读锁
②写锁
线程a：select * from tf_arr_member_test where id=1 for update; --操作成功
线程b
select * from tf_arr_member_test where id=1 for update; --阻塞等待
select * from tf_arr_member_test where id=1 lock in share mode; --阻塞等待
select * from tf_arr_member_test where id=2 for update; --操作成功
某一线程持有一数据行的写锁，其他线程对该数据行加锁（读锁，写锁）都会进行阻塞等待，可以操作其他数据
③对索引加锁再理解
插入相同id不同name的数据（id列带索引）：insert  into tab1 values(1,'itbigbird'),(1,'以数据之名');
线程a
select * from tf_arr_member_test where id=1 and name='itbigbird' for update; --操作成功
线程b
select * from tf_arr_member_test where id=1 and name='以数据之名' for update; --阻塞等待
虽然查询的不是同一数据，但是查询使用的是同一索引，对索引加锁，所以线程b会阻塞等待
④同一线程加锁
先加读锁，再加写锁
线程a：select * from tf_arr_member_test where id=1 lock in share mode; --操作成功
线程a：select * from tf_arr_member_test where id=1 for update; --操作成功
先加写锁，再加读锁
线程a：select * from tf_arr_member_test where id=1 for update; --操作成功
线程a：select * from tf_arr_member_test where id=1 lock in share mode; --操作成功
同一线程可以对同一数据反复加锁（读锁、写锁）

四、困之事务

事务之间的隔离，是通过锁机制实现的。当一个事务需要对数据库中的某行数据进行修改时，需要先给数据加锁；加了锁的数据，其它事务是不运行操作的，只能等待当前事务提交或回滚将锁释放。

4.1、两种操作

Commit（提交）：将事务执行结果写入数据库。

Rollback（回滚）：回滚所有已经执行的语句，返回修改之前的数据。

4.2、隔离级别

在实际的数据库设计中，隔离级别越高，导致数据库的并发效率会越低；而隔离级别太低，又会导致数据库在读写过程中会遇到各种乱七八糟的问题。因此在大多数数据库系统中，默认的隔离级别时读已提交（如Oracle）或者可重复读RR（MySQL的InnoDB引擎）。

Read Uncommitted（读未提交）：允许一个事务读取另一个事务未提交的数据，可能会发生脏读，幻读，不可重复读（不建议使用）

Read Committed（读已提交）：一个事务只能读取另一个事务已经提交的数据，可以避免脏读；可能发生幻读、不可重复读

Repeatable Read（可重复读）：一个事务可以多次执行某一查询操作，并且每次的返回结果相同；可以避免脏读、不可重复读，可能发生幻读 

Serializable（串行化）：每个事务都有序的进行，事务之间互相不干扰，可以防止脏读，幻读，不可重复读，但是会影响系统的效率

4.3、MVCC

Multi-Version Concurrency Control，即多版本的并发控制协议。是一种用来解决读-写冲突的无锁并发控制，主要依赖记录中的 3个隐式字段，undo日志，Read View来实现。也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照数据。

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能。

同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决数据更新丢失问题。

五、困之ACID

5.1、Atomicity（原子性）

事务的原子性就如原子操作一般，表示事务不可再分，其中的操作要么都做，要么都不做；如果事务中一个SQL语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。只有0和1，没有其它值。

事务的原子性表明事务就是一个整体，当事务无法成功执行的时候，需要将事务中已经执行过的语句全部回滚，使得数据库回归到最初未开始事务的状态。

事务的原子性就是通过undo log日志进行实现的。当事务需要进行回滚时，InnoDB引擎就会调用undo log日志进行SQL语句的撤销，实现数据的回滚。要么全执行，要么全不执行，是事务最核心的特性，事务本身就是以原子性来定义的；实现主要基于undo log日志实现的。

5.2、Durability（持久性）

事务的持久性是指当事务提交之后，数据库的改变就应该是永久性的，而不是暂时的。这也就是说，当事务提交之后，任何其它操作甚至是系统的宕机故障都不会对原来事务的执行结果产生影响。事务的持久性是通过InnoDB存储引擎中的redo log日志来实现的，具体实现思路见下文。

保证事务提交后不会因为宕机等原因导致数据丢失；实现主要基于redo log日志。

5.3、Isolation（隔离性）

原子性和持久性是单个事务本身层面的性质，而隔离性是指事务之间应该保持的关系。隔离性要求不同事务之间的影响是互不干扰的，一个事务的操作与其它事务是相互隔离的。

由于事务可能并不只包含一条SQL语句，所以在事务的执行期间很有可能会有其它事务开始执行。因此多事务的并发性就要求事务之间的操作是相互隔离的。这一点跟多线程之间数据同步的概念有些类似。

保证事务执行尽可能不受其他事务影响；InnoDB默认的隔离级别是RR，RR的实现主要基于锁机制、数据的隐藏列、undo log和类next-key lock机制。

5.4、Consistency（一致性）

事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障。

六、困之日志

6.1、undo log（回滚日志）

undo log是InnoDB引擎提供的一种逻辑日志，undo log由两个作用，一是提供回滚，二是实现MVCC。

比如：任何操作之前，首先将数据被分到undo Log，然后再进行修改数据，如果出现错误或者rollback将undo log备份的数据恢复到事务开始之前的状态。delete之前，undolog会记录一条insert记录。insert之前，undolog会记录一条delete记录。update之前，记录一条相反的update记录 undolog是逻辑日志，修改记录的sql的相反sql的日志。

6.2、redo log（重做日志）

redo log是InnoDB引擎层的日志，用来记录事务操作引起数据的变化，记录的是数据页的物理修改。

redo log可分为两个部分，存在易失性内存中的缓存日志redo log buff和保存在磁盘上的redo log日志文件redo log file。

InnoDB引擎对数据的更新，是先将更新记录写入redo log日志，然后会在系统空闲的时候或者是按照设定的更新策略再将日志中的内容更新到磁盘之中。这就是所谓的预写式技术（Write Ahead logging）。这种技术可以大大减少IO操作的频率，提升数据刷新的效率。

和undo log相反，redo log记录的是新数据的备份。在事物提交前，只要将redoLog持久化即可，不需要将数据持久化，系统可以根据redoLog恢复数据。

• 每秒写入OS buffer，并调用fsync()刷到磁盘 redo log buff --fsync()-->redo log file；

• redo log buffer在内存中；

• commit之后再执行redo log；

• ib_logfile0/ib_logfile1文件就是redo log，除非断电才会失败；

• redo log是两阶段提交，先prepare再正式提交。

6.3、binlog（二进制日志）

undo log是用来回滚数据的用于保障 未提交事务的原子性，而二进制日志binlog是服务层的逻辑日志，又被称为归档日志。

binlog主要记录数据库的变化情况，内容包括数据库所有的更新操作。所有涉及数据变动的操作，都要记录进二进制日志中。因此有了binlog可以很方便的对数据进行复制和备份，因而也常用作主从库的同步。

6.4、主从复制

主从复制的原理实际上就是通过bin log日志实现的，bin log日志中保存了数据库中所有SQL语句，通过对bin log日志中SQL的复制，然后再进行语句的执行即可实现从数据库与主数据库的同步。

主从复制的过程

• 运行在主服务器中的发送线程，用于发送binlog日志到从服务器

• 从服务器上的I/O线程用于读取主服务器发送过来的binlog日志内容，并拷贝到本地的中继日志中

• 从服务器上SQL线程用于读取中继日志中关于数据更新的SQL语句并执行，从而实现主从库的数据一致。

主从复制的好处

• 通过复制实现数据的异地备份，当主数据库故障时，可切换从数据库，避免数据丢失。

• 可实现架构的扩展，当业务量越来越大，I/O访问频率过高时，采用多库的存储，可以降低磁盘I/O访问的频率，提高单个机器的I/O性能。

• 可实现读写分离，使数据库能支持更大的并发。

• 实现服务器的负载均衡，通过在主服务器和从服务器之间切分处理客户查询的负荷。

七、困之优化

见第二遍