阅读 453

MySQL笔记:普通索引和唯一索引

普通索引和唯一索引

选择普通索引还是唯一索引,需要从查询和更新两方面考虑。

1. 查询操作

索引上的查询,是从根结点开始,按层搜索到叶子节点,即所谓的数据页,然后在数据页内部使用二分法定位记录。

  • 对于普通索引,先查找到满足条件的第一个记录,然后查找下一个记录,直到不满足条件。
  • 对于唯一索引,只要查找到满足条件的第一个记录,就会停止继续检索。

InnoDB数据是以页为单位从磁盘读写,数据页默认16k

对于普通索引,多做的一次查找和判断下一条记录操作,往往是在内存中进行。一次寻址和一次计算的消耗很小。

可以认为,普通索引和唯一索引的查询操作,性能差异可以忽略。

2. 更新操作

change buffer

更新一个数据时,如果数据在内存中就直接更新。

如果不在内存中,就会将更新操作缓存在change buffer。查询操作访问该数据页时,会将数据页读入内存,然后对change buffer进行merge

发生merge的时刻:

  • 访问数据页
  • 后台定期merge
  • 数据库正常关闭时

merge过程:

  • 从磁盘读取数据页
  • 应用该数据页的change buffer记录,得到新版数据页
  • redo log,包含了change buffer的变更和数据的变更

优点:

  • 减少读磁盘
  • 数据读入内存会占用buffer pool,所以可避免占用buffer pool,提高内存利用率

唯一索引无法使用change buffer:

唯一索引需要判断唯一性约束,因此需要将数据页读入内存。这时比起使用change buffer,直接更新内存更快,

innodb_change_buffer_max_size

change buffer使用的是buffer pool里的内存。

大小通过innodb_change_buffer_max_size参数控制。

比如设为50,表示change buffer最多只能占用buffer pool的50%。

插入新行

如果记录在内存中

对于唯一索引来说,需要判断唯一性冲突;对于普通索引来说,可以直接插入。多一个判断对性能基本没影响。

如果记录不在内存中

对于唯一索引来说,需要将数据页读入内存,从而判断唯一性。

对于普通索引来说,将更新记录记在change buffer中即可。

change buffer使用场景

普通索引才适用。

对于写多读少的业务,写完立马读取的概率小,可使用。

对于读取频繁的业务,写完之后马上读取,不会减少随机访问IO的次数,还增加了change buffer的维护代价。

redo logchange buffer

redo log节省随机写磁盘的IO消耗,转成顺序写。

change buffer节省的是随机读磁盘的IO消耗。

文章分类
后端
文章标签