Explain 结果解读与实践

基于 MySQL 5.0.67 ，存储引擎 MyISAM 。

注：单独一行的"%%"及"`"表示分隔内容，就象分开“第一章”“第二章”。

explain 可以分析 select 语句的执行，即 MySQL 的“执行计划”：

用"\G"代替分号可得到竖排的格式：
mysql> explain select 1\G
*************************** 1
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: No tables used

二、结果列详细说明：
2.1、id 列
用下面的例子来说明，在多张表的查询中，突出的更显著。
1、建表：
create table a(a_id int);
create table b(b_id int);
create table c(c_id int);
2、执行查询explain select * from a join b on a_id=b_id where b_id in (select c_id from c);
mysql> explain select * from a join b on a_id=b_id where b_id in (select c_id from c);
+----+--------------------+-------+...
| id | select_type | table |...
+----+--------------------+-------+...
| 1 | PRIMARY | a |...
| 1 | PRIMARY | b |...
| 2 | DEPENDENT SUBQUERY | c |...
+----+--------------------+-------+...
从 3 个表中查询，对应输出 3 行，每行对应一个表， id 列表示执行顺序，id 越大，越先执行，id 相同，由上至下执行。此处的执行顺序为（以 table 列表示）：c -> a -> b

2.2、select_type 列
MySQL 把 SELECT 查询分成简单和复杂两种类型，复杂类型又可以分成三个大类：简单子查询、所谓的衍生表（子查询在 FROM 子句里）和 UNION 。
SIMPLE：查询中不包含子查询或者UNION
mysql> explain select * from a;
+----+-------------+-------+...
| id | select_type | table |...
+----+-------------+-------+...
| 1 | SIMPLE | a |...
+----+-------------+-------+...

SUBQUERY：子查询
PRIMARY:子查询上层
mysql> explain select * from a where a_id in (select b_id from b);
+----+--------------------+-------+...
| id | select_type | table |...
+----+--------------------+-------+...
| 1 | PRIMARY | a |...
| 2 | DEPENDENT SUBQUERY | b |...
+----+--------------------+-------+...

DERIVED：在FROM列表中包含子查询， MySQL 会递归执行这些子查询，把结果放在临时表里。
mysql> explain select count(*) from (select * from a) as der;
+----+-------------+-------+...
| id | select_type | table |...
+----+-------------+-------+...
| 1 | PRIMARY | NULL |...
| 2 | DERIVED | a |...
+----+-------------+-------+...

2.3、table 列
显示每行对应的表名。若在 SELECT 语句中为表起了别名，则会显示表的别名。

ALL
全表扫描，MySQL 从头到尾扫描整张表查找行。

index
按索引次序扫描表，就是先读索引，再读实际的行，其实还是全表扫描。主要优点是避免了排序，因为索引是排好序的。

mysql> explain select * from aaa where a_id > 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | aaa | NULL | range | a_id | a_id | 4 | NULL | 9 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

IN 比较符也会用 range 表示：
mysql> explain select * from aaa where a_id in (1,3,4);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | aaa | NULL | range | a_id | a_id | 4 | NULL | 3 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

ref
非唯一性索引访问

建表：
create table a4(a_id int not null, key(a_id));
insert into a4 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
mysql> explain select * from a4 where a_id=1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | a4 | NULL | ref | a_id | a_id | 4 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+

eq_ref
使用有唯一性索引查找（主键或唯一性索引）

删除 a_info 的主键：ALTER TABLE `a5_info` DROP PRIMARY KEY;
现在 a_info 已经没有索引了：

删除 a 的主键：alter table a5 drop primary key;
现在 a 也没有索引了：

现在两个表都使用全表扫描了。

建表及插入数据：
create table a6(id int primary key);
create table a6_info(id int, title char(1), key(id));
insert into a6 value(1),(2);
insert into a6_info value(1, 'a'),(2, 'b');

现在 a6_info 表 id 列变为普通索引（非唯一性索引）：

mysql> explain select * from a6 join a6_info using(id) where a6.id=1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | a6 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | a6_info | NULL | ref | id | id | 5 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

a6_info 表 type 变为 ref 类型了。

所以，唯一性索引才会出现 eq_ref （非唯一性索引会出现 ref ），因为唯一，所以最多只返回一条记录，找到后无需继续查找，因此比 ref 更快。

const
被称为“常量”，这个词不好理解，不过出现 const 的话就表示发生下面两种情况：
在整个查询过程中这个表最多只会有一条匹配的行，比如主键 id=1 就肯定只有一行，只需读取一次表数据便能取得所需的结果，且表数据在分解执行计划时读取。
返回值直接放在 select 语句中，类似 select 1 AS f 。可以通过 extended 选择查看内部过程：
建表及插入数据：
create table a7(id int primary key, c1 char(20) not null, c2 text not null, c3 text not null);
insert into a7 values(1, 'asdfasdf', 'asdfasdf', 'asdfasdf'), (2, 'asdfasdf', 'asdfasdf', 'asdfasdf');

