explain工具详解

explain工具详解

explain是MySQL提供的一个工具;可以使用该工具判断索引的使用情况，从而可以根据结果对sql进行优化
explain主要包括id,select_type,table,type,possible_key,key,key_len,ref,rows,extra等信息

例如：EXPLAIN SELECT * FROM peoson WHERE id =1;

1.id

select 的序列号，有几个select就有几个id，id越大执行的优先级越高；id相同从上往下一次执行

2.select_type

• 意义：表示语句是简单查询还是复杂查询
• 对应数值
  • simple:简单查询，不包含子查询和union
  • primary: 复杂查询中最外层的查询
  • subquery: 子查询,包含在select后面的子查询,不包括from后面的子查询
  • derived: form后面的子查询，结果回放到一个衍生表内
  • union: union链接中的第二个以及随后的select

table

表示这一步所对应的数据库表的名称或者简称

type

• null:mysql在优化阶段分析语句，不需要访问表或者索引，例如获取索引列的最大值，最小值
• system:使用唯一索引等值查询，同时数据表中只有一条数据

  注意测试时数据库存储引擎需要设置为myisam

• const:使用唯一索引进行等值查询，表中最多有一行数据匹配
• eq_ref:进行连表查询时，关联字段是唯一索引，表中只有一条数据匹配
• ref:使用普通索引或者唯一索引的部分前缀索引查询，可以匹配到多条数据
• range:使用一个索引检索给定范围的数据，通常使用< ,>,in,between时
• index:扫描全索引表即可获取所需结果，索引表为普通索引，不需要回表查询
• all:全表扫描，无法命中索引

possible_key

表示查询可能会用到的索引

key

表示查询时真正用到的索引

key_len

• 定义：表示使用索引占用的字节数，例如组合索引只使用到前面几个字段，可以通过这个字段判断用到了那些字段
• key_len计算规则
  • 字符串：varchar(n)中n代表的是字符数，一个数字或者一个字母占用一个字节，utf8编码格式下一个汉字占用3个字节，所以字符串占用字节可以用3n+2表示，2个字节用于存储字符串长度
  • 数值类型
    • tinyint:1字节
    • smallint:2字节
    • int:4字节
    • bigint:8字节
  • 时间类型
    • date:3字节
    • timestamp:4字节
    • datetime:8字节
  • 字段如果允许为null，需要一个字节记录是否为null

ref

表示key列对应索引中，查询所用到的列名称，或者常量const

rows

预测要扫描的行数

extra

• using index:使用覆盖索引，不需要回表查询
• using where:使用where查询，并且查询条件未被索引覆盖
• using index condition:表示查询条件中虽然出现了索引但是有部分索引无法使用（例如联合索引只有第一个索引命中）,回根据能用的索引查询一遍，然后在匹配无法使用的索引
• using temporary:查询时需要创建一个临时表
• using filesort:使用外部排序而不是索引排序，一般是需要优化的
• select tables optimezed away:使用某一个聚合函数（max，min）来访问存在索引的某个字段