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

---

前言

本文学习了开发者应如何选择索引

---

提示：以下是本篇文章正文内容，下面案例可供参考

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

这里先有一个前提 , 那就是系统中的业务代码保证不会向目标字段写入两个相同的值 , 这个前提是非常理想化的了

那么此时对于目标表中新增一个普通索引 还是 对目标字段设置唯一索引选择的问题而言 , 如何做出选择使性能最大化呢?
性能我们可以从查询和更新这两种操作来分析

我们假设新增的普通索引字段为k

1.查询过程

由于普通索引并未做唯一性约束 , 因此

1. 对于普通索引 , 当查找到第一个k==5的字段时,会继续向下搜索,直到找到第一个不满足where条件的记录
2. 对于唯一索引 , 当查找到第一个满足条件的记录时 , 查询过程终止
   但是 , 上述两种操作的性能差距很小.

原因在于innoDB的数据读写机制

2.InnoDB的数据读写机制

由于读写磁盘I/O操作的成本很大 , 因此在读取数据的时候,是先将数据从磁盘读入内存中 , 并以页为单位组织.
既然数据已经存入内存了 , 那普通索引的查找方式就只是多移动几次指针的问题了 , 速度与唯一索引相差不大

3.更新过程

同样地 , 受innoDB读写机制的影响 , 当需要更新一行数据时 , 如果数据已经存到内存中了 , 就对其直接更新
但是如果数据未读入内存呢? 这时就需要引入另一个技术点 change buffer了

前文说过 , 对于磁盘的读写成本是很大的. 因此 , InnoDB会将更新操作缓存到change buffer中. 当下一次访问这个数据页时 , 从磁盘中读入该数据页 (这个读入的数据页是未做更新操作的 , 即,保存着更新以前的数据). 然后InnoDB会对其做一次change buffer中缓存下来的操作(这个过程称为merge) , 以此保证数据的一致性

· 性能分析 :
需要更新的目标页在内存中(假设要插入的索引值为3):

• 对于唯一索引 , 找到索引为2和4的记录,判断是否符合唯一性约束,在二者之间插入记录
• 对于普通索引 , 找到索引2/4之间的记录 , 插入数据

显然 , 这种情况下性能差异几乎没有

需要更新的目标页不在内存中

• 对于唯一索引 , 需要将数据从磁盘读入内存 , 判断是否符合唯一性约束 , 然后插入数据
• 对于普通索引 , 将更新操作写入change buffer, 完事~

此时普通索引的性能更高 , 而唯一索引则会导致一系列问题如: 系统阻塞 , 内存利用率不高

4.change buffer

change buffer在内存中又拷贝 , 同时也需要存入磁盘做持久化, 实质上是持久化了的数据

使用场景 : change buffer 仅限于普通索引 , 尤其是对于读多写少的业务而言 , 对性能提升较大
然而 , change buffer 也有其受限的情况: 当做完更新操作后 , 立马要读取数据的业务而言 , merge操作会频繁触发 , 适得其反

change buffer 和 redo log : 二者都是用于记录数据库操作的 但是, redo log主要用于节省随机写磁盘的IO消耗 , 而change buffer 则是用于节省随机读磁盘的IO消耗

5.索引的选择建议

具体问题具体分析 , 对于开头设定的这种理想情况而言 , 索引确保唯一,我们可以选择使用普通索引
然而如果业务代码无法保证唯一性 , 那还是老实用唯一索引吧!