用 show warnings 查看 MySQL 是如何优化的：
mysql> show warnings\G
Message: select '1' AS `id`,'asdfasdf' AS `c1`,'asdfasdf' AS `c2`,'asdfasdf' AS
`c3` from `test`.`a` where 1

查询返回的结果为：
mysql> select * from a7 where id=1;
+----+----------+----------+----------+
| id | c1 | c2 | c3 |
+----+----------+----------+----------+
| 1 | asdfasdf | asdfasdf | asdfasdf |
+----+----------+----------+----------+

可以看出，返回结果中的字段值都以“值 AS 字段名”的形式直接出现在优化后的 select 语句中。

重新建表及插入数据：
create table a8 (id int not null);
insert into a8 value(1),(2),(3);

system
system 是 const 类型的特例，当表只有一行时就会出现 system 。

建表及插入数据：
create table a9(id int primary key);
insert into a9 value(1);

2.5、possible_keys 列
可能被用到的索引

2.8、ref 列
指出对 key 列所选择的索引的查找方式，常见的值有 const, func, NULL, 具体字段名。当 key 列为 NULL ，即不使用索引时，此值也相应的为 NULL 。

再建表及插入数据：
create table a12(id int primary key, a_name int not null);
create table b12(id int primary key, b_name int not null);
insert into a12 value(1, 1),(2, 2),(3, 3);
insert into b12 value(1, 111),(2, 222),(3, 333);

mysql> explain select * from a12 join b12 using(id);
...+-------+--------+...+---------+...+-----------+...
...| table | type |...| key |...| ref |...
...+-------+--------+...+---------+...+-----------+...
...| a | ALL |...| NULL |...| NULL |...
...| b | eq_ref |...| PRIMARY |...| test.a.id |...
...+-------+--------+...+---------+...+-----------+...

2.9、rows 列
MySQL 估计的需要扫描的行数。只是一个估计。

2.10、Extra 列
显示上述信息之外的其它信息，但却很重要。
Using index
此查询使用了覆盖索引(Covering Index)，即通过索引就能返回结果，无需访问表。
若没显示"Using index"表示读取了表数据。

建表：create table a15(id int auto_increment primary key, age int, name char(10));
插入 100w 条数据：insert into a15 value(null, rand()*100000000, 'jack');

Using where
表示 MySQL 服务器从存储引擎收到行后再进行“后过滤”（Post-filter）。所谓“后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。因为检查是在读取行后才进行的，所以称为“后过滤”。

建表及插入数据：
create table a16 (num_a int not null, num_b int not null, key(num_a));
insert into a16 value(1,1),(1,2),(2,1),(2,2);
mysql> explain select * from a16 where num_a=1;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | a16 | NULL | ref | num_a | num_a | 4 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

虽然查询中有 where 子句，但只有 num_a=1 一个条件，且 num_a 列存在索引，通过索引便能确定返回的行，无需进行“后过滤”。
所以，并非带 WHERE 子句就会显示"Using where"的。
mysql> explain select * from a16 where num_a=1 and num_b=1;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | a16 | NULL | ref | num_a | num_a | 4 | const | 2 | 25.00 | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+

此查询增加了条件 num_b=1 ，此列没有索引，但可以看到查询同样能使用 num_a 索引。 MySQL 先通过索引 num_a 找到 num_a=1 的行，然后读取整行数据，再检查 num_b 是否等于 1 ，执行过程看上去象这样：

num_a索引|num_b 没有索引，属于行数据
+-------+-------+
| num_a | num_b | where 子句(num_b=1)
+-------+-------+
| 1 | 1 | 符合
| 1 | 2 | 不符合
| ... | ... | ...
+-------+-------+

在《高性能 MySQL 》（第二版）P144(pdf.167) 页有更形象的说明图片（图 4-5 MySQL 通过整表扫描查找数据）。

字段是否允许 NULL 对 Using where 的影响：

建表及插入数据：
create table a17 (num_a int null, num_b int null, key(num_a));
insert into a17 value(1,1),(1,2),(2,1),(2,2);

这次 num_a, num_b 字段允许为空。

在上例 num_a not null 时， num_a 索引的长度 key_len 为 4 ，当 num_a null 时， num_a 索引的长度变为了 5 ：

mysql> explain select * from a17 where num_a=3;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | a17 | NULL | ref | num_a | num_a | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

