程序地带

面试官:不会sql优化?出门右转顺便带上门,谢谢


导读

作为一个后端程序员,数据库这个东西是绕不开的,特别是写sql的能力,如果您参加过多次面试,那么一定会从面试复盘中发现面试官总是会考察到sql优化这个东西。


我在之前的多次面试中最常遇到的一个问题的sql优化,不论是大厂还是小厂。但我之前没有详细去了解过这些东西啊,我就瞎鸡儿吹了,毕竟我也干过两三年的crud,sql还是写过不少的,也遇到过一些特别长的sql,执行时间特别长的sql,所以以前经常牺牲午睡时间给客户出报表还是有点效果的,mmp的。


我是这样说的:Sql优化,首先就是看sql的执行计划,然后按照执行计划对应的执行修改,比如该建索引建索引,然后就balabala....


其实老子之前压根就没学过怎么看执行计划,纯靠这么些年写sql的经验瞎鸡儿吹。这也是上个月开始看MySqL,才学会看,那今天就现学现卖,给您们展示两下子。


SQL执行计划(以MySQL为例)

1、如何查看sql的执行计划


在需要执行的查询SQL前添加一个关键字“EXPLAIN”



 


从上图可以看到,执行计划共有12字段,先来简便看每一列的作用:


字段解释idselect查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序select_type查询类型table访问到的表partitions匹配的分区type访问类型possible_keys有可能会使用到的索引key实际使用到的索引,如果为NULL,则没有使用索引key_len索引中使用的字节数,可通过该列计算查询中使用的索引长度ref显示索引的哪一列被使用了rows估算找到所需数据需读取的行数filtered查询的表行占表的百分比extra包含不适合在其他列展示但异常重要的信息,比如是使用索引排序还是文件排序


2、EXPLAIN中的列


(1)id


1、标识select所属的行,sql语句中有多少个select就有多少个id,并且id的顺序是按照select出现的顺序增长的


2、id越大,越先执行


3、id相同,从上往下执行


4、id为NUll的最后执行


例如:(1)id相同,从上往下依次执行


explain
select * from student,class,class_student;

  



 


(2)id不同,id越大执行优先级越高


explain
select s.id,s.name,(select 1 from class) from student s;

  



 


(3)id相同又不同,id越大越先执行,id相同从上往下执行


explain
select id,name from student where id <5 union select s.id,s.
name from student s,class_student cs where cs.stu_id = s.id and cs.class_id = 4;

  



 


(2) select_type


显示对应行是简单还是复杂select,SIMPLE值表明没有子查询或Union,如果有子查询,那么最外层标记为Primary


(1)SUBQUERY 包含在select字段中的子查询,不在From语句中 例如:


explain
select id,name,(select class_id from class_student) from student;

  



 


(2)DERIVED 包含在From中的子查询,MySQL会递归执行并将结果放在一个临时表中,成为派生表,从子查询中派生出来的。


(3)UNION 在UNION中的第二个和随后的select被标记为UNION。第一个select被标记为外查询来执行,如果UNION被From子句中的子查询包含,那么它的第一个Select会被标记为DERIVED。


explain
select id,name from student where id>5
union
select id,name from student where id>15;

  



 


UNION被包含在From子句中的示例:


explain
select * from (
select id,name from student where id>15
union
select id,name from student where id<4
) a;

  



 


(4)UNION RESULT 用来从UNION的匿名临时表检索结果的select被标记为UNION RESULT。上例中可以看到


(5)DEPENDENT select依赖于外层查询中发现的数据。


explain
     select s.id,s.name,(select class_id from class_student cs where cs.stu_id = s.id) from student s;

 


(6)UNCACHEABLE select中的某些特性阻止结果被缓存在一个Item_cache中。


(3) table


显示当前行的数据来自于哪一张表


(4)type


访问类型,结果值从好到坏依次是:NULL>system>const>eq_ref>ref>range>index>ALL


一般来说,保证查询至少能到达range级别,最好能达到ref。


(1)system 表中只有一行数据(系统表)


(2)const 通过索引一次就能找到的数据,比如primary key 和union key,主键在where条件中,就能将查询转换成一个常量。比如:student表中id是主键


explain
select * from student where id = 1;

  



 


