| |
EXPLAIN statement can be used to obtain some information on when SQL execution, such as the connection order of the table, the table manner as the like. By the relevant information for further analysis, we can add the appropriate index on the table, as well as to optimize the connection order, usage tips, and so on means to achieve the purpose of SQL allows efficient operation. This article describes the use of EXPLAIN and give relevant examples.
One, EXPLAIN Overview
EXPLAIN statement is primarily used for parsing SQL execution plan and take appropriate manner to optimize efficiency by analyzing running SQL execution plan.
EXPLAIN statement output generally includes the id column, select_type, table, type, possible_keys, key column information, etc.
MySQL 5.6.3 After support SELECT, DELETE, INSERT, REPLACE, and UPDATE.
EXPLAIN EXTENDED supports some additional information related to the implementation plan
EXPLAIN PARTITIONS supports partition table query execution plan infos
Two, EXPLAIN output Column Description
- The following by way of example to show EXPLAIN output column
(Root @ localhost) [test]> explain select sum (amount) from customer a,
-> Payment b where 1 = 1 and a.customer_id = b.customer_id and
-> Email='abc@abc.org '\ G
*************************** 1. row ******************** *******
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 590
Extra: Using where
*************************** 2. row ******************** *******
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: test.a.customer_id
rows: 14
Extra:
1, showing the significance of each column
Column Meaning
------ ------------------------------------
id The SELECT identifier
select_type The SELECT type
table The table for the output row
partitions The matching partitions
type The join type
possible_keys The possible indexes to choose
key index actually chosen
key_len The length of the chosen key
ref The columns compared to the index
rows Estimate of rows to be examined
filtered Percentage of rows filtered by table condition
Extra Additional information
2, the detailed description of each column
id:
It contains a number that represents the order of the operating table or select clause of the query execution
The same id, the execution order from top to bottom, or the greater the id value (usually subquery produces) a higher priority, the first to be executed
id if the same can be considered as a group, down from the sequential execution; in all groups, id value, the higher the priority, the more the first implementation
select_type:
Each type represents the query select clause (simple OR complicated)
select_type Value Meaning
------------- ------------------------------------- ----------
SIMPLE Simple SELECT (not using UNION or subqueries)
PRIMARY Outermost SELECT outermost select
UNION Second or later SELECT statement in a UNION
DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT Result of a UNION.
SUBQUERY First SELECT in subquery
DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query (usually for the relevant sub-query)
DERIVED Derived table SELECT (subquery in FROM clause)
MATERIALIZED Materialized subquery
UNCACHEABLE SUBQUERY A subquery for which the result can not be cached and must be reevaluated
for each row of the outer query
UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable
subquery (see UNCACHEABLE SUBQUERY)
table:
From which table (table name) on the output line record, it could be the following values:
< UnionM, N>: The row refers to the union of the rows with id values of M and N.
< DerivedN>: The row refers to the derived table result for the row with an id value of N.
A derived table may result, for example, from a subquery in the FROM clause.
< SubqueryN>: The row refers to the result of a materialized subquery for the row with an id value of N.
partitions:
Which match the query records from a partition when using EXPLAIN, partitions PARTITIONS keyword is specified
type:
Connection Type
system table only one row
const tables at most only one line matching, common for the primary key or unique index comparison
eq_ref each table combined with the previous row in the table to read only one line, which is in addition to the system, const best kind,
Characterized by the use =, and all parts are involved in join indexes and index is a primary key or unique key blank
ref If every match only a small number of lines, it is better kind, use = or < =>, may be left covering index or a primary key or unique key
Full-text search fulltext
Similar ref_or_null the ref, but including NULL
index_merge indicates that an index merge optimization (including the intersection, union and intersection between the union), but does not include cross tables and full-text indexing.
The more complex, the current understanding of the range of an index scan combined single-table (if the cost estimate to be better than normal range, then)
unique_subquery in the sub-query is value in (select ...) to the form "select unique_key_column" subquery replacement.
PS: it is not necessarily in the sub-clause of the query is inefficient!
index_subquery above, but the form as "select non_unique_key_column" subquery replacement
Range range of constant values
. Index a query when the index is covered, that is, all the data are available (Extra in Using Index) from the time the index tree acquired;
. B in order to find the index data rows full table scan (no Using Index) from the index;
. C If the Extra Using Index and Using Where occur simultaneously, then the key is to use the index to find the meaning;
d. As appears alone, it is used to read the index instead of the line read, but not for finding
all full table scan
possible_keys:
Which noted that MySQL can use an index to find the rows in the table.
On the field the queries involved if there is an index, the index will be listed, but not necessarily queries.
If re-classified as NULL, indicating that the query does not use the relevant index on the table currently considering whether it is necessary to add index
key
Display index MySQL actually used in the query, if not use the index, shown as NULL
There may also be situations possible_keys key is not equal, that possible_keys not suitable to extract the required line
And the column selected by the query while using other more efficient index
TIPS: If the query uses a covering index, the index appears only in the key list
key_len
The number of bytes used represents an index, you can calculate the index used in the query through the column length
ref
The above table represents the connection to match the conditions, namely which columns or constants are used to find the value of the index column
rows
MySQL Table showing statistics and index selection, the estimated number of rows to find the desired record to be read
For InnoDB, the estimated value, not necessarily accurate
Extra
However, additional information is very important unsuitable appear in other columns
Third, the use EXPLAIN EXTENDED example
(Root @ localhost) [test]> explain extended select * from city where country_id in
-> (Select country_id from country where country = 'China') and 1 = 1 \ G
*************************** 1. row ******************** *******
id: 1
select_type: SIMPLE
table: country
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 109
filtered: 100.00
Extra: Using where
*************************** 2. row ******************** *******
id: 1
select_type: SIMPLE
table: city
type: ref
possible_keys: idx_fk_country_id
key: idx_fk_country_id
key_len: 2
ref: test.country.country_id
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
(Root @ localhost) [test]> show warnings \ G
*************************** 1. row ******************** *******
Level: Note
Code: 1003
Message: / * select # 1 * / select `city`.`city_id` AS` city_id`, `city`.`city` AS` city`, `city`.`country_id`
AS `country_id`,` city`.`last_update` AS `last_update` from` test`.`country` join `test`.`city` where
(( `City`.`country_id` =` country`.`country_id`) and ( `country`.`country` = 'China'))
1 row in set (0.00 sec)
- It can be seen from the above extended use, the more filtered query column
- Secondly, the original SQL statement is rewritten in real time execution, that the original condition 1 = 1 is removed
- For complex SQL statements we can get a clearer real SQL statements to be executed by the way
Four, EXPLAIN PARTITIONS Example
(Root @ localhost) [test]> CREATE TABLE `actor_part` (
-> `Actor_id` smallint (5) unsigned NOT NULL AUTO_INCREMENT,
-> `First_name` varchar (45) NOT NULL,
-> `Last_name` varchar (45) NOT NULL,
-> `Last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY ( `actor_id`),
-> KEY `idx_actor_last_name` (` last_name`)
->) Partition by hash (actor_id) partitions 4;
Query OK, 0 rows affected (0.11 sec)
(Root @ localhost) [test]> insert into actor_part select * from actor;
Query OK, 200 rows affected (0.02 sec)
Records: 200 Duplicates: 0 Warnings: 0
(Root @ localhost) [test]> explain select * from actor_part where actor_id = 10; - when not in use partitions
+ ---- + ------- + ------------ + ------------- + --------- ------ + --------- + --------- + ------- + ------ + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------- + ------------ + ------------- + --------- ------ + --------- + --------- + ------- + ------ + ------- +
| 1 | SIMPLE | actor_part | const | PRIMARY | PRIMARY | 2 | const | 1 | NULL |
+ ---- + ------- + ------------ + ------------- + --------- ------ + --------- + --------- + ------- + ------ + ------- +
1 row in set (0.00 sec)
(Root @ localhost) [test]> explain partitions select * from actor_part where actor_id = 10; - when using partitions
+ ---- + + ------------ + ------------ + ------------- ---- --- + --------------- + --------- + --------- + ------- + - ---- + ------- +
| Id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + + ------------ + ------------ + ------------- ---- --- + --------------- + --------- + --------- + ------- + - ---- + ------- +
| 1 | SIMPLE | actor_part | p2 | const | PRIMARY | PRIMARY | 2 | const | 1 | NULL |
+ ---- + + ------------ + ------------ + ------------- ---- --- + --------------- + --------- + --------- + ------- + - ---- + ------- +
1 row in set (0.00 sec)
- More out of the partitions column |
|
|
|