如何选择普通索引和唯一索引?|CSDN博文精选


作者 | .NY&XX

责编 | 郭芮

出品 | CSDN博客

网上已经有很多关于唯一索引和普通索引的区别,这里就不详细阐述了,接下来我们深入讨论如何根据不同业务场景,应该选择普通索引还是唯一索引。比如维护一个社保管理系统,每个社保人员都有一个唯一的身份证号,而且业务代码已经保证了不会写入两个重复的身份证号。如果该系统需要按照身份证号查询姓名,就会执行这样的SQL语句:

select name from suser where id_card = ‘xxxxxxxxxxx’;

所以一般会考虑在id_card 字段上建索引。由于身份证号字段比较大,不适合用来作主键,索引现在有两个选择,要么给id_card字段创建唯一索引,要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号,那么这两个选择逻辑上都是正确的。但是要从性能角度上来考虑,选择的依据应该是什么呢?下面我们就从两种索引对查询过程和更新过程的性能影响来分析。

查询操作

我们来看一下InnoDB索引组织机构,假设执行:

select id from t where a=3

这个查询语句在索引树上查找的过程将如下:

  • 先是通过B+树从树根开始,按层序遍历的方式搜索到叶子节点,从而定位数据页。

  • 通过二分查找来定位记录。

唯一索引而言,查找到满足条件的第一个条目(比如 (3,300))后就会停止继续检索。普通索引查找到一个满足条件的条目后将会继续查找,直到碰到第一个不满足a=3条件的条目。

它们的不同所带来的性能差距却是微乎其微的。因为InnoDB中是按数据页为单位来读写的,也就是说,当读取一个条目的时候并不是将条目从磁盘读出来,而是以页为单位,整体读入内存。既然存储引擎是按页读写的,所以说当找到a=3的条目时,它所在的数据页已经在内存里了。那么对于普通索引需要多做的“查找以及判断条目是否满足条件”的操作就只需要一次指针操作及计算。

更新操作

当需要更新一个数据页时,如果数据页在内存缓冲池(buffer pool)中就直接更新,并同时记录redo log,但是如果这个数据页不在内存中的话。在不影响一致性的前提下,InnoDB会将更新操作缓存在写缓冲(change buffer)中,同时记录redo log。

写缓冲(change buffer)

那什么是change buffer呢?

它的主要目的是将对二级索引的数据操作缓存下来,以此减少二级索引的随机IO,并达到操作合并的效果。

在MySQL5.5之前的版本中,由于只支持缓存insert操作,所以最初叫做insert buffer,只是后来的版本中支持了更多的操作类型(操作类型包括insert、update、delete)缓存,才改叫change buffer。

change buffer的数据结构上是一颗b+树,存储在ibdata系统表空间中,根页为ibdata的第4个page(FSP_IBUF_TREE_ROOT_PAGE_NO)。

将change buffer中的操作应用到原数据页从而得到最新结果的过程被称为merge。merge 的时候才是是真正进行数据更新的时刻,change buffer 将条目的变更动作进行缓存。在一个数据页做 merge 之前,change buffer 记录的变更越多(也就是这个页面上要更新的次数越多),收益就越大。

一般来说,触发merge的操作主要有以下几种:

  • 访问这个数据页;

  • master thread线程每秒或每10秒进行一次merge insert buffer的操作;

  • 在数据库正常关闭的时候。

此外,虽然名字叫做change buffer,但实际上它是可以持久化的数据,也就说它在内存中有拷贝,也会被写入到磁盘上。

change buffer状态查看

  • seg size 为插入缓冲区的总大小(页的数量X16KB);

  • merges表示已经合并的merge的数量;

  • merged operations: insert 插入记录被merge的次数;

  • delete mark 删除操作被merge的次数;

  • delete 更新操作被merge了多少次。

change buffer占用buffer pool

数据读入内存是需要占用buffer pool的,采用这种方式能够避免占用内存,提升内存利用率。

change buffer用的是buffer pool的内存,因此不能无线增大,它通过参数innodb_change_buffer_max_size来设置,这个参数表示占用内存的比例,默认是25%,最大值为50%,一般在写多读少的场景下才需要设置。

change buffer带来什么好处?

如果MySQL承担大量的DML操作,则change buffer是必不可少的,他的存在就是尽量减小I/O的消耗,通过内存进行数据的合并操作,将多次操作操作尽量变为少量的I/O操作,从而提升了更新操作的速度。

什么场景适合开启change buffer?

change buffer只限于普通索引的场景下,不适用与唯一索引。为什么呢?

因为,假设要插入(3, 300)这个条目,首先要判断这个条目是否在表中出现过。而这必须要将数据页读入内存才能判断。如果都已经读入到内存了,那直接更新内存会更快,就没必要使用 change buffer 了。

那么InnoDB中插入的条目(3,300)的流程是如何的呢?

如果这个条目要更新的数据页在内存中:

  • 对于唯一索引,找到2和4的位置,判断没有冲突后,插入这个值,执行结束

  • 对于普通索引,找到2和4的位置,插入这个值,执行结束

如果这个条目要更新的数据页不在内存中:

  • 对于唯一索引,需要将数据页读入内存,然后判断有没有冲突,然后进行插入。

  • 对于普通索引,只需要将条目更新操作记录在change buffer就执行结束了。

不是所有场景都可以用change buffer

普通索引并不是所有场景使用change buffer都能受益,对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时 change buffer 的使用效果最好。

但是假设一个业务的更新模式是写入之后马上会做查询,那么即使满足了条件,将更新先记录在change buffer,但之后由于马上要访问这个数据页,会立即触发 merge 过程。这样随机访问 IO 的次数不会减少,反而增加了 change buffer 的维护代价。所以,对于这样类似的业务模式来说,change buffer 反而起到了副作用。

举个例子:

假设要执行insert into t values(id1,a1),(id2,a2);

假设a1 所在的数据页在内存 (InnoDB buffer pool) 中,a2 所在的数据页不在的话,如图所示:

  1. 如果a1 所在的Page1 在内存中,则直接更新内存;

  2. 如果a2 所在的Page2 没有在内存中,则在change buffer中记录下“要往 Page2 插入一行”这个信息;

  3. 将更新Page1这个动作记入到redo log 中;

  4. 将change buffer记录插入信息这个动作记入到redo log中。

第3、4写redo log的两次操作合在一起写磁盘。所以从执行过程中可以发现, 执行这条更新语句的成本很低,只写了两处内存,而且还是顺序写的。图中的两个红色箭头,都是后台操作(空闲时或者必须时写入磁盘),不影响更新的响应时间。

那么在之后的读请求该怎么处理呢,比如我们要执行select * from t where a in (a1, a2);

a1 本来就在内存中, 之前内存也更新了, 所以直接从内存返回。

读取Page2的时候,需要把Page2从磁盘读入内存,然后结合change buffer里面的操作日志生成一个新版本并返回结果。

总结

普通索引和唯一索引在查询能力上是没差别的,主要考虑的是更新的影响。一般建议使用普通索引。特别是在使用机械盘的场景下,尽量把change buffer开大从而确保数据的写入速度。

声明:本文为CSDN博主「.NY&XX」的原创文章,原文链接:https://blog.csdn.net/songguangfan/article/details/103059623。

想为博主点赞?

想要请教博主?

扫描下方二维码,快速获取与博主直面沟通的方式吧!

你点的每个“在看”,我都认真当成了喜欢

©️2020 CSDN 皮肤主题: 代码科技 设计师: Amelia_0503 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值