MySQL之普通索引和唯一索引的选择
次访问
MySQL索引)这篇文章讲了一些基础的索引知识，我们了解到了索引是什么，能做什么并在适当时候做一些优化。
这篇文章谈的其实也是优化MySQL的索引，但是只局限于比较普通索引和唯一索引。
引子
假设有一个市民系统，每个人都有唯一的身份证号，假设业务代码能够保证不会有身份证号重复的数据录入。
现在要根据身份证号（id_card）查询姓名。
当然，从MySQL索引)这篇文章我们了解到一些必要的优化：身份证号（id_card）作为主键会使得二级索引的叶节点变大，浪费存储空间。
所以，一般不建议使用身份证号作为主键，而是要选择一个自增的非业务id作为主键。
确定了主键之后，我们现在要提高一下查询效率，首先想到的就是：添加索引。
在什么字段上加索引呢？
因为要根据身份证号查询姓名，所以首先想到的肯定是 id_card 字段加索引。
那么加普通索引呢？还是加唯一索引？
我们以 InnoDB 存储引擎为例从两方面的性能对此二者进行比较分析。
查询性能
假设查询语句为
select id from cuser where id_card=XXXXX
因为索引的结构是一棵 B+ 树，所以需要从根节点开始遍历，按层搜索叶子节点。
普通索引不保证索引唯一，所以遍历到 id_card=XXXXX之后，还是要继续往后走，看后面是否还有相同 id_card 的数据节点。
对于唯一索引，则遍历到 id_card=XXXXX 的节点之后就不继续往前走了。
看起来普通索引会比唯一索引有可能多遍历几次（如果重复节点多）。
多遍历的那几次带来的性能影响有多大呢？
我们先了解下 InnoDB 存储引擎的数据加载方式。
我们了解到：存储引擎是以 Page 为单位进行加载的，Page 默认大小为 16KB。每个 Page 可能会容纳几千条 key。
所以如果我们在内存中找到了 id_card，则代表着它所在的 Page 也一起加载进来了。
回头再看看使用普通索引的情况，如果遍历到 id_card=XXXXX 的数据，则后面如果有重复的 id_card，则很大概率也在这一个Page里。而内存级别的扫描，时间量级为 ns，所以因为多扫描而多出来的时间微乎其微。
结论：在查询性能方面，普通索引和唯一索引不分伯仲。
更新性能
再看看两类索引在更新方面的性能影响。
普通索引的更新
普通索引的更新的过程使用时序图表示如下
从时序图可以看到使用普通索引的更新，实际上只是将更新操作缓存到了 change buffer 里，后面的 merge 操作会将缓存到 buffer 里面的操作施加于原始数据上。
merge 操作什么时候触发呢？
下次对原始数据的读操作
这也是防止出现脏数据，既然已经 load 到内存了，那干脆就 merge 操作到数据了。
系统后台线程定期 merge
万一很久都没有读操作，buffer 岂不是越来越大？所以需要系统的后台线程定期 merge。
数据库正常关闭的过程
这个很容易理解，正常关闭，肯定要将未完成的工作执行完。
所以了解了这个过程之后，再来看看，如果我们想插入一个人的数据，会发生什么？
分两种情况：
数据在内存的数据页中
找到合适的位置，直接插入这个值，也不用判断有无冲突。
数据不在内存的数据页中
将更新记录在 change buffer 中，返回。是不是很简单？
唯一索引的更新
唯一索引的更新因为要判断是否有 key 值冲突，所以肯定要将数据页读入内存，以此来判断有无冲突产生，所以也不存在使用 change buffer 这个过程了。
同样要分两种情况：
数据在内存的数据页中
找到合适的位置，判断值有无冲突，无则直接插入这个值。
数据不在内存的数据页中
加载数据页到内存中，执行步骤1。
可以看到，对于唯一索引来说，所要更新的数据所在的数据页必须在内存中，这样才能判断是否有冲突，这是不同于普通索引的地方。
普通索引 vs 唯一索引
对于数据在内存中的情况，唯一索引需要判断是否冲突，对于数据不在内存中的情况，唯一索引要加载数据页到内存，再执行一次判断，之后插入。
唯一索引在更新数据上，多了判断冲突，还有可能多磁盘随机读写操作，而后者往往才是最要命的。
结论：在更新数据上，普通索引的性能会高于唯一索引。（这就是保证唯一性的代价）
Change Buffer 的使用场景
题外话一。
通过普通索引的更新动作，我们可以知道：这种记录缓存操作的方式最适用于写多读少的场景，这样能够实现内存级别的写入效率，比如账单类、日志类的系统。对于这种场景，把 change buffer 设置的尽量大一些较好。
极端情况下，如果一条记录写入后都马上查询，相当于每次读操作都会多出来一次 merge，这样反而增加了 change buffer 的维护代价。
所以每一种机制都有其最适用的场景，天生我才必有用。
选择普通索引 or 唯一索引？
题外话二。
为了维护索引的唯一性，势必造成唯一索引的更新效率低，但是这两类索引在查询效率上没有什么明显差别。
所以一般情况下还是选择普通索引的好。要是索引的唯一性那么重要的话，在业务逻辑上也同样可以保证。
Change buffer vs Redo log
题外话三。
这两个概念容易混淆，下面用一个例子来诠释二者的区别。
我们要在一张表上执行插入语句：
insert into t(id,k) values(id1,k1),(id2,k2)
图中用红色数字标出了执行步骤。
图中涉及了四个部分：内存、redo log(ib_log_fileX)、数据表空间(t.ibd)、系统表空间(ibdata1)。
- page1 在内存中，直接更新数据
- page2 不在内存中，就在内存的 change buffer 区域记录了相应操作
- 步骤1记录到 redo log 中
- 步骤2记录到 redo log 中
总结来说上面4步，1和2都是内存级操作，3和4是磁盘级操作，但是是顺序写入。图中虚线箭头表示是后台操作，不影响响应。
如果我们要在一张表上执行读语句：
select * from t where k in (k1,k2)
图中：
- 读 page1 的内容，直接从内存返回。
- 读 page2 的内容时，需要把 page2 的内容从磁盘加载到内存中，然后应用 change buffer 里面的操作日志，再返回。
（全文完）
参考资料
- 丁奇 《MySQL 45 讲》