introduce
This article mainly summarizes the knowledge of Daniel on the Internet, briefly introduces how to use the mysql execution plan, and judges how to optimize and whether the index is optimal according to the execution plan.
The execution plan can display the estimated query statement execution plan, from which you can analyze whether the query execution is optimal, which is helpful to optimize the statements that do not use the index. EXPLAIN returns a row of information for each query and lists ordered tables. MySQL reads them when processing statements. MySQL solves all connections using the nested connection method. This means reading the first row and then matching all the rows of the second table, the third table, or even more tables. When all tables are being processed, MySQL will output the queried columns, trace back to the table and continue to search until all rows are found, and read the next row from the table until the program continues to process the next table. Using the keyword EXTENDED, EXPLAIN handles some additional information seen through SHOW WARNINGS. EXPLAIN EXTENDED displays these filtered columns.
Syntax:
EXPLAIN <select statement>;
The output table fields are as follows:
mysql> explain select * from mysql.user where user='root';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+ Column | JSONName | Meaning |
|---|---|---|
id | select_id | Query id. The larger the id, the higher the priority; The same id is executed from top to bottom; |
select_type | None | Type of query |
table | table_name | Table to query |
partitions | partitions | Thematching partitions |
type | access_type | line type |
possible_keys | possible_keys | Possible indexes |
key | key | Index actually used |
key_len | key_length | Index length used |
ref | ref | Which column or constant is used with the index key column in the query |
rows | rows | Estimate the number of rows for the query |
filtered | filtered | Percentage of rows filtered by criteria |
Extra | None | Some additional information to resolve the query |
The following three fields are mainly exemplified: select_type ,type,Extra
[select_type]
alue | JSONName | Meaning |
|---|---|---|
SIMPLE | None | Simple query (without UNION or subquery) |
PRIMARY | None | Outer query |
UNION | None | The second or subsequent statement in UNION |
DEPENDENTUNION | dependent (true) | The second or subsequent statement in the UNION is independent of the external query |
UNIONRESULT | union_result | Result of UNION |
SUBQUERY | None | First SELECT in subquery |
DEPENDENTSUBQUERY | dependent (true) | The first SELECT in the subquery is independent of the external query |
DERIVED | None | Subquery in FROM clause |
MATERIALIZED | materialized_from_subquery | Materialized subquery (not sure what kind of query statement it is?) |
UNCACHEABLESUBQUERY | cacheable (false) | For sub queries whose result set cannot be cached, each row of the outer query must be re evaluated |
UNCACHEABLEUNION | cacheable (false) | The second or subsequent statement in the UNION belongs to a non cacheable subquery |
To create a test table:
create table tabname (
id int auto_increment not null primary key,
name varchar(10) null,
indate datetime null,
tid int null,
key(tid),
key(indate)
)engine=innodb;
create table tabname2 (
id int auto_increment not null primary key,
name varchar(10) null,
indate datetime null,
tid int null,
key(tid),
key(indate)
)engine=myisam;
insert into tabname(name,indate,tid) values('love',now(),2),('lucky',now(),3),('passion',now(),4);
insert into tabname2(name,indate,tid) values('love',now(),2),('lucky',now(),3),('passion',now(),4); #SIMPLE
create table tabname (
id int auto_increment not null primary key,
name varchar(10) null,
indate datetime null,
tid int null,
key(tid),
key(indate)
)engine=innodb;
create table tabname2 (
id int auto_increment not null primary key,
name varchar(10) null,
indate datetime null,
tid int null,
key(tid),
key(indate)
)engine=myisam;
insert into tabname(name,indate,tid) values('love',now(),2),('lucky',now(),3),('passion',now(),4);
insert into tabname2(name,indate,tid) values('love',now(),2),('lucky',now(),3),('passion',now(),4); #PRIMARY / DERIVED
mysql> explain select * from (select * from tabname) as a;
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3 | |
| 2 | DERIVED | tabname | ALL | NULL | NULL | NULL | NULL | 3 | |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+ #PRIMARY / UNION / UNION RESULT
mysql> explain select * from tabname union select * from tabname;
mysql> explain select * from tabname union all select * from tabname;
+----+--------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+------+---------------+------+---------+------+------+-------+
| 1 | PRIMARY | tabname | ALL | NULL | NULL | NULL | NULL | 3 | |
| 2 | UNION | tabname | ALL | NULL | NULL | NULL | NULL | 3 | |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+------+---------------+------+---------+------+------+-------+ mysql> explain select * from tabname where id=(select max(id) from tabname);
+----+-------------+---------+-------+---------------+---------+---------+-------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+------------------------------+
| 1 | PRIMARY | tabname | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+------------------------------+ #PRIMARY / SUBQUERY
#PRIMARY / DEPENDENT SUBQUERY
[plain] view plain copy
mysql> explain select * from tabname a where exists(select 1 from tabname b where a.id=b.id);
mysql> explain select *,(select name from tabname b where a.id=b.id) from tabname a;
mysql> explain select * from tabname where id not in(select id from tabname);
+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | tabname | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
| 2 | DEPENDENT SUBQUERY | tabname | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using index |
+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+ #PRIMARY / DEPENDENT UNION / DEPENDENT SUBQUERY / UNION RESULT
mysql> explain select * from tabname where id in (select id from tabname union select id from tabname);
+----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | tabname | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
| 2 | DEPENDENT SUBQUERY | tabname | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using index |
| 3 | DEPENDENT UNION | tabname | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using index |
| NULL| UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+ [type]
type | Meaning |
|---|---|
system | The table has only one row of data (= system table). This is a special case of const connection type. |
const | The table has at most one matching row and is read at the beginning of the query. Because there is only one value, the optimizer treats the column value as a constant. Used when the primarykey or unique index is compared as a constant. |
eq_ref(engine=myisam) | Read a row from the result set of the previous table, which is the best connection type except system and const. It is used when an index with PRIMARYKEY or unique null is used. |
ref | For all rows of the result set matching query in the previous table, this type is used when the connection uses the index key, or the index is not primary key and UNIQUE. If indexes are used to match a small number of rows, it is a good connection type. |
ref_or_null | The connection type is similar to ref, except that the rows searched contain NULL values, and MySQL makes additional searches. |
fulltext | An error occurred while using full-text indexing. |
index_merge | Index merge optimization is used. (unsuccessful) |
unique_subquery | This type replaces ref with the format of the following sub queries: valueIN (SELECTprimary_key FROMsingle_table WHERE some_expr) |
index_subquery | And unique_subquery is similar, but change the primary key to a non unique index: valueIN (SELECTkey_columnFROMsingle_table WHERE some_expr) |
range | Retrieves rows within a given range using an index. |
index | The connection type is the same as ALL, except that the index tree is scanned. If the fields of the query are ALL in the index column, the index type is used; otherwise, the ALL type is used. |
ALL | For the result set of the previous table, a full table scan was performed. The worst type should consider query optimization! |
Query type performance from excellent to poor:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
#system
mysql> explain select id from(select id from tabname where id=1) as a;
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | tabname | const | PRIMARY | PRIMARY | 4 | | 1 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+ #const
mysql> explain select * from tabname as a,tabname as b where a.id=b.id and a.id=1;
mysql> explain select * from tabname where id=1;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | tabname | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+ #eq_ref(engine=myisam)
mysql> explain select * from tabname2 as a,tabname2 as b where a.id=b.id;
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+
| 1 | SIMPLE | a | ALL | PRIMARY | NULL | NULL | NULL | 3 | |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.a.id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+ #ref
mysql> explain select * from tabname as a,tabname as b where a.tid=b.tid and a.tid=2;
mysql> explain select * from tabname where tid=2;
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | tabname | ref | tid | tid | 5 | const | 1 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+ #ref_or_null
mysql> explain select id,tid from tabname where tid=2 or tid is null;
+----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+
| 1 | SIMPLE | tabname | ref_or_null | tid | tid | 5 | const | 2 | Using where; Using index |
+----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+ #fulltext
mysql> explain select id,tid from tabname where tid=2 or tid is null;
+----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+
| 1 | SIMPLE | tabname | ref_or_null | tid | tid | 5 | const | 2 | Using where; Using index |
+----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+ #index_ Merge (unsuccessful)
mysql> explain select * from tabname where tid>1 or indate<now(); mysql> explain select * from tabname where (tid>1 or indate>now()) AND name<'kk';
#unique_subquery
mysql> explain select * from tabname where tid in(select id from tabname);
mysql> explain select * from tabname where id in(select id from tabname);
+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | tabname | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
| 2 | DEPENDENT SUBQUERY | tabname | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using index |
+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+ #index_subquery
mysql> explain select * from tabname where tid in(select tid from tabname);
mysql> explain select * from tabname where id in(select tid from tabname);
+----+--------------------+---------+----------------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------+----------------+---------------+------+---------+------+------+--------------------------+
| 1 | PRIMARY | tabname | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
| 2 | DEPENDENT SUBQUERY | tabname | index_subquery | tid | tid | 5 | func | 1 | Using index; Using where |
+----+--------------------+---------+----------------+---------------+------+---------+------+------+--------------------------+ #range
mysql> explain select * from tabname where tid between 1 and 2;
mysql> explain select * from tabname where id>1;
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | tabname | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+ #index
mysql> explain select id,tid from tabname; mysql> explain select tid from tabname; +----+-------------+---------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tabname | index | NULL | tid | 5 | NULL | 3 | Using index | +----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
#ALL
mysql> explain select * from tabname where tid<>2;
mysql> explain select * from tabname;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | tabname | ALL | NULL | NULL | NULL | NULL | 3 | |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+ [Extra]
This column outputs additional information about how MySQL solves queries. (some common are listed below)
Extra | Meaning |
|---|---|
usingwhere | Use filter conditions |
usingindex | Find all columns from the index tree |
usingtemporary | Using temporary tables to store result sets occurs when using group by and order by |
selecttables optimized away | Use min(),max(), or count(*) without groupby |
usingfilesort | Sorted |
notexists | After matching a row in the leftjoin, the query will not continue |
distinct | After finding the first matching row, MySQL will stop searching the current row |
impossiblewhere | where clause total number of failed queries |
impossiblehaving | Total number of queries with failed having clauses |
usingjoin buffer | Use connection cache |
Usingindex for group-by | Similar to using index, the fields can be found in the index when using group by |
#using where
mysql> explain select * from tabname where id>2;
mysql> explain select * from tabname where tid=2;
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | tabname | ref | tid | tid | 5 | const | 1 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+ #using index
mysql> explain select tid from tabname;
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tabname | index | NULL | tid | 5 | NULL | 3 | Using index |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+ #using temporary
mysql> explain select distinct name from tabname;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------+
| 1 | SIMPLE | tabname | ALL | NULL | NULL | NULL | NULL | 3 | Using temporary |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------+ #select tables optimized away
mysql> explain select max(tid) from tabname;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ #using filesort
mysql> explain select id,name from tabname group by id,name;
mysql> explain select * from tabname order by name;
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | tabname | ALL | NULL | NULL | NULL | NULL | 3 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+ #not exists
mysql> explain select * from tabname a left join tabname b on a.id=b.id where b.id is null;
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------------------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 3 | |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.a.id | 1 | Using where; Not exists |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------------------+ #distinct
mysql> explain select distinct a.id from tabname a left join tabname b on a.id=b.id; +----+-------------+-------+--------+---------------+---------+---------+-----------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-----------+------+------------------------------+ | 1 | SIMPLE | a | index | NULL | tid | 5 | NULL | 3 | Using index; Using temporary | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.a.id | 1 | Using index; Distinct | +----+-------------+-------+--------+---------------+---------+---------+-----------+------+------------------------------+
#impossible where
mysql> explain select * from tabname where 1=2;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+ #impossible having
mysql> explain select id,count(*) from tabname group by id having 1=2; mysql> explain select count(*) from tabname having 1=2; +----+-------------+-------+------+---------------+------+---------+------+------+-------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible HAVING | +----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
Use EXTENDED now:
Syntax:
- EXPLAIN EXTENDED <select statement>;
Analysis without and with extended:
mysql> explain select tid from tabname;
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tabname | index | NULL | tid | 5 | NULL | 3 | Using index |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain extended select tid from tabname;
+----+-------------+---------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tabname | index | NULL | tid | 5 | NULL | 3 | 100.00 | Using index |
+----+-------------+---------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec) You can see that when using extended, there is one more warning at the bottom of the output. You can use show warnings to view:
mysql> show warnings \G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`tabname`.`tid` AS `tid` from `test`.`tabname`
1 row in set (0.00 sec)
ERROR:
No query specified show warnings shows how table and field names are standardized in the optimizer, and what the select statement looks like after rewriting and optimizing rules.
More references:
EXPLAIN EXTENDED Output Format
Publisher: full stack programmer, stack length, please indicate the source for Reprint: https://javaforall.cn/119681.html Original link: https://javaforall.cn