一. EXPLAIN 语法
1. EXPLAIN tbl_name|SELECT select_options

EXPLAIN tbl_name和DESCRIBE tbl_name的作用是一样的,用于显示表结构等信息。

当我们在select语句前加上EXPLAIN后,Mysql将告诉我们它是如何处理select语句的,提供表之间的联结方式、使用索引等有关信息。

二. 测试环境简单介绍
为了节省创建表的时间,我用了joomla的文章表做测试,因为要演示优化过程,所以我事先删除了表里除主键之外的所有索引。
这里用到了三个表:
mysql> explain jos_content;
+------------------+------------------+------+-----+---------------------+----------------+
| Field            | Type             | Null | Key | Default             | Extra          |
+------------------+------------------+------+-----+---------------------+----------------+
| id               | int(11) unsigned | NO   | PRI | NULL                | auto_increment |
| title            | varchar(255)     | NO   |     |                     |                |
| alias            | varchar(255)     | NO   |     |                     |                |
| title_alias      | varchar(255)     | NO   |     |                     |                |
| introtext        | mediumtext       | NO   |     | NULL                |                |
| fulltext         | mediumtext       | NO   |     | NULL                |                |
| state            | tinyint(3)       | NO   |     | 0                   |                |
| sectionid        | int(11) unsigned | NO   |     | 0                   |                |
| mask             | int(11) unsigned | NO   |     | 0                   |                |
| catid            | int(11) unsigned | NO   |     | 0                   |                |
| created          | datetime         | NO   |     | 0000-00-00 00:00:00 |                |
| created_by       | int(11) unsigned | NO   |     | 0                   |                |
| created_by_alias | varchar(255)     | NO   |     |                     |                |
| modified         | datetime         | NO   |     | 0000-00-00 00:00:00 |                |
| modified_by      | int(11) unsigned | NO   |     | 0                   |                |
| checked_out      | int(11) unsigned | NO   |     | 0                   |                |
| checked_out_time | datetime         | NO   |     | 0000-00-00 00:00:00 |                |
| publish_up       | datetime         | NO   |     | 0000-00-00 00:00:00 |                |
| publish_down     | datetime         | NO   |     | 0000-00-00 00:00:00 |                |
| images           | text             | NO   |     | NULL                |                |
| urls             | text             | NO   |     | NULL                |                |
| attribs          | text             | NO   |     | NULL                |                |
| version          | int(11) unsigned | NO   |     | 1                   |                |
| parentid         | int(11) unsigned | NO   |     | 0                   |                |
| ordering         | int(11)          | NO   |     | 0                   |                |
| metakey          | text             | NO   |     | NULL                |                |
| metadesc         | text             | NO   |     | NULL                |                |
| access           | int(11) unsigned | NO   |     | 0                   |                |
| hits             | int(11) unsigned | NO   |     | 0                   |                |
| metadata         | text             | NO   |     | NULL                |                |
+------------------+------------------+------+-----+---------------------+----------------+
30 rows in set (0.00 sec)
 
mysql> select count(*) from jos_content;
+----------+
| count(*) |
+----------+
|    46585 |
+----------+
1 row in set (0.00 sec)
 
mysql> desc jos_categories;
+------------------+---------------------+------+-----+---------------------+----------------+
| Field            | Type                | Null | Key | Default             | Extra          |
+------------------+---------------------+------+-----+---------------------+----------------+
| id               | int(11)             | NO   | PRI | NULL                | auto_increment |
| parent_id        | int(11)             | NO   |     | 0                   |                |
| title            | varchar(255)        | NO   |     |                     |                |
| name             | varchar(255)        | NO   |     |                     |                |
| alias            | varchar(255)        | NO   |     |                     |                |
| image            | varchar(255)        | NO   |     |                     |                |
| section          | varchar(50)         | NO   |     |                     |                |
| image_position   | varchar(30)         | NO   |     |                     |                |
| description      | text                | NO   |     | NULL                |                |
| published        | tinyint(1)          | NO   |     | 0                   |                |
| checked_out      | int(11) unsigned    | NO   |     | 0                   |                |
| checked_out_time | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| editor           | varchar(50)         | YES  |     | NULL                |                |
| ordering         | int(11)             | NO   |     | 0                   |                |
| access           | tinyint(3) unsigned | NO   |     | 0                   |                |
| count            | int(11)             | NO   |     | 0                   |                |
| params           | text                | NO   |     | NULL                |                |
+------------------+---------------------+------+-----+---------------------+----------------+
17 rows in set (0.00 sec)
 
mysql> select count(*) from jos_categories;
+----------+
| count(*) |
+----------+
|       18 |
+----------+
1 row in set (0.00 sec)
 
mysql> desc jos_sections;
+------------------+---------------------+------+-----+---------------------+----------------+
| Field            | Type                | Null | Key | Default             | Extra          |
+------------------+---------------------+------+-----+---------------------+----------------+
| id               | int(11)             | NO   | PRI | NULL                | auto_increment |
| title            | varchar(255)        | NO   |     |                     |                |
| name             | varchar(255)        | NO   |     |                     |                |
| alias            | varchar(255)        | NO   |     |                     |                |
| image            | text                | NO   |     | NULL                |                |
| scope            | varchar(50)         | NO   |     |                     |                |
| image_position   | varchar(30)         | NO   |     |                     |                |
| description      | text                | NO   |     | NULL                |                |
| published        | tinyint(1)          | NO   |     | 0                   |                |
| checked_out      | int(11) unsigned    | NO   |     | 0                   |                |
| checked_out_time | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| ordering         | int(11)             | NO   |     | 0                   |                |
| access           | tinyint(3) unsigned | NO   |     | 0                   |                |
| count            | int(11)             | NO   |     | 0                   |                |
| params           | text                | NO   |     | NULL                |                |
+------------------+---------------------+------+-----+---------------------+----------------+
15 rows in set (0.00 sec)
 