Using filesort
若查询所需的排序与使用的索引的排序一致，因为索引是已排序的，因此按索引的顺序读取结果返回，否则，在取得结果后，还需要按查询所需的顺序对结果进行排序，这时就会出现 Using filesort。

现在增加索引：create index a_id on a19(a_id);
把表 a 的记录增加到约 100w(1048576) 条， a_id 与 b_id 都是随机生成的数字：

mysql> select * from a order by rand() limit 10;
+-------+--------+
| a_id | b_id |
+-------+--------+
| 61566 | 961297 |
| 33951 | 680542 |
| ..... | ...... |
+-------+--------+
mysql> explain select * from a19 order by rand() limit 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | a19 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
同样是 Using filesort ，type 为 ALL ，全表扫描。听说“取全表数据根据ID排序，走索引一定不如直接查，因为可以减少因为需要索引改变数据访问顺序造成随机IO的概率，数据库放弃索引是应该的”，参考：
http://isky000.com/database/mysql_order_by_implement#comment-2981

当 type 为 rang、 ref 或者 index 的时候才有可能利用索引排序，其它，如 ALL ，都无法通过索引排序，此时若有 order by ，如上例，便会出现 Using filesort 。

现在增加 where 子句：
mysql> explain select * from a19 where a_id=10 order by a_id;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | a19 | NULL | ref | a_id | a_id | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
查询走了索引 a_id ，此时 type 为 ref ，直接按索引顺序返回，没有 Using filesort 。
修改 where 子句：
mysql> explain select * from a19 where a_id>10 and a_id<100 order by a_id;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | a19 | NULL | range | a_id | a_id | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
同样利用索引排序，没有 Using filesort 。

再修改 where 子句：
mysql> explain select * from a19 where a_id >1 order by a_id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | a19 | NULL | ALL | a_id | NULL | NULL | NULL | 5 | 60.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+

又出现 Using filesort 且 type 变为 ALL 。注意以上例子的 rows 列，此列表示 MySQL 估计查询需要读取的行数，分别为 1048576, 8, 712, 1048576 ，特别注意最后两个数字： 712, 1048576 。

可见，当索引能为查询排除大部份行时（ a_id=10 时约读取 8 行，排除了大部份， a_id>10 and a_id<100 时约读取 712 行，同样排除了大部份）便使用索引，否则，如 a_id>10 时约读取 1048576 ， MySQL 直接改用全表扫描，再 Using filesort 。也就是说， MySQL 会根据表中的信息及查询来决定使用任种方式。

关于 MySQL 读取数据表的方式，可参考（暂缺参考资料），就会明白为什么需读取 1048576 行时，先读索引再读表数据还不如全表扫描了。

对于多字段排序(order by a, b)及带 group by 的查询，可参考 MySQL 帮助手册 7.2.12. MySQL如何优化ORDER BY 。

参考资料：

MySQL 帮助手册
7.2.1. EXPLAIN语法（获取SELECT相关信息）
7.2.12. MySQL如何优化ORDER BY

《高性能 MySQL 》（第二版）中文版
P463(pdf.487) 附录 B

《 MySQL 性能调优与架构设计》

MySQL ORDER BY 的实现分析
http://isky000.com/database/mysql_order_by_implement

Mysql执行计划
http://www.xifenfei.com/954.html

MySQL执行计划解读
http://wenku.baidu.com/view/41846439376baf1ffc4fad4f.html

Mysql Explain 详解
http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html

MYSQL EXPLAIN语句的extended 选项学习体会
http://hi.baidu.com/dearhwj/blog/item/03badf17641a28094a90a78d.html

Show Warnings

MySQL索引类型一览让MySQL高效运行起来
http://database.51cto.com/art/200910/156685.htm

Mysql执行计划中的Using filesort
"指出Mysql可以使用哪个索引在该表中找到行"
http://www.taobaodba.com/html/235_mysql_using_filesort.html

2.order by b，如果b列不在索引中，不管b值是否相同，总会出现Using filesort。

What does Using filesort mean in MySQL?
"任何不能通过index进行的sort都称之为filesort"
http://iceskysl.1sters.com/?p=638

mysql explain中的using filesort
"只能是如果可能的话修改查询的排序条件"
http://blog.csdn.net/wdwbw/article/details/5256064

Mysql之EXPLAIN显示using filesort
"结果昨天看到公司的一个"
http://www.ccvita.com/169.html（可能这个是出处）

多列索引在建立的时候是以B-树结构建立的，因此建立索引的时候是先建立ID的按顺序排的索引，在相同ID的情况下建立FID按顺序排的索引，最后在FID 相同的情况下建立按INVERSE_DATE顺序排的索引，如果列数更多以此类推。

