北京荨麻疹治疗好医院 https://m-mip.39.net/pf/mipso_5059008.html
本文大纲《“数据分析师”面试最怕被问到的SQL优化问题（上）》讲解使用的数据源5.explain执行计划常用关键字详解1）id关键字的使用说明①案例：查询课程编号为2或教师证编号为3的老师信息；

#查看执行计划explainselectt.*fromteachert,coursec,teacherCardtcwheret.tid=c.tidandt.tcid=tc.tcidand(c.cid=2ortc.tcid=3);结果如下：然后，在往teacher表中增加3条数据。

insertintoteachervalues(4,ta,4);insertintoteachervalues(5,tb,5);insertintoteachervalues(6,tc,6);此时，我们再次查看执行计划。

#查看执行计划explainselectt.*fromteachert,coursec,teacherCardtcwheret.tid=c.tidandt.tcid=tc.tcidand(c.cid=2ortc.tcid=3);结果如下：

#下面举一个例子abc最终：2*3*4=6*4=24cba最终：4*3*2=12*2=24②案例：查询教授SQL课程的老师的描述(desc)；

#查看执行计划explainselecttc.tcdescfromteacherCardtcwheretc.tcid=(selectt.tcidfromteachertwheret.tid=(selectc.tidfromcoursecwherec.cname=sql));结果如下：③针对②做一个简单的修改

#查看执行计划explainselectt.tname,tc.tcdescfromteachert,teacherCardtcwheret.tcid=tc.tcidandt.tid=(selectc.tidfromcoursecwherecname=sql);结果如下：2）select_type关键字的使用说明：查询类型①simple：简单查询不包含子查询，不包含union查询。

explainselect*fromteacher;结果如下：②primary：包含子查询的主查询(最外层)③subquery：包含子查询的主查询(非最外层)

#查看执行计划explainselectt.tname,tc.tcdescfromteachert,teacherCardtcwheret.tcid=tc.tcidandt.tid=(selectc.tidfromcoursecwherecname=sql);结果如下：④derived：衍生查询(用到了临时表)a.在from子查询中，只有一张表；b.在from子查询中，如果table1uniontable2，那么table1就是derived表；

explainselectcr.cnamefrom(select*fromcoursewheretid=1unionselect*fromcoursewheretid=2)cr;结果如下：⑤union：union之后的表称之为union表，如上例⑥unionresult：告诉我们，哪些表之间使用了union查询3）type关键字的使用说明：索引类型①system源表(数据源)只有一条数据(实际中，基本不可能)；衍生表只有一条数据的主查询(偶尔可以达到)。②const仅能查到一条数据的SQL，只针对Primarykey主键索引或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;结果如下：④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.tid3;结果如下：⑥index查询全部索引中的数据(扫描整个索引)⑦ALL查询全部源表中的数据(暴力扫描全表)4）possible_keys和keypossible_keys可能用到的索引。是一种预测，不准。了解一下就好。key指的是实际使用的索引。

#先给course表的cname字段，添加一个索引createindexcname_indexoncourse(cname);#查看执行计划explainselectt.tname,tc.tcdescfromteachert,teacherCardtcwheret.tcid=tc.tcidandt.tid=(selectc.tidfromcoursecwherecname=sql);结果如下：5）key_len索引的长度：用于判断复合索引是否被完全使用(a,b,c)。①新建一张新表，用于测试

#创建表createtabletest_kl(namechar(20)notnulldefault);#添加索引altertabletest_kladdindexindex_name(name);#查看执行计划explainselect*fromtest_klwherename=;结果如下：②给test_kl表，新增name1列，该列没有设置“notnull”

#新增一个字段name1，name1可以为nullaltertabletest_kladdcolumnname1char(20);#给name1字段，设置为索引字段altertabletest_kladdindexindex_name1(name1);#查看执行计划explainselect*fromtest_klwherename1=;结果如下：③删除原来的索引name和name1，新增一个复合索引

