mysql中的explain

Mysql相关知识

explain中的字段意义

• id

  • id 相同的情况下，执行顺序由上而下
  • 如果是子查询，id 越大，越先执行
  • 小表驱动大表

• select_type

  • simple 简单的查询不包含子查询或者union
  • primary 主查询，最后加载
  • subquery 子查询
  • derived 衍生的临时表
  • union
    • 若第二个select出现在union之后，则标记为union
    • 若union包含在from子句的子查询中，则外层select标记为derived
  • union result 从union表获取结果的select
    • 两个union合并的select

• table 哪张表

• type

  • 从最好到最差依次
    • system>const>eq_ref>ref>range>index>all
    • system 只有一条记录的单表，系统表
    • const 相当于primary key 或 unique 等于一个数值 where id =1
    • eq_ref 唯一索引 t1.id=t2.id
    • ref 普通索引，非唯一值
    • range 只检索给定范围的行，使用一个索引来选择行
    • index 遍历所有索引
    • all 全表扫描，需要优化

• possible_keys，key

  • 可能用到的索引和实际用到的索引
  • 覆盖索引不会显示在possible_keys上，只会显示在key中

• key_len

  • 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
  • 同样的查询结果，长度越少越好

• ref

  • 显示索引的哪一列被使用,如果可能的话，是一个常量，哪些列或常量被用于查找索引列上的值
  • const 常量
  • test1.t1.id

• rows

  • 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
  • 理论上影响了多少行

• extra

  • 不适合在其他列中显示但是十分重要的额外信息
  • using filesort
    • mysql 无法利用索引完成的排序，文件排序
  • using temporary
    • 对查询结果，使用了临时表 order by ,group by
    • 最好使用覆盖索引，不要产生临时表
  • using index
    • 表示相应的select 操作中使用了覆盖索引
    • 同时出现using where ，表明索引被用来执行索引键值的查找
    • 没出现using where，表明索引用来读取数据而非执行查找动作
  • using where
    • 使用了 where
  • using join buffer
    • join 多，而使用了缓冲区
  • impossible where
    • where子句的值是false

索引失效的情况

• 全值匹配我最爱
• 违背最左前缀原则
• 跳过索引中的列
• 某列属于联合索引，对该列进行范围查询，值使用部分索引，该列右边的列无法使用索引
• !=,is null,is not null
• like以通配符开头
  • 解决%like% 索引时不能失效的方法
  • 使用覆盖索引可以解决
• 用or连接条件
• 不在索引列上做任何操作（计算，函数，自动or手动类型转换，会导致索引失效而转向全表扫描）
• 尽量使用覆盖索引
• 字符串不加单引号索引失效

sql语句优化步骤

• 慢查询的开启并捕获
• explain + 慢sql分析
• show profile查询sql在mysql服务器里面的执行细节和生命周期情况
• sql数据库服务器的参数调优