MySQL普通索引与唯一索引

  • 查询语句性能分析
  • 更新语句性能分析
  • change buffer
  • 总结
  • 参考文献



  当我们需要在一个唯一字段上建立索引时，究竟是建立普通索引还是唯一索引的性能更好呢？这时需要从两个角度考虑

查询语句性能分析

  对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录。
  对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
  虽然普通索引需要再判断下一个记录，但由于B+树的叶子结点是一起加载到内存中的，所以这个检查并不需要耗费过多的时间，在查询时两种索引的性能基本相同。

更新语句性能分析

change buffer

  当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。Change buffer由存储引擎负责持久化，写入磁盘
  将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。
  显然更多的利用change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
  由于唯一索引在插入数据时需要判断唯一性，所以必须将所有数据加载到内存中，这时直接更新内存就行了，没有必要用change buffer 了，所以，唯一索引不会用到change buffer,普通索引可以应用change buffer
  当进行插入数据的操作时，如果目标页在内存中，则两种索引性能类似，如果目标页不在内存中，普通索引在change buffer的加持下性能会更好。

总结

  当你更新完之后需要立即查询时，就应关闭change buffer,其余情况尽量采用普通索引与change buffer结合使用。
  redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗，如果没有change buffer, 执行更新的“当时那一刻”，就要求从磁盘把数据页读出来（这个操作是随机读）。

参考文献

MySQL实战45讲