#删除原来的索引name和name1dropindexindex_nameontest_kl;dropindexindex_name1ontest_kl;#增加一个复合索引createindexname_name1_indexontest_kl(name,name1);#查看执行计划explainselect*fromtest_klwherename1=;explainselect*fromtest_klwherename=;结果如下：④再次怎加一个name2字段，并为该字段创建一个索引。不同的是：该字段数据类型是varchar

#新增一个字段name2，name2可以为nullaltertabletest_kladdcolumnname2varchar(20);#给name2字段，设置为索引字段altertabletest_kladdindexname2_index(name2);#查看执行计划explainselect*fromtest_klwherename2=;结果如下：6）ref这里的ref的作用，指明当前表所参照的字段。注意与type中的ref值区分。在type中，ref只是type类型的一种选项值。

#给course表的tid字段，添加一个索引createindextid_indexoncourse(tid);#查看执行计划explainselect*fromcoursec,teachertwherec.tid=t.tidandt.tname=tw;结果如下：7）rows(这个目前还是有点疑惑)被索引优化查询的数据个数(实际通过索引而查询到的数据个数)

explainselect*fromcoursec,teachertwherec.tid=t.tidandt.tname=tz;结果如下：8）extra①usingfilesort：针对单索引的情况当出现了这个词，表示你当前的SQL性能消耗较大。表示进行了一次额外的排序。常见于orderby语句中。

#新建一张表，建表同时创建索引createtabletest02(a1char(3),a2char(3),a3char(3),indexidx_a1(a1),indexidx_a2(a2),indexidx_a3(a3));#查看执行计划explainselect*fromtest02wherea1=orderbya1;explainselect*fromtest02wherea1=orderbya2;结果如下：②usingfilesort：针对复合索引的情况(贼重要)不能跨列(官方术语：最佳左前缀)，这句话一定要牢记！！！

#删除test02的索引dropindexidx_a1ontest02;dropindexidx_a2ontest02;dropindexidx_a3ontest02;#创建一个复合索引altertabletest02addindexidx_a1_a2_a3(a1,a2,a3);#查看下面SQL语句的执行计划explainselect*fromtest02wherea1=orderbya3;--usingfilesortexplainselect*fromtest02wherea2=orderbya3;--usingfilesortexplainselect*fromtest02wherea1=orderbya2;结果如下：③usingtemporary当出现了这个词，也表示你当前的SQL性能消耗较大。这是由于当前SQL用到了临时表，一般出现在groupby中。

explainselecta1fromtest02wherea1in(1,2,3)groupbya1;explainselecta1fromtest02wherea1in(1,2,3)groupbya2;--usingtemporary结果如下：

selectdinstinct..from..join..on..where..groupby..having..orderby..limit..Ⅱ解析过程

from..on..join..where..groupby..having..selectdinstinct..orderby..limit..

explainselect*fromtest03wherea2=2anda4=4groupbya2,a4;explainselect*fromtest03wherea2=2anda4=4groupbya3;④usingindex当你看到这个关键词，恭喜你，表示你的SQL性能提升了。usingindex称之为索引覆盖。当出现了usingindex，就表示不用读取源表，而只利用索引获取数据，不需要回源表查询。只要使用到的列，全部出现在索引中，就是索引覆盖。

#删除test02中的复合索引idx_a1_a2_a3dropindexidx_a1_a2_a3ontest02;#重新创建一个复合索引idx_a1_a2createindexidx_a1_a2ontest02(a1,a2);#查看执行计划explainselecta1,a3fromtest02wherea1=ora3=;explainselecta1,a2fromtest02wherea1=anda2=;结果如下：

explainselecta1,a2fromtest02wherea1=ora2=;explainselecta1,a2fromtest02;结果如下：如果用到了索引覆盖(usingindex时)，会对possible_keys和key造成影响：a.如果没有where，则索引只出现在key中；b.如果有where，则索引出现在key和possible_keys中。⑤usingwhere表示需要，表示既在索引中进行了查询，又回到了源表进行了查询。

