Students with MySQL experience may encounter SQL slow query scenarios in the actual project, some scenarios are easy to locate the problem (such as a single table operation with slow query SQL, carefully check SQL statements are usually easy to locate index problems), and some complex business scenarios (such as multi-table union query dozens of fields and do group or sort operations), Manual Check SQL statement is often difficult to find the root of SQL bottleneck. At this point, MySQL provides a explain command that comes in handy.
This note mainly describes the output of explain, and gives an idea of how to optimize SQL based on explain output.
1. Explain syntax and use
The syntax of the explain command is shown in the official website documentation, which is skipped.
The main function of this command is to output MySQL's optimizer to SQL execution plan, that is, MySQL will explain how to handle the input SQL (whether using the index, using thewhich index, in what order and what associated fields are the multiple tables to join).
Explain's tips can help you realize which fields should be indexed, and also help you confirm that SQL execution is optimizer in a reasonable order .joins more than one table. For example, if you have a SQL statement like this:
Select T1.id, T2.link, t3.detail from T1, T2, T3 where T1.id < and t1.id = t2.base_id and t3.link_sign =t2.sign;
When this statement executes, optimizer does not necessarily join the 3 tables in the order of the tables listed from, and the join order of the table is likely to affect SQL performance.
In this scenario, there are 2 ways to make optimizer join in the table order listed in the FROM statement:
1) Add Straight_join after the Select keyword to prompt optimizer to join in the order of the tables listed in from, see the Select document for specific syntax
2) Adjust the position of the Table association fields before and after the equals sign in the SQL where condition
The following section of this article explains how to determine the order in which optimizer tables are executed by explain output and how to adjust SQL to affect optimizer The line plan.
2. Explain output format Description
The explain command returns a row of information for each table that appears in SQL to illustrate how optimizer will manipulate the table, and the table order listed in its output is the order in which the tables are processed when MySQL actually executes SQL.
MySQL to
Nested-loopThe algorithm handles all join operations, and the algorithm principle illustrates here that it is helpful to understand the behavior of join and suggest understanding.
explain each row of records that are output for each table contains the following fields:
The following are explained separately.
1) ID
This field identifies the SELECT statement ID, which is 1 if there are only 1 SELECT statements in SQL, even if it is a multi-table association query, or if SQL is the result of Union, the value is null.
2) Select_type
This field describes the type of the SELECT statement, and its possible values are as follows (from official website documents):
Where simple is the most common type, indicating that SQL contains only 1 SELECT statements; derived indicates that the data table (derived table) represented by the row is actually the output of the subquery contained in the FROM clause; the remaining types are easier to understand, read the official documents, Don't dwell on it here.
3) Table
This field indicates the table from which the data set represented by each row of the explain output is usually a specific table name, and when the dataset is the result of a union, its value may
is <unionm,n> When the dataset comes from derived table, its value may be <derivedN>. The M or n mentioned here is the value of the ID field.
4) Type
This field indicates how the tables are to be join, the value of which is more complex, in detail can refer to the official website documentation. There are only a few of the most common values listed here.
A. System/const
The const indicates that the "table" field above represents the dataset, with up to 1 rows of records hitting the query criteria for this step execution plan, such as where to execute the planned SQL
When a clause is compared to a constant by the primary key or unique index of a table, the value of the Type field corresponding to the execution plan is const.
System is just a special case of the const value, which indicates that there are only 1 rows of records in the data set that the plan is going to operate on.
They can only appear in the value of the Type field of a single-table query SQL.
B. Eq_ref
This value indicates that the associated field in the dataset for this step of the scheduled operation is an indexed field and
only 1 RecordsAn association condition that conforms to the data set that the scheduled operation was performed on.
it
is the optimal join type that can be obtained when querying against multiple tables(because it usually indicates that the associated field is the primary key or unique index of the table to which this step is scheduled to operate).
c. Ref
This value indicates that the associated field in the dataset for this step of the scheduled operation is an indexed field but
No, only 1 records .An association condition that conforms to the data set that the scheduled operation was performed on.
Records that meet the association criteria not only 1 indicate that the associated field is non-primary key or unique index, this join_type= ' ref ' scenario is reasonable when the number of records matching the associated conditions is less, butIt's obviously less efficient than join_type= ' eq_ref '。
d. Ref_or_null
The join type is similar to the ref scenario, but it indicates that MySQL does an extra search for fields that contain null values. For example, the following SQL join type is ref_or_null:
SELECT * from ref_table WHERE key_column=expr OR key_column is NULL;
E. Index_merge
This value indicates that MySQL will optimize the index merge for this step execution plan, and the SQL that triggers the index merge usually contains an ' or ' operation, as in the following common examples:
SELECT * from tbl_name WHERE key1 = ten OR key2 = 20;
SELECT * from Tbl_name WHERE (Key1 = ten OR Key2 =) and non_key=30;
SELECT * from T1, T2 WHERE (T1.key1 in (or t1.key2 like ' value% ') and T2.key1=t1.some_col;
SELECT * from t1, T2 WHERE t1.key1=1 and (T2.key1=t1.some_col OR t2.key2=t1.some_col2);
F. Range
This value indicates that the execution plan for this step
More than 1 records that only work on a single table and meet the query criteria, it may appear in SQL with in or between operations.
only the single-table operation scenario is also the difference from the previously mentioned Join_type value of ' ref ' scenarios, because ref may appear under the single table/multi-table JOIN operation scenario.
G. All
This value indicates that a full table scan of the dataset is performed by this step plan.This is the scene that must be optimized。 You can usually avoid a full table scan by properly indexing certain fields.
H. Index
This value indicates that MySQL scanned the index tree while executing this step plan, and all was the full table.
it may appear in two scenarios:
Case1.The field that the SELECT statement needs to return when you plan to query the dataset in this step is the leftmost prefix match set for that dataset's index field. If a federated index with 2 fields has been established in table A (F1, F2), then select F1 from A where F2 = ' yyy ' may trigger the MySQL scan of the index tree, in which case the extra field of the execution plan contains the "Using index "To indicate that it scanned the index tree because F1 was the 1 elements in the final prefix (F1, F2), and select F2 from A where F2 = ' yyy ' would scan the full table.
Case2.This step plans a full table scan in index order to find eligible data. In this case, the extra field of the execution plan does not contain "Using index",This full-table scan is also a must-be-optimized scenario。
5) Possible_keys
The value of this field is a field that may be indexed by MySQL, and if the value is null, No field is used as an index, so the query is not efficient, in which caseneed to optimize the index structure of the data table。
6) Key
The value of this field is the index that MySQL really uses.
It is worth noting that:The value of this field may not be the candidate index field listed by Possible_keys, for example, the field that the current query SQL is returning is the leftmost prefix match field of an indexed field in the datasheet, but the where condition of the SQL does not use the index field of the datasheet, then Possible_ The keys may be null, and the value of the key field may be the Data Table index field that can cover the field to be queried, and MySQL scans the index tree, albeit inefficient, but faster than scanning the whole table. This scenario is also the case1 mentioned earlier in this article explaining join_type= ' index '.
In addition, you can force MySQL to use the candidate index fields listed in Possible_keys in the SELECT statement with the help of "forces index" or "Using index".
7) Key_len
The value of this field indicates the length of the key field above, and when MySQL uses a federated index field as an index for SQL execution, the Key_len field can indicate to what extent MySQL is actually using the Federated index (the longest leftmost prefix match field). If the value of the key field is null, the Key_len field value is also null.
8) Ref
The value of this field indicates which column or constants in the data table will be used to compare with the index specified by the key field.
9) Rows
The value of this field indicates the number of rows that must be scanned when MySQL executes the corresponding query for the step plan.
This value is very useful for SQL optimization, and typically, the smaller the value, the higher the query efficiency.
) Extra
The value of this field contains additional information when MySQL executes query. This field may be a lot of value, detailed information can refer to the official website documentation.
In addition to explain, MySQL also supports explain extended commands to analyze optimizer's execution plan, which has a filtered field in the output, and can use the show warnings statement to analyze the output extra information.
3. How to optimize SQL based on the output of explain
if you understand the meaning behind each field in the explain output, Optimizing SQL performance on this basis can be efficient and well-grounded.
in engineering practice using explain to trouble the idea of shoot inefficient SQL, with the experience and ability of engineers, here are recommended a few technical information to stimulate.
1) A PDF share on OReilly's official website: Explain demystified
2) A shared document on SlideShare: MySQL Explain explained, which explains MySQL in a gradual way The output of explain and the typical idea of optimizing SQL on this basis is worth reading.
3) An article of the official technology blog of the United States: MySQL indexing principle and slow query optimization, this paper introduces the principle of B+tree, and gives several engineering scenarios using explain to optimize SQL, also worth intensive reading.
Resources
1. MySQL Doc:explain Syntax
2. MySQL doc:explain Output Format
3. MySQL Doc:select Syntax about Straight_join
4. MySQL doc:optimizing Queries with EXPLAIN
5. MySQL Doc:nested-loop Join Algorithms
6. PDF Presentation from Oreilly.com:Explain demystified
7. Slideshare:mysql Explain Explained
8. mysql indexing principle and slow query optimization
===================== EOF =====================
"MySQL notes" SQL optimizer-the output format of the explain command is detailed
