Mysql Explain usage detailed _mysql

If you put the keyword explain,mysql in front of the SELECT statement, it explains how it handles the Select, providing an order about how tables are joined and joined.

Each output row of the explain provides information about a table, and each row includes the following columns:

1,id Select identifier. This is the query sequence number for the select.
2,select_type can be used for any one type
Simple select (Do not use union or subqueries)
Primary most out of the Select
The second or subsequent SELECT statement in the Union union
The second or subsequent SELECT statement in the Dependent Union union, depending on the query outside
Results of Union result union.
Subquery The first select in a subquery
Dependent the first select in the subquery subquery, depending on the query outside
Derived a SELECT (subquery for the FROM clause) of the export table
3,table the table referenced by the row that is output.
4,type join type. The types of joins are given below, sorted by best type to worst type:
There is only one row (= system table) on the system table. This is a special case of the const join type.
The const table has at most one matching row, and it will be read at the start of the query. Because there is only one row, the column values in this row can be considered constants by the remainder of the optimizer. The const table is quick because they are only read once!
Eq_ref reads a row from the table for each combination of rows from the previous table. This may be the best join type, except for the const type. It is used in all parts of an index to be joined and the index is unique or primary key
Ref for each combination of rows from the preceding table, all rows that have a matching index value are read from this table. Use ref if the join uses only the leftmost prefix of the key, or if the key is not a unique or primary key (in other words, if the join cannot select a single row based on the keyword). If you use a key that matches only a few rows, the join type is good.
Ref can be used to use an indexed column of the = or <=> operator.
Possible_keys if the column is null, there is no index associated with it. In this case, you can improve your query performance by checking the WHERE clause to see if it references certain columns or columns that fit the index. If so, create an appropriate index and check the query key column again with explain to show the key (index) that MySQL actually decides to use. If no index is selected, the key is null. To force MySQL to use or ignore the index in the Possible_keys column, use force index, using index, or ignore index in the query.
The 5,rows rows column shows the number of rows that MySQL must check when it executes the query.

The above simple introduction of MySQL in the use of explain statements, I hope to help you.

The MySQL explain speak more clearlyOf

With the help of explain, you know when to add an index to a table to use the index to find records so that the select runs faster.
If some problems arise from improper use of the index, you can run analyze table to update the table's statistics, such as the cardinality of the key, to help you make better choices about optimization.

Explain returns a row of records that includes information about the tables used in the SELECT statement. These tables are listed in the results in the order in which they are read in the query that MySQL is about to execute. MySQL solves the connection by scanning multiple connections (single-sweep,multi-join) at a time. This means that MySQL reads a record from the first table, then finds the corresponding record in the second table, and then looks in the third table, and so on. When all the tables are scanned, it outputs the selected fields and backtracking all the tables until it is not found, because there may be more than one matching record in some of the tables. The next record will be read from the table and continue processing from the next table.
In MySQL version 4.1, the result format of the explain output changes, making it more appropriate for example union statements, subqueries, and the structure of derived tables. More notably, it adds 2 fields: IDs and Select_type. These fields are not visible when you use a version earlier than mysql4.1.
Each row of the explain results shows information about each table, and each row contains the following fields:

Id
The identifier for this select. In a query, each select has a numeric value in the order.
Select_type
The type of select may have the following:
Simplicity: Simple Select (no union or subquery is used)

Primary: the outermost select.

Union: The second layer, which uses union after the select.

Dependent the second select in the Union:union statement, dependent on the outer subquery

Subquery: The first select in a subquery

Dependent subquery: The first subquery in a subquery relies on an external subquery

Derived: Derived table Select (subquery in FROM clause)

Table
Records the tables referenced by the query.

Type
Table connection type. Here are a list of different types of table joins, from best to worst in turn:

System: The table has only one row of records (equal to the system table). This is a special case of the const table join type.

Const: A table that has at most one row of matching records that is read at the beginning of the query. Because there is only one row of records, the field values of the row records in the remainder of the optimizer can be treated as a constant value. The Const table query is very fast because just read it once! Const is used in situations where there is a fixed value comparison with a primary key or a unique index. In the following few queries, Tbl_name is the C table:
SELECT * from Tbl_name where primary_key=1; SELECT * from Tbl_namewhere primary_key_part1=1 and primary_key_part2=2;

Eq_ref: A row of records from this table is read to unite with records read from the previous table. Unlike the const type, this is the best connection type. It is used in all parts of the index to make a connection and this index is a primary key or a unique type. Eq_ref can be used to retrieve a field when a "=" comparison is made. The value of the comparison can be either a fixed value or an expression in which the fields in the table can be used, and they are ready before they are read. In the following examples, MySQL uses a eq_ref connection to handle ref_table:

