专栏首页moon聊技术mysql|聊完了mysql索引,面试官直接给我涨了2000!

mysql|聊完了mysql索引,面试官直接给我涨了2000!

前言

mysql索引真的是一个让人不得不说的话题,这个东西你在面试中会用到,在实际的工作中也会用到,这更是一个专业的DBA所必须掌握的内容,它的重要性体你在大厂的面试题汇总也可以看到,属于必问的一个内容。

这篇文章的内容可能有点多,也有点晦涩难懂,没有基础的小伙伴需要多读几遍才能读懂,但是希望你能沉下心来读完这篇文章,你会得到更多:

什么是索引?

常见的索引模型有哪些?

什么是回表?

什么是覆盖索引?

什么是最左前缀原则?

什么是索引下推?

..............

正文

什么是索引?

相信大家小时候学习汉字的时候都会查字典,想想你查字典的步骤,我们是通过汉字的首字母a~z一个一个在字典目录中查找,最终找到该字的页数。想想,如果没有目录会怎么样,最差的结果是你有可能翻到字典的最后一页才找到你想要找的字。

索引就相当于我们字典中的目录,可以极大的提高我们在数据库的查询效率。

常见的索引模型有哪些?

①有序数组

如图:

我们按照IDCard从小到大排列:

在我们想要根据IDCard查找某一条数据时,就可以通过二分法查找。

在我们想要根据IDCard查找IDCard在 10 到 1000 内的的数据时,就可以先通过二分法先查找10,然后向递增的方向遍历,找到IDCard为1000,再继续遍历,直到找到的IDCard大于1000时就完成了整个范围查询。

我们仅仅从查询的角度来看有序数组已经很优秀了,但是我们都知道,有序数组在插入一条数据时是非常麻烦的,你需要将你插入位置后面的数据整体向后移一位,这是非常消耗性能的。

优点:查询效率很高,也很适合范围查询。

缺点:当新数据插入时会影响效率。

②哈希表

如图

学过hashmap 的朋友应该都比较了解了,它的原理其实就是将IDCard通过哈希算法计算出一个特定的值,然后存储地址,这样在你找数据的时候直接可以通过IDCard去找了,当然也会有种情况,就是两个元素选中了相同的空间,我们通常会引出一个链表去存储。

我们可以看到hash表在等值查询的效率是很高的,但是由于hash表是无序的,所以在范围查询的时候只能遍历所有了,效率会很低。

感兴趣的朋友可以去看看lru,是如何解决范围查询的问题的,后续我也会和大家讲讲。

优点:等值查询效率高,插入效率高

缺点:不适合范围查询

③二叉树

如图:

二叉树是比较经典的数据结构了,它的特点是每个节点的左儿子小于父节点,父节点又小于右儿子。

二叉树是有序的,查找的时间复杂度为O(logn)

二叉树可以说在插入查询方面都是比较优秀的,但是在数据的索引选择方面我们并不会选择二叉树,我们按照上图来讲:

假如我要访问IDCard5,那么我要先访问IDCard1,然后访问IDCard3,最后才能访问到IDCard5,我们总会访问了3个数据块,每一次访问都是一次磁盘寻址的过程,假设树高30,那么我们最差的情况下寻找一个数据要访问30次磁盘,这在效率上是不能忍受的。

④B+树

如图

我们数据库innodb默认的索引引擎就是B+树。

B+树其实是就是一个N叉树,只在子节点上存储数据,并且子节点用链表维护,而且是有序的,在范围查询(链表更高效),等值查询,插入新数据上来说都是很高效的。并且作为N叉树,在树的每一层都可以存储很多数据,这样在数据库查询数据的时候最差也只需要几次磁盘寻址就可以了。

在mysql中有主键索引和非主键索引之分,主键索引上存储的是数据行信息,非主键索引上存储的是主键信息。

