【MySQL数据库原理 零】MySQL数据库原理看这一篇就够了

MySQL是使用最广泛的数据库，只有理解了其底层机制，才能更好的写出高性能的SQL查询，所谓知其然，也要知其所以然。本篇Chat为接下来的深入理解MySQL数据原理 精华版本，重点知识，如果某个知识点不理解，可以再深入的看本专栏中的其它Blog内容介绍。

1. MySQL架构及查询语句执行流程，一条SQL语句的执行流程，MySQL数据库的整体结构
2. MySQL数据库存储引擎，InnoDB和MyISAM的区别，InnoDB的优势
3. MySQL日志机制，MySQL的两种日志，日志的作用是什么
4. MySQL数据库索引，索引的底层结构，B+树的构造，索引的优化策略
5. MySQL数据库事务及锁机制，事务的ACID，隔离级别，MVCC机制，锁的种类和使用

适合人群：不了解MySQL的新手，对MySQL的实现机制感兴趣的技术人员

本文的全部内容来自我个人在深入理解【MySQL数据库原理】学习过程中整理的博客，是该博客专栏的精华部分。在书写过程中过滤了流程性的上下文，例如部署环境、配置文件、代码示例等，而致力于向读者讲述其中的核心部分，如果读者有意对过程性内容深入探究，可以移步专栏中的其它Blog

MySQL架构及查询语句执行流程

本部分回答以下几个问题，如果能回答正确，则证明本部分掌握好了。

MySQL的整体架构，分为哪几个部分，使用上需要注意什么
一条查询语句的执行流程是什么样的

接下来我们看这部分的内容。

MySQL的架构

客户端依据通信协议请求服务端，而MySQL这个服务器执行SQL语句命令并给出反馈，整体架构如下：

可以粗略的把MySQL服务器分为两层，上面的为Server层，主要包括连接器、查询缓存、分析器【分析器+预处理器】、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块：

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)，我们在数据库层执行SQL语句时，应用程序会连接到相应的数据库服务器，把SQL语句发送给服务器处理，实际上也就是通信，数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个
• 查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。在接收到查询请求后，并不会直接去数据库查询，而是在数据库的查询缓存中找是否有相对应的查询数据（某条给定的查询语句在第一次执行时，服务器会缓存这条查询语句和他返回的结果。）
  • 如果存在，那么在返回查询结果之前，MySQL会检查一次用户权限。如果权限没有问题，key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被则直接从缓存中拿到结果返回给客户端。
    • 查询不会被解析，不用生成执行计划，不会被执行。
    • 判断是否命中缓存是将此查询语句和缓存中的查询语句进行比对，如果完全相同，那就认为它们是相同的，就认为命中缓存了。
  • 如果不存在，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中
• 分析器: 没有命中缓存的话，SQL 语句就会经过分析器，Mysql通过将SQL语句进行解析，并生成一棵对应的解析树。MySQL解析器将使用MySQL语法分析(语法规则验证)和解析查询，如将验证是否使用错误的关键字，或者关键字的顺序是否正确
• 预处理器：预处理器根据一些MySQL规则进一步检查解析树是否合法，如数据表和数据列是否存在，解析列名和别名，是否有歧义。接下来预处理器会验证用户权限(precheck)。查看用户是否有相应的操作权限
• 优化器： 按照 MySQL 认为最优的方案去执行。例如表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序，将SQL语句转化成执行计划，一条查询可以有很多种执行方式，最后都返回相同的结果，最后找到其中最好的执行计划（Mysql使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划的成本，选择其中成本最小的一个）
• 执行器: Mysql根据执行计划给出的指令逐步执行。开始执行的时候，要先判断一下你对这个表有没有执行查询的权限，如果没有就会返回没有权限的错误。在此过程中，有大量的操作需要通过调用存储引擎实现的接口完成，这些接口即为“handler API”接口。查询中的每一个表由一个handler的实例表示。（实际上，在优化阶段Mysql就为每一个表创建了一个handelr实例，优化器可以根据这些实例的接口获取表的相关信息，如表的所有列名、索引统计信息等）

下面的一层为存储引擎，以及真实存储的数据，存储引擎又分为很多中，在【MySQL数据库基础 五】数据库存储引擎这篇文章里详细的分析过MySQL的存储引擎选择。以上的分析器和预处理器也可以简化为一个分析器：

总体而言MySQL的架构分为两层

• Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
• 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎

这两层共同构建了MySQL，以上各个环节中包含如下三个注意事项：

1. 连接的权限时效，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置
2. 长连接如何使用呢？因为创建连接比较复杂，所以建议使用长连接，但是长连接容易OOM
   • 定期断开长连接，使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连
   • 如果用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态
3. 不建议使用查询缓存，查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空，8.0已经将该功能彻底移除了

总结而言，连接设置修改只影响后续连接，长连接虽好，但还是定期断开，否则容易OOM，不建议使用查询缓存。

一条查询语句的执行流程

了解了MySQL的架构之后呢，正式理解下一条语句如何进行执行和优化。，以如下的查询语句举例分析，从人员库的人员信息表里拿出一条人员数据：

use User
go
select * from  UserInfo   where  name='tml' and age='26' and sex='男';

分析下这个语句的执行流程：

1. 连接器： 连接数据库User，并通过输入账号密码通过连接认证【数据库权限check】
2. 查询缓存: 先执行查询缓存，如果命中数据，在返回之前先判断是否有权限【查询缓存check】，如果有则返回，没有则继续向下
3. 分析器: 若没有命中缓存，进行语法分析，提取关键字：use 、go、select 、from 、where 、and ，判断关键字是否满足MySQL的语法
4. 预处理器：进一步获取UserInfo表名、列名：name、age、sex，判断这些元素是否都存在，如果都存在则验证权限【权限precheck】，如果权限存在继续向下
5. 优化器： 判断先获取哪一列，产生各种方案【name->age->sex、name->sex->age、age->sex->name等】，最终会选取最优、成本最小的方案去执行
6. 执行器: 执行前先判断是否有权限执行语句【表权限check】， 调用handler查询相关接口，从InnoDB存储引擎中获取数据，
   • 调用 InnoDB 引擎接口取这个表的第一行，判断是否满足条件name='tml' and age='26' and sex='男'如果不是则跳过，如果是则将这行存在结果集中；
   • 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
   • 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。以上就是查询的执行流程

MySQL数据库存储引擎

本部分回答以下几个问题，如果能回答正确，则证明本部分掌握好了。

MySQL的存储库引擎有哪几种，分别有什么区别

接下来我们看这部分的内容。

数据库引擎分类介绍

在MySQL中，存储引擎是以插件的形式运行的。支持的引擎有十几种之多，但我们实战常用到的，只有InnoDB、MyISAM和Memory ，MySQL的默认存储引擎为InnoDB。

MyISAM

MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表名。

• 体积小，质量大。MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。同时能加载更多索引，建立了一个MyISAM引擎的tb_Demo表，那么就会生成以下三个文件：MyISAM索引数据分离，体积小，内存利用率高

  • tb_demo.frm，存储表定义； tb_demo.MYD，存储数据；tb_demo.MYI，存储索引

• MyISAM无法处理事务，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。

• DELETE FROM table时，MyISAM会先将表结构备份到一张虚拟表中，然后执行drop，最后根据备份重建该表。

• innodb寻址要映射到块，再到行，MYISAM 记录的直接是文件的OFFSET，定位比INNODB要快

MyISAM存储引擎特别适合在以下几种情况下使用：

