关于mysql:Mysql普通索引和唯一索引的选择分析

假如一个用户管理系统，每个人注册都有一个惟一的手机号，而且业务代码曾经保障了不会写入两个反复的手机号。如果用户管理系统须要依照手机号查姓名，就会执行相似这样的 SQL 语句：

select name from users where mobile = '15202124529';

通常会思考在 mobile 字段上建索引。因为手机号字段绝对较大，通常根本不会把手机号当做主键，那么当初就有两个抉择:

1.  给 id_card 字段创立惟一索引
2.  创立一个一般索引

如果业务代码曾经保障了不会写入反复的身份证号，那么这两个抉择逻辑上都是正确的。

从性能的角度思考，抉择惟一索引还是一般索引？

如图：假如字段 k 上的值都不反复

接下来，就从这两种（ID，k）索引对查问语句和更新语句的性能影响来进行剖析

查问过程

假如，执行查问的语句是 select id from T where k=5。这个查问语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜寻到叶子节点，也就是图中右下角的这个数据页，而后能够认为数据页外部通过二分法来定位记录(数据页外部通过有序数组保留节点。数据页之间通过双向链表串接)。

• 对于一般索引来说，查找到满足条件的第一个记录 (5,500) 后，须要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
• 对于惟一索引来说，因为索引定义了唯一性，查找到第一个满足条件的记录后，就会进行持续检索。

那么，这个不同带来的性能差距会有多少呢？答案是，微不足道。

起因：除非 Key 的列十分大，有间断多个 Key 占满了一个 page，才会引起一次 page 的 IO，这样才会产生比拟显著的性能差别，从均摊上看，差别简直能够不算。

InnoDB 的数据是按数据页为单位来读写的。也就是说，当须要读一条记录的时候，并不是将这个记录自身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。

更新过程

为了阐明一般索引和惟一索引对更新语句性能的影响这个问题，须要先介绍一下 change buffer

• 当须要更新一个数据页时，如果数据页在内存中就间接更新，
• 而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下：

1.  InnoDB 会将这些 更新操作 缓存在 change buffer 中，这样就不须要从磁盘中读入这个数据页了。
2. 在下次查问须要拜访这个数据页的时候，将数据页读入内存，
3. 而后执行 change buffer 中与这个页无关的操作。

    通过这种形式就能保障这个数据逻辑的正确性

须要阐明的是，尽管名字叫作 change buffer，实际上它是能够长久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

把 change buffer 中的操作，利用到旧的数据页，失去新的数据页的过程，应该称为 merge。

Ps.  除了拜访这个数据页会触发 merge 外，零碎有后盾线程会定期 merge。在数据库失常敞开（shutdown）的过程中，也会执行 merge 操作。

(change buffer 的 merge 操作，先把 change buffer 的操作更新到内存的数据页中，此操作写到 redo log 中，mysql 未宕机，redo log 写满后须要挪动 check point 点时，通过判断内存中数据和磁盘是否统一即是否是脏页来刷新到磁盘中，当 mysql 宕机后没有内存即没有脏页, 通过 redo log 来复原。)

显然，如果可能将更新操作先记录在 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%。

Ps. 数据库缓冲池(buffer pool) https://www.jianshu.com/p/f9ab1cb24230

剖析：插入一个新记录 InnoDB 的解决流程

了解了 change buffer 的机制，那么如果要在这张表中插入一个新记录 (4,400) 的话，InnoDB 的解决流程是怎么的

1、第一种状况是：这个记录要更新的指标页在内存中。

•  这时，InnoDB 的解决流程如下：对于惟一索引来说，找到 3 和 5 之间的地位，判断到没有抵触，插入这个值，语句执行完结；
• 对于一般索引来说，找到 3 和 5 之间的地位，插入这个值，语句执行完结。

这样看来，一般索引和惟一索引对更新语句性能影响的差异，只是一个判断，只会消耗渺小的 CPU 工夫。但，这不是关注的重点

2、第二种状况是，这个记录要更新的指标页不在内存中。这时，InnoDB 的解决流程如下：

• 对于惟一索引来说，须要将数据页读入内存，判断到没有抵触，插入这个值，语句执行完结；
• 对于一般索引来说，则是将更新记录在 change buffer，语句执行就完结了。

将数据从磁盘读入内存波及随机 IO 的拜访，是数据库外面老本最高的操作之一。change buffer 因为缩小了随机磁盘拜访，所以对更新性能的晋升是会很显著的。

