MySQL 笔记整理(9) --普通索引和唯一索引,应该怎么选择?

笔记记录自林晓斌(丁奇)老师的《MySQL实战45讲》 (本篇内图片均来自丁奇老师的讲解,如有侵权,请联系我删除) 9) --普通索引和唯一索引,应该怎么选择?   假如你在维护一个市民系统...

笔记记录自林晓斌(丁奇)老师的《MySQL实战45讲》

(本篇内图片均来自丁奇老师的讲解,如有侵权,请联系我删除)

9) --普通索引和唯一索引,应该怎么选择?

  假如你在维护一个市民系统,每个人都有唯一的身份证号,而且业务代码也已经保证了不会写入两个相同的身份证号。如果需要按身份证号来查找,你可能会执行类似这样的SQL语句:

select name from CUser where id_card = 'xxxxxxxxyyyyyzzz';

  由于身份证号id_card字段较长,不建议将身份证号当做主键,那么现在你有两个选择。要么给id_card建立一个普通索引,要么给它建立一个唯一索引。由于业务代码已经保证了身份证号的唯一性,这两个选择在逻辑上都是正确的。那么,从性能角度来考虑,你会选择普通索引还是唯一索引呢?为什么呢?

  前面我们提到过,在InnoDB中,索引是以B+树的形式存在的。查询语句在索引树的查找过程,先是通过B+树从树根开始,按层搜索到叶子节点,然后再进行判断.(当然如果查询条件比较多,可能需要回表,具体请参考之前的文章。)

  •   对于普通索引来说,查找到满足条件的id_card之后,需要再去查找下一个记录,直到碰到id_card不符合要求的记录。
  •   对于唯一索引,由于索引保证了唯一性,查找到第一个符合条件的id_card之后就会立即返回,不再进行检索了。

  那么这两个索引之间的不同带来的性能差异会有多少呢?答案是,微乎其微。InnoDB的数据是按数据页为单位来读写的。也就是说,当需要一条记录的时候,并不是将这个记录本身从磁盘读出来,而是以页为单位,将其整体读入内存。在InnoDB中,每个数据页的大小默认是16KB。因为引擎是按页读写的,所以,当找到符合id_card的记录的时候,它所在的数据页就都在内存中了。那么,对于普通索引来说,只不过是多做一次“查找和判断下一条记录”的操作,只需要一次指针寻找和一次计算。当然,如果这条记录恰好是这个数据页的最后一条,那么就必须读取下一个数据页才能进行判断。但,一个数据页可以放下很多个key,这种情况出现的概率会很低。所以,对于查询来说,这两种索引的性能差异是不大的。

更新过程:

  这里,我们需要先介绍一下Change buffer。当需要更新一个数据页的时候,如果这个数据页在内存中就直接更新,如果这个数据页不在内存中,在不影响数据一致性的前提下,InnoDB会将这些更新操作缓存在change buffer中,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存中,然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。需要说明的是,虽然名叫Change buffer,实际上它是可以持久化的数据。也就是说,change buffer在内存中有拷贝,也会被写入到磁盘上。

  将Change buffer中的操作应该用到原数据页,得到最新结果的过程叫做merge.除了访问这个数据页会触发merge外,系统后台线程会定期merge。在数据库正常关闭(shutdown)的过程中,也会执行merge操作。显然,如果能够将更新操作先记录在change buffer,减少读磁盘,语句的执行速度会得到明显的提升。而且,数据读入内存是需要占用buffer pool的,所以这种方式还能避免占用内存,提高内存利用率。那么,什么条件下可以使用change buffer呢?

  对于唯一索引来说,所有的更新操作都要先判断这个操作是否违反了唯一性约束。比如要插入某条记录,就要先判断现在在表中是否已经存在了索引相同的记录,而这必须要将数据页读入到内存中才能进行判断。如果都已经读入到内存中了,那么直接更新内存会更快,没必要使用change buffer了。因此,唯一索引的更新就不能使用change buffer,实际上也只有普通索引可以使用?

  change buffer用的是buffer pool里的内存,因此不能无限增大。change buffer的大小,可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候,表示change_buffer的大小最多只能占用buffer pool的50%。那么,如果要在一张表里插入一条新记录的时候InnoDB的处理流程是怎样的呢?

  第一种情况,这个记录要更新的目标在内存中。这时,InnoDB的处理流程如下:

  •   对于唯一索引来说,找到要插入的位置,判断有没有冲突,插入这个值,语句执行结束
  •   对于普通索引来说,找到要插入的位置,插入这个值,语句执行结束。

  这样看来,两种方式的差别知识一个判断,影响很小。我们再来看看第二种情况,这个记录要更新的目标不再内存中,这时,处理的流程如下:

  •   对于唯一索引,需要将数据页读入内存,判断有没有冲突,插入这个值,语句执行结束。
  •   对于普通索引,将更新记录在change buffer,语句执行结束。

  将数据从磁盘读入内存涉及随机IO访问,是数据库里成本最高的操作之一。Change buffer因为减少了随机磁盘访问,所以对更新的提升是明显的。

索引的选择和实践:

  这两类索引再查询能力上是没差别的,主要考虑的是对更新性能的影响。所以建议尽量选择普通索引。如果所有的更新后面,都马上伴随着对这个记录的查询,那么你应该关闭change buffer.而在其他情况下,change buffer都能提升更新性能。实际上,普通索引和change buffer的配合使用,对于数据量大的表的更新优化是很明显的。

上期问题:

  上期问题可以点击这里查看 。两种方式为:

  

  

问题:

  change buffer一开始是写内存的,那么如果这个时候及其掉电重启,会不会导致change buffer丢失呢?change buffer丢失可不是小事,因为丢失以后就无法再进行merge了,等于是数据丢失了,会不会出现这种情况呢?

  

  • 发表于 2019-03-11 11:20
  • 阅读 ( 287 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除