SELECT * from Ref_table,other_table whereref_table.key_column=other_table.column; SELECT * Fromref_table,other_table whereref_table.key_column_part1=other_table.column andref_table.key_column_ Part2=1;

Ref: All records in the table that match the retrieved value are taken out and combined with records fetched from the previous table. Ref is used by the connector to use the leftmost prefix of a key, or if the key is not a primary key or a unique index (in other words, the connection program cannot obtain only one record based on the key value). This is a good connection type when only a few matching records are queried based on the key value. Ref can also be used to retrieve the time when the field is compared using the = operator. In the following few examples, MySQL will use ref to process ref_table:
SELECT * from ref_table where key_column=expr; SELECT * Fromref_table,other_table whereref_table.key_column=other_table.column; SELECT * Fromref_table,other_table whereref_table.key_column_part1=other_table.column andref_table.key_column_ Part2=1;

Ref_or_null: This type of connection is similar to ref, and MySQL will search for additional records that contain null values at the time of retrieval. This type of connection is optimized from mysql4.1.1 and is often used for subqueries. In the following example, MySQL uses the ref_or_null type to handle ref_table:
SELECT * from ref_table where key_column=expr or key_column is null;

Unique_subquery: This type replaces ref with, for example, a form in subquery:
Value in (select Primary_key from single_table where some_expr)

Unique_subquery: Only the index lookup function, which is used to completely replace the subquery, is more efficient.

Index_subquery: This type of connection is similar to Unique_subquery. It replaces in with a subquery, but it is used in cases where there is no unique index in the subquery, such as the following:
Value in (select Key_column from single_table where some_expr)

Range: Only records in a given range are taken out, using the index to get a record. The key field indicates which index is used. The Key_len field includes the longest part of the key used. The REF field value is NULL when this type. Range is used to compare a field and a colonization with any of the following operators =, <>, >,>=,, <=, is null, <=>, between, or in:
SELECT * from tbl_name where key_column = 10; SELECT * Fromtbl_name where key_column between and 20; SELECT * from Tbl_namewhere key_column in (10,20,30); SELECT * from Tbl_name wherekey_part1=, Key_part2 in (10,20,30);

Index: The connection type is the same as all, except that it scans the index tree only. It is usually faster than all, because index files are usually smaller than data files. MySQL uses this type of connection in the case of a query that is part of a separate index of the field knowledge.

All: A full scan of the table is made to unite with records obtained from the previous table. At this point, if the first table is not identified as const, it is not good, in other cases is usually very bad. Normally, you can increase the index so that you can get records faster from the table to avoid all.

Possible_keys

The Possible_keys field refers to which index MySQL may use when searching for a table record. Note that this field is completely independent of the table order displayed by explain. This means that the index contained in Possible_keys may not be available in actual use. If the value of this field is null, it means that no index is used. In this case, you can check which fields in the WHERE clause are suitable for adding indexes to improve the performance of the query. So, create the index and then check it with explain. Detailed view of the chapter "14.2.2 alter Tablesyntax". To see what index the table has, you can see it by show the index from Tbl_name.

Key

The key field shows the index to which MySQL is actually used. When no index is used, the value of this field is null. To allow MySQL to force or ignore the list of indexes in the Possible_keys field, you can use the Keyword Force index, using index, or ignore index in the query statement. If you are a MyISAM and BDB type table, you can use analyzetable to help you analyze which index is better to use. If it is a MyISAM type table, running the command Myisamchk--analyze is the same effect. The chapters "14.5.2.1 Analyze Tablesyntax" and "5.7.2 table maintenance and crash recovery" can be viewed in detail.

Key_len

The Key_len field shows the length of the index that MySQL uses. When the value of the key field is null, the length of the index is null. Note that the value of Key_len can tell you which indexes the MySQL will actually use in the federated index.

Ref

The REF field shows which fields or constants are used to query records from the table with the key.

Rows
The Rows field shows the number of records that MySQL considers should be retrieved in the query.

Extra

This field shows additional information about MySQL in the query. Here is an explanation of several different values for this field:

Distinct:mysql when the first record of a matching union result for the current record is found, no more records are searched.

Not exists:mysql when you make a LEFT JOIN optimization query, it no longer searches for more records when it finds in the current table and matches the previous record with the left join condition. Here is an example of this type of query:
SELECT * from T1 LEFT join T2 on T1.id=t2.id where T2.id isnull;