(3)eq_ref 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。


explain
select * from student s left join (select * from class_student where stu_id > 10)cs on cs.stu_id = s.id where cs.class_id = 4;

  



 


(4)ref 非唯一性索引扫描,返回匹配某个单独值得所有行 比如:student表中的age是一个普通索引


explain
select * from student where age = 20;

  



 


(5)range


1、只检索给定范围的行,使用一个索引来选择行,key列显示使用了哪个索引


2、一般就是where条件中出现“>”、“<”、“between”、“in”等条件


3、这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。


例如:


explain
select * from student where id > 5;

  



 


(6) All 全表扫描,不使用任何条件或索引。比如:student表中score字段是没有设置索引的。



 


(5)possible_keys


可能会使用到的key


(6)key


实际使用到的key


(7)key_len


索引中使用的字节数,可通过该列计算出使用的是哪些列,长度越短越好。显示的是索引字段的最大可能长度,并非实际使用长度,根据表定义计算而来,不是通过表内检索而来。


需要注意的是:1、char字段一个字符在utf8编码下最多占3个字节,可变长字段需要额外的两个字节记录长度,外加需要存入一个null值,一个null是一个字节 2、复合索引有最左前缀的特性,如果复合索引能全部使用上,则是复合索引字段的索引长度之和,这也可以用来判定复合索引是否部分使用,还是全部使用。


比如:student表的id是int类型,四个字节,所以key_len是4


explain
select * from student where id > 5;

  



 


name字段类型是varchar(20),所以ken_len=20*3+2+1=63


EXPLAIN
select * from student where name = "张三";

  



 


(8)ref


显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或者常量被用于查找索引列上的值。


explain
select * from student s,class_student cs where cs.stu_id=s.id and cs.class_id = 4;

  



 


从第二行可知,使用了student表中的主键查询,ken_len为4 ref为test.cs.stu_id表明使用了cs表中的stu_id字段。


(9) rows


根据表统计信息及索引选用情况,大致估算出找到所需的记录需要读取的行数。


(10)extra


值描述Using filesort说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为"文件排序"Using temporary使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。Using index表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!  如果同时出现using where,表明索引被用来执行索引键值的查找;   如果没有同时出现using where,表明索引用来读取数据而非执行查找动作Using where使用了where条件Using join buffer使用了连接缓存impossible wherewhere子句的值总是false,不能用来获取任何元素distinct一单mysql找到了与形相联合匹配的行,就不在搜索了


注意:当在Extra列出现了Using filesort时候,就说明可以建立相应的索引进行排序优化查询了。


下面是一个完整的思维导图:



 


===============================


我是Liusy,一个喜欢健身的程序员。


获取更多干货以及最新消息,请关注公众号:上古伪神


如果对您有帮助,点个关注就是对我最大的支持!!!


结尾小惊喜:公众号回复“MySQL”,送您一个MySQL的整体思维导图。


版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://www.cnblogs.com/liusy01/p/14492375.html

随机推荐

python入门:字符串操作

name='aBc'#首字母大写print(name.title())#Abc#全大写print(name.upper())#ABC#全小写print(name.lower()...

LittleFlyCloud 阅读(983)

Rxlifecycle 源码解析

Rxlifecycle使用依赖调用测试是否可用?原理bindToLifecycle和bindUntilEventbindUntilEvent:bindToLifecycle...

蓝天白云水清澈 阅读(359)

网络github_[网络]通过api启动GitHub云服务器

网络github_[网络]通过api启动GitHub云服务器

[网络]登录上“真香”的GitHub云和[网络]薅资本主义羊毛之GitHub编译OpenWrt中的启动Actions都是在push代码上去后,是否可以通过api方式控制呢,...

痔知识 阅读(307)

语言模型介绍(一)

本文翻译自ysda的自然语言处理课程课件,原文网址为:https://lena-voita.github.io/nlp_course/language_modeling.h...

qq_40729302 阅读(975)

瑞云APaaS平台上线,加快企业“云”转型步伐

面对互联网日趋多元化的趋势,传统的SaaS模式因其本身的配置扩展能力不够灵活,已不能满足当前企业业务发展需求,要想真正为客户提供专属服务、随需而定的解决方案&...

瑞云服务云 阅读(481)