专栏首页洁癖是一只狗Mysql如何选择唯一索引和普通索引

Mysql如何选择唯一索引和普通索引

相信大家对唯一索引和普通索引是有一定的了解的,那么在不同的业务场景,使用唯一索引还是普通索引呢,比如下面的场景

假设你在维护一个账户系统,每一个人都有一个唯一的身份证,而业务也能保证他的唯一性,此时我们设置唯一索引和普通索引其实都是可以的,但是在性能上他们有什么差异吗

select name from user where id_card='6102222xxxxxx'

那么我如何分析性能问题呢,我针对查询和更新两方面进行分析

查询过程

假设插叙的语句是select id from T where k=5,这个查询语句在索引树上查询的过程,先是通过B+树树根查询,找到叶子节点,也就是上图的右下角的数据页,然后通过数据页内容通过二分法定位记录

  • 对于普通索引,查询到满足的而第一个记录后,需要向后继续寻找,直到不满足条件
  • 对于唯一索引,由于索引的唯一性,查询到数据后,直接停止查询

上面的不同索引带来的性能差异是微乎其微的

一般InnoDB的数据是按数据页为单位来读写的,也就是说,当需要读取一条记录的时候,并不是把这个记录从磁盘读取出来,而是以页为单位整体的读入内存,innoDB的页的默认大小是16k.

由于是按照页的方式读取数据的,当k=5的时候,且数据页在内存中,只需要在内存中获取就可以了,对于普通索引,要多做一次"查询判断"操作,只需要一次指针寻找和一次计算,

但是如果内存中的数据页不包k=5的数据需要获取下一个数据页,这就比较复杂了.一个数据也一般可以存储上千个key,这种的概率也比较小,一般是可以忽略不记的。

更新过程

在说明更新过程之前,我们必须知道一个概念那就是change buffer.'

当需要更新一个数据页时,如果数据页在内存中,就直接把更新操作缓存在change buffer中,这样就不需要从磁盘读取这个数据页了,在下次查询的时候,将数据也从磁盘读取到内存中,然后执行change buffer和这个也有关的操作,通过这种方式保障数据的正确性

需要说明的是,虽然名字叫做change buffer,实际上他是可以持久化的数据,也就说,change buffer在内存中有拷贝,也是会写入磁盘的,将change buffer 中的操作应用到原数据页,得到最新的结果的过程叫做merge,除了这个数据页触发merge外,系统的后台定期会merge,在数据库正常关闭的时候,也会执行merge操作。

那么什么时候可以使用change buffer呢

对于唯一索引,所有的更新操作都要判断这个操作是否违反唯一性约束,比如要插入(4,400)记录,就要判断表中是否已经存在k=4的记录,而这将必须把数据也读取到内存中,既然已经在内存中了,我们直接更新内存会更快,就没有必要使用change buffer了,因此唯一索引的更新是不会使用change buffer,只有普通索引可以使用.

change buffer 使用的buffer pool的内存大小,因此不能无限增大,可以使用innodb_change_buffer_size来动态设置,这个参数设置为50表示change buffer最大使用buffer pool的50%。

以上就是change buffer基本原理,现在我们看看更新操作,插入(4,400)记录,分为两种情况

第一种是更新的记录在内存中

  • 对于唯一索引找到3和5之间的位置,判断没有冲突就插入这个值,语句结束
  • 对于普通索引找到3和5之间的位置,插入这个值,语句结束

这种情况差距就是判断冲突的操作,影响差别不大

第二种更新记录不在内存中

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

减少从磁盘读入内存以及随机IO访问,是数据库性能提高的操作之一,而change buffer就是在较少随机访问磁盘的操作,因此对性能的提高是很明显的。

change buffer使用场景

从上面分析我可以知道cahnge buffer对于唯一索引不起作用,只能使用在普通中,问题是普通索引中一定会起到加速作用吗

因为merge的时候是整整进行数据更新的时候,而change buffer主要目的就是记录更新操作,所以在一次merge之前,change buffer 的记录越大,起到的作用就越好,因此,对于写多读少的业务场景,使用change buffer的效果是最好的,

反过来,如果在更新完成之后,立马就进行查询,那么即使满足了条件,把更新的操作记录在change buffer中,但是随后就会进行查询,导致merge操作,这样随机访问IO次数不会减少,反而增加了change buffer的维护成本,做一这种业务场景,change buffer反而起到副作用。

索引的选择和实践

普通索引和唯一索引选择,其实,这类索引在查询能力上是没有差别,主要考虑的是对更新性能的影响,所以建议选择普通索引。

