MySQL execution plan

Time:2020-12-5

In the application scenarios of enterprises, in order to know how to optimize the execution of SQL statements, it is necessary to view the specific execution process of SQL statements to speed up the execution efficiency of SQL statements. You can use the explain + SQL statement to simulate the optimizer to execute SQL query statements, so as to know how MySQL processes SQL statements.

1. Information contained in the execution plan

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 The 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

1. id

The sequence number of a select query, containing a set of numbers, represents the order in which the select clause or operation table is executed in the query

The ID number can be divided into three situations:

1. If the ID is the same, the execution order is from top to bottom

 explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal;

2. If the ID is different, if it is a subquery, the sequence number of the ID will be increased. The higher the ID value, the higher the priority, and the earlier it is executed

 explain select * from emp e where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');

3. The same ID and different ID exist at the same time: the same can be considered as a group and executed in order from top to bottom. In all groups, the higher the ID value, the higher the priority, the earlier the execution

 explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');

2. select_type

It is mainly used to distinguish the type of query, ordinary query, union query or subquery

select_type Value Meaning
SIMPLE Simple SELECT (not using UNION or subqueries)
PRIMARY 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
DERIVED Derived table
UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated 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)
--Sample: simple query without subquery and union
 explain select * from emp;
 ​
 --Primary: if the query contains any complex subqueries, the outermost query is marked as primary
 explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;
 ​
 --Union: if the second select appears after the union, it is marked as a union
 explain select * from emp where deptno = 10 union select * from emp where sal >2000;
 ​
 --Dependent Union: similar to union, the dependency here means that the result of union or union all will be affected by external tables
 explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)
 ​
 --Union result: select to get the result from the union table
 explain select * from emp where deptno = 10 union select * from emp where sal >2000;
 ​
 --Subquery: include subqueries in the select or where list
 explain select * from emp where sal > (select avg(sal) from emp) ;
 ​
 --dependent  subquery:subquery The subqueries of are affected by external table queries
 explain select * from emp e where e.deptno in (select distinct deptno from dept);
 ​
 --Subqueries that appear in the derived: from clause are also called derived classes,
 explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;
 ​
 --Uncacheable subquery: indicates that the results using subqueries cannot be cached
  explain select * from emp where empno = (select empno from emp where [email protected]@sort_buffer_size);

 --Uncacheable Union: indicates that the query result of union cannot be cached: SQL statement is not verified

3. table

Which table, table name or alias the corresponding row is accessing may be a temporary table or a union merge result set. 1. If it is a specific table name, it indicates that the data is obtained from the actual physical table. Of course, it can also be the alias of the table

2. The table name, in the form of derived n, represents the derived table generated by the query with ID n

3. When there is a union result, the table name is in the form of union N1, N2, and so on. N1 and N2 represent the ID participating in the union

4. type

Type shows the access type, and the access type indicates how I access our data. The easiest thing to think about is to scan the whole table, and directly and violently traverse a table to find the required data. The efficiency is very low. There are many types of access. The efficiency from the best to the worst is as follows:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

In general, it is necessary to ensure that the query reaches at least range level, and it is better to achieve Ref

--All: full table scan. In general, if such SQL statements appear and the amount of data is relatively large, it needs to be optimized.
 explain select * from emp;
 ​
 --Index: the efficiency of full index scanning is better than that of all. There are two main situations. One is to cover the index during the current query, that is, the data we need can be obtained from the index, or the index is used for sorting, so as to avoid data reordering
 explain  select empno from emp;
 ​
 --Range: when using index query, the range is limited and the query is carried out within the specified range, thus avoiding the full index scanning of the index. The applicable operators are =, < >, >, > =, < =, is null, between, like, or in () 
 explain select * from emp where empno between 7000 and 7500;
 ​
 --index_ Subquery: use the index to associate subqueries and no longer scan the whole table
 explain select * from emp where emp.job in (select job from t_job);
 ​
 --unique_ Subquery: the connection type is similar to index_ Subquery, which uses a unique index
  explain select * from emp e where e.deptno in (select distinct deptno from dept);

 --index_ Merge: in the query process, multiple indexes are required to be used in combination, which is not simulated
 ​
 --ref_ Or_ Null: the query optimizer will select this access method when a field requires both the associated condition and the null value
 explain select * from emp e where  e.mgr is null or e.mgr=7369;
 ​
 --Ref: non unique index is used to search data
  create index idx_3 on emp(deptno);
  explain select * from emp e,dept d where e.deptno =d.deptno;
 ​
 --eq_ Ref: data lookup using unique index
 explain select * from emp,emp2 where emp.empno = emp2.empno;
 ​
 --Const: this table has at most one matching row,
 explain select * from emp where empno = 7369;

 --System: the table has only one row of records (equal to the system table). This is a special case of const type, which usually does not appear

5. possible_keys

Display the indexes that may be applied to this table. If there is an index on the fields involved in the query, the index will be listed, but not necessarily used by the query

 explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

6. key

If the index used is null, the index is not used. If the overlay index is used in the query, the index overlaps with the select field of the query.

 explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

7. key_len

Indicates the number of bytes used in the index, which can be accessed by key_ Len calculates the index length used in the query. The shorter the length, the better without losing precision.

 explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

8. ref

Shows which column of the index is used, and if possible, a constant

 explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

9. rows

According to the statistical information of the table and the index usage, it can roughly estimate the number of rows to be read to find out the required records. This parameter is very important. The less data the SQL directly reflects, the less the better

 explain select * from emp;

10. extra

Contains additional information.

--Using filesort: indicates that MySQL cannot sort by using index, but can only use sorting algorithm to sort, which will consume additional positions
 explain select * from emp order by sal;
 ​
 --Using temporary: create a temporary table to save the intermediate results, and delete the temporary table after the query is completed
 explain select ename,count(*) from emp where deptno = 10 group by ename;
 ​
 --Using index: This indicates that the current query will override the index and read data directly from the index without accessing the data table. If there is a using where table name at the same time, the index is used to perform the lookup of the index key value. If not, the surface index is used to read the data instead of the actual search
 explain select deptno,count(*) from emp group by deptno limit 10;
 ​
 --Using where: use where for conditional filtering
 explain select * from t_user where id = 1;
 ​
 --Using join buffer: using connection cache, the situation is not simulated
 ​
 --Impossible where: the result of a where statement is always false
 explain select * from emp where empno = 7469;

sql

This work adoptsCC agreementThe author and the link to this article must be indicated in the reprint

jzhao