mysql中explain⽤法详解
如果在select语句前放上关键词explain，mysql将解释它如何处理select，提供有关表如何联接和联接的次序。
explain的每个输出⾏提供⼀个表的相关信息，并且每个⾏包括下⾯的列：
1，id  select识别符。这是select的查询序列号。
2，select_type 可以为⼀下任何⼀种类型
simple  简单select(不使⽤union或⼦查询)
primary  最外⾯的select
union    union中的第⼆个或后⾯的select语句
dependent union  union中的第⼆个或后⾯的select语句，取决于外⾯的查询
union result  union的结果。
subquery ⼦查询中的第⼀个select
dependent subquery  ⼦查询中的第⼀个select，取决于外⾯的查询
derived    导出表的select(from⼦句的⼦查询)
3，table  输出的⾏所引⽤的表。
4，type  联接类型。下⾯给出各种联接类型，按照从最佳类型到最坏类型进⾏排序：
system  表仅有⼀⾏(=系统表)。这是const联接类型的⼀个特例。
const  表最多有⼀个匹配⾏，它将在查询开始时被读取。因为仅有⼀⾏，在这⾏的列值可被优化器剩余部分认为是常数。const表很快，因为它们只读取⼀次！
eq_ref 对于每个来⾃于前⾯的表的⾏组合，从该表中读取⼀⾏。这可能是最好的联接类型，除了const类型。它⽤在⼀个索引的所有部分被联接使⽤并且索引是unique或primary key
ref  对于每个来⾃于前⾯的表的⾏组合，所有有匹配索引值的⾏将从这张表中读取。如果联接只使⽤键的最左边的前缀，或如果键不是unique或primary key（换句话说，如果联接不能基于关键字选择单个⾏的话），则使⽤ref。如果使⽤的键仅仅匹配少量⾏，该联接类型是不错的。
ref可以⽤于使⽤=或<=>操作符的带索引的列。
possible_keys 如果该列是null，则没有相关的索引。在这种情况下，可以通过检查where⼦句看是否它引⽤某些列或适合索引的列来提⾼你的查询性能。如果是这样，创造⼀个适当的索引并且再次⽤explain检查查询key 列显⽰mysql实际决定使⽤的键（索引）。如果没有选择索引，键是null。要想强制mysql使⽤或忽视possible_keys列中的索引，在查询中使⽤force index、use index或者ignore index。
5，rows  rows列显⽰mysql认为它执⾏查询时必须检查的⾏数。
以上简单介绍了mysql中explain语句的⽤法，希望对⼤家有所帮助。
的
在 explain的帮助下，您就知道什么时候该给表添加索引，以使⽤索引来查记录从⽽让select 运⾏更快。
如果由于不恰当使⽤索引⽽引起⼀些问题的话，可以运⾏ analyze table来更新该表的统计信息，例如键的基数，它能帮您在优化⽅⾯做出更好的选择。
explain 返回了⼀⾏记录，它包括了 select语句中⽤到的各个表的信息。这些表在结果中按照mysql即将执⾏的查询中读取的顺序列出来。mysql⽤⼀次扫描多次连接（single- sweep,multi-join）的⽅法来
解决连接。这意味着mysql从第⼀个表中读取⼀条记录，然后在第⼆个表中查到对应的记录，然后在第三个表中查，依次类推。当所有的表都扫描完了，它输出选择的字段并且回溯所有的表，直到不到为⽌，因为有的表中可能有多条匹配的记录下⼀条记录将从该表读取，再从下⼀个表开始继续处理。
在mysql version 4.1中，explain输出的结果格式改变了，使得它更适合例如 union语句、⼦查询以及派⽣表的结构。更令⼈注意的是，它新增了2个字段： id和 select_type。当你使⽤早于mysql4.1的版本就看不到这些字段了。
explain结果的每⾏记录显⽰了每个表的相关信息，每⾏记录都包含以下⼏个字段：
id
本次 select 的标识符。在查询中每个 select都有⼀个顺序的数值。
select_type
select 的类型，可能会有以下⼏种：
simple: 简单的 select （没有使⽤ union或⼦查询）
primary: 最外层的 select。
union: 第⼆层，在select 之后使⽤了 union。
dependent union: union 语句中的第⼆个select，依赖于外部⼦查询
subquery: ⼦查询中的第⼀个 select
dependent subquery: ⼦查询中的第⼀个 subquery依赖于外部的⼦查询
derived: 派⽣表 select（from⼦句中的⼦查询）
table
记录查询引⽤的表。
type
表连接类型。以下列出了各种不同类型的表连接，依次是从最好的到最差的：
system：表只有⼀⾏记录（等于系统表）。这是 const表连接类型的⼀个特例。
const：表中最多只有⼀⾏匹配的记录，它在查询⼀开始的时候就会被读取出来。由于只有⼀⾏记录，在余下的优化程序⾥该⾏记录的字段值可以被当作是⼀个恒定值。const表查询起来⾮常快，因为只要读取⼀次！const ⽤于在和 primary key 或unique 索引中有固定值⽐较的情形。下⾯的⼏个查询中，tbl_name 就是 c表了：
select * from tbl_name where primary_key=1; select * from tbl_namewhere primary_key_part1=1 and primary_key_part2=2; eq_ref：从该表中会有⼀⾏记录被读取出来以和从前⼀个表中读取出来的记录做联合。与const类型不同的是，这是最好的连接类型。它⽤在索引所有部分都⽤于做连接并且这个索引是⼀个primary key 或 unique 类型。eq_ref可以⽤于在进⾏"="做⽐较时检索字段。⽐较的值可以是固定值或者是表达式，表达⽰中可以使⽤表⾥的字段，它们在读表之前已经准备好了。以下的⼏个例⼦中，mysql使⽤了eq_ref 连接来处理 ref_table：
select * from ref_table,other_table whereref_table.key_column=lumn; select * fromref_table,other_table whereref_table.key_column_part1=lumn andref_table.key_column_part2=1;
ref: 该表中所有符合检索值的记录都会被取出来和从上⼀个表中取出来的记录作联合。ref⽤于连接程序使⽤键的最左前缀或者是该键不是 primary key 或 unique索引（换句话说，就是连接程序⽆法根据
键值只取得⼀条记录）的情况。当根据键值只查询到少数⼏条匹配的记录时，这就是⼀个不错的连接类型。 ref还可以⽤于检索字段使⽤ =操作符来⽐较的时候。以下的⼏个例⼦中，mysql将使⽤ ref 来处理ref_table：
select * from ref_table where key_column=expr; select * fromref_table,other_table
whereref_table.key_column=lumn; select * fromref_table,other_table
whereref_table.key_column_part1=lumn andref_table.key_column_part2=1;
ref_or_null: 这种连接类型类似 ref，不同的是mysql会在检索的时候额外的搜索包含null 值的记录。这种连接类型的优化是从mysql4.1.1开始的，它经常⽤于⼦查询。在以下的例⼦中，mysql使⽤ref_or_null 类型来处理 ref_table：
select * from ref_table where key_column=expr or key_column is null;
unique_subquery: 这种类型⽤例如⼀下形式的 in ⼦查询来替换 ref：
value in (select primary_key from single_table where some_expr)
unique_subquery: 只是⽤来完全替换⼦查询的索引查函数效率更⾼了。
index_subquery: 这种连接类型类似 unique_subquery。它⽤⼦查询来代替in，不过它⽤于在⼦查询中没有唯⼀索引的情况下，例如以下形式：
value in (select key_column from single_table where some_expr)
range: 只有在给定范围的记录才会被取出来，利⽤索引来取得⼀条记录。key字段表⽰使⽤了哪个索引。key_len字段包括了使⽤的键的最长部分。这种类型时 ref 字段值是 null。range⽤于将某个字段和⼀个定植⽤以下任何操作符⽐较时 =, <>, >,>=, <, <=, is null, <=>, between, 或 in：
select * from tbl_name where key_column = 10; select * fromtbl_name where key_column between 10 and 20; select * from tbl_namewhere key_column in (10,20,30); select * from tbl_name wherekey_part1= 10 and key_part2 in (10,20,30); index: 连接类型跟 all ⼀样，不同的是它只扫描索引树。它通常会⽐ all快点，因为索引⽂件通常⽐数据⽂件⼩。mysql在查询的字段知识单独的索引的⼀部分的情况下使⽤这种连接类型。
all: 将对该表做全部扫描以和从前⼀个表中取得的记录作联合。这时候如果第⼀个表没有被标识为const的话就不⼤好了，在其他情况下通常是⾮常糟糕的。正常地，可以通过增加索引使得能从表中更快的取得记录以避免all。
possible_keys字段是指 mysql在搜索表记录时可能使⽤哪个索引。注意，这个字段完全独⽴于explain 显⽰的表顺序。这就意味着 possible_keys⾥⾯所包含的索引可能在实际的使⽤中没⽤到。如果这个字段的值是null，就表⽰没有索引被⽤到。这种情况下，就可以检查 where⼦句中哪些字段那些字段适合增加索引以提⾼查询的性能。就这样，创建⼀下索引，然后再⽤explain 检查⼀下。详细的查看章节"14.2.2 alter tablesyntax"。想看表都有什么索引，可以通过 show index from tbl_name来
看。
key字段显⽰了mysql实际上要⽤的索引。当没有任何索引被⽤到的时候，这个字段的值就是null。想要让mysql强⾏使⽤或者忽略在 possible_keys字段中的索引列表，可以在查询语句中使⽤关键字force index, use index，或 ignore index。如果是myisam 和 bdb 类型表，可以使⽤ analyzetable 来帮助分析使⽤使⽤哪个索引更好。如果是 myisam类型表，运⾏命令myisamchk --analyze也是⼀样的效果。详细的可以查看章节"14.5.2.1 analyze tablesyntax"和"5.7.2 table maintenance and crash recovery"。
key_len 字段显⽰了mysql使⽤索引的长度。当 key 字段的值为 null时，索引的长度就是 null。注意，key_len的值可以告诉你在联合索引中mysql会真正使⽤了哪些索引。
ref 字段显⽰了哪些字段或者常量被⽤来和 key配合从表中查询记录出来。
rows 字段显⽰了mysql认为在查询中应该检索的记录数。
本字段显⽰了查询中mysql的附加信息。以下是这个字段的⼏个不同值的解释：
handicraftdistinct:mysql当到当前记录的匹配联合结果的第⼀条记录之后，就不再搜索其他记录了。
not exists:mysql在查询时做⼀个 left join优化时，当它在当前表中到了和前⼀条记录符合 left join条件后，就不再搜索更多的记录了。下⾯是⼀个这种类型的查询例⼦：
select * from t1 left join t2 on t1.id=t2.id where t2.id isnull;
假使 t2.id 定义为 not null。这种情况下，mysql将会扫描表 t1并且⽤ t1.id 的值在 t2 中查记录。当在 t2中到⼀条匹配的记录时，这就意味着 t2.id 肯定不会都是null，就不会再在 t2 中查相同 id值的其他记录了。也可以这么说，对于 t1 中的每个记录，mysql只需要在t2 中做⼀次查，⽽不管在 t2 中实际有多少匹配的记录。
range checked for each record (index map: #)
mysql没到合适的可⽤的索引。取代的办法是，对于前⼀个表的每⼀个⾏连接，它会做⼀个检验以决定该使⽤哪个索引（如果有的话），并且使⽤这个索引来从表⾥取得记录。这个过程不会很快，但总⽐没有任何索引时做表连接来得快。
using filesort: mysql需要额外的做⼀遍从⽽以排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录，并且将所有符合 where条件的记录的要排序的键和指向记录的指针存储起来。这些键已经排完序了，对应的记录也会按照排好的顺序取出来。详情请看"7.2.9how mysql optimizes order by"。
using index
字段的信息直接从索引树中的信息取得，⽽不再去扫描实际的记录。这种策略⽤于查询时的字段是⼀个独⽴索引的⼀部分。
using temporary: mysql需要创建临时表存储结果以完成查询。这种情况通常发⽣在查询时包含了groupby 和 order by ⼦句，它以不同的⽅式列出了各个字段。
using where
where⼦句将⽤来限制哪些记录匹配了下⼀个表或者发送给客户端。除⾮你特别地想要取得或者检查表种的所有记录，否则的话当查询的extra 字段值不是 using where 并且表连接类型是 all 或 index时可能表⽰有问题。
如果你想要让查询尽可能的快，那么就应该注意 extra 字段的值为usingfilesort 和 using temporary 的情况。
你可以通过 explain 的结果中 rows字段的值的乘积⼤概地知道本次连接表现如何。它可以粗略地告诉我们mysql在查询过程中会查询多少条记录。如果是使⽤系统变量 max_join_size 来取得查询结果，这个乘积还可以⽤来确定会执⾏哪些多表select 语句。
下⾯的例⼦展⽰了如何通过 explain提供的信息来较⼤程度地优化多表联合查询的性能。
假设有下⾯的 select 语句，正打算⽤ explain 来检测：
explain select tt.ticketnumber, tt.timein, tt.projectreference,tt.estimatedshipdate, tt.actualshipdate, tt.clientid,tt.servicecodes, tt.repetitiveid, tt.currentprocess,tt.dvolume, tt.dpprinted, et.country,untry, do.custname from tt, et, et as et_1, do wherett.submittime is null and tt.actualpc = et.employid andtt.assignedpc = ployid and tt.clientid = do.custnmbr;
在这个例⼦中，先做以下假设：
要⽐较的字段定义如下：
table  column  columntype
tt  actualpc char(10)
tt  assignedpc char(10)
tt  clientid char(10)
et  employid char(15)
do  custnmbr char(15)
数据表的索引如下：
table  index
tt  actualpc
tt  assignedpc
tt  clientid
et  employid (primary key)
do  custnmbr (primary key)
tt.actualpc 的值是不均匀分布的。
在任何优化措施未采取之前，经过 explain分析的结果显⽰如下：
table type possible_keys key key_len ref rows extra
et all primarynull null null 74
do all primary null null null 2135
et_1 allprimary null null null 74
tt all assignedpc, null null null 3872 clientid, actualpc range checked for each record (key map: 35)
由于字段 type 的对于每个表值都是all，这个结果意味着mysql对所有的表做⼀个迪卡尔积；这就是说，每条记录的组合。这将需要花很长的时间，因为需要扫描每个表总记录数乘积的总和。在这情况下，它的积是74 * 2135 * 74 * 3872 =
45,268,558,720条记录。如果数据表更⼤的话，你可以想象⼀下需要多长的时间。
在这⾥有个问题是当字段定义⼀样的时候，mysql就可以在这些字段上更快的是⽤索引（对isam类型
的表来说，除⾮字段定义完全⼀样，否则不会使⽤索引）。在这个前提下，varchar和 char是⼀样的除⾮它们定义的长度不⼀致。由于 tt.actualpc 定义为char(10)，et.employid 定义为 char(15)，⼆者长度不⼀致。
为了解决这个问题，需要⽤ alter table 来加⼤ actualpc的长度从10到15个字符：
mysql> alter table tt modify actualpc varchar(15);
现在 tt.actualpc 和 et.employid 都是 varchar(15)
了。再来执⾏⼀次 explain 语句看看结果：
table type possible_keys key key_len ref rows extra
tt allassignedpc, null null null 3872 using clientid, where actualpc
do all primary null null null 2135 range checked for each record (keymap: 1)
et_1 all primary null null null 74 range checked for eachrecord (key map: 1) et eq_ref primary primary 15 tt.actualpc 1
这还不够，它还可以做的更好：现在 rows值乘积已经少了74倍。这次查询需要⽤2秒钟。
第⼆个改变是消除在⽐较 tt.assignedpc = ployid 和 tt.clientid= do.custnmbr 中字段的长度不⼀致问题：
mysql> alter table tt modify assignedpc varchar(15), ->modify clientid varchar(15);
现在 explain 的结果如下：
table type possible_keys key key_len ref rows extra
et all primary null null null 74
tt ref assignedpc, actualpc ployid 52 using clientid, where actualpc
et_1 eq_ref primary primary 15 tt.assignedpc 1
do eq_ref primary primary 15 tt.clientid 1
这看起来已经是能做的最好的结果了。
遗留下来的问题是，mysql默认地认为字段 tt.actualpc的值是均匀分布的，然⽽表 tt并⾮如此。幸好，我们可以很⽅便的让mysql分析索引的分布：
mysql> analyze table tt;
到此为⽌，表连接已经优化的很完美了，explain 的结果如下：
table type possible_keys key key_len ref rows extra
tt all assignedpc null null null 3872 using clientid, where actualpc
et eq_ref primary primary 15 tt.actualpc 1
et_1 eq_ref primary primary 15 tt.assignedpc 1
do eq_ref primary primary 15 tt.clientid 1
请注意，explain 结果中的 rows字段的值也是mysql的连接优化程序⼤致猜测的，请检查这个值跟真实值是否基本⼀致。如果不是，可以通过在select 语句中使⽤ straight_join 来取得更好的性能，同时可以试着在from分句中⽤不同的次序列出各个表。
EXPLAIN tbl_name
或：
EXPLAIN [EXTENDED] SELECT select_options
mysql面试题详解
每秒100亿亿次浮点运算前者可以得出⼀个表的字段结构等等，后者主要是给出相关的⼀些索引信息，⽽今天要讲述的重点是后者。
举例
复制代码代码如下:
mysql> explain select * from event;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | event | ALL | NULL | NULL | NULL | NULL | 13 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
各个属性的含义
id
select查询的序列号
select_type
select查询的类型，主要是区别普通查询和联合查询、⼦查询之类的复杂查询。
table
输出的⾏所引⽤的表。
type
联合查询所使⽤的类型。
type显⽰的是访问类型，是较为重要的⼀个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
⼀般来说，得保证查询⾄少达到range级别，最好能达到ref。
possible_keys
指出MySQL能使⽤哪个索引在该表中到⾏。如果是空的，没有相关的索引。这时要提⾼性能，可通过检验WHERE⼦句，看是否引⽤某些字段，或者检查字段不是适合索引。
key
显⽰MySQL实际决定使⽤的键。如果没有索引被选择，键是NULL。
key_len
显⽰MySQL决定使⽤的键长度。如果键是NULL，长度就是NULL。⽂档提⽰特别注意这个值可以得出⼀个多重主键⾥mysql 实际使⽤了哪⼀部分。
ref
显⽰哪个字段或常数与key⼀起被使⽤。
hadoop单机模式搭建
rows
这个数表⽰mysql要遍历多少数据才能到，在innodb上是不准确的。
Extra
如果是Only index，这意味着信息只⽤索引树中的信息检索出的，这⽐扫描整个表要快。
如果是where used，就是使⽤上了where限制。
如果是impossible where 表⽰⽤不着where，⼀般就是没查出来啥。
验证表单
如果此信息显⽰Using filesort或者Using temporary的话会很吃⼒，WHERE和ORDER BY的索引经常⽆法兼顾，如果按照WHERE来确定索引，那么在ORDER BY时，就必然会引起Using filesort，这就要看是先过滤再排序划算，还是先排序再过滤划算。
常见的⼀些名词解释
搭建h5游戏网站
Using filesort
MySQL需要额外的⼀次传递，以出如何按排序顺序检索⾏。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。