一、MySQL基础架构
基本架构
MySQL可以分为Server层和存储引擎层两部分
- Server层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖MySQL的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。
- 而存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB,它从MySQL 5.5.5版本开始成为了默认存储引擎。
service层
连接器
第一步,你会先连接到这个数据库上,这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。
如果用户名密码认证通过,连接器会到权限表里面查出你拥有的权限。之后,这个连接
里面的权限判断逻辑,都将依赖于此时读到的权限。对这个用户的权限做了修改,也不会影响已经存在连接的权限。修改完成后,只有再新建的连接才会使用新的权限设置。
客户端如果太长时间没动静,连接器就会自动将它断开。这个时间是由参数wait_timeout
控制的,默认值是8小时。
数据库里面,长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个。
建立连接的过程通常是比较复杂的,所以我建议你在使用中要尽量减少建立连接的动作,也就是尽量使用长连接。
但是全部使用长连接后,你可能会发现,有些时候MySQL占用内存涨得特别快,这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来,可能导致内存占用太大,被系统强行杀掉(OOM),从现象看就是MySQL异常重启了。
- 定期断开长连接。使用一段时间,或者程序里面判断执行过一个占用内存的大查询后,断开连接,之后要查询再重连。
- 如果你用的是MySQL 5.7或更新版本,可以在每次执行一个比较大的操作后,通过执行
mysql_reset_connection
来重新初始化连接资源。这个过程不需要重连和重新做权限验证,但是会将连接恢复到刚刚创建完时的状态。
查询缓存
连接建立完成后,你就可以执行select语句了。执行逻辑就会来到第二步:查询缓存。
MySQL拿到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式,被直接缓存在内存中。key是查询的语句,value是查询的结果。如果你的查询能够直接在这个缓存中找到key,那么这个value就会被直接返回给客户端。
如果语句不在查询缓存中,就会继续后面的执行阶段。执行完成后,执行结果会被存入查询缓存中。你可以看到,如果查询命中缓存,MySQL不需要执行后面的复杂操作,就可以直接返回结果,这个效率会很高。
查询缓存往往弊大于利:查询缓存的失效非常频繁,只要有对一个表的更新,这个表上所有的查询缓存都会被清空。MYSQL 8.0 后不支持查询缓存
分析器
如果没有命中查询缓存,就要开始真正执行语句了。首先,MySQL需要知道你要做什么,因此需要对SQL语句做解析。
分析器先会做“词法分析”,识别出里面的字符串分别是什么,代表什么。
做完了这些识别以后,就要做“语法分析”,判断你输入的这个SQL语句是否满足MySQL语法。
优化器
经过了分析器,MySQL就知道你要做什么了。在开始执行之前,还要先经过优化器的处理。
优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序。
优化器阶段完成后,这个语句的执行方案就确定下来了,然后进入执行器阶段。
执行器
MySQL通过分析器知道了你要做什么,通过优化器知道了该怎么做,于是就进入了执行器阶段,开始执行语句。
开始执行的时候,要先判断一下你对这个表T有没有执行查询的权限,如果没有,就会返回没有权限的错误。
如果有权限,就打开表继续执行。打开表的时候,执行器就会根据表的引擎定义,去使用这个引擎提供的接口。
二、日志系统:一条SQL更新语句是如何执行的?
- 先连接器连接
- 分析器通过词法和语法分析是一条更新语句
- 优化器决定要是使用ID的索引
日志模块:redo log
类比《孔乙己》的掌柜记账 WAL 技术,WAL 的全称是 Write-Ahead Logging,它的关键点就是先写日志,再写磁盘。也就是先写粉板,等不忙的时候再写账本。
redo log 是InnoDB 引擎特有的,从开头写到末尾又回开头循环写,有crash-safe能力
write pos是当前记录的位置,一边写一边后移,写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置,也是往后推移并且循环的,擦除记录前要把记录更新到数据文件。
如果write pos追上checkpoint,表示“粉板”满了,这时候不能再执行新的更新,得停下来先擦掉一些记录,把checkpoint推进一下。
有了redo log,InnoDB就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为crash-safe。
日志模块:binlog
MySQL整体来看,其实就有两块:一块是Server层,它主要做的是MySQL功能层面的事情;还有一块是引擎层,负责存储相关的具体事宜。上面我们聊到的粉板redo log是InnoDB引擎特有的日志,而Server层也有自己的日志,称为binlog(归档日志)。
这两种日志有以下三点不同。
- redo log是InnoDB引擎特有的;binlog是MySQL的Server层实现的,所有引擎都可以使用。
- redo log是物理日志,记录的是“在某个数据页上做了什么修改”;binlog是逻辑日志,记录的是这个语句的原始逻辑,比如“给ID=2这一行的c字段加1 ”。
- redo log是循环写的,空间固定会用完;binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。
执行这个简单的update语句时的内部流程。
- 执行器先找引擎取ID=2这一行。ID是主键,引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁盘读入内存,然后再返回。
- 执行器拿到引擎给的行数据,把这个值加上1,比如原来是N,现在就是N+1,得到新的一行数据,再调用引擎接口写入这行新数据。
- 引擎将这行新数据更新到内存中,同时将这个更新操作记录到redo log里面,此时redo log处于prepare状态。然后告知执行器执行完成了,随时可以提交事务。
- 执行器生成这个操作的binlog,并把binlog写入磁盘。
- 执行器调用引擎的提交事务接口,引擎把刚刚写入的redo log改成提交(commit)状态,更新完成。
这个update语句的执行流程图,图中浅色框表示是在InnoDB内部执行的,深色框表示是在执行器中执行的。
将redo log的写入拆成了两个步骤:prepare和commit,这就是"两阶段提交"。
如果不使用“两阶段提交”,那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。
小结
redo log用于保证crash-safe能力。innodb_flush_log_at_trx_commit这个参数设置成1的时候,表示每次事务的redo log都直接持久化到磁盘。这个参数我建议你设置成1,这样可以保证MySQL异常重启之后数据不丢失。
sync_binlog这个参数设置成1的时候,表示每次事务的binlog都持久化到磁盘。这个参数我也建议你设置成1,这样可以保证MySQL异常重启之后binlog不丢失。
三、事务隔离隔离性与隔离级别
提到事务,你肯定会想到ACID(Atomicity、Consistency、Isolation、Durability,即原子性、一致性、隔离性、持久性),今天我们就来说说其中I,也就是“隔离性”。
当数据库上有多个事务同时执行的时候,就可能出现脏读(dirty read)、不可重复读(non-repeatable read)、幻读(phantom read)的问题,为了解决这些问题,就有了“隔离级别”的概念。
在谈隔离级别之前,你首先要知道,你隔离得越严实,效率就会越低。因此很多时候,我们都要在二者之间寻找一个平衡点。SQL标准的事务隔离级别包括:读未提交(read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(serializable )。
- 读未提交是指,一个事务还没提交时,它做的变更就能被别的事务看到。
- 读提交是指,一个事务提交之后,它做的变更才会被其他事务看到。
- 可重复读是指,一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的。
- 串行化,顾名思义是对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行。
- 在实现上,数据库里面会创建一个视图,访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下,这个视图是在事务启动时创建的,整个事务存在期间都用这个视图。在“读提交”隔离级别下,这个视图是在每个SQL语句开始执行的时候创建的。这里需要注意的是,“读未提交”隔离级别下直接返回记录上的最新值,没有视图概念;而“串行化”隔离级别下直接用加锁的方式来避免并行访问。
事务隔离的实现
在MySQL中,实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值,通过回滚操作,都可以得到前一个状态的值。
- 假设一个值从1被按顺序改成了2、3、4,在回滚日志里面就会有类似下面的记录。
- 长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚记录都必须保留,这就会导致大量占用存储空间。
除了对回滚段的影响,长事务还占用锁资源,也可能拖垮整个库。
事务的启动方式
如前面所述,长事务有这些潜在风险,我当然是建议你尽量避免。MySQL的事务启动方式有以下几种:
- 显式启动事务语句,
begin
或start transaction
。配套的提交语句是commit
,回滚语句是rollback
。 set autocommit=0
,这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句,这个事务就启动了,而且并不会自动提交。这个事务持续存在直到你主动执行commit 或 rollback 语句,或者断开连接。
建议你总是使用set autocommit=1
, 然后通过显式语句的方式来启动事务。
在autocommit为1的情况下,用begin
显式启动的事务,如果执行commit
则提交事务。如果执行 commit work and chain
,则是提交事务并自动启动下一个事务,这样也省去了再次执行begin语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。
你可以在information_schema库的innodb_trx这个表中查询长事务,比如下面这个语句,用于查找持续时间超过60s的事务。
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
小结
1、务的特性:原子性、一致性、隔离性、持久性
2、多事务同时执行的时候,可能会出现的问题:脏读、不可重复读、幻读
3、事务隔离级别:读未提交、读提交、可重复读、串行化
4、不同事务隔离级别的区别:
读未提交:一个事务还未提交,它所做的变更就可以被别的事务看到
读提交:一个事务提交之后,它所做的变更才可以被别的事务看到
可重复读:一个事务执行过程中看到的数据是一致的。未提交的更改对其他事务是不可见的
串行化:对应一个记录会加读写锁,出现冲突的时候,后访问的事务必须等前一个事务执行完成才能继续执行
5、配置方法:启动参数transaction-isolation
6、事务隔离的实现:每条记录在更新的时候都会同时记录一条回滚操作。同一条记录在系统中可以存在多个版本,这就是数据库的多版本并发控制(MVCC)。
7、回滚日志什么时候删除?系统会判断当没有事务需要用到这些回滚日志的时候,回滚日志会被删除。
8、什么时候不需要了?当系统里么有比这个回滚日志更早的read-view的时候。
9、为什么尽量不要使用长事务。长事务意味着系统里面会存在很老的事务视图,在这个事务提交之前,回滚记录都要保留,这会导致大量占用存储空间。除此之外,长事务还占用锁资源,可能会拖垮库。
10、事务启动方式:一、显式启动事务语句,begin或者start transaction,提交commit,回滚rollback;二、set autocommit=0,该命令会把这个线程的自动提交关掉。这样只要执行一个select语句,事务就启动,并不会自动提交,直到主动执行commit或rollback或断开连接。
11、建议使用方法一,如果考虑多一次交互问题,可以使用commit work and chain语法。在autocommit=1的情况下用begin显式启动事务,如果执行commit则提交事务。如果执行commit work and chain则提交事务并自动启动下一个事务。
索引的出现其实就是为了提高数据查询的效率,就像书的目录一样
索引的常见模型
索引的出现是为了提高查询效率,但是实现索引的方式却有很多种,所以这里也就引入了索引模型的概念。常见的有:哈希表、有序数组和搜索树。
哈希表
- 一种键-值(key-value)存储数据的结构,哈希的思路很简单,把值放在数组里,用一个哈希函数把 key 换算成一个确定的位置,然后把 value 放在数组的这个位置
- 多个 key 值经过哈希函数的换算,会出现同一个值的情况。处理这种情况的一种方法是,拉出一个链表,用哈希索引做区间查询速度很慢
- 哈希表这种结构适用于只有等值查询的场景,比如 Memcached 及其他一些NoSQL 引擎
有序数组
- 在等值查询和范围查询场景中的性能就都非常优秀
- 在需要更新数据的时候,往中间插入一个记录就必须得挪动后面所有的记录,成本太高
- 有序数组索引只适用于静态存储引擎
二叉搜索树
- 查询和更新复杂度都是O(log(N)),但是实际上大多数的数据库存储却并不使用二叉树。其原因是,索引不止存在内存中,还要写到磁盘上。
N叉树
- 以InnoDB的一个整数字段索引为例,这个N差不多是1200。这棵树高是4的时候,就可以存1200的3次方个值,这已经17亿了。考虑到树根的数据块总是在内存中的,一个10亿行的表上一个整数字段的索引,查找一个值最多只需要访问3次磁盘。其实,树的第二层也有很大概率在内存中,那么访问磁盘的平均次数就更少了。
- N叉树由于在读写上的性能优点,以及适配磁盘的访问模式,已经被广泛应用在数据库引擎中了。
InnoDB 的索引模型
InnoDB使用了B+树索引模型,所以数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树。
eg:一个表的建表语句是:
表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6),两棵树的示例示意图如下。
从图中不难看出,根据叶子节点的内容,索引类型分为主键索引和非主键索引。
- 主键索引的叶子节点存的是整行数据。在InnoDB里,主键索引也被称为聚簇索引(clustered index)。
- 非主键索引的叶子节点内容是主键的值。在InnoDB里,非主键索引也被称为二级索引(secondary index)。
每一张表包含多个B+树,树结点的key值就是某一行的主键或索引,value是该行的其他数据。新建索引就是新增一个B+树,查询不走索引就是遍历 主B+树。
基于主键索引和普通索引的查询有什么区别?
如果语句是select * from T where ID=500,即主键查询方式,则只需要搜索ID这棵B+树;
如果语句是select * from T where k=5,即普通索引查询方式,则需要先搜索k索引树,得到ID的值为500,再到ID索引树搜索一次。这个过程称为回表。
也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。
覆盖索引
eg:表的初始化语句。
我们一起来看看这条SQL查询语句的执行流程:
- 在k索引树上找到k=3的记录,取得 ID = 300;
- 再到ID索引树查到ID=300对应的R3;
- 在k索引树取下一个值k=5,取得ID=500;
- 再回到ID索引树查到ID=500对应的R4;
- 在k索引树取下一个值k=6,不满足条件,循环结束。
在这个过程中,回到主键索引树搜索的过程,我们称为回表。可以看到,这个查询过程读了k索引树的3条记录(步骤1、3和5),回表了两次(步骤2和4)。
如果执行的语句是select ID from T where k between 3 and 5,这时只需要查ID的值,而ID的值已经在k索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引k已经“覆盖了”我们的查询需求,我们称为覆盖索引。
由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。
eg:
假设这个市民表的定义是这样的:
有一个高频请求,要根据市民的身份证号查询他的姓名,这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引,不再需要回表查整行记录,减少语句的执行时间
最左前缀原则
B+树这种索引结构,可以利用索引的“最左前缀”,来定位记录。
为了直观地说明这个概念,我们用(name,age)这个联合索引来分析。
如果你要查的是所有名字第一个字是“张”的人,你的SQL语句的条件是"where name like ‘张%’"。这时,你也能够用上这个索引,查找到第一个符合条件的记录是ID3,然后向后遍历,直到不满足条件为止。
可以看到,不只是索引的全部定义,只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段,也可以是字符串索引的最左M个字符。
在建立联合索引的时候,如何安排索引内的字段顺序?
这里我们的评估标准是,索引的复用能力。因为可以支持最左前缀,所以当已经有了(a,b)这个联合索引后,一般就不需要单独在a上建立索引了。因此,第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。
那么,如果既有联合查询,又有基于a、b各自的查询呢?查询条件里面只有b的语句,是无法使用(a,b)这个联合索引的,这时候你不得不维护另外一个索引,也就是说你需要同时维护(a,b)、(b) 这两个索引。
这时候,我们要考虑的第二原则就是空间了。比如上面这个市民表的情况,name字段是比age字段大的 ,那我就建议你创建一个(name,age)的联合索引和一个(age)的单字段索引。
索引下推
上一段我们说到满足最左前缀原则的时候,最左前缀可以用于在索引中定位记录。
索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
小结
1.索引的作用:提高数据查询效率
2.常见索引模型:哈希表、有序数组、搜索树
3.哈希表:键 - 值(key - value)。
4.哈希思路:把值放在数组里,用一个哈希函数把key换算成一个确定的位置,然后把value放在数组的这个位置
5.哈希冲突的处理办法:链表
6.哈希表适用场景:只有等值查询的场景
7.有序数组:按顺序存储。查询用二分法就可以快速查询,时间复杂度是:O(log(N))
8.有序数组查询效率高,更新效率低
9.有序数组的适用场景:静态存储引擎。
10.二叉搜索树:每个节点的左儿子小于父节点,父节点又小于右儿子
11.二叉搜索树:查询时间复杂度O(log(N)),更新时间复杂度O(log(N))
12.数据库存储大多不适用二叉树,因为树高过高,会适用N叉树
13.InnoDB中的索引模型:B+Tree
14.索引类型:主键索引、非主键索引
主键索引的叶子节点存的是整行的数据(聚簇索引),非主键索引的叶子节点内容是主键的值(二级索引)
15.主键索引和普通索引的区别:主键索引只要搜索ID这个B+Tree即可拿到数据。普通索引先搜索索引拿到主键值,再到主键索引树搜索一次(回表)
16.一个数据页满了,按照B+Tree算法,新增加一个数据页,叫做页分裂,会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并,合并的过程是分裂过程的逆过程。
17.从性能和存储空间方面考量,自增主键往往是更合理的选择。
联合索引技巧
1、覆盖索引:如果查询条件使用的是普通索引(或是联合索引的最左原则字段),查询结果是联合索引的字段或是主键,不用回表操作,直接返回结果,减少IO磁盘读写读取正行数据
2、最左前缀:联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符
3、联合索引:根据创建联合索引的顺序,以最左原则进行where检索,比如(age,name)以age=1 或 age= 1 and name=‘张三’可以使用索引,单以name=‘张三’ 不会使用索引,考虑到存储空间的问题,还请根据业务需求,将查找频繁的数据进行靠左创建索引。
4、索引下推:like 'hello%’and age >10 检索,MySQL5.6版本之前,会对匹配的数据进行回表查询。5.6版本后,会先过滤掉age<10的数据,再进行回表查询,减少回表率,提升检索速度
根据加锁的范围,MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类
全局锁
顾名思义,全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)
。当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。
风险:
- 如果在主库备份,在备份期间不能更新,业务停摆
- 如果在从库备份,备份期间不能执行主库同步的binlog,导致主从延迟
全局锁的典型使用场景是,做全库逻辑备份。也就是把整库每个表都select出来存成文本。
不加锁的话,备份系统备份的得到的库不是一个逻辑时间点,这个视图是逻辑不一致的
官方导出数据工具mysqldump,使用参数 -single-transaction,会启动一个事务,来确保拿到一致性视图,适用于InnoDB引擎,MyISAM只能使用FTWRL命令
不使用set global readonly=true方式设置为只读的原因
- 在有些系统中,readonly的值会被用来做其他逻辑,比如判断主备库。所以修改global变量的方式影响太大。
- 在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开,那么MySQL会自动释放这个全局锁,整个库回到可以正常更新的状态。而将整个库设置为readonly之后,如果客户端发生异常,则数据库就会一直保持readonly状态,这样会导致整个库长时间处于不可写状态,风险较高。
表级锁
MySQL里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。
表锁的语法是 lock tables … read/write
。与FTWRL类似,可以用unlock tables
主动释放锁,也可以在客户端断开的时候自动释放。需要注意,lock tables语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。
表锁是最常用的处理并发的方式,lock table 建议不要使用, 影响太大
另一类表级的锁是MDL(metadata lock)。MDL不需要显式使用,在访问一个表的时候会被自动加上,MDL会直到事务提交才释放。MDL的作用是,保证读写的正确性。
当对一个表做增删改查操作的时候,加MDL读锁;当要对表做结构变更操作的时候,加MDL写锁。
- 读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。
- 读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。
虽然MDL锁是系统默认会加的,但却是你不能忽略的一个机制。
如何安全地给小表加字段?
- 首先我们要解决长事务,事务不提交,就会一直占着MDL锁。在MySQL的information_schema 库的 innodb_trx 表中,你可以查到当前执行中的事务。如果你要做DDL变更的表刚好有长事务在执行,要考虑先暂停DDL,或者kill掉这个长事务。
- 比较理想的机制是,在alter table语句里面设定等待时间,如果在这个指定的等待时间里面能够拿到MDL写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。
行锁
在InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。
知道了这个设定,对我们使用事务有什么帮助呢?那就是,如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
死锁和死锁检测
当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态,称为死锁。
当出现死锁以后,有两种策略:
- 一种策略是,直接进入等待,直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。容易出现误杀
- 另一种策略是,发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on,表示开启这个逻辑。死锁检测要耗费大量的 CPU 资源。
begin/start transaction
命令并不是一个事务的起点,在执行到它们之后的第一个操作InnoDB表的语句(第一个快照读语句),事务才真正启动。如果你想要马上启动一个事务,可以使用start transaction with consistent snapshot
这个命令。
在MySQL里,有两个“视图”的概念,它没有物理结构,作用是事务执行期间用来定义“我能看到什么数据”:
- 一个是view。它是一个用查询语句定义的虚拟表,在调用的时候执行查询语句并生成结果。创建视图的语法是create view … ,而它的查询方法与表一样。
- 另一个是InnoDB在实现MVCC时用到的一致性读视图,即consistent read view,用于支持RC(Read Committed,读提交)和RR(Repeatable Read,可重复读)隔离级别的实现。
“快照”在MVCC里是怎么工作的?
在可重复读隔离级别下,事务在启动的时候就“拍了个快照”。注意,这个快照是基于整库的。
快照是怎么实现的
InnoDB里面每个事务有一个唯一的事务ID,叫作transaction id。它是在事务开始的时候向InnoDB的事务系统申请的,是按申请顺序严格递增的。
而每行数据也都是有多个版本的。每次事务更新数据的时候,都会生成一个新的数据版本,并且把transaction id赋值给这个数据版本的事务ID,记为row trx_id。同时,旧的数据版本要保留,并且在新的数据版本中,能够有信息可以直接拿到它。
也就是说,数据表中的一行记录,其实可能有多个版本(row),每个版本有自己的row trx_id。
图中虚线框里是同一行数据的4个版本,当前最新版本是V4,k的值是22,它是被transaction id 为25的事务更新的,因此它的row trx_id也是25。三个虚线箭头,就是undo log;而V1、V2、V3并不是物理上真实存在的,而是每次需要的时候根据当前版本和undo log计算出来的。InnoDB为每个事务构造了一个数组,用来保存这个事务启动瞬间,当前正在“活跃”的所有事务ID。“活跃”指的就是,启动了但还没提交。
数组里面事务ID的最小值记为低水位,当前系统里面已经创建过的事务ID的最大值加1记为高水位。
这个视图数组和高水位,就组成了当前事务的一致性视图(read-view)。
这个视图数组把所有的row trx_id 分成了几种不同的情况。
这样,对于当前事务的启动瞬间来说,一个数据版本的row trx_id,有以下几种可能:
- 如果落在绿色部分,表示这个版本是已提交的事务或者是当前事务自己生成的,这个数据是可见的;
- 如果落在红色部分,表示这个版本是由将来启动的事务生成的,是肯定不可见的;
- 如果落在黄色部分,那就包括两种情况
a. 若 row trx_id在数组中,表示这个版本是由还没提交的事务生成的,不可见;
b. 若 row trx_id不在数组中,表示这个版本是已经提交了的事务生成的,可见。
InnoDB利用了“所有数据都有多个版本”的这个特性,实现了“秒级创建快照”的能力(可重复读、一致性读)。
一个数据版本,对于一个事务视图来说,除了自己的更新总是可见以外,有三种情况:
- 版本未提交,不可见;
- 版本已提交,但是是在视图创建后提交的,不可见;
- 版本已提交,而且是在视图创建前提交的,可见。
现在,我们用这个规则来判断图中的查询结果,事务A的查询语句的视图数组是在事务A启动的时候生成的,这时候:
- (1,3)还没提交,属于情况1,不可见;
- (1,2)虽然提交了,但是是在视图数组创建之后提交的,属于情况2,不可见;
- (1,1)是在视图数组创建之前提交的,可见。
更新逻辑
更新数据都是先读后写的,而这个读,只能读当前的值,称为“当前读”(current read)
- 除了update语句外,select语句如果加锁,也是当前读。
事务的可重复读的能力是怎么实现的?
- 可重复读的核心就是一致性读(consistent read);而事务更新数据的时候,只能用当前读。如果当前的记录的行锁被其他事务占用的话,就需要进入锁等待。
读提交的逻辑和可重复读的逻辑类似,它们最主要的区别是:
- 在可重复读隔离级别下,只需要在事务开始的时候创建一致性视图,之后事务里的其他查询都共用这个一致性视图;
- 在读提交隔离级别下,每一个语句执行前都会重新算出一个新的视图。
小结
innodb支持RC和RR隔离级别实现是用的一致性视图(consistent read view);InnoDB的行数据有多个版本,每个数据版本有自己的row trx_id,每个事务或者语句有自己的一致性视图。普通查询语句是一致性读,一致性读会根据row trx_id和一致性视图确定数据版本的可见性。
- 对于可重复读,查询只承认在事务启动前就已经提交完成的数据;
- 对于读提交,查询只承认在语句启动前就已经提交完成的数据;
- 而当前读,总是读取已经提交完成的最新版本(涉及到行锁)。
事务在启动时会拍一个快照,这个快照是基于整个库的.
基于整个库的意思就是说一个事务内,整个库的修改对于该事务都是不可见的(对于快照读的情况)
如果在事务内select t表,另外的事务执行了DDL t表,根据发生时间,要嘛锁住要嘛报错(参考第六章)事务是如何实现的MVCC呢?
(1)每个事务都有一个事务ID,叫做transaction id(严格递增)
(2)事务在启动时,找到已提交的最大事务ID记为up_limit_id。
(3)事务在更新一条语句时,比如id=1改为了id=2.会把id=1和该行之前的row trx_id写到undo log里,
并且在数据页上把id的值改为2,并且把修改这条语句的transaction id记在该行行头
(4)再定一个规矩,一个事务要查看一条数据时,必须先用该事务的up_limit_id与该行的transaction id做比对,
如果up_limit_id>=transaction id,那么可以看.如果up_limit_id<transaction id,则只能去undo log里去取。去undo log查找数据的时候,也需要做比对,必须up_limit_id>transaction id,才返回数据什么是当前读,由于当前读都是先读后写,只能读当前版本的值,所以为当前读.会更新事务内的up_limit_id为该事务的transaction id
为什么rr能实现可重复读而rc不能,分两种情况
(1)快照读的情况下,rr不能更新事务内的up_limit_id,
而rc每次会把up_limit_id更新为快照读之前最新已提交事务的transaction id,则rc不能可重复读
(2)当前读的情况下,rr是利用record lock+gap lock来实现的,而rc没有gap,所以rc不能可重复读