[mysql] 7.普通索引与唯一索引的选择

普通索引： 唯一任务是加快对数据的访问速度，因此，应该只为那些最经常出现在查询条件（WHERE column=）或者排序条件（ORDERBY column）中的数据列创建索引

唯一性索引： 如果确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候，就应该用关键字UNIQUE把它定义为一个唯一索引，Mysql会在有新纪录插入数据表时，自动检查新纪录的这个字段的值是否已经在某个记录的这个字段里出现过了

两者区别以以下场景为例

假如有一个表： 主键列为 ID ，表中有字段 k，并且在 k 上有索引。

建表语句

mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树
的示例示意图如下。

对于查询过程

执行查询语句select id from T where k = 5

• 对于普通索引来说，查找满足条件的第一个记录(5, 500)后，需要查找下一个记录，直到碰到第一个不满足k = 5条件的记录
• 对于唯一索引，由于定义了唯一性，查找到第一个满足条件的记录后就停止查询

两者性能差异：微乎其微

原因：

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

因为引擎是按页读写的，所以说，当找到k=5的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”操作，就只需要一次指针寻找和一次计算。

对于更新过程

概念：change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在
内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change
buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

同时，change buffer也会进行持久化

如果在这张表中插入一个新记录(4,400)， 对于两个索引在更新的区别：

需要更新的记录的目标页存在内存中：

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

需要更新的记录的目标也不在内存中:

• 唯一索引，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束
• 普通索引，将更新记录在change buffer中，语句执行就结束了

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

change buffer适用场景

由于change buffer针对普通索引的更新过程起到了加速作用，但是，普通索引的所有场景，change buffer都会加速吗？答案是否定的。

因为change buffer将更新的记录进行缓存，而在真正进行更新操作时是在下一次读取这个表的时候，所以，change buffer中的记录值越多，收益越大。

可以想到，对于读少写多的业务来说，记录在写完之后马上访问的次数很少，这样的场景适合Change buffer的加速，场景的业务就是：账单类、日志类的系统。

相反的是，如果一个业务的更新模式是写入之后马上就进行查询，更新记录虽然保存在了change buffer上，但是马上查询这个数据页就会执行合并操作，这样的操作不仅访问IO的次数没有减少，还增加了change buffer的维护成本。 所以，这种场景下 change buffer起了副作用

索引的选择

两种索引在查询上基本上没有差异，差异在于更新，所以尽量选择普通索引

如果所有的更新后面，都跟随着马上查询，那么应该关闭change buffer。