目录
全局锁
全局锁就是对整个数据库实例加锁。
Flush tables with read lock (FTWRL)
当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。
全局锁的典型使用场景是,做全库逻辑备份。也就是把整库每个表都select出来存成文本。
官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于MVCC的支持,这个过程中数据是可以正常更新的。
有了这个功能,为什么还需要FTWRL呢?一致性读是好,但前提是引擎要支持这个隔离级别。比如,对于MyISAM这种不支持事务的引擎,如果备份过程中有更新,总是只能取到最新的数据,那么就破坏了备份的一致性。这时,我们就需要使用FTWRL命令了。
single-transaction方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎,那么备份就只能通过FTWRL方法。这往往是DBA要求业务开发人员使用InnoDB替代MyISAM的原因之一。
既然要全库只读,为什么不使用set global readonly=true的方式呢?确实readonly方式也可以让全库进入只读状态,但我还是会建议你用FTWRL方式,主要有两个原因:
- 一是,在有些系统中,readonly的值会被用来做其他逻辑,比如用来判断一个库是主库还是备库。因此,修改global变量的方式影响面更大,不建议使用。
- 二是,在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开,那么MySQL会自动释放这个全局锁,整个库回到可以正常更新的状态。而将整个库设置为readonly之后,如果客户端发生异常,则数据库就会一直保持readonly状态,这样会导致整个库长时间处于不可写状态,风险较高。
表级锁
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(metadata lock)。MDL不需要显式使用,在访问一个表的时候会被自动加上。MDL的作用是,保证读写的正确性。你可以想象一下,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。
因此,在MySQL 5.5版本中引入了MDL,当对一个表做增删改查操作的时候,加MDL读锁;当要对表做结构变更操作的时候,加MDL写锁。
-
读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。
-
读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。
Online DDL的过程是这样的:
1. 拿MDL写锁
2. 降级成MDL读锁
3. 真正做DDL
4. 升级成MDL写锁
5. 释放MDL锁
1、2、4、5如果没有锁冲突,执行时间非常短。第3步占用了DDL绝大部分时间,这期间这个表可以正常读写数据,是因此称为“online ”
坑里:给一个小表加个字段,导致整个库挂了。
session A先启动,这时候会对表t加一个MDL读锁。由于session B需要的也是MDL读锁,因此可以正常执行。
之后session C会被blocked,是因为session A的MDL读锁还没有释放,而session C需要MDL写锁,因此只能被阻塞。
如果只有session C自己被阻塞还没什么关系,但是之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。前面我们说了,所有对表的增删改查操作都需要先申请MDL读锁,就都被锁住,等于这个表现在完全不可读写了。
如果某个表上的查询语句频繁,而且客户端有重试机制,也就是说超时后会再起一个新session再请求的话,这个库的线程很快就会爆满。
如何安全地给小表加字段?
首先我们要解决长事务,事务不提交,就会一直占着MDL锁。在MySQL的information_schema 库的 innodb_trx 表中,你可以查到当前执行中的事务。如果你要做DDL变更的表刚好有长事务在执行,要考虑先暂停DDL,或者kill掉这个长事务。
但考虑一下这个场景。如果你要变更的表是一个热点表,虽然数据量不大,但是上面的请求很频繁,而你不得不加个字段,你该怎么做呢?
这时候kill可能未必管用,因为新的请求马上就来了。比较理想的机制是,在alter table语句里面设定等待时间,如果在这个指定的等待时间里面能够拿到MDL写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。
MariaDB已经合并了AliSQL的这个功能,所以这两个开源分支目前都支持DDL NOWAIT/WAIT n这个语法。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
备份一般都会在备库上执行,你在用–single-transaction方法做逻辑备份的过程中,如果主库上的一个小表做了一个DDL,比如给一个表上加了一列。这时候,从备库上会看到什么现象呢?
假设这个DDL是针对表t1的, 这里我把备份过程中几个关键的语句列出来:
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT;
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */
在备份开始的时候,为了确保RR(可重复读)隔离级别,再设置一次RR隔离级别(Q1);
启动事务,这里用 WITH CONSISTENT SNAPSHOT确保这个语句执行完就可以得到一个一致性视图(Q2);
设置一个保存点,这个很重要(Q3);
show create 是为了拿到表结构(Q4),然后正式导数据 (Q5),回滚到SAVEPOINT sp,在这里的作用是释放 t1的MDL锁 (Q6。当然这部分属于“超纲”,上文正文里面都没提到。
DDL从主库传过来的时间按照效果不同,我打了四个时刻。题目设定为小表,我们假定到达后,如果开始执行,则很快能够执行完成。
参考答案如下:
-
如果在Q4语句执行之前到达,现象:没有影响,备份拿到的是DDL后的表结构。
-
如果在“时刻 2”到达,则表结构被改过,Q5执行的时候,报 Table definition has changed, please retry transaction,现象:mysqldump终止;
-
如果在“时刻2”和“时刻3”之间到达,mysqldump占着t1的MDL读锁,binlog被阻塞,现象:主从延迟,直到Q6执行完成。
-
从“时刻4”开始,mysqldump释放了MDL读锁,现象:没有影响,备份拿到的是DDL前的表结构。
行锁
MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁,比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。InnoDB是支持行锁的,这也是MyISAM被InnoDB替代的重要原因之一。
行锁就是针对数据表中行记录的锁。
实际上事务B的update语句会被阻塞,直到事务A执行commit之后,事务B才能继续执行。
在InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。
如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
假设你负责实现一个电影票在线交易业务,顾客A要在影院B购买电影票。我们简化一点,这个业务需要涉及到以下操作:
-
从顾客A账户余额中扣除电影票价;
-
给影院B的账户余额增加这张电影票价;
-
记录一条交易日志。
也就是说,要完成这个交易,我们需要update两条记录,并insert一条记录。当然,为了保证交易的原子性,我们要把这三个操作放在一个事务中。那么,你会怎样安排这三个语句在事务中的顺序呢?
试想如果同时有另外一个顾客C要在影院B买票,那么这两个事务冲突的部分就是语句2了。因为它们要更新同一个影院账户的余额,需要修改同一行数据。
根据两阶段锁协议,不论你怎样安排语句顺序,所有的操作需要的行锁都是在事务提交的时候才释放的。所以,如果你把语句2安排在最后,比如按照3、1、2这样的顺序,那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待,提升了并发度。
死锁和死锁检测
当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态,称为死锁。
这时候,事务A在等待事务B释放id=2的行锁,而事务B在等待事务A释放id=1的行锁。 事务A和事务B在互相等待对方的资源释放,就是进入了死锁状态。当出现死锁以后,有两种策略:
- 一种策略是,直接进入等待,直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout(默认50s)来设置。
- 在InnoDB中,innodb_lock_wait_timeout的默认值是50s,意味着如果采用第一个策略,当出现死锁以后,第一个被锁住的线程要过50s才会超时退出,然后其他线程才有可能继续执行。对于在线服务来说,这个等待时间往往是无法接受的。
- 但是,我们又不可能直接把这个时间设置成一个很小的值,比如1s。这样当出现死锁的时候,确实很快就可以解开,但如果不是死锁,而是简单的锁等待呢?所以,超时时间设置太短的话,会出现很多误伤。
- 另一种策略是,发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on(默认为on),表示开启这个逻辑。
所以,正常情况下我们还是要采用第二种策略,即:主动死锁检测,而且innodb_deadlock_detect的默认值本身就是on。主动死锁检测在发生死锁的时候,是能够快速发现并进行处理的,但是它也是有额外负担的。
每当一个事务被锁的时候,就要看看它所依赖的线程有没有被别人锁住,如此循环,最后判断是否出现了循环等待,也就是死锁。
每个新来的被堵住的线程,都要判断会不会由于自己的加入导致了死锁,这是一个时间复杂度是O(n)的操作。假设有1000个并发线程要同时更新同一行,那么死锁检测操作就是100万这个量级的。虽然最终检测的结果是没有死锁,但是这期间要消耗大量的CPU资源。因此,你就会看到CPU利用率很高,但是每秒却执行不了几个事务。
怎么解决由这种热点行更新导致的性能问题呢?
一种头痛医头的方法,就是如果你能确保这个业务一定不会出现死锁,可以临时把死锁检测关掉。但是这种操作本身带有一定的风险,因为业务设计的时候一般不会把死锁当做一个严重错误,毕竟出现死锁了,就回滚,然后通过业务重试一般就没问题了,这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时,这是业务有损的。
另一个思路是控制并发度。根据上面的分析,你会发现如果并发能够控制住,比如同一行同时最多只有10个线程在更新,那么死锁检测的成本很低,就不会出现这个问题。一个直接的想法就是,在客户端做并发控制。但是,你会很快发现这个方法不太可行,因为客户端很多。我见过一个应用,有600个客户端,这样即使每个客户端控制到只有5个并发线程,汇总到数据库服务端以后,峰值并发数也可能要达到3000。
因此,这个并发控制要做在数据库服务端。如果你有中间件,可以考虑在中间件实现;如果你的团队有能修改MySQL源码的人,也可以做在MySQL里面。基本思路就是,对于相同行的更新,在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。
考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例,可以考虑放在多条记录上,比如10个记录,影院的账户总额等于这10个记录的值的总和。这样每次要给影院账户加金额的时候,随机选其中一条记录来加。这样每次冲突概率变成原来的1/10,可以减少锁等待个数,也就减少了死锁检测的CPU消耗。
这个方案看上去是无损的,但其实这类方案需要根据业务逻辑做详细设计。如果账户余额可能会减少,比如退票逻辑,那么这时候就需要考虑当一部分行记录变成0的时候,代码要有特殊处理。
如果你要删除一个表里面的前10000行数据,有以下三种方法可以做到:
- 第一种,直接执行delete from T limit 10000;
- 第二种,在一个连接中循环执行20次 delete from T limit 500;
- 第三种,在20个连接中同时执行delete from T limit 500。
第一种方式(即:直接执行delete from T limit 10000)里面,单个语句占用时间长,锁的时间也比较长;而且大事务还会导致主从延迟。
第二周方式相对较好。
第三种方式(即:在20个连接中同时执行delete from T limit 500),会人为造成锁冲突。
如果可以加上特定条件,将这10000行天然分开,可以考虑第三种。是的,实际上在操作的时候我也建议你尽量拿到ID再删除。
事务下:一致性读,当前读
一个事务要更新一行,如果刚好有另外一个事务拥有这一行的行锁,它又不能这么超然了,会被锁住,进入等待状态。问题是,既然进入了等待状态,那么等到这个事务自己获取到行锁要更新数据的时候,它读到的值又是什么呢?
mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`k` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);
![](https://i-blog.csdnimg.cn/blog_migrate/ec367426f8953b3248b53c752d9329bb.png)
事务B查到的k的值是3,而事务A查到的k的值是1
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。
![](https://i-blog.csdnimg.cn/blog_migrate/7c0488c38ec64604fb12027bb46e235f.png)
图中虚线框里是同一行数据的4个版本,当前最新版本是V4,k的值是22,它是被transaction id 为25的事务更新的,因此它的row trx_id也是25。
实际上,图2中的三个虚线箭头,就是undo log(回滚日志);而V1、V2、V3并不是物理上真实存在的,而是每次需要的时候根据当前版本和undo log计算出来的。比如,需要V2的时候,就是通过V4依次执行U3、U2算出来。
InnoDB是怎么定义快照的
按照可重复读的定义,一个事务启动的时候,能够看到所有已经提交的事务结果。但是之后,这个事务执行期间,其他事务的更新对它不可见。
因此,一个事务只需要在启动的时候声明说,“以我启动的时刻为准,如果一个数据版本是在我启动之前生成的,就认;如果是我启动以后才生成的,我就不认,我必须要找到它的上一个版本”。
当然,如果“上一个版本”也不可见,那就得继续往前找。还有,如果是这个事务自己更新的数据,它自己还是要认的。
在实现上, InnoDB为每个事务构造了一个数组,用来保存这个事务启动瞬间,当前正在“活跃”的所有事务ID。“活跃”指的就是,启动了但还没提交。
数组里面事务ID的最小值记为低水位,当前系统里面已经创建过的事务ID的最大值加1记为高水位。
这个视图数组和高水位,就组成了当前事务的一致性视图(read-view)。
而数据版本的可见性规则,就是基于数据的row trx_id和这个一致性视图的对比结果得到的。
这个视图数组把所有的row trx_id 分成了几种不同的情况。
![](https://i-blog.csdnimg.cn/blog_migrate/7fe98a51649f005d565b203cb9eaf8e7.png)
一个数据版本的row trx_id,有以下几种可能:
-
如果落在绿色部分,表示这个版本是已提交的事务或者是当前事务自己生成的,这个数据是可见的;
-
如果落在红色部分,表示这个版本是由将来启动的事务生成的,是肯定不可见的;
-
如果落在黄色部分,那就包括两种情况
a. 若 row trx_id在数组中,表示这个版本是由还没提交的事务生成的,不可见;
b. 若 row trx_id不在数组中,表示这个版本是已经提交了的事务生成的,可见。
比如,对于图2中的数据来说,如果有一个事务,它的低水位是18,那么当它访问这一行数据时,就会从V4通过U3计算出V3,所以在它看来,这一行的值是11。
有了这个声明后,系统里面随后发生的更新,是不是就跟这个事务看到的内容无关了呢?因为之后的更新,生成的版本一定属于上面的2或者3(a)的情况,而对它来说,这些新的数据版本是不存在的,所以这个事务的快照,就是“静态”的了。
InnoDB利用了“所有数据都有多个版本”的这个特性,实现了“秒级创建快照”的能力。
接下来,我们继续看一下图1中的三个事务,分析下事务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查询逻辑有关的操作:
图4 事务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,3)的时候,判断出row trx_id=101,比高水位大,处于红色区域,不可见;
- 接着,找到上一个历史版本,一看row trx_id=102,比高水位大,处于红色区域,不可见;
- 再往前找,终于找到了(1,1),它的row trx_id=90,比低水位小,处于绿色区域,可见。
这样执行下来,虽然期间这一行数据被修改过,但是事务A不论在什么时候查询,看到这行数据的结果都是一致的,所以我们称之为一致性读。
一个数据版本,对于一个事务视图来说,除了自己的更新总是可见以外,有三种情况:
版本未提交,不可见;
版本已提交,但是是在视图创建后提交的,不可见;
版本已提交,而且是在视图创建前提交的,可见。
更新逻辑
更新数据都是先读后写的,而这个读,只能读当前的值,称为“当前读”(current read)。
因此,在更新的时候,当前读拿到的数据是(1,2),更新后生成了新版本的数据(1,3),这个新版本的row trx_id是101。
所以,在执行事务B查询语句的时候,一看自己的版本号是101,最新数据的版本号也是101,是自己的更新,可以直接使用,所以查询得到的k的值是3。
这里我们提到了一个概念,叫作当前读。其实,除了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’,会怎么样呢?
![](https://i-blog.csdnimg.cn/blog_migrate/aebbe8fe2d09d005478af5192a768f9c.png)
事务C’的不同是,更新后并没有马上提交,在它提交前,事务B的更新语句先发起了。前面说过了,虽然事务C’还没提交,但是(1,2)这个版本也已经生成了,并且是当前的最新版本。那么,事务B的更新语句会怎么处理呢?
这时候,我们在上一篇文章中提到的“两阶段锁协议”就要上场了。事务C’没提交,也就是说(1,2)这个版本上的写锁还没释放。而事务B是当前读,必须要读最新版本,而且必须加锁,因此就被锁住了,必须等到事务C’释放这个锁,才能继续它的当前读。
![](https://i-blog.csdnimg.cn/blog_migrate/2f5e0231cfe0b92debcf8a95259501e7.png)
事务的可重复读的能力是怎么实现的?
可重复读的核心就是一致性读(consistent read);而事务更新数据的时候,只能用当前读。如果当前的记录的行锁被其他事务占用的话,就需要进入锁等待。
而读提交的逻辑和可重复读的逻辑类似,它们最主要的区别是:
- 在可重复读隔离级别下,只需要在事务开始的时候创建一致性视图,之后事务里的其他查询都共用这个一致性视图;
- 在读提交隔离级别下,每一个语句执行前都会重新算出一个新的视图。
“start transaction with consistent snapshot; ”的意思是从这个语句开始,创建一个持续整个事务的一致性快照。所以,在读提交隔离级别下,这个用法就没意义了,等效于普通的start transaction。
为什么表结构不支持“可重复读”?这是因为表结构没有对应的行数据,也没有row trx_id,因此只能遵循当前读的逻辑。
用下面的表结构和初始化语句作为试验环境,事务隔离级别是可重复读。现在,我要把所有“字段c和id值相等的行”的c值清零,但是却发现了一个“诡异”的、改不掉的情况。请你构造出这种情况,并说明其原理。
mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, c) values(1,1),(2,2),(3,3),(4,4);
这样,session A看到的就是我截图的效果了。
其实,还有另外一种场景,同学们在留言区都还没有提到。
这个操作序列跑出来,session A看的内容也是能够复现我截图的效果的。这个session B’启动的事务比A要早。
用“活跃事务的判断”来分析session B’的更新为什么对session A不可见就是:在session A视图数组创建的瞬间,session B’是活跃的,属于“版本未提交,不可见”这种情况。
这个实际场景还挺常见——所谓的“乐观锁”。时常我们会基于version字段对row进行cas式的更新,类似update ...set ... where id = xxx and version = xxx。如果version被其他事务抢先更新,则在自己事务中更新失败,trx_id没有变成自身事务的id,同一个事务中再次select还是旧值,就会出现“明明值没变可就是更新不了”的“异象”(anomaly)。解决方案就是每次cas更新不管成功失败,结束当前事务。如果失败则重新起一个事务进行查询更新。
似乎只有MySQL是在一致性视图下采用这种宽松的update机制。也许是考虑易用性吧。其他数据库大多在内部实现cas,只是失败后下一步动作有区别。
普通索引和唯一索引
![](https://i-blog.csdnimg.cn/blog_migrate/4bfd5c89421209738212848d29558e41.png)
接下来,我们就从这两种索引对查询语句和更新语句的性能影响来进行分析。
查询过程
假设,执行查询的语句是 select id from T where k=5。这个查询语句在索引树上查找的过程,先是通过B+树从树根开始,按层搜索到叶子节点,也就是图中右下角的这个数据页,然后可以认为数据页内部通过二分法来定位记录。
- 对于普通索引来说,查找到满足条件的第一个记录(5,500)后,需要查找下一个记录,直到碰到第一个不满足k=5条件的记录。
- 对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索。
那么,这个不同带来的性能差距会有多少呢?答案是,微乎其微。
你知道的,InnoDB的数据是按数据页为单位来读写的。也就是说,当需要读一条记录的时候,并不是将这个记录本身从磁盘读出来,而是以页为单位,将其整体读入内存。在InnoDB中,每个数据页的大小默认是16KB。
因为引擎是按页读写的,所以说,当找到k=5的记录的时候,它所在的数据页就都在内存里了。那么,对于普通索引来说,要多做的那一次“查找和判断下一条记录”的操作,就只需要一次指针寻找和一次计算。
当然,如果k=5这个记录刚好是这个数据页的最后一个记录,那么要取下一个记录,必须读取下一个数据页,这个操作会稍微复杂一些。
但是,我们之前计算过,对于整型字段,一个数据页可以放近千个key,因此出现这种情况的概率会很低。所以,我们计算平均性能差异时,仍可以认为这个操作成本对于现在的CPU来说可以忽略不计。
更新过程(changebuffer)
为了说明普通索引和唯一索引对更新语句性能的影响这个问题,我需要先跟你介绍一下change buffer。
当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InooDB会将这些更新操作缓存在change buffer中,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
需要说明的是,虽然名字叫作change buffer,实际上它是可以持久化的数据。也就是说,change buffer在内存中有拷贝,也会被写入到磁盘上。
将change buffer中的操作应用到原数据页,得到最新结果的过程称为merge。除了访问这个数据页会触发merge外,系统有后台线程会定期merge。在数据库正常关闭(shutdown)的过程中,也会执行merge操作。
显然,如果能够将更新操作先记录在change buffer,减少读磁盘,语句的执行速度会得到明显的提升。而且,数据读入内存是需要占用buffer pool的,所以这种方式还能够避免占用内存,提高内存利用率。
什么条件下可以使用change buffer呢?
对于唯一索引来说,所有的更新操作都要先判断这个操作是否违反唯一性约束。比如,要插入(4,400)这个记录,就要先判断现在表中是否已经存在k=4的记录,而这必须要将数据页读入内存才能判断。如果都已经读入到内存了,那直接更新内存会更快,就没必要使用change buffer了。
因此,唯一索引的更新就不能使用change buffer,实际上也只有普通索引可以使用。
change buffer用的是buffer pool里的内存,因此不能无限增大。change buffer的大小,可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候,表示change buffer的大小最多只能占用buffer pool的50%。
如果要在这张表中插入一个新记录(4,400)的话,InnoDB的处理流程是怎样的。
第一种情况是,这个记录要更新的目标页在内存中。这时,InnoDB的处理流程如下:
- 对于唯一索引来说,找到3和5之间的位置,判断到没有冲突,插入这个值,语句执行结束;
- 对于普通索引来说,找到3和5之间的位置,插入这个值,语句执行结束。
这样看来,普通索引和唯一索引对更新语句性能影响的差别,只是一个判断,只会耗费微小的CPU时间。
第二种情况是,这个记录要更新的目标页不在内存中。这时,InnoDB的处理流程如下:
- 对于唯一索引来说,需要将数据页读入内存,判断到没有冲突,插入这个值,语句执行结束;
- 对于普通索引来说,则是将更新记录在change buffer,语句执行就结束了。
将数据从磁盘读入内存涉及随机IO的访问,是数据库里面成本最高的操作之一。change buffer因为减少了随机磁盘访问,所以对更新性能的提升是会很明显的。
负责的某个业务的库内存命中率突然从99%降低到了75%,整个系统处于阻塞状态,更新语句全部堵住。而探究其原因后,发现这个业务有大量插入数据的操作,而在前一天把其中的某个普通索引改成了唯一索引。
change buffer的使用场景
普通索引的所有场景,使用change buffer都可以起到加速作用吗?
因为merge的时候是真正进行数据更新的时刻,而change buffer的主要目的就是将记录的变更动作缓存下来,所以在一个数据页做merge之前,change buffer记录的变更越多(也就是这个页面上要更新的次数越多),收益就越大。
merge的执行流程是这样的:
从磁盘读入数据页到内存(老版本的数据页);
从change buffer里找出这个数据页的change buffer 记录(可能有多个),依次应用,得到新版数据页;
写redo log。这个redo log包含了数据的变更和change buffer的变更。
因此,对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
反过来,假设一个业务的更新模式是写入之后马上会做查询,那么即使满足了条件,将更新先记录在change buffer,但之后由于马上要访问这个数据页,会立即触发merge过程。这样随机访问IO的次数不会减少,反而增加了change buffer的维护代价。所以,对于这种业务模式来说,change buffer反而起到了副作用。
change buffer 和 redo log
MySQL的WAL(Write-Ahead Logging)机制 - 简书 (jianshu.com)
WAL(Write-Ahead Logging)提升性能的核心机制,也的确是尽量减少随机读写,这两个概念确实容易混淆。所以,这里我把它们放到了同一个流程里来说明,便于你区分这两个概念。
现在,我们要在表上执行这个插入语句:
mysql> insert into t(id,k) values(id1,k1),(id2,k2);
这里,我们假设当前k索引树的状态,查找到位置后,k1所在的数据页在内存(InnoDB buffer pool)中,k2所在的数据页不在内存中。如图2所示是带change buffer的更新状态图。
![](https://i-blog.csdnimg.cn/blog_migrate/1e8e60b4378921d1c35517edfc816d97.png)
分析这条更新语句,你会发现它涉及了四个部分:内存、redo log(ib_log_fileX)、 数据表空间(t.ibd)、系统表空间(ibdata1)。
这条更新语句做了如下的操作(按照图中的数字顺序):
-
Page 1在内存中,直接更新内存;
-
Page 2没有在内存中,就在内存的change buffer区域,记录下“我要往Page 2插入一行”这个信息
-
将上述两个动作记入redo log中(图中3和4)。
做完上面这些,事务就可以完成了。所以,你会看到,执行这条更新语句的成本很低,就是写了两处内存,然后写了一处磁盘(两次操作合在一起写了一次磁盘),而且还是顺序写的。
同时,图中的两个虚线箭头,是后台操作,不影响更新的响应时间。
那在这之后的读请求,要怎么处理呢?
比如,我们现在要执行 select * from t where k in (k1, k2)。这里,我画了这两个读请求的流程图。
如果读语句发生在更新语句后不久,内存中的数据都还在,那么此时的这两个读操作就与系统表空间(ibdata1)和 redo log(ib_log_fileX)无关了。所以,在图中就没画出这两部分。
![](https://i-blog.csdnimg.cn/blog_migrate/c6188681b5c921e86cd741a48141ca95.png)
从图中可以看到:
-
读Page 1的时候,直接从内存返回。WAL之后如果读数据,是不是一定要读盘,是不是一定要从redo log里面把数据更新以后才可以返回?其实是不用的。你可以看一下图3的这个状态,虽然磁盘上还是之前的数据,但是这里直接从内存返回结果,结果是正确的。
-
读Page 2的时候,需要把Page 2从磁盘读入内存中,然后应用change buffer里面的操作日志,生成一个正确的版本并返回结果。
可以看到,直到需要读Page 2的时候,这个数据页才会被读入内存。
所以,如果要简单地对比这两个机制在提升更新性能上的收益的话,redo log 主要节省的是随机写磁盘的IO消耗(转成顺序写),而change buffer主要节省的则是随机读磁盘的IO消耗。
change buffer一开始是写内存的,那么如果这个时候机器掉电重启,会不会导致change buffer丢失呢?change buffer丢失可不是小事儿,再从磁盘读入数据可就没有了merge过程,就等于是数据丢失了。会不会出现这种情况呢?
1.change buffer有一部分在内存有一部分在ibdata.
做purge操作,应该就会把change buffer里相应的数据持久化到ibdata
2.redo log里记录了数据页的修改以及change buffer新写入的信息
如果掉电,持久化的change buffer数据已经purge,不用恢复。主要分析没有持久化的数据
情况又分为以下几种:
(1)change buffer写入,redo log虽然做了fsync但未commit,binlog未fsync到磁盘,这部分数据丢失
(2)change buffer写入,redo log写入但没有commit,binlog以及fsync到磁盘,先从binlog恢复redo log,再从redo log恢复change buffer
(3)change buffer写入,redo log和binlog都已经fsync.那么直接从redo log里恢复。
MySQL为什么有时候会选错索引
先建一个简单的表,表里有a、b两个字段,并分别建上索引:
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;
然后,我们往表t中插入10万行记录,取值按整数递增,即:(1,1,1),(2,2,2),(3,3,3) 直到(100000,100000,100000)。
用存储过程来插入数据的:
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
分析一条SQL语句:
mysql> select * from t where a between 10000 and 20000;
操作2:
这里,session A开启了一个事务。随后,session B把数据都删除后,又调用了 idata这个存储过程,插入了10万行数据。
这时候,session B的查询语句select * from t where a between 10000 and 20000就不会再选择索引a了。我们可以通过慢查询日志(slow log)来查看一下具体的执行情况。
为了说明优化器选择的结果是否正确,我增加了一个对照,即:使用force index(a)来让优化器强制使用索引a(这部分内容,我还会在这篇文章的后半部分中提到)。
下面的三条SQL语句,就是这个实验过程。
set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/
- 第一句,是将慢查询日志的阈值设置为0,表示这个线程接下来的语句都会被记录入慢查询日志中;
- 第二句,Q1是session B原来的查询;
- 第三句,Q2是加了force index(a)来和session B原来的查询语句执行情况对比。
如图3所示是这三条SQL语句执行完成后的慢查询日志。
图3 slow log结果
可以看到,Q1扫描了10万行,显然是走了全表扫描,执行时间是40毫秒。Q2扫描了10001行,执行了21毫秒。也就是说,我们在没有使用force index的时候,MySQL用错了索引,导致了更长的执行时间。
这个例子对应的是我们平常不断地删除历史数据和新增数据的场景。这时,MySQL竟然会选错索引,是不是有点奇怪呢?今天,我们就从这个奇怪的结果说起吧。
优化器的逻辑
优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的CPU资源越少。
当然,扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
扫描行数是怎么判断的?
MySQL在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条,而只能根据统计信息来估算记录数。
这个统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”(cardinality)。也就是说,这个基数越大,索引的区分度越好。
使用show index方法,看到一个索引的基数,虽然这个表的每一行的三个字段值都是一样的,但是这三个索引的基数值并不同,而且其实都不准确。
MySQL是怎样得到索引的基数的呢
为什么要采样统计呢?因为把整张表取出来一行行统计,虽然可以得到精确的结果,但是代价太高了,所以只能选择“采样统计”。
采样统计的时候,InnoDB默认会选择N个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。
而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过1/M的时候,会自动触发重新做一次索引统计。
在MySQL中,有两种存储索引统计的方式,可以通过设置参数innodb_stats_persistent的值来选择:
- 设置为on的时候,表示统计信息会持久化存储。这时,默认的N是20,M是10。
- 设置为off的时候,表示统计信息只存储在内存中。这时,默认的N是8,M是16。
由于是采样统计,所以不管N是20还是8,这个基数都是很容易不准的。
其实索引统计只是一个输入,对于一个具体的语句来说,优化器还要判断,执行这个语句本身要扫描多少行。
接下来,我们再一起看看优化器预估的,这两个语句的扫描行数是多少。
rows这个字段表示的是预计扫描行数。
其中,Q1的结果还是符合预期的,rows的值是104620;但是Q2的rows值是37116,偏差就大了。而图1中我们用explain命令看到的rows是只有10001行,是这个偏差误导了优化器的判断。
到这里,可能你的第一个疑问不是为什么不准,而是优化器为什么放着扫描37000行的执行计划不用,却选择了扫描行数是100000的执行计划呢?
这是因为,如果使用索引a,每次从索引a上拿到一个值,都要回到主键索引上查出整行数据,这个代价优化器也要算进去的。
而如果选择扫描10万行,是直接在主键索引上扫描的,没有额外的代价。
优化器会估算这两个选择的代价,从结果看来,优化器认为直接扫描主键索引更快。当然,从执行时间看来,这个选择并不是最优的。
使用普通索引需要把回表的代价算进去,在图1执行explain的时候,也考虑了这个策略的代价 ,但图1的选择是对的。也就是说,这个策略并没有问题。
原因还是没能准确地判断出扫描行数
analyze table t 命令,可以用来重新统计索引信息。
如果你发现explain的结果预估的rows值跟实际情况差距比较大,可以采用这个方法来处理。
其实,如果只是索引统计不准确,通过analyze命令可以解决很多问题,但是前面我们说了,优化器可不止是看扫描行数。
依然是基于这个表t,我们看看另外一个语句:
mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
从条件上看,这个查询没有符合条件的记录,因此会返回空集合。
在开始执行这条语句之前,你可以先设想一下,如果你来选择索引,会选择哪一个呢?
为了便于分析,我们先来看一下a、b这两个索引的结构图。
图7 a、b索引的结构图
如果使用索引a进行查询,那么就是扫描索引a的前1000个值,然后取到对应的id,再到主键索引上去查出每一行,然后根据字段b来过滤。显然这样需要扫描1000行。
如果使用索引b进行查询,那么就是扫描索引b的最后50001个值,与上面的执行过程相同,也是需要回到主键索引上取值再判断,所以需要扫描50001行。
所以你一定会想,如果使用索引a的话,执行速度明显会快很多。那么,下面我们就来看看到底是不是这么一回事儿。
图8是执行explain的结果。
mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
图8 使用explain方法查看执行计划 2
可以看到,返回结果中key字段显示,这次优化器选择了索引b,而rows字段显示需要扫描的行数是50198。
从这个结果中,你可以得到两个结论:
-
扫描行数的估计值依然不准确;
-
这个例子里MySQL又选错了索引。
索引选择异常和处理
一种方法是,像我们第一个例子一样,采用force index强行选择一个索引。MySQL会根据词法解析的结果分析出可能可以使用的索引作为候选项,然后在候选列表中依次判断每个索引需要扫描多少行。如果force index指定的索引在候选索引列表中,就直接选择这个索引,不再评估其他索引的执行代价。
- 一来这么写不优美,二来如果索引改了名字,这个语句也得改,显得很麻烦。而且如果以后迁移到别的数据库的话,这个语法还可能会不兼容。
第二种方法就是,我们可以考虑修改语句,引导MySQL使用我们期望的索引。比如,在这个例子里,显然把“order by b limit 1” 改成 “order by b,a limit 1” ,语义的逻辑是相同的。
我们来看看改之后的效果:
图10 order by b,a limit 1 执行结果
之前优化器选择使用索引b,是因为它认为使用索引b可以避免排序(b本身是索引,已经是有序的了,如果选择索引b的话,不需要再做排序,只需要遍历),所以即使扫描行数多,也判定为代价更小。
现在order by b,a 这种写法,要求按照b,a排序,就意味着使用这两个索引都需要排序。因此,扫描行数成了影响决策的主要条件,于是此时优化器选了只需要扫描1000行的索引a。
当然,这种修改并不是通用的优化手段,只是刚好在这个语句里面有limit 1,因此如果有满足条件的记录, order by b limit 1和order by b,a limit 1 都会返回b是最小的那一行,逻辑上一致,才可以这么做。
如果你觉得修改语义这件事儿不太好,这里还有一种改法,图11是执行效果。
mysql> select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;
在这个例子里,我们用limit 100让优化器意识到,使用b索引代价是很高的。其实是我们根据数据特征诱导了一下优化器,也不具备通用性。
第三种方法是,在有些场景下,我们可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引。
通过session A的配合,让session B删除数据后又重新插入了一遍数据,然后就发现explain结果中,rows字段从10001变成37000多。
而如果没有session A的配合,只是单独执行delete from t 、call idata()、explain这三句话,会看到rows字段其实还是10000左右。你可以自己验证一下这个结果。
1.为什么没有session A,session B扫描的行数是1W
由于mysql是使用标记删除来删除记录的,并不从索引和数据文件中真正的删除。
如果delete和insert中间的间隔相对较小,purge线程还没有来得及清理该记录。
如果主键相同的情况下,新插入的insert会沿用之前删除的delete的记录的空间。
由于相同的数据量以及表大小,所以导致了统计信息没有变化
2.为什么开启了session A,session B扫描行数变成3W
由于session A开启了一致性读,目的为了保证session A的可重复读,insert只能
另起炉灶,不能占用delete的空间。所以出现的情况就是delete虽然删除了,但是
未释放空间,insert又增加了空间。导致统计信息有误(a索引上的数据有两份,主键是直接按照表的行数来估计的。而表的行数,优化器直接用的是show table status的值。)