mysql优化之二

　　7.2.1 EXPLAIN 语法（得到SELECT 的相关信息）

　　EXPLAIN tbl_name

　　或者:

　　EXPLAIN SELECT select_options

　　EXPLAIN 语句可以被当作 DESCRIBE 的同义词来用，也可以用来获取一个MySQL要执行的 SELECT 语句的相关信息。

　　* EXPLAIN tbl_name 语法和 DESCRIBE tbl_name 或 SHOW COLUMNS FROM tbl_name 一样。

　　* 当在一个 SELECT 语句前使用关键字 EXPLAIN 时，MYSQL会解释了即将如何运行该 SELECT 语句，它显示了表如何连接、连接的顺序等信息。

　　本章节主要讲述了第二种 EXPLAIN 用法。在 EXPLAIN 的帮助下，您就知道什么时候该给表添加索引，以使用索引来查找记录从而让 SELECT 运行更快。如果由于不恰当使用索引而引起一些问题的话，可以运行 ANALYZE TABLE 来更新该表的统计信息，例如键的基数，它能帮您在优化方面做出更好的选择。详情请看"14.5.2.1 ANALYZE TABLE Syntax"。您还可以查看优化程序是否以最佳的顺序来连接数据表。为了让优化程序按照 SELECT 语句中的表名的顺序做连接，可以在查询的开始使用 SELECT STRAIGHT_JOIN 而不只是 SELECT。 EXPLAIN 返回了一行记录，它包括了 SELECT 语句中用到的各个表的信息。这些表在结果中按照MySQL即将执行的查询中读取的顺序列出来。MySQL用一次扫描多次连接（single-sweep, multi-join）的方法来解决连接。这意味着MySQL从第一个表中读取一条记录，然后在第二个表中查找到对应的记录，然后在第三个表中查找，依次类推。当所有的表都扫描完了，它输出选择的字段并且回溯所有的表，直到找不到为止，因为有的表中可能有多条匹配的记录下一条记录将从该表读取，再从下一个表开始继续处理。在MySQL version 4.1中，EXPLAIN 输出的结果格式改变了，使得它更适合例如 UNION 语句、子查询以及派生表的结构。更令人注意的是，它新增了2个字段： id 和 select_type。当你使用早于MySQL 4.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 就是 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 * FROM ref_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 值的记录。这种连接类型的优化是从MySQL 4.1.1开始的，它经常用于子查询。在以下的例子中，MySQL使用 ref_or_null 类型来处理 ref_table：

　　SELECT * FROM ref_table

　　WHERE key_column=expr OR key_column IS NULL;

　　详情请看"7.2.6 How MySQL Optimizes IS NULL"。

　　index_merge

　　这种连接类型意味着使用了 Index Merge 优化方法。这种情况下，key字段包括了所有使用的索引，key_len 包括了使用的键的最长部分。详情请看"7.2.5 How MySQL Optimizes OR Clauses"。

　　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 检查一下。详细的查看章节"14.2.2 ALTER TABLE Syntax"。想看表都有什么索引，可以通过 SHOW INDEX FROM tbl_name 来看。

　　key

　　key 字段显示了MySQL实际上要用的索引。当没有任何索引被用到的时候，这个字段的值就是 NULL。想要让MySQL强行使用或者忽略在 possible_keys 字段中的索引列表，可以在查询语句中使用关键字FORCE INDEX, USE INDEX, 或 IGNORE INDEX。如果是 MyISAM 和 BDB 类型表，可以使用 ANALYZE TABLE 来帮助分析使用使用哪个索引更好。如果是 MyISAM 类型表，运行命令 myisamchk --易做图yze 也是一样的效果。详细的可以查看章节"14.5.2.1 ANALYZE TABLE Syntax"和"5.7.2 Table Maintenance and Crash Recovery"。

　　key_len

　　key_len 字段显示了My