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);

事务C没有显式地使用begin/commit，表示这个update语句本身就是一个事务，语句完成的时候会自动提交。

可重复读隔离级别下，事务在启动的时候就“拍了个整个库的快照”。如果一个库有100G，那么我启动一个事务，MySQL就要拷100G的数据出来，这个过程得多慢啊。但是平时事务执行起来却是非常快的。不是全部拷贝出来那是怎么实现的呢?

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(回滚日志)，图中的三个虚线箭头，就是undo log。

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

一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认;如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。

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

在实现上， InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。“活跃”指的就 是，启动了但还没提交。数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。 这个视图数组和高水位，就组成了当前事务的一致性视图(read-view)。而数据版本的可性规则，就是基于数据的row trx_id和这个一致性视图的对比结果得到的。

InnoDB利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

回到我们最开始的表格，看看最后执行的结果是多少。做如下假设:

事务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又成为了历史版本。

事务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。

select语句如果加锁，也是当前读。

如果把事务A的查询语句select * from t where id=1修改一下，加上lock in share mode 或 for update，也都可以读到版本号是101的数据，返回的k的值是3。下面这两个select语句，就是分别加了读锁(S锁，共享锁)和写锁(X锁，排他锁)。

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

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

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

为什么主键索引不能过长？

身份证是唯一的那么是不是就可以把我们知道InnoDB使用的聚簇索引id和数据一起存，在通过二级索引（普通索引）查询数据时是先找到主键索引再查询数据，如果数据量比较大，主键id过长就会导致一个二级索引(普通非主键索引)树所能存储的主键id记录就会变少，因为索引缓冲区的内存空间是有限的。

主键索引为什么最好是自增的？

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个「紧凑」的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。Mysql 为维护索引可能需要频繁的刷新缓冲，增加了方法磁盘 IO 的次数，而且时常需要对索引结构进行重组织。

应该设置唯一索引还是普通索引？

唯一索引和普通索引有什么区别？

先看看查询操作两者的区别。

对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。

对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

上面的查询区别对于性能而言是微乎其微的，InnoDB读写数据是按页来的，也是说当找到k=5的记录的时候，它所在的数据就都在内存里了，那么我们只需要判断下一条数据是不是等于5即可，直到出现不为5的值为止，这些都是在内存中操作的，所以性能微乎其微。

再来看看更新插入操作。想了解更新操作对性能的影响先了解了解change buffer。

当需要更新一个数据时，如果数据在内存中就直接更新，而如果这个数据还没有在内存中的话，在不影响数据一致性的 前提下，InooDB会将这些更新操作缓存在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%。

再了解了change buffer之后我们再看看一条插入流程是怎么样的。

这个记录要更新的目标在内存中。这时，InnoDB的处理流程如下:

普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的CPU时间。可以认为性能相同。

对于唯一索引来说，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束;

对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束。

这个记录要更新的目标不在内存中。这时，InnoDB的处理流程如下:

对于唯一索引来说，需要将数据读入内存，判断到没有冲突，插入这个值，语句执行结束;

对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。change buffer因为减少了随机磁盘访问，所 以对更新性能的提升是会很明显的。

change buffer的使用场景

change buffer只限于用在普通索引的场 景下，而不适用于唯一索引。普通索引的所有场景，使用change buffer都可以起到加速作用吗?

因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据做merge之前，change buffer记录的变更越多(也就是这个面上要更新的次数越多)，收益就越大。

写多读少的业务来说，面在写完以后上被访问到的概率比较小，此时change buffer的使用效果最好。这种业 务模型常的就是账单类、日志类的系统。

假设一个业务的更新模式是写入之后上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后 由于上要访问这个数据，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代 价。所以，对于这种业务模式来说，change buffer反而起到了副作用。

change buffer 和 redo log

假设我们要执行一个插入操作，insert into t(id,k) values(id1,k1),(id2,k2);我们假设当前k索引树的状态，查找到位置后，k1所在的数据在内存(InnoDB buffer pool)中，k2所在的数据不在内 存中。如图所示是带change buffer的更新状态图。

分析这条更新语句，你会发现它涉及了四个部分:内存、redo log(ib_log_fileX)、 数据表空间(t.ibd)、系统表空间(ibdata1)。

这条更新语句做了如下的操作(按照图中的数字顺序):

Page 1在内存中，直接更新内存;

Page 2没有在内存中，就在内存的change buffer区域，记录下“我要往Page 2插入一行”这个信息 3. 将上述两个动作记入redo log中(图中3和4)。

这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘 (两次操作合在一起写了一次磁盘)，而且还是顺序写的。

我们现在要执行 select * from t where k in (k1, k2)。这里，我画了这两个读请求的流程图。如果读语句发生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与系统表空间(ibdata1)和 redo log(ib_log_fileX)无关了。所以，我在图中就没画出这两部分。

读Page 1的时候，直接从内存返回。WAL之后如果读数据，是不是一定要读盘， 是不是一定要从redo log里面把数据更新以后才可以返回?其实是不用的。你可以看一下图3的这个状态，虽然磁盘上还是 之前的数据，但是这里直接从内存返回结果，结果是正确的。

要读Page 2的时候，需要把Page 2从磁盘读入内存中，然后应用change buffer里面的操作日志，生成一个正确的版本并返回结果。

如果要简单地对比这两个机制在提升更新性能上的收益的话，redo log 主要节省的是随机写磁盘的IO消耗(转成顺序 写)，而change buffer主要节省的则是随机读磁盘的IO消耗。