面试三轮我倒在了一道 SQL 题上……| 原力计划

发表于 4年以前  | 总阅读数:684 次

作者 | 牧小农的夏天 责编 | 王晓曼 出品 | CSDN博客

前言

最近小农在找工作,因为今年疫情的特殊原因,导致工作不是特别好找,所以一旦有面试电话,如果可以,都会去试一试,刚好接到一个面试邀请,感觉公司还不错,于是就确定了面试时间,准备了一下就去面试了。 第一轮面试是小组组长面试,通过。 第二轮是经理面试也是通过了。 第三轮总监面试,前面都还有模有样,突然画风一转,面试官说:“问你最后一个问题”。 面试官:10W条数据,我要从其中查出100条不连续的数据,给你ID,来查Name和Password进行展示,如何才能高性能的去使用?

我:在ID上建立聚簇索引,然后用 in id 来缩小表搜索范围,最后 使用条件查询小于最大ID,大于最小ID,这样可以让SQL速度能够比较快的展示,虽然In的性能比较低

心理活动:雕虫小技,还最后一个问题,这样的问题再来一个吧。

只见面试官紧锁眉头,与我心里期待的表情有点不一样啊,难道是哪个环节出了问题?面试官:这样的性能不能达到最优化的程度,而且如果我给你的最小ID是1,最大ID是100000呢?你这就有点杠精了啊,那行吧,你是面试官你说了算

我:既然ID已经给出来了,而且只查询两个字段,用聚簇索引那么查询数据是很快的,用in id应该是可以的。

面试官:好的,回去等通知吧

我。。。。。

后知

于是回去后,查询资料,才知道原来面试官,真正想考的是“覆盖索引”。

什么是覆盖索引:

当SQL语句的所求查询字段(Select列)和查询条件字段(Where子句)全都包含在一个索引中(联合索引),可以直接使用索引查询而不需要回表。这就是覆盖索引,通过使用覆盖索引,可以减少搜索树的次数,这就是覆盖索引,在了解覆盖索引之前,我们先来看看什么是索引。

什么是索引?

我们有一个主键列为ID的表,表中有字段name,并且在name上有索引。

表中 t_user 值分别为(1,张一)、(2,张二)、(3,张三)、(4,张四)、(5,张五)

表结构如下:

mysql> create table t_user (
id bigint(20) not null auto_increment ,
name varchar(255) not null,
primary key (id),
index index_name (name) using btree)
engine=innodb
default character set=utf8 collate=utf8_general_ci
————————————————
版权声明:本文为CSDN博主「牧小农的夏天」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_14996421/article/details/106098595

两棵树的示例示意图如下:

从图中不难看出,根据叶子节点的内容,索引类型分为主键索引和二级索引(非主键索引)。

主键索引:主键索引的叶子节点保存着主键即对应行的全部数据。在InnoDB里,主键索引也被称为聚簇索引(clustered index)。

二级索引(非主键索引):二级索引树中的叶子结点保存着索引值和主键值,当使用二级索引进行查询时,需要进行回表操作。在InnoDB里,非主键索引也被称为二级索引(secondary index)。

通过上面所讲的,我们来看看如何通过SQL语句来区分主键索引和普通索引的查询。

  • select* from t_user where id=1 即主键查询方式,则只需要搜索ID这棵B+树

  • select* from t_user where name=张三 即普通索引查询方式,则需要先搜索name索引树,得到ID的值为3,再ID引树搜索一次。这个过程称为回表

也就是说,基于二级索引(非主键索引)的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。

看到这里如果你看懂了上面的介绍,那么这里你会有一个疑问,我直接用in id不就好了吗,建立ID主键索引,就可以不用回表了,速度不也就提升了吗?

如果是 5.5 之前的版本确实不会走索引的,在 5.5 之后的版本,MySQL 做了优化。MySQL 在2010 年发布 5.5 版本中,优化器对 In 操作符可以自动完成优化,针对建立了索引的列可以使用索引,没有索引的列还是会走全表扫描,也就是我们所说的回表。

那么,有没有可能经过索引优化,避免回表过程呢?答应是有的。

覆盖索引

SQL语句如下,其中ID自增,Name为索引:

