mysql执行计划EXPLAIN

适用场景

适用于 select、update、insert、replace、delete语句，在需要分析的语句前加EXPLAIN，即可。  

执行计划分析可以得到的信息

    1、SQL如何使用索引
    2、关联查询的执行顺序
    3、查询扫描的数据行数

执行计划输出内容分析

    1、ID
       ID列中的数据为一组数字，表示执行select语句的顺序
       ID值相同时，执行顺序由上至下
       ID值越大优先级越高，越先被执行
    2、SELECT_TYPE
       SIMPLE
         不包含子查询或是UNION操作的查询
       PRIMARY
         查询中如果包含任何子查询，那么最外层的查询则被标记为PRIMARY
       SUBQUERY
         SELECT 列表中的子查询
       DEPENDENT SUBQUERY
         被别的查询所依赖的子查询
       UNION
         union操作的第二个或是之后的查询的值为union
       DEPENDENT UNION
         当union作为子查询时，第二或者是第二个后的查询的值
       UNION RESULT
         union产生的结果集
       DERIVED
         出现在from子句中的子查询
    3、TABLE
      输出数据行所在的表的名称或别名
      <unionM,N>
         由ID为M,N查询union产生的结果集
      <derivedN>/<subqueryN>
         由ID为N的查询产生的结果
    4、PARTITIONS
      对于分区表，显示查询的分区ID
      对于非分区表，显示为NULL
    5、TYPE（类型性能是依次降低的）
      system，这是const连接类型的一个特例，当查询的表只有一行时使用。
      const，表中有且只有一个匹配的行时使用，如对主键或是唯一索引的查询，这是效率最高的联接方式。
      eq_ref，唯一索引或者是主键索引查找，对于每个索引键，表中只有一条记录与之匹配
      ref，非唯一索引查找，返回匹配某个单独值的所有行。
      ref_or_null，类似于ref类型的查询，但是附加了对NULL值列的查询。
      index_merge，该联接类型表示使用了索引合并优化方法。
      range，索引范围扫描，常见于between、>、<、这样的查询条件。
      index，full index scan 全索引扫描，同ALL的区别是，遍历的是索引树。
      all，full table scan 全表扫描，这是效率最差的联接方式。
    6、EXTRA
      distinct，优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作
      not exists，使用not exists来优化查询
      using filesort，使用额外操作进行排序，通常会出现在order by 或者 group by查询中。
      using index，使用了覆盖索引进行查询。
      using temporary，需要使用临时表来处理查询，常见于排序，子查询，分组查询
      using where，需要在mysql服务器层使用where条件来过滤数据。
      select tables optimized away，直接通过索引来获得数据，不用访问表。
    7、POSSIBLE_KEYS
      指出mysql能使用那些索引来优化查询，查询列所涉及到的列上的索引都会被列出，但不一定会被使用
    8、KEYS
      查询优化器优化查询实际所使用的索引，如果查询过程中没有用到索引，则会显示为NULL，此处的值也可能不出现在POSSIBLE_KEYS，则说明，使用了覆盖索引。
    9、KEY_LEN
      表示索引字段的最大可能长度。
      此值的长度有字段定义计算而来，并非数据的实际长度。
    10、REF
      表示那些列或常量被用于查找索引列上的值
    11、ROWS
      表示mysql通过索引统计的信息，估算出的所需读取的行数。是一个不十分准确的值。
    12、FILTERED
      表示反悔结果的行数占需读取行数的百分比，越大越好，也并不十分准确。

执行计划的限制

    无法展示存储过程、触发器、UDF对查询的影响
    无法使用EXPLAIN对存储过程进行分析
    早起版本的mysql只支持对select语句进行分析

优化评论分页查询

1、现有以下sql语句
    select customer_id,title,content from `product_comment` where audit_status=1 and product_id=123456 limit 0,5;
2、执行EXPLAIN结果
    id：1
    select_type：SIMPLE
    table：product_comment
    partitions：NULL
    type：ALL
    possible_keys：NULL
    key：NULL
    key_len：NULL
    ref：NULL
    rows：1233
    filtered：1.00
    extra：using where
3、结果分析
    使用where子句过滤，但是没有可能用到的索引和用到的索引。
4、针对优化
    从where 子句中看出，想要用到索引，应该在 audit_status字段或者 product_id字段加上索引，前边谈到索引选择应该尽量选择唯一性高的字段，以下为索引唯一性计算SQL
      select count(distinct audit_status)/count(*) as audit_rate,count(distinct product_id)/count(*) as product_rate from product_comment;
      计算结果可能为小数，则越接近1的小数说明字段的唯一性越高，也应该先选择，并放到联合索引的左侧
5、分析后的处理
    创建联合索引
      create index idx_productID_auditStatus on product_comment(product_id,audit_status)
6、进一步优化
    通过改写sql