• 选择密集型的表，MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。
• 插入密集型的表，MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。不支持事务，自然就不支持行级锁
• 如果和 MyISAM 比 Insert 写操作的话，Innodb还达不到MyISAM的写性能，如果是针对基于索引的update操作，MyISAM会慢于InnoDB，MyISAM的insert快于Innodb，update慢于Innodb
• select count(*) 和 order by 大概是使用最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作Innodb其实也是会锁表的，很多人以为Innodb是行级锁，那个只是where对它主键是有效，非主键的还是会锁全表的，MyISAM表的select count(*) 是非常快的；在 MyISAM 存储引擎中，把表的总行数（row）存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。同样，当 count(*) 语句包含 where条件时，两种表的操作是一样的MyISAM的select count(*) 和 order by的速率快
• 定期提供某些表的数据时，MyISAM的话很方便，只要发给他们对应那表的（frm.MYD,MYI）的文件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。

所以综合而言MyISAM更加适合select多的，事务无要求的场景

InnoDB

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。我的电脑上安装的MySQL 5.6.13版，InnoDB就是作为默认的存储引擎。

• InnoDB还引入了行级锁定和外键约束，
• Innodb的索引和数据是紧密捆绑的，没有使用压缩从而会造成 Innodb 比 MyISAM 数据文件体积庞大很多
• InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度
• InnoDB 表的select count(*) 比 MyISAM 慢很多；当执行 select count(*) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量，需要注意的是，当count(*) 语句包含 where 条件时，两种表的操作是一样的，当count(*)语句包含where条件时MyISAM也需要扫描整个表
• DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除

在以下场合下，使用InnoDB是最理想的选择：

• 更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
• 事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。默认的事务隔离级别为可重复读（REPEATABLE-READ），通过MVCC（并发版本控制）来实现，使用的锁粒度默认为行级锁，可以支持更高的并发；当然，也支持表锁，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了
• 支持行级锁，INNODB的行级锁是有条件的。在where条件没有使用主键时，照样会锁全表
• 自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
• 外键约束。MySQL支持外键的存储引擎只有InnoDB。
• 支持自动增加列AUTO_INCREMENT属性。

一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。大数据量下用innodb，因为支持事务，行级锁。对于InnoDB来说，最大的优势在于支持事务，当然这是以牺牲效率为代价的

MEMORY

使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。

• 虽然在内存中存储表数据确实会提供很高的性能，但当mysqld守护进程崩溃时，所有的Memory数据都会丢失。
• 要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用
• 只支持表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈
• 查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低

基于以上的MEMORY的特性，适用场景如下：

• 目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。
• 如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。
• 存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是，可以使用部分查询和通配查询，也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了，因此散列索引值适合使用在**=和<>**的操作符中，不适合在<或>操作符中，也同样不适合用在order by子句中。

三种存储引擎的对比

三种存储引擎的详细对比用如下的这个表格来展示下吧：

功能特性	Innodb	MyISAM	MEMORY
是否MySQL默认引擎	是	否	否
是否支持事务	是【原子和回滚】	否	-
高并发下DML语句适合场景	频繁更新以及插入涉及到安全性较高操作 【读写密集型】	查询【查询密集型】	-
是否支持外键	是 【联表数据耦合高】	否	-
是否保存表行数，这将增加count(*)性能	否	是	-
是否支持自动增长列	是	否	-
清空表时如何操作	一行一行的删除，效率非常慢	drop，然后重建表，效率高	-
清空支持锁类型	行级锁【粒度更细，适合高并发】、表级锁	表级锁	表级锁
占用存储空间	多	少	只在内存中运行
占用缓存在内存的内容	索引+数据【缓冲池】	索引	索引+数据
是否支持MVCC	是	否	否

MySQL日志机制

本部分回答以下几个问题，如果能回答正确，则证明本部分掌握好了。

一条更新语句的执行流程
RedoLog指什么，BinLog指什么，二者的区别和作用场合分别是什么
WAL机制、crash-safe能力和主从备份同步
两阶段提交机制
组提交策略和延迟提交策略

接下来我们看这部分的内容。

更新语句的执行流程

DML数据操作语句（更新、删除、插入）这些在执行的时候肯定要记录日志，MySQL 自带的日志模块 binlog（归档日志） ，所有的存储引擎都可以使用，常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），假如我们要更新ID为2的这条数据当前值自增1，其中ID为主键，加了索引：

use User
go
update T set c=c+1 where ID=2;

我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

1. 连接器： 连接数据库User，并通过输入账号密码通过连接认证， 查询缓存不执行，因为在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空
2. 分析器: 进行语法分析，提取关键字：use 、go、update 、set 、where ，判断关键字是否满足MySQL的语法， 预处理器：进一步获取UserInfo表名、列名：name、age，判断这些元素是否都存在，如果都存在则验证权限，如果权限存在继续向下
3. 优化器： 定位到要更新的数据，查询tml这一条数据，然后把age改为18，生成一个执行计划
4. 执行器: 调用handler查询相关接口写入这一行数据
   • 执行器先找存储引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回
   • 执行器拿到引擎给的行数据，把这个值设置为18，得到新的一行数据，再调用引擎接口写入这行新数据。
   • 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态，然后告知执行器执行完成了，随时可以提交事务
   • 执行器收到通知后记录 binlog，并把 binlog 写入磁盘
   • 执行器调用引擎接口，提交 redo log 为提交状态。

图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的

以上就是在更新数据时的一些操作，实际上只比查询多后半段。接下来我们从为什么需要使用开始讲起一直到日志是如何满足MySQL的安全可靠的。为了方便把前面的知识串联起来，我画了一个流程图：

循着这个路线正序的提取下日志部分的知识精华。

为什么要用日志

日志有三方面的作用，分别是WAL机制、备份归档以及crash-safe能力。一个个来说说它的好处：

WAL机制

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志【log file on disk】，再写磁盘【Page on disk】。看定义貌似跑不掉写磁盘这一步，因为日志文件也存储在磁盘中。为啥WAL的写磁盘就快呢？

• redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快，到达一定时机会将日志中涉及的数据写到磁盘中的数据库。如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高
• 组提交机制，积累一些buffer后一次性刷盘。可以大幅度降低磁盘的 IOPS 消耗（IOPS (Input/Output Per Second)即每秒的输入输出量(或读写次数),是衡量磁盘性能的主要指标之一。IOPS是指单位时间内系统能处理的I/O请求数量），这样其实写日志【log file on disk】的次数也会少，可能一次写好几个事务。

其中IOPS (Input/Output Per Second)即每秒的输入输出量(或读写次数)，是衡量磁盘性能的主要指标之一，数值越小，说明IO占用越低。

crash-safe能力

crash-safe能力表示，即使数据库发生异常重启【可能是数据库进程掉电，也可能是主机掉电】，之前提交的记录，都不会丢失，也即已经提交（commit）成功的事务不会被丢失，可以通过持久化到磁盘的日志【log file on disk】+持久化到磁盘的数据【Page on disk】来恢复，

begin;
insert into t1 ...
insert into t2 ...
commit;

举个例子，如果仅仅只有数据库，掉电的时候以上的第一个insert执行完中断，数据库中的数据就不是我们预想的了，满足不了事务的ACID特性。

备份归档

数据库的备份和恢复依赖于日志和数据库的全量备份的，因为我们不能频繁的进行数据库的实时的全量备份，成本太大而且不易实现，所以定期的整库备份+日志才是实现数据库安全稳定的王道

日志设计策略

Redolog和Binlog可以满足以上提到的三个日志需要的功能。

Redolog和Binlog实现WAL

其实无论是Redolog还是Binlog，都有WAL机制，也就是更新记录都会先落到磁盘上的日志文件上，并且更新内存中的数据页，等到合适的时机再刷数据落盘。具体而言有如下三个步骤：

• 创建阶段：事务创建一条日志，并添加到日志缓存
• 日志刷盘【log file on disk】：日志缓存经过文件系统缓存后写入到磁盘上的日志文件，并更新记录到内存中的数据页，使其变为脏页
• 数据刷盘【Page on disk】：日志对应的脏页数据写入到磁盘上的数据文件
• 更新checkpoint【Redo log特有】，RedoLog在数据刷盘之后还有一个步骤就是，也就是说redolog中的checkpoint前进方向区域一定是数据页还没落盘的提交，这样我们就能确定哪些数据即使事务commit也就是日志刷盘已经成功的状态下其实数据还没有刷盘

