mysql之explain(性能分析)

目录

1.说明

2.使用方式

3.字段解释

(1)id

(2)select_type

(3)table

(4)type

(5)possible_keys

(6)keys

(7)ken_len

(8)ref

​编辑

(9)rows

(10)extra

4.示例

---

1.说明

使用EXPLAIN关键字可以模拟优化器执行SOL查询语句从而知道MySOL是如何处理你的SOL语句的。分析你的查询语句或是表结构的性能瓶颈

2.使用方式

explain 查询语句

通过执行结果可以查看如下信息

表的读取顺序

数据读取操作的操作类型哪些

索引可以使用哪些

索引被实际使用

表之间的引用

每张表有多少行被优化器查询

3.字段解释

(1)id

select查询的序列号,包含一组数字表示查询中执行select子句或操作表的顺序。

有如下三种情况

id相同执行顺序由上至下 

id不同如果是子查询id的序号会递增id值越大优先级越高越先被执行 

id相同不同同时存在id如果相同可以认为是一组从上往下顺序执行;在所有组中id值越大优先级越高越先执行。衍生 = DERIVED。

要使用小表驱动大表。

(2)select_type

查询的类型主要是用于区别普通查询、联合查询、子查询等的复杂查询。

主要有SIMPLEPRIMARYSUBQUERYDERIUEDUNIONUNION RESULT。

1.SIMPLE 简单的 select 查询,查询中不包含子查询或者UNION

2. PRIMARY 查询中若包含任何复杂的子部分最外层查询则被标记为PRIMARY

3.SUBQUERY 在SELECT或WHERE列表中包含了子查询,子查询则被标记为SUBQUERY

4.DERIVED 在FROM列表中包含的子查询,MySQL会递归执行这些子查询把结果放在临时表里,用临时表在进行查询操作则被标记为derived。

5.UNION 若第二个SELECT出现在UNION之后则被标记为UNION若UNION包含在FROM子句的子查询中,外层SELECT将被标记为: DERIVED

6.UNION RESULT从UNION表获取结果的SELECT。

(3)table

显示这一行数据是关于哪张表的

(4)type

访问类型代表查询使用的是何种类型性能从好到差依次是

 system>const>eq_ref>ref>range>index>ALL

最全的是(有些不长出现)

system > const> eq_ref> ref> fulltext > ref or null> index merge > unigue subquery > index subquery > range > index >all

只要要求达到range级别能达到ref级别最好。        

system表只有一行记录(等于系统表)这是const类型的特列平时不会出现这个也可以忽略不计。

const表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据所以很快如将主键置于where列表中MySQL就能将该查询转换为一个常量。

 eq_ref唯一性索引扫描对于每个索引键表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

ref非唯一性索引扫描返回匹配某个单独值的所有行.本质上也是一种索引访问它返回所有匹配某个单独值的行然而它可能会找到多个符合条件的行所以他应该属于查找和扫描的混合体

range只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询
这种范围扫描索引扫描比全表扫描要好因为它只需要开始于索引的某一点而结束语另一点不用扫描全部索引。

indexFull Index Scanindex与ALL区别为index类型只遍历索引树。这通常比ALL快因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表但index是从索引中读取的而all是从硬盘中读的)

(5)possible_keys

显示可能应用在这张表中的索引一个或多个。

查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询实际使用。

(6)keys

实际使用的索引。如果为NULL则没有使用索引。

查询中若使用了覆盖索引则该索引仅出现在key列表中。

覆盖索引指的是select后面查询出来的字段和索引中的字段一一对应。如下

(7)ken_len

表示索引中使用的字节数可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下长度越短越好。

key_len显示的值为索引字段的最大可能长度并非实际使用长度即key_en是根据表定义计算而得不是通过表内检索出的。

(8)ref

显示索引的哪一列被使用了如果可能的话是一个常数。哪些列或常量被用于查找索引列上的值。

(9)rows

根据表统计信息及索引选用情况大致估算出找到所需的记录所需要读取的行数。越小越好。

(10)extra

Using filesort:说明mysql会对数据使用一个外部的索引排序而不是按照表内的索引顺序进行读写MySQL中无法利用索引完成的排序操作称为"文件排序”。

上述条件用到了col1排序中用到了col3索引中的col2没有使用导致出现文件索引。出现这种情况必须优化。

如果在排序中添加了col2结果如下

 using temporary使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。必须进行优化。

可以优化为如下

using index表示相应的select操作中使用了覆盖索引(Covering Index)避免访问了表的数据行效率不错!如果同时出现usingwhere表明索引被用来执行索引键值的查找;如果没有同时出现using where表明索引用来读取数据而非执行查找动作。

using where表明使用了where过滤。

using join buffer使用了连接缓存。

impossible wherewhere子句的值总是false不能用来获取任何元组。

select tables optimized away在没有GROUPBY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作不必等到执行阶段再进行计算查询执行计划生成的阶段即完成优化。

distinct优化distinct操作在找到第一匹配的元组后即停止找同样值的动作。

4.示例