12.3.3　用EXPLAIN理解查询操作的工作过程

有两种方式可以调用EXPLAIN语句。第一种，可以使用：

EXPLAIN table;

上述语句的输出结果非常类似于DESCRIBE table或SHOW COLUMNS FROM table语句的输出结果。

第二种，也是更有趣的方式，可以使用EXPLAIN语句来查看MySQL是如何来解释并执行一个SELECT查询。要使用这种方式，只要在SELECT语句前面加上单词EXPLAIN即可。

当试图使一个复杂的查询能够正常工作起来而查询语句写得不是很正确的时候，或者当一个查询的执行时间大大超出它应该需要的时间时，可以使用EXPLAIN语句。如果编写一个复杂的查询，可以在实际运行查询之前执行EXPLAIN命令以提前检查它，根据该语句的结果，如果有必要的话，可以修改SQL语句，从而对其进行优化。它也是方便的学习工具。

例如，尝试运行下列基于Book-O-Rama数据库的查询。

explain

select customers.name

from customers,orders,order_items,books

where customers.customerid=orders.customerid

and orders.orderid=order_items.orderid

and order_items.isbn=books.isbn

and books.title like'%Java%';

其运行结果如下所示（请注意，我们是以垂直的模式显示结果的，因为表行的宽度太大，无法在本书显示出来。可以通过在查询语句末尾处添加\G选项来实现）：

*1.row*

id:1

select_type:SIMPLE

table:orders

type:ALL

possible_keys:PRIMARY

key:NULL

key_len:NULL

ref:NULL

rows:4

Extra:

*2.row*

id:1

select_type:SIMPLE

table:order_items

type:ref

possible_keys:PRIMARY

key:PRIMARY

key_len:4

ref:books.orders.orderid

rows:1

Extra:Using index

*3.row*

id:1

select_type:SIMPLE

table:customers

type:ALL

possible_keys:PRIMARY

key:NULL

key_len:NULL

ref:NULL

rows:3

Extra:Using where;Using join buffer

*4.row*

id:1

select_type:SIMPLE

table:books

type:eq_ref

possible_keys:PRIMARY

key:PRIMARY

key_len:13

ref:books.order_items.isbn

rows:1

Extra:Using where

初看起来，这个输出结果有点乱，但是它却很有意义。下面，我们将逐个讨论该表中的列。

第一列id，给出的是该行所引用的查询SELECT语句的ID号。

select_type列，解释了所使用的查询类型。该列的值如表12-8所示。

table列只是列出了用来完成查询所需的表。结果中的每一行将给出特定表在查询中如何使用的详细信息。在这个例子中，可以看到，所使用的表包括orders、order_items、customers和books。（通过查看这个查询就可以知道）。

type列解释了表在查询的关联中是如何使用的。该列的可能值如表12-9所示。这些值是按照由快至慢的查询速度排列。通过这个表，可以知道执行一个查询需要读入一个表的多少行。

在前面的例子中，可以发现有两个表是通过eq_ref(orders和customers)来关联的，而其中一个表是使用index(order_items)来关联的，但其他一个表（books）是使用ALL；也就是说，通过查看表中每一行来连接的。

rows列列出（粗略地）执行连接所必须扫描的每个表的行数。这样，可以将这些行数相乘而得出查询执行时所检查的行的总数。我们将这些数字相乘是因为一个连接就像不同表中行的乘积。请参阅第10章获得关于连接的详细信息。请记住，行数是所查看的行数，而不是返回的行数，并且它只是一个估计——没有真正执行查询，MySQL不可能知道确切的数量。

很显然，这个数字越小越好。现在，我们的数据库中只有几乎可以忽略的数据量，但是当数据的容量开始增长的时候，该查询将耗费一定的执行时间。稍后，我们还将回过来讨论它。

与你理解的一样，possible_keys列列出MySQL可能用来连接表的关键字。在这个例子中，可以看出可能的关键字都是PRIMARY关键字。

key列，或者是MySQL实际使用的表中的关键字，或者为NULL，如果没有使用关键字的话。注意对books表尽管可以使用PRIMARY关键字，但是本查询并没有使用它们。

key_len列给出了所用关键字的长度。可以使用它来判别是否只使用了关键字的一部分。当我们使用包含多列的关键字时，这是有意义的。在这个例子中，需要使用关键字的地方，我们都使用了完整关键字。

ref列显示的是用来从表中选择列而必须与关键字一起使用的列。

最后，extra列告诉我们关于连接是如何执行的，以及其他所有信息。在该列中，可能出现的值如表12-10所示。

可以用多种方法解决在EXPLAIN的结果中出现的问题。首先，可以检查列类型并且确认它们相同。这特别适用于列宽度。如果它们的列宽度不同，索引不能用于匹配这些列。

可以通过修改列类型以使其匹配，或者在设计的开始就建立类型匹配的列，从而解决这个问题。

其次，可以让关联优化器来检查关键字的分布，因此使用myisamchk或ANALYZE TABLE语句对关联进行优化，使得它效率更高。可以使用如下命令调用它：

myisamchk—analyze pathtomysqldatabase/table

可以通过在命令行上列出所有连接，对多个表进行检查，或者使用：

myisamchk—analyze pathtomysqldatabase/*.MYI

可以运行下列命令检查所有数据库的所有表：

myisamchk—analyze pathtomysqldatadirectory//.MYI

或者，可以在MySQL监视程序中运行ANALYZE TABLE语句来列出所有表：

analyze table customers,orders,order_items,books;

除此之外，可能还要考虑对该表添加一个新索引。如果该查询是a）慢和b）一般，就应该认真考虑这个问题。如果它是一个使用一次就不再使用的查询，例如请求一次的模糊报告，它将不值得这样的努力，因为它将使其他东西慢下来。我们将在下一节讨论如何解决它。

如果EXPLAIN结果possible_keys列包含一些NULL值，可能需要对正在被讨论的表添加一个索引来提高查询性能。如果在WHERE子句中使用的列适合作为索引，可以使用ALTER TABLE语句为它创建一个新索引，例如：

ALTER TABLE表ADD INDEX(列);