0%

普通索引和唯一索引的选择

MySQL中尽量使用普通索引,减少使用唯一索引

查询过程

平均性能差异微乎其微。

更新过程

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的机制,那么我们再一起来看看如果要在这张表中插入一个新纪录(4,400)的话,InnoDB的处理流程是怎样的。

第一种情况是,这个记录要更新的目标页在内存中。那么两者差不多。

第二种情况是,这个记录要更新的目标页不在内存中。那么唯一索引要读入内存,涉及磁盘随机IO的访问,是成本最高的操作之一。

change buffer的使用场景

普通索引的所有场景,使用change buffer都可以起到加速作用吗?

因为merge的时候是真正进行数据更新的时刻,而change buffer记录的变更越多(也就是这个页面上要更新的次数越多),收益就越大。

因此,对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。(这类写入比较多的业务类型可以使用LSM树作为数据结构的数据库)。

反过来,假设一个业务的更新模式是写入之后马上会做查询,那么即使满足了条件,将更新先记录在change buffer,但之后由于马上要访问这个数据页,会立即触发merge过程。这样随机访问IO的次数不会减少,反而增加了change buffer的维护代价。

索引该如何选择和实践呢?

普通索引和唯一索引应该如何选择。查询能力上没差别,主要考虑的是对更新性能的影响。所以,我建议你尽量选择普通索引。

如果所有的更新后面,都马上伴随着对这个记录的查询,那么你应该关闭change buffer。而在其他情况下,change buffer都能提升更新性能。

在实际应用中,你会发现,普通索引和change buffer的配合使用,对于数据量大的表的更新优化还是很明显的。

特别的,在使用机械硬盘时,change buffer这个机制的收效是非常显著的。所以,当你有一个类似”历史数据“的库,并且处于成本考虑用的是机械硬盘时,那你应该特别关注这些表里的索引,尽量使用普通索引,然后把change buffer尽量开大,以确保这个”历史数据“表的数据写入速度。

change buffer和redo log

现在,我们要在表上执行这个语句:

1
mysql> insert into t(id, k) values(id1, k1), (id2, k2);

这里,我们假设当前k索引树的状态,查找到位置后,k1所在的数据页在内存(InnoDB buffer pool)中,k2所在的数据页不在内存中。如果2所示是带change buffer的更新状态图。

分析这条更新语句,你会发现它涉及了四个部分:内存、redo log(ib_log_fileX)、数据表空间(t.ibd)、系统表空间(ibdata1)。

这条更新语句做了如下的操作(按照图中的数组顺序):

  1. Page 1在内存中,直接更新内存;
  2. Page 2没有在内存中,就在内存的change buffer区域,记录下”我要往Page2插入一行“这个信息
  3. 将上述两个动作记录到redo log中。

做完上面这些,事务就可以完成了。所以,你会看到,执行这条更新语句的成本很低,就是写了两处内存,然后写了一处磁盘(两次操作合在一起写了一次磁盘),而且还是顺序写的。

同时,图中的两个虚线箭头,是后台操作,不影响更新的响应时间。

那么这之后的读请求,要怎么处理呢?

比如,我们现在要执行select * from t where k in (k1, k2)。这里,我画了这两个请求的流程图。

如果读语句发生在更新语句后不久,内存中的数据都还在,那么此时的这两个读操作就与系统表空间(ibdata1)和redo log(ib_log_fileX)无关了。

图中可以看到:

  1. 读Page1的时候,直接从内存返回。有同学之前问到,WAL之后读数据,是不是一定要读盘,是不是一定要从redo log里面把数据更新以后才可以返回?其实是不用的。你可以看一下图3的状态,虽然磁盘上还是之前的数据,但是这里直接从内存返回结果,结果是正确的。
  2. 要读Page 2的时候,需要把Page 2从磁盘读入内存中,然后应用change buffer里面的操作日志,生成一个正确的版本并返回结果。

可以看到,直到需要读Page 2的时候,这个数据页才会被读入内存。

wal机制可以将数据按顺序写到redo log(减少随机写对io的消耗)对普通索引上的数据进行修改,通常是将修改动作记录到change buffer(减少随机读磁盘的消耗),待查询时如果数据页不在内存,先读取到内存,再将change buffer中的修改动作通过merge操作,合并数据。

  1. 如果业务要求有唯一键。业务正确性优先,文章的前提是业务代码已经保证不会写入重复数据,在这情况下,讨论性能问题。如果业务不能保证,或者业务就是要求数据库来做约束,那么没得选,必须创建唯一索引。这种下,如果碰上了大量插入数据慢、内存命中率低的时候,可以给你多提供一个排查思路。
  2. 然后,在一些“归档库”的场景,你是可以考虑使用普通索引的。比如,线上数据只需要保留半年,然后历史数据保存在归档库。这时候,归档数据已经是确保没有唯一键冲突了。要提高归档效率,可以考虑把表里面的唯一索引改成普通索引。