commit主要就是在日志刷盘阶段，我们主要讨论下事务从产生到日志落盘为止的过程。

Redolog写入机制

以下这几个步骤对应于整个事务数据落盘的前两步，也就是日志刷盘为止。

redo log 可能存在三种状态，这三种状态是依次写入的：

1. 事务先写到 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；日志写到 redo log buffer 是很快的
2. 写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分【redo log file on FS cache】，wirte 到 page cache 也很快
3. 持久化到磁盘，对应的是 hard disk，也就是图中的绿色部分【binlog file on disk】，fsync到磁盘的速度就慢多了

为了控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：

• 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ，不管是主机掉电还是MySQL异常重启，都有丢数据的风险，风险高
• 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache，写入文件系统的page cache，主机掉电后会丢数据，但是MySQL异常重启不会丢数据，风险较低，写入比较快
• 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘，非常安全，但慢。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。可以理解为innodb_flush_log_at_trx_commit三个设置对应每次事务写入到不同级别，而每隔一秒就会从最不稳定状态直接刷到最稳定状态

Binlog写入机制

事务执行过程中，**先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件【文件系统缓存page cache】**中。一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache

1. 事务先写到每个线程自己的 binlog cache，但是共用同一份 binlog 文件。
2. 然后write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快【binlog file on FS cache】。
3. 最后fsync，才是将数据持久化到磁盘的操作【binlog file on disk】

一般情况下，我们认为 fsync 才占磁盘的 IOPS。write 和 fsync 的时机，是由参数 sync_binlog 控制的：

• sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0
• sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
• sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。比较常见的是将其设置为 100~1000 中的某个数值。但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志，主机都crash了，文件系统缓存里的日志当然也丢了，没法落盘

可以理解为sync_binlog等于几表明每几个事务发生一次fsync，因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。

Redolog实现crash-safe

为什么Redolog可以实现crash-safe而binlog不可以，因为RedoLog的特殊数据结构，RedoLog是循环写的，有特有的checkpoint。

binglog是顺序写的，binlog 没有能力恢复“数据页”。如果MySQL 发生了 crash需要恢复（恢复的过程就是将未刷盘的内存中的数据脏页恢复到内存中）

• binlog由于没有checkpoint所以可能虽然有N个事务已经commit，但数据页还没有落盘，依据binlog没有办法判断哪些事务的commit涉及的数据没有落盘，也就不知道该从哪里恢复
• redo log是循环写的，有checkpoint，通过checkpoint的移动位置可以确定哪些事务指向的数据脏页确实被刷盘了，只要刷入磁盘的数据，都会从 redo log 中抹掉，数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了

如果真想使用binlog来恢复的话，那么就要在每个commit之前，将更改的内存记录刷盘。刷盘之后再将这个事务改为commit状态。 这样崩溃恢复就可以在事务级去做了，而不用在数据页级去做了，但是这样显然违背WAL技术【累计多个事务修改的数据页存储内存，一次刷盘来减少IOPS】的初衷

binlog实现备份归档

redo log 是循环写的，空间固定会用完，用完后会之前的记录会被覆盖；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志，所以归档使用追加写的binlog。

Redo log和Binglog存在意义

除了上述二者各自不可替代的功能外，他们还有一些必须存在的历史原因

• Binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。binlog 作为 MySQL 一开始就有的功能，被用在了很多地方。其中，MySQL 系统高可用的基础，就是 binlog 复制。还有很多公司有异构系统（比如一些数据分析系统），这些系统就靠消费 MySQL 的 binlog 来更新自己的数据。关掉 binlog 的话，这些下游系统就没法输入了。总之，由于现在包括 MySQL 高可用在内的很多系统机制都依赖于 binlog
• Redo log是 InnoDB 引擎特有的，可以实现crash-safe，因为这个能力，后续被当做了MySQL的默认引擎

所以因为有了Redo log的crash-safe，所以没必要再去做一个出来，而又因为Binlog比较早期，除了归档还在很多生态中被广泛使用，所以二者都不能被取代。只能并存，那么并存就有一个问题，我们需要保证这两份并存的日志是一致的！

两阶段提交机制

两阶段提交的顺序如下图所示，那么为什么要两阶段提交呢？前面我们讨论了两个日志各自存在的必要性，表明我们不能只用其中一种日志，而要结合使用，结合使用就一定要保证它两是一致的。

如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。

• 先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 bingog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。binlog丢，无法备份
• 先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于 redo log 还没写，崩溃恢复以后这个事务无效。本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。redolog丢，无法crash-safe

所以如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。采用这种方式再验证一下：

• 写redo log 预提交过程中，机器挂了，则回滚事务【时刻A】
• 写完redo log 预提交，并且写binglog过程中 ，机器挂了，则判断【时刻B】
  • 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交【说明写完binlog了】
  • 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务

这样就解决了数据一致性的问题，那么redo log 和 binlog 是怎么关联起来的?它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

• 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
• 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务，binlog完整就提交，不完整就回滚

这样就能知道这条事务binlog到什么阶段了

组提交策略

三个并发事务 (trx1, trx2, trx3) 在 prepare 阶段，都写完 redo log buffer，持久化到磁盘的过程，对应的 LSN 分别是 50、120 和 160

从图中可以看到：

1. trx1 是第一个到达的，会被选为这组的 leader；
2. 等 trx1 要开始写盘的时候，这个组里面已经有了三个事务，这时候 LSN 也变成了 160；
3. trx1 去写盘的时候，带的就是 LSN=160，因此等 trx1 返回时，所有 LSN 小于等于 160 的 redo log，都已经被持久化到磁盘；这时候 trx2 和 trx3 就可以直接返回了。

所以，一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好。但如果只有单线程压测，那就只能老老实实地一个事务对应一次持久化操作了。在并发更新场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好

提升MySQL的IO性能

结合以上的日志写入策略和延迟刷盘尽量组里事务多一些来谈谈MySQL的IO性能

日志落盘参数配置

如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？针对这个问题，可以考虑以下三种方法：

• 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
• 将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）【事务都写到文件系统缓存】。这样做的风险是，主机crash掉电时会丢 binlog 日志。
• 将 innodb_flush_log_at_trx_commit 设置为 2【事务都写到文件系统缓存】。这样做的风险是，主机掉电的时候会丢数据。

不建议把 innodb_flush_log_at_trx_commit 设置成 0。因为把这个参数设置成 0，表示 redo log 只保存在内存中，这样的话 MySQL 本身异常重启也会丢数据，风险太大。而 redo log 写到文件系统的 page cache 的速度也是很快的，所以将这个参数设置成 2 跟设置成 0 其实性能差不多，但这样做 MySQL 异常重启时就不会丢数据了，相比之下风险会更小

MySQL的延迟刷盘策略

为了让一次 fsync 带的组员更多，MySQL 有一个优化策略：拖时间。了解了两种日志的写入过程后，我们可以把这个过程更加的细化

这样做有两个好处，对redolog和binglog都加了组提交优化：

• redolog可以更好的组提交。把 redo log 做 fsync 的时间拖到了步骤 2之后，这样redo log做fsync 和 write的中间隔了一个binlog的write，所以会累计更多的事务在文件缓存中一起刷盘
• binlog 也可以组提交了。在执行第 4 步把 binlog fsync 到磁盘时，如果有多个事务的 binlog 已经写完了，也是一起持久化的，这样也可以减少 IOPS 的消耗。不过通常情况下第 3 步执行得会很快，所以 binlog 的 write 和 fsync 间的间隔时间短，导致能集合到一起持久化的 binlog 比较少，因此 binlog 的组提交的效果通常不如 redo log 的效果那么好