而在更新操作之后,马上就会执行查询操作,因此此时应该关闭change buffer,而在其他情况下,change buffer都能提高性能.

change buffer和redo log

WAL提高性能的核心机制,也的确是尽量减少随机读写,redo log 和change buffer 都有可以提高性能的作用,往往比较容易混淆.

如下面插入语句

insert into t(id,k) values(id1,k1),(id2,k2);

这里,我们假设当前k索引树的状态,查找到位置后,K1所在的数据页在内存中,k2所在的数据页不在内存中.

分析这条更新语句,你会发现他涉及四部分:内存,redo log ,数据表空间,系统表空间

这条更新语句做了如下操作

  1. Page1在内存中,直接更新内存
  2. Page2没有在内存中,就直接在内存中的change bufffer区域,记录下想page2插入这一行的信息
  3. 将上述两个动作计入redo log中

上面操作完,事物也就结束,索引你会看到,执行这条语句成本很低,就是写了两处内存,然后写入一下磁盘(两次操作合在一起操作写入磁盘),而且是顺序写的。

此时如果,我们要进行select * from t where k in(k1,k2),如果读语句发生在更新语句后不久,内存中的数据还存在,那么此时的这两个读操作就与系统表空间,和redo log无关了。而此时他们的顺序

  1. 读取page1的时候,直接从内存返回。而不需要读取磁盘,不需要把redo log的数据更新之后才可以返回,其实直接从内存中获取的数据就是正确的
  2. 要读取page2的时候,需要把page2从磁盘读入内存中,然后应用change buffer 里面的操作日志,生成一个正确的版本并返回结果。

可以看到,直到需要读page2的时候,这个数据页才会被读入内存。

所以,如果简单的对比这两个机制(change buffer,redo log)在提高性能上的收益的话,redo log主要节省的是随机写磁盘的IO消耗,而change buffer主要节省的则是随机读磁盘的IO消耗。

本文分享自微信公众号 - 洁癖是一只狗(rookie-dog),作者:洁癖汪

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-09-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 面试java内存模型

    今天我们讲一下java内存模型(JMM),JMM的问题在面试中经常被问到,我们今天就讲一下这道题.

    小土豆Yuki
  • Mysql索引解密(下)

    上面ID索引树进行查找记录的过程叫回表,可以看出k树索引树进行了三次查询,Id索引树进行了两次查询。查询数据过程中是否可以避免回表查询呢,

    小土豆Yuki
  • Mysql数据--死锁解密

    Mysql行锁是在引擎中实现的,并不是所有的存储引擎都支持行锁,比如myisam就不支持行锁,而innodb支持行锁,myisam在并发度高的系统中就会影响系统...

    小土豆Yuki
  • 数据库原理: Change Buffer 是干什么的?

    redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的是随机读磁盘的IO消耗。

    王小明_HIT
  • input输入框change和blur事件区别

    blur与change事件在绝大部分的情况下表现都非常相似,输入结束后,离开输入框,会先后触发change与blur,唯有两点例外。

    Daotin
  • 案例 - 某公司薪酬体系数据分析

    今天拿到了一个公司的人事体系的报表,里面有个薪酬体系的数据,我们就拿这个案例来分析分析这家公司的薪酬体系,首先我们来看下该公司的薪酬体系。

    王佩军
  • 北上广深差不多的薪酬,哪里过得更潇洒?

    作者 孙竞 本文为原创作品,转载需授权 有这样一群年轻人,他们即使过得异常艰苦,远离亲人,仍然义无反顾选择来北上广深打拼。北上广深哪个城市会让我们过的更潇洒一...

    CDA数据分析师
  • 用数据分析来探讨 北上广深哪个城市过得更潇洒

    北上广深,似乎已成了一个沉重的话题,它代表着无数功成名就的梦想和不知所措的未来。那里,有的是机遇与资源,与之相伴的是源源不断的压力和挑战。有这样一群年轻人,他...

    灯塔大数据
  • R语言之可视化③点图续

    函数scale_x_discrete可用于将项目的顺序更改为“2”,“0.5”,“1”:

    用户1359560
  • 【FinTech】机器学习是发展金融科技公司的最佳方式吗?

    今天,机器学习在金融生态系统的许多阶段扮演着不可或缺的角色。 从审批贷款到管理资产,评估风险。 然而,只有少数技术娴熟的专业人员能准确了解ML如何进入日常金融生...

    陆勤_数据人网

扫码关注云+社区

领取腾讯云代金券