MySQL普通索引和唯一索引
选择普通索引还是唯一索引?
对于查询来说
:
- a.普通索引,查到满足条件的第一个记录后,继续查找下一个记录,直到第一个不满足的记录;
- b.唯一索引,由于索引唯一性,查到第一个满足的记录后,停止检索,但是两者性能插件微乎其微。因为innodb根据数据页来读写的。
对于更新过程来说:
当需要更新一个数据页,如果数据页在内存里直接更新,如果不存在内存中,不影响数据一致性的前提下,innodb会将这些更新操作缓存在change buffer 中,下次查询需要访问这个数据页的时候,将读入内存,然后执行change buffer中与这个页有关的操作;
change buffer是可以持久化的数据。在内存中有拷贝,也会被写入到磁盘上;
唯一索引的更新不能使用change buffer
change buffer用的是buffer pool的内存,change buffer的大小,可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候,表示change buffer的大小最多只能占用buffer pool的50%。将数据从磁盘读入内存涉及随机IO的访问,是数据库里面成本最高的操作之一。
change buffer 因为减少了随机磁盘访问,所以对更新性能的提升很明显。
change buffer的场景?
在一个数据页做purge之前,change buffer记录的日志的更变越多,收益越大;对于写多读少来说,而且在写完以后马上被访问的概率小,此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。反之,假设一个业务的更新模式是写入后马上查询,即使满足了条件,将更新记录在change buffer之后马上访问这数据页,会立即触发purge过程,这样随机访问IO的次数不会减少,反而增加了change buffer的维护代价。对于这种业务模式,change buffer反而起到了副作用;
索引的选择和实践
尽可能使用普通索引。
redo log主要节省的是随机写磁盘的IO消耗(转成顺序写),而change buffer主要节省的则是随机读磁盘的IO消耗。
change buffer不会丢失,因为change buffer是可以持久化的数据,在磁盘上占据了系统表空间ibdata,对应的内部系统表名为SYS_IBUF_TABLE。因此在异常关机的时候,不会丢失。