preface
What's the use of explain: in order to optimize the execution of SQL statements, you need 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 handles SQL statements. Check the execution plan to see if the executor processes SQL as we want.
The information contained in the explain execution plan is as follows:
id: Query serial number select_type: Query type table: Table name or alias partitions: Matching partitions type: Access type possible_keys: Possible indexes key: Index actually used key_len: Index length ref: Columns compared to index rows: Estimated rows filtered: Percentage of rows filtered by table criteria Extra: Additional information
analysis
The following describes the meaning of each column and the corresponding sql
The structure of the test table is as follows:
CREATE TABLE `demo`.`emp` ( `emp_id` bigint(20) NOT NULL, `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'full name', `empno` int(20) NOT NULL COMMENT 'Job number', `deptno` int(20) NOT NULL COMMENT 'Department number', `sal` int(11) NOT NULL DEFAULT 0 COMMENT 'sales volume', PRIMARY KEY (`emp_id`) USING BTREE, INDEX `u1`(`deptno`) USING BTREE, UNIQUE INDEX `u2`(`empno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
CREATE TABLE `demo`.`dept` ( `id` bigint(20) NOT NULL, `deptno` int(20) NOT NULL COMMENT 'Department code', `dname` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT 'Department name', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `dept_u1`(`deptno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
CREATE TABLE `demo`.`salgrade` ( `id` bigint(20) NOT NULL, `losal` int(20) NULL DEFAULT NULL, `hisal` int(20) NULL DEFAULT NULL, `emp_id` bigint(20) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
01. id column
The sequence number (a set of numbers) of the select query, indicating the order in which the select clause or operation table is executed in the query.
The id column is divided into three cases:
1. If the IDs are the same, the execution order is from top to bottom
mysql> 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 IDs are different, if it is a sub query, the id sequence number will increase. The larger the id value, the higher the priority, and the earlier it will be executed
mysql> explain select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');
3. The same and different IDS exist at the same time: the same can be considered as a group, which is executed from top to bottom. In all groups, the higher the id value, the higher the priority, and the first to execute
mysql> 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 wheree.deptno = (select d.deptno from dept d where d.dname = 'SALES');
02,select_type column
It is mainly used to distinguish the type of query, whether it is a general query, a joint query or a sub query
sample: simple query, excluding subquery and union
mysql> explain select * from emp;
Primary: if the query contains any complex sub query, the outermost query is marked as primary
mysql> explain select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');
Union: the second and subsequent select in the union, union all, and subqueries are marked Union
mysql> explain select * from emp where deptno = 10 union select * from emp where sal >2000;
DEPENDENT UNION: in a large query containing UNION or UNION ALL, if each small query depends on the outer query, the selection of other small queries except the leftmost one_ The value of type is DEPENDENT UNION.
mysql> 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: get the select of the result from the union table.
mysql> explain select * from emp where deptno = 10 union select * from emp where sal >2000;
Subquery: include subquery in the select or where list (not in the from clause)
mysql> explain select * from emp where sal > (select avg(sal) from emp) ;
dependent subquery: the first select in the subquery (not in the from clause) and depends on the external query.
mysql> explain select e1.* from emp e1 WHERE e1.deptno = (SELECT deptno FROM emp e2 WHERE e1.empno = e2.empno);
DERIVED: the subqueries contained in the FROM list are marked DERIVED, also known as DERIVED classes
mysql> explain select * from ( select emp_id,count(*) from emp group by emp_id ) e;
UNCACHEABLE SUBQUERY: the results of a subquery cannot be cached and the first row of the external link must be re evaluated. For the outer main table, the subquery cannot be materialized and needs to be calculated every time (time-consuming operation)
mysql> explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
uncacheable union: indicates that the query results of the union cannot be cached: no specific sql statement verification was found
03. table column
Which table, table name or alias the corresponding row is accessing, which may be a temporary table or union merge result set
1. If it is a specific table name, it indicates that data is obtained from the actual physical table. Of course, it can also be an alias of the table
2. The table name is in the form of derivedN, which indicates that the derived table generated by the query with id N is used
3. When there is a union result, the table name is in the form of union N1 and N2. N1 and N2 represent the id of the participating Union
04. type column
Type shows the access type. The access type indicates how I access our data. The easiest thing to think about is full table scanning. It is very inefficient to directly and violently traverse a table to find the required data.
There are many types of access, and the efficiency is from the best to the worst:
system > **const** > eq_ref > **ref** > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > **range** > **index** > ALL
Generally, to ensure that the query reaches at least range level, it is best to reach ref
all: full table scanning. You need to scan the whole table to find the required data rows from beginning to end. Generally, if there are such sql statements and the amount of data is large, it needs to be optimized.
mysql> explain select * from emp;
Index: the efficiency of full index scanning is better than that of all. There are two main cases. One is to overwrite the index during the current query, that is, the data we need can be obtained in the index, or the index is used for sorting, so as to avoid data reordering
mysql> explain select empno from emp;
Range: indicates that the range is limited when using the index query, and the query is performed within the specified range, which avoids the full index scanning of the index. Applicable operators: =, < >, >, > =, <, < =, is null, between, like, or in()
mysql> explain select * from emp where empno between 100 and 200;
index_subquery: uses indexes to associate subqueries without scanning the entire table
mysql> explain select * from emp where deptno not in (select deptno from emp)
However, in most cases, when the SELECT subquery is used, the MySQL query optimizer will automatically optimize the subquery into a join table query, so the type will not be displayed as index_subquery, but refunique_subquery: this connection type is similar to index_subquery, which uses a unique index
mysql> explain SELECT * from emp where emp_id not in (select emp.emp_id from emp );
In most cases, when using the SELECT subquery, the MySQL query optimizer will automatically optimize the subquery into a join table query, so the type will not be displayed as index_subquery, but eq_refindex_merge: multiple indexes need to be combined in the query process
MySQL > is not simulatedref_or_null: the query optimizer will select this access method when a field requires both an association condition and a null value
MySQL > is not simulatedref: non unique index is used to find data
mysql> explain select * from emp where deptno=10;
eq_ Ref: when querying an equivalent table, use the primary key index or the unique non empty index to find data (in fact, the unique index equivalent query type is not eq_ref but const)
mysql> explain select * from salgrade s LEFT JOIN emp e on s.emp_id = e.emp_id;
const: only one piece of data can be matched at most. The primary key or unique index is usually used for equivalent condition query
mysql> explain select * from emp where empno = 10;
System: the table has only one row of records (equal to the system table). This is a special case of const type. It usually does not appear and disk io is not required
mysql> explain SELECT * FROM `mysql`.`proxies_priv`;
05,possible_keys column
Displays one or more indexes that may be applied to this table. If there are indexes on the fields involved in the query, the indexes will be listed, but they may not be actually used by the query.
06. key column
If the index actually used is null, no index is used. If an overlay index is used in the query, the index overlaps with the select field of the query.
07,key_len column
Indicates the number of bytes used in the index. You can use key_len calculates the index length used in the query. The shorter the length, the better without losing precision.
The larger the index, the larger the storage space it occupies. In this way, the number and amount of io will increase, affecting the execution efficiency
08. ref column
Displays the columns or constants used by the previous table to find values in the index of the key column record
09. rows column
According to the statistical information of the table and the usage of the index, roughly estimate the number of rows to be read to find the required records. This parameter is very important. It directly reflects how much data the sql finds. The less the better when the purpose is completed.
10. filtered column
A pessimistic estimate of the percentage of records in a table that meet a certain condition (where clause or join condition).
11. extra column
Contains additional information.
using filesort: it indicates that mysql cannot use the index to sort, but can only use the sorting algorithm to sort, which will consume additional location
mysql> explain select * from emp order by sal;
using temporary: create a temporary table to save intermediate results. Delete the temporary table after the query is completed
mysql> explain select name,count(*) from emp where deptno = 10 group by name;
using index: This indicates that the current query overwrites the index and reads data directly from the index without accessing the data table. If the using where table name index appears at the same time, it is used to perform the search of index key values. If not, the surface index is used to read data instead of real search
mysql> explain select deptno,count(*) from emp group by deptno limit 10;
using where: use where for conditional filtering
mysql> explain select * from emp where name = 1;
using join buffer: use connection buffer
mysql> explain select * from emp e left join dept d on e.deptno = d.deptno;
impossible where: the result of the where statement is always false
mysql> explain select * from emp where 1=0;
Author: notes of Mr. Ji
Link:[ https://mp.weixin.qq.com/s?__biz=Mzg5MDYzMzM5MA==&mid=2247483863&idx=1&sn=5f5911b6e4f8e993ec8e7ee1edc0c279&chksm=cfd8ec16f8af6500551efc1e49dee22ac4bca052c5b8d36044007afafc6f591b091f220a0c69&scene=21#wechat_redirect]
Source: official account of WeChat