explain执行计划

可以使用的语句：SELECT， DELETE， INSERT， REPLACE，和 UPDATE语句

可选选项FORMAT表示输出格式：默认为TABLE，可选JSON

对于SELECT语句， EXPLAIN生成可使用SHOW WARNINGS来显示的其他执行计划信息 

EXPLAIN为SELECT语句中使用的每个表返回一行信息 。它按照MySQL在处理语句时读取它们的顺序列出了输出中的表。MySQL使用嵌套循环连接方法解析所有连接。这意味着MySQL从第一个表中读取一行，然后在第二个表，第三个表中找到匹配的行，依此类推。处理完所有表后，MySQL将通过表列表输出选定的列和回溯，直到找到一个表，其中存在更多匹配的行。从该表中读取下一行，然后继续下一个表。

通过获取EXPLAIN输出的rows列中的值的乘积，将告诉您MySQL必须检查多少行才能执行查询

输出列

id	该SELECT标识符
select_type	该SELECT类型
table	输出行表
partitions	匹配的分区
type	联接类型
possible_keys	可能的索引选择
key	实际选择的索引
key_len	所选键的长度
ref	与索引比较的列
rows	估计要检查的行
filtered	按表条件过滤的行百分比
Extra	附加信息

select_type

SIMPLE	简单SELECT（不使用 UNION或子查询）
PRIMARY	最外层 SELECT
UNION	SELECT陈述中的 第二条或更高条UNION
DEPENDENT UNION	中的第二个或更高版本的SELECT语句 UNION，取决于外部查询
UNION RESULT	的结果UNION。
SUBQUERY	首先SELECT在子查询
DEPENDENT SUBQUERY	首先SELECT在子查询中，取决于外部查询
DERIVED	派生表
MATERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	子查询，其结果无法缓存，必须针对外部查询的每一行重新进行评估
UNCACHEABLE UNION	UNION 属于不可缓存子查询的中的第二个或更高版本的选择（请参阅参考资料 UNCACHEABLE SUBQUERY）

type

system
该表只有一行（=系统表）。这是const联接类型的特例。

const
该表最多具有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。const表非常快，因为它们只能读取一次。
const在将PRIMARY KEY或UNIQUE INDEX的所有部分与常量值进行比较时使用

eq_ref
对于先前表中的每行组合，从此表中读取一行。除了system和const类型，这是最好的联接类型。当连接使用索引的所有部分且索引为PRIMARY KEY或 NOT NULL UNIQUE INDEX时使用。
eq_ref可以用于使用=运算符进行比较的索引列。比较值可以是常量，也可以是使用在此表之前读取的表中列的表达式

ref
对于先前表中的每个行组合，将从该表中读取具有匹配索引值的所有行。ref如果联接仅使用键的最左前缀，或者如果键不是PRIMARY KEY或UNIQUE INDEX则使用。如果使用的键仅匹配几行，则这是一种很好的联接类型。
ref可以用于使用=或<=>运算符进行比较的索引列 

fulltext
使用FULLTEXT索引执行联接。

ref_or_null
这种连接类型类似于ref，但是还会额外搜索包含NULL值的行。此联接类型优化最常用于解析子查询。

index_merge
此联接类型指示使用索引合并优化。在这种情况下，key输出行中的列包含使用的索引列表，并key_len包含使用的索引的最长键部分的列表。

unique_subquery
unique_subquery 只是一个索引查找函数，它完全替代了子查询以提高效率;这种类型替换了以下形式的子查询中的一些eq_ref: value IN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery
此连接类型类似于unique_subquery。它替代IN子查询，但适用于以下形式的子查询中的非唯一索引： value IN (SELECT key_column FROM single_table WHERE some_expr)

range
使用索引选择行，仅检索给定范围内的行。key的输出行中的列指示使用哪个索引。key_len包含所使用的最长的键部分。NULL适用于此类型,该ref列为NULL
range可用于使用任意=、<>、>、>=、<、<=、NULL、<=>、BETWEEN、LIKE或IN()操作符将键列与常量进行比较

index
该index联接类型是一样的 ALL，只是索引树被扫描。这发生两种方式

• 如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，Extra列显示为 Using index。仅索引扫描通常比索引扫描更快，因为索引的大小通常小于表数据。
• 使用对索引的读取执行全表扫描，以按索引顺序查找数据行。Extra列中不用Uses index

ALL
对前面表中的每个行组合进行全表扫描。

Extra列