这就是MySQL的拖时间组提交策略，需注意其实组提交处理的是并发的场景，单线程的话在双1设置下都是每次事务完成单个刷盘的。

MySQL数据库索引

本部分回答以下几个问题，如果能回答正确，则证明本部分掌握好了。

索引建在哪些字段上比较合适，应用场景是什么
聚簇索引和非聚簇索引的区别
索引的常见模型有哪几种
InnoDB的索引模型是什么样的
索引的几种策略，覆盖索引和回表机制
联合索引的最佳使用方式、最左前缀原则
索引下推机制

接下来我们看这部分的内容。

索引的使用场景

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。一本 500 页的书，如果你想快速找到其中的某一个知识点，在不借助目录的情况下是比较难找的。同样，对于数据库的表而言，索引其实就是它的目录。数据库中如果不使用索引，大概率会非常慢，所以一定需要创建索引。索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列 上创建索引，例如：

• 在经常需要搜索的列上，可以加快搜索的速度；
• 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
• 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
• 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
• 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

总之就是在进行一些常用的查询和排序的过程中使用索引的场景比较多。因为如果不建立索引的话只能全表扫描了。

聚簇索引和非聚簇索引

聚簇索引和非聚簇索引是mysql数据库中两种主要的索引方式

• 聚簇索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。聚簇索引与字典相同，字典按字母顺序排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引，我们把这种正文内容本身就是一种按照一定规则排列的目录称为聚集索引
• 非聚簇索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为非聚集索引。

二者的详细对比如下，我们可以这么理解：聚簇索引索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过节点上存储一个指向对应的数据块的指针

索引常见模型

索引的出现是为了提高查询效率，但是实现索引的方式却有很多种，所以这里也就引入了索引模型的概念。可以用于提高读写效率的数据结构很多，这里介绍三种常见、也比较简单的数据结构，它们分别是哈希表、有序数组和搜索树

哈希表

哈希表是一种以键 - 值（key-value）存储数据的结构，只要输入待查找的键即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。以下是一个按照身份证号查名字的示例：

需要注意的是，图中四个 ID_card_n 的值并不是递增的，而且同一个链表上的User2也不会与User4比较就直接追加到链表尾部，这样做的好处是增加新的 User 时速度会很快，只需要往后追加。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。所以哈希表这种结构适用于只有等值查询的场景

这种模型使用场景较少，其优点和应用场景如下：

• 优点：Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B+Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于B+Tree 索引。时间复杂度是O(1)
• 应用场合：依赖于这种单值查找的系统被称为 键-值存储；对于这种系统，尽可能地使用 hash 索引

缺点不言而喻，应用场合太小了，只有做等值查询时候才有意义，区间查询时效率极差。

有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀。还是上面这个根据身份证号查名字的例子，如果我们使用有序数组来实现的话，示意图如下所示

这里我们假设身份证号没有重复，这个数组就是按照身份证号递增的顺序保存的。

• 等值查询，这时候如果你要查 ID_card_n2 对应的名字，用二分法就可以快速得到，这个时间复杂度是 O(log(N))
• 区间查询，要查身份证号在[ID_card_X, ID_card_Y]区间的 User，可以先用二分法找到 ID_card_X（如果不存在 ID_card_X，就找到大于 ID_card_X 的第一个 User），然后向右遍历，直到查到第一个大于 ID_card_Y 的身份证号，退出循环

如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高（需要O(n)的时间复杂度），所以，有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据

搜索树

二叉搜索树也是经典数据结构了，这里简单回顾下。Binary Sort Tree，二叉搜索树或者是一棵空树，或者是具有下列性质的二叉树：

• 若它的左子树不空，则左子树上所有结点的值均小于它的根结点的值；
• 若它的右子树不空，则右子树上所有结点的值均大于或等于它的根结点的值；
• 它的左、右子树也分别为二叉排序树。
• 没有键值相等的节点。

二叉搜索树是按照关键升序排列，对每一个节点来说，比它节点值高的节点是它的中序后继，所以对二叉查找树进行中序遍历（左根右），即可得到有序的数列。它和二分查找一样，插入和查找的时间复杂度均为O(logn)，但是在最坏的情况下仍然会有O(n)的时间复杂度。原因在于插入和删除元素的时候，树没有保持平衡，所以我们这里也需要数是平衡的，也就是二叉搜索平衡树。

二叉搜索平衡树

为了保证插入效率，使用了平衡树，平衡二叉树（Self-balancing binary search tree）又被称为AVL树（有别于AVL算法），且具有以下性质：它是一 棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树

还是上面根据身份证号查名字的例子，如果我们用二叉搜索树来实现的话，示意图如下所示

这样如果你要查 ID_card_n2 的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路径得到。这个时间复杂度是 O(log(N))，当然为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))

多叉搜索树

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上，因为二叉树树高过高，每次查询都需要访问过多节点，即访问数据块过多，而从磁盘随机读取数据块过于耗时

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块，那么，我们就不应该使用二叉树，而是要使用N叉树。

以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了

InnoDB索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的，每一个索引在 InnoDB 里面对应一棵 B+ 树

B+树结构

一个m阶的B+树具有如下几个特征：

1. 有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
2. 所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3. 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素

下图为一个B+树的结构：

在等值查询的时候：

区间查询：

B+树的优势显而易见：

1. 单一节点存储更多的元素，使得查询的IO次数更少。
2. 所有查询都要查找到叶子节点，查询性能稳定。
3. 所有叶子节点形成有序链表，便于范围查询。

需要注意数据库的聚集索引（Clustered Index）中，叶子节点直接包含卫星数据。在非聚集索引（NonClustered Index）中，叶子节点带有指向卫星数据的指针，这部分再下文中讲到

B+Tree适用场景

我们这里比较下Hash索引和B+树的区别，有序数组暂不讨论。由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。 Hash 索引不能够用于诸如 < 等用于查找一个范围值的比较运算符，Hash 索引仅仅能满足”=”,”IN”和”<=>”（等价于）查询

• Hash 索引不能够用于范围查询，B+Tree可以进行范围查询
• Hash 索引无法被用来数据的排序操作，B+Tree可以进行排序操作，由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；优化器不能够使用 hash 索引来加速 ORDER BY 操作。这种类型的索引不能够用于按照顺序查找下一个条目。
• Hash 索引不能利用部分索引键查询。B+Tree可以，对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。查找某行记录必须进行全键匹配。而 B+Tree 索引，任何该键的左前缀都可用以查找记录。
• Hash 索引在任何时候都不能避免表扫描。前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
• Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B+Tree索引高，对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

总而言之，还是B+树的应用场景较多

主键与非主键索引

假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引

