Mysql 普通索引、唯一索引，底层区别

1 概念区分

---

普通索引 V.S 唯一索引

普通索引可重复，唯一索引和主键一样不能重复。

唯一索引可作为数据的一个合法验证手段，例如学生表的身份证号码字段，人为规定该字段不得重复，那么就使用唯一索引。（一般设置学号字段为主键）

主键 V.S 唯一索引

主键保证DB的每一行都是唯一、不重复，比如身份证，学号等，不重复。

唯一索引的作用跟主键一样。

但在一张表里面只能有一个主键，不能为空，唯一索引可有多个。唯一索引可有一条记录为null。

比如学生表：

• 在学校，一般用学号做主键，身份证号作为唯一索引

• 在教育局，就把身份证号弄成主键，学号作为唯一索引

所以选谁做主键，取决于业务需求。

2 案例

---

某居民系统，每人有唯一身份证号。

若系统要按身份证号查姓名：

select name from CUser where id_card = 'ooxx';

id_card字段较大，不推荐做主键。

现有如下选择：

1. 在id_card创建唯一索引

2. 创建一个普通索引

假定业务代码已确保不会写入重复身份证号，这两个选择逻辑上都正确。

但性能角度考虑，选择哪个呢？

假设字段 k 上的值都不重复。

InnoDB索引结构：

3 查询性能

---

select id from T where k=4

通过B+树从root开始层序遍历到叶节点，数据页内部通过二分搜索：

• 普通索引 查找到满足条件的第一个记录(4,400)后，继续查找下个记录，直到碰到第一个不满足k=4的记录

• 唯一索引 查到第一个满足条件的，就停止搜索

看起来性能差距很小。

InnoDB数据按数据页单位读写。

即读一条记录时，并非将该一个记录从磁盘读出，而以页为单位，将其整体读入内存。

所以普通索引，多了一次“查找和判断下一条记录”的操作，即一次指针寻找和一次计算。

若k=4记录恰为该数据页的最后一个记录，则此时要取下个记录，还得读取下个数据页。

对整型字段，一个数据页可存近千个key，因此这种情况概率其实也很低。

因此计算平均性能差异时，可认为该操作成本对CPU开销忽略不计。

4 更新性能

---

往表中插入一个新记录(4,400)，InnoDB会有什么反应？

这要看该记录要更新的目标页是否在内存：

在内存

• 普通索引 找到3和5之间的位置，插入值，结束。

• 唯一索引 找到3和5之间的位置，判断到没有冲突，插入值，结束。

只是一个判断的差别，耗费微小CPU时间。

不在内存

• 唯一索引 将数据页读入内存，判断到没有冲突，插入值，结束。

• 普通索引 将更新记录在change buffer，结束。

将数据从磁盘读入内存涉及随机I/O访问，是DB里成本最高的操作之一。

而change buffer可以减少随机磁盘访问，所以更新性能提升明显。

5 索引选择最佳实践

---

普通索引、唯一索引在查询性能上无差别，主要考虑更新性能。

所以，推荐尽量选择普通索引。

若所有更新后面，都紧跟对该记录的查询，就该关闭change buffer。

其它情况下，change buffer都能提升更新性能。

普通索引和change buffer的配合使用，对数据量大的表的更新优化还是明显的。

在使用机械硬盘时，change buffer收益也很大。

所以，当你有“历史数据”库，且出于成本考虑用机械硬盘，应该关注这些表里的索引，尽量用普通索引，把change buffer开大，确保“历史数据”表的数据写性能。

6 普通索引带change buffer的读过程和写过程

---

6.1 插入流程

insert into t(id,k)values (id1,k1),(id2,k2);

假设当前k索引树的状态，查找到位置后：

• k1所在数据页在内存(buffer pool)

• k2数据页不在内存

看如下流程：

带change buffer的更新流程

图中箭头都是后台操作，不影响更新请求的响应。

该更新做了如下操作：

1. Page1在内存，直接更新内存

2. Page2不在内存，就往change buffer区，缓存一个“往Page2插一行记录”的信息

3. 将前两个动作记入redo log

