佛山网站优化排名推广,公司简介模板怎么做,成都设计公司电话,中装建设属于什么板块EXPLAIN是MySQL官方提供的sql分析的工具之一#xff0c;可以用于模拟优化器执行sql查询语句#xff0c;从而知道MySQL是如何处理sql语句。EXPLAIN主要用于分析查询语句或表结构的性能瓶颈。
以下是基于MySQL5.7.19版本进行分析的#xff0c;不同版本之间略有差异。
1、EXP…EXPLAIN是MySQL官方提供的sql分析的工具之一可以用于模拟优化器执行sql查询语句从而知道MySQL是如何处理sql语句。EXPLAIN主要用于分析查询语句或表结构的性能瓶颈。
以下是基于MySQL5.7.19版本进行分析的不同版本之间略有差异。
1、EXPLAIN使用
explainsql语句通过执行explain可以获得sql语句执行的相关信息。 2、EXPLAIN包含的信息
MySQL EXPLAIN包含字段 列名说明id执行编号标识select所属的行。如果在语句中没子查询或关联查询只有唯一的select每行都将显示1。否则内层的select语句一般会顺序编号对应于其在原始语句中的位置select_type显示本行是简单或复杂select。如果查询有任何复杂的子查询则最外层标记为PRIMARYDERIVED、UNION、UNION RESUlTtable访问引用哪个表引用某个查询如“user”partitions匹配的分区type数据访问/读取操作类型ALL、index、range、ref、eq_ref、const/system、NULLpossible_keys揭示哪一些索引可能有利于高效的查找key显示mysql决定采用哪个索引来优化查询key_len显示mysql在索引里使用的字节数ref显示了之前的表在key列记录的索引中查找值所用的列或常量rows为了找到所需的行而需要读取的行数估算值不精确。通过把所有rows列值相乘可粗略估算整个查询会检查的行数filtered表示此查询条件所过滤的数据的百分比extra额外信息如using index、filesort等
2.1 id
id是用来顺序标识整个查询中SELELCT语句的在嵌套查询中id越大的语句越先执行。该值可能为NULL如果这一行用来说明的是其他行的联合结果。大致分为下面几种情况
1id相同执行顺序由上至下
EXPLAIN select * from user,school,class where user.school_id school.school_id and user.class_id class.class_id 三个id都为1表示具有相同的优先级执行顺序由上而下具体执行顺序由优化器决定这里执行顺序为schooluserclass。
2id不同数字越大优先级越高
EXPLAIN select * from user where school_id (select school_id from school where school_id (select school_id from class where class_id 2))
如果sql中存在子查询那么id的序号会递增id越大越先被执行。如上述查询执行顺序是class、school、user也就是说最里面的子查询最先执行由里往外执行。 如果上述查询中的“”换成“in”则运行结果会发生变化原因是每执行一次最外层子查询里面的子查询都会被重复执行这也就是sql优化中提到的尽量不要使用in关键字 使用in关键字示例
EXPLAIN select * from user where school_id in (select school_id from school where school_id in (select school_id from class where class_id 2)) 2.2 select_type select_type表示查询的类型
类型说明simple简单子查询不包含子查询和unionprimary包含union或者子查询最外层的部分标记为primarysubquery一般子查询中的子查询被标记为subquery也就是位于select列表中的查询derived派生表——该临时表是从子查询派生出来的位于form中的子查询union位于union中第二个及其以后的子查询被标记为union第一个就被标记为primary如果是union位于from中则标记为derivedunion result用来从匿名临时表里检索结果的select被标记为union result
EXPLAIN select * from user where school_id (select school_id from school where school_id (select school_id from class where class_id 2)) 2.3 table
table显示查询操作基于哪张表的。
EXPLAIN select user.* from user where school_id (select school_id from school where school_id 2)
union
select user.* from user where class_id (select class_id from class where class_id 2) table列并不一定是真实存在的表比如上面出现union1,3一般来说会出现下面的取值
union a,b输出结果中编号为 a 的行与编号为 b 的行的结果集的并集。 derived a输出结果中编号为 a 的行的结果集derived 表示这是一个派生结果集如 FROM 子句中的查询。 subquery a输出结果中编号为 a 的行的结果集subquery 表示这是一个物化子查询。
2.4 partitions
partitions列是查询时匹配到的分区信息对于非分区表值为NULL当查询的是分区表时partitions显示分区表命中的分区情况。
2.5 type
type显示的是访问类型是较为重要的一个指标性能从最好到最坏依次是 system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL。一般来说得保证查询至少达到range级别最好能达到ref。
类型说明system这是const连接类型的一种特例表仅有一行满足条件。const当确定最多只会有一行匹配的时候MySQL优化器会在查询前读取它而且只读取一次因此非常快。当主键放入where子句时mysql把这个查询转为一个常量高效eq_ref最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 高效ref一种索引访问它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是它用在关联操作只使用了索引的最左前缀或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用或操作符的带索引的列。fulltext查询时使用 fulltext 索引。ref_or_null对于某个字段既需要关联条件也需要null 值的情况下。查询优化器会选择用ref_or_null 连接查询。index_merge在查询过程中需要多个索引组合使用通常出现在有or 关键字的sql 中。unique_subquery该联接类型类似于index_subquery。子查询中的唯一索引。在某些in子查询里用于替换eq_refindex_subquery利用索引来关联子查询不再全表扫描。用于非唯一索引子查询可以返回重复值。类似于unique_subquery但用于非唯一索引range范围扫描一个有限制的索引扫描。key 列显示使用了哪个索引。当使用、 、、、、、IS NULL、、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 rangeindex和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index说明正在使用覆盖索引只扫描索引的数据它比按索引次序全表扫描的开销要小很多all全表扫描性能最差。
2.6 possible_keys
查询时可能使用的索引一个或多个。查询涉及到的字段上若存在索引则该索引将被列出。注意是可能实际查询时不一定会用到。
2.7 key
查询时实际使用的索引没有使用索引则为NULL。查询时若使用了覆盖索引则该索引只出现在key字段中。
2.8 key_len
表示索引中使用的字节数可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下长度越短越好。
key_len显示的值是索引字段可能的最大长度并非实际使用长度即key_len是根据表定义计算得到不是通过表内检索。
key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长说明索引使用的越充分。
注意key_len只计算where条件中用到的索引长度而排序和分组即便是用到了索引也不会计算到key_len中。
2.9 ref
显示索引的哪一列被使用了常见的取值有const funcnull字段名。
当使用常量等值查询显示const当关联查询时会显示相应关联表的关联字段如果查询条件使用了表达式、函数或者条件列发生内部隐式转换可能显示为func其他情况null
2.10 rows
rows 列表示 MySQL 认为它执行查询时可能需要读取的行数一般情况下这个值越小越好
2.11 filtered
filtered 是一个百分比的值表示符合条件的记录数的百分比。简单点说这个字段表示存储引擎返回的数据在经过过滤后剩下满足条件的记录数量的比例。
在MySQL.5.7版本以前想要显示filtered需要使用explain extended命令。MySQL.5.7后默认explain直接显示partitions和filtered的信息。
2.12 Extra
Extra是EXPLAIN输出中另外一个很重要的列该列显示MySQL在查询过程中的一些详细信息MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。
类型说明Using filesortMySQL有两种方式可以生成有序的结果通过排序操作或者使用索引当Extra中出现了Using filesort 说明MySQL使用了后者但注意虽然叫filesort但并不是说明就是用了文件来进行排序只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快所以一般这时也要考虑优化查询了。使用文件完成排序操作这是可能是ordery bygroup by语句的结果这可能是一个CPU密集型的过程可以通过选择合适的索引来改进性能用索引来为查询结果排序。Using temporary用临时表保存中间结果常用于GROUP BY 和 ORDER BY操作中一般看到它说明查询需要优化了就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。Not existsMYSQL优化了LEFT JOIN一旦它找到了匹配LEFT JOIN标准的行 就不再搜索了。Using index说明查询是覆盖了索引的不需要读取数据文件从索引树索引文件中即可获得信息。如果同时出现using where表明索引被用来执行索引键值的查找没有using where表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的但无需再回表查询记录。Using index condition这是MySQL 5.6出来的新特性叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的但是现在可以了这样减少了不必要的IO操作但是只能用在二级索引上。Using where使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。Using join buffer使用了连接缓存Block Nested Loop连接算法是块嵌套循环连接;Batched Key Access连接算法是批量索引连接impossible wherewhere子句的值总是false不能用来获取任何元组select tables optimized away在没有GROUP BY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作不必等到执行阶段再进行计算查询执行计划生成的阶段即完成优化。distinct优化distinct操作在找到第一匹配的元组后即停止找同样值的动作
