唯一索引、普通索引的使用场景

假设我们有两个表 t1 和 t2：

CREATE TABLE t1 (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100)
    PRIMARY KEY (id),
    KEY idx_key1 (key1)
) Engine=InnoDB CHARSET=utf8;

CREATE TABLE t2 (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100)
    PRIMARY KEY (id),
    UNIQUE KEY idx_key1 (key1)
) Engine=InnoDB CHARSET=utf8;

t1 和 t2 的唯一区别在于：对于列 key1，表 t1 是建立了普通索引，表 t2 是建立了唯一索引。现在分别往两个表插入一样的数据 R1~R5 ，对应的 (id,key1) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)。

1.查询过程

假设，执行查询的语句是 select id from test where k=5。

对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。

对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微 。

2.更新过程

为了说明普通索引和唯一索引对更新语句性能的影响这个问题，介绍一下 change buffer。

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下， InooDB 会将这些更新操作缓存在 change buffer 中 ，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge 。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge操作。

如果能够将更新操作先记录在 change buffer， 减少读磁盘，语句的执行速度会得到明显的提升。

而唯一索引的更新不能使用 change buffer ，实际上也只有普通索引可以使用。因此，对于需要频繁更新的字段，建议用普通索引。

3.总结

• 对于需要频繁更新的字段，建议用普通索引，因为他能通过使用 change buffer 来提升更新表的性能。此外，如果所有的更新后面，都马上伴随着对这个记录的查询 ，那么你应该关闭 change buffer 。
• 如果数据需要保证唯一性，但业务无法保证，即需要数据库来保证唯一性。那为了业务的正确性，就必须要用唯一索引。
• 对于联表查询的情况，可以考虑将被驱动表的连接字段设置为唯一索引，因为 InnoDB 在选择执行计划前会去计算成本，而唯一索引的成本一般被认为是更低的，那生成的执行计划就会更加偏向于走索引。