阿里的OceanBase已经上天了，但是你不知道用Explain看SQL查询计划吗？

Mysql性能优化神器详解。彻底的Mysql性能优化神器讲解。清晰的前言数据准备创建数据表插入数据explain命令使用select_typetyperowsrefextra总结前言SQL语句在不同的人手里会写成不同的形式，比如经常遇到的慢SQL查询，这时候往往需要针对SQL进行优化.为了保证SQL语句的高效运行，Mysql提供了Explain命令，用于分析SQL语句的语义，供开发者针对SQL进行优化。数据准备为了便于整个流程的执行，首先创建测试数据。创建数据表SQL的执行涉及到单表和多表的联合执行。这次创建了两个表来模拟这种情况。more多张表联合执行，两张表的执行计划是一样的。CREATETABLE`users`(`id`int(11)NOTNULLAUTO_INCREMENT,`create_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMP,`name`varchar(20)NOTNULLDEFAULT''COMMENT'username',`sex`tinyint(4)NOTNULLDEFAULT'1'COMMENT'性别',`phone`varchar(11)NOTNULLCOMMENT'电话号码',`desc`varchar(200)NOTNULLDEFAULT''COMMENT'介绍',主键(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT'usertable';CREATETABLE`order`(`id`int(11)NOTNULLAUTO_INCREMENT,`phone`varchar(11)NOTNULLCOMMENT'phonenumber',`name`varchar(20)NOTNULLCOMMENT'username',primarykey(`id`))ENGINE=InnoDBdefaultCHARSET=utf8COMMENT'order';为方便插入数据这次不再使用SQL语句，而是使用存储过程创建数据，简单、快速、方便。#Createstoredprocedurecreateprocedureinsert_user_data()begindeclareiint;declarenamevarchar(20);declarephone_numvarchar(11);set@SURNAME='WangLiZhangLiuChenYangHuangZhaoWuZhouXuSunMaZhuHuGuoHeGaoLinLuoZhengLiangXieSongTangWeiXuHanFengDengCaoPengZengXiaoTianDongPanYuanYuJiangCaiYuDuYeChengSuWeiLuDingRenShenYaoLuJiangCuiZhongTanLuWangFanJinShiLiaoJiaXiaWeiFuFangBaiZouMengXiongQinQiuJiangYinXueYanDuanLeiHouLongShiTaoLiHeGuMaoHaoGongShaoWanQianYanQinWuDaiMoKongXiangtang';set@NAME='DanJuyi'smusicbookQianYunalsofromgenerationtoWeiYouJunXiuJianAoErYuanGuangLanDongIcecoldLingNingFanKaiChuLiqinThousandsofflowers,halfofChina,Nanbo,andfriends,XiangJuntingandZheJia,thestrongcity,thesummernight,theskyisaswonderfulasthewonderfulchild,JiGuyu,AnWanchen,HanXun,ErYaoshan,LanJun,Qiaoping,Youkang,Jiankai,Hongqiang,Tongyan,BinPeng,Reminiscence,RemembranceandPityHateXiMuChengqingMinWenXinXuanXuHaoMingYiXinYingChunYuJinXiaoHanShengJingQingZhiManPengLangJieSongFengBaiRouLiuGeTaoMengKaiHuaiZhengShuiPeiBoZeCleanYangJiPuHaoHaiTaoRunHanYuanPuPuHanLingCanYanYeRanXuanYuXiYiYuShanZhenLiQiQinYuRuiYaoJinPuChiHaoHopeTrueRuiBiLeiXiangQiBingChengLiZhuXiaoZiShaoJingLuQunCuiHanZhiHangLiangFuZhiCangYuanRuomaoRonglianHanLingxuanRongLanruiLeiweibutterflyseekssincerewordsGuHaoFusurpassesXuanHuiDaYuandrunkJinXinJinWenYanYaYuXueLinFrostDewQingJingJingJingFengFlyingFragranceChiQianGaoHongpengHeli';seti=1;whilei<100000doSETphone_num=concat('1',substring(cast(3+(rand()*10)%7ASchar(50)),1,1),right(left(trim(cast(rand()ASchar(50))),11),9));setname=concat(substr(@surname,floor(rand()*length(@surname)/3+1),1),substr(@NAME,floor(rand()*length(@NAME)/3+1),1),substr(@NAME,floor(rand()*length(@NAME)/3+1),1));insertintousers(create_time,name,sex,phone,`desc`)values(now(),名称,rand()*1,phone_num,'测试');插入`order`(phone,name)values(phone_num,name);seti=i+1;endwhile;end#执行存储过程调用insert_user_data();#删除存储过程dropprocedureifexistsinsert_user_data;创建存储过程后，如果需要修改，可以直接使用删除的存储过程，然后重新创建。explain命令使用explain执行命令explainselect*fromuserswhereid=1\G;显示如下：***************************1.行****************************id:1select_type:SIMPLEtable:userspartitions:NULLtype:constpossible_keys:PRIMARYkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)一共有12个字段，每个字段的含义如下：id:每个查询statement会生成一个标识符，执行顺序为执行select_type：查询的类型，包含各种类型的跳转toselect_typetable：查询的表名，包括关联的表信息partitions：匹配的分区类型：表示Mysql在表中查找需要的行的方式，这里是使用索引的方式。typepossible_keys:Theindexkeythatmaybeusedbythequerystatement:查询语句实际使用的索引key_len:表示索引使用的字节数。注意显示的是索引字段中的最大可能长度，而不是实际长度需要阅读以找到所需的记录extra：此列的查询中包含的其他附加详细信息。有些字段的类型比较多，下面会详细说明。select_type用于表示每一种查询类型，常用类型如下：SIMPLE：最简单的查询方式，单表查询，不包括UNION和子查询，如select*fromuserswhereid=1PRIMAPY：表示次要query是最外层query。当有子查询时显示。解释select*fromuserswherephone=(selectphonefromorderwhereid=10);UNION：表示第二次查询是UNION的第二次或后续查询方式，查询语句中有union关键字explainselect*fromuserswhereid=10unionselect*fromuserswhereid=20;DEPENDENTUNION：UNION中的第二条或后续查询语句依赖于外层查询UNIONRESULT，UNION的结果。SUBQUERY：子查询中的第一个SELECT.DEPENDENTSUBQUERY：子查询中的第一个SELECT依赖于外部查询。当子查询依赖于外部查询结果时，会显示explainselect*fromuserswherephone=(selectphonefromorderwhereid=users.id)andid=10;。这里最常见的类型是SIMPLE类型，我们经常使用的多表查询也是SIMPLE类型。比如解释select*fromusersleftjoinorderoonusers.phone=o.phonewhereusers.id=10typetype字段帮助我们定位查询是否高效，是全表扫描还是索引扫描。不同的类型代表不同的性能，顺序如下：ALL、<=、>=、BETWEEN、IN运算符。解释从id>10和id<20的用户中选择电话；SQL语句中，>和<用于限制where条件和使用范围，$\color{red}{当语句中的字段不是索引时，不使用范围}$ref：使用非-查询中的唯一索引查询，并显示ref列中使用了哪一列或常量。虽然使用了索引，但是索引列中可以有多个值，比如phone列中出现了同一个手机号码。说明select*fromorderwherephone='16485461071'ref_eq:用法类似，但是比ref好，这个类型知道只有一个结果集。直接知道结果集是一条记录的索引就是主键索引和唯一索引。使用该类型时，查询多表时条件包括主键或唯一索引。说明select*fromusers,orderwhereusers.id=order.idconst:主键值作为where的条件查询，Mysql优化器会把这个查询转化为常量。说明select*fromorderwhereid=10;system:const类型的特例，当表中只有一行数据时，会使用system类型rows查询中需要扫描的行数.我们使用各种索引，优化就是减少扫描的行数。ref表示查询时表的连接匹配条件，可以是常量，也可以是查询列explainselect*fromusers,orderwhereusers.id=order.id;extraextra表示更多的sql查询信息，extra是Mysql查询计划中查询信息的重要补充。extra的类型如下：Distinct：找到第一行后，不再查找数据进行匹配，对应查询中的distinct去重查询。Usingfilesort：表示MYSQL使用内存排序或文件排序，排序不使用索引。可以在orderby、groupby语句中使用合适的索引来修改条件。使用临时表：使用临时表保存中间结果，常用于Groupby和Orderby语句查询。还要尽量避免使用临时表来保存中间结果。Notexists：在一些LEFTJOIN连接中，MYSQL使用优化器进行优化，改变原有QUERY的优化部分，减少数据访问次数。使用索引：查询时无需回表，直接通过索引获取查询到的数据。Usingunion：当用or连接各个索引条件时，表示信息表示union是从处理结果中得到的。使用intersect：当使用and连接各种索引条件时，表示该信息表示处理结果得到的并集。Usingsort_union/Usingsort_intersection：出现在and/or语句中，先查询主键信息，然后在数据读取中对结果进行排序合并。使用where：使用Where子句来限制数据的返回。注意：UsingUsingWhere是指Mysql服务器在进行条件过滤之前，会将存储引擎返回给服务层。使用joinbuffer：连接缓存有两种：blocknested-loopconnectionBlockNestedLoop，和IndexNested-LoopJoinusingindexquery。综上所述，了解SQL的查询计划。再写SQL的时候，用explain语句看看SQL的查询计划是什么。本文所用SQL语句已上传至github，需自行收集·END·路虽远，行必来点个赞。微信号：YoungRUIQ