MySQL普通索引和唯一索引的深入讲解


场景
1、维护一个市民系统,有一个字段为身份证号
2、业务代码能保证不会写入两个重复的身份证号(如果业务无法保证,可以依赖数据库的唯一索引来进行约束)
3、常用SQL查询语句:SELECT name FROM CUser WHERE id_card = ‘XXX’
4、建立索引
假设字段k上的值都不重复
查询过程
1、查询语句:SELECT id FROM T WHERE k=5
2、查询过程
3、针对普通索引
4、针对唯一索引
性能差异
1、性能差异:微乎其微
2、InnoDB的数据是按照数据页为单位进行读写的,默认为16KB
3、当需要读取一条记录时,并不是将这个记录本身从磁盘读出来,而是以数据页为单位进行读取的
4、当找到k=5的记录时,它所在的数据页都已经在内存里了
5、对于普通索引而言,只需要多一次指针寻找和多一次计算 – CPU消耗很低
change buffer
1、当需要更新一个数据页时,如果数据页在内存中就直接更新2、如果这个数据页不在内存中,在不影响数据一致性的前提下
3、change buffer是可以持久化的数据,在内存中有拷贝,也会被写入到磁盘上
4、将更新操作先记录在channge buffer,减少随机读磁盘,提升语句的执行速度
5、另外数据页读入内存需要占用buffer pool,使用channge buffer能避免占用内存,提高内存利用率
6、change buffer用到是buffer pool里的内存,不能无限增大,控制参数innodb_change_buffer_max_size
merge
1、merge:将change buffer中的操作应用到原数据页
2、merge的执行过程
3、merge开发云主机域名执行完后,内存中的数据页和change buffer所对应的磁盘页都还没修改,属于脏页
4、触发时机
使用条件
1、对于唯一索引来说,所有的更新操作需要先判断这个操作是否违反唯一性约束
2、唯一索引的更新无法使用change buffer,只有普通索引可以使用change buffer
使用场景
1、一个数据页在merge之前,change buffer记录关于这个数据页的变更越多,收益越大
2、对于写多读少的业务,页面在写完后马上被访问的概率极低,此时change buffer的使用效果最好
3、如果一个业务的更新模式为:写入之后马上会做查询
更新过程
插入(4,400)
目标页在内存中
目标页不在内存中
1、对于唯一索引来说,需要将数据页读入内存,判断没有冲突,插入这个值
对于普通索引来说,将更新操作记录在change buffer即可
索引选择
1、普通索引与唯一索引,在查询性能上并没有太大差异,主要考虑的是更新性能,推荐选择普通索引
2、建议关闭change buffer的场景
change buffer + redolog
更新过程
当前k树的状态:找到对应的位置后,k1所在的数据页Page 1在内存中,k2所在的数据页Page 2不在内存中

1、Page 1在内存中,直接更新内存
2、Page 2不在内存中,在changer buffer中记录:add (id2,k2) to Page 2
3、上述两个动作计入redolog(磁盘顺序写)
4、至此事务完成,执行更新语句的成本很低
5、由于在事务提交时,会把change buffer的操作记录也记录到redolog
虚线为后台操作,不影响更新操作的响应时间
读过程
假设:读语句发生在更新语句后不久,内存中的数据都还在,与系统表空间(ibdata1)和redolog(ib_logfileX)无关
1、读Page 1,直接从内存返回(此时Page 1有可能还是脏页,并未真正落盘)
2、读Page 2,通过磁盘随机读将数据页读入内存,然后应用change buffer里面的操作日志(merge)
提升更新性能
1、redolog:节省随机写磁盘的IO消耗(顺序写)
2、change buffer:节省随机读磁盘的IO消耗
参考资料
《MySQL实战45讲》总结以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对开发云的支持。

相关推荐: MySQL数据库集群实战

一、数据库集群使用场景1.随着访问量的不断增加,单台MySQL数据库服务器压力不断地增加,需要对MySQL进行优化和架构改造,如果MySQL优化不能明显改善压力,可以使用高可用、主从复制、读写分离出来、拆分库、拆分表等方法来进行优化。2.MySQL主从复制集群…

免责声明:本站发布的图片视频文字,以转载和分享为主,文章观点不代表本站立场,本站不承担相关法律责任;如果涉及侵权请联系邮箱:360163164@qq.com举报,并提供相关证据,经查实将立刻删除涉嫌侵权内容。

(0)
打赏 微信扫一扫 微信扫一扫
上一篇 06/10 16:57
下一篇 06/10 16:58

相关推荐