Parser and Optimizer
The MySQL server receives queries in the SQL format. Once a query is received, it first needs to be parsed, which involves translating it from what is essentially a textual format into a combination of internal binary structures that can be easily manipulated by the optimizer.
MySQL's optimizer has several important tasks:
. Determine which keys can be used to retrieve the records from tables, and choose the best one for each table.
. For each table, decide whether a table scan is better than reading on a key. If there are a lot of records that match the key value, the advantages of the key are reduced and the table scan becomes faster.
. Determine the order in which tables should be joined when more than one table is present in the query.
. Rewrite the WHERE clause to eliminate dead code, reducing the unnecessary computations and changing the constraints whenever possible to open the way for using keys.
. Eliminate unused tables from the join.
. Determine whether keys can be used for ORDER BY and GROUP BY.
. Attempt to replace an outer join with an inner join.
. Attempt to simplify subqueries, as well as determine to what extent their results can be cached.
. Merge views.
Using EXPLAIN to Understand the Optimizer
The MySQL EXPLAIN command tells the optimizer to show its query plan.
E.g,
EXPLAIN SELECT ... \G
Understanding the output of EXPLAIN
| EXPLAIN FIELD | DESCRIPTION | |
|
id |
QueryID. Meaningful only when subqueries are used.
|
|
|
select_type |
Indicates what happens with the result set retrieved from the table. A join not involving subqueries or UNION will have this value set to simple. See the upcoming section "Select types" for details. |
|
| table | The alias the table is referenced by in the query. If no alias is used, the real name of this table. | |
| type | The method used for retrieving the records from the table. See the upcoming section "Record access types" for details. | |
| possible_keys | A list of keys that can be used in conjunction with the WHERE clause to retrieve the records from this table. | |
| key | The name of the key used for retrieving the records. When index_merge optimization is used, contains a list of keys. | |
| key_len | The length of the key used in a query. This does not have to be the full length of the key - it is possible to use only a key prefix. | |
| ref | A list of fields from other tables whose values are involved in an index lookup in this table. | |
| rows | Average estimated number of records in this table to be retrieved on each join iteration. | |
| Extra | Additional comments on the optimization strategy. See the section "Extra field" for details. |
评论排行榜