MySQL之普通索引和唯一索引的选择

本篇博客要谈的，就是在保证业务的情况下，选择普通索引还是唯一索引！

即你保证插入的数据不会重复，如果你插入的数据不保证不会重复，而你的业务又要求必须不能重复，那你只能选择唯一索引啰！
本篇博客是基于保证插入数据不会重复的情况下来选择普通索引还是唯一索引！！

1. 场景

现在假如你在维护一个市民系统（User表），每个人都有唯一的身份证号（id_card），且业务保证数据库里面的身份证号不会重复，现在你要根据身份证号来查询业务，会用到如下语句：

select * from User where id_card = 'XXXXXXXXXXXXXXXXXX';

所以，对于上面的查询语句，我们考虑在id_card上建立索引，由于身份证号比较大，所以这里就不推荐使用主键了，所以此时有两种方案：

• 在id_card上加普通索引
• 在id_card上加唯一索引

由于身份证号过长，我在下面举例的时候假设身份证号为一位，下图的ID是上面User表的主键，我们知道，主键中存放了该用户的所有信息，而非主键索引节点中存放的是主键；

2. 查询分析

假设现在我要执行下列查询语句：

select * from User where id_card = '5';

这个查询语句在索引树上查找的过程：

• 先从B+树的根开始，按层搜索到叶子节点，也就是图中右下角那个数据页，然在在那个数据页内（3~5）进行二分查找来定位id_card=5这条记录；

对于普通索引和唯一索引，上面的过程都是一样的，接下来的过程就不同了：

• 普通索引：当找到满足条件的第一个记录（5，500）后然后回表查询主键找到该条记录对应的所有信息，保存到结果集，然后继续在id_card那个数据块中查找下一个记录，重复上面操作，直到碰到那条记录不满足id_card=5；
• 唯一索引：由于唯一索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索；

性能差距：
对于上面的查询性能差距，可以说是微乎其微！

• InnDB的数据是按照数据页来进行读取的；也就是说，当需要读一条记录的时候，并不是将这条记录从磁盘加载到内存然后进行读取，而是以页为单位，将查找到的记录那一块整体读入内存（而引擎的设置一般是一个数据块刚好为一页的大小），在InnoDB中，每个数据页的大小默认是16KB；
• 因为引擎是按页读的，所以当找到 id_card这条记录的时候，那一块数据都在内存里了，此时普通索引不过比唯一索引多查询了几下而已（内存中访问贼快嘛），**也就是多移动了几下指针和回表，效率差距微乎其微！ **
• 特例情况： （忽略不计）
  假如k=5这条记录刚好是那个数据页的最后一条记录，此时指针往下寻找时就得从磁盘读入新的数据块（数据页），此时涉及到访问磁盘，所以效率比较低下，但是一个数据页为16KB，这足够存放近千个key，所以这种情况还是比较少的，可以忽略不记；

3. 更新分析

1. change buffer（⭐）

change_buffer 是buffer pool里面的内存，因此不能无限增大；它的大小可以通过参数：
innodb_change_buffer_max_size 来动态设置 ，当这个参数为50 的时候，表示change_buffer的大小最多只能占用buffer pool的50%；

mysql> show variables like'innodb_change_buffer_max_size';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+
1 row in set, 1 warning (0.15 sec)

上面便是我在我的数据库中查的的信息，可见默认值为25；

当需要更新一个数据页时：

• 如果数据页在内存中就直接更新，并将更新记录到redo_log中，然后更新就完毕了；
• 如果数据页不在内存中，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change_buffer中，这样就不需要从磁盘中读入这个数据页了，在下次查询需要访问这个数据页的时候，再将这个数据页读入内存，然后执行change_buffer中与这个数据页有关的操作，这样就可以保证数据逻辑的正确性！

2. merge

将change_buffer中的数据应用到数据页上得到最新结果的过程就叫做：merge

发生merge的情况：

• 访问不在内存中的数据页（该数据页还被修改过，记录在change_buffer上）；
• 系统有后台程序进行定期merge；
• 数据库正常关闭的过程也会执行merge；

显然，如果能将更新操作先记录在change_buffer上，减少读磁盘，语句的执行速度会得到明显提升，而且数据读入内存是要占用buffer pool（所有的数据页都由buffer pool来管理）的，这种方式还能够避免占用内存，提高内存利用率；

3. 更新下普索和唯一索引的具体效率区别

上面我们已经了解了change_buffer的概念，那么对于更新过程的普通索引和唯一索引，我们作如下结论：
（注意：这里的更新指更新索引的内容）

• 唯一索引不能使用change_buffer（⭐）
  • 因为对于唯一索引的查询所有的更新操作都要判断这个操作是否违反唯一性约束，比如要插入id_card=5这条记录，就得判断表中是否已经存在id_card为5 的记录，而这个操作必须将该数据页读入内存才能判断，如果都已经读入内存了，那么更新就直接更新内存，不用再使用change_buffer;
• 普通索引在更新的时候，若更新的数据页不在内存里，则可以使用change_buffer，若需要更新的数据页在内存里，则直接更新内存；

从上面就可以看到，当我们要更新的数据在内存里的话，此时普通索引和唯一索引都是直接更新内存，没有区别（唯一索引只是多了个判断是否有重复，效率影响很小），所以他们更新的区别就体现在了： （⭐⭐）

• 当要更新的数据不在内存，唯一索引就得读取磁盘，而普通索引不需要，这种情况两者的效率差距就非常的大了；

4. change_buffer的具体使用场景（????????????）

上面的分析，证明了change_buffer在普索的更新下有着很大的效率提升，那么普通索引的所有场景，都适合使用change_buffer吗？

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。
因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。 反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用 ；

4. 总结

根据上面的分析，做个简单总结：

• 唯一索引不能使用change_buffer，普通索引能使用，所以一般场景下最好使用普通索引；
• 注意：在更新少，读多的场景下不适合使用change_buffer，应该关闭change_buffer ; （⭐）
• 当数据库使用机械硬盘来存储数据时，在大量更新的场景下使用普索+change_buffer将会带来效率的大幅提升！！

5. change_buffer和redo_log（????）

可能到这，对这两个概念比较混淆；
redo_log主要是为了减少随机写磁盘的IO消耗，而change_buffer是为了减少随机读磁盘的IO消耗！！！

redo_log是为了避免更新后数据马上写入磁盘导致性能降低，而change_buffer是为了避免更新的时候去磁盘中读取那个数据所在的数据页！
也就是说，

• 只有redo_log的话，所有更新都会先将需要更新的数据所在的数据页从磁盘中读入内存，然后更新内存，将更新记录记载在redo_log中，
• 而使用change_buffer的话，读都不会读入要更新的数据的数据页，那么此时redo_log怎么做呢？此时redo_log就将change_buffer这条变动信息保存给自己就行；