mysql> create table t_user (
id bigint(20) not null auto_increment ,
name varchar(255) not null,
password varchar(255) ,
primary key (id),
engine=innodb
default character set=utf8 collate=utf8_general_ci
————————————————
版权声明:本文为CSDN博主「牧小农的夏天」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_14996421/article/details/106098595

比如有这么两句SQL:

语句A:select id from user_table where name= '张三'

语句B:select password from user_table wherename= '张三'

语句A:因为 name索引树 的叶子结点上保存有 Name和ID的值 ,所以通过Name索引树查找到ID后,因此可以直接提供查询结果,不需要回表,也就是说,在这个查询里面,索引Name 已经 “覆盖了” 我们的查询需求,我们称为 覆盖索引。

语句B:name索引树 上 找到name=‘张三’ 对应的主键ID, 通过回表在主键索引树上找到满足条件的数据。

因此我们可以得知,当SQL语句的所求查询字段(select列)和查询条件字段(where子句)全都包含在一个索引中(联合索引),可以直接使用索引查询而不需要回表。这就是覆盖索引

例如上面的语句B是一个高频查询的语句,我们可以建立(name,password)的联合索引,这样,查询的时候就不需要再去回表操作了,可以提高查询效率。

所以关于上面的面试题我们就可以得出,使用联合索引就可以很好的回答面试官的问题(id,name,password)这样的联合索引就可以调用到覆盖索引,可以减少树的搜索次数,不再需要回表查整行记录,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

说到了联合索引我们就不得不说联合索引中最重要的匹配原则,最左匹配原则了

最左匹配原则

最左前缀匹配原则,是非常重要的原则,mysql会从左向右进行匹配。

例如我们定义了(name,password)两个联合索引字段,我们使用 where name = '张三' and password = '2'索引可以生效的,当我们是颠倒了他们的顺序 使用wherepassword = '1' and name = '王五',索引同样也是可以生效的,在mysql查询优化器会判断纠正这条SQL语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划,我们能尽量的利用到索引时的查询顺序效率最高,所以mysql查询优化器会最终以这种顺序(where name = '张三' and password = '2')进行查询执行,就类似 我们的 order byname,password这样一种排序规则,先对张三的用户进行查询排序,在对password进行处理。 比如我们要查询姓张的用户,我们的条件查询可以为 "where name like ‘张%’",但是不能是 where name like '%张%'或者是 where name like '%张',因为索引可以用于查询条件字段为索引字段,根据字段值必须是最左若干个字符进行的模糊查询,也就是需要是 ‘张%’ 这样的添加才可以使用。 索引的复用能力。因为可以支持最左前缀,所以当已经有了(name,password)这个联合索引后,一般就不需要单独在name上建立索引了。因此,第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

如果既有联合查询,又有基于name,password各自的查询呢?查询条件里面只有password的语句,是无法使用(name,password)这个联合索引的,这时候你需要同时维护(name,password)、(password) 这两个索引。

创建索引时,我们也要考虑空间代价,使用较少的空间来创建索引。

假设我们现在不需要通过Name查询Password了,需要通过Nname查询Age或通过Age查询Name。

1.(name,age)联合索引+age单字段索引

2.(age,name)联合索引+name单字段索引

Name字段是比Age字段大的,所以,选择第一种,索引占用空间较小的一个

索引下推

上面我们说到满足最左前缀原则的时候,最左前缀可以用于在索引中定位记录。那么如果那些不符合最左前缀的部分,会怎么样呢?

如果现在有一个需求:检索出表中“名字第一个字是张,而且没有删除的信息(is_del = 1)。SQL语句如下:

mysql> select * from t_user where name like ‘张%’ and is_del=1

在MySQL5.6之前,只能从匹配的位置一个个回表。到主键索引上找出数据行,再对比字段值。

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

根据(username,is_del)联合索引查询所有满足名称以“张”开头的索引,然后回表查询出相应的全行数据,然后再筛选出未删除的用户数据。过程如下图:

每一个虚线箭头表示回表一次

图一(无索引下推执行流程)

每一个虚线箭头表示回表一次

图二(索引下推执行流程)

图1跟图2的区别是,InnoDB在(name,is_del)索引内部就判断了数据是否逻辑删除,对于逻辑删除的记录,直接判断并跳过。在我们的这个例子中,只需要对ID1、ID4这两条记录回表取数据判断,就只需要回表2次。 mysql默认启用索引下推,我们也可以通过修改系统变量optimizer_switch的index_condition_pushdown标志来控制SEToptimizer_switch = 'index_condition_pushdown=off';

我们也需要注意:

  • Innodb引擎的表,索引下推只能用于二级索引,因为innodb的主键索引树叶子结点上保存的是全行数据,所以这个时候索引下推并不会起到减少查询全行数据的效果。

  • 索引下推一般可用于所求查询字段(select列)不是/不全是联合索引的字段,查询条件为多条件查询且查询条件子句(where/order by)字段全是联合索引。

小结

今天的内容就到这里了,我们在上面描述了数据库索引的概念,包括了覆盖索引、联合索引、索引下推,那么下次如果有面试官问你刚开始的问题,相信大家可以好好的回(dui)答(ta)一下面试官了,在SQL优化中,减少回表次数,或者直接使用覆盖索引是比较重要的,尽量少地访问资源也是数据库设计的重要原则之一,谢谢大家,加油~

版权声明:本文为CSDN博主「牧小农的夏天」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。

原文链接:https://blog.csdn.net/qq_14996421/article/details/106098595

 相关推荐

刘强东夫妇:“移民美国”传言被驳斥

京东创始人刘强东和其妻子章泽天最近成为了互联网舆论关注的焦点。有关他们“移民美国”和在美国购买豪宅的传言在互联网上广泛传播。然而,京东官方通过微博发言人发布的消息澄清了这些传言,称这些言论纯属虚假信息和蓄意捏造。

发布于:1年以前  |  808次阅读  |  详细内容 »

博主曝三大运营商,将集体采购百万台华为Mate60系列

日前,据博主“@超能数码君老周”爆料,国内三大运营商中国移动、中国电信和中国联通预计将集体采购百万台规模的华为Mate60系列手机。

发布于:1年以前  |  770次阅读  |  详细内容 »

ASML CEO警告:出口管制不是可行做法,不要“逼迫中国大陆创新”

据报道,荷兰半导体设备公司ASML正看到美国对华遏制政策的负面影响。阿斯麦(ASML)CEO彼得·温宁克在一档电视节目中分享了他对中国大陆问题以及该公司面临的出口管制和保护主义的看法。彼得曾在多个场合表达了他对出口管制以及中荷经济关系的担忧。

发布于:1年以前  |  756次阅读  |  详细内容 »

抖音中长视频App青桃更名抖音精选,字节再发力对抗B站

今年早些时候,抖音悄然上线了一款名为“青桃”的 App,Slogan 为“看见你的热爱”,根据应用介绍可知,“青桃”是一个属于年轻人的兴趣知识视频平台,由抖音官方出品的中长视频关联版本,整体风格有些类似B站。

发布于:1年以前  |  648次阅读  |  详细内容 »

威马CDO:中国每百户家庭仅17户有车

日前,威马汽车首席数据官梅松林转发了一份“世界各国地区拥车率排行榜”,同时,他发文表示:中国汽车普及率低于非洲国家尼日利亚,每百户家庭仅17户有车。意大利世界排名第一,每十户中九户有车。

发布于:1年以前  |  589次阅读  |  详细内容 »

研究发现维生素 C 等抗氧化剂会刺激癌症生长和转移

近日,一项新的研究发现,维生素 C 和 E 等抗氧化剂会激活一种机制,刺激癌症肿瘤中新血管的生长,帮助它们生长和扩散。

发布于:1年以前  |  449次阅读  |  详细内容 »

苹果据称正引入3D打印技术,用以生产智能手表的钢质底盘

据媒体援引消息人士报道,苹果公司正在测试使用3D打印技术来生产其智能手表的钢质底盘。消息传出后,3D系统一度大涨超10%,不过截至周三收盘,该股涨幅回落至2%以内。

发布于:1年以前  |  446次阅读  |  详细内容 »

千万级抖音网红秀才账号被封禁

9月2日,坐拥千万粉丝的网红主播“秀才”账号被封禁,在社交媒体平台上引发热议。平台相关负责人表示,“秀才”账号违反平台相关规定,已封禁。据知情人士透露,秀才近期被举报存在违法行为,这可能是他被封禁的部分原因。据悉,“秀才”年龄39岁,是安徽省亳州市蒙城县人,抖音网红,粉丝数量超1200万。他曾被称为“中老年...

发布于:1年以前  |  445次阅读  |  详细内容 »

亚马逊股东起诉公司和贝索斯,称其在购买卫星发射服务时忽视了 SpaceX

9月3日消息,亚马逊的一些股东,包括持有该公司股票的一家养老基金,日前对亚马逊、其创始人贝索斯和其董事会提起诉讼,指控他们在为 Project Kuiper 卫星星座项目购买发射服务时“违反了信义义务”。

发布于:1年以前  |  444次阅读  |  详细内容 »

苹果上线AppsbyApple网站,以推广自家应用程序

据消息,为推广自家应用,苹果现推出了一个名为“Apps by Apple”的网站,展示了苹果为旗下产品(如 iPhone、iPad、Apple Watch、Mac 和 Apple TV)开发的各种应用程序。

发布于:1年以前  |  442次阅读  |  详细内容 »

特斯拉美国降价引发投资者不满:“这是短期麻醉剂”

特斯拉本周在美国大幅下调Model S和X售价,引发了该公司一些最坚定支持者的不满。知名特斯拉多头、未来基金(Future Fund)管理合伙人加里·布莱克发帖称,降价是一种“短期麻醉剂”,会让潜在客户等待进一步降价。

发布于:1年以前  |  441次阅读  |  详细内容 »

光刻机巨头阿斯麦:拿到许可,继续对华出口

据外媒9月2日报道,荷兰半导体设备制造商阿斯麦称,尽管荷兰政府颁布的半导体设备出口管制新规9月正式生效,但该公司已获得在2023年底以前向中国运送受限制芯片制造机器的许可。

发布于:1年以前  |  437次阅读  |  详细内容 »

马斯克与库克首次隔空合作:为苹果提供卫星服务

近日,根据美国证券交易委员会的文件显示,苹果卫星服务提供商 Globalstar 近期向马斯克旗下的 SpaceX 支付 6400 万美元(约 4.65 亿元人民币)。用于在 2023-2025 年期间,发射卫星,进一步扩展苹果 iPhone 系列的 SOS 卫星服务。

发布于:1年以前  |  430次阅读  |  详细内容 »

𝕏(推特)调整隐私政策,可拿用户发布的信息训练 AI 模型

据报道,马斯克旗下社交平台𝕏(推特)日前调整了隐私政策,允许 𝕏 使用用户发布的信息来训练其人工智能(AI)模型。新的隐私政策将于 9 月 29 日生效。新政策规定,𝕏可能会使用所收集到的平台信息和公开可用的信息,来帮助训练 𝕏 的机器学习或人工智能模型。

发布于:1年以前  |  428次阅读  |  详细内容 »

荣耀CEO谈华为手机回归:替老同事们高兴,对行业也是好事

9月2日,荣耀CEO赵明在采访中谈及华为手机回归时表示,替老同事们高兴,觉得手机行业,由于华为的回归,让竞争充满了更多的可能性和更多的魅力,对行业来说也是件好事。

发布于:1年以前  |  423次阅读  |  详细内容 »

AI操控无人机能力超越人类冠军

《自然》30日发表的一篇论文报道了一个名为Swift的人工智能(AI)系统,该系统驾驶无人机的能力可在真实世界中一对一冠军赛里战胜人类对手。

发布于:1年以前  |  423次阅读  |  详细内容 »

AI生成的蘑菇科普书存在可致命错误

近日,非营利组织纽约真菌学会(NYMS)发出警告,表示亚马逊为代表的电商平台上,充斥着各种AI生成的蘑菇觅食科普书籍,其中存在诸多错误。

发布于:1年以前  |  420次阅读  |  详细内容 »

社交媒体平台𝕏计划收集用户生物识别数据与工作教育经历

社交媒体平台𝕏(原推特)新隐私政策提到:“在您同意的情况下,我们可能出于安全、安保和身份识别目的收集和使用您的生物识别信息。”

发布于:1年以前  |  411次阅读  |  详细内容 »

国产扫地机器人热销欧洲,国产割草机器人抢占欧洲草坪

2023年德国柏林消费电子展上,各大企业都带来了最新的理念和产品,而高端化、本土化的中国产品正在不断吸引欧洲等国际市场的目光。

发布于:1年以前  |  406次阅读  |  详细内容 »

罗永浩吐槽iPhone15和14不会有区别,除了序列号变了

罗永浩日前在直播中吐槽苹果即将推出的 iPhone 新品,具体内容为:“以我对我‘子公司’的了解,我认为 iPhone 15 跟 iPhone 14 不会有什么区别的,除了序(列)号变了,这个‘不要脸’的东西,这个‘臭厨子’。

发布于:1年以前  |  398次阅读  |  详细内容 »
 目录