mysql> create table T(
    id int primary key, 
    k int not null, 
    name varchar(16),
    index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下

可以看到表里创建了两个索引，一个是默认给主键创建的，一个是我们手动给k创建的：

• 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）
• 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引(非聚簇索引)（secondary index）

主键索引： key:主键的值，value:整行数据。 普通列索引： key：索引列的值， value:主键的值。

根据上面的索引结构说明，我们来讨论一个问题：基于主键索引和普通索引的查询有什么区别？

• 如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
• 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询

索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上图为例

• 如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。
• 如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据空出位置。

以上情况维护起来就比较麻烦了。

页分裂和页合并

而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。页合并的过程，可以认为是分裂过程的逆过程

自增主键

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值

• 自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
• 业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高

除了考虑性能外，我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。所以，从性能和存储空间方面考量，自增主键往往是更合理的选择

业务主键

有没有什么场景适合用业务字段直接做主键的呢？还是有的。比如，有些业务的场景需求是这样的：只有一个索引；该索引必须是唯一索引。这就是典型的 KV 场景。由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树

MyISAM和InnoDB的索引实现对比

上文我们了解了InnoDB的索引结构。MySQL的BTree索引使⽤的是B树中的B+Tree，但对于主要的两种存储引擎的实现⽅式是不同的。

• MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，⾸先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。⾮聚簇索引
• InnoDB: 其数据⽂件本身就是索引⽂件。相⽐MyISAM，索引⽂件和数据⽂件是分离的，其表数据⽂件本身就是按B+Tree组织的⼀个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据⽂件本身就是主索引。聚簇索引。⽽其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值⽽不是地址，这也是MyISAM不同的地⽅。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再⾛⼀遍主索引。

在设计表的时候，不建议使⽤过⻓的字段作为主键，也不建议使⽤⾮单调的字段作为主键，这样会造成主索引频繁分裂

索引策略

理解了InnoDB索引实现的方式之后，我们再来看看索引在具体的使用过程中有哪些执行策略和优化方式。

我们来看下select * from T where k between 3 and 5SQL 查询语句的执行流程，注意取3和取5因为叶子节点单链表链接，所以从根节点搜索行为仅执行一次。

1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300；再到 ID 索引树查到 ID=300 对应的 R3；回表
2. 在 k 索引树取下一个值 k=5，取得 ID=500；再回到 ID 索引树查到 ID=500 对应的 R4；回表
3. 在 k 索引树取下一个值 k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 k 索引树的 3 条记录，回表了两次。在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？

覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经覆盖了我们的查询需求，我们称为覆盖索引

需要注意的是，在引擎内部使用覆盖索引在索引 k 上其实读了三个记录，ID3、ID4、ID5（对应的索引 k 上的记录项），但是对于 MySQL 的 Server 层来说，它就是找引擎拿到了两条记录，因此 MySQL 认为扫描行数是 2。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段，基于上面覆盖索引的说明，我们来讨论一个问题：在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`),
  KEY `id_card_name` (`id_card`,`name`)   //联合索引
) ENGINE=InnoDB

我们知道，身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了。而再建立一个（身份证号、姓名）的联合索引，是不是浪费空间？

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。例如这个sql：select name from T where id_card =500

它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。给id_card 和 name建立联合索引后，name的值也会被保存在id_card索引树的节点上，这样根据给定id_card的值找到的对应行时，就可以直接获取到name了，而不需要拿着对应的主键再进行回表操作。

需要注意，覆盖索引不能只覆盖要查询的列，同时必须将WHERE后面的查询条件的列都覆盖，也就是这个联合索引要包含查询条件和返回的查询列。

联合索引

比较简单的是单列索引。遇到多条件查询时，不可避免会使用到多列索引。联合索引又叫联合索引，对于联合索引（多列b+tree，使用多列值组合而成的b+tree索引）。遵循最左侧原则，从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c)。可以支持a ，a,b ，a,b,c 这3种组合进行查找，但不支持 b,c进行查找。当使用最左侧字段时，索引就十分有效，也就是我们创建了三个索引：(a)、（a,b）（a,b,c）

• 当(a=? and b=? and c=?)这样的数据来检索的时候，b+树会优先比较a列来确定下一步的所搜方向，如果a列相同再依次比较b列和c列，最后得到检索的数据；顺序最优，联合发挥效力，最少回表次数
• 当(b=? and c=?)这样的没有a列的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候a列就是第一个比较因子，必须要先根据a列来搜索才能知道下一步去哪里查询。无法利用联合索引查询
• 当(a=? and c=?)这样的数据来检索时，b+树可以用a列来指定搜索方向，但下一个字段b列的缺失，所以只能把a列的数据找到，然后再匹配c列的数据了 只能用a的查询，依据c做匹配（索引下推），减少回表次数

这个是非常重要的性质，即索引的最左匹配特性，where后的查询顺序与索引创建顺序一致。需要注意，联合索引是首先使用多列索引的第一列构建的索引树，用上面key index (a,b,c)的例子就是优先使用a列构建，当a列值相等时再以b列排序，若b列的值也相等则以c列排序

最左前缀原则

如果为每一种查询都设计一个索引，索引是不是太多了。如果我现在要按照市民的身份证号去查他的家庭地址呢？虽然这个查询需求在业务中出现的概率不高，但总不能单独为一个不频繁的请求创建一个（身份证号，地址）的索引吧，应该怎么做呢？

B+ 树这种索引结构，可以利用索引的最左前缀来定位记录。为了直观地说明这个概念，我们用（name，age）这个联合索引来分析

可以看到，索引项是按照索引定义里面出现的字段顺序排序的

• 当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。
• 如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张 %’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。

这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符，那么既然最左边的字段这么重要，我们该怎么安排联合索引的顺序呢？

• 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。这段开头的问题里，我们要为高频请求创建 (身份证号，姓名）这个联合索引，并用这个索引支持“根据身份证号查询地址”的需求【查地址时回表查询】，这样不用创建多余索引
• 第二原则是节省空间，如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引，比如上面这个市民表的情况，name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

基于以上的方式进行联合索引的创建。需要注意，

索引下推

我们还是以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

因为最左前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3。当然，这还不错，总比全表扫描要好，在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值，为什么age联合索引不生效呢？因为查询条件里面 name 字段的查询值后面是 % ，导致 age 条件无法和 name 一次进行联合索引的查询，所以 age 查询值是用不上的，存储引擎在(name,age)索引树以"张"进行前缀匹配的数搜索。所以查询顺序并不是 name->age，只有name，也就是因为 name 列使用了 like ，导致后面的索引无法再以组合索引生效

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

MySQL数据库事务及锁机制

本部分回答以下几个问题，如果能回答正确，则证明本部分掌握好了。

MySQL中事务的四大特性，分别如何保证
并发事务的隔离问题有哪几种
事务有哪几种隔离级别应对并发隔离问题
MySQL有哪几种锁，分别有什么作用
隔离机制的实现方式

接下来我们看这部分的内容。事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位事务体现出整体的概念，要么事务中的操作全部成功，要么全部失败体现在数据库sql里就是逻辑上相互依赖的一组sql语句在 MySQL 中，事务支持是在引擎层实现的。MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一

事务的四大特性

事务最经典也经常被拿出来说例⼦就是转账了。假如⼩明要给⼩红转账1000元，这个转账会涉及到两个关键操作就是：将⼩明的余额减少1000元，将⼩红的余额增加1000元。万⼀在这两个操作之间突然出现错误⽐如银⾏系统崩溃，导致⼩明余额减少⽽⼩红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败

事务的四大特性就是ACID，也就是原子性、一致性、隔离性以及持久性

• 原子性（Atomicity），事务是最⼩的执⾏单位，不允许分割。事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
• 一致性（Consistency），是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
• 隔离性（Isolation），是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离，要串行执行。即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。
• 持久性（Durability），是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，导致数据库因为故障而没有执行事务的重大错误。

只有满足这四大特性，才能说这是一个标准的事务。原子性、一致性和持久性由MySQL的日志机制通过两阶段提交、crash-safe能力以及备份能力来满足，在日志机制这篇Blog里我详细的讨论过，那么我们当前最需要关注的其实就是事务的隔离性。

并发事务的隔离问题

当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性，我们先看看如果不考虑事务的隔离性，会发生的几种问题：

脏读

脏读（dirty reads），是指在一个事务处理过程里读取了另一个未提交的事务中的数据。当⼀个事务正在访问数据并且对数据进⾏了修改，⽽这种修改还没有提交到数据库中，这时另外⼀个事务也访问了这个数据，然后使⽤了这个数据。因为这个数据是还没有提交的数据，那么另外⼀个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的

用户A向用户B转账100元，对应SQL命令如下：
update account set money=money+100 where name=’B’;  (此时A通知B)
update account set money=money - 100 where name=’A’;

• 当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读）
• 无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚
• 当B以后再次查看账户时就会发现钱其实并没有转

也就是脏读一般针对修改数据而言，Transaction 1 修改了一行数据，然后 Transaction 2 在 Transaction 1 还未提交修改操作之前读取了被修改的行。如果 Transaction 1 回滚了修改操作，那么Transaction 2读取的数据就可以看作是从未存在过的 A未提交的修改被B读了

不可重复读

不可重复读（non-repeatable reads），针对修改，是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。A已提交的修改被B读了

A向B转账100并且提交，B收到读到100，这时候A又向B转100并提交，B读到200

• 在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主
• 不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据

Transaction 1 读取一行数据，然后 Transaction 2 修改或删除该行并提交修改操作。当 Transaction 1 试图重新读取该行时，它就会得到不同的数据值（如果该行被更新）或发现该行不再存在（如果该行被删除）

幻读

幻读（phantom read）是事务非独立执行时发生的一种现象。针对插入。

• 事务T1对一个表中所有的行的某个数据项做了从1修改为2的操作
• 这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为1并且提交给数据库。
• 而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项（修改），而幻读针对的是一批数据整体（插入或删除）。A已提交的新增删除数据被B读了

综合举例

对于同一个银行帐户A内有200元，甲进行提款操作100元，乙进行转帐操作100元到B帐户。如果事务没有进行隔离可能会并发如下问题：

• 脏读：甲先取款100元未提交，乙进行转帐查到帐户内剩有100元，这时甲放弃操作回滚，乙正常操作转账至B账户提交，帐户内最终更新为0元，乙读取了甲的脏数据，客户损失100元。
  不可重复读：甲乙同时开始都查到帐户内为200元，甲先开始取款100元提交，这时乙在准备最后更新的时候又进行了一次查询，发现结果是100元，这时乙就会很困惑，不知道转账后该将帐户改为100还是0，如果按照正常操作乙会将账户A更新为0，正确执行，所以说一般不可重复读不一定会有问题。
• 幻读：银行会计甲对所有账户的金额单位更新为美元，这时银行会计乙又插入了两条金额单位为人民币的数据，银行会计甲更新完后查数据发现有几条还是人民币，于是以为自己产生了幻觉

以上就是事务并发时会产生的问题。

事务的隔离级别

针对以上的四个常见的并发事务的问题，数据库设置了四种隔离级别来应对：

• Read uncommitted (读未提交)：最低级别，最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读
• Read committed (读已提交)：允许读取并发事务已经提交的数据，可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣
• Repeatable read (可重复读)：对同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改，可以阻⽌脏读和不可重复读，但幻读仍有可能发⽣
• Serializable (串行化)： 最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰，也就是说，该级别可以防⽌脏读、不可重复读以及幻读，对于同一行记录，写会加写锁，读会加读锁。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

当然，随着隔离级别的增高，并发性能也会逐渐下降。

结合隔离问题和隔离机制举例说明，在这样一个事务流程下：

我们来看看在不同的隔离级别下，事务 A 会有哪些不同的返回结果，也就是图里面 V1、V2、V3 的返回值分别是什么。

• 若隔离级别是读未提交， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。
• 若隔离级别是读提交，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
• 若隔离级别是可重复读，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
• 若隔离级别是串行化，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

• 读未提交隔离级别下直接返回记录上的最新值，没有视图概念；
• 读提交隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的，所以会在执行sql时发生变动（需要判断事务是否已提交，提交后才变动）
• 可重复读隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。假设你在管理一个个人银行账户表。一个表存了账户余额，一个表存了账单明细。到了月底你要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。你一定希望在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。这时候使用“可重复读”隔离级别就很方便。事务启动时的视图可以认为是静态的，不受其他事务更新的影响
• 串行化隔离级别下直接用加读写锁的方式来避免并行访问

我们可以看到在不同的隔离级别下，数据库行为是有所不同的。MySQL InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读），与 SQL 标准不同的地⽅在于

• InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下使⽤的是Next-Key Lock 锁算法，因此可以避免幻读的产⽣，这与其他数据库系统(如SQL Server) 是不同的。
• InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串⾏化) 隔离级别。
• 因为隔离级别越低，事务请求的锁越少，所以⼤部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) ，但是你要知道的是InnoDB 存储引擎默认使⽤REPEATABLE-READ（可重读）并不会有任何性能损失。

InnoDB 存储引擎在分布式事务 的情况下⼀般会⽤到 SERIALIZABLE(可串⾏化) 隔离级别

隔离机制的实现

理解了事务的隔离级别，我们再来看看事务隔离具体是怎么实现的。视图可以理解为数据副本，每次创建视图时，将当前『已持久化的数据』创建副本，后续直接从副本读取，从而达到数据隔离效果，存在视图的 2 种隔离级别：

1. 读提交，在每一条 SQL 开始执行时创建视图，隔离作用域仅限该条 SQL 语句。
2. 可重复读，事务启动时创建视图，因此，在事务任意时刻，对记录读取的值都是一样的。

其他 2 种无视图的隔离级别：

1. 读未提交，直接返回记录最新值。
2. 串行化，串行化通过读写锁来避免并行访问，读-读：允许并发执行，读-写：只能串行，写-写：只能串行

这里我们展开说明可重复读。在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

回滚日志

回滚日志记录在undo log，undo log记录的是逆过程流，比如对表进行了一次insert，对应的就是delete；对字段a进行一次+1操作，那undo log就是一次-1操作

回滚日志总不能一直保留吧，什么时候删除呢？答案是，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。什么时候才不需要了呢？就是当一个查询事务开启以后，在这个时刻之后，事务提交/回滚之前，所有更新产生的undo log都不能被删除

长事务的问题

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小（逻辑清理）。最终只好为了清理回滚段，重建整个库（物理清理）。除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库

事务的启动方式

事务启动方式有两种，一种是显式提交，另一种是通过设置禁止自动提交。

• 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
• set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。因此，我会建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务。

有的开发同学会纠结“多一次交互”的问题。对于一个需要频繁使用事务的业务，第二种方式每个事务在开始时都不需要主动执行一次 “begin”，减少了语句的交互次数。建议使用 commit work and chain 语法。在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中

MySQL的锁机制

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构针对不同的分类尺度进行分类，根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类，同时依据锁是否可以被共享又有读写锁的区别：

读写锁

表级锁和行级锁可以进一步划分为共享锁（s）和排他锁（X），在另一个维度上交叉组合：

• 共享锁（s），又被称为读锁，其他用户可以并发读取数据，但任何事务都不能获取数据上的排他锁，直到已释放所有共享锁。共享锁(S锁)又称为读锁，若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。本线程和其它线程都只能读
• 排他锁（X）：又称为写锁，若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁。本线程能读写，其它线程不能读写

两者之间的区别：

• 共享锁（S锁）：如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获取共享锁的事务只能读数据，不能修改数据。
• 排他锁（X锁）：如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获取排他锁的事务既能读数据，又能修改数据。

当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定

全局锁/表级锁/行级锁

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

顾名思义，全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。让整库都只读，听上去就很危险：

• 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
• 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

看来加全局锁不太好。但是细想一下，备份为什么要加锁呢？我们来看一下不加锁会有什么问题

备份不加锁的问题

现在发起一个逻辑备份。假设备份期间，有一个用户，他购买了一门课程，业务逻辑里就要扣掉他的余额，然后往已购课程里面加上一门课。如果时间顺序上是先备份账户余额表 (u_account)，然后用户购买，然后备份用户课程表 (u_course)，会怎么样呢？你可以看一下这个图：

可以看到，这个备份结果里，用户 A 的数据状态是“账户余额没扣，但是用户课程表里面已经多了一门课”。如果后面用这个备份来恢复数据的话，用户 A 就发现，自己赚了，也就是说，不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的

一致性读

前面讲事务隔离的时候，有一个方法能够拿到一致性视图的，就是在可重复读隔离级别下开启一个事务，官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的，一致性读是好，但前提是引擎要支持这个隔离级别，InnoDB支持而 MyISAM不支持

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)

表锁

表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，

• 其他线程写 t1、读写 t2 的语句都会被阻塞。
• 线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

加上读锁，不会限制别的线程读，但会限制别的线程写。加上写锁，会限制别的线程读写，在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大

MDL锁

MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行

小表问题

给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。这往往会导致不可预知的问题：

我们可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁。

1. 由于 session B 需要的也是 MDL 读锁(因为读锁是共享锁)，因此可以正常执行。
2. 之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞
3. 之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。mysql server 端对于sessionC,D有一个队列决定谁先执行

前面我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。

事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放，梦幻联动到我们对长事务的探索，长事务坏处：1、导致undo log过多，占用存储空间 2、获取的锁要等到事务结束才释放，有可能会阻塞请求或者线程爆满

比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程，其实就是我们在并发中学到的RetreenedLock等待可中断、超时放弃锁的机制，所以其实机制一通百通

其它表级锁【意向共享锁&意向排他锁】

意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。

• 如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。
• 而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。
• 这里的意向锁是表级锁，表示的是一种意向，仅仅表示事务正在读或写某一行记录，在真正加行锁时才会判断是否冲突。意向锁是InnoDB自动加的，不需要用户干预

意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在

• 意向共享锁（IS）： 表示事务准备给数据行记入共享锁，事务在一个数据行加共享锁前必须先取得该表的IS锁
• 意向排他锁（IX）： 表示事务准备给数据行加入排他锁，事务在一个数据行加排他锁前必须先取得该表的IX锁。

当一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之如果请求不兼容，则该事物就等待锁释放

行级锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一

顾名思义，行锁就是针对数据表中行记录的锁。这很好理解，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新

两阶段锁

在下面的操作序列中，事务 B 的 update 语句执行时会是什么现象呢？假设字段 id 是表 t 的主键

这个问题的结论取决于事务 A 在执行完两条 update 语句后，持有哪些锁，以及在什么时候释放。可以验证一下：实际上事务 B 的 update 语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行，其实事务 A 持有的两个记录的行锁，都是在 commit 的时候才释放的

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议，所以如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放，假设顾客A、C要在电影院B买票。

1. 从顾客 A 账户余额中扣除电影票价；
2. 给影院 B 的账户余额增加这张电影票价；
3. 记录一条交易日志。

也就是说，要完成这个交易，我们需要 update 两条记录，并 insert 一条记录。当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。

那么，你会怎样安排这三个语句在事务中的顺序呢？试想如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

死锁的检测与避免

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁，InnoDB的行级锁是基于索引实现的，如果查询语句未命中任何索引，那么InnoDB会使用表级锁. 此外，InnoDB的行级锁是针对索引加的锁，不针对数据记录，因此即使访问不同行的记录，如果使用了相同的索引键仍然会出现锁冲突

这时候，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
• 一种策略是，主动发起死锁检测，发现死锁后**，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行**，其实就是破坏不可抢占条件。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。所以，正常情况下我们还是要采用第二种策略，即主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的

主动检测的问题

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务

如何避免死锁

发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个则可以获取锁完成事务，我们可以采取以上方式避免死锁：

• 通过表级锁来减少死锁产生的概率；
• 多个程序尽量约定以相同的顺序访问表
• 同一个事务尽可能做到一次锁定所需要的所有资源

以上关于死锁

表级锁&行级锁总结

MyISAM和InnoDB存储引擎使⽤的锁：MyISAM采⽤表级锁(table-level locking)。InnoDB⽀持⾏级锁(row-level locking)和表级锁,默认为⾏级锁,表级锁和⾏级锁对⽐：

• 表级锁： MySQL中锁定粒度较大的⼀种锁，对当前操作的整张表加锁，实现简单，资源消耗也⽐较少，加锁快，不会出现死锁。其锁定粒度最⼤，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都⽀持表级锁。
• ⾏级锁： MySQL中锁定 粒度最⼩的⼀种锁，只针对当前操作的⾏进⾏加锁。 ⾏级锁能⼤⼤减少数据库操作的冲突。其加锁粒度最⼩，并发度⾼，但加锁的开销也最⼤，加锁慢，会出现死锁。

InnoDB行级锁支持的算法有如下三种算法

• Record Lock: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项
• Gap Lock: 对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。Gap锁设计的⽬的是为了阻⽌多个事务将记录插⼊到同⼀范围内，⽽这会导致幻读问题的产⽣；除了外键约束和唯⼀性检查外，其余情况关闭Gap，仅使⽤record
• Next-key Lock： 锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。Innodb对于⾏的查询使⽤next-key lock；当查询的索引含有唯⼀属性时，将next-key lock降级为record key

虽然使用行级索具有粒度小、并发度高等特点，但是表级锁有时候也是非常必要的：

• 事务更新大表中的大部分数据直接使用表级锁效率更高；
• 事务比较复杂**，使用行级索很可能引起死锁导致回滚**

Next-key Lock的设计其实就是为了解决幻读问题，这样，Innodb就可以在可重复读的隔离级别基础之上实现串行隔离级别

锁与事务的实现

上文我们花大量的篇幅介绍了事务的实现和锁的机制，这部分我们来把他们联系起来，即锁是如何在事务的实现中发挥作用，我们知道读未提交不需要做任何控制，所以不讨论，串行化是指对数据加了读写锁，而且由于事务提交后才会释放锁，且其它线程不能对数据再加任何锁，也即数据对其它线程不可见，实现机制比较明确，这里也不多做讨论。

提出问题

可重复读级别下，事务中读取的数据在整个事务过程中都是一致的，那么别的事务更新了数据，当前事务再去更新数据的时候，看到的是更新后的，还是更新前的？举个例子，初始插入值为insert into t(id, k) values(1,1),(2,2);

需要注意，begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令

1. 事务 C 没有显式地使用 begin/commit，表示这个 update 语句本身就是一个事务，语句完成的时候会自动提交，因为事务C本来就只有一条执行语句。
2. 事务 B 在更新了行之后查询
3. 事务 A 在一个只读事务中查询，并且时间顺序上是在事务 B 的查询之后，接着提交
4. 事务 B 提交

在这里，事务 B 查到的 k 的值是 3，而事务 A 查到的 k 的值是 1，我们需要知道为什么结果是这样

MVCC机制

这部分详细讲解下MVCC机制，包括快照和事务ID

快照

在 MySQL 里，有两个视图的概念，这两个视图分别用于不同的场景。

• 一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。
• 一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”

第二种视图更像是一种快照。在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。

事务ID

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id

比如，如果有一个事务，它的低水位事务ID是 18，那么当它访问这一行数据时，就会从 V4 通过 U3 计算出 V3，所以在它看来，这一行的值是 11

undo log

undo log 在哪呢？实际上，上图的三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来，返回过来看

回滚段是真实存在的，而视图也就是快照是一种逻辑形态，是计算出来的。

MVCC实现

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。因此，一个事务只需要在启动的时候声明说，

• “以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；
• 如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。

当然，如果“上一个版本”也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认的

在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）

这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx_id（对一个数据的操作ID），有以下几种可能：

1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
2. 如果落在黄色部分，那就包括两种情况
   • a. 若 row trx_id 在活跃数组中，表示这个版本是由还没提交的事务生成的，不可见；
   • b. 若 row trx_id 不在活跃数组中，表示这个版本是已经提交了的事务生成的，可见。
3. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；事务ID一定大于当前。

我们举个例子，假如事务ID从【90,100】，其中90,91,93,95,98,99,100已提交，则活跃事务ID数组为【92,96,97】，低水位为92，当前事务为94，高水位为101。则可以进行如下划分：

• 绿色区域，低水位之前已提交的事务ID，90,91可见。包括当前事务虽然未提交刚开启，但是自身可见，94可见
• 黄色区域，低水位到高水位之间，剩下的【92,100】，这一系列事务ID，分为两类
  • a. 若 row trx_id 在活跃数组中，【92,96,97】不可见。
  • b. 若 row trx_id 不在活跃数组中，93,95,98,99,100均可见
• 红色区域，未开始的事务101及以后的事务ID都不可见

以上就是一致性视图的使用规范。对于上边的行数据版本链问题，低水位是18，则在所有版本的数据中，trx_id低于18的版本都可见，所以值为11肯定是可见的，有了这个声明后，系统里面随后发生的更新，是不是就跟这个事务看到的内容无关了呢？因为之后的更新，生成的版本一定属于上面的 3或者 2(a) 的情况，而对它来说，这些新的数据版本是不存在的，所以这个事务的快照，就是“静态”的了

解决问题

接下来，我们继续看一下开始提出问题的三个事务，分析下事务 A 的语句返回的结果，为什么是 k=1。这里，我们不妨做如下假设：

• 事务 A 开始前，系统里面只有一个活跃事务 ID 是 99；
• 事务 A、B、C 的版本号分别是 100、101、102，且当前系统里只有这四个事务；

三个事务开始前，(1,1）这一行数据的 row trx_id 是 90。这样，事务 A 的视图数组就是**[99,100], 事务 B 的视图数组是[99,100,101], 事务 C 的视图数组是[99,100,101,102]**。为了简化分析，我先把其他干扰语句去掉，只画出跟事务 A 查询逻辑有关的操作：

• 从图中可以看到，第一个有效更新是事务 C，把数据从 (1,1) 改成了 (1,2)。这时候，这个数据的最新版本的 row trx_id 是 102，而 90 这个版本已经成为了历史版本
• 第二个有效更新是事务 B，把数据从 (1,2) 改成了 (1,3)。这时候，这个数据的最新版本（即 row trx_id）是 101，而 102 又成为了历史版本

在事务 A 查询的时候，其实事务 B 还没有提交，但是它生成的 (1,3) 这个版本已经变成当前版本了。但这个版本对事务 A 必须是不可见的，否则就变成脏读了

读取逻辑

好，现在事务 A 要来读数据了，它的视图数组是[99,100]。当然了，读数据都是从当前版本读起的。所以，事务 A 查询语句的读数据流程是这样的：

1. 找到 (1,3) 的时候，判断出 row trx_id=101，比高水位大，处于红色区域，不可见；
2. 接着，找到上一个历史版本，一看 row trx_id=102，比高水位大，处于红色区域，不可见；
3. 再往前找，终于找到了（1,1)，它的 row trx_id=90，比低水位小，处于绿色区域，可见。

这样执行下来，虽然期间这一行数据被修改过，但是事务 A 不论在什么时候查询，看到这行数据的结果都是一致的，所以我们称之为一致性读，一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

• 版本未提交，不可见；
• 版本已提交，但是是在视图创建后提交的，不可见；
• 版本已提交，而且是在视图创建前提交的，可见。

现在，我们用这个规则来判断查询结果

1. 事务 A 的查询语句的视图数组是在事务 A 启动的时候生成的，这时候：(1,3) 还没提交，属于情况 1，不可见；
2. (1,2) 虽然提交了，但是是在视图数组创建之后提交的，属于情况 2，不可见
3. (1,1) 是在视图数组创建之前提交的，可见。

去掉数字对比后，只用时间先后顺序来判断，分析起来是不是轻松多了。所以，后面我们就都用这个规则来分析

更新逻辑

事务 B 的 update 语句，如果按照一致性读，好像结果不对？事务 B 的视图数组是先生成的，之后事务 C 才提交，不是应该看不见 (1,2) 吗，怎么能算出 (1,3) 来

是的，如果事务 B 在更新之前查询一次数据，这个查询返回的 k 的值确实是 1

但是，当它要去更新数据的时候，就不能再在历史版本上更新了，否则事务 C 的更新就丢失了。因此，事务 B 此时的 set k=k+1 是在（1,2）的基础上进行的操作。所以，这里就用到了这样一条规则：更新数据都是先读后写的，而这个读，只能读当前的值，称为当前读（current read）

因此，在更新的时候，当前读拿到的数据是 (1,2)，更新后生成了新版本的数据 (1,3)，这个新版本的 row trx_id 是 101。所以，在执行事务 B 查询语句的时候，一看自己的版本号是 101，最新数据的版本号也是 101，是自己的更新，可以直接使用，所以查询得到的 k 的值是 3。有点像Java的volatile机制。

当前读

这里我们提到了一个概念，叫作当前读。其实，除了 update 语句外，select 语句如果加锁，也是当前读。所以，如果把事务 A 的查询语句 select * from t where id=1 修改一下，加上 lock in share mode 或 for update，也都可以读到版本号是 101 的数据，返回的 k 的值是 3。下面这两个 select 语句，就是分别加了读锁（S 锁，共享锁）和写锁（X 锁，排他锁）

mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;

言归正传，假设事务 C 不是马上提交的，而是变成了下面的事务 C’，会怎么样呢？

事务 C’的不同是，更新后并没有马上提交，在它提交前，事务 B 的更新语句先发起了。前面说过了，虽然事务 C’还没提交，但是 (1,2) 这个版本也已经生成了，并且是当前的最新版本。那么，事务 B 的更新语句会怎么处理呢？

事务 C’没提交，也就是说 (1,2) 这个版本上的写锁还没释放。而事务 B更新时 是当前读，必须要读最新版本，而且必须加锁，因此就被锁住了，必须等到事务 C’释放这个锁，才能继续它的当前读

可重复读和读提交的实现

到这里，我们把一致性读、当前读和行锁就串起来了。那么事务的可重复读的能力是怎么实现的？

• 可重复读的核心就是一致性读（consistent read）；事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。
• 读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

那么，我们再看一下，在读提交隔离级别下，事务 A 和事务 B 的查询语句查到的 k，分别应该是多少呢？这里需要说明一下，“start transaction with consistent snapshot; ”的意思是从这个语句开始，创建一个持续整个事务的一致性快照。所以，在读提交隔离级别下，这个用法就没意义了，等效于普通的 start transaction。下面是读提交时的状态图，可以看到这两个查询语句的创建视图数组的时机发生了变化，就是图中的 read view 框。（注意：这里，我们用的还是事务 C 的逻辑直接提交，而不是事务 C’）

这时，事务 A 的查询语句的视图数组是在执行这个语句的时候创建的，时序上 (1,2)、(1,3) 的生成时间都在创建这个视图数组的时刻之前。但是，在这个时刻：

• (1,3) 事务B还没提交，属于情况 1，不可见；
• (1,2) 事务C提交了，属于情况 3，可见。

所以，这时候事务 A 查询语句返回的是 k=2。显然地，事务 B 查询结果 k=3，因为B的视图里，C已经提交了。自己状态又可见，直接加2次。

隔离级别与行锁

Innodb对于行级锁，行文至此，我们最终探讨一次四种隔离级别是如何产生效果的呢？是依据MVCC机制和行级别的锁来实现的，针对每种隔离级别分别介绍一下：

• 读未提交，不创建视图，所有语句不加任何锁，有脏读问题。解决办法就是下面的读已提交
• 读已提交，执行sql时创建一致性视图，SELECT语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性，且SELECT不加锁，更新语句使用当前读机制+两阶段行锁（Record Lock排它锁）机制，存在的问题不可重复读。即在一次事务之间，进行了两次读取，但是结果不一样，不可重复读问题
• 可重复读，事务开始时创建一致性视图，查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性，且SELECT不加锁，更新语句使用当前读机制+两阶段行锁（Record Lock排它锁）机制，可重复读阻止的写事务包括update（只给存在的数据行加上了锁），但是不包括insert、delete（新行不存在，所以没有办法加锁）
• 串行化，不创建视图，读加读锁，写加跨行级别Next-key Lock排他锁。阻止其它读写事务 ，基本上就是一个个执行事务，所以叫串行化。

整体的隔离机制介绍如上，可算是对事务和锁的实现有了一个全盘的掌握了。