Mysql-普通索引和唯一索引怎么选择

普通索引和唯一索引怎么选择

从性能角度来说

查询性能

• 唯一索引：查询等值条件的话由于是唯一的查询的数据直接返回。（范围查询索引不会生效）；
• 普通索引：查询到符合条件的数据后，还会遍历下一条数据是否满足条件。

也就是说普通索引比唯一索引多一次条件判断的操作，这个操作几乎微乎其微，而且Mysql是以页为单位进行读取的（默认16k/页），查询一条记录也会读取整个页到内存，下一次条件判断也是在内存中进行，如果是下一条记录是在另一页的话，这个过程只会复杂一点点，可以忽略不记。

更新性能

change buffer

change buffer是给普通索引使用的，change buffer是一种特殊的数据结构，它包含在buffer pool中，它可以缓存来自二级索引的新增，更改、删除的数据，当有读请求是，就会读到内存中，进行合并操作。

跟主键索引不同的是，二级索引不是唯一的，插入顺序相对的随机，还会造成空洞，所以有change buffer来避免这些问题。更新这些操作全部都写到缓存中，如果缓存中有数据就直接更改。

清除操作会在系统基本闲置或缓慢关闭期间定期运行，将更新后的页写入磁盘。与将每个值立即写入磁盘相比，清除操作可以更有效地写入一系列索引值的磁盘块。

更新性能

​ 两种情况:

1. 数据在内存中：

   • 唯一索引：当插入数据时，找到位置插入，判断是否存在，不存在插入，执行结束；

   • 普通索引：找到位置插入，执行结束。

2. 数据不在内存中：

   • 唯一索引：对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
   • 普通索引：对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

普通索引有change buffer的帮助，减少了随机IO的访问。

怎么选择？

对于读请求多的情况下，建议使用唯一索引，在写多读少的情况下是普通索引更有优势。因为有change buffer的存在，可以一次缓存很多的写请求，然后再系统空闲的时候进行合并，或者是读数据时读到内存中进行合并merge操作，大大的提高了写操作的效率。change buffer也是一样的，读多写少的情况下，建议关闭（或者二级索引少的情况下）。