至此，事务完成。执行该更新语句成本很低，只是写两处内存，然后写一处磁盘（前两次操作合在一起写了一次磁盘），还是顺序写。

6.2 处理之后的读请求

select * from t where k  in (k1, k2);

读语句紧随更新语句之后，这时内存中的数据都还在，所以此时这俩读操作就与系统表空间和 redo log 无关。

带change buffer的读过程

读Page1时，直接从内存返回。

WAL之后若读数据，是否一定要读盘？一定要从redo log将数据更新后才能返回？其实不用。看上图状态，虽然磁盘上还是之前的数据，但这里直接从内存返回结果，结果是正确的。

读Page2时，需将Page2从磁盘读入内存，然后应用change buffer里的操作日志，生成一个正确版本并返回结果。所以一直到需要读Page2时，该数据页才会被从磁盘读入内存。

综上，这俩机制的更新性能：

• redo log 主要节省随机写磁盘的I/O消耗（转成顺序写）

• change buffer主要节省随机读磁盘的I/O消耗

7 change buffer 和 redo log 底层原理

---

更新流程中涉及到：重做日志（redo log）和归档日志(bin log)

重做日志（redo log）是在引擎层中，采用“黑板-账本”模式（即WAL技术）。

为什么需要redo log？

每一次的更新操作的具体数据变更，都是需要写入到磁盘中去的，因为写入的是一条确定的数据，

所以我们还需要在磁盘中找到那条相对应的记录，然后才能完成更新。

而对于磁盘的IO操作，众所周知是最消耗的操作，因此为了解决这个问题，我们使用redo log。

redo log中记录的是要更新的数据，

比如一条数据已提交成功，并不会立即同步到磁盘，而是先记录到redo log中，等待合适的时机再刷盘，为了实现事务的持久性。

如果没有redo log, 那么每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。

但是这么做会有严重的性能问题，主要体现在两个方面：

因为 Innodb 是以 页 为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！

一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

因此 Mysql 设计了 redo log ， 具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

redo log的具体过程

在redo log中采用是“黑板”+“账本”的过程。

通俗的想法是，一旦由需要写入的内容时，我们先写在黑板上，当黑板上写不下的时候或者有空闲的时候，再将黑板上的内容都拷贝到账本中。

• 黑板，在Mysql中对应日志（redo log）;

• 账本，对应Mysql中的磁盘。

举例-----------当有一条记录需要更新，InnoDB引擎会先把记录写到redo log（黑板）中，并更新内存。

到这一步，该引擎认为这个更新步骤已经完成了。

当InnoDB遇到了系统空闲的时候，才会将这个操作记录更新到磁盘中去。

update图解执行过程

bin log

归档日志(bin log)-------在server层，记录MySQL功能层面的事情

redo log跟bin log的区别：

redo log是存储引擎层产生的，而bin log是数据库层产生的。

假设一个事务，对表做了10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录，而bin log不会记录，直到这个事务提交，才会一次写入bin log文件中。

两阶段提交

从流程图中，我们可以看到，

redo log的完成过程是被分成了两个阶段的，分别是：prepare阶段和commit状态。

这就是所谓的两阶段提交。

目的：为了保证数据库的目前状态和它通过日志恢复出来的库的状态是一致的。

redo log 的好处

相较于在事务提交时将所有修改过的页刷新到磁盘中，只将该事务执行过程中产生的redo日志刷到磁盘，有下面的好处：

(1) redo日志降低了刷盘频率。

(2) redo日志占用的空间非常小。

(3) redo日志是顺序写入磁盘的。

在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志是按照产生的顺序写入磁盘的，也就是顺序IO。

InnoDB存储引擎的事务采用了WAL技术（Write-Ahead Logging）,这种技术就是先写日志，再写磁盘，只有日志写入成功，才算事务提交成功，这里的日志就是redo log。

当发生宕机且数据未刷新到磁盘的时候，可以通过redo log恢复过来，保证事务的持久性。

事务的原子性、一致性和持久性由事务的 redo 日志和 undo 日志来保证。

redo log称为重做日志 ，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。

