Explanation of explain usage in mysql _ MySQL

Explanation of the usage of explain in mysql MySQLexplain

BitsCN.com

The explanation of mysql explain is clear.Of

With the help of explain, you will know when to add an index to the table to use the index to search for records so that select can run faster.
If the index is not properly used, you can run analyze table to update the statistical information of the table, such as the base of the key, which can help you make better choices in terms of optimization.

Explain returns a row of records, which includes information about each table used in the select statement. These tables are listed in the results in the Read Order of the mysql Query to be executed. Mysql uses the single-sweep (multi-join) method to resolve the connection. This means that mysql reads a record from the first table, finds the corresponding record in the second table, searches in the third table, and so on. When all the tables are scanned, it outputs the selected fields and traces back to all the tables until they cannot be found, some tables may have multiple matching records. the next record will be read from the table and processed from the next table.
In mysql version 4.1, the format of explain output results has changed, making it more suitable for the structure of union statements, subqueries, and derived tables. It adds two new fields: id and select_type. If you use versions earlier than mysql4.1, you will not be able to see these fields.
The relevant information of each table is displayed in each line of the explain result. each line of record contains the following fields:

Id
The identifier of this select statement. Each select statement in a query has an ordered value.
Select_type
The select type may include the following types:
Simple: simple select (union or subquery is not used)

Primary: select of the outermost layer.

Union: second layer. union is used after select.

Dependent union: The Second select in the union statement, depending on external subqueries

Subquery: The first select in the subquery.

Dependent subquery: The first subquery in a subquery depends on an external subquery.

Derived: derived table select (subquery in the from clause)

Table
Record the referenced table.

Type
Table connection type. The following lists various types of table connections, from the best to the worst:

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

Const: a table can contain a maximum of one matching record. it is read at the beginning of the query. Since there is only one record, the field values recorded by this row in the remaining optimization programs can be treated as a constant value. The const table query is very fast, because only one read is required! Const is used to compare a fixed value with a primary key or unique index. In the following 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 is read from the table and associated with the records read from the previous table. Different from the const type, this is the best connection type. It is used for connecting all parts of the index and the index is of the primary key or unique type. Eq_ref can be used to retrieve fields when "=" is compared. The comparative values can be fixed values or expressions, and the fields in the table can be used in the expression. they are ready before reading the table. In the following examples, mysql uses the eq_ref connection to process ref_table:

Select * from ref_table, other_table whereref_table.key_column = other_table.column; select * fromref_table, other_table partition = other_table.column andref_table.key_column_part2 = 1;

