假如现在有个需求:想查出所有code是10开头的用户。
这个需求太简单了吧,sql语句如下:
explain select * from user
where code like ‘10%’;
执行结果:
图中看出这种%在10右边时走了索引。
而如果把需求改了:想出现出所有code是1结尾的用户。
查询sql语句改为:
explain select * from user
where code like ‘%1’;
执行结果:
从图中看出这种%在1左边时,code字段上索引失效了,该sql变成了全表扫描。
此外,如果出现以下sql:
explain select * from user
where code like ‘%1%’;
该sql语句的索引也会失效。
下面用一句话总结一下规律:当like语句中的%,出现在查询条件的右边时,索引会失效。
那么,为什么会出现这种现象呢?
答:其实很好理解,索引就像字典中的目录。一般目录是按字母或者拼音从小到大,从左到右排序,是有顺序的。
我们在查目录时,通常会先从左边第一个字母进行匹对,如果相同,再匹对左边第二个字母,如果再相同匹对其他的字母,以此类推。
通过这种方式我们能快速锁定一个具体的目录,或者缩小目录的范围。
但如果你硬要跟目录的设计反着来,先从字典目录右边匹配第一个字母,这画面你可以自行脑补一下,你眼中可能只剩下绝望了,哈哈。
8、列对比
=====
上面的内容都是常规需求,接下来,来点不一样的。
假如我们现在有这样一个需求:过滤出表中某两列值相同的记录。比如user表中id字段和height字段,查询出这两个字段中值相同的记录。
这个需求很简单,sql可以这样写:
explain select * from user
where id=height
执行结果:
意不意外,惊不惊喜?索引失效了。
为什么会出现这种结果?
id字段本身是有主键索引的,同时height字段也建了普通索引的,并且两个字段都是int类型,类型是一样的。
但如果把两个单独建了索引的列,用来做列对比时索引会失效。
感兴趣的朋友可以找我私聊。
9、使用or关键字
=========
我们平时在写查询sql时,使用or关键字的场景非常多,但如果你稍不注意,就可能让已有的索引失效。
不信一起往下面看。
某天你遇到这样一个需求:想查一下id=1或者height=175的用户。
你三下五除二就把sql写好了:
explain select * from user
where id=1 or height=‘175’;
执行结果:
没错,这次确实走了索引,恭喜被你蒙对了,因为刚好id和height字段都建了索引。
但接下来的一个夜黑风高的晚上,需求改了:除了前面的查询条件之后,还想加一个address=‘成都’。
这还不简单,sql走起:
explain select * from user
where id=1 or height=‘175’ or address=‘成都’;
执行结果:
结果悲剧了,之前的索引都失效了。
你可能一脸懵逼,为什么?我做了什么?
答:因为你最后加的address字段没有加索引,从而导致其他字段的索引都失效了。
注意:如果使用了or关键字,那么它前面和后面的字段都要加索引,不然所有的索引都会失效,这是一个大坑。
10、not in和not exists
====================
在我们日常工作中用得也比较多的,还有范围查询,常见的有:
-
in
-
exists
-
not in
-
not exists
-
between and
今天重点聊聊前面四种。
10.1 in关键字
假如我们想查出height在某些范围之内的用户,这时sql语句可以这样写:
explain select * from user
where height in (173,174,175,176);
执行结果:
从图中可以看出,sql语句中用in关键字是走了索引的。
10.2 exists关键字
有时候使用in关键字时性能不好,这时就能用exists关键字优化sql了,该关键字能达到in关键字相同的效果:
explain select * from user t1
where exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)
执行结果:
从图中可以看出,用exists关键字同样走了索引。
10.3 not in关键字
上面演示的两个例子是正向的范围,即在某些范围之内。
那么反向的范围,即不在某些范围之内,能走索引不?
话不多说,先看看使用not in的情况:
explain select * from user
where height not in (173,174,175,176);
执行结果:
你没看错,索引失效了。
看如果现在需求改了:想查一下id不等于1、2、3的用户有哪些,这时sql语句可以改成这样:
explain select * from user
where id not in (173,174,175,176);
执行结果:
你可能会惊奇的发现,主键字段中使用not in关键字查询数据范围,任然可以走索引。而普通索引字段使用了not in关键字查询数据范围,索引会失效。
10.4 not exists关键字
除此之外,如果sql语句中使用not exists时,索引也会失效。具体sql语句如下:
explain select * from user t1
where not exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)
执行结果:
从图中看出sql语句中使用not exists关键后,t1表走了全表扫描,并没有走索引。
11、order by的坑
=============
在sql语句中,对查询结果进行排序是非常常见的需求,一般情况下我们用关键字:order by就能搞定。
但我始终觉得order by挺难用的,它跟where或者limit关键字有很多千丝万缕的联系,一不小心就会出问题。
Let go
11.1 哪些情况走索引?
首先当然要温柔一点,一起看看order by的哪些情况可以走索引。
我之前说过,在code、age和name这3个字段上,已经建了联合索引:idx_code_age_name。
11.1.1 满足最左匹配原则
order by后面的条件,也要遵循联合索引的最左匹配原则。具体有以下sql:
explain select * from user
order by code limit 100;
explain select * from user
order by code,age limit 100;
explain select * from user
order by code,age,name limit 100;
执行结果:
从图中看出这3条sql都能够正常走索引。
除了遵循最左匹配原则之外,有个非常关键的地方是,后面还是加了limit关键字,如果不加它索引会失效。
11.1.2 配合where一起使用
order by还能配合where一起遵循最左匹配原则。
explain select * from user
where code=‘101’
order by age;
执行结果:
code是联合索引的第一个字段,在where中使用了,而age是联合索引的第二个字段,在order by中接着使用。
假如中间断层了,sql语句变成这样,执行结果会是什么呢?
explain select * from user
where code=‘101’
order by name;
执行结果:
虽说name是联合索引的第三个字段,但根据最左匹配原则,该sql语句依然能走索引,因为最左边的第一个字段code,在where中使用了。只不过order by的时候,排序效率比较低,需要走一次filesort排序罢了。
11.1.3 相同的排序
order by后面如果包含了联合索引的多个排序字段,只要它们的排序规律是相同的(要么同时升序,要么同时降序),也可以走索引。
具体sql如下:
explain select * from user
order by code desc,age desc limit 100;
执行结果:
该示例中order by后面的code和age字段都用了降序,所以依然走了索引。
11.1.4 两者都有
如果某个联合索引字段,在where和order by中都有,结果会怎么样?
explain select * from user
where code=‘101’
order by code, name;
执行结果:
code字段在where和order by中都有,对于这种情况,从图中的结果看出,还是能走了索引的。
11.2 哪些情况不走索引?
前面介绍的都是正面的用法,是为了让大家更容易接受下面反面的用法。
好了,接下来,重点聊聊order by的哪些情况下不走索引?
11.2.1 没加where或limit
如果order by语句中没有加where或limit关键字,该sql语句将不会走索引。
explain select * from user
order by code, name;
执行结果:
从图中看出索引真的失效了。
11.2.2 对不同的索引做order by
前面介绍的基本都是联合索引,这一个索引的情况。但如果对多个索引进行order by,结果会怎么样呢?
explain select * from user
order by code, height limit 100;
执行结果:
从图中看出索引也失效了。
11.2.3 不满足最左匹配原则
前面已经介绍过,order by如果满足最左匹配原则,还是会走索引。下面看看,不满足最左匹配原则的情况:
explain select * from user
order by name limit 100;
执行结果:
name字段是联合索引的第三个字段,从图中看出如果order by不满足最左匹配原则,确实不会走索引。
11.2.4 不同的排序
前面已经介绍过,如果order by后面有一个联合索引的多个字段,它们具有相同排序规则,那么会走索引。
但如果它们有不同的排序规则呢?
explain select * from user
order by code asc,age desc limit 100;
执行结果:
自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数Java工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则几千的学费,着实压力不小。自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年Java开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,真正体系化!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!
如果你觉得这些内容对你有帮助,可以扫码获取!!(备注Java获取)
言尽于此,完结
无论是一个初级的 coder,高级的程序员,还是顶级的系统架构师,应该都有深刻的领会到设计模式的重要性。
- 第一,设计模式能让专业人之间交流方便,如下:
程序员A:这里我用了XXX设计模式
程序员B:那我大致了解你程序的设计思路了
- 第二,易维护
项目经理:今天客户有这样一个需求…
程序员:明白了,这里我使用了XXX设计模式,所以改起来很快
- 第三,设计模式是编程经验的总结
程序员A:B,你怎么想到要这样去构建你的代码
程序员B:在我学习了XXX设计模式之后,好像自然而然就感觉这样写能避免一些问题
- 第四,学习设计模式并不是必须的
程序员A:B,你这段代码使用的是XXX设计模式对吗?
程序员B:不好意思,我没有学习过设计模式,但是我的经验告诉我是这样写的
从设计思想解读开源框架,一步一步到Spring、Spring5、SpringMVC、MyBatis等源码解读,我都已收集整理全套,篇幅有限,这块只是详细的解说了23种设计模式,整理的文件如下图一览无余!
搜集费时费力,能看到此处的都是真爱!
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!
mages/e5c14a7895254671a72faed303032d36.jpg" alt=“img” style=“zoom: 33%;” />
言尽于此,完结
无论是一个初级的 coder,高级的程序员,还是顶级的系统架构师,应该都有深刻的领会到设计模式的重要性。
- 第一,设计模式能让专业人之间交流方便,如下:
程序员A:这里我用了XXX设计模式
程序员B:那我大致了解你程序的设计思路了
- 第二,易维护
项目经理:今天客户有这样一个需求…
程序员:明白了,这里我使用了XXX设计模式,所以改起来很快
- 第三,设计模式是编程经验的总结
程序员A:B,你怎么想到要这样去构建你的代码
程序员B:在我学习了XXX设计模式之后,好像自然而然就感觉这样写能避免一些问题
- 第四,学习设计模式并不是必须的
程序员A:B,你这段代码使用的是XXX设计模式对吗?
程序员B:不好意思,我没有学习过设计模式,但是我的经验告诉我是这样写的
[外链图片转存中…(img-dZK6nM0A-1712987389012)]
从设计思想解读开源框架,一步一步到Spring、Spring5、SpringMVC、MyBatis等源码解读,我都已收集整理全套,篇幅有限,这块只是详细的解说了23种设计模式,整理的文件如下图一览无余!
[外链图片转存中…(img-aAAw0ogb-1712987389012)]
搜集费时费力,能看到此处的都是真爱!
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!