导读
作为一个后端程序员,数据库这个东西是绕不开的,特别是写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相同,从上往下依次执行
12 | explainselect * from student,class,class_student; |
(2)id不同,id越大执行优先级越高
12 | explainselect s.id,s.name,(select 1 from class) from student s; |
(3)id相同又不同,id越大越先执行,id相同从上往下执行
123 | explainselect 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语句中 例如:
12 | explainselect id,name,(select class_id from class_student) from student; |
(2)DERIVED 包含在From中的子查询,MySQL会递归执行并将结果放在一个临时表中,成为派生表,从子查询中派生出来的。
(3)UNION 在UNION中的第二个和随后的select被标记为UNION。第一个select被标记为外查询来执行,如果UNION被From子句中的子查询包含,那么它的第一个Select会被标记为DERIVED。
1234 | explainselect id,name from student where id>5unionselect id,name from student where id>15; |
UNION被包含在From子句中的示例:
123456 | explainselect * from (select id,name from student where id>15unionselect 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是主键
12 | explainselect * from student where id = 1; |
(3)eq_ref 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
12 | explainselect * 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是一个普通索引
12 | explainselect * from student where age = 20; |
(5)range
1、只检索给定范围的行,使用一个索引来选择行,key列显示使用了哪个索引
2、一般就是where条件中出现“>”、“<”、“between”、“in”等条件
3、这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。
例如:
12 | explainselect * 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
12 | explainselect * from student where id > 5; |
name字段类型是varchar(20),所以ken_len=20*3+2+1=63
12 | EXPLAINselect * from student where name = '张三'; |
(8)ref
显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或者常量被用于查找索引列上的值。
12 | explainselect * 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时候,就说明可以建立相应的索引进行排序优化查询了。 下面是一个完整的思维导图: