MySQL EXPLAIN

--执行计划

--MySQL的EXPLAIN命令用于SQL语句的查询执行计划(QEP),来自于优化器
--例
mysql> EXPLAIN select d.id as id
    ->   from t_ncl_insurance_prd_detail d
    ->   LEFT JOIN t_ncl_area_prd p
    ->     ON d.id = p.insurance_prd_detail_id
    ->   LEFT JOIN (select pid, COUNT(id) as num from t_ncl_click GROUP BY pid) as click
    ->     on click.pid = d.id
    ->  where p.area_no = '8621'
    ->    and (d.isselected != 1 or d.isselected is NULL)
    ->    and d.isenable = '1'
    ->    AND d.branchtype like '%1%'
    ->    AND d.type_label = '1'
    ->  ORDER BY click.num DESC;
+----+-------------+-------------+------------+------+--------------------+--------------------+---------+---------------+--------+----------+----------------------------------------------+
| id | select_type | table       | partitions | type | possible_keys      | key                | key_len | ref           | rows   | filtered | Extra                                        |
+----+-------------+-------------+------------+------+--------------------+--------------------+---------+---------------+--------+----------+----------------------------------------------+
|  1 | PRIMARY     | d           | NULL       | ALL  | PRIMARY            | NULL               | NULL    | NULL          |     20 |     5.00 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | p           | NULL       | ref  | FK_area_prd_detail | FK_area_prd_detail | 5       | xhweshop.d.id |    113 |    10.00 | Using where                                  |
|  1 | PRIMARY     | <derived2>  | NULL       | ref  | <auto_key0>        | <auto_key0>        | 5       | xhweshop.d.id |   3298 |   100.00 | NULL                                         |
|  2 | DERIVED     | t_ncl_click | NULL       | ALL  | NULL               | NULL               | NULL    | NULL          | 329873 |   100.00 | Using temporary; Using filesort              |
+----+-------------+-------------+------------+------+--------------------+--------------------+---------+---------------+--------+----------+----------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

--字段说明：
id
--是一位数字，表示执行SELECT语句的顺序。
--id值相同执行顺序从上到下。
--id值不同时id值大的先执行。

select_type
--查询类型，包括：
SIMPLE                 --简单查询（不使用UNION或子查询）
PRIMARY                --最外层的SELECT语句
UNION                  --在UNION结构中的第二个及以上的SELECT语句
DEPENDENT UNION        --在UNION结构中的第二个及以上的SELECT语句，依赖外层查询
UNION RESULT           --UNION的结果
SUBQUERY               --子查询中的第一个SELECT语句
DEPENDENT SUBQUERY     --子查询中的第一个SELECT语句，依赖于外层查询
DERIVED                --子查询中FROM后面的语句
MATERIALIZED           --物化视图子查询
UNCACHEABLE SUBQUERY   --查询结果没有被缓存且需要重新外层查询计算每行数据的子查询
UNCACHEABLE UNION      --UNION 结构中第二个及之后的SELECT语句且没有生成查询缓存

table
--表名,显示这一行的数据是关于哪张表的
--输出数据行所在的表的名称

PARTITIONS
--对于分区表，显示查询的分区ID，对于非分区表，显示为NULL

type
--从最好到最差的连接类型为const、eq_reg、ref、range、index、all
--表连接的类型，包括：
system           --表中只有一行数据，这是cost连接类型的一种特殊情况
const            --表中只有一行匹配记录，且在查询中被最先读取
eq_ref           --和之前的表作nested loop连接时，每次两个表中连接字段相比，都有一行匹配的记录。当索引中的所有部分被用于连接且索引是主键索引或UNIQUE非空索引时，会使用这种类型。
ref              --每次和之前的表做连接时，读取所有符合条件的索引值。如果连接使用索引的最左边前缀字段，或者索引不是主键或UNIQUE索引，会用到这种连接方式，也就是说如果连接不能基于每个符合连接条件的索引值选择出单独的一行，则会使用这种连接方式。
fulltext         --使用FULLTEXT索引来建立连接
ref_or_null      --连接类型类似ref，除此之外，MySQL会额外扫描出包含NULL值的行。这种连接方式通常用于有子查询的情形下。
index_merge      --使用索引合并的连接方式。在这种情况下，key字段会包含使用的索引，key_len包含使用索引的最长索引部分。
unique_subquery  --这种连接方式在某种情况下会代替eq_ref，如value IN (SELECT primary_key FROM single_table WHERE some_expr)，这种方式使用索引查询功能代替子查询，以获得更好的执行效率。
index_subquery   --这种连接方式类似unique_subquery。它会代替IN子查询，但是它适用于非unique索引的子查询，如value IN (SELECT key_column FROM single_table WHERE some_expr)
range            --使用索引扫描出指定范围的行。key字段指示使用的索引。key_len指示索引的最大长度。ref字段会显示NULL；这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
index            --这种索引连接类型和ALL相同，除了索引树被扫描到。这会出现在两种情况下：一、如果该索引是一个覆盖索引查询,且只扫描出索引树。在这种情况下，Extra字段会显示Using index。二、通过索引顺序来执行全表扫描。这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
ALL              --和之前表做连接时，每次两表关联时都做全表扫描。这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

possible_keys
--可供选择的索引,显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key
--查询优化器优化查询实际所使用的索引，如果没有可用的索引，则显示为NULL，如查询使用了覆盖索引，则该索引仅出现在Key列中
--实际选择的索引,如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len
--表示索引字段的最大可能长度，KEY_LEN的长度由字段定义计算而来，并非数据的实际长度
--选择的索引长度,在不损失精确性的情况下，长度越短越好

ref
--表示哪些列或常量被用于查找索引列上的值
--显示和索引相比较的字段或常量，如果这个字段的值是func，这个值会用在函数的结果中.显示索引的哪一列被使用了，如果可能的话，是一个常数

rows
--估计的表的行数

Filtered
--表示返回结果的行数占需读取行数的百分比，Filter列的值越大越好

Extra
--额外信息,坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢
--extra列返回的描述的意义
Distinct                                      --一旦mysql找到了与行相联合匹配的行，就不再搜索了。
Not exists                                    --mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
Range checked for each Record（index map:#）  --没有找到理想的索引，因此对从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。
Using filesort                                --看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
Using index                                   --列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。
Using temporary                               --看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。
Where used                                    --使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题

-- 显示sql运行时间
-- select  profiling | ON/OFF
show variables like "%pro%";
-- 设置开启方法
set profiling = 1;
-- 关闭参数：
set profiling=0;

-- 查询时间
show profiles;
show profile FOR QUERY 16;