Child of 'table' pushed join@1
将该表引用为 table可以下推到NDB内核的联接中的子级。启用下推联接时，仅适用于NDB群集。

const row not found
对于诸如SELECT ... FROM tbl_name之类的查询，该表为空。 

Deleting all rows
对于DELETE，某些存储引擎（如MyISAM）支持一种处理程序方法，该方法以一种简单而快速的方式删除所有表行。如果引擎使用此优化，则显示此值

Distinct
MySQL正在寻找不同的值，因此在找到第一个匹配的行后，它将停止为当前行组合搜索更多行。

FirstMatch(tbl_name)
tbl_name使用了半联接 FirstMatch联接快捷策略。

Full scan on NULL key
当优化器无法使用索引查找访问方法时，这将作为子查询优化的后备策略发生

Impossible HAVING
HAVING子句始终为false，无法选择任何行

Impossible WHERE
该WHERE子句始终为false，无法选择任何行

Impossible WHERE noticed after reading const tables
MySQL已经读取了所有const（和 system）表，并注意到该WHERE子句始终为false。

LooseScan(m..n)
使用半连接的LooseScan策略。 m和n是关键部件号。

No matching min/max row
没有行满足查询的条件

no matching row in const table
对于具有联接的查询，存在一个空表或没有满足唯一索引条件的行的表

No matching rows after partition pruning
对于DELETE或UPDATE，在分区修剪后，优化器未发现任何要删除或更新的内容

No tables used
查询没有FROM子句，或者有 FROM DUAL子句,对于INSERT或 REPLACE语句， EXPLAIN在没有任何SELECT 部分时显示此值

Not exists
MySQL能够对LEFT JOIN查询进行优化，并且在找到符合LEFT JOIN条件的一行后，不再检查该表中的更多行是否为上一行。

Plan isn't ready yet 
当优化器尚未完成为在命名连接中执行的语句创建执行计划时，就会出现此值。如果执行计划输出包括多行，则Extra取决于优化程序确定完整执行计划的进度，其中任何一行或所有行都可以具有此值。

Range checked for each record (index map: N)
MySQL找不到很好的索引来使用，但是发现一些索引可以在已知先前表中的列值之后使用。对于上表中的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来检索行。这不是很快，但是比根本没有索引的联接要快。

Scanned N databases
这表明在处理INFORMATION_SCHEMA表查询时服务器执行了多少目录扫描

Select tables optimized away
优化器决定最多应该返回一行，为了产生这一行，必须读取一组确定的行。如果在优化阶段可以读取要读取的行(例如，通过读取索引行)，那么在查询执行期间不需要读取任何表。如果要读取的行不确定，则不会出现此值
当对查询进行隐式分组(包含一个聚合函数，但不包含GROUP BY子句)时，第一个条件得到满足。当对使用的每个索引执行一行查找时，第二个条件就满足了。读取的索引数决定了要读取的行数。

Skip_open_table， Open_frm_only， Open_full_table
这些值指示适用于INFORMATION_SCHEMA表查询的文件打开优化

• Skip_open_table：不需要打开表文件。通过扫描数据库目录，该信息已在查询中可用。
• Open_frm_only：仅需要打开.frm表的文件。
• Open_full_table：未优化的信息查找。.frm，.MYD和 .MYI文件必须被打开。

Start temporary，End temporary
这表明临时表用于半联接重复淘汰策略

unique row not found
对于诸如SELECT ... FROM tbl_name的查询，没有行满足UNIQUEP INDEX或RIMARY KEY的条件

Using filesort
MySQL必须额外进行一遍，以找出如何按排序顺序检索行。通过根据联接类型遍历所有行并存储与该WHERE子句匹配的所有行的排序键和指向该行的指针来完成排序。然后对键进行排序，并按排序顺序检索行。

Using index
仅使用索引树中的信息从表中检索列信息，而不必进行其他查找以读取实际行。当查询仅使用属于单个索引的列时，可以使用此策略。
对于具有用户定义的聚集索引的InnoDB表，即使额外列中没有使用索引，也可以使用该索引。如果type是index，而key是PRIMARY，就会出现这种情况。

Using index condition
通过访问索引元组并首先对其进行测试以确定是否读取完整的表行来读取表。除非有必要，否则索引信息将用于延迟（“下推”）读取整个表行。

Using index for group-by
表示MySQL找到了一个索引，该索引可用于检索GROUP BY或DISTINCT查询的所有列，而无需对实际表进行任何额外的磁盘访问。此外，以最有效的方式使用索引，因此对于每个组，仅读取少数索引条目。类似Using index

