Scott's world.

MySQL-普通索引与唯一索引笔记

Word count: 2.2kReading time: 7 min
2019/11/10 Share

MySQL-普通索引与唯一索引笔记

在前面我们已经接触过了索引的基本概念,也了解了什么是唯一索引和普通索引的区别。

今天这篇文章,就主要来谈谈在更具体的业务场景中,应该选择唯一索引还是普通索引?

下面在业务代码已经保证了不会写入两个重复数据,我们来讨论着两种索引对查询语句和更新语句的性能影响来进行分析

查询过程

查询语句是在索引树上查找的过程,先是通过B+树从树根开始,树层索引到叶子节点,也就是数据页,然后可以认为数据页内部通过二分法来定位记录。

  • 对于普通索引,查找到满足条件的第一个记录之后,需要查找下一个记录,直到碰到第一个不满足查询条件的记录
  • 对于唯一索引,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索

那么这个不同带来的性能差距其实是微乎其微。

InnoDB的数据是按数据页为单位来读写的,在需要读一条记录的时候并不是将这个记录本身从磁盘读出来而是以页为单位将其整体读入内存,最后存入内存的数据页中查找。

在InnoDB中,每个数据页的大小默认是16KB

在上面的查询语句中,在开始的时候数据页已经在内存中或者从磁盘中读入内存中,对于普通索引来说,要多做的就是那一次“查找和判断下一条记录”的操作,也就是需要一次指针寻址和一次计算,但是如果查询的那一行刚好是数据页的最后一条,那么因为要取下一个记录就必须读取下一个数据页,但是这一情况出现的概率比较小,所以在计算平均性能差异时仍可认为这个操作成本对于现在的CPU来说忽略不计。

更新过程

概念:change buffer

  • 当需要更新一个数据页时,若它在内存中则直接更新,若不在则不影响数据一致性的前提下,InnoDB会将这些更新操作缓存到change buffer中,这就不需要从磁盘中读入。那么在下一次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中与这个页有关的操作。这样就能保证这个数据逻辑的正确性

change buffer实际上是可以持久化的数据,也就是它在内存中有拷贝,也会被写入到磁盘上。

  • 而将change buffer中的操作应用到原数据页得到最新结果的过程称为merge

除了访问这个数据页会触发merge之外,系统也会后台线程定期merg,在正常关闭时也会执行该操作。

显然,通过change buffer可以减少读磁盘,语句的执行速度就会得到明显提升,而且数据读入内存是需要占用buffer pool的,所以这种方式还能够避免占用内存,提高内存利用率。

将数据从磁盘读入内存涉及随机IO的访问,是数据库里面成本最高的操作之一。

change buffer使用条件

实际上只有普通索引能够使用change buffer,而唯一索引因为在执行语句之前要判断是否唯一则数据页必须读入内存,所以就没必要使用change buffer了。

change buffer用的是buffer pool里的内存,因此不能无限增大

可以通过参数innodb_change_buffer_max_size来动态设置其大小

change buffer使用场景

前面说到执行merge操作是真正要进行数据更新的时刻,而change buffer的主要目的就是将记录的变更动作缓存下来,所以在一个数据页merge之前,change buffer记录的变更越多,收益就越大

因此,对于写多读少的业务来说,页面在写完后马上被访问到的概率比较小,所以此时用change buffer效果最好,一般业务模型常见的就是账单类、日志类的系统。

所以change buffer不适用在页面写完就需要马上做查询的,这样merge操作频率很高,这样不仅不会减少随机IO次数,而且change buffer的维护代价也会很高。

索引选择和实践

前面我们也说到,这两种索引在查询能力上是没差别的,主要考虑的是对更新性能的影响,所以我们尽量选择普通索引。

change buffer的不适用场景有限,但其适用场景都是非常常见的,能够提升更新性能。

而且在实际使用中,在普通索引和change buffer配合使用在数据量大的表的更新优化是非常明显的

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

change buffer和redo log

change buffer和redo log虽然都是尽量减少随机读写,但是这其中的差别还是挺大的。

比如执行更新操作的时候,流程一般:

  • 若数据页在内存中,直接更新内存;
  • 若数据页不在内存中,则将其插入语句存入change buffer中;
  • 将上述两个动作记入redo log中。

在上述更新操作完成之后,如果我们想再执行查询操作,就可以看到,在内存页中的数据则直接从内存返回结果,若不在内存中的,则在从磁盘读入内存过程中,直接应用change buffer里面的操作日志,生成一个正确的版本并返回结果,这其中就与系统表空间和redo log无关。

系统表空间就是用来放系统信息的,比如数据字典什么的,对应的磁盘文件是ibdata1,
数据表空间就是一个个的表数据文件,对应的磁盘文件就是 表名.ibd

如果简单地对比这两个机制在提升更新性能上的收益的话,redo log主要节省的是随机写磁盘的IO消耗(转成顺序写),而change buffer主要节省的是随机读磁盘的IO消耗。

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

问题

change buffer一开始是写内存的,那么如果这个时候机器掉电重启,会不会导致change buffer丢失呢?change buffer丢失可不是小事儿,再从磁盘读入数据可就没有了merge过程,就等于是数据丢失了。会不会出现这种情况呢?

分析

不会导致change buffer丢失。因为在更改change buffer 时也会写redo log,也需要持久化。
change buffer 更新完成并且相应事务提交的情况下,首先要保证redo log落盘(二阶段提交),若此时掉电重启,则可以根据 redo 进行恢复;
若change buffer 更新完成但是相应事务未提交的情况下,则redo 有可能落盘了(redo 的组提交),也有可能未落盘,若落盘了,读取redo发现没有commit标志(还会进行lsn,binlog的对比),则回滚;若redo未落盘则也就不会出现前滚和回滚的情况,数据依旧一致。

CATALOG
  1. 1. MySQL-普通索引与唯一索引笔记
    1. 1.1. 查询过程
    2. 1.2. 更新过程
      1. 1.2.1. change buffer使用条件
      2. 1.2.2. change buffer使用场景
    3. 1.3. 索引选择和实践
    4. 1.4. change buffer和redo log
    5. 1.5. 问题
      1. 1.5.1. 分析