作者
.NYXX
责编
郭芮
出品
CSDN博客
网上已经有很多关于唯一索引和普通索引的区别，这里就不详细阐述了，接下来我们深入讨论如何根据不同业务场景，应该选择普通索引还是唯一索引。比如维护一个社保管理系统，每个社保人员都有一个唯一的身份证号，而且业务代码已经保证了不会写入两个重复的身份证号。如果该系统需要按照身份证号查询姓名，就会执行这样的SQL语句：selectnamefromsuserwhereid_card=‘xxxxxxxxxxx’;
所以一般会考虑在id_card字段上建索引。由于身份证号字段比较大，不适合用来作主键，索引现在有两个选择，要么给id_card字段创建唯一索引，要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号，那么这两个选择逻辑上都是正确的。但是要从性能角度上来考虑，选择的依据应该是什么呢？下面我们就从两种索引对查询过程和更新过程的性能影响来分析。
查询操作我们来看一下InnoDB索引组织机构，假设执行：selectidfromtwherea=3
这个查询语句在索引树上查找的过程将如下：
先是通过B+树从树根开始，按层序遍历的方式搜索到叶子节点，从而定位数据页。
通过二分查找来定位记录。
唯一索引而言，查找到满足条件的第一个条目（比如（3,））后就会停止继续检索。普通索引查找到一个满足条件的条目后将会继续查找，直到碰到第一个不满足a=3条件的条目。
它们的不同所带来的性能差距却是微乎其微的。因为InnoDB中是按数据页为单位来读写的，也就是说，当读取一个条目的时候并不是将条目从磁盘读出来，而是以页为单位，整体读入内存。既然存储引擎是按页读写的，所以说当找到a=3的条目时，它所在的数据页已经在内存里了。那么对于普通索引需要多做的“查找以及判断条目是否满足条件”的操作就只需要一次指针操作及计算。更新操作当需要更新一个数据页时，如果数据页在内存缓冲池（bufferpool）中就直接更新，并同时记录redolog,但是如果这个数据页不在内存中的话。在不影响一致性的前提下，InnoDB会将更新操作缓存在写缓冲（changebuffer）中，同时记录redolog。写缓冲(changebuffer)那什么是changebuffer呢？它的主要目的是将对二级索引的数据操作缓存下来，以此减少二级索引的随机IO，并达到操作合并的效果。在MySQL5.5之前的版本中，由于只支持缓存insert操作，所以最初叫做insertbuffer，只是后来的版本中支持了更多的操作类型（操作类型包括insert、update、delete）缓存，才改叫changebuffer。changebuffer的数据结构上是一颗b+树，存储在ibdata系统表空间中，根页为ibdata的第4个page(FSP_IBUF_TREE_ROOT_PAGE_NO)。将changebuffer中的操作应用到原数据页从而得到最新结果的过程被称为merge。merge的时候才是是真正进行数据更新的时刻，changebuffer将条目的变更动作进行缓存。在一个数据页做merge之前，changebuffer记录的变更越多（也就是这个页面上要更新的次数越多),收益就越大。一般来说，触发merge的操作主要有以下几种：访问这个数据页；
masterthread线程每秒或每10秒进行一次mergeinsertbuffer的操作；
在数据库正常关闭的时候。
此外，虽然名字叫做changebuffer，但实际上它是可以持久化的数据，也就说它在内存中有拷贝，也会被写入到磁盘上。
changebuffer状态查看segsize为插入缓冲区的总大小（页的数量X16KB）；
merges表示已经合并的merge的数量；
mergedoperations:insert插入记录被merge的次数；
deletemark删除操作被merge的次数；
delete更新操作被merge了多少次。
changebuffer占用bufferpool数据读入内存是需要占用bufferpool的，采用这种方式能够避免占用内存，提升内存利用率。changebuffer用的是bufferpool的内存，因此不能无线增大，它通过参数innodb_change_buffer_max_size来设置，这个参数表示占用内存的比例，默认是25%，最大值为50%，一般在写多读少的场景下才需要设置。changebuffer带来什么好处？如果MySQL承担大量的DML操作，则changebuffer是必不可少的，他的存在就是尽量减小I/O的消耗，通过内存进行数据的合并操作，将多次操作操作尽量变为少量的I/O操作，从而提升了更新操作的速度。什么场景适合开启changebuffer？changebuffer只限于普通索引的场景下，不适用与唯一索引。为什么呢？因为，假设要插入(3,)这个条目，首先要判断这个条目是否在表中出现过。而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用changebuffer了。那么InnoDB中插入的条目（3,）的流程是如何的呢？如果这个条目要更新的数据页在内存中：对于唯一索引，找到2和4的位置，判断没有冲突后，插入这个值，执行结束
对于普通索引，找到2和4的位置，插入这个值，执行结束
如果这个条目要更新的数据页不在内存中：
对于唯一索引，需要将数据页读入内存，然后判断有没有冲突，然后进行插入。
对于普通索引，只需要将条目更新操作记录在changebuffer就执行结束了。
不是所有场景都可以用changebuffer普通索引并不是所有场景使用changebuffer都能受益，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时changebuffer的使用效果最好。但是假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在changebuffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了changebuffer的维护代价。所以，对于这样类似的业务模式来说，changebuffer反而起到了副作用。举个例子：假设要执行insertintotvalues(id1,a1),(id2,a2);假设a1所在的数据页在内存(InnoDBbufferpool)中，a2所在的数据页不在的话，如图所示：如果a1所在的Page1在内存中，则直接更新内存；
如果a2所在的Page2没有在内存中，则在changebuffer中记录下“要往Page2插入一行”这个信息；
将更新Page1这个动作记入到redolog中；
将changebuffer记录插入信息这个动作记入到redolog中。
第3、4写redolog的两次操作合在一起写磁盘。所以从执行过程中可以发现,执行这条更新语句的成本很低，只写了两处内存，而且还是顺序写的。图中的两个红色箭头，都是后台操作(空闲时或者必须时写入磁盘)，不影响更新的响应时间。
那么在之后的读请求该怎么处理呢，比如我们要执行select*fromtwhereain(a1,a2);a1本来就在内存中,之前内存也更新了,所以直接从内存返回。读取Page2的时候，需要把Page2从磁盘读入内存，然后结合changebuffer里面的操作日志生成一个新版本并返回结果。总结普通索引和唯一索引在查询能力上是没差别的，主要考虑的是更新的影响。一般建议使用普通索引。特别是在使用机械盘的场景下，尽量把changebuffer开大从而确保数据的写入速度。声明：本文为CSDN博主「.NYXX」的原创文章，原文链接：