MySQL之Explain性能分析

　　一、Explain概述
　　使用EXPLAIN关键字可以模拟优化器来执行SQL查询语句，从而知道MySQL是如何处理我们的SQL语句的。分析出查询语句或是表结构的性能瓶颈。1、MySQL查询过程
　　通过explain我们可以获得以下信息：表的读取顺序；数据读取操作的操作类型；哪些索引可以被使用；哪些索引真正被使用；表的直接引用；每张表的有多少行被优化器查询了；
　　Explain使用方式：explainsql语句，通过执行explain可以获得sql语句执行的相关信息。explainselectfromL1；
　　二、Explain详解
　　1）数据准备创建数据库CREATEDATABASEtestexplainCHARACTERSETutf8；创建表CREATETABLEL1（idINTPRIMARYKEYAUTOINCREMENT，titleVARCHAR（100））；CREATETABLEL2（idINTPRIMARYKEYAUTOINCREMENT，titleVARCHAR（100））；CREATETABLEL3（idINTPRIMARYKEYAUTOINCREMENT，titleVARCHAR（100））；CREATETABLEL4（idINTPRIMARYKEYAUTOINCREMENT，titleVARCHAR（100））；每张表插入3条数据INSERTINTOL1（title）VALUES（zhang001），（zhang002），（zhang003）；INSERTINTOL2（title）VALUES（zhang004），（zhang005），（zhang006）；INSERTINTOL3（title）VALUES（zhang007），（zhang008），（zhang009）；INSERTINTOL4（title）VALUES（zhang010），（zhang011），（zhang012）；
　　2）ID字段说明
　　select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
　　id相同，执行顺序由上至下EXPLAINSELECTFROML1，L2，L3WHEREL1。idL2。idANDL2。idL3。id；
　　id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行EXPLAINSELECTFROML2WHEREid（SELECTidFROML1WHEREid（SELECTL3。idFROML3WHEREL3。titlezhang））；
　　3）selecttype和table字段说明
　　表示查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询
　　simple：简单的select查询，查询中不包含子查询或者UNIONEXPLAINSELECTFROML1whereid1；
　　primary：查询中若包含任何复杂的子部分，最外层查询被标记
　　subquery：在select或where列表中包含了子查询EXPLAINSELECTFROML2WHEREid（SELECTidFROML1WHEREid（SELECTL3。idFROML3WHEREL3。titlezhang））；
　　union：union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表selecttype都是union
　　derived：在from列表中包含的子查询被标记为derived（派生表），MySQL会递归执行这些子查询，把结果放到临时表中
　　unionresult：UNION的结果EXPLAINSELECTFROM（selectfromL3unionselectfromL4）a；
　　4）type字段说明
　　type字段在MySQL官网文档描述如下：
　　Thejointype。Fordescriptionsofthedifffferenttypes。
　　type字段显示的是连接类型（jointype表示的是用什么样的方式来获取数据），它描述了找到所需数据所使用的扫描方式，是较为重要的一个指标。
　　下面给出各种连接类型，按照从最佳类型到最坏类型进行排序：完整的连接类型比较多systemconsteqrefreffulltextrefornullindexmergeuniquesubqueryindexsubqueryrangeindexALL简化之后，我们可以只关注一下几种systemconsteqrefrefrangeindexALL
　　一般来说，需要保证查询至少达到range级别，最好能到ref，否则就要就行SQL的优化调整
　　下面介绍type字段不同值表示的含义：
　　system：表中就仅有一行数据的时候。这是const连接类型的一个特例，很少出现。
　　const：const表示命中主键索引（primarykey）或者唯一索引（unique），表示通过索引一次就找到数据记录。因为只匹配一条记录，所以被连接的部分是一个常量。（如果将主键放在where条件中，MySQL就能将该查询转换为一个常量）这种类型非常快。
　　例如以下查询：explainselectfromL1whereid3；为L1表的title字段添加唯一索引altertableL1addunique（title）；explainselectfromL1wheretitlezhang；
　　eqref：对于前一个表中的每个一行，后表只有一行被扫描。除了system和const类型之外，这是最好的连接类型。只有当联接使用索引的部分都是主键或惟一非空索引时，才会出现这种类型。
　　例如以下查询：EXPLAINSELECTL1。id，L1。titleFROML1leftjoinL2onL1。idL2。id；
　　ref：非唯一性索引扫描（使用了普通索引），对于前表的每一行（row），后表可能有多于一行的数据被扫描，它返回所有匹配某个单独值的行。
　　例如以下查询：为L1表的title字段添加普通索引altertableL1addindexidxtitle（title）；EXPLAINSELECTFROML1innerjoinL2onL1。titleL2。title；
　　range：索引上的范围查询，检索给定范围的行，between，in函数，都是典型的范围（range）查询。
　　例如以下查询：EXPLAINSELECTFROML1WHEREL1。idbetween1and10；
　　注：当in函数中的数据很大时，可能会导致效率下降，最终不走索引
　　index：出现index是SQL使用了索引，但是没有通过索引进行过滤，需要扫描索引上的全部数据（查找所有索引树，比ALL快一些，因为索引文件要比数据文件小），一般是使用了索引进行排序分组。EXPLAINSELECTFROML2groupbyidorderbyid；该count查询需要通过扫描索引上的全部数据来计数EXPLAINSELECTcount（）FROML2；
　　ALL：没有使用到任何索引，连接查询时对于前表的每一行，后表都要被全表扫描。EXPLAINSELECTFROML3innerjoinL4onL3。titleL4。title；
　　总结各类type类型的特点：
　　5）possiblekeys与key说明
　　possiblekeys
　　显示可能应用到这张表上的索引，一个或者多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
　　key
　　实际使用的索引，若为null，则没有使用到索引。（两种可能，1。没建立索引，2。建立索引，但索引失效）。查询中若使用了覆盖索引，则该索引仅出现在key列表中。
　　1。理论上没有使用索引，但实际上使用了EXPLAINSELECTL3。idFROML3；
　　2。理论和实际上都没有使用索引EXPLAINSELECTFROML3WHEREtitlezhang007；
　　3。理论和实际上都使用了索引EXPLAINSELECTFROML2WHEREtitlezhang004；
　　6）keylen字段说明
　　表示索引中使用的字节数，可以通过该列计算查询中使用索引的长度。
　　keylen字段能够帮你检查是否充分利用了索引，kenlen越长，说明索引使用的越充分。
　　创建表
　　CREATETABLEL5（aINTPRIMARYKEY，bINTNOTNULL，cINTDEFAULTNULL，dCHAR（10）NOTNULL）；
　　使用explain进行测试EXPLAINSELECTFROML5WHEREa1ANDb1；
　　观察keylen的值，索引中只包含了1列是int类型，所以，keylen是4字节。
　　为b字段添加索引，进行测试
　　ALTERTABLEL5ADDINDEXidxb（b）；执行SQL，这次将b字段也作为条件EXPLAINSELECTFROML5WHEREa1ANDb1；
　　为c、d字段添加联合索引，然后进行测试ALTERTABLEL5ADDINDEXidxcb（c，d）；explainselectfromL5wherec1andd；
　　c字段是int类型4个字节，d字段是char（10）代表的是10个字符相当30个字节
　　数据库的字符集是utf8一个字符3个字节，d字段是char（10）代表的是10个字符相当30个字节，多出的一个字节用来表示是联合索引
　　下面这个例子中，虽然使用了联合索引，但是可以根据kenlen的长度推测出该联合索引只使用了一部分，没有充分利用索引，还有优化空间。explainselectfromL5wherec1；
　　7）ref字段说明
　　显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
　　L1。id1；1是常量，refconstEXPLAINSELECTFROML1WHEREL1。id1；
　　8）rows字段说明
　　表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。
　　L3中的title没有添加索引，所以L3中有3条记录，就需要读取3条记录进行查找。EXPLAINSELECTFROML3，L4WHEREL3。idL4。idANDL3。titleLIKEzhang007；
　　需要注意的是rows只是一个估算值，并不准确。所以rows行数过大的问题并不值得过多考虑，主要分析的还是索引是否使用正确了
　　9）fifiltered字段说明
　　它指返回结果的行占需要读到的行（rows列的值）的百分比。
　　10）extra字段说明
　　Extra是EXPLAIN输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息。
　　准备数据
　　CREATETABLEusers（uidINTPRIMARYKEYAUTOINCREMENT，unameVARCHAR（20），ageINT（11））；INSERTINTOusersVALUES（NULL，lisa，10）；INSERTINTOusersVALUES（NULL，lisa，10）；INSERTINTOusersVALUES（NULL，rose，11）；INSERTINTOusersVALUES（NULL，jack，12）；INSERTINTOusersVALUES（NULL，sam，13）；
　　Usingfifilesort
　　执行结果Extra为Usingfilesort，这说明，得到所需结果集，需要对所有记录进行文件排序。这类SQL语句性能极差，需要进行优化。
　　典型的，在一个没有建立索引的列上进行了orderby，就会触发fifilesort，常见的优化方案是，在orderby的列上添加索引，避免每次查询都全量排序。EXPLAINSELECTFROMusersORDERBYage；
　　Usingtemporary
　　表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询EXPLAINSELECTCOUNT（），unameFROMusersGROUPBYuname；
　　需要注意的是：返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化；使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断。例如本例查询的age未设置索引，所以返回的type为ALL，仍有优化空间，可以建立索引优化查询。EXPLAINSELECTFROMusersWHEREage10；
　　Usingindex
　　表示直接访问索引就能够获取到所需要的数据（覆盖索引），不需要通过索引回表。为uname创建索引altertableusersaddindexidxuname（uname）；EXPLAINSELECTuid，unameFROMusersWHEREunamelisa；
　　Usingjoinbuffffer
　　使用了连接缓存，会显示join连接查询时，MySQL选择的查询算法。EXPLAINSELECTFROMusersu1LEFTJOIN（SELECTFROMusersWHEREage1）u2ONu1。ageu2。age；
　　Usingjoinbuffer（BlockNestedLoop）说明，需要进行嵌套循环计算，这里每个表都有五
　　条记录，内外表查询的type都为ALL。
　　问题在于两个关联表的关联使用了字段age，并且age字段未建立索引，就会出现这种情况。
　　Usingindexcondition
　　查找使用了索引（但是只使用了一部分，一般是指联合索引），但是需要回表查询数。explainselectfromL5wherec10andd；
　　Extra主要指标的含义（有时会同时出现）usingindex：使用覆盖索引的时候就会出现；usingwhere：在查找使用索引的情况下，需要回表去查询所需的数据；usingindexcondition：查找使用了索引，但是需要回表查询数据；usingindexusingwhere：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据；