mysql> select count(*) from jos_sections;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

简单说明:
jos_sections我们可以称它为大类表,jos_categories为小类表,jos_content为文章表,三个表的记录数分别为2、18、46585。

重要的字段基本上可以见名知义,只对几个联结字段说明一下:
jos_categories.secion: 这个字段是jos_section的主键id,它表明了大类和小类的父子关系,至于jos_categories.parent_id,我们可以先无视它的存在。
jos_content.sectionid: 这个字段也是jos_section的主键id,它表明了文章所属的大类。
jos_content.catid: 这个字段是jos_categories的主键id,它表明了文章所属的小类。

至于为什么会用两个表来存储类别,而不用parent_id的方式实现无限级分类,我们也不必深究,因为这个就是joomla的结构,我们只是用来测试EXPLAIN而已。

三. 测试过程
下面我们说明如何使用EXPLAIN查看执行过程并对表结构和sql语句进行优化。
在下面的测试过程中,我会用一些看来根本就没有意义的SQL语句,只是为了演示SQL语句结构和输出效果,其可用价值我们大可不必过于追究,在实际生产中还要以实际应用为准。
mysql> explain select * from jos_content where id=16;
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | jos_content | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)


因为使用了EXPLAIN关键字,所以Mysql为我们列出了一些查询信息,它包含以下列:

id: SELECT的识别符,这是SELECT的查询序列号。

select_type: SELECT类型,有以下几种不同的类型
    (1).SIMPLE: 简单的SELECT(不使用UNION或子查询)
    (2).PRIMARY: 最外面的SELECT,如果我们使用UNION或子查询,第一个查询将会是这个类型
    (3).UNION: 使用UNION查询时,除第一个语句外的所有语句会返回这个类型
    (4).DEPENDENT UNION: UNION中的第二个或后面的SELECT语句,取决于外面的查询。
    (5).UNION RESULT: UNION的结果。
    (6).SUBQUERY: 子查询中的第一个SELECT。
    (7).DEPENDENT SUBQUERY: 子查询中的第一个SELECT,取决于外面的查询。
    (8).DERIVED: 衍生表会返回这个类型。如:select * from (select * from jos_content) as A;。

table: 输出引用的表。

type: 联接类型,从这个选项我们可以初步判断查询效率,有以下几种不同的类型(按从最佳到最坏排序):
    (1).system: 表中仅有一行记录,这是const的一个特例。
    (2).const: 表中最多有一行符合查询条件,它在查询开始时被读取。因为只有一行,这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只被读取一次!(如上面的查询)
    (3).eq_ref: 对于每个来自于前面的表的行组合,从该表中读取一行。例如:select * from A,B where A.id=B.id,如果id在B表中是unique或primary key,会返回这个类型。它是说对于A表中的每一行,在B表中读取符合记录的一行。除了const之外,这是最好的联接类型。
    (4).ref: 这个类型跟eq_ref类似,不同的是eq_ref能根据unique或主键在后面的表中选择出唯一的行,而不能确定唯一行,则使用这个类型。
    (5).ref_or_null: 该联接类型如同ref,但是添加了MySQL 可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
    (6).index_merge: 索引合并方法用于通过range扫描搜索行并将结果合成一个。合并会产生并集、交集或者正在进行的扫描的交集的并集。在EXPLAIN输出中,该方法表现 为type列内的index_merge。在这种情况下,key列包含一列使用的索引,key_len包含这些索引的最长的关键元素。
    (7).unique_subquery: unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。explain select * from jos_content where id in (select id from jos_categories);会使用这个类型。
    (8).index_subquery: 该联接类型类似于unique_subquery。可以替换IN子查询,但只适合子查询中的非唯一索引。
    (9).range: 只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。 当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用这个类型。
    (10).index: 这与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
    (11).all: 对于每个来自于先前的表的行组合,将要做一个完整的表扫描。如果表格是第一个没标记const的表,效果不是很好,并且在所有的其他情况下很差。你可以通过增加更多的索引来避免ALL,使得行能从早先的表中基于常数值或列值被检索出来。
    
possible_keys: possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于EXPLAIN输出所示的表的次序。这意味着在 possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样,创造一个适当的索引并且再次用EXPLAIN检查查询。

key: key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索 引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。对于MyISAM 和BDB 表,运行ANALYZE TABLE 可以帮助优化器选择更好的索引。对于MyISAM 表,可以使用myisamchk --analyze。

key_len: 此列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。在不损失精确性的情况下,长度越短越好。

ref: 此列显示使用哪个列或常数与key一起从表中选择行。

rows: 此列显示了MySQL认为它执行查询时必须检查的行数。

Extra: 该列包含MySQL解决查询的详细信息。
    (1).Distinct: 一旦MYSQL找到了与行相联合匹配的行,就不再搜索了。
    (2).Not exists: MYSQL 优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了。
    (3).Range checked for each: Record(index map:#)没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一。
    (4).Using filesort: MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
    (5).Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候。
    (6).Using temporary: 看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上。
    (7).Using where: 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题。
    
上面的解释,不必全部看懂,有些可以一带而过,因为确实有些抽象,有些我也只是摘抄,但是下面我们用几个具体的实例来更加深入地理解一下。
下面的请移步下一篇
转自: http://blog.chinaunix.net/uid-510743-id-3191907.html