1、explain 简介
explain 命令可以用来分析select 语句的运行效果，通过explain命令可以得到下面这些信息: 表的读取顺序，数据读取操作的操作类型 ，哪些索引可以使用，哪些索引被实际使用，表之间的引用，每张表有多少行被优化器查询等信息。
两个扩展命令：
-
explain extended
能够在原本explain的基础上额外的提供一些查询优化的信息，通过mysql的show warnings命令可以得到优化后的查询语句，可以看出优化器能优化什么。用explain extended查看执行计划会比explain多一列 filtered。filtered列给出了一个百分比的值，这个百分比值和rows列的值一起使用，rows * filtered/100 可以估计出那些将要和explain中的前一个表进行连接的行的数目。前一个表就是指explain 的 id列的值比当前表的id小的表。具体的使用，下面会有涉及。
-
explain partitions(TODO)
查看语句访问的分区。
2、explain 各列解释
2.1 id
MySQL Query Optimizer选定的执行计划中查询的序列号。
这里的id，是select的序号，有几个select，就有多少个不同的id。并且id的顺序，是按照select在语句中出现的顺序往后延续的。
如果没有出现derived等特殊语法，那么explain的执行顺序是按照从上到下来执行的。
下面这个sql 的执行顺序是从里往外，依次执行。
|
|
|---|
下面这个查询语句，子查询id为2先执行，inner join的两个查询的id都是1。
| explain select pd.poi_id,pc.store_name from poi_deal pd inner join (select * from poi_crm where category_id = 1) pc on pd.poi_id = pc.poi_id; |
|
|
2.2 select_type
（1）SIMPLE
简单的select查询语句。
|
|
|---|
（2） PRIMARY
复杂查询的最外层的select，这个类型通常可以在DERIVED 和UNION 类型混合使用时见到。
| explain select * from (select * from poi_crm where category_id =1) pc; |
|
|
（3） DERIVED
当一个表不是一个物理表时，那么就被叫做DERIVED
|
|
|---|
（4） UNION
UNION 会产生没有id的行，select_type对应为UNION RESULT
|
|
|---|
（5） UNION RESULT
见上
（6） SUBQUERY
一般的子查询
（7） DEPENDENT SUBQUERY
与上面的区别就是子查询的select，取决于外面的查询。什么意思呢？请看下面的查询语句
| explain extended select * from business_opportunity bo where bo.id in (select boa.opportunity_id from business_opportunity_action boa where user_id in (26494, 9239, 23705,82)); |
|
|
这个查询是分为下面两步的：
a.首先是select * from business_opportunity 得到411198条结果，组成一个大的结果集。
b.然后，上面的结果集的每一条记录将与子查询组成新的查询语句select boa.opportunity_id from business_opportunity_action boa where user_id in (26494, 9239, 23705,82) and boa.opportunity_id= %bo.id%。这就相当于子查询要执行411198次。
所以说子查询取决于外面的查询，导致查询时间很长，为3.69s。
可以从show warnings 看出优化后的查询语句。
|
|
|---|
从中可以看出优化的in，where <in_optimizer>(`meituancrm`.`bo`.`id`,<exists>(<index_lookup>(<cache>(`meituancrm`.`bo`.`id`) in business_opportunity_action on idx_opportunity_id_action_time where ((`meituancrm`.`boa`.`user_id` in (26494,9239,23705,82)) and (<cache>(`meituancrm`.`bo`.`id`) = `meituancrm`.`boa`.`opportunity_id`)))))
注意其中and (<cache>(`meituancrm`.`bo`.`id`) = `meituancrm`.`boa`.`opportunity_id`))) 作为了where里面的条件，从而验证了上面的分析。
优化策略：
使用临时表，将bo表和临时表连接起来查。sql语句如下
| select bo.* from business_opportunity bo inner join (select distinct opportunity_id from business_opportunity_action where user_id in (26494, 9239, 23705,82)) boa on boa.opportunity_id = bo.id; |
|
|
show warnings的结果如下，可以看出没有对语句进行优化。
|
|
|---|
语句的执行时间如下：
| ps. mysql有时候优化子查询很糟糕，特别是在In()子查询的时候 |
|---|
2.3 table
显示操作的哪张表
2.4 type
连接使用了哪种类别以及是否使用了索引。
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
（1）System
这是const联接类型的一个特例。表仅有一行满足条件。
| explain select * from (select * from user where id = 26494) a; |
|
|
（2）const
表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数（可以参加下面show warnings的结果）(如何查出这些常数？ TODO)。const表很快，因为它们只读取一次！ const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。
| explain extended select * from user where id = 26494; |
|
|
show warnings结果
|
|
|---|
（3）eq_ref
对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。（简单select查询应该不会有这种type出现）
| explain extended select * from business_opportunity bo inner join business_opportunity_action boa on bo.id=boa.opportunity_id; |
|
|
（4）ref
与 eq_ref 类似，区别在于不是使用 primary key 或者 unique index ，而是使用普通的索引
ref出现在使用=或<=>（安全等于）操作符的带索引的列。
1、简单查询，匹配索引值的是联合PRIMARY KEY或UNIQUE索引的前缀
| explain select * from bd_city_date where employeeid = 3 and datekey = 20140401; |
|
|
3、复杂查询，联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY
| explain select * from user inner join orgunit_district ora on user.org_id = ora.orgunit_id; |
|
|
（5）ref_or_null
该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
在下面的例子中，MySQL可以使用ref_or_null联接来处理ref_tables：
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
（6）index_merge
该联接类型表示使用了索引合并优化方法。
|
|
|---|
当 and的时候用的联合主键索引
or的时候没有去用联合主键索引，而是同时用了联合主键索引和datekey索引
| explain select * from bd_city_date where employeeid = 3 and datekey = 20140401; explain select * from bd_city_date where employeeid = 3 or datekey = 20140401; |
|
|
（7）unique_subquery（没理解什么意思，TODO）
该联接类型用于替换value IN (SELECT primary_key FROM single_table WHERE some_expr)这样的子查询的ref。注意ref列，其中第二行显示的是func，表明unique_subquery是一个函数，而不是一个普通的ref。
| explain select * from branch_user where branch_id in (select poi_id from poi_crm where source = 1); |
|
|
（8）index_subquery（没理解什么意思，TODO）
该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
| explain select * from bd_city_date where name in (select name from user where city_id = 62); |
|
|
（9）range
只检索给定范围的行,使用一个索引来选择行。
| explain select * from user where id >26778; explain select * from user where name like "丁%"; |
|
|
（10）index
该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。
当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。
（为什么用的是city_id这个索引，主键是id？ TODO）
|
|
|---|
（11）ALL
对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。
|
|
|---|
2.5 possible_keys
possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
explain的时候可能会出现possible_keys为有列，而key显示为NULL的情况。出现这种情况的原因是因为表中数据内容不多，查询语句需要查询全表，索引对查询帮助不大。如果出现这种情况，可以通过在查询语句中强制使用索引，来验证。（这个没有找到合适的例子，可以参考http://stackoverflow.com/questions/5719392/why-isnt-mysql-using-any-of-these-possible-keys）
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询
2.6 key
key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
2.7 key_len
key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。
使用的索引的长度。在不损失精确性的情况下，长度越短越好
一些示例值如下：
key_len: 4 // INT NOT NULL
key_len: 5 // INT NULL
key_len: 30 // CHAR(30) NOT NULL
key_len: 32 // VARCHAR(30) NOT NULL
key_len: 92 // VARCHAR(30) NULL CHARSET=utf8
因为varchar是可变长度的，所以这2个字节用来存储字符串长度。同时，如果该字段是可以为null的话，那么索引也需要额外增加一个字节用来记录是否为null。
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来来做索引。
2.8 ref
指出对 key 列所选择的索引的查找方式，常见的值有 const, func, NULL, 具体字段名。
2.9 rows
rows列显示MySQL认为它执行查询时必须检查的行数。
注意，explain是mysql分析你的sql不预估会扫描多少行，所以这个数字是不准确的。
参见mysql官方文档
rows
The rows column indicates the number of rows MySQL believes it must examine to execute the query.
For InnoDB tables, this number is an estimate, and may not always be exact.
— http://dev.mysql.com/doc/refman/5.5/en/explain-output.html#explain_rows
2.10 Extra
(1).Distinct
一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
| explain extended select distinct ora.rank from orgunit_rank ora inner join orgunit_district od on ora.id= od.orgunit_id; |
 |
(2).Using filesort
看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行。
会浏览整个user表，并保存所有的排序关键字job_number以及对应row的指针，然后排序关键字并指针去检索行。
| explain extended select * from user order by job_number; |
 |
(3).Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
返回的内容只有索引的信息，没有读取实际的行。这是性能很高的一种表现。
 |
|---|
(4).Using temporary （需要优化）
看到这个的时候，查询需要优化了。发生这种情况一般都是需要进行优化的。mysql需要创建一张临时表用来处理此类查询。
用临时表来进行distinct
| explain select distinct org_name from user ; |
 |
这个查询是using index，因为name有索引，不需要临时表来进行distinct操作。所以在遇到using temporary的时，首先能想到加索引来优化。
| explain select distinct name from user ; |
 |
(5).Using where
表示 MySQL 服务器从存储引擎收到行后再进行“后过滤”（Post-filter）。所谓“后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。因为检查是在读取行后才进行的，所以称为“后过滤”。
 |
|---|
当使用主键索引或unique key时 where 进行=匹配时，通过索引便能确定返回的行，无需进行“后过滤”，是不会显示using where的。
3、explain用于sql优化经验谈
TODO
扫一扫
765
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