undo log称为回滚日志 ，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。

redo log是存储引擎层生成的日志，记录的是物理级别上的页修改操作，比如页号xxx，偏移量yyy，写入了zzz数据，主要是为了保证数据的可靠性。

undo log是存储引擎层生成的日志，记录的是逻辑操作的日志，比如对某一行数据进行了insert语句操作，那么undo log就记录一条与之相反的delete操作。主要用于事务的回滚和一致性非锁定读(mvcc)。

什么是 Buffer Pool

InnoDB存储引擎在处理客户端的请求时，如果需要访问某个页的数据，就会把完整的页中的数据全部加载到内存中，即使只访问页中的一条记录，也需要先把整个页的数据加载到内存中。

将整个页加载到内存后就可以进行读写访问了，而且在读写访问之后并不着急把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以剩下磁盘IO的开销了。

为了缓存磁盘中的页，Innodb在MySQL服务器启动时就像操作系统申请了一片连续的内存，即Buffer Pool（缓冲池）。

默认情况下，Buffer Pool的大小为128M。

Buffer Pool对应的一片连续的内存被划分为若干个页面，页面大小与Innodb表空间用的页面大小一致，默认都是16kb，为了与磁盘中的页面区分开来，我们把这些Buffer Pool中的页面称为缓冲页。

当我们修改了Buffer Pool中某个缓冲页的数据，它就与磁盘上的页不一致了，这样的缓冲页称为脏页。

当然，我们可以每当修改完某个数据页时，就立即将其刷新到磁盘中对应的页上，但是频繁的往磁盘中写数据会严重的影响程序的性能，所以每次修改缓冲页后，我们并不着急立即将修改刷新到磁盘上，而是在某个时间点进行刷新。

后台有专门的线程负责每隔一段时间就把脏页刷新到磁盘，这样就可以不影响用户线程处理正常的请求。

总之：

InnoDB存储引擎是以页为单位来管理存储空间的，在真正访问页面之前，需要先把磁盘中的页加载到内存中的Buffer Pool中，

之后才可以访问，所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页以一定的频率刷新到磁盘（checkpoint机制），通过缓冲池来优化CPU和磁盘之间的鸿沟，这样就能保证整体的性能不会下降的太快。

Buffer Pool 缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘IO，起到加速访问的作用。

速度快，那为啥不把所有数据都放到缓冲池里？

凡事都具备两面性，抛开数据易失性不说，访问快速的反面是存储容量小：

（1）缓存访问快，但容量小，数据库存储了200G数据，缓存容量可能只有64G；

（2）内存访问快，但容量小，买一台笔记本磁盘有2T，内存可能只有16G；

因此，只能把“最热”的数据放到“最近”的地方，以“最大限度”的降低磁盘访问。

什么是 change buffer

change buffer 是 buffer pool 中的一部分内存；

它既在内存中有拷贝，也可以持久化到磁盘；

其大小通过参数 innodb_change_buffer_max_size 控制,表示最多占用 buffer pool的百分比；

当需要更新一个数据页时，如果数据页在内存中，则直接更新；

否则，在不影响数据一致性的前提下，InnoDB 将这些操作缓存在 change buffer 中，这样就不必从磁盘中读取数据，当下次查询需要访问这个数据页时，再将数据页读入内存，然后执行 change buffer 中与这个页有关的操作，最后将查询结果返回。

merge：将 change buffer 的操作应用到数据页的过程称为 merge。

除了访问数据页会触发 merge 外；系统后台有线程会定期 merge；数据库正常关闭的过程中也会触发 merge 操作。

更新操作记录到 change buffer ，可以减少读磁盘，提高执行效率；而且读入数据会占用 buffer pool ，还可以提高内存使用率。

change buffer使用条件

• 对于唯一索引，所有更新操作都需要做唯一性约束的判断，必须将数据页读入内存，直接在内存中更新，不使用 change buffer 。

• 对于普通索引，当数据页在内存中时，直接进行更新操作即可；当数据页不在内存中时，直接将更新操作写入 change buffer 即可。

change buffer 使用场景