Using join buffer (Block Nested Loop)， Using join buffer (Batched Key Access)
来自较早联接的表被部分读取到联接缓冲区中，然后从缓冲区中使用它们的行来与当前表执行联接。 (Block Nested Loop)表示使用块嵌套循环算法，(Batched Key Access)表示使用批量密钥访问算法。即缓冲输出的前一行中的表中的键 ，并从出现行所在的表中批量提取匹配的行。

Using MRR
使用多范围读取优化策略读取表

Using sort_union(...)，Using union(...)，Using intersect(...)
这些指示了特定算法，该算法显示了如何针对index_merge联接类型合并索引扫描 

Using temporary
为了解决该查询，MySQL需要创建一个临时表来保存结果。如果查询包含以不同的方式列出列的GROUP BY和 ORDER BY子句，通常会发生这种情况

Using where
WHERE子句用于限制来匹配下一个表或发送到客户端的行。除非您专门打算从表中获取或检查所有行，否则如果查询中的Extra值不是Using where且表连接类型为ALL或index，则查询中可能出错

Using where with pushed condition
这意味着NDB集群正在使用条件下推优化来提高非索引列和常量之间直接比较的效率。在这种情况下，条件被“下推”到集群的数据节点，并在所有数据节点上同时进行评估。这样就不需要通过网络发送不匹配的行，并且在可以使用条件下推但没有使用的情况下，可以将查询速度提高5到10倍。

Zero limit
该查询有一个LIMIT 0子句，不能选择任何行

扩展SELECT语句的EXPLAIN输出

SHOW WARNINGS可能包含特殊标记以提供有关查询重写或优化程序操作的信息，因此该语句不一定是有效的SQL，也不打算执行。输出中可能还包含带有Message值的行，这些值提供有关优化程序采取的操作的其他非SQL解释性说明
<auto_key>
自动生成的临时表密钥。

<cache>(expr)
表达式（例如标量子查询）执行一次，结果值保存在内存中以备后用。对于包含多个值的结果，可能会创建一个临时表，您可能会看到<temporary table>。

<exists>(query fragment)
子查询谓词被转换为EXISTS谓词，并且子查询被转换为可以与EXISTS谓词一起使用 。

<in_optimizer>(query fragment)
这是一个内部优化器对象，对用户没有任何意义。

<index_lookup>(query fragment)
使用索引查找来处理查询片段以查找合格的行

<if>(condition, expr1, expr2)
如果条件为true，则值为expr1，否则为expr2

<is_not_null_test>(expr)
验证表达式不等于NULL的测试 

<materialize>(query fragment)
使用子查询实现

`materialized-subquery`.col_name
实现了对内部临时表中col_name列的引用，以保存评估子查询的结果

<primary_index_lookup>(query fragment)
使用主键查找来处理查询片段以查找合格的行

<ref_null_helper>(expr)
这是一个内部优化器对象，对用户没有任何意义

/* select#N */ select_stmt
SELECT与非扩展解释EXPLAIN输出中id值为N的行相关联

outer_tables semi join (inner_tables)
半联接操作。 inner_tables显示未拉出的表

<temporary table>
这表示为缓存中间结果而创建的内部临时表

估计查询性能

在大多数情况下，您可以通过计算磁盘搜索次数来估计查询性能。对于小型表，通常可以在一个磁盘搜索中找到一行（因为索引可能已缓存）。对于更大的表，您可以估计，使用B树索引，您需要进行许多查找才能找到行：

log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1

示例：
索引块通常为1,024字节，数据指针通常为四个字节。对于键值长度为三个字节的500,000行表

log(500,000)/log(1024/3*2/(3+4)) + 1 = 4

该索引将需要大约500,000 * 7 * 3/2 = 5.2MB的存储空间，在内存中可能拥有很多索引，因此只需要一个或两个调用即可读取数据以查找行。
但是，对于写操作，您需要四个搜索请求来查找在何处放置新索引值，通常需要两个搜索来更新索引并写入行
公式不意味着应用程序性能会因log(N)缓慢下降。只要所有内容都由OS或MySQL服务器缓存，随着表的变大，事情只会稍微变慢。在数据变得太大而无法缓存之后，事情开始变得缓慢得多，直到您的应用程序仅受磁盘搜索约束（增加了log(N)）。为避免这种情况，请随着数据的增长而增加密钥缓存的大小

