A detailed explanation of explain in MySQL

Source: Persister

Links: http://www.blogjava.net/persister/archive/2008/10/27/236813.html

With the help of explain, you know when to add an index to the table to use the index to find records and make the select run faster. If some problems arise due to improper use of the index, you can run analyze table to update the table's statistics, such as the cardinality of the key, which can help you make better choices in terms of optimization.

Explain returns a row of records that includes the information for each table used in the SELECT statement. These tables are listed in the results in the order that they are read in the query that MySQL is about to execute. MySQL uses a single scan of multiple connections (Single-sweep,multi-join) to resolve the connection. This means that MySQL reads a record from the first table, then finds the corresponding record in the second table, then finds it in the third table, and so on. When all the tables are scanned, it outputs the selected fields and goes back all the tables until it is not found, because there may be multiple matching records in the table where the next record will be read from the table, and then continue processing from the next table.

In MySQL version 4.1, the result format of the explain output changed, making it more suitable for the structure of Union statements, subqueries, and derived tables. More notably, it adds 2 fields: ID and Select_type. When you use a version earlier than mysql4.1, you won't see these fields.

Each row of the explain results shows information about each table, and each row of records contains the following fields:

Id

identifier for this select. Each select in the query has a sequential numeric value.

Select_type

The type of select may be as follows:

Simple: Easy Select (No union or subquery is used)

Primary: the outermost select.

Union: The second layer, after select, uses Union.

Dependent the second select in a 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 the FROM clause)

Table

Records the tables referenced by the query.

Type

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

System: Table has only one row of records (equals system tables). This is a special case of the Const table connection type.