change buffer 的主要作用就是将记录的变更操作缓存下来，

在 merge 之前， change buffer 记录的越多，收益就越大。

change buffer 适合页面变更完之后被马上访问的 概率较小的场景。就是修改之后，就访问的概率小。

如果页面变更之后又要被访问，此时会立即触发 merge 过程，这样反而增加了 change buffer 的维护代价，多了一个写 change buffer 的操作，此时关闭 change buffer 反而能提高效率；

redo log 和 change buffer的区别

redo log：

• 节省了随机写磁盘的IO消耗 chagne buffer:

• 节省了随机读磁盘的IO消耗

更新普通索引时：

如果数据不在内存中，可以直接将变更操作缓存到 change buffer，而不需要将数据读入内存；如果没有 change buffer，则必须将数据读入内存，然后更新数据。此时节省了随机读磁盘的IO消耗；

redo log 将数据变更操作记录在日志中，不用写入磁盘即可返回执行结果；如果没有 redo log，则更新完数据必须先将数据写入磁盘，再返回执行结果。此时节省了随机写磁盘的IO消耗；

查询普通索引时：

如果数据不在内存中，必须先将数据读入磁盘，再执行 change buffer 中的相关操作，然后返回查询结果。只有查询数据时，才需要将数据读磁盘到内存

8 总结

---

因为唯一索引用不了change buffer，若业务可以接受，从性能角度，优先考虑非唯一索引。

到底何时使用唯一索引

问题就在于“业务可能无法确保”，而本文前提是“业务代码已保证不会写入重复数据”，才讨论的性能问题。

• 若业务无法保证或业务就是要求数据库来做约束 没有撤退可言，必须创建唯一索引。那本文意义就在于，若碰上大量插入数据慢、内存命中率低时，多提供了一个排查思路

• “归档库”场景，可考虑使用唯一索引 比如线上数据只需保留半年，然后历史数据存在归档库。此时，归档数据已是确保没有唯一键冲突。要提高归档效率，可考虑把表的唯一索引改为普通索引。

若某次写入使用了change buffer，之后主机异常重启，是否会丢失change buffer数据

不会！虽然是只更新内存，但在事务提交时，change buffer的操作也被记录到了redo log。所以崩溃恢复时，change buffer也能找回。

merge时是否会把数据直接写回磁盘

merge流程

1. 从磁盘读入数据页到内存（老版本数据页）

2. 从change buffer找出该数据页的change buffer 记录(可能多个），依次应用，得到新版数据页

3. 写redo log 该redo log包含数据的变更和change buffer的变更

至此merge结束。

这时，数据页和内存中change buffer对应磁盘位置都尚未修改，是脏页，之后各自刷回自己物理数据，就是另外一过程。

在构造第一个例子的过程，通过session A的配合，让session B删除数据后又重新插入一遍数据，然后就发现explain结果中，rows字段从10001变成37000多。

而如果没有session A的配合，只是单独执行delete from t 、call idata()、explain这三句话，会看到rows字段其实还是10000左右。这是什么原因呢？

如果没有复现，检查

• 隔离级别是不是RR（Repeatable Read，可重复读）

• 创建的表t是不是InnoDB引擎

为什么经过这个操作序列，explain的结果就不对了？

delete 语句删掉了所有的数据，然后再通过call idata()插入了10万行数据，看上去是覆盖了原来10万行。

但session A开启了事务并没有提交，所以之前插入的10万行数据是不能删除的。这样，之前的数据每行数据都有两个版本，旧版本是delete之前数据，新版本是标记deleted的数据。

这样，索引a上的数据其实有两份。

不对啊，主键上的数据也不能删，那没有使用force index的语句，使用explain命令看到的扫描行数为什么还是100000左右？（潜台词，如果这个也翻倍，也许优化器还会认为选字段a作为索引更合适）

是的，不过这个是主键，主键是直接按照表的行数来估计的。而表的行数，优化器直接用的是show table status的值。

大家的机器如果IO能力比较差的话，做这个验证的时候，可以把innodb_flush_log_at_trx_commit 和 sync_binlog 都设成0。