09普通索引和唯一索引:如何选择

Posted 2021-04-27 12:34 +0800 by ZhangJie ‐ 1 min read

分享:  

对比两种类型的索引

  • 普通索引,允许多条记录中组成索引的字段值出现重复的情况;
  • 唯一索引,不允许……

两种类型索引实现

肯定都是一样的啊

两种类型索引效率

我们以表user为例:

create table `user` (
	id int auto_increment,
	id_card varchar(64),
	name varchar(32),
	primary key(id),
	[uique|index] (id_card) -- 创建索引:唯一索引或者普通索引
)

其中id_card可能是唯一索引,也可能是普通索引。

查询效率

以这条查询语句为例:select name from user where id_card=?

  • 普通索引的查询

    顺着B+树根据id_card查询,查询到第一条记录之后,回表查询对应的name,加入结果集。继续遍历向右的指针对应的记录,直到找到第一条id_card不匹配的记录为止。因为id_card肯定是不重复的,所以这里向右的匹配开销顶多也就是多比较一次。

    当然如果匹配到的这条记录如果是page的最后一条记录的话,那么可能向右的查找需要加载另一个page,这是最坏的情况了。

    实际情况是B+树种一个节点可以存储非常多的key和指针,真的出现匹配记录出现在最后一个的情况非常少。

  • 唯一索引的查询

    查找过程也是顺着B+树根据id_card查询,然后再回表。区别是它找到第一个匹配的节点之后就停止向右的查找了,因为它知道是唯一索引,不可能有重复的记录存在。

  • 性能对比

    看上去唯一索引查询性能会高一点,但是前面也分析了id_card本身具备唯一性,普通查询中这种继续向右查找的操作对性能影响开销并不大,微乎其微。所以对于这两种索引,建议使用普通索引来代替唯一索引。

更新效率

更新语句以这个为例:update user set name="xxxx" where id_card=?

  • change buffer

    在mysql执行数据更新时,会先写redo log,然后收到ok后准备更新数据。这个要更新的行对应的页数据如果在内存中,则直接更新内内存中的相应字段就可以了。

    如果这个数据没有在binlog中,也不会立即写入磁盘,而是从从磁盘加载速度比较慢,所以可以将一些更新操作,记录到change buffer中。后面有读数据请求等等时,会触发从磁盘加载文件,加载成功后再应用change buffer中的数据。

  • 普通索引更新

    普通索引更新的时候,基本上就是上述说的过程,它是可以使用change buffer的。

  • 唯一索引更新

    唯一索引,这里的唯一意味着每次操作都要判断是否会违反唯一性这个约束。比如要插入一行数据(1,‘id_card’,100),就要判断是否已经有将“id_card”写入的记录。

    类似地,要对id_card=‘xxx’的记录做更新,就要能够找到id_card对应的行数据,这个时候目标行数肯定是要加载到内存中的,所以对应的页一定在内存中。

    这种情况下直接更新内存肯定比更新change buffer要快了,change buffer还受限于buffer pool大小机器设定的可占buffer pool的比例呢,所以这种情况下就没必要使用change buffer了。

    所以唯一索引不使用change buffer。

  • 对比

    实际情况是,唯一索引的更新效率会比普通索引低。因为它必须要将行数据加载到内存中判断并更新,不能向普通索引那样直接写完change buffer就完事了。change buffer中的一些操作,会在后续读取的时候加载完原始数据之后,然后应用change buffer中的操作到原始数据,这个过程称为merge。

changebuffer应用场景

也不是说所有的场景都适合使用changebuffer,如果写多读少的话,就很适用了。反过来的话,读多写少,这种就不适合使用change buffer了,可以考虑禁用。

changebuffer是通过buffer pool分配的,可以设定一个变量来控制change buffer的大小。