Mysql性能调优(二)

事务

ACID特性

原子性（Atomicity）：当前事务操作要么同时成功，要么同时失败。原子性由undo log日志来实现

一致性（Consistency）：使用事务的最终目的，由其它3个特性以及业务代码正确逻辑来实现

隔离性（Isolation）：事务并发执行时，他们内部的操作不能互相干扰，隔离性由MySQL的各种锁以及MVCC机制来实现

持久性（Durability）：一旦提交了事务，它对数据库的改变就应该是永久性的。持久性由redo log日志来实现

事务隔离性

InnoDB引擎中，定义了四种隔离级别供我们使用，级别越高事务隔离性越好，但性能就越低，而隔离性是由MySQL的各种锁以及MVCC机制来实现的

read uncommit (读未提交) ：脏读

read commit (读已提交)：不可重复读

repeatable read (可重复读) ：幻读

serializable (串行)：解决上面所有问题，包括脏写

MVCC

MVCC(Multi-Version Concurrency Control)多版本并发控制，就可以做到读写不阻塞，且避免了类似脏读这样的问题，主要通过undo日志链来实现

select操作是快照读（历史版本）

insert、update和delete是当前读（当前版本）

read commit (读已提交) ，语句级快照

repeatable read (可重复读)，事务级快照

事务持久性

MySQL引入了redo log，Buffer Pool内存写完了，然后会写一份redo log，这份redo log记载着这次在某个页上做了什么修改。 即便MySQL在中途挂了，我们还可以根据redo log来对数据进行恢复。 redo log 是顺序写的，写入速度很快。并且它记录的是物理修改（xxxx页做了xxx修改），文件的体积很小，恢复速度也很快。

案例分析

 CREATE TABLE `account` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) DEFAULT NULL,
 `balance` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lilei', '450');
 INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('hanmei', '16000');
 INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lucy', '2400')

读未提交

（1）打开一个客户端A，并设置当前事务模式为read uncommitted（未提交读），查询表

account的初始值：

set tx_isolation = 'read‐uncommitted' ;

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：

（3）这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据：

（4）一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端A查询到

的数据其实就是 脏数据 ：

（5）在客户端A执行更新语句update account set balance = balance - 50 where id

=1，lilei的balance没有变成350，居然是400，是不是很奇怪，数据不一致啊， 如果你这么 想就太天真了，在应用程序中，我们会用400-50=350，并不知道其他会话回滚了，要想解

决这个问题可以采用读已提交的隔离级别

读已提交 

（1）打开一个客户端A，并设置当前事务模式为read committed（未提交读），查询表

account的所有记录：

set tx_isolation = 'read‐committed' ;

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：

（3）这时，客户端B的事务还没提交，客户端A不能查询到B已经更新的数据， 解决了脏读问

题 ：

（4）客户端B的事务提交

（5）客户端A执行与上一步相同的查询，结果 与上一步不一致，即产生了不可重复读的问题

可重复读

（1）打开一个客户端A，并设置当前事务模式为repeatable read，查询表account的所有

记录

  set tx_isolation = 'repeatable‐read' ;

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account并提交

（3）在客户端A查询表account的所有记录，与步骤（1）查询结果一致，没有出现不可重

复读的问题

（4）在客户端A，接着执行update account set balance = balance - 50 where id = 1，

balance没有变成400-50=350，lilei的balance值用的是步骤2中的350来算的，所以是

300，数据的一致性倒是没有被破坏。可重复读的隔离级别下使用了MVCC(multi-version

concurrency control)机制，select操作是 快照读（历史版本） ；insert、update和delete

是 当前读（当前版本）

（5）重新打开客户端B，插入一条新数据后提交

（6）在客户端A查询表account的所有记录，没有查出新增数据，所以没有出现幻读

（7)验证幻读

在客户端A执行update account set balance=888 where id = 4；能更新成功，再次查询

能查到客户端B新增的数据

串行化

（1）打开一个客户端A，并设置当前事务模式为serializable，查询表account的初始值：

set tx_isolation = 'serializable' ;

（2）打开一个客户端B，并设置当前事务模式为serializable，更新相同的id为1的记录会被

阻塞等待，更新id为2的记录可以成功，说明在串行模式下innodb的查询也会被加上行锁，

如果查询的记录不存在会给这条不存在的记录加上锁(这种是间隙锁，后面会详细讲)。

如果客户端A执行的是一个范围查询，那么该范围内的所有行包括每行记录所在的间隙区间

范围都会被加锁。此时如果客户端B在该范围内插入数据都会被阻塞，所以就避免了幻读。

这种隔离级别并发性极低，开发中很少会用。

事务问题定位

 #查询执行时间超过1秒的事务
 SELECT
 *
 FROM
 information_schema.innodb_trx
 WHERE
 TIME_TO_SEC( timediff( now( ), trx_started ) ) > 1;

 #强制结束事务
 kill 事务对应的线程id(就是上面语句查出结果里的trx_mysql_thread_id字段的值)

锁

读锁（共享锁、S锁）：select ...  lock in share mode;     读锁是共享的，多个事务可以同时读取同一个资源，但不允许其他事务修改

写锁（排它锁、X锁）：select ...  for update；     写锁是排他的，会阻塞其他的写锁和读锁，update、delete、insert都会加写锁

