建议:尽量使用普通索引
查询性能比较:基本没差别
在字段K上建立普通和唯一索引,执行sql语句:
select id from T where k=5
- 对于普通索引来说,查找到满足条件的第一个记录 k = 5后,需要查找下一个记录,直到碰到第一个不满足 k=5 条件的记录;
- 对于唯一索引来说,由于索引定义了唯一性,表中字段k=5最多出现在一行记录中。查找到第一个满足条件的记录后,就会停止继续检索。
由于InnoDB查询时是先将表中的一页数据读取到内存,所以普通索引查询好几行数据都是从内存中读取,内存中读一行数据和多行数据的速度相差不大。(另外此处分页的情况属于低概率事件,不做考虑)
更新性能:普通索引+change buffer速度更快
补充知识:change buffer(也会持久化:也是先写入redo log,在磁盘中更新后写入bin log,并将redolog状态改为commit)
change buffer在内存占有一块区域,当需要更新一个数据页时,如果数据页在内存中就直接更新,如果这个数据页不在内存中的话,InnoDB 会将这些更新操作缓存在 change buffer 中。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作(merge:查询的时候先更新再读数据),具体如下图所示:
change buffer优点:
- 减少了磁盘io次数(本来每次更新数据时要一次磁盘io,查询数据时又要一次磁盘io,现在多条更新操作和一条查询语句只需要一次磁盘io)
- 提高了内存利用率(每次磁盘io要将一页数据放到内存,现在io次数少了,内存占用少了)
使用change buffer的条件:
- 只有普通索引可以用:
因为唯一索引必须保证表中相同的索引字段只有一行,所以每次更新操作必须先将数据库中的数据读到内存,查询是否已有该字段值,再进行更新(普通索引的话,相同索引字段值可以出现在多行,所以不需要先查询数据表在更新);
由于change buffer的作用就是省去查数据库的过程,唯一索引必须读数据库所以不能用change buffer,普通索引可以用。 - 适用于写多读少的情况
多次写操作可以将sql暂存与内存中,所以提升效果明显
ps:数据库读写压力的释放策略
- redo log主要减少磁盘写操作的io消耗:因为redo log是将所有的要写数据的操作先在内存中进行更新,之后再在磁盘中进行持久化,所以是释放了系统实时的写数据压力。
- change buffer主要减少的是磁盘的读操作io消耗:减少了从数据库读数据页的次数,所以是释放了系统实时的读数据压力。
参考文章:
https://time.geekbang.org/column/article/70848