问题:在字段满足唯一性的情况下,应该选择普通索引还是唯一索引?

下面分别从查询语句以及更新语句对性能进行分析。

一、查询语句的比较

查询语句示例:select * from table_1 where column_1 = *;

1.如果采用“普通索引”,会去找到第一条满足where条件的记录,并且继续查找,直到出现第一条不满足where条件的记录。

2.如果采用“唯一索引”,由于该字段唯一,找到第一条满足where条件的记录就直接结束。
这么看,唯一索引的性能是高于普通索引的;
但是,InnoDB的数据是按数据页为单位来读写的,读取到内存中的;在内存中,“查找并判断下一个字段”的操作,对性能的影响微乎其微。
注:对于整型字段,一个数据页可以放近千个 key,所以“下一个字段在下一页中”的情况,可以忽略不计。

总结:就查询语句而言,普通索引和唯一索引在性能上的差距,可以忽略不计。

二、更新语句的比较

普通索引可以采用change buffer的机制,而唯一索引不行。

change buffer机制介绍
1.当有数据需要更新时,若数据在内存中,直接更新;
2.若数据不在内存中,InnoDB 会将这些更新操作缓存在 change buffer 中;
3.当有操作需要访问该数据所在的数据页时,会读取该数据页,并更新数据。(就算没有操作去访问这个数据页,也会有后台线程定时去更新)

change buffer机制有效的减少了磁盘的读取次数,有效提高了语句的执行速度。
如果采用普通索引,完全可以使用change buffer机制;
如果采用唯一索引,那么是不可以采用change buffer机制的;因为唯一索引的使用,需要满足数据的唯一性;我们将数据暂存在change buffer中,最后数据能否正常更新,这个是不确定的。

采用change buffer机制就一定可以提高数据库性能吗?
答案一定是否定的。
在多写少读的业务场景下,确实减少了大量的磁盘读取,对数据库确有很大的优化提升;
但是在多写多读的业务场景下,写完数据之后马上去读取数据,则立马会进行merge操作(更新),这并没有达到减少磁盘读取的效果;恰恰相反,甚至还增加了change buffer的维护成本。

总结:由于change buffer机制,在多写少读的业务场景下,普通索引更优。


有几个需要注意的地方:
1.change buffer的merge操作要和buffer pool的刷脏操作有所区分:将数据记录到change buffer时,内存中时没有对应的数据页的,也就没有所谓的“脏页”;在执行merge操作时,将数据页读取到内存中,现在内存中就是“脏页”,其后便是刷脏。


overover!!掰掰~~


萌新上路,有理解错误或者可以优化的地方,望指出!

Q.E.D.

知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议

一个喜欢拔刀的萌新Coder