意向锁（Intention Lock）：又称 I锁 ，针对 表锁，主要是为了提高加表锁的效率，是mysql数据库自己 加的。当有事务给表的数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了，其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。特别是表中的记录很多时，逐行判断加表锁的方式效率很低。而这个标识就是意向锁。

意向锁主要分为：

意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。

意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁。

表锁

每次操作锁住整张表。 开销小，加锁快 ；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。

‐‐建表SQL
CREATE TABLE `mylock` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR (20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;
‐‐插入数据
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a');
 INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b');
 INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c');
 INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd');
‐‐手动增加表锁
lock table 表名称 read(write),表名称2 read(write);
‐‐查看表上加过的锁
show open tables;
‐‐删除表锁
unlock tables;

页锁

只有 BDB存储引擎支持页锁 ，页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中

可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的 数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

行锁

每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB相对于MYISAM的最大不同有两点：

InnoDB支持事务（TRANSACTION）

InnoDB支持行级锁

注意，InnoDB的行锁实际上是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁。(RR级别会升级为表锁，RC级别不会升级为表锁) 比如我们在RR级别执行如下sql

select * from account where name = 'lilei' for update ; ‐‐ where 条件里的 name 字段无索引

则其它Session对该表任意一行记录做修改操作都会被阻塞住。

PS：关于RR级别行锁升级为表锁的原因分析

因为在RR隔离级别下，需要解决不可重复读和幻读问题，所以在遍历扫描聚集索引记录时，为了防止扫描过

的索引被其它事务修改(不可重复读问题) 或 间隙被其它事务插入记录(幻读问题)，从而导致数据不一致，所

以MySQL的解决方案就是把所有扫描过的索引记录和间隙都锁上，这里要注意，并不是直接将整张表加表

锁，因为不一定能加上表锁，可能会有其它事务锁住了表里的其它行记录。

间隙锁(Gap Lock)

间隙锁，锁的就是两个值之间的空隙，间隙锁是在可重复读隔离级别下才会生效。

上节课讲过，Mysql默认级别是repeatable-read，有幻读问题，间隙锁是可以解决幻读问题的。

假设account表里数据如下：

那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间，在Session_1下面执行如下sql：

select * from account where id = 18 for update ;

则其他Session没法在这个(10,20)这个间隙范围里插入任何数据。

如果执行下面这条sql：

select * from account where id = 25 for update ;

则其他Session没法在这个(20,正无穷)这个间隙范围里插入任何数据。 也就是说，只要在间隙范围内锁了一条不存在的记录会锁住整个间隙范围，不锁边界记录，这样就能防止其它Session在这个间隙范围内插入数据，就解决了可重复读隔离级别的幻读问题。

总结：

MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行update、insert、delete操作会 自动给涉及的表加写锁。

InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。 另外，读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。 Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更

高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb 的整体性能和MYISAM相比就会有比较明显的优势了。 但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MYISAM高，甚至可能会更差。

锁等待分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划

查看INFORMATION_SCHEMA系统库锁相关数据表

 ‐‐ 查看事务
 select * from INFORMATION_SCHEMA.INNODB_TRX;
 ‐‐ 查看锁，8.0之后需要换成这张表performance_schema.data_locks
 select * from INFORMATION_SCHEMA.INNODB_LOCKS;
 ‐‐ 查看锁等待，8.0之后需要换成这张表performance_schema.data_lock_waits
 select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

 ‐‐ 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
 kill trx_mysql_thread_id

 ‐‐ 查看锁等待详细信息
 show engine innodb status;

死锁问题分析

 set tx_isolation='repeatable‐read';
 Session_1执行：select * from account where id=1 for update;
 Session_2执行：select * from account where id=2 for update;
 Session_1执行：select * from account where id=2 for update;
 Session_2执行：select * from account where id=1 for update;
 查看近期死锁日志信息：show engine innodb status;

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁， 这种情况我们可以通过日志分析找到对应事务线程id，可以通过kill杀掉。

锁优化实践

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

合理设计索引，尽量缩小锁的范围

尽可能减少检索条件范围，避免间隙锁

尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

尽可能用低的事务隔离级别

MVCC多版本并发控制机制

Mysql在可重复读隔离级别下如何保证事务较高的隔离性，我们上节课给大家演示过，同样的sql查询语句在一个事务里多次

执行查询结果相同，就算其它事务对数据有修改也不会影响当前事务sql语句的查询结果。

这个隔离性就是靠MVCC( Multi-Version Concurrency Control )机制来保证的，对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥来实现的。

Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制。

undo日志版本链与read view机制详解

undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日

志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链

  在可重复读隔离级别 ，当事务开启，执行任何查询sql时会生成当前事务的 一致性视图read-view， 该 视图在事务结束之前永远都不会变化 ( 如果是读已提交隔离级别在每次执行查询sql时都会重新生成read-view)，这个视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。

版本链比对规则：

1. 如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；

2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的

trx_id 就是当前自己的事务是可见的）；

3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况

a. 若 row 的 trx_id 在视图数组 中，表示这个版本是由还没提交的事务生成的， 不可见(若 row 的 trx_id 就是当前自己的

事务是可见的)；

b. 若 row 的 trx_id 不在视图数组 中，表示这个版本是已经提交了的事务生成的， 可见。  

对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。

总结：

MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。