Const: There is a maximum of one row of records in the table that will be read at the beginning of the query. Because there is only one row of records, the value of the field recorded in the remaining optimizer can be treated as a constant value. The const table is very fast to query because it only reads once! The const is used in cases where there is a fixed value comparison in the primary key or 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 will be read to federate with records read from the previous table. Unlike the const type, this is the best type of connection. 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 making a "=" comparison. The values that are compared can be fixed values or expressions, and the fields in the table can be used in the expression, and they are ready to read. In the following example, MySQL uses the EQ_REF connection to process the 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 values are taken out and combined with the records taken from the previous table. Ref is used when the connector uses the leftmost prefix of a key or if the key is not a primary key or a unique index (in other words, the connector cannot get 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 fields when using the = operator to compare. In a few examples below, 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 the difference is that MySQL searches for additional records that contain null values when retrieving. The optimization of this type of connection starts with mysql4.1.1, and it 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 an in subquery such as the form:

Value in (select Primary_key from single_table where some_expr)

Unique_subquery: An index lookup function that is used to completely replace a subquery is more efficient.

Index_subquery: This type of connection is similar to Unique_subquery. It uses subqueries instead of in,

However, it is used in cases where there are no unique indexes in the subquery, such as the following form:

Value in (select Key_column from single_table where some_expr)

Range: Only records in a given range are taken out, and the index is used to obtain a record. The key field indicates which index is used. The Key_len field includes the longest portion of the key used. This type is when the REF field value is null. 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 the index file is usually smaller than the data file. MySQL uses this type of connection in cases where the field knowledge of a query is part of a separate index.

All: The table will be scanned for union with the records obtained from the previous table. This is not a good time if the first table is not identified as const, which in other cases is usually very bad. Normally, you can make records faster from the table by increasing the index to avoid all.

Possible_keys

The Possible_keys field refers to which index MySQL may use when searching for table records. Note that this field is completely independent of the table order displayed by explain. This means that the index contained within the Possible_keys may not be used in actual use. If the value of this field is null, it means that no index is used.

In this case, you can examine which fields in the WHERE clause are appropriate for increasing the index to improve the performance of the query. So, create the index, and then check it with explain. See section "14.2.2 alter Tablesyntax" for details. If you want to see what index the table has, you can see it by show index from Tbl_name.

Key

The key field shows the actual index that MySQL will use. When no index is used, the value of this field is null. To make MySQL use or ignore the list of indexes in the Possible_keys field, you can use the Keyword Force index in the query statement, using index, or ignore index.

If it is a MyISAM and BDB type table, you can use analyzetable to help analyze which index is better to use. If it is a MyISAM type table, running command myisamchk–analyze is the same effect. You can view the chapters "14.5.2.1 Analyze Tablesyntax" and "5.7.2 table maintenance and crash recovery" in detail.

Key_len

The Key_len field shows the length of the index used by MySQL. 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 what indexes MySQL will actually use in the federated index.

Ref

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

Rows

The Rows field shows the number of records that MySQL believes 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 is found for the current record, no more records are searched.

Not Exists:mysql when a left join is optimized at query time, when it is found in the current table and the previous record matches the left join condition, no more records are searched. 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 will scan the table T1 and use the value of T1.id to find records in T2. When a matching record is found in T2, this means that t2.id is definitely not NULL, and no other records of the same ID value are found in T2. It can also be said that for each record in T1, MySQL only needs to do a lookup in T2, regardless of how many matching records are actually in the T2.

Range checked for each record (Index map: #)

MySQL did not find a suitable index available. Instead, for each row of the previous table, it would do a test to determine which index to use, if any, and use the index to get the records from the table. This process will not be fast, but it is always faster than making table connections without any indexes.

The using filesort:mysql needs to be done in order to get the records in an orderly sequence. The sequencer traverses all records based on the type of connection, and stores all the keys to be sorted and pointers to records that match the Where Condition's records. These keys are already sorted out, and the corresponding records are taken out in the order in which they are sorted. 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 no longer scans the actual record. This policy is used when querying a field that is part of an independent index.

The using Temporary:mysql needs to create a temporary table to store the results to complete the query. This typically occurs when the query contains the GROUPBY and ORDER BY clauses, which list 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 a form, the query's extra field value is not a using where and the table connection type is all or index may indicate a problem.

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

You can probably know how this connection behaves by multiplying the value of the Rows field in the results of the explain. It can tell us roughly how many records MySQL will query during the query. If you are using system variable max_join_size to get query results, this product can also be used to determine which multi-table SELECT statements will be executed.

The following example shows how to use the information provided by explain to optimize the performance of a multi-table federated query in a larger way.

Suppose you have the following SELECT statement and are going to use explain to detect:

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, do wherett.submittime is null and tt.actualpc = Et.employid

ANDTT.ASSIGNEDPC = Et_1.employid and tt.clientid = do.custnmbr;

In this example, the following assumptions are made:

The fields to compare 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 data table 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 not evenly distributed.

Before any optimization measures are taken, the results of the explain analysis are shown below:

Table type possible_keys key key_len ref rows extra

Et all primarynull null null

Do all primary null null null 2135

Et_1 allprimary null null NULL

tt all assignedpc, NULL NULL null 3872 ClientID, actualpc< c40> range checked for each record (key map: + )

Because the field type is all for each table value, this result means that MySQL makes a Cartesian product for all tables, that is, a combination of each record. This will take a long time because the sum of the product of the total number of records for each table needs to be scanned. In this case, its product is 74 * 2135 * 74 * 3872 = 45,268,558,720 records. If the data table is larger, 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 a table of ISAM type, unless the field definition is exactly the same, the index will not be used). In this premise, varchar and char are the same unless the length they define is inconsistent. Because TT.ACTUALPC is defined as char (ten), Et.employid is defined as char (15), which is inconsistent in length.

To solve this problem, you need to use ALTER TABLE 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 both varchar (15). Then execute the explain statement once 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 For each record (keymap: 1)

Et_1 all primary null NULL null- range checked For Eachrecord (key map: 1) et eq_ref primary Primary 15 TT.ACTUALPC 1

This is not enough, it can be done better: now the rows value product has been 74 times times less. This query takes 2 seconds.

The second change is to eliminate the inconsistent length of the fields in the comparison tt.assignedpc = Et_1.employid and tt.clientid= do.custnmbr:

Mysql> ALTER TABLE TT modify ASSIGNEDPC varchar (n),->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 null a.

TT ref ASSIGNEDPC, ACTUALPC Et.employid clientid using , where actualpc

et_1 eq_ref Primary Primary tt.assignedpc 1

do eq_ref primary primary Tt.clientid 1

This seems to be the best possible result.

The remaining 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 indexes:

mysql> analyze table TT;

So far, the table connection has been optimized, 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 15 TT.ACTUALPC 1

Et_1 eq_ref primary primary tt.assignedpc 1

do eq_ref primary Primary tt.clientid 1

Note that the value of the Rows field in the explain results is also roughly guessed by the MySQL connection optimizer, check that the value is basically consistent with the true value. If not, you can get better performance by using Straight_join in the SELECT statement, and you can try to list the tables in different order in the FROM clause.

A detailed explanation of explain in MySQL