如下

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `application` varchar(64) NOT NULL DEFAULT '' COMMENT '所属应用名称',
  `owner` varchar(32) NOT NULL DEFAULT '' COMMENT '负责人',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_application_id` (`application`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

主键为id,那么id这棵B+树上就会存储该行所有的信息字段,包括application,owner。

对于没有主键的表,innodb会给默认创建一个Rowid做主键。

唯一索引是application,那么在application这颗索引树上存储的信息就是id。

优点

♦第一层只放索引信息,存放的索引信息更多。

♦树高更低,故磁盘寻址带来的损耗更小。

♦链表维护,范围查询效率更高。

⑤B-树

如图

B-树和B+树的区别是每一个节点都会存储数据,叶子节点之间不用链表链接。

相比B+树来说做范围查询的效率会低一点,如果空间大小固定的话,第一层存放的索引信息更少(想想目录,我们都希望第一层是只用来存储目录信息的)。

什么是回表?

还按这个表举例:

CREATE TABLE `user` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',  `application` varchar(64) NOT NULL DEFAULT '' COMMENT '所属应用名称',  `owner` varchar(32) NOT NULL DEFAULT '' COMMENT '负责人',  PRIMARY KEY (`id`),  UNIQUE KEY `idx_application_id` (`application`) USING BTREE)ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

来一条查询语句

select * from user where application = 'wechat';

‍‍‍‍‍‍我们来看看,针对这张user表,上述的查询语句要经过哪些步骤:

①由于application是索引,所以先搜索application这颗索引树,找到application='wechat'这条数据,取得主键id

②通过取得的主键id,去主键id这颗B+树找到该条数据

③找到该条数据后,取得该数据行的值,并且返回

④结束

刚刚的第一步到第二步,其实就是一个回表操作,我们定义一下回表:

回表就是在普通索引树上取得主键信息,再返回到主键索引树去搜索数据信息,这就是回表操作。

覆盖索引

moon这里再举个例子你就能明白覆盖索引的含义了,还是刚刚的表

这条语句就可以用到覆盖索引这个特性了,我们再来看下步骤:

①由于application是索引,所以先搜索application这颗索引树,找到application='wechat'这条数据,取得主键id

②mysql发现id 就是select要查询的数据,并且application是唯一索引,于是直接返回

这就是覆盖索引的效果,可以减少我们的回表次数,甚至可以不用回表。

当然,在实际开发场景中也不要轻易只是为了能用到覆盖索引就建立冗余字段索引,还是要根据实际开发场景来的。

最左前缀原则

还是之前的表,索引变成了(application,owner)的联合索引,我们再来写个sql:

select owner from user where application like "w%";

当你的查询条件是application以w开头的数据时,就可以用到最左前缀原则了。

可以看到,不只是索引的全部定义,只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段,也可以是字符串索引的最左N个字符。

聪明的朋友已经发现了,最左前缀还有一层优化,比如(application,owner)这个联合索引中,我们用到了最左前缀,可以少维护一个application的单独索引,因此,第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

索引下推

联合索引(application,owner)

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

select * from user where application like "w%" and owner = "老王";

这个语句在搜索索引树的时候,只能用 “w”,找到第一个满足条件的记录,然后判断其他条件是否满足。

在MySQL 5.6之前,只能从ID3开始一个个回表。到主键索引上找出数据行,再对比字段值。

而MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

也就是owner在联合索引中,‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍判断owner是否等于'老王',会直接过滤掉不等于'老王'的数据。

结语

今天这篇文章内容确实够多的,聊了很多和数据库索引相关的知识,包括了什么是索引,索引模型,覆盖索引,前缀索引,索引下推,回表。

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

你可以看到,在满足语句需求的情况下, 我们要利用数据库的特性尽量的减少资源的损耗。我们在使用数据库的时候,尤其是在设计表结构时,也要以减少资源消耗作为目标。

下一篇内容,数据库的表锁行锁~

记得点赞。

END

本文分享自微信公众号 - moon聊技术(onetraveller_llxz),作者:moon聊技术

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

原始发表时间:2020-10-20

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 无语,我差点被面试官怼坏了,又给我问到MySQL索引

    前一阵子,又跑出去搞了一场面试,心态算是崩了,关于MySQL索引的原理及使用被面试官怼的体无完肤,立志要总结一番,然后一直没有时间(其实是懒……),准备好了吗?

    Java程序猿阿谷
  • 我以为我对Mysql索引很了解,直到我被阿里面试官22连击

    相信很多人对于MySQL的索引都不陌生,索引(Index)是帮助MySQL高效获取数据的数据结构。

    帅地
  • 一文搞懂select语句在MySQL中的执行流程!

    MySQL作为互联网行业使用最多的关系型数据库之一,与其免费、开源的特性是密不可分的。然而,很多小伙伴工作了很多年,只知道使用MySQL进行CRUD操作,这也导...

    Bug开发工程师
  • .NET6 平台系列4 .NET开源之路

      .NET平台是微软于2000年推出的Windows操作系统的应用软件开发框架,发展至今形成巨大的技术栈,涉及多语言(支持C#、F#、VB.NET)多领域(W...

    张传宁IT讲堂
  • 面试官:order by 怎么优化?

    刚换了新工作,用了两周时间准备,在 3 天之内拿了 5 个 offer,最后选择了广州某互联网行业独角兽 offer,昨天刚入职。这几天刚好整理下在面试中被问到...

    一个优秀的废人
  • 优秀后端架构师必会知识:史上最全MySQL大表优化方案总结

    本文原作者“ manong”,原创发表于segmentfault,原文链接:segmentfault.com/a/1190000006158186

    JackJiang
  • 我后悔在简历上写了“精通”…

    前言:众所周知,简历上“了解=听过名字;熟悉=知道是啥;熟练=用过;精通=做过东西”,我现在十分后悔在简历上写了“精通”二字…

    小灰
  • java架构之路-(四)Mysql之让我们再深撸一次mysql

      让我再深撸一次mysql吧,这次主要以应对面试来说说mysql,大概几个方向,索引结构,查询引擎,索引优化,explain的详解和trace工具的使用。

    小菜的不能再菜
  • 微盟OOM排查之旅

    作者:张远,腾讯CDB高级工程师;余成真,微盟DBA负责人 首发:「老叶茶馆」微信公众号 背景      微盟是中小企业云端商业及营销解决方案提供商,客户...

    腾讯数据库技术
  • 学弟问我:explain 很重要吗?

    哈喽,小伙伴们好呀。我是狗哥,今天打算跟大家聊聊一个很基础的 MySQL 命令 —— explain。这个命令相信很多小伙伴都熟悉并且几乎每天都会使用,反正我是...

    一个优秀的废人
  • 上亿数据怎么玩深度分页?兼容MySQL + ES + MongoDB

    大家在面试时,或者准备面试中可能会遇到上述的问题,大多的回答基本上是分库分表建索引,这是一种很标准的正确回答,但现实总是很骨感,所以面试官一般会追问你一句,<f...

    Kerwin
  • 云数据库VS自建数据库,到底该如何抉择?

    有赞的基础架构使用了UCloud的基础服务,我们有相当比例的数据库是UCloud的RDS(一部分使用云RDS,一部分使用购买他们的物理服务器自建数据库)。

    老叶茶馆
  • 京东面试:说说MySQL的架构体系

    虽然他搞java开发好几年了,也一直使用的是MySQL数据库,但是面对这个问题依然是一脸懵逼,还以为面试官要问索引、慢查询、性能优化之类的(因为这些都是网上找点...

    田维常
  • 面试官:mysql 表删除一半数据,表空间会变小吗?

    哈喽,我是狗哥。小伙伴都知道我最近换工作了,薪资、工作内容什么的都是我比较满意的。五月底也面试了有 6、7 家公司,应该拿了有 5 个 offer。这段时间也被...

    一个优秀的废人
  • 学妹:你的 IDEA 主题怎么这么好看?

    哈喽,小伙伴们好呀。我是狗哥,今天不聊技术,聊聊我们的吃饭工具。经常有小伙伴问我:“你的 IDEA 主题怎么这么好看,能推荐一下不?”。问的人太多了,才索性写了...

    一个优秀的废人
  • 美团面试官:讲清楚MySQL结构体系,立马发offer

    继续和大家分享,我去上海美团面试遇到的技术问题,当时,回答的也是马马虎虎的,不能说不好,也不能说好,反正就是没有给面试官一种爽的感觉。

    田维常
  • 面试让HR都能听懂的MySQL锁机制,欢声笑语中搞懂MySQL锁

      今天的主人公是我们公司同事侨总,传说中手上有10个比特币的男人。自从比特币大涨以来,养成了几个小爱好:周末听戏坐包厢,骑马酒吧滑雪场。

    陈哈哈
  • 字节跳动后端实习面经,一面+二面

    本来约的今天下午2点一面,一面完面试官让我直接等二面,然后就连着面了,二面完面试官又让我等着直接三面,然后等了一会儿说三面面试官不在,下次约时间。。。。看来是打...

    烂猪皮
  • 通过宝塔面板实现MySQL性能简单调优

    在PHP+MYSQL架构网站运行过程中,往往会遇到各种性能问题影响,如MySQL、PHP、CPU、磁盘IO、缓存等,其中MySQL瓶颈就是最常见也最难解决的一种...

    用户4988376

扫码关注云+社区

领取腾讯云代金券