Detailed explanation of MySQL execution plan

The EXPLAIN statement provides information about how MySQL executes a statement. EXPLAIN is used with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

EXPLAIN returns one row for each table used in the SELECT statement. It lists the tables in the output in the order that MySQL reads them while processing the statement. MySQL resolves all joins using the nested loop join method. This means that MySQL reads a row from the first table, then finds a matching row in the second table, the third table, and so on. After processing all tables, MySQL outputs the selected columns and backtracks through the list of tables until it finds a table with more matching rows. Read the next row from this table and continue processing the next table.

1. EXPLAIN Output Columns

Here are a few key columns:

• type : connection type
• possible_keys : optional indexes
• key: the index used during actual execution
• ref : The ref column shows which column or constant is compared to the named index shown in the previous key column to select rows from the table.
• rows: The rows column indicates the number of rows that MySQL believes must be examined to execute the query.
  

2. Connection Type

The connection types, in order from best to worst, are:

system

The table has only one row. This is a special case of the const join type.

const

The table has at most one matching row, which is read at the start of the query. Because there is only one row, the values ​​of the columns in this row can be treated as constants by the rest of the optimizer. Const tables are very fast because they are only read once.

const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to a constant value.

For example, the following table tbl_name can be treated as a const table:

SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref

For each combination of rows in the previous table, read a row from this table. Apart from the system and const types, this is the best possible join type. Use it when all parts of an index are used by joins and the index is a PRIMARY KEY or UNIQUE NOT NULL index.

eq_ref can be used with indexed columns that are compared using the = operator. The comparison value can be a constant or an expression using columns from a table that is read before this one.

For example, in the following example, MySQL can use eq_ref join to handle 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

For each combination of rows from the previous table, all rows with matching index values ​​are read from this table. If the join uses only a leftmost prefix of key, or if key is not a PRIMARY KEY or a UNIQUE index (in other words, if the join cannot select a single row based on the key value), then ref is used. This is a good type of join if the key used only matches a few rows.

ref can be used on indexed columns that are compared using the = or <=> operators.

For example, in the following example, MySQL can use ref connections to handle 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;

fulltext

Performing a join using a FULLTEXT index

ref_or_null

This join type is similar to ref, but MySQL additionally searches for rows that contain NULL values. This join type optimization is most often used to resolve subqueries.

For example, in the following example, MYSQL can use ref_or_null to handle ref_table:

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

index_merge

This join type indicates that the Index Merge optimization is used. In this case, the key column in the output row contains the list of indices used, and key_len contains the longest list of key parts of the indices used.

unique_subquery

This type replaces eq_ref with some IN subquery of the form:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery

Similar to unique_subquery, it replaces an IN subquery, but works with non-unique indexes in subqueries of the following form:

value IN (SELECT key_column FROM single_table WHERE some_expr)

range

Retrieve only the rows in a given range, using the index to select the rows. The key column in the output row indicates which index was used. key_len contains the longest key part used. For this type, the ref column is NULL.

You can use range when you compare a key column to a constant using the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators:

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

The index join type is the same as all, the difference is that the index join type scans the index tree. Usually, it only happens in the following two situations:

• If the index is a covering index for the query and can be used to satisfy all the data required in the table, only the index tree is scanned. In this case, the Extra column displays Using index. An index-only scan is usually faster than an ALL scan because the size of the index is usually smaller than the table data.
• Perform a full table scan using Read Data from Index to find the data rows in index order. "Uses index" does not appear in the Extra column.

ALL

A full table scan is performed for each combination of rows from the previous table. This is generally bad if the table is the first table not marked const, and generally very bad in all other cases. Often, ALL can be avoided by adding indexes that allow rows to be retrieved from a table based on constant values ​​or column values ​​from earlier tables.

3. Extra Column

Regarding the output of the Extra column, here are a few common ones:

Using filesort

MySQL must do an extra operation to figure out how to retrieve the rows in sorted order. The sorting is done by iterating over all rows according to the join type and storing the sort key and a pointer to the row for all rows matching the WHERE clause. The keys are then sorted and the rows are retrieved in sorted order.

Using index

Column information is retrieved from the table using only the information in the index tree without performing additional seeks to read the actual row. This strategy can be used when the query uses only columns that belong to a single index.

Using temporary

To parse the query, MySQL needs to create a temporary table to hold the results. Typically, this happens if the query contains GROUP BY and ORDER BY clauses that present columns differently.

Using where

The WHERE clause is used to restrict which rows are matched to the next table or sent to the client. Unless you intend to fetch or examine all rows from a table, errors may occur in your query if the extra values ​​are not used with where and the table join type is all or index.

4. Optimize ORDER BY

In some cases, MySQL might use an index to satisfy the ORDER BY clause, thus avoiding the extra sorting involved in performing a filesort operation.

Assuming there is an index on (key_part1, key_part2), the following query can use the index to resolve the ORDER BY part. Whether the optimizer actually does this depends on whether reading the index is more efficient than a table scan if it must also read outside the index.

SELECT * FROM t1 ORDER BY key_part1, key_part2;

In the above statement, the query uses SELECT *, which may select more columns than key_part1 and key_part2. In this case, scanning the entire index and looking up the table rows for columns not included in the index may be more expensive than scanning the table and sorting the results. If so, the optimizer is less likely to use the index. If SELECT * selects only indexed columns, the index is used and a sort is avoided.

In the following query, key_part1 is constant, so all rows accessed through the index are in key_part2 order, and an index on (key_part1, key_part2) can avoid a sort if the WHERE clause is selective enough to make an index range scan cheaper than a table scan:

SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;

The above is the detailed content of the MySQL execution plan. For more information about the MySQL execution plan, please pay attention to other related articles on 123WORDPRESS.COM!