我秃了!唯一索引、普通索引我该选谁?

我秃了!唯一索引、普通索引我该选谁?你遇到过不知道如何选择唯一索引和普通索引的场景么?你知道他俩的原理和区别么?来不及了,快上车!!

大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺

小伙伴想精准查找自己想看的MySQL文章?喏 → MySQL江湖路 | 专栏目录
在这里插入图片描述

  提到唯一索引普通索引,相信大家都不陌生,当同事小姐姐问你这俩有什么区别时?或许你会脱口而出:“这还用问?见名知意啊,一个是允许字段重复,一个不允许存在重复数据!”

  是否解决小姐姐的疑问我不知道,但你在同事心目中,肯定不是啥好玩意儿~ 要知道,一眼就看出的答案,一般不会有人问,除非问傻子~

那么当你处理一张市民信息表时,其中一列为市民的身份证号信息,你会怎么选择哪个索引?为什么?

  对于一个经历过风风雨雨、日日夜夜的程序员来说,需要你考虑的东西可不仅是重不重复这类问题,而是…

在这里插入图片描述

开个玩笑~~应当结合实际情况,对各个场景进行综合考虑。

  其实,如果在业务代码中保证了不会写入重复的身份证号,那么这两个选择逻辑上都是正确的。但是在SELECT和DML场景中,唯一索引和普通索引却有很多不同。

1、在SELECT中,唯一索引和普通索引的区别

  本文测试引擎选择我们最常用的InnoDB,版本为MySQL8.0;

假设,执行查询的语句是:

select id from T where id_card = 666;

  (身份证太长,咱们用简单数据做演示)我们知道,MySQL的InnoDB采用的是B+树实现的索引结构,查找过程从B+树的树根起,按层搜索到666所在的叶子节点,然后取出该节点所在的数据页,把数据页读到内存后,通过二分法在数据页中定位id_card=666的行数据。

在这里插入图片描述

B+ 树的查找过程如上图:

  1. 将磁盘块1从磁盘加载到内存,发生一次IO ,在内存中使用二分查找方式找到 666 在600和700 之间,锁定磁盘块1的P2 指针。
  2. 通过磁盘块1 的 P2 指针地址把磁盘块3 加载到内存,发生第二次IO ,锁定磁盘块3 的 P2 指针
  3. 通过磁盘块3 的P2指针加载磁盘块7到内存,发生第三次 IO,同时根据二分查找找到666 查询结束。

普通索引和唯一索引的定位方式:

  • 普通索引:查到第一条id_card=666 后,然后继续往后查找直到碰到第一个 id_card<>666 的记录时,结束。
  • 唯一索引:由于索引定义了唯一性,查找到第一个满足条件的记录后,直接结束。

  两者在查询方面的性能差距微乎其微。对于普通索引多的那一次操作,因为本身就是以数据页为单位读进内存,数据页大小默认16KB(大概1000行),要多做的那一次“查找和判断下一条记录”的操作,就只需要一次指针寻找和一次计算。当然,不可避免查询的数据是该数据页的最后一位,这样还要再读下一块数据页,算法会复杂一些。

  但你知道的,这种概率很小,我们程序员要相信逆墨菲定律:大概率不会出现且未被发现的BUG,在难以改动的前提下,你就当不知道就完了,发生了又能咋地?有测试顶着呢!

在这里插入图片描述

  有同学问我了:普通索引为什么要继续向下查找?继续向下查找的原因是由于普通索引允许重复值,且B+Tree是天然有序的。SQL中并没有指定limit 1,所以他还要往下查,看是否有同条件的数据一起返回,直到查到第一条不满足条件的数据为止。

2、在DML中,唯一索引和普通索引的区别

  ding!这是本篇文章的重点,在看之前,我们需要先了解什么是change buffer

  了解MySQL机制的同学们知道,当执行 DML(INSERT、UPDATE、DELETE)等操作时,InnoDB会利用 change buffer进行加速写操作,可以将写操作的随机磁盘访问调整为局部顺序操作,而在机械硬盘时代,随机磁盘访问(随机I/O)也是数据库操作中的最耗性能的硬伤。当普通索引(非唯一索引)的数据页发生写操作时,把操作内容写到内存中的change buffer后就可以立刻返回(执行完成)了。

  这里我以UPDATE操作为例,当需要更新某一行数据时,会先判断该行所在数据页是否在内存中,如果在就直接在内存数据页中更新,如果这个数据页没有内存中的话,在不影响数据一致性的前提下,InnoDB 会将这些UPDATE操作缓存在 change buffer 中,这样就不需要从磁盘读入数据页,当有SQL查询需要访问这个数据页的数据时,将数据页读入内存后,然后先执行 change buffer 中与这个页的相关UPDATE操作,通过这种方式保证这个数据页的逻辑正确性

在这里插入图片描述

  可见,change buffer是会被从内存持久化到磁盘中的,将 change buffer 中的操作应用到原数据页,得到最新结果的过程被称为 merge。除了访问这个数据页会触发 merge 外,系统有后台线程会定期 merge。在数据库正常关闭(shutdown)的过程中,也会执行 merge 操作,相当于刷脏页啦(把已修改的数据更新到实际数据文件中)。