要在优化一下这个sql就应该为它建立另一个索引IDX（ID,INVERSE_DATE），这样就消除了using filesort速度也会快很多。

mysql优化Using filesort
"并且用到了room_number这列索引"
http://www.askwan.com/post/151/

MySQL 索引优化 Using filesort
"页面咔的一下就出来了"
http://www.leakon.com/archives/332

一直总是想通过奇技淫巧来解决负载，数据库压力大的问题，缺忽略了最基本的mysql的调制整理，现在开始尝试压榨mysql的性能。
第一步，你的索引加对了吗？

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 就是 const 表了：
select * from tbl_name where primary_key=1;
select * from tbl_name where 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 where ref_table.key_column=other_table.column;
select * fromref_table,other_table where ref_table.key_column_part1=other_table.column and ref_table.key_column_part2=1;
ref: 该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该键不是 primary key 或 unique索引（换句话说，就是连接程序无法根据键值只取得一条记录）的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。 ref还可以用于检索字段使用 =操作符来比较的时候。以下的几个例子中，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;
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 * 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: 连接类型跟 all 一样，不同的是它只扫描索引树。它通常会比 all快点，因为索引文件通常比数据文件小。mysql在查询的字段知识单独的索引的一部分的情况下使用这种连接类型。
all: 将对该表做全部扫描以和从前一个表中取得的记录作联合。这时候如果第一个表没有被标识为const的话就不大好了，在其他情况下通常是非常糟糕的。正常地，可以通过增加索引使得能从表中更快的取得记录以避免all。

possible_keys
possible_keys字段是指 mysql在搜索表记录时可能使用哪个索引。注意，这个字段完全独立于explain 显示的表顺序。这就意味着 possible_keys里面所包含的索引可能在实际的使用中没用到。如果这个字段的值是null，就表示没有索引被用到。这种情况下，就可以检查 where子句中哪些字段那些字段适合增加索引以提高查询的性能。就这样，创建一下索引，然后再用explain 检查一下。想看表都有什么索引，可以通过 show index from tbl_name来看。

key
key字段显示了mysql实际上要用的索引。当没有任何索引被用到的时候，这个字段的值就是null。想要让mysql强行使用或者忽略在 possible_keys字段中的索引列表，可以在查询语句中使用关键字force index, use index,或 ignore index。如果是 myisam 和 bdb 类型表，可以使用 analyzetable 来帮助分析使用使用哪个索引更好。如果是 myisam类型表，运行命令 myisamchk --analyze也是一样的效果。

key_len
key_len 字段显示了mysql使用索引的长度。当 key 字段的值为 null时，索引的长度就是 null。注意，key_len的值可以告诉你在联合索引中mysql会真正使用了哪些索引。

ref
ref 字段显示了哪些字段或者常量被用来和 key配合从表中查询记录出来。

rows
rows 字段显示了mysql认为在查询中应该检索的记录数。

extra
本字段显示了查询中mysql的附加信息。以下是这个字段的几个不同值的解释：
distinct: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条件的记录的要排序的键和指向记录的指针存储起来。这些键已经排完序了，对应的记录也会按照排好的顺序取出来。

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.currentdppers tt.recordvolume, tt.dpprinted, et.country,et_1.country, do.custname from tt, et, et as et_1, do
where tt.submittime is null and tt.actualpc = et.employid and
tt.assignedpc = et_1.employid 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 = et_1.employid 和 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 15 et.employid 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分句中用不同的次序列出各个表。

[原创]MYSQL的WARNINGS 和 ERRORS查询细节 2007-11-02 10:50:36
分类： Mysql/postgreSQL
1、SHOW ERRORS 语句只是显示上一个语句的错误，不同时显示警告以及注意事项。
举个例子：

mysql> show dfdafsadf
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'dfdaf
sadf' at line 1

这里就有个错误。关于如何显示她，已经很明显了。

mysql> show errors
-> \G
*************************** 1. row ***************************
Level: Error
Code: 1064
Message: You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'dfdafsadf' at li
ne 1
1 row in set (0.00 sec)

如果一下子有好多错误，而你又想只显示第二条的话：
show errorw limit 1,1;
如果你想看到有错误的数目，前面的 1 rows in set 已经很明显了。
不过还有办法：
mysql> show count(*) errors;
+-----------------------+
| @@session.error_count |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
注意：这里的count(*)不能写成count(1).
你还可以这样：
mysql> select @@error_count;
+---------------+
| @@error_count |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)

2、SHOW WARNINGS 显示上一个语句的错误、警告以及注意。
基本语法和SHOW ERRORS大同小异。
不过要注意的是在MYSQL5后的大部分以前的WARNINGS直接被显示为ERRORS。

巴特西

Explain 结果解读与实践

最新文章

热门文章