唯一索引和普通索引怎么选择

前段时间还在忙,终于又出点空更新了,虽然不是所有的付出都有收获,很多时候需要我们先付出才可以看到希望。

一 前言

Mysql 类的文章看的人比较少,我想一方面可能是大家更热衷于比较前言的技术,像 Mysql 类的已经发展很久的基础数据库,关注的人反而少,当时我在学习 Mysql 的过程中发现,Mysql 的很多优秀的技术在大数据很多开源框架中都能看到影子,所以还是按照笔记的方式把这个系列写完吧,当然也会穿插些其他内容。

二 选择标准

其实,如果业务上就要求我们数据库的值必须是唯一的,那没什么好讨论的,就选择唯一索引;那么如果业务上要求不严格,或者说不需要我们数据库后台来保障唯一性要求,这时候我们选择唯一索引还是普通索引就看谁在性能上更好,谁好选谁。

三 性能比较

3.1 查询性能比较

对于数据库查询来说,以前介绍过,InnoDB 引擎的索引以 B+树这种数据结构保存的,我们在利用索引查询的时候,先从 B+树的叶子节点进行按层搜索,定位到我们数据在的数据页,数据页内基本按照二分法查找我们具体要查找的数据。

  • 普通索引: 这时候,我们查找到满足条件的值后,需要进一步查找,直到不满足条件为止。
  • 唯一索引: 我们查找到需要查找的值后,由于唯一索引,所以只有一个值,所以可以直接返回。 从这个角度来看,唯一索引更快。但是影响并不大,是因为我们读取数据的时候都是按照数据页去读取的, 一个数据页默认大小为 64K,可以存上千个索引值。那普通索引的移动指针到下一个元素和比较的数据都是在内存中的,所以影响比较小。

3.2 更新性能比较

我们想一下,Mysql 的数据包含两个部分,一部分在内存中,一部分在磁盘上,在内存中的不光是 Mysql 的数据还有索引。 那么我们在更新的时候,如果数据在内存里面好说,直接更新,定期刷新到磁盘,但是更多的时候可能是数据不在内存中,如果每次都从磁盘读取数据所在的数据页,然后去操作,就需要至少涉及到一次磁盘的随机读操作,比较昂贵的操作。

那么我们是否可以在内存里面中把这个更新操作纪录下来,在合适的时候再将数据合并到磁盘上。这块内存在 Mysql 中就叫 change buffer,(看到 buffer,可能就会认为只存在内存中,实际上 change buffer 不光在内存中也存在磁盘上,同步到数据库的系统表空间 ibdata1),更新操作的时候,数据如果不在内存中,且不影响数据(比如不影响数据的唯一性的情况下),我们把更新操作纪录到 change buffer 上,并不需要从磁盘上读取数据页。这时候,如果来了一个查询动作,刚好要查这条数据的情况下,Mysql 会从磁盘上读取这个数据页,然后发现 change buffer 有修改了这个数据页,会将这个数据页修改的内容 merge 到这个数据页上。 查询配置如下:

mysql> show variables like ‘%innodb_change_buffer%‘;
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
| innodb_change_buffering       | all   |
+-------------------------------+-------+
2 rows in set, 1 warning (0.03 sec)
mysql> show variables like ‘%innodb_buffer_pool_size%‘;
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+
1 row in set, 1 warning (0.01 sec)

说明下:

  1. innodb_change_buffer 为 change_buffer 占 innodb_buffer_pool_size 中的百分比,这里面 25%最大为 50%,我这个本机配置实在是太低了。
  2. innodb_change_buffering 表示哪些场景用 change_buffer 取值:all/none/inserts/deletes

那在更新数据的时候,数据库是如何操作的,总体来说分两种清空,要操作的数据所在的数据页在内存中和不在内存中。 数据页在内存中:

  • 如果是普通索引,则直接更新内存中的数据。(这里简化了很多还涉及到 redolog 和 binlog 等)
  • 如果是唯一索引,碰巧要做的是插入操作,则需要将需要插入的字段值和数据页中的比较看是否存在,决定是否可以插入。 两种操作的耗时差别很小。

数据页不在内存中:

  • 如果是普通索引,则在 change buffer 中记录对那个数据页做了什么样的修改。
  • 如果是唯一索引,则需要将数据页读取到内存中,判断是否满足唯一性约束,数据已经读入内存了,这时候肯定不会再适用 change buffer 了,因为已经多了一次 IO 的随机读了。

所以从更新角度看,普通索引可以利用 change buffer 更新操作的性能比唯一索引要更好。 这里面要说明的是有些人可能会认为像插入操作,我们需要一个主键,主键是唯一索引,所以插入操作是用不到 change buffer,这样是不对的,因为一个表一般除了主键还有二级索引,主键用不到,二级索引可以用到 change buffer。

四 Change buffer 适用场景

4.1 Change buffer 不适用场景

不是所有的场合都适合使用 change buffer 的,change buffer 的本质是通过减少磁盘的随机 IO 读的访问来提升系统的性能。

  • 如果一个数据在写入后,经常需要立刻读出来,那么我们并不能降低随机读,而且还会增加 change buffer 的操作负担,所以并不适合。
  • 如果数据库的数据都只有主键,或只有唯一索引,也不合适。

4.2 Change buffer 适用场景

  • 如果我们的数据是读少,写多的,比如日志数据。
  • 还有就是我们系统中的历史库,几乎不会再读取数据了,却需要随时搬迁历史数据到此库中,肯定没有唯一冲突了,可以考虑把唯一索引改成普通索引,以提升搬迁性能。
江城子·墨云拖雨过西楼
[宋] 苏轼
墨云拖雨过西楼。水东流。晚烟收。
柳外残阳,回照动帘钩。
今夜巫山真个好,花未落,酒新篘。
美人微笑转星眸。月花羞。捧金瓯。
歌扇萦风,吹散一春愁。
试问江南诸伴侣,谁似我,醉扬州。

相关推荐