#删除test02中的复合索引idx_a1_a2dropindexidx_a1_a2ontest02;#将a1字段，新增为一个索引createindexa1_indexontest02(a1);#查看执行计划explainselecta1,a3fromtest02wherea1=""anda3="";结果如下：⑥impossiblewhere(了解)当where子句永远为False的时候，会出现impossiblewhere。

#查看执行计划explainselecta1fromtest02wherea1="a"anda1="b";结果如下：6.优化示例1）引入案例

#创建新表createtabletest03(a1int(4)notnull,a2int(4)notnull,a3int(4)notnull,a4int(4)notnull);#创建一个复合索引createindexa1_a2_a3_test03ontest03(a1,a2,a3);#查看执行计划explainselecta3fromtest03wherea1=1anda2=2anda3=3;结果如下：

#查看执行计划explainselecta3fromtest03wherea3=1anda2=2anda1=3;结果如下：

#查看执行计划explainselecta3fromtest03wherea1=1anda3=2groupbya3;结果如下：2）单表优化

#创建新表createtablebook(bidint(4)primarykey,namevarchar(20)notnull,authoridint(4)notnull,publicidint(4)notnull,typeidint(4)notnull);#插入数据insertintobookvalues(1,tjava,1,1,2);insertintobookvalues(2,tc,2,1,2);insertintobookvalues(3,wx,3,2,1);insertintobookvalues(4,math,4,2,3);结果如下：

explainselectbidfrombookwheretypeidin(2,3)andauthorid=1orderbytypeiddesc;结果如下：

from..on..join..where..groupby..having..selectdinstinct..orderby..limit..????????????????????????????????????????????????????????????????????????????????????????????????????

#添加索引createindextypeid_authorid_bidonbook(typeid,authorid,bid);#再次查看执行计划explainselectbidfrombookwheretypeidin(2,3)andauthorid=1orderbytypeiddesc;结果如下：将in字段放在最后面。需要注意一点：每次创建新的索引的时候，最好是删除以前的废弃索引，否则有时候会产生干扰(索引之间)。

#删除以前的索引dropindextypeid_authorid_bidonbook;#再次创建索引createindexauthorid_typeid_bidonbook(authorid,typeid,bid);#再次查看执行计划explainselectbidfrombookwhereauthorid=1andtypeidin(2,3)orderbytypeiddesc;结果如下：总结如下：a.最佳做前缀，保持索引的定义和使用的顺序一致性。b.索引需要逐步优化(每次创建新索引，根据情况需要删除以前的废弃索引)。c.将含in的范围查询，放到where条件的最后，防止失效。

explainselectbidfrombookwhereauthorid=1andtypeid=3orderbytypeiddesc;结果如下：3）两表优化

#创建teacher2新表createtableteacher2(tidint(4)primarykey,cidint(4)notnull);#插入数据insertintoteacher2values(1,2);insertintoteacher2values(2,1);insertintoteacher2values(3,3);#创建course2新表createtablecourse2(cidint(4),cnamevarchar(20));#插入数据insertintocourse2values(1,java);insertintocourse2values(2,python);insertintocourse2values(3,kotlin);结果如下：

explainselect*fromteacher2tleftouterjoincourse2cont.cid=c.cidwherec.cname=java;结果如下：①优化对于两张表，索引往哪里加？答：对于表连接，小表驱动大表。索引建立在经常使用的字段上。为什么小表驱动大表好一些呢？

小表:10大表:#小表驱动大表select...where小表.x10=大表.x;for(inti=0;i小表.length10;i++){for(intj=0;j大表.length;j++){...}}#大表驱动小表select...where大表.x=小表.x10;for(inti=0;i大表.length;i++){for(intj=0;j小表.length10;j++){...}}

#给左表的字段加索引createindexcid_teacher2onteacher2(cid);#查看执行计划explainselect*fromteacher2tleftouterjoincourse2cont.cid=c.cidwherec.cname=java;结果如下：

