亲宝软件园·资讯

展开

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

上古伪神 人气:0

导读

作为一个后端程序员,数据库这个东西是绕不开的,特别是写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的整体思维导图。

加载全部内容

相关教程
猜你喜欢
用户评论