唯一索引和普通索引怎么选择

前段时间还在忙，终于又出点空更新了，虽然不是所有的付出都有收获，很多时候需要我们先付出才可以看到希望。

一前言

Mysql类的文章看的人比较少，我想一方面可能是大家更热衷于比较前言的技术，像Mysql类的已经发展很久的基础数据库，关注的人反而少，当时我在学习Mysql的过程中发现，Mysql的很多优秀的技术在大数据很多开源框架中都能看到影子，所以还是按照笔记的方式把这个系列写完吧，当然也会穿插些其他内容。

二选择标准

其实，如果业务上就要求我们数据库的值必须是唯一的，那没什么好讨论的，就选择唯一索引；那么如果业务上要求不严格，或者说不需要我们数据库后台来保障唯一性要求，这时候我们选择唯一索引还是普通索引就看谁在性能上更好，谁好选谁。

三性能比较

3.1 查询性能比较

对于数据库查询来说，以前介绍过，InnoDB引擎的索引以B+树这种数据结构保存的，我们在利用索引查询的时候，先从B+树的叶子节点进行按层搜索，定位到我们数据在的数据页，数据页内基本按照二分法查找我们具体要查找的数据。

普通索引：这时候，我们查找到满足条件的值后，需要进一步查找，直到不满足条件为止。
唯一索引：我们查找到需要查找的值后，由于唯一索引，所以只有一个值，所以可以直接返回。
从这个角度来看，唯一索引更快。但是影响并不大，是因为我们读取数据的时候都是按照数据页去读取的，
一个数据页默认大小为64K，可以存上千个索引值。那普通索引的移动指针到下一个元素和比较的数据都是在内存中的，所以影响比较小。

3.2 更新性能比较

我们想一下，Mysql的数据包含两个部分，一部分在内存中，一部分在磁盘上，在内存中的不光是Mysql的数据还有索引。
那么我们在更新的时候，如果数据在内存里面好说，直接更新，定期刷新到磁盘，但是更多的时候可能是数据不在内存中，如果每次都从磁盘读取数据所在的数据页，然后去操作，就需要至少涉及到一次磁盘的随机读操作，比较昂贵的操作。

那么我们是否可以在内存里面中把这个更新操作纪录下来，在合适的时候再将数据合并到磁盘上。这块内存在Mysql中就叫 change buffer，（看到buffer，可能就会认为只存在内存中，实际上change buffer不光在内存中也存在磁盘上，同步到数据库的系统表空间ibdata1），更新操作的时候，数据如果不在内存中，且不影响数据（比如不影响数据的唯一性的情况下），我们把更新操作纪录到change buffer上，并不需要从磁盘上读取数据页。这时候，如果来了一个查询动作，刚好要查这条数据的情况下，Mysql会从磁盘上读取这个数据页，然后发现change buffer 有修改了这个数据页，会将这个数据页修改的内容merge到这个数据页上。
查询配置如下：

mysql> show variables like '%innodb_change_buffer%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
| innodb_change_buffering       | all   |
+-------------------------------+-------+
2 rows in set, 1 warning (0.03 sec)
mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+
1 row in set, 1 warning (0.01 sec)

说明下：

innodb_change_buffer 为change_buffer 占 innodb_buffer_pool_size中的百分比，这里面25%最大为50%，我这个本机配置实在是太低了。

innodb_change_buffering 表示哪些场景用change_buffer 取值：all/none/inserts/deletes

那在更新数据的时候，数据库是如何操作的，总体来说分两种清空，要操作的数据所在的数据页在内存中和不在内存中。
数据页在内存中：

如果是普通索引，则直接更新内存中的数据。（这里简化了很多还涉及到redolog和binlog等）
如果是唯一索引，碰巧要做的是插入操作，则需要将需要插入的字段值和数据页中的比较看是否存在，决定是否可以插入。
两种操作的耗时差别很小。

数据页不在内存中：

如果是普通索引，则在change buffer中记录对那个数据页做了什么样的修改。
如果是唯一索引，则需要将数据页读取到内存中，判断是否满足唯一性约束，数据已经读入内存了，这时候肯定不会再适用change buffer了，因为已经多了一次IO的随机读了。

所以从更新角度看，普通索引可以利用change buffer 更新操作的性能比唯一索引要更好。
这里面要说明的是有些人可能会认为像插入操作，我们需要一个主键，主键是唯一索引，所以插入操作是用不到change buffer，这样是不对的，因为一个表一般除了主键还有二级索引，主键用不到，二级索引可以用到change buffer。

四 Change buffer 适用场景

4.1 Change buffer 不适用场景

不是所有的场合都适合使用change buffer的，change buffer 的本质是通过减少磁盘的随机IO读的访问来提升系统的性能。

如果一个数据在写入后，经常需要立刻读出来，那么我们并不能降低随机读，而且还会增加change buffer的操作负担，所以并不适合。
如果数据库的数据都只有主键，或只有唯一索引，也不合适。

4.2 Change buffer 适用场景

如果我们的数据是读少，写多的，比如日志数据。
还有就是我们系统中的历史库，几乎不会再读取数据了，却需要随时搬迁历史数据到此库中，肯定没有唯一冲突了，可以考虑把唯一索引改成普通索引，以提升搬迁性能。