普通索引和唯一索引有什么区别?如果我们的业务中已经能够保证索引的唯一性,那么我们到底要建什么索引呢?
查询逻辑
现在有一条sql:
select id from t where k = 5; k是索引
普通索引:Mysql会到索引树中按层级查询到第一个满足k=5的数据后,需要继续往后查找到第一个不为5的数后返回。
唯一索引:Mysql会到索引树种按层级查询到第一个满足k=5的数据,由于k是唯一索引,索引可以不用再往后查,直接返回。
所以对于查询来说,普通索引比唯一索引会多查询一次索引树,但是这种消耗可以忽略不计。
更新逻辑
正常的数据更新逻辑是:如果数据对应的数据页在内存中,那么直接更新;如果数据页不在内存中呢?
Mysql在数据一致性可以保证的前提下,使用了change buffer。如果数据页不在内存中,则先将更新缓存在change buffer中,等这个数据页被读到内存的时候,再将更新Merge到数据页中,而且后台还会有线程定时执行merge操作。change buffer也是可以序列化到磁盘中的,所以哪怕数据库宕机数据也不会丢失。
那么对于普通索引来说,更新流程就是在数据页不在内存中,直接将更新操作缓存在change buffer中;但是对于唯一索引来说,由于在插入数据的时候必须保证索引的唯一性,所以必须要把对应的数据页读到内存中判断唯一,然后将数据更新到数据页中。这就导致再插入的时候无法使用change buffer来优化插入。
change buffer 可以通过 innodb_change_buffer_max_size 来设置。表示占用buffer pool的比例。如果值为50,表示占用50%的buffer pool内存大小。
通过上面的分析,普通索引和唯一索引只有在更新的时候会有比较大的性能区别。那么什么时候用普通索引呢?
- 如果插入语句后面紧跟着就要查询改数据,那么使用唯一索引比较合适。
分析:如果插入的时候,数据页不在内存中,那么直接将更新缓存到change buffer中,但是紧跟着就要查询该数据,所以Mysql需要把对应的数据页读取到内存中,此时还需要做merge操作。而唯一索引直接读到数据页到内存插入,再读也是直接从内存中读,此时change buffer的额外花销反而更大。
- 如果所有业务不能保证数据唯一性,靠Mysql保证,那肯定直接就使用唯一索引了。
- 如果频繁更新的表,比如日志表或者历史数据归档表等,使用普通索引会更高效。尤其是对于大量插入操作,因为change buffer越大,一次merge带来的效益也最高。
到此,我们可以检测一下我们现在业务中,有没有频繁插入的表中使用了唯一索引呢?