Mysql-EXPLAIN,

Mysql-EXPLAIN,
Select_type Column

This column shows whether the corresponding row is simple or complex SELECT (if complex SELECT is used, which of the three complex types is used ). The SIMPLE value means that the query does not include subqueries and UNION. If the query contains any complicated child parts, the outermost part is marked as PRIMARY, and the other part is marked as follows:

　　SUBQUERY:Include SELECT in subqueries in the list

Explain select * from role_permission where role_id = (select id from role where name = 'admin ');

　　　　　　

DERIVED:The DRIVED value is used to represent the SELECT statement of a subquery containing the FROM clause. MySQL recursively executes the statement and places the result in a temporary table. The server internally calls it a "derived table" because the temporary table is born from the subquery.

 

explain select role_id from (select * from role_permission) rp

　　UNION: SELECT statement after or after the second statement in UNION

explain select * from role_permission where role_id=28 union select * from role_permission;

　　　　　　

UNION RESULT: SELECT used to retrieve results from the anonymous temporary table of UNION is marked as UNION RESULT

Table Column

This column shows the table that the corresponding row is accessing. In this column, we can observe the Association Sequence selected by the MySQL Association optimizer from the top down. The Association Sequence selected by MySQL in the following query is different from that specified in the statement.

EXPLAIN SELECT film_idFROM film INNER JOIN film_actor USING(film_id)INNER JOIN actor USING(actor_id);　　

　　　　　　　　　　　　　　

The query execution plan of MySQL is always the left-side depth priority tree. If this scheme is put down, the leaf nodes can be read in order.

 

Type Column

MySQL determines how to find rows in a table. From the worst to the best

　　ALL: Full table scan means that MySQL must scan the entire table to find the required rows from start to end. (There are exceptions here. For example, LIMIT is used in the query, or "Using distinct/not exists" is displayed in the Extra column ")

　　Index:This is the same as full table scan, but MySQL scans the table in the order of indexes instead of rows. Its main advantage is to avoid sorting, and its biggest drawback is to bear the overhead of reading the entire table in the order of indexes. This usually means that if you access rows in a random order, the overhead will be very large.If "Using index" is displayed in the Extra column, MySQL is Using the overwriting index.It only scans the index data, rather than each row in the index order. It has much less overhead than full table scan by index order.

　　Range:A range scan is a restricted index scan. It starts from a certain point in the index and returns rows that match the value range. This is better than full index scanning because it does not need to traverse all indexes. Obvious ScopeA scan is a plug-in with ">" in the BETWEEN or WHERE clause.. When MySQL uses an index to search for a series of values, for exampleIN () and OR listsAnd is displayed as a range scan. However, the two are actually quite different access types, with significant performance differences. The overhead of the secondary scan is equivalent to that of the index type.

For example, the primary key of the doctor table is ID.

explain select * from doctor WHERE ID in(1,2,3);

　　

explain select * from doctor WHERE hospital_id in(1,2,3);

explain select * from doctor WHERE ID BETWEEN 1 AND 3;

 

 

Ref:This is an index access (sometimes called index search),It returns all rows that match a single value..However, it may find multiple qualified rows, so it is a mixture of search and scan. This type of index access occurs only when a non-unique index or a non-unique prefix of a unique index is used. It is called ref because the index must be compared with a specific parameter value. This reference value is either a constant or a result value from the previous table in a multi-Table query.Ref_or_null is a variant above ref, which means that MySQL must perform a second search in the first search results to find the NULL entry.

　　Eq_ref: Using this index search, MySQL knows that only one matching record can be returned. This access method can be seen when MySQL uses a primary key or a unique index for search. It will compare them with a specific parameter value. MySQL optimizes such access types very well, because it knows that it does not need to estimate the range of matched rows or continue searching after finding matched rows.

　　Const, system: When MySQL can optimize a certain part of the query and convert it into a constant, it will use these access types. If you select the primary key of a row by placing the primary key of a row in the WHERE clause, MySQL can convert the query to a constant. Then, the table can be efficiently deleted from the join execution.

For example, the table doctor ID is the primary key.

EXPLAIN select hospital_id from doctor WHERE ID=2;

EXPLAIN select ID, hospital_id from doctor WHERE ID=2;

EXPLAIN select ID from doctor WHERE ID=2;

　　NULL:This access method means that MySQL can break down query statements in the optimization phase, and does not even need to access tables or indexes during the execution phase.

Possible_keys column:

This column shows the indexes that can be used for a query. This is determined based on the columns accessed by the query and the comparison operators used. This list is created in the early stages of the optimization process, so some listed indexes may be useless in subsequent optimization processes.

Key column:

This column shows which index MySQL uses to optimize access to the table. If the index does not appear in the "possible_keys" column, MySQL chooses it for another reason. -- For example, it may select a overwriting index, even if there is no WHERE clause

Key_len column:

This column shows the number of bytes used by MySQL in the index.

Ref column:

This column shows the columns or constants used by the table to search for values in the index of the key Column Record.

Rows column:

This column is the number of rows that MySQL estimates to read to find the required rows. This number is the number of loops in the nested loop association plan. That is to say, it is not the number of rows that MySQL considers to eventually read from the table, but the average number of rows that MySQL must read to meet the criteria at each point of the query.

Filtered column:

This column appears when "explain extended" is used. It displays a pessimistic estimate of the percentage of records that meet a certain condition (WHERE clause or join condition) in the table, the optimizer uses this estimation only when the ALL, index, range, and index_merge access methods are used.

Extra column:

This column contains additional information not suitable for other columns.

"Using index"Indicates that MySQL will use overwriting indexes to avoid table access. Do not confuse the covered index with the index access type.

"Using whereThis means that the MySQL server will filter the row after the storage engine retrieval. Many WHERE conditions involve columns in the index. When it reads the index, it can be checked by the storage engine. Therefore, not all queries with the WHERE clause will display "Using where ". Sometimes the appearance of "Using where" is a suggestion:Queries can benefit from different indexes..

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

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

The value of "Range checked for each record (index map: N)" indicates that there is no useful index, and the new index will be re-estimated on each row of the join. N is the bitmap displayed in the possible_keys column and is redundant.

Refer:

[1] Baron Schwartz; translated by Ninghai yuan; high-performance MySQL (version 3rd); Electronic Industry Press, 2013

Blog, http://blog.csdn.net/zhuxineli/article/details/14455029