explain：执行计划能告诉我们什么
1、sql如何使用索引
2、关联查询时：可以表示联接查询的查询顺序：
3、查询扫描的数据行数
explain的多个列表示：
ID列中的数据为一组数字，表示执行select语句的顺序，
    1、ID值相同时，执行顺序由上至下，
    2、ID值不同时，越大优先级越高，越先被执行
select_type列：
    1、simple 不包含子查询或是union操作的查询
    2、primary 查询中如果包含任何子查询，那么最外层的查询则被标记为primary
    3、subquery select列表中的子查询
    4、dependent subquery 依赖外部结果的子查询
    5、union union操作的第二个或是之后的查询的值为union
    6、dependent union 当union做为子查询时，第二个或是第二个后的查询的select_type值
    7、union result union产生的结果集
    8、derived 当出现from子句中的子查询

table列
    输出数据行所在的表的名称
    unionM,N 由id为M,N查询union产生的结果集
    derivedN/subqueryN 由id为N的查询产生的结果

partitions列：
    对于分区表，显示查询的分区id
    对于非分区表，显示为NULL

type列：
    1、system 这时const联接类型的一个特例，当查询的表只有一行时使用
    2、const 表中有且只有匹配的行时使用，如对主键或是唯一索引的查询，这时效率最高的联接方式
    3、eq_ref 唯一索引或主键索引查询，对于每个索引键，表中只有一条记录与之匹配，
    4、ref 非唯一索引查询，返回匹配某个单独值的所有行
    5、ref_of_null 类似于ref类型的查询，但是附加了对null值列的查询
    6、index_merge 该联接类型表示使用了索引合并优化方法
    7、range 索引范围扫描，常见于between 、 > 、 <这样的查询条件
    8、index full index scan全索引扫描，同ALL的区别是，遍历的是索引树
    9、all full table scan全表扫描，这时效率最差的联接方式

extra列：
    distinct 优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作
    not exists 使用 not exists来优化查询
    using filesort 使用额外操作进行排序，通常会出现在order by 或group by 查询中
    using index 使用了覆盖索引进行查询
    using temporary mysql需要使用临时表来处理查询，常见于排序，子查询和分组查询
    using where 需要在mysql服务器层使用where条件来进行过滤数据
    select tables optimized away 直接通过索引来获得数据，不用访问表
possible keys列：
    指出mysql能使用那些索引来优化查询
    查询列所涉及到的列上的索引都会被列出来，但是不一定会使用到
key列：
    查询优化器优化查询实际所使用的索引，如果没有使用索引，则显示null，如使用了覆盖索引，则该索引仅出现在key列中

key_len列
    表示索引字段的最大长度
ref列：
    表示哪些列或常量被用于查询索引列上的值
rows列：
    表示mysql通过索引统计信息，估算的所需读取的行数
    rows值的大小是统计抽样结果，并不是十分准确
filtered列：
    表示返回结果的行数占需读取行数的百分比
    filtered列的值越大越好
    filtered列的值依赖于统计信息

执行计划的限制：
    无法展示存储过程，触发器，udf对查询的影响
    无法使用explain对存储过程进行分析
    早起版本的mysql只支持对select语句进行分析

执行计划：相对比较准确表达出当前sql运行状况（判断是否走了索引，走了什么索引） type类型从上到下效率越来越低
1、explain sql语句 （explain和desc是一样的方法）
  desc  sql语句
explain select * from tb1;

id  select_type table   type  possible_keys    key_len   ref    rows    Extra
1    SIMPLE        tb1     ALL   NULL                NULL      NULL    3441639 NULL
type ALL 表示会对数据表进行全表扫描  rows 表示3441639条数据

explain select nid from tb1;
id  select_type table   type  possible_keys    key_len   ref    rows    Extra
1    SIMPLE        tb1     INDEX   NULL            ix_name   NULL   3441639  using index
type INDEX 表示会对索引表进行全表扫描  rows 表示3441639条数据
type 为ALL和INDEX 表示sql效率并不高

2、limit
select * from tb1 where name="alex";  会全表扫描
select * from tb1 where name="alex" limit 1; 匹配到一条后就不在继续扫描，除非数据在最后面才会全表扫描

3、range 多索引进行范围查询时 （这个类型比较常用）
explain select nid from tb1 where name < "123";
id  select_type table   type  possible_keys    key_len   ref    rows    Extra
1    SIMPLE        tb1     range   NULL            ix_name   NULL   1  using index
type range  效率比上面的ALL和index高