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 verified3. 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 appear5. 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;This work adoptsCC agreementThe author and the link to this article must be indicated in the reprint