1)id关键字的使用说明
① 案例:查询课程编号为2 或 教师证编号为3 的老师信息:
#查看执行计划 explainselectt.* fromteachert,coursec,teacherCardtc wheret.tid=c.tidandt.tcid=tc.tcid and(c.cid=2ortc.tcid=3);
结果如下:
接着,在往teacher表中增加几条数据。
insertintoteachervalues(4,'ta',4); insertintoteachervalues(5,'tb',5); insertintoteachervalues(6,'tc',6);
再次查看执行计划。
#查看执行计划 explainselectt.* fromteachert,coursec,teacherCardtc wheret.tid=c.tidandt.tcid=tc.tcid and(c.cid=2ortc.tcid=3);
结果如下:
表的执行顺序 ,因表数量改变而改变的原因:笛卡尔积。
abc 234 最终:2 * 3 * 4 = 6 * 4 = 24 cba 432 最终:4 * 3 * 2 = 12 * 2 = 24
分析:最终执行的条数,虽然是一致的。但是中间过程,有一张临时表是6,一张临时表是12,很明显6 < 12,对于内存来说,数据量越小越好,因此优化器肯定会选择第一种执行顺序。
结论:id值相同,从上往下顺序执行。表的执行顺序因表数量的改变而改变。
② 案例:查询教授SQL课程的老师的描述(desc)
#查看执行计划 explainselecttc.tcdescfromteacherCardtc wheretc.tcid= ( selectt.tcidfromteachert wheret.tid= (selectc.tidfromcoursecwherec.cname='sql') );
结果如下:
结论:id值不同,id值越大越优先查询。这是由于在进行嵌套子查询时,先查内层,再查外层。
③ 针对②做一个简单的修改
#查看执行计划 explainselectt.tname,tc.tcdescfromteachert,teacherCardtc wheret.tcid=tc.tcid andt.tid=(selectc.tidfromcoursecwherecname='sql');
结果如下:
结论:id值有相同,又有不同。id值越大越优先;id值相同,从上往下顺序执行。
2)select_type关键字的使用说明:查询类型
① simple:简单查询
不包含子查询,不包含union查询。
explainselect*fromteacher;
结果如下:
② primary:包含子查询的主查询(最外层)
③ subquery:包含子查询的主查询(非最外层)
④ derived:衍生查询(用到了临时表)
a.在from子查询中,只有一张表;
b.在from子查询中,如果table1 union table2,则table1就是derived表;
explainselectcr.cname from(select*fromcoursewheretid=1unionselect*fromcoursewheretid=2)cr;
结果如下:
⑤ union:union之后的表称之为union表,如上例
⑥ union result:告诉我们,哪些表之间使用了union查询
3)type关键字的使用说明:索引类型
system、const只是理想状况,实际上只能优化到index --> range --> ref这个级别。要对type进行优化的前提是,你得创建索引。
① system
源表只有一条数据(实际中,基本不可能);
衍生表只有一条数据的主查询(偶尔可以达到)。
② const
仅仅能查到一条数据的SQL ,仅针对Primary key或unique索引类型有效。
explainselecttidfromtest01wheretid=1;
结果如下:
删除以前的主键索引后,此时我们添加一个其他的普通索引:
createindextest01_indexontest01(tid); #再次查看执行计划 explainselecttidfromtest01wheretid=1;
结果如下:
③ eq_ref
唯一性索引,对于每个索引键的查询,返回匹配唯一行数据(有且只有1个,不能多 、不能0),并且查询结果和数据条数必须一致。
此种情况常见于唯一索引和主键索引。
deletefromteacherwheretcid>=4; altertableteacherCardaddconstraintpk_tcidprimarykey(tcid); altertableteacheraddconstraintuk_tciduniqueindex(tcid); explainselectt.tcidfromteachert,teacherCardtcwheret.tcid=tc.tcid;
结果如下:
总结:以上SQL,用到的索引是t.tcid,即teacher表中的tcid字段;如果teacher表的数据个数和连接查询的数据个数一致(都是3条数据),则有可能满足eq_ref级别;否则无法满足。条件很苛刻,很难达到。
④ ref
非唯一性索引,对于每个索引键的查询,返回匹配的所有行(可以0,可以1,可以多)
准备数据:
创建索引,并查看执行计划:
#添加索引 altertableteacheraddindexindex_name(tname); #查看执行计划 explainselect*fromteacherwheretname='tz';
结果如下:
⑤ range
检索指定范围的行 ,where后面是一个范围查询(between, >, <, >=, in)
in有时候会失效,从而转为无索引时候的ALL
#添加索引 altertableteacheraddindextid_index(tid); #查看执行计划:以下写了一种等价SQL写法,查看执行计划 explainselectt.*fromteachertwheret.tidin(1,2); explainselectt.*fromteachertwheret.tid<3;
结果如下:
⑥ index
查询全部索引中的数据(扫描整个索引)
⑦ ALL
查询全部源表中的数据(暴力扫描全表)
注意:cid是索引字段,因此查询索引字段,只需要扫描索引表即可。但是tid不是索引字段,查询非索引字段,需要暴力扫描整个源表,会消耗更多的资源。
4)possible_keys和key
possible_keys可能用到的索引。是一种预测,不准。了解一下就好。
key指的是实际使用的索引。
#先给course表的cname字段,添加一个索引 createindexcname_indexoncourse(cname); #查看执行计划 explainselectt.tname,tc.tcdescfromteachert,teacherCardtc wheret.tcid=tc.tcid andt.tid=(selectc.tidfromcoursecwherecname='sql');
结果如下:
有一点需要注意的是:如果possible_key/key是NULL,则说明没用索引。
5)key_len
索引的长度,用于判断复合索引是否被完全使用(a,b,c)。
① 新建一张新表,用于测试
#创建表 createtabletest_kl ( namechar(20)notnulldefault'' ); #添加索引 altertabletest_kladdindexindex_name(name); #查看执行计划 explainselect*fromtest_klwherename='';
结果如下:
结果分析:因为我没有设置服务端的字符集,因此默认的字符集使用的是latin1,对于latin1一个字符代表一个字节,因此这列的key_len的长度是20,表示使用了name这个索引。
② 给test_kl表,新增name1列,该列没有设置“not null”
结果如下:
结果分析:如果索引字段可以为null,则mysql底层会使用1个字节用于标识。
③ 删除原来的索引name和name1,新增一个复合索引
#删除原来的索引name和name1 dropindexindex_nameontest_kl; dropindexindex_name1ontest_kl; #增加一个复合索引 createindexname_name1_indexontest_kl(name,name1); #查看执行计划 explainselect*fromtest_klwherename1='';--121 explainselect*fromtest_klwherename='';--60
结果如下:
结果分析:对于下面这个执行计划,可以看到我们只使用了复合索引的第一个索引字段name,因此key_len是20,这个很清楚。再看上面这个执行计划,我们虽然仅仅在where后面使用了复合索引字段中的name1字段,但是你要使用复合索引的第2个索引字段,会默认使用了复合索引的第1个索引字段name,由于name1可以是null,因此key_len = 20 + 20 + 1 = 41呀!
④ 再次怎加一个name2字段,并为该字段创建一个索引。
不同的是:该字段数据类型是varchar
#新增一个字段name2,name2可以为null altertabletest_kladdcolumnname2varchar(20); #给name2字段,设置为索引字段 altertabletest_kladdindexname2_index(name2); #查看执行计划 explainselect*fromtest_klwherename2='';
结果如下:
结果分析:key_len = 20 + 1 + 2,这个20 + 1我们知道,这个2又代表什么呢?原来varchar属于可变长度,在mysql底层中,用2个字节标识可变长度。
6)ref
这里的ref的作用,指明当前表所参照的字段。
注意与type中的ref值区分。在type中,ref只是type类型的一种选项值。
#给course表的tid字段,添加一个索引 createindextid_indexoncourse(tid); #查看执行计划 explainselect*fromcoursec,teachert wherec.tid=t.tid andt.tname='tw';
结果如下:
结果分析:有两个索引,c表的c.tid引用的是t表的tid字段,因此可以看到显示结果为【数据库名.t.tid】,t表的t.name引用的是一个常量"tw",因此可以看到结果显示为const,表示一个常量。
7)rows(这个目前还是有点疑惑)
被索引优化查询的数据个数 (实际通过索引而查询到的数据个数)
explainselect* fromcoursec,teachert wherec.tid=t.tid andt.tname='tz';
结果如下:
8)extra
表示其他的一些说明,也很有用。
① using filesort:针对单索引的情况
当出现了这个词,表示你当前的SQL性能消耗较大。表示进行了一次“额外”的排序。常见于order by语句中。
Ⅰ 什么是“额外”的排序?
为了讲清楚这个,我们首先要知道什么是排序。我们为了给某一个字段进行排序的时候,首先你得先查询到这个字段,然后在将这个字段进行排序。
紧接着,我们查看如下两个SQL语句的执行计划。
#新建一张表,建表同时创建索引 createtabletest02 ( a1char(3), a2char(3), a3char(3), indexidx_a1(a1), indexidx_a2(a2), indexidx_a3(a3) ); #查看执行计划 explainselect*fromtest02wherea1=''orderbya1; explainselect*fromtest02wherea1=''orderbya2;
结果如下:
结果分析:对于第一个执行计划,where后面我们先查询了a1字段,然后再利用a1做了依次排序,这个很轻松。但是对于第二个执行计划,where后面我们查询了a1字段,然而利用的却是a2字段进行排序,此时myql底层会进行一次查询,进行“额外”的排序。
总结:对于单索引,如果排序和查找是同一个字段,则不会出现using filesort;如果排序和查找不是同一个字段,则会出现using filesort;因此where哪些字段,就order by哪些些字段。
② using filesort:针对复合索引的情况
不能跨列(官方术语:最佳左前缀)
#删除test02的索引 dropindexidx_a1ontest02; dropindexidx_a2ontest02; dropindexidx_a3ontest02; #创建一个复合索引 altertabletest02addindexidx_a1_a2_a3(a1,a2,a3); #查看下面SQL语句的执行计划 explainselect*fromtest02wherea1=''orderbya3;--usingfilesort explainselect*fromtest02wherea2=''orderbya3;--usingfilesort explainselect*fromtest02wherea1=''orderbya2;
结果如下:
结果分析:复合索引的顺序是(a1,a2,a3),可以看到a1在最左边,因此a1就叫做“最佳左前缀”,如果要使用后面的索引字段,必须先使用到这个a1字段。对于explain1,where后面我们使用a1字段,但是后面的排序使用了a3,直接跳过了a2,属于跨列;对于explain2,where后面我们使用了a2字段,直接跳过了a1字段,也属于跨列;对于explain3,where后面我们使用a1字段,后面使用的是a2字段,因此没有出现【using filesort】。
③ using temporary
当出现了这个词,也表示你当前的SQL性能消耗较大。这是由于当前SQL用到了临时表。一般出现在group by中。
explainselecta1fromtest02wherea1in('1','2','3')groupbya1; explainselecta1fromtest02wherea1in('1','2','3')groupbya2;--usingtemporary
结果如下:
结果分析:当你查询哪个字段,就按照那个字段分组,否则就会出现using temporary。
针对using temporary,我们在看一个例子:
using temporary表示需要额外再使用一张表,一般出现在group by语句中。虽然已经有表了,但是不适用,必须再来一张表。
再次来看mysql的编写过程和解析过程。
Ⅰ 编写过程
selectdinstinct..from..join..on..where..groupby..having..orderby..limit..
Ⅱ 解析过程
from..on..join..where..groupby..having..selectdinstinct..orderby..limit..
很显然,where后是group by,然后才是select。基于此,我们再查看如下两个SQL语句的执行计划。
explainselect*fromtest03wherea2=2anda4=4groupbya2,a4; explainselect*fromtest03wherea2=2anda4=4groupbya3;
分析如下:对于第一个执行计划,where后面是a2和a4,接着我们按照a2和a4分组,很明显这两张表已经有了,直接在a2和a4上分组就行了。但是对于第二个执行计划,where后面是a2和a4,接着我们却按照a3分组,很明显我们没有a3这张表,因此有需要再来一张临时表a3。因此就会出现using temporary。
④ using index
当你看到这个关键词,恭喜你,表示你的SQL性能提升了。
using index称之为“索引覆盖”。
当出现了using index,就表示不用读取源表,而只利用索引获取数据,不需要回源表查询。
只要使用到的列,全部出现在索引中,就是索引覆盖。
#删除test02中的复合索引idx_a1_a2_a3 dropindexidx_a1_a2_a3ontest02; #重新创建一个复合索引idx_a1_a2 createindexidx_a1_a2ontest02(a1,a2); #查看执行计划 explainselecta1,a3fromtest02wherea1=''ora3=''; explainselecta1,a2fromtest02wherea1=''anda2='';
结果如下:
结果分析:我们创建的是a1和a2的复合索引,对于第一个执行计划,我们却出现了a3,该字段并没有创建索引,因此没有出现using index,而是using where,表示我们需要回表查询。对于第二个执行计划,属于完全的索引覆盖,因此出现了using index。
针对using index,我们在查看一个案例:
explainselecta1,a2fromtest02wherea1=''ora2=''; explainselecta1,a2fromtest02;
结果如下:
如果用到了索引覆盖(using index时),会对possible_keys和key造成影响:
a.如果没有where,则索引只出现在key中;
b.如果有where,则索引 出现在key和possible_keys中。
⑤ using where
表示需要【回表查询】,表示既在索引中进行了查询,又回到了源表进行了查询。
#删除test02中的复合索引idx_a1_a2 dropindexidx_a1_a2ontest02; #将a1字段,新增为一个索引 createindexa1_indexontest02(a1); #查看执行计划 explainselecta1,a3fromtest02wherea1=""anda3="";
结果如下:
结果分析:我们既使用了索引a1,表示我们使用了索引进行查询。但是又对于a3字段,我们并没有使用索引,因此对于a3字段,需要回源表查询,这个时候出现了using where。
⑥ impossible where(了解)
当where子句永远为False的时候,会出现impossible where
#查看执行计划 explainselecta1fromtest02wherea1="a"anda1="b";
结果如下:
本站推荐
-
905
-
902
-
874
-
868
-
846