触发merge的操作主要有以下几种(你该记住的点):

  • 有SQL线程访问这个数据页;
  • master thread线程每秒或每10秒进行一次merge change buffer的操作;
  • 在数据库正常关闭的时候。

  小朋友,你是否有很多问号??DB服务器宕机,数据不是就丢了?这就得redo log + binlog来保证了,可以参考作者另一篇文章《听我讲完redo log、binlog原理,面试官老脸一红》,本篇不再赘述。

  跑远了?言归正传~~上文提到普通索引(非唯一索引)会使用到change buffer进行加速写操作,你是不是已经get到点了呢~

  是的,唯一索引不会使用 Change buffer ,如果索引设置了唯一属性,在进行插入或者修改操作时,InnoDB 必须进行唯一性检查,如果不读取索引页到缓冲池,无法校验索引是否唯一,如果都把索引页读到内存了,那直接更新内存会更快,就没必要使用change buffer了。

  • 对于普通索引(非唯一索引)的DML操作来说,当待更新的数据页在内存中时,找到前值和后值的区间插入即可;当待更新的数据页在不在内存中时,直接把操作写到Change buffer就完事儿了。舒服!

  • 对于唯一索引,当待更新的数据页在不在内存中时,索引每次都得把数据页读到内存中判断唯一性,将数据从磁盘读入内存涉及大量随机IO的访问,慢的一批,当遇到高频写操作时??唉,别想了,难受!

  到这里,相信你对普通索引和唯一索引的取舍有了一定的概念,普通索引和唯一索引在查询能力上是没差别的,主要考虑的是更新的影响。还得结合实际业务场景来判断,如果是读取远大于更新和插入的表,唯一索引和普通索引都可以,但是如果业务需求相反,个人觉得应该使用普通索引,当然如果是那种更新完要求立即可见的需求,就是刚更新完就要再查询的,这种情况下反而不推荐普通索引,因为这样会频繁的产生merge操作,起不到change buffer的作用,反而需要额外空间来维护change buffer就有点得不偿失了。

  当我们使用普通索引,尤其在使用机械盘的场景下,尽量把change buffer开大从而确保数据的写入速度。最后,通过列举一下 change buffer 的配置,结束今天的分享,相信看到这里的都是有心人,也是喜爱MySQL的崽子,记得不要吝啬你的点赞哦~~

在这里插入图片描述

change buffer 配置

  • innodb_change_buffer_max_size% 配置写缓冲的大小,占整个缓冲池的比例,默认值是25%,可以通过修改该值提高InnoDB写效率,最大值是50%。
mysql> show variables like '%innodb_change_buffer_max_size%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+
1 row in set (0.00 sec)
  • innodb_change_buffering配置是否缓存辅助索引页的修改,默认为 all,即缓存 INSERT/DELETE/UPDATE等DML操作。
mysql> show variables like '%innodb_change_buffering%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_change_buffering | all   |
+-------------------------+-------+
1 row in set (0.00 sec)

MySQL系列文章汇总与《MySQL江湖路 | 专栏目录》

往期热门MySQL系列文章

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/191563.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 什么叫单页面开发_获取当前页面url

    什么叫单页面开发_获取当前页面url参考网址https://www.cnblogs.com/belongs-to-qinghua/p/11151054.htmlhttp://www.zhiliaotang.net/jishujiaoliu/web/965.html单页面开发:单页面开发常用于webapp开发和后台管理系统等单页面应用原理:我们通常所说的单页面应用程序通常是通过前端框架vue,react,angular…

    2022年10月13日
  • 面试被问selenium自动化模型,你了解多少?

    面试被问selenium自动化模型,你了解多少?

  • 软件测试流程规范简介(不同公司流程规范不一样,仅供参考)「建议收藏」

    软件测试流程规范简介(不同公司流程规范不一样,仅供参考)「建议收藏」前言:整理了一下软件测试流程规范简洁,仅供参考!一、流程图概述二、测试启动阶段(需求分析)参与软件需求评审、技术评审,以测试的角度分析需求的可测性,可构思将来对测试进行的方法、原则等。更重要的是对不可测或难以测试性问题要及时与产品经理、项目经理、研发人员协调解决。全面了解需求,从用户角度考虑软件测试需要达到的验证的状态,即哪些功能需要重点测试,哪些则无需,以便将来制定测试计划。测试人员参与项目晨会,明确需求及任务完成进度及时间节点,研发人员需向测试人员提供外部应用及使用说明(如Redis、RMQ

  • Java中JDBC的使用详解[通俗易懂]

    Java中JDBC的使用详解[通俗易懂]打个广告,帮朋友卖点东西,东西超便宜的哟【衣服鞋子等】,厂家直接出货,绝对低于市场价!!!一般都比市场价便宜3—7折【都是牌子货】,如果您感兴趣,可以扫描屏幕下方的二维码,感谢关注!!!微信一、环境介绍在mysql中创建一个库shen,并创建user表和插入表的数据。 新建一个Java工程jdbc,并导入数据驱动。二、详细步骤1、加载数据库驱动//1.加载…

  • ESP8266开发之旅 小程序之阿里云篇① “IOT菜鸟”小程序,小白简单配置就可以玩起来

    ESP8266开发之旅 小程序之阿里云篇① “IOT菜鸟”小程序,小白简单配置就可以玩起来ESP8266开发之旅小程序之阿里云篇①IOT菜鸟,小程序如何对接阿里云

  • linux设置ip默认,Linux设置ip地址与默认网关[通俗易懂]

    linux设置ip默认,Linux设置ip地址与默认网关[通俗易懂]1.设置ip地址打开终端,取得root权限(sudosu)。输入命令:#ifconfigeth0192.168.0.20netmask255.255.255.0详解:ifconfig—设置ip的命令eth0—要设置的网卡,可以只输入ifconfig查看已安装网卡192.168.0.20—预设置的IP值。netmask255.255.255.0—子网掩…

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号