看帮助文档吧7.2.2. EXPLAIN Output Format EXPLAIN returns a row of information for each table used in the SELECT statement. The tables are listed in the output in the order that MySQL would read them while processing the query. MySQL resolves all joins using a nested-loop join method. This means that MySQL reads a row from the first table, and then finds a matching row in the second table, the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table. When the EXTENDED keyword is used, EXPLAIN produces extra information that can be viewed by issuing a SHOW WARNINGS statement following the EXPLAIN statement. This information displays how the optimizer qualifies table and column names in the SELECT statement, what the SELECT looks like after the application of rewriting and optimization rules, and possibly other notes about the optimization process. EXPLAIN EXTENDED also displays the filtered column. Note You cannot use the EXTENDED and PARTITIONS keywords together in the same EXPLAIN statement. Each output row from EXPLAIN provides information about one table, and each row contains the following columns: id The SELECT identifier. This is the sequential number of the SELECT within the query. select_type The type of SELECT, which can be any of those shown in the following table. SIMPLE Simple SELECT (not using UNION or subqueries) PRIMARY Outermost SELECT UNION Second or later SELECT statement in a UNION DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query UNION RESULT Result of a UNION. SUBQUERY First SELECT in subquery DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query DERIVED Derived table SELECT (subquery in FROM clause) UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) DEPENDENT typically signifies the use of a correlated subquery. See Section 12.2.10.7, “Correlated Subqueries”. DEPENDENT SUBQUERY evaluation differs from UNCACHEABLE SUBQUERY evaluation. For DEPENDENT SUBQUERY, the subquery is re-evaluated only once for each set of different values of the variables from its outer context. For UNCACHEABLE SUBQUERY, the subquery is re-evaluated for each row of the outer context. Cacheability of subqueries is subject to the restrictions detailed in Section 7.7.5.1, “How the Query Cache Operates”. For example, referring to user variables makes a subquery uncacheable. table The table to which the row of output refers. MySQL+5.5+Reference+Manual.chm
http://www.blogjava.net/persister/archive/2008/10/27/236813.html
EXPLAIN returns a row of information for each table used in the SELECT statement. The tables are listed in the output in the order that MySQL would read them while processing the query. MySQL resolves all joins using a nested-loop join method. This means that MySQL reads a row from the first table, and then finds a matching row in the second table, the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table. When the EXTENDED keyword is used, EXPLAIN produces extra information that can be viewed by issuing a SHOW WARNINGS statement following the EXPLAIN statement. This information displays how the optimizer qualifies table and column names in the SELECT statement, what the SELECT looks like after the application of rewriting and optimization rules, and possibly other notes about the optimization process. EXPLAIN EXTENDED also displays the filtered column. Note
You cannot use the EXTENDED and PARTITIONS keywords together in the same EXPLAIN statement. Each output row from EXPLAIN provides information about one table, and each row contains the following columns: id The SELECT identifier. This is the sequential number of the SELECT within the query. select_type The type of SELECT, which can be any of those shown in the following table. SIMPLE Simple SELECT (not using UNION or subqueries)
PRIMARY Outermost SELECT
UNION Second or later SELECT statement in a UNION
DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT Result of a UNION.
SUBQUERY First SELECT in subquery
DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query
DERIVED Derived table SELECT (subquery in FROM clause)
UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) DEPENDENT typically signifies the use of a correlated subquery. See Section 12.2.10.7, “Correlated Subqueries”. DEPENDENT SUBQUERY evaluation differs from UNCACHEABLE SUBQUERY evaluation. For DEPENDENT SUBQUERY, the subquery is re-evaluated only once for each set of different values of the variables from its outer context. For UNCACHEABLE SUBQUERY, the subquery is re-evaluated for each row of the outer context. Cacheability of subqueries is subject to the restrictions detailed in Section 7.7.5.1, “How the Query Cache Operates”. For example, referring to user variables makes a subquery uncacheable. table The table to which the row of output refers. MySQL+5.5+Reference+Manual.chm