Suppose t2.id is defined as NOT NULL. In this case, MySQL scans the table T1 and finds records in T2 with t1.id values. When a matching record is found in the T2, this means that t2.id is definitely not NULL, and no other records of the same ID value will be looked up in T2. It can also be said that for each record in T1, MySQL only needs to do a lookup in the T2, regardless of how many matching records actually have in T2.

Range checked for each record (Index map: #)

MySQL did not find the appropriate index available. Instead, for each row of the previous table, it does a test to determine which index to use, if any, and use the index to get the records from the tables. This process is not fast, but it is faster than making a table connection without any index.

Using Filesort:mysql requires extra work to get records in a sorted order. The sequencer traverses all records based on the type of connection, and stores all the keys to be sorted and pointers to records for all records that match the Where condition. These keys have been sorted out, and the corresponding records will be sorted out in order. Please see "7.2.9how MySQL optimizes order by" for details.
Using index

The information in the field is obtained directly from the information in the index tree and is not scanned for actual records. The fields that this policy uses when querying are part of a separate index.

Using Temporary:mysql needs to create temporary tables to store results to complete the query. This usually occurs when the query contains the GROUPBY and ORDER BY clauses, and it lists the fields in different ways.
Using where

The WHERE clause will be used to restrict which records match the next table or send to the client. Unless you specifically want to get or check all the records of the form, the query's extra field value is not a using where and the table join type is all or index, which may indicate a problem.

If you want to make the query as fast as possible, you should be aware that the value of the extra field is usingfilesort and using temporary.

You can probably tell how the connection behaves by multiplying the value of the Rows field in the result of the explain. It can tell us roughly how many records MySQL will query during the query. If you use System variable max_join_size to obtain query results, this product can also be used to determine which multiple table SELECT statements will be executed.
The following example shows how to greatly optimize the performance of a multiple-table federated query with the information provided by explain.
Assume that the following SELECT statement is intended to be detected using explain:
Explain select Tt.ticketnumber, Tt.timein, Tt.projectreference,tt.estimatedshipdate, Tt.actualshipdate, Tt.clientid, Tt.servicecodes, Tt.repetitiveid, Tt.currentprocess,tt.currentdppers tt.recordvolume, tt.dpprinted, et.country,et_ 1.country, do.custname from TT, ET, et as et_1, does wherett.submittime is null and TT.ACTUALPC = Et.employid andtt.assigned PC = Et_1.employid and Tt.clientid = DO.CUSTNMBR;

In this example, let's make the following assumptions:

The fields to be compared are defined as follows:
Table Column ColumnType
TT ACTUALPC CHAR (10)
TT ASSIGNEDPC CHAR (10)
TT ClientID CHAR (10)
ET Employid char (15)
Do custnmbr char (15)

The index of the datasheet is as follows:
Table Index
TT ACTUALPC
TT ASSIGNEDPC
TT ClientID
ET Employid (primary key)
Do CUSTNMBR (primary key)

The value of the TT.ACTUALPC is unevenly distributed.

Before any optimization measures have been taken, the results of the explain analysis are shown as follows:
Table Type Possible_keys key Key_len ref rows extra
ET all primarynull null NULL 74
Do all primary null null 2135
Et_1 allprimary NULL NULL 74
tt all ASSIGNEDPC, NULL NULL null 3872 ClientID, ACTUALPC range checked to each record (key map:35)

Because the field type is all for each table value, this result means that MySQL does a Cartesian product for all tables; that is, the combination of each record. This will take a long time because you need to scan the sum of the product of the total number of records per table. In this case, its product is 74 * 2135 * 74 * 3872 = 45,268,558,720 records. If the datasheet is bigger, you can imagine how long it will take.
The problem here is that when the field definition is the same, MySQL can be indexed faster on these fields (for ISAM types of tables, unless the field definition is exactly the same, the index is not used). Under this premise, varchar and char are the same unless they are defined in an inconsistent length. Because the TT.ACTUALPC is defined as char, Et.employid is defined as char (15), which is inconsistent in length.
To solve this problem, ALTER TABLE is required to increase the length of the ACTUALPC from 10 to 15 characters:
Mysql> ALTER TABLE TT modify ACTUALPC varchar (15);

Now TT.ACTUALPC and Et.employid are varchar (15)
Out. Again, execute the explain statement to see the result:
Table Type Possible_keys key Key_len ref rows extra
TT ALLASSIGNEDPC, NULL NULL NULL 3872 using ClientID, where ACTUALPC
Do all primary null NULL NULL 2135 range checked to each record (keymap:1)
Et_1 all primary null-null range checked for Eachrecord (key map:1) et Eq_ref Primary primary 1

That's not enough, it can do better: now the product is 74 times times less. This query will take 2 seconds.
The second change is to eliminate the length inconsistency of fields in the comparison tt.assignedpc = Et_1.employid and tt.clientid= do.custnmbr:
Mysql> ALTER TABLE TT modify ASSIGNEDPC varchar,->modify clientid varchar (15);

Now the results of the explain are as follows:
Table Type Possible_keys key Key_len ref rows extra
ET all primary NULL NULL 74
TT ref ASSIGNEDPC, ACTUALPC Et.employid using ClientID, where ACTUALPC
Et_1 Eq_ref Primary Primary TT.ASSIGNEDPC 1
Do eq_ref Primary primary Tt.clientid 1

It seems to be the best that can be done.
The legacy of the problem is that MySQL defaults to the value of the field TT.ACTUALPC is evenly distributed, but the table TT is not. Fortunately, we can easily let MySQL analyze the distribution of the index:
mysql> analyze table TT;

So far, the table connection has been optimized for perfection, and the results of explain are as follows:
Table Type Possible_keys key Key_len ref rows extra
TT all ASSIGNEDPC null NULL NULL 3872 using ClientID, where ACTUALPC
ET Eq_ref Primary primary TT.ACTUALPC 1
Et_1 Eq_ref Primary Primary TT.ASSIGNEDPC 1
Do eq_ref Primary primary Tt.clientid 1

Note that the values of the Rows field in the explain result are also roughly guessed by the MySQL connection optimizer, and check to see if this value is basically consistent with the real value. If not, you can achieve better performance by using Straight_join in the SELECT statement, while trying to list each table in a different order in the FROM clause.

The following are supplementary information:

With the help of explain, you know:
1 When you must add an index to the table to get a faster select that uses the index to find records.
2 whether the optimizer joins the table in a best order. To force the optimizer to use a specific join order for a SELECT statement, add a straight_join clause.
Official documentation on explain in http://dev.mysql.com/doc/refman/5.1/en/using-explain.html (English)

MySQL explain detailed

Methods used by MySQL explain

EXPLAIN Tbl_name
Or:
EXPLAIN [EXTENDED] SELECT select_options

The former can draw a table of the field structure, and so on, the latter is mainly to give some relevant index information, and today to talk about the latter.

Example

Copy Code code as follows:

Mysql> Explain select * from event;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | Simple | Event | All | NULL | NULL | NULL | NULL | 13 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in Set (0.00 sec)

The meaning of each property

Id
Serial number of the select query

Select_type
The type of a select query is primarily a complex query that distinguishes between common and federated queries, subqueries, and so on.

Table
The table referenced by the row being exported.

Type
The type used by the union query.
Type shows access types and is a more important metric, and the resulting values are from good to bad in turn:
System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_sub Query > Range > Index > All
In general, you have to ensure that the query reaches at least range level, preferably to ref.

Possible_keys
Indicates which index MySQL can use to find rows in the table. If it is empty, there is no index associated with it. To improve performance, you can check the WHERE clause to see if some of the fields are referenced, or if the field is not an appropriate index.

Key
Displays the key that MySQL has actually decided to use. If no index is selected, the key is null.

Key_len
Displays the length of the key that MySQL decided to use. If the key is null, the length is null. Document prompts pay special attention to this value to derive which part of MySQL is actually used in a multiple primary key.

Ref
Shows which field or constant is used with the key.

Rows
This number indicates how much data MySQL is going to traverse to find, and is inaccurate on the InnoDB.

Extra
If it is only index, this means that the information is retrieved using only the information in the index tree, which is faster than scanning the entire table.
If it is a where used, the where constraint is used.
If it's a impossible where it means no where, it's usually nothing.
If this information displays using Filesort or using temporary, it will be difficult to find the index of where and order by, and if the index is determined according to the where, then it is bound to cause a using Filesort, this depends on the first filter and then sort the cost, or the first sorting and filtering cost-effective.
Some common explanations of nouns

Using Filesort
MySQL requires an extra pass to find out how to retrieve rows in sorted order.

Using Index
Retrieves the column information in a table from using only the information in the index tree without further searching to read the actual rows.

Using Temporary
To resolve the query, MySQL needs to create a temporary table to accommodate the results.

Ref
For each combination of rows from the previous table, all rows with matching index values are read from this table

All
Without indexing at all, the performance is very poor.

Index
The same as all except that only the index tree is scanned. This is usually faster than all because index files are usually smaller than data files.

Simple
Simple select (Do not use union or subqueries)