Ref: all records in the table that match the search value will be taken out and used together with the records obtained from the previous table. Ref is the leftmost prefix used by the Connection program to use the key, or the key is not a primary key or a unique index (in other words, the connection program cannot retrieve only one record based on the key value. When only a few matching records are queried based on the key value, this is a good connection type. Ref can also be used to compare fields by using the = operator. In the following examples, mysql uses ref to process ref_table:
Select * from ref_table where key_column = expr; select * fromref_table, other_table partition = other_table.column; select * fromref_table, other_table partition = other_table.column andref_table.key_column_part2 = 1;

Ref_or_null: this connection type is similar to ref. The difference is that mysql will search for records containing null values during retrieval. This connection type is optimized from mysql4.1.1 and is often used for subqueries. In the following example, mysql uses the ref_or_null type to process ref_table:
Select * from ref_table where key_column = expr or key_column is null;

Unique_subquery: replace ref with in subqueries in the following form:
Value in (select primary_key from single_table where some_expr)

Unique_subquery: it is more efficient to replace the index search function of a subquery.

Index_subquery: the connection type is similar to unique_subquery. It uses a subquery to replace in, but it is used when there is no unique index in the subquery, for example, the following form:
Value in (select key_column from single_table where some_expr)

Range: only records within a specified range are retrieved and an index is used to obtain a record. The key field indicates which index is used. The key_len field contains the longest part of the key used. In this type, the ref field value is null. Range is used to compare a field with a field by any of the following operators: =, <>,>, >=, <, <=, is null, <=>,, or in:
Select * from tbl_name where key_column = 10; select * fromtbl_name where key_column between 10 and 20; select * from tbl_namewhere key_column in (10, 20, 30 ); select * from tbl_name wherekey_part1 = 10 and key_part2 in (10, 20, 30 );

Index: The connection type is the same as that of all. The difference is that it only scans the index tree. It is usually faster than all because the index file is usually smaller than the data file. Mysql uses this connection type when the queried field knowledge is a separate part of the index.

All: scans all the tables and associates the records obtained from the previous table. At this time, if the first table is not identified as const, it is not very good. In other cases, it is usually very bad. Normally, you can add indexes to quickly retrieve records from the table to avoid all.

Possible_keys

The possible_keys field indicates the index that mysql may use when searching table records. Note that this field is completely independent from the table sequence displayed in the explain statement. This means that the indexes contained in possible_keys may not be used in actual use. If the value of this field is null, it indicates that no index is used. In this case, you can check which fields in the where clause are suitable for adding indexes to improve query performance. In this case, create an index and then use the explain command to check the index. For details, see section "14.2.2 alter tablesyntax ". If you want to see what indexes are available for the table, you can use show index from tbl_name.

Key

The key field shows the indexes actually used by mysql. When no index is used, the value of this field is null. To enable mysql to forcibly use or ignore the index list in the possible_keys field, you can use the keyword force index, use index, or ignore index in the query statement. For myisam and bdb tables, you can use analyzetable to help analyze which index is better. For tables of the myisam type, the same effect is achieved by running the command myisamchk -- analyze. For details, see chapter "14.5.2.1 analyze tablesyntax" and "5.7.2 table maintenance and crash recovery ".

Key_len

The key_len field shows the index length used by mysql. If the value of the key field is null, the index length is null. Note that the value of key_len tells you which indexes mysql uses in the Union index.

Ref

The ref field shows which fields or constants are used to work with keys to query records from the table.

Rows
The rows field shows the number of records that mysql considers to be retrieved in the query.

Extra

This field displays the additional mysql information in the query. The following is an explanation of several different values of this field:

Distinct: when mysql finds the first record of the matching Union result of the current record, it no longer searches for other records.

Not exists: when mysql performs a left join optimization during query, when it finds that it meets the left join condition with the previous record in the current table, it no longer searches for more records. The following is an example of this type of query:
Select * from t1 left join t2 on t1.id = t2.id where t2.id isnull;

If t2.id is defined as not null. In this case, mysql scans table t1 and searches for records in T2. When a matched record is found in T2. this means that t2.id will not be null, and other records with the same id value will not be searched in T2. It can also be said that for each record in t1, mysql only needs to perform a search in T2. no matter how many matching records actually exist in T2.

Range checked for each record (index map :#)

Mysql does not find an appropriate available index. Instead, for each row join in the previous table, it performs a test to determine which index to use (if any ), use this index to retrieve records from the table. This process is not very fast, but it is always faster than doing table join joins without any indexes.

Using filesort: mysql requires an additional record to be obtained in an ordered order. The ordering program traverses all records based on the connection type, and stores the keys to be sorted and pointers to records that meet the where condition. These keys have been sorted out, and the corresponding records are also sorted out. For details, see "7.2.9how mysql optimizes order ".
Using index

The field information is obtained directly from the index tree, instead of scanning the actual records. Fields used for query are part of an independent index.

Using temporary: mysql needs to create a temporary table storage result to complete the query. This usually occurs when the query contains the groupby and order by clauses, which list fields in different ways.
Using where

The where clause will be used to limit which records match the next table or send to the client. Unless you particularly want to obtain or check all records, it may indicate a problem when the queried extra field value is not using where and the table connection type is all or index.

If you want to make the query as fast as possible, you should note that the extra field values are usingfilesort and using temporary.

You can get a rough idea about how the connection works by the product of the rows field value in the explain result. It roughly tells us how many records mysql will query during the query process. If the system variable max_join_size is used to obtain the query result, the product can also be used to determine which multi-table select statements will be executed.
The following example shows how to optimize the performance of multi-table joint query by using the information provided by explain.
Assume that the following select statement is used, and the explain statement is intended to be used for detection:
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. employee ID andtt. assignedpc = et_1.employid and tt. clientid = do. custnmbr;

In this example, 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 data table index is as follows:
Table index
Tt actualpc
Tt assignedpc
Tt clientid
Et employee ID (primary key)
Do custnmbr (primary key)

The tt. actualpc values are unevenly distributed.

Before any optimization measures are taken, the results of the explain analysis are as follows:
Table type possible_keys key key_len ref rows extra
Et all primarynull null 74
Do all primary null 2135
Et_1 allprimary null 74
Tt all assignedpc, null 3872 clientid, actualpc range checked for each record (key map: 35)

Because the field type is all for each table value, this result means that mysql creates a DIKAR product for all tables, that is, the combination of each record. This takes a long time because you need to scan the total number of records in each table. 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 use indexes for these fields more quickly (for an isam table, unless the field definition is the same, otherwise, the index will not be used ). On this premise, varchar and char are the same unless they are defined with different lengths. Since tt. actualpc is defined as char (10) and et. employee ID is defined as char (15), the length of the two is different.
To solve this problem, we need to use alter table to increase the length of actualpc from 10 to 15 characters:
Mysql> alter table tt modify actualpc varchar (15 );

Now tt. actualpc and et. employid are both varchar (15)
. Execute the explain statement again to see the result:
Table type possible_keys key key_len ref rows extra
Tt allassignedpc, null, 3872 using clientid, where actualpc
Do all primary null 2135 range checked for each record (keymap: 1)
Et_1 all primary null 74 range checked for eachrecord (key map: 1) et eq_ref primary 15 tt. actualpc 1

This is not enough. it can do better: the rows value product is 74 times less. This query takes 2 seconds.
The second change is to eliminate the inconsistent length of fields in tt. assignedpc = et_1.employid and tt. clientid = do. custnmbr:
Mysql> alter table tt modify assignedpc varchar (15),-> modify clientid varchar (15 );

The explain result is as follows:
Table type possible_keys key key_len ref rows extra
Et all primary null 74
Tt ref assignedpc, actualpc 15 et. employee ID 52 using clientid, where actualpc
Et_1 eq_ref primary 15 tt. assignedpc 1
Do eq_ref primary 15 tt. clientid 1

This seems to be the best result.
The legacy problem is that mysql considers the tt. actualpc value of the field to be evenly distributed by default, but not in the table tt. Fortunately, we can easily let mysql analyze the index distribution:
Mysql> analyze table tt;

By now, the table connection has been optimized perfectly. the explain result is as follows:
Table type possible_keys key key_len ref rows extra
Tt all assignedpc null 3872 using clientid, where actualpc
Et eq_ref primary 15 tt. actualpc 1
Et_1 eq_ref primary 15 tt. assignedpc 1
Do eq_ref primary 15 tt. clientid 1

Note that the value of the rows field in the explain result is also roughly guessed by the mysql connection optimization program. check whether the value is basically the same as the actual value. If not, you can use straight_join in the select statement to achieve better performance. you can also try to list tables in different order in the from clause.

Additional information is provided as follows:

With the help of explain, you can know:
1) When must I add an index to the table to obtain a faster SELECT statement that uses the index to locate records.
2) whether the optimizer joins the table in an optimal 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)