change buffer 次要是将更新操作缓存起来, 异步解决. 这样每次更新过去, 间接记下 change buffer 即可, 速度很快，将屡次写磁盘变为一次写磁盘

change buffer 的应用场景

通过下面的剖析，曾经分明了应用 change buffer 对更新过程的减速作用，也分明了 change buffer 只限于用在一般索引的场景下，而不适用于惟一索引。

一般索引的所有场景，应用 change buffer 都能够起到减速作用吗？

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

因而，对于写多读少的业务来说，页面在写完当前马上被拜访到的概率比拟小，此时 change buffer 的应用成果最好。这种业务模型常见的就是账单类、日志类的零碎。（适宜写多读少的场景，读多写少反倒会减少 change buffer 的保护代价）

反过来，假如一个业务的更新模式是写入之后马上会做查问，那么即便满足了条件，将更新先记录在 change buffer，但之后因为马上要拜访这个数据页，会立刻触发 merge 过程。这样随机拜访 IO 的次数不会缩小，反而减少了 change buffer 的保护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。（如果立刻对一般索引的更新操作后果执行查问，就会触发 merge 操作，磁盘中的数据会和 change buffer 的操作记录进行合并，产生大量 io）

索引抉择和实际

综上剖析，一般索引和惟一索引应该怎么抉择：

其实，这两类索引在 查问能力上是没差异的，次要思考的是对更新性能的影响 。所以， 倡议尽量抉择一般索引。

如果所有的更新前面，都马上随同着对这个记录的查问，那么应该敞开 change buffer。

而在其余状况下，change buffer 都能晋升更新性能。在理论应用中，一般索引和 change buffer 的配合应用，对于数据量大的表的更新优化还是很显著的。

Ps. 特地地，在应用机械硬盘时，change buffer 这个机制的收效是十分显著的。所以，当有一个相似“历史数据”的库，应该特地关注这些表里的索引，尽量应用一般索引，而后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

change buffer 和 redo log

了解了 change buffer 的原理，可能会联想到 redo log 和 WAL(Write-Ahead Logging，它的关键点就是先写日志，再写磁盘)。

WAL 晋升性能的外围机制，也确实是尽量减少随机读写

在表上执行这个插入语句：

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

假如以后 k 索引树的状态，查找到地位后，k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中。如图 是带 change buffer 的更新状态图。

图 3  带 change buffer 的更新过程

剖析这条更新语句，你会发现它波及了四个局部：

内存、redo log（ib_log_fileX）、数据表空间（t.ibd）、零碎表空间（ibdata1）。

数据表空间：就是一个个的表数据文件，对应的磁盘文件就是“表名.ibd”；零碎表空间：用来放零碎信息，如数据字典等，对应的磁盘文件是“ibdata1”

数据表空间 和 零碎表空间 仿佛代表的就是 B + 树对应的那个简单的构造

这条更新语句做了如下的操作（依照图中的数字程序）：

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

做完下面这些，事务就能够实现了。所以，你会看到，执行这条更新语句的老本很低，就是写了两处内存，而后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是程序写的。

change buffer 和 redo log 颗粒度不一样，因为 change buffer 只是针对如果更改的数据所在页不在内存中才临时贮存在 change buffer 中。而 redo log 会记录一个事务内进行数据更改的所有操作，即便批改的数据曾经在内存中了，那也会记录下来

同时，图中的两个虚线箭头，是后盾操作，不影响更新的响应工夫。

那在这之后的读申请，要怎么解决呢？

比方，咱们当初要执行 select * from t where k in (k1, k2)。

如果读语句产生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与零碎表空间（ibdata1）和 redo log（ib_log_fileX）无关了。

图 4 带 change buffer 的读过程

从图中能够看到：读 Page 1 的时候，间接从内存返回。

WAL 之后如果读数据，是不是肯定要读盘，是不是肯定要从 redo log 外面把数据更新当前才能够返回？

其实是不必的。尽管磁盘上还是之前的数据，然而这里间接从内存返回后果，后果是正确的。要读 Page 2 的时候，须要把 Page 2 从磁盘读入内存中，而后利用 change buffer 外面的操作日志，生成一个正确的版本并返回后果。能够看到，直到须要读 Page 2 的时候，这个数据页才会被读入内存。

如果要简略地比照这两个机制在晋升更新性能上的收益的话，redo log 次要节俭的是随机写磁盘的 IO 耗费（转成程序写），而 change buffer 次要节俭的则是随机读磁盘的 IO 耗费。

思考题：

1、通过图 3 能够看到，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 里复原。