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的大小。