Mysql explain

How to use mysql explain

EXPLAIN tbl_name
Or:
EXPLAIN [EXTENDED] SELECT select_options

The former can obtain the field structure of a table, and the latter mainly provides related index information. The latter focuses on the latter.

Example

Mysql> explain select * from event;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------- +
| 1 | SIMPLE | event | ALL | NULL | 13 |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------- +
1 row in set (0.00 sec)

Meaning of each attribute

Id
Serial Number of the select query

Select_type
Select queries are different from complex queries such as common queries and joint queries and subqueries.

Table
The table referenced by the output row.

Type
The type used by the union query.
Type displays the access type and is an important indicator. The result values are as follows:
System> const> eq_ref> ref> fulltext> ref_or_null> index_merge> unique_subquery> index_subquery> range> index> ALL
In general, make sure that the query reaches the range level at least, and it is best to reach the ref level.

Possible_keys
Specifies which index MySQL can use to find rows in the table. If it is null, there is no relevant index. To improve performance, you can check the WHERE clause to see if some fields are referenced or if the fields are not suitable for indexing.

Key
Displays the Keys actually determined by MySQL. If no index is selected, the key is NULL.

Key_len
Displays the key length determined by MySQL. If the key is NULL, the length is NULL. Note that this value can be used to determine which part of mysql is actually used in multiple primary keys.

Ref
Shows the field or constant used with the key.

Rows
This number indicates how much data mysql needs to traverse before it can be found, which is inaccurate in innodb.

Extra
If it is Only index, this means that information is Only retrieved from the index tree, which is faster than scanning the entire table.
If it is where used, the where restriction is applied.
If it is impossible where, it indicates that the where is not needed. generally, nothing is found.
If this information shows Using filesort or Using temporary, it will be very difficult, and the WHERE and order by indexes are often unable to take into account. if the index is determined by where, then in order, this will inevitably lead to Using filesort. it depends on whether filtering and sorting are cost-effective, or sorting and filtering are cost-effective.
Common Glossary

Using filesort
MySQL requires an additional pass to find out how to retrieve rows in order.

Using index
You can use only the information in the index tree without further searching and reading the actual row to retrieve the column information in the table.

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

Ref
For each row combination from the preceding table, all rows that match the index value are read from this table.

ALL
If there is no index at all, the performance is very poor.

Index
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 subquery)

BitsCN.com