MySQL :: MySQL 5.6 Reference Manual :: 8.8 Understanding the Query Execution Plan
取决于tables、columns、indexes的详细情况和where条件，MySQL优化器考虑了很多技术来有效的进行SQL Query中的lookup。
大表的查询可以不需要读取所有的行；多个表的连接可以不用对比每一个行组合。
优化器选择了一系列操作实现最高效的查询，这些操作的集合称为“query execution plan”，被大家熟知为EXPLAIN plan。
你需要接受EXPLAIN plan显示查询优化良好的一面，但同时需要掌握SQL语法和索引技术以便在查询效率不好的时候进行优化。
一、Optimizing Queries with EXPLAIN
EXPLAIN 语句提供了MySQL是怎么执行语句的相关信息：
EXPLAIN语句提供了MySQL是怎么执行语句的相关信息。EXPLAIN可用于SELECT,DELETE,INSERT,REPLACE, andUPDATE语句。当使用
EXPLAIN，MySQL会显示optimizer关于语句执行计划的信息。 即，MySQL解释了语句是怎么处理的，比如表是怎么加入的，以什么顺序加入。参见Section 8.8.2, “EXPLAIN Output Format”。EXPLAIN EXTENDED生成可以通过SHOW WARNINGS展示的额外执行计划信息。参见Section 8.8.3, “Extended EXPLAIN Output Format”。EXPLAIN PARTITIONS用于检查涉及partitioned tables的查询。参见Section 19.3.5, “Obtaining Information About Partitions”。FORMAT参数用于格式化输出。TRADITIONAL以表格格式输出，默认配置。JSON以JSON格式输出，通过设置FORMAT = JSON，输出包含扩展和分区信息。
通过EXPLAIN，你可以看到哪里需要添加索引，通过索引查找行将使得语句执行更快。还可以使用EXPLAIN来检查optimizer是否以最优顺序join tables。要指定optimizer使用与SELECT语句中表的命名顺序相对应的顺序，请在语句开头使用SELECT STRIGHT_join，而不是SELECT。
参考Section 13.2.9, “SELECT Statement”
但是，STRAIGHT_JOIN可能会阻止使用索引，因为它禁用半连接转换。
参见Section 8.2.2.1, “Optimizing Subqueries with Semijoin Transformations”.
如果遇到索引未被使用的问题，可以运行ANALYZE TABLE来更新表统计信息，例如键的基数，这可能会影响optimizer所做的选择。
参见 Section 13.7.2.1, “ANALYZE TABLE Statement”。
EXPLAIN还可以用于获取表中列的信息。EXPLAIN *tbl_name*等同于DESCRIBE *tbl_name*和SHOW COLUMNS FROM *tbl_name*。
参见 Section 13.8.1, “DESCRIBE Statement”， Section 13.7.5.6, “SHOW COLUMNS Statement”.
二、EXPLAIN Output Format
1. EXPLAIN Output Columns
# EXPLAIN
+----+--------------------+----------------------+--------+---------------------------------------------------------+------------------------+---------+-----------------------------------+--------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------------------+--------+---------------------------------------------------------+------------------------+---------+-----------------------------------+--------+------------------------------------+
| 1 | PRIMARY | b | index | PRIMARY,IDX_lcgrpper_insuredno | IDX_lcgrpper_insuredno | 63 | NULL | 396391 | Using where; Using index |
| 1 | PRIMARY | a | ref | IDX_INSUREDNO_NO,IDX_LCINSURED_GPCONT,IDX_LCINSURED_GCI | IDX_INSUREDNO_NO | 74 | lis.b.InsuredNo | 1 | Using index condition; Using where |
| 5 | SUBQUERY | lcgrppersonalvoucher | index | NULL | IDX_lcgrpper_insuredno | 63 | NULL | 396391 | Using where; Using index |
| 4 | DEPENDENT SUBQUERY | ldcode | eq_ref | PRIMARY | PRIMARY | 184 | const,lis.a.relationtomaininsured | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | ldcode | eq_ref | PRIMARY | PRIMARY | 184 | const,lis.a.idtype | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | ldcode | eq_ref | PRIMARY | PRIMARY | 184 | const,lis.a.sex | 1 | Using where |
+----+--------------------+----------------------+--------+---------------------------------------------------------+------------------------+---------+-----------------------------------+--------+------------------------------------+
# EXPLAIN EXTENDED
+----+--------------------+----------------------+--------+---------------------------------------------------------+------------------------+---------+-----------------------------------+--------+----------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+----------------------+--------+---------------------------------------------------------+------------------------+---------+-----------------------------------+--------+----------+------------------------------------+
| 1 | PRIMARY | b | index | PRIMARY,IDX_lcgrpper_insuredno | IDX_lcgrpper_insuredno | 63 | NULL | 396391 | 100.00 | Using where; Using index |
| 1 | PRIMARY | a | ref | IDX_INSUREDNO_NO,IDX_LCINSURED_GPCONT,IDX_LCINSURED_GCI | IDX_INSUREDNO_NO | 74 | lis.b.InsuredNo | 1 | 100.00 | Using index condition; Using where |
| 5 | SUBQUERY | lcgrppersonalvoucher | index | NULL | IDX_lcgrpper_insuredno | 63 | NULL | 396391 | 100.00 | Using where; Using index |
| 4 | DEPENDENT SUBQUERY | ldcode | eq_ref | PRIMARY | PRIMARY | 184 | const,lis.a.relationtomaininsured | 1 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | ldcode | eq_ref | PRIMARY | PRIMARY | 184 | const,lis.a.idtype | 1 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | ldcode | eq_ref | PRIMARY | PRIMARY | 184 | const,lis.a.sex | 1 | 100.00 | Using where |
+----+--------------------+----------------------+--------+---------------------------------------------------------+------------------------+---------+-----------------------------------+--------+----------+------------------------------------+
| Column | JSON Name | Meaning |
|---|---|---|
| id | select_id |
The SELECT identifier |
| select_type | None | The SELECT type |
| table | table_name |
The table for the output row |
| partitions | partitions |
The matching partitions. Only if the PARTITIONS keyword is used |
| type | access_type |
The join type |
| possible_keys | possible_keys |
MySQL可能使用的索引。如果是NULL，则无索引，需要添加。此列完全独立于EXPLAIN输出中显示的表格顺序。这意味着一些key在实际生成的表顺序中可能不可用。 |
| key | key |
The index actually chosen |
| key_len | key_length |
The length of the chosen key |
| ref | ref |
The columns compared to the index |
| rows | rows |
Estimate of rows to be examined |
| filtered | filtered |
Percentage of rows filtered by table condition |
| Extra | None | Additional information |
select_type
| select_type | JSON Name | Meaning |
|---|---|---|
| SIMPLE | None | Simple SELECT (not using UNION or subqueries) |
| PRIMARY | None | Outermost SELECT
|
| UNION | None | Second or later SELECT statement in a UNION
|
| DEPENDENT UNION |
dependent (true) |
Second or later SELECT statement in a UNION, dependent on outer query |
| UNION RESULT | union_result |
Result of a UNION
|
| SUBQUERY | None | First SELECT in subquery |
| DEPENDENT SUBQUERY |
dependent (true) |
First SELECT in subquery, dependent on outer query |
| DERIVED | None | Derived table |
| MATERIALIZED | materialized_from_subquery |
Materialized subquery |
| UNCACHEABLE SUBQUERY |
cacheable (false) |
A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query |
| UNCACHEABLE UNION |
cacheable (false) |
The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) |
2. EXPLAIN Join Types
从最好到最差：
system
表仅有一行 (= system table)。const的一种特殊存在。-
const
表最多只有一个匹配行，在开始查询的时候就会读取到。因为仅有一行，该行中列的值会被optimizer的其余部分视为常量constants。 const tables 非常快，仅为它们仅读取一次。const 用在对
PRIMARY KEYorUNIQUEindex的所有部分跟常量值constant values比对的时候。 在如下的查询中，tbl_name可以作为一个const table:
SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name
WHERE primary_key_part1=1 AND primary_key_part2=2;
-
eq_ref
对于每个联合，仅从该表读取一行。除systemandconst类型外，这可能是最好的 join type。在使用了索引的全部并且该索引是PRIMARY KEYorUNIQUE NOT NULLindex的情况下，使用该类型。
eq_ref可以用于通过=元算符对比的，创建了索引的列。对比的值可以是一个常量，或表达式。该表达式使用了在该表之前就读取的表的列。如下范例，MySQL可以使用eq_ref联接到进程ref_table:
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
-
ref
对于每个联合，所有匹配索引的行都读取自该表。如果联接仅使用Key的最左侧前缀，或者Key不是PRIMARY KEYorUNIQUEindex（换句话说，联接无法基于key选择唯一row），则使用ref。如果使用的Key只匹配数行，则这是一种良好的join type。
ref可以用于通过=or<=>operator进行对比的，拥有索引的列。如下范例，MySQL 可以使用ref联接到进程ref_table:
SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
fulltext
联接使用FULLTEXT索引。ref_or_null
类似于ref，但是有一个查询是否包含NULL值的额外检索。这种join type优化常用于子查询。如下范例， MySQL 可以使用ref_or_null联接到进程ref_table:
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
参见 Section 8.2.1.12, “IS NULL Optimization”.
index_merge
索引合并优化。该情况下，key列包含所用索引的列表，key_len包含所用索引的最长key部分的列表。
参见Section 8.2.1.3, “Index Merge Optimization”unique_subquery
在IN子查询中替换eq_ref类型，如下格式：
value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery 是彻底替换subquery的更高效的索引查询功能。
-
index_subquery
类似于unique_subquery。它替代IN子查询，但是它工作于非唯一索引，如下格式：
value IN (SELECT key_column FROM single_table WHERE some_expr)
-
range
在给定范围内，使用索引检索。key列显示了使用哪些索引，key_len列包含了使用的最长的key。该类型下，ref列为NULL。
range适用于 key 列对比常量，通过=,<>,>,>=,<,<=,IS NULL,<=>,BETWEEN,LIKE, orIN()操作符：
SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name
WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
-
index
indexjoin type 与ALL同，只是扫描了索引树。发生于如下两种方式：如果索引是查询的covering index，并且可以用于满足表中所需的所有数据，则只需扫描索引树。该情况下，
Extra显示Using index。仅扫描索引通常比ALL快，因为索引的大小通常小于表数据。使用从索引中读取的内容执行全表扫描，按索引顺序查找数据。
Uses index不会出现在Extra列中。
当查询使用列是唯一索引一部分，使用该联接类型。
ALL
对每个联合都进行全表扫描。如果该表是没有标记为const的第一个表，这通常是不好的；其他情况下则非常糟糕。通常，可以通过添加索引来避免ALL，这些索引允许基于常量或更早表的列值从表中检索行。
3. EXPLAIN Extra Information
4. EXPLAIN Output Interpretation
三、Extended EXPLAIN Output Format
四、Estimating Query Performance
大多数情况下，你可以通过磁盘查找来估算查询性能。对于小表，通常仅需要一次磁盘查找就可以找到该行（因为索引缓存了）。对于大表，可以估算，使用B-tree索引，需要如下很多次查询才能获取到该行：
log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1
在MySQL，一个index block通常是1,024 bytes， data pointer通常是4 bytes。对于一个键值长度3字节（the size of MEDIUMINT）、50万行的表，公式显示 log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 次seek请求。
这个索引需要大约50000073/2=5.2MB的存储空间（假设典型的索引缓冲区填充率为2/3），因此内存中可能有很多索引，因此只需要一个或两个调用来读取数据以查找行。
索引需要大概500,000 * 7 * 3/2 = 5.2MB的存储空间，这里假设index buffer填充率通常为2/3，所以内存中可能有很多索引，只需要一个或两个调用来读取数据以查找行。
对于写操作，需要四个seek请求来确认放置新索引值的位置，通常需要两个seek请求来更新索引并写入行。
前面的讨论并不意味着您的应用程序会慢慢下降 log N的性能。只要所有内容被OS或MySQL缓存，表变大只会稍微变慢。在数据变得太大而无法缓存之后，才会变得慢得多，直到应用程序受限于磁盘请求（增长 log N）。
要避免这种情况，key cache需要随着数据的增长而增加。
对于MyISAM表，key cache size由key_buffer_size 系统变量控制。参考 Section 5.1.1, “Configuring the Server”。
