普通索引和唯一索引如何选择
假设你在维护一个市民系统,每个人都有一个唯一的身份证号,而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名,就会执行类似这样的SQL语句:
select name from CUser where id_card = ‘xxxxxxxyyyyyyzzzzz’;
所以,你一定会考虑在id_card字段上建索引。
由于身份证号字段比较大,我不建议你把身份证号当做主键,那么现在你有两个选择,要么给id_card字段创建唯一索引,要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号,那么这两个选择逻辑上都是正确的。
查询过程
假设,执行查询的语句是 select id from T where k=5。这个查询语句在索引树上查找的过程,先是通过B+树从树根开始,按层搜索到叶子节点,也就是图中右下角的这个数据页,然后可以认为数据页内部通过二分法来定位记录。
- 普通索引:查找到满足条件的第一个记录(5,500)后,需要查找下一个记录,直到碰到第一个不满足k=5条件的记录。
- 唯一索引:由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索。
那么,这个不同带来的性能差距会有多少呢?答案是,微乎其微。
你知道的,InnoDB的数据是按数据页为单位来读写的。也就是说,当需要读一条记录的时候,并不是将这个记录本身从磁盘读出来,而是以页为单位,将其整体读入内存。在InnoDB中,每个数据页的大小默认是16KB。
因为引擎是按页读写的,所以说,当找到k=5的记录的时候,它所在的数据页就都在内存里了。那么,对于普通索引来说,要多做的那一次“查找和判断下一条记录”的操作,就只需要一次指针寻找和一次计算。
当然,如果k=5这个记录刚好是这个数据页的最后一个记录,那么要取下一个记录,必须读取下一个数据页,这个操作会稍微复杂一些。
但是,我们之前计算过,对于整型字段,一个数据页可以放近千个key,因此出现这种情况的概率会很低。所以,我们计算平均性能差异时,仍可以认为这个操作成本对于现在的CPU来说可以忽略不计。
更新过程
首先了解一下change buffer
当需要更新数据页时,如果数据页在内存中就直接更新,而如果数据页还不在内存之中,在不影响数据一致性的前提下,InnoDB会将这些更新操作缓存在change buffer中,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中与这个页有关的操作。这样就能保证数据的正确性。
需要说明的是,change buffer是可以持久化的数据,也就是说,change buffer在内存中有拷贝,也会被写入磁盘上。
将change buffer中的操作应用到原数据页,得到最新结果的过程称为merge。除了访问这个数据页会触发merge之外,系统有后台线程会定期进行merge。在数据库正常shutdown的过程中,也会执行merge操作。
显然,如果能把更新操作先记录在change buffer中,减少读磁盘,语句的执行速度会大大提升。而且,数据读入内存是要占用buffer pool的。所以这种方式还能避免占用内存,提高内存利用率。
那么,change buffer什么条件下使用比较合适呢?
对于唯一索引来说,所有的更新操作都需要判断是否违反唯一约束。比如,要插入(4,400)这个记录,就要先判断现在表中是否已经存在k=4的记录,而这必须要将数据页读入内存才能判断,如果数据页已经读入内存了,可以直接进行更新操作,没必要使用change buffer了。
因此,唯一索引不能使用change buffer,只有普通索引才可以使用。
change buffer使用的是buffer pool中的内存,因此不能无限增大。change buffer的大小,可以通过InnoDB_change_buffer_max_size 来动态设置。这个参数设置为50的时候,表示change buffer最多占用buffer pool的50%。
change buffer使用场景
普通索引的所有场景,change buffer都可以起到加速作用吗?
因为merge的时候是真正进行数据更新的时刻,而change buffer的主要目的就是将变更缓存下来,所以在一个数据页做merge之前,change buffer中的变更越多,收益越大。
因此,对于写多读少的业务,页面写完之后被访问的概率很小,此时change buffer的使用效果最好,这种业务模型最常见的就是账单类,日志类系统。
反过来,加入一个业务的更新之后立马做查询,即使满足了条件,将更新先记录在change buffer中,记录完之后,马上要访问这个数据页,会立马触发merge过程。这样访问IO的次数不会减少,反而增加了change buffer的维护。所以,这很业务模式来说,change buffer反而起到了反作用。
问题
如果某次更新写入了change buffer,之后电脑异常重启,是否会丢失change buffer和数据?
答案是不会。虽然是只更新内存,但是在事务提交的时候,我们把change buffer的操作也都记录到了redo log中了,所以崩溃恢复的时候,change buffer也能找回来。