#给cname的字段加索引createindexcname_course2oncourse2(cname);#查看执行计划explainselectt.cid,c.cnamefromteacher2tleftouterjoincourse2cont.cid=c.cidwherec.cname=java;结果如下：4）三表优化大于等于2张表，优化原则一样；小表驱动大表；索引建立在经常查询的字段上；7、避免索引失效的一些原则①复合索引需要注意的点a.复合索引，不要跨列或无序使用(最佳左前缀)；b.复合索引，尽量使用全索引匹配，也就是说，你建立几个索引，就使用几个索引；②不要在索引上进行任何操作(计算、函数、类型转换)，否则索引失效

explainselect*frombookwhereauthorid=1andtypeid=2;explainselect*frombookwhereauthorid*2=1andtypeid=2;结果如下：③索引不能使用不等于（!=）或isnull(isnotnull)，否则自身以及右侧所有全部失效(针对大多数情况)。复合索引中如果有，则自身和右侧索引全部失效。

#针对不是复合索引的情况explainselect*frombookwhereauthorid!=1andtypeid=2;explainselect*frombookwhereauthorid!=1andtypeid!=2;结果如下：再观看下面这个案例：

#删除单独的索引dropindexauthorid_indexonbook;dropindextypeid_indexonbook;#创建一个复合索引altertablebookaddindexidx_book_at(authorid,typeid);#查看执行计划explainselect*frombookwhereauthorid1andtypeid=2;explainselect*frombookwhereauthorid=1andtypeid2;结果如下：④SQL优化，是一种概率层面的优化。至于是否实际使用了我们的优化，需要通过explain进行推测。

#删除复合索引dropindexauthorid_typeid_bidonbook;#为authorid和typeid，分别创建索引createindexauthorid_indexonbook(authorid);createindextypeid_indexonbook(typeid);#查看执行计划explainselect*frombookwhereauthorid=1andtypeid=2;结果如下：

#查看执行计划explainselect*frombookwhereauthorid=1andtypeid=2;结果如下：⑤索引覆盖，百分之百没问题⑥like尽量以“常量”开头，不要以%开头，否则索引失效

explainselect*fromteacherwheretnamelike"%x%";explainselect*fromteacherwheretnamelikex%;explainselecttnamefromteacherwheretnamelike%x%;结果如下：⑦尽量不要使用类型转换（显示、隐式），否则索引失效

explainselect*fromteacherwheretname=abc;explainselect*fromteacherwheretname=;结果如下：⑧尽量不要使用or，否则索引失效

explainselect*fromteacherwheretname=andtcid1;explainselect*fromteacherwheretname=ortcid1;结果如下：注意：or很猛，会让自身索引和左右两侧的索引都失效。8、一些其他的优化方法1）exists和in的优化如果主查询的数据集大，则使用i关键字，效率高。如果子查询的数据集大，则使用exist关键字,效率高。

select..fromtablewhereexist(子查询);select..fromtablewhere字段in(子查询);2）orderby优化IO就是访问硬盘文件的次数。usingfilesort有两种算法：双路排序、单路排序（根据IO的次数）MySQL4.1之前默认使用双路排序；双路：扫描2次磁盘(1：从磁盘读取排序字段,对排序字段进行排序(在buffer中进行的排序)2：扫描其他字段)MySQL4.1之后默认使用单路排序：只读取一次（全部字段），在buffer中进行排序。但种单路排序会有一定的隐患(不一定真的是“单路/1次IO”，有可能多次IO)。原因：如果数据量特别大，则无法将所有字段的数据一次性读取完毕，因此会进行“分片读取、多次读取”。注意：单路排序比双路排序会占用更多的buffer。单路排序在使用时，如果数据大，可以考虑调大buffer的容量大小：

#不一定真的是“单路/1次IO”，有可能多次IOsetmax_length_for_sort_data=a.选择使用单路、双路；调整buffer的容量大小；b.避免使用select*...（select后面写所有字段，也比写*效率高）c.复合索引，不要跨列使用，避免usingfilesortd.保证全部的排序字段，排序的一致性（都是升序或降序）黄伟呢

我要鼓励黄同学原创