Mysql - 普通索引与唯一索引之间性能差别change buffer

    开始之前先做一个总结和对一个误区进行说明（我误解了好久）......

总结：普通索引（或没有二级索引）、唯一索引当执行查询操作时，性能本身没有差距；执行写（insert、update、delete）操作时，如果数据页在内存中性能差距也可以忽略；如果数据页不在内存中，那么非唯一索引可以只在change buffer中做修改，而唯一索引一定要从磁盘中获取数据页加载到内存中，判断唯一操作。即只有写操作并且数据页不在Buffer Pool中时，非唯一索引可以利用change buffer将随机访问磁盘操作变成批量的顺序操作，提升性能。

    那么面试问题来了，怎么可以提升Mysql的写入能力？1、尽量开大change buffer 2、尽量不要使用唯一索引（可以在查询中保证唯一）。由参数设置：innodb_change_buffer_max_size = 50，表示将buffer Pool的 50% 设置为change buffer，默认25%，最大50%。

一个误解：insert buffer、change buffer的关系？

最开始的时候我以为比较早的版本中使用 insert buffer对插入语句进行优化；后面的版本增加了change buffer对（update、delete）语句进行优化。其实是，后面的版本使用change buffer兼容了原来insert buffer的功能，即change buffer会对非唯一索引的insert、update、delete操作进行优化。insert的时候，写主键是肯定不能用change buffer了，但是同时也会要写其它索引，而其它索引中的“非唯一索引”是可以用的change buffer机制的。

change buffer

    change buffer虽然叫buffer但是它可能在内存中也可能在磁盘中，可以参见上一篇博客中的InnoDB架构图。buffer Pool的作用就是将每次写操作时，可能需要先执行的随机读操作（数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一），尽可能多的叠加到一起执行顺序读。这里需要与redo log进行对比（非常容易混淆，下面的merge动作也说明了他们的联系）： redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。虽然提升了写的性能，但是change buffer中的数据与磁盘中的不一致，需要同步，触发机制有两个：

1、后台的定时线程执行merge操作

2、当查询时，发现数据在change buffer中，则需要先执行merge操作，再将正确的结果返回给客户端。

    基于上面的刷新策略，所以change buffer的使用场景：使用于写多读少的场景，每次执行merge操作时，数据量越多性能提升越大【比如：日志、账单等业务系统就满足该特点】。并且对于写入后马上就要进行读取的场景，频繁执行change buffer的merge操作反而影响性能。

info：正好自己前段时间就在写全微服务系统的操作日志，自己就想到了尽量不要使用唯一索引，尽量开大change buffer，这算是进步吗，哈哈。

    change buffer的具体merge执行流程：

1、从磁盘读取数据页到内存

2、从change buffer中找到这个数据页的change buffer（可能是多条数据），依次应用到新的数据页

3、写rudo log日志，这个redo log包含了数据变更信息，change buffer的数据变更动作。

 

    如果某一字段一定要添加索引，以提升查询性能。业务能够保证数据的唯一性，那么选择普通所以和唯一索引都是可以的。下面对普通索引和唯一索引在执行流程进行分析，对执行性能进行对比：

1、读取操作

    比如执行查询的语句是 select id from user where name=kevin。这个查询语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点，确定了数据页，然后可以认为数据页内部通过二分法来定位记录。前面在索引一篇的博客中分析过二级索引的查询过程，下面还是使用该图：

普通索引查询流程：查询到满足条件的第一个记录后，需要继续查询下一条记录，直到第一个不满足条件的记录为止（因为索引本身有序）。

唯一索引查询流程：查询到满足条件的第一个记录，因为唯一所以停止查询，返回。

  分析：普通索引和唯一索引的查询性能区别，就是非唯一索引需要查询到第一个不满足条件的记录。对于查询的到满足条件的数据页肯定已经在内存中，那么继续查询基本也是在该数据页中，即对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算，对现在的cpu来说可以忽略。 如果刚好下一条数据不再该页中（一个数据页默认16K，可以存储int类型大概2000条数据）， 我们之前分析InnoDB 预读的能力，如果下一页也不再内存中，概率就更小了。即使这么小的概率还是发生了磁盘寻址读取，那么我们利用时间复杂度分析中的均摊的时间复杂度都等于（或者说接近）最好时间复杂度。即，查询性能方面，普通索引和唯一索引基本一致。

2、写操作

数据页在内存中：如果数据页在内存中，直接更新操作即可；

数据页不在内存中： 普通索引可以直接使用change buffer更新即可，后续执行merge。 而唯一索引需要将数据页读入内存中，判断是否唯一，所以不能使用change buffer。

info：如果服务器本身是ssd存储，那么寻址的代价差距就不是特别大了。但是如果还是机械硬盘，那么性能替身还是蛮大的。