mysql中explain关键字段解释以及索引优化

    explain模拟优化器执行sql语句,显示了mysql如何使用索引来处理select语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。

通过Explain,我们可以分析出以下结果:

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

   使用方法:在select语句前加上explain就可以了,例如:

explain select * from user u left join company c on c.id=u.company_id;

一、explain关键字解释

expain出来的信息有11列,分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、filtered、Extra。

        

解释如下:

1、id

    MySQL QueryOptimizer 选定的执行计划中查询的序列号,表示查询中执行select 子句或操作表的顺序。id 值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。

2、select_type

    (1) SIMPLE

        简单的 select 查询(不使用 union 及子查询)。

    (2) PRIMARY

        最外层的 select 查询。

        如果两表存在则查询,则外层的表操作为PRIMARY,内层(子查询)的操作为SUBQUERY。

        如果两表做union,则外层的表操作为PRIMARY,内层(union后面)的操作为union。

       

    (3)SUBQUERY/ DEPENDENT SUBQUERY

       SUBQUERY:子查询中首个SELECT(如果有多个子查询存在),不依赖于外层的表。除from子句中包含的子查询外,其他地方出现的子查询都可能是subquery。

       DEPENDENT SUBQUERY:子查询中首个select(如果有多个子查询存在) ,但依赖于外层的表。

      

    (4)UNION/ DEPENDENT UNION

      UNION:union操作中,处于内层(第二个或随后的) select 查询,不依赖于外部查询的结果集。

      DEPENDENT UNION:union操作中,处于内层的select 查询,但内层的SELECT语句与外层的SELECT语句有依赖关系。

      UNION RESULT:union操作的结果,id值通常为NULL  。

     

   (5)UNCACHEABLE SUBQUERY/UNCACHEABLE UNION

     MATERIALIZED:被物化的子查询

     UNCACHEABLE SUBQUERY:对于外层的主表,子查询不可被物化,每次都需要计算(耗时操作)。

     UNCACHEABLE UNION:union操作中,内层的不可被物化的子查询。

   (6)DERIVED

     被驱动的SELECT子查询,用于 from 子句里有子查询的情况。 MySQL 会递归执行这些子查询, 把结果放在临时表里(from字句中出现的子查询,也叫做派生表,其他数据库中可能叫做内联视图或嵌套select)。

     

3、table 

     输出行所引用的表。显示的查询表名,如果查询使用了别名,那么这里显示的是别名,如果不涉及对数据表的操作,那么这显示为null,如果显示为尖括号括起来的<derived N>就表示这个是临时表,后边的N就是执行计划中的id,表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>,与<derived N>类似,也是一个临时表,表示这个结果来自于union查询的id为M,N的结果集。

 4、type

     从优到差的顺序如下:(红色标识的是常见的级别。)除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一个索引。

System-->const-->eq_ref-->ref--> fulltext-->ref_or_null-->index_merge-->unique_subquery-->index_subquery-->range-->index-->all.

各自的含义如下:

(1)system

        表仅有一行(=系统表)。这是 const 连接类型的一个特例。表中只有一行数据或者是空表,且只能用于myisam和memory表。如果是Innodb引擎表,type列在这个情况通常都是all或者index。

(2)const

       const 用于用常数值比较 PRIMARY KEY 时。使用唯一索引或者主键,返回记录一定是1行记录的等值where条件时,通常type是const。其他数据库也叫做唯一索引扫描。当查询的表仅有一行时,使用 System。

 

    (3)eq_ref

      出现在要连接这个表的查询计划中,从前面的表中,对每一个记录的联合都从表中读取一个记录,驱动表只返回一行数据,且这行数据是第二个表的主键或者唯一索引,且必须为not null,它在查询使用了索引为主键或唯一键的全部时使用。唯一索引和主键是多列时,只有所有的列都用作比较时才会出现eq_ref。(4)ref

     不像eq_ref那样要求连接顺序,也没有主键和唯一索引的要求,只要使用相等条件检索时就可能出现,常见与辅助索引的等值查找。或者多列主键、唯一索引中,使用第一个列之外的列作为等值查找也会出现,总之,返回数据不唯一的等值查找就可能出现.

        

