Explanation of EXPLAIN in MySQL _ MySQL

EXPLAIN is the main method for viewing how the MySQL Optimizer determines how to execute a query. this function has limitations, so that it always tells the truth, but can obtain the best information. after learning to EXPLAIN, you will understand how the MySQL Optimizer works and how you can optimize MySQL. how to call EXPLAIN is the main method to check how the MySQL Optimizer decides to execute the query. this function has limitations, so that it always tells the truth, but can obtain the best information.

After learning to EXPLAIN, you will understand how the MySQL Optimizer works and how you can optimize MySQL.

How to call it?

You only need to add the EXPLAIN command before the SELECT statement.

Add \ G at the end of the statement (before;) to make it clearer.

It must be said that the EXPLAIN statement only explains the SELECT query. INSERT, UPDATE, and DELETE statements are not applicable.

Columns in EXPLAIN

Id column: id of the row to which the SELECT belongs. if there is no subquery or union in the statement, there is only one SELECT, so this column is displayed as 1; otherwise, the SELECT in the inner layer is sequentially numbered.

MySQL divides SELECT queries into simple and complex types, including simple subqueries, derived tables (subqueries in the FROM clause), and UNION queries.

Simple subquery: explain select (SELECT 'uid' FROM 'tids' limit 1) FROM 'test _ key' WHERE 1

The so-called derived table (subquery in the FROM clause) explain select uid FROM (SELECT uid FROM user) as der.

Select_type column: it shows whether the corresponding columns are simple or complex SELECT (if the latter column is used, it will be one of the three complex types ).

SIMPLE means that the query does not contain the subquery and UNION. if the query contains the subquery or UNION, the SELECT in the outermost layer is marked as PRIMARY (that is, the id column is 1)

Other tags:

SUBQUERY: SUBQUERY (not included in the FROM clause) included in the SELECT list is marked as this;

DERIVED, the subquery in the FROM clause is marked as this;

UNION, the second and subsequent SELECT in the UNION are marked as this; for example, explain select 1 union all select 1

Union result, which is used to mark the SELECT of the RESULT retrieved from the temporary UNION table as union result, for example, explain select 1 union all select 1

Table column: displays the table to which the corresponding row is accessing.

When the FROM clause contains a subquery or UNION, the table column is Where N is the value corresponding to the id column

Type column: MySQL determines how to find the row in the table (from the worst to the best)

ALL, full table scan

Index, like full table scan, knowledge MySQL performs index scanning in order instead of row range, range scan, and a limited index scan, it starts from a point in the index and returns the rows that match the value range (the obvious range scan. that is, a query with between or> IN the WHERE clause. when MySQL uses an index to search for a series of values, such as IN () and OR lists, it also displays a range scan)

Ref. an index is also called an index query. it returns all rows that match a single value, it may find multiple qualified rows (explain select tipname FROM 'tids' WHERE uid = 10984)

Eq_ref is an index query. it returns at most one row that meets the condition. this will be seen when the primary key or unique index is used. (explain select * FROM 'tids' WHERE uid = 12)

Const and system. when MySQL can optimize a certain part of the query and convert it into a constant (explain select * FROM 'tids' WHERE id = 5)

NULL. this access method means that MySQL can break down query statements in the optimization phase, and does not need to access tables or indexes (explain select max (id), min (id) in the execution phase) FROM 'tids ')

Possible_keys column: This column shows which indexes can be used for queries. it is determined based on the columns accessed by the query and the comparison operators used.

Key column: This column shows which index MySQL determines to use to optimize access to the table.

Key_len column: displays the number of bytes used by MySQL in the index. for example, the primary key is used in the query, and the data type of the primary key is INT, then 4, and SMALLINT is 2.

Ref column: displays the columns or constants used by the previous table to query values in the index of the key column record.

Row column: displays the number of rows that MySQL reads to find the required value.

Extra column: displays additional information that is not suitable for other columns.

Using index, MySQL will use overwriting indexes to avoid table access (that is, only the information in the index is used instead of reading the table)

Using where means that the MySQL server will filter the records returned by the storage engine after the row is retrieved by the storage engine (the WHERE condition will be used to filter the records returned by the storage engine)

Using temporary means that MySQL uses a temporary table when sorting query results.

Using filesort means that MySQL uses an external index to sort the results, instead of reading the results from the table in the index order.

Rangechecked for each record (indexmap: N) means that there is no good index, and the new index will be re-estimated on each row of the join. N represents the bitmap of the index in the possible_keys column, and redundant.