31.Explain(执行计划)浅谈

root@192.168.11.8 19:44:  [liulin]> explain select * from t;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   11 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

这里就说几个比较重要的字段：

　　1.id : select 识别符号，也是select 查询序列号

• • • 当id相同时，对应的sql语句是从上到下的顺序执行，且认为id相同的视为一组
    • 如果是子查询，id序列号会自动增加，id值越大sql语句的优先级就越高，就会越先被执行。

　　2.select_type : select 的类型，官网上共显示了11中类型

• • • simple：简单的select类型(sql语句中没有使用union或子查询)
    • primary : 最外层的select 查询，查询中若包含复杂的子查询，那么最外层的子查询就是primary类型
    • union   :  union的第二个或后面的select 语句
    • DEPENDENT UNION　：union的第二个或后面的select语句，取决于外面的查询。
    • union result : union结果，union中第二个select和后面的所有select
    • subquery :子查询中的第一个select，结果不依赖与外部查询
    • dependent subquery: 子查询中的第一个select ,结果依赖于外部查询
    • deriver : 派生表

　　3.table ：表示查询的是那张表

　　4.partitions : 是否是分区表(自己的猜测)

　　5.type  ： 表示连接表的形式，通常也指mysql找到所需要行的方式

　　　　　　常用的类型有：All-->index-->range -->index_subquery-->unique_subquery--> index_merge-->ref_or_null-->fulltext-->ref-->eq_ref       -->const-->system-->null (从左到右，性能从差到好)

• • • All　:  “全表扫描”的方式查找所需要的行，如果第一张表的查询类型(EXPLAIN的输出结果)为const，其性能可能不算太坏，而第一张表的查询类型为其它结果时，其性能通常会非常差；

    • index : 同全表扫描(ALL)，只不过是按照索引的次序进行的；其优点是避免了排序，但是要承担按索引次序读取整个表的开销，这意味着若是按随机次序访问行，代价将非常大；
    • range : 带有范围限制的索引扫描，而非全索引扫描，它开始于索引里的某一点，返回匹配那个值的范围的行；相应地，其key字段(EXPLAIN的输出结果)中会输出所用到的索引，key_len字段中会包含用到的索引的最长部分的长度；range通常用于将索引与常量进行=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或IN()类的比较操作中
    • index_subquery : 类似于unique_subquery，但子查询中键值不惟一；
    • unique_subquery: 用于IN比较操作符中的子查询中进行的“键值惟一”的访问类型场景中，如 value IN (SELECT primary_key FROM single_table WHERE some_expr)；
    • index_merge: 使用“索引合并优化”的记录访问类型，相应地，其key字段(EXPLAIN的输出结果)中会出现用到的多个索引，key_len字段中会出现被使用索引的最长长度列表；将多个“范围扫描(range scan)”获取到的行进行合并成一个结果集的操作即索引合并(index merge)。
    • ref_or_null：类似于ref，但可以额外搜索NULL值；
    • fulltext：用于FULLTEXT索引中用纯文本匹配的方法来检索记录。
    • ref ： 查询时的索引类型不是PRIMARY KEY或UNIQUE NOT NULL导致匹配到的行可能不惟一，或者仅能用到索引的左前缀而非全部时的访问类型；ref可被用于基于索引的字段进行=或<=>操作；
    • eq_ref ：类似于const，表中至多有一个匹配的行，但比较的数值不是某常量，而是来自于其它表；ed_ref出现在PRIMARY KEY或UNIQUE NOT NULL类型的索引完全用于联结操作中进行等值(=)比较时；这是除了system和const之外最好的访问类型；
    • const ：表中至多有一个匹配的行，该行仅在查询开始时读取一次，因此，该行此字段中的值可以被优化器看作是个常量(constant)；当基于PRIMARY KEY或UNIQUE NOT NULL字段查询，且与某常量进行等值比较时其类型就为const，其执行速度非常快；
    • system : 表中仅有一行，是const类型的一种特殊情况；
    • null : MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

　　6.possible_keys : 表示mysql按照哪些可能要走的索引去找到所需要的记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

　　如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询。

　　7.key : 表示查询时实际用到的索引，必然包含在possible_keys中

　　　　　如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE 　　　　　INDEX。

　　8.key_len ： 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

　　9.ref : 列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

　　10.rows : 估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

　　11.extra : 额外的信息(这个挺重要的)

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

    • Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by

    • Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

    • Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

    • Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。

    • Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

    • No tables used：Query语句中使用from dual 或不含任何from子句