Explanation of explain in MySQL

EXPLAIN is the main way to see how the MySQL optimizer decides to execute a query, a feature that has limitations, that it will always tell the truth, but that it can get the best information.

Learn to explain EXPLAIN, and you'll understand how the MySQL optimizer works so you can optimize MySQL.

how to invoke?

Just add explain to the select front.

The end of the statement (; before) plus \g can be viewed more clearly.

Need to say is explain only to select query to explain, Insert,update,delete not oh.

columns in the explain

• The ID column identifies the row number that the select belongs to, and if there is no subquery or union in the statement, it indicates that there is only one select, and the column is displayed as 1, otherwise the inner select is numbered sequentially.

MySQL divides select queries into simple and complex types, and complex types can be divided into: simple subqueries, so-called derived tables (subqueries in the FROM clause), union queries.
Simple subquery: EXPLAIN Select (SELECT ' uid ' from ' tips ' limit 1) from ' Test_key ' WHERE 1
The so-called derived table (subquery in the FROM clause) EXPLAIN the Select UID from (select UID from user) as Der.

• Select_type column: Shows whether the corresponding or simple or complex select (in the latter case, will be one of the three complex types).

Simple means that the query does not contain subqueries and unions. If the query contains a subquery or union, the outermost select is marked as primary (that is, the ID column is 1)
Other Tags:
subquery, a subquery contained in the select list (not in the FROM clause) is marked for this;
DERIVED, the subquery in the FROM clause is marked for this;
Union, the second and subsequent select in the Union are marked for this; Explain select 1 UNION all select 1
Union result, which is used to retrieve the result from the Union's temporary table, with the select Mark as union result, such as explain select 1 UNION all select 1

• Table column: Shows which table the corresponding row is accessing

When there is a subquery or union in the FROM clause, the table column is <derivedn>, where n is the value corresponding to the ID column

• Type column: MySQL determines how to find rows in a table (from worst to best)

all, full table scan
index, as with the full table scan, knowledge mysql is indexed sequentially instead of rows when scanning tables
Range , scope scan, a restricted index scan, which starts at some point in the index and returns rows that match that range (the obvious scope scan.) is a query with between or a > in the WHERE clause, when MySQL uses the index to find a series of values, such as in () and or lists
, also scanned for the displayed range)
ref, an index-access also called an index lookup, returns all rows matching a single value, and it may find more than one qualifying row (EXPLAIN SELECT tipname from ' tips ' WHERE uid=10984)
eq_ref, an index lookup, returns only one row that matches a condition. This will be seen when using a primary key or a uniqueness index. (EXPLAIN SELECT * from ' tips ' WHERE uid=12)
Const and system, when MySQL can optimize a part of a query and convert it to a constant (EXPLAIN SELECT * from ' tips ' WHERE id=5)
NULL, this means that MySQL can decompose query statements during the optimization phase, without having to access tables or indexes at execution stage (EXPLAIN SELECT max (id), min (id) from ' tips ')

• Possible_keys column: This column shows which indexes the query can use, based on the columns that the query accesses and the comparison operators used.
• Key column: This column shows what index MySQL decides to use to optimize access to the table
• Key_len column: Shows the number of bytes that MySQL uses in the index. For example, the primary key is used in the query, and the primary key has an int, or 4,smallint is 2.
• Ref columns: Displays the columns or constants used by the previous table to query values in the index of the key column record.
• Row column: Shows the number of rows that MySQL will read in order to find the desired value.
• Extra column: This shows additional information that is not suitable for display in other columns

Using Index,mysql will use the overwrite index to avoid accessing the table (that is, simply using the information in the index without reading the table)
The Using where means that the MySQL server will filter after the storage engine retrieves rows (the records returned by the storage engine will be filtered through the Where condition)
Using temporary means that MySQL uses a temporary table when sorting the results of the query.
Using filesort means that MySQL uses an external index to sort the results, rather than reading them from the table in indexed order.
Rangechecked for each record (indexmap:n) means that there is no good index, the new index will be recalculated on each row of the join, N represents the bitmap of the index in the Possible_keys column, and is redundant.

Explanation of explain in MySQL