问题

一张市民表，为身份证号列建立索引。如果业务上可以保证身份证号列满足唯一性，那么身份证号列建立普通索引还是建立唯一索引？为什么？

分场景分析

1.首先由于身份证号字段比较大，不建议做为主键。（因为非主键索引会存储主键的值）

2.查询过程比较：select name from citizen_table where id_card = ‘5’;
1）普通索引：查询到满足条件的第一个记录(小明，5)之后，需要查询下一个记录，直到碰到第一个不满足id_card=5条件的记录。
2）唯一索引：由于唯一索引定义了唯一性，所以查询到第一个满足条件的记录(小明，5)之后，就会停止检索。
查询过程的性能差异微乎其微。InnoDB的数据是按照数据页为单位来读写的，默认的数据页大小是16KB。当找到id_card=5的记录时，这条记录所在的数据页就已经在内存里了。对于普通索引来说，要多做的就是一次‘查询和判断下一条记录’的操作，只是一次指针寻找和计算而已。如果id_card=5的记录恰好是数据页的最后一个记录，那么查询判断下一条记录需要读取下一个数据页，会复杂一些，但是对于整型字段，一个数据也可以放近千个key，所以概率很低。综上，查询的平均性能差异可以忽略不计。

3.更新过程比较：主要在于change buffer
change buffer：目的主要是减少随机读取io。当需要更新一个数据时，如果数据对应的数据页在内存中，就直接更新，否则在不影响数据一致性的前提下，InnoDB会将更新操作保存在change buffer中，然后通过定时刷新到磁盘或者读取数据时进行merge数据，保证了数据的正确性，同时减少了将磁盘的数据页读入内存。
change buffer是可持久化的数据，需要被持久化到系统表空间ibdata1，以及把change buffer的改动记录在redo log里。change buffer会定期、或者在访问相应的数据页、或者数据库正常关闭shutdown时，执行merge操作。change buffer占用的是bufer pool的内存，因此不能无限增大，可以通过参数innodb_change_buffer_max_size动态设置其在buffer pool中的最大占比。
唯一索引不能使用change buffer。因为对唯一索引的所有更新操作，都要先判断这个操作是否满足唯一性约束。判断时必须要依赖所在的数据页存在于内存中，或者将所在的数据页读入内存。因此用不到change buffer。
所以更新过程中，普通索引和唯一索引的差别主要是，当记录所在的数据页不在内存时：1）唯一索引：需要将数据页读入内存，判断没有唯一性冲突，执行更新。2）普通索引：将更新记录在change buffer中，结束。普通索引减少了将磁盘读入内存这一涉及随机IO访问的高成本操作，所以性能要高很多。

change buffer的使用场景

对于change buffer来说，真正进行数据更新的时候是merge，所以在merge之前，change buffer记录的更新越多，收益越大，所以对于写少读多的场景合适，比如账单类，日志类系统。
如果更新后马上做查询，这时候由于需要访问数据页，会出发merge过程，这样不但没有减少随机访问IO的次数，反而增加了维护change buffer的成本，会起到反作用。

结论

综上，如果考录到更新性能的差异，建议尽量选择普通索引。如果是更新后立即查询的场景，应该关闭change buffer。实际应用中，普通索引配合change buffer对数据量大的表的更新优化时比较明显的。

change buffer和redo log的对比

change buffer主要节省了随机读取磁盘的IO消耗。redo log主要通过将随机写磁盘转成顺序写磁盘，节省了随机写磁盘的IO消耗。