(5)fulltext

        全文索引检索,要注意,全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价,优先选择使用全文索引。

(6)ref_or_null

        如同 ref, 但是 MySQL 必须在初次查找的结果里找出 null 条目,然后进行二次查找。与ref方法相比,只是增加了null值的比较,实际用的不多。

(7)index_merge

       说明索引合并优化被使用了。表示查询使用了两个以上的索引,最后取交集或者并集,常见and ,or的条件使用了不同的索引,官方排序这个在ref_or_null之后,但是实际上由于要读取多个索引,性能可能大部分时间都不如range。

(8)unique_subquery

       用于where中的in形式子查询,子查询返回不重复值唯一值。在某些 IN 查询中使用此种类型,而不是常规的 ref:valueIN (SELECT primary_key FROM single_table WHERE some_expr)。

(9)index_subquery

       用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询去重。在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与unique_subquery 类似,但是查询的是非唯一性索引。

(10)range

      索引范围扫描,常见于使用>,<,is null,between ,in ,like等运算符的查询中。只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range。

        

(11)index

      索引全表扫描,把索引从头到尾扫一遍,常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。全表扫描,只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。

(12)all

全表扫描数据文件,然后在server层进行过滤返回符合要求的记录。最坏的情况,从头到尾全表扫描。

5、possible keys

     指出能在该表中使用哪些索引有助于查询,查询可能使用到的索引都会在这里列出来。如果为空,说明没有可用的索引。

 6、key

     实际从 possible_key 选择使用的索引,如果为 NULL,则没有使用索引。select_type为index_merge时,这里可能出现两个以上的索引,其他的select_type这里只会出现一个。很少的情况下,MYSQL 会选择优化不足的索引。这种情况下,可以在 SELECT语句中使用 USE INDEX (indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制 MYSQL 忽略索引。

7、key_len

     用于处理查询的索引长度,在不损失精确性的情况 下,长度越短越好。如果是单列索引,那就整个索引长度算进去,如果是多列索引,那么查询不一定都能使用到所有的列,具体使用到了多少个列的索引,这里就会计算进去,没有使用到的列,这里不会计算进去。留意下这个列的值,算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意,mysql的ICP特性使用到的索引不会计入其中。另外,key_len只计算where条件用到的索引长度,而排序和分组就算用到了索引,也不会计算到key_len中。

 8、ref

     显示索引的哪一列被使用了。如果是使用的常数等值查询,这里会显示const,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func。

9、rows

       MySQL估算要查找到结果集需要扫描读取的数据行数

10、extra

       这个列可以显示的信息非常多,有几十种,常用的有下面几种。其中,如果出现Using filesort、Using temporary 两项意味着不能使用索引,效率会受到重大影响。应尽可能对此进行优化。

常见的有以下几种内容:

(1)using filesort:排序时无法使用到索引时,就会出现这个。常见于order by和group by语句中。没有办法利用现有索引进行排序,需要额外排序,建议:根据排序需要,创建相应合适的索引。
(2)Using temporary:查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

(3)using index:表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错;

(4)Using where:不用读取表中所有信息,仅通过索引就可以获取所需数据,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤

(5)Using join buffer:表明使用了连接缓存,比如说在查询的时候,多表join的次数非常多,那么将配置文件中的缓冲区的join buffer调大一些

(6)impossible where:where子句的值总是false,不能用来获取任何元组

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

(8)distinct:优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作

除了这些之外,还有很多查询数据字典库,执行计划过程中就发现不可能存在结果的一些提示信息。

 

总结:其中重要的几个就是 key、type 、rows、extra,其中key为null时,说明没有使用到索引,需要调整索引。type为all的地方,需要进行优化.一般需要达到 ref、eq_ref 级别,范围查找需要达到 range。extra有Using filesort、Using temporary 的一定需要优化,根据rows可以直观看出优化结果。

 

sql优化最低标准 :

1、不超过6层嵌套

2、每个嵌套内不超过3个join 

3、最大检索行数不超过10亿行

 

  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值