Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use the explain command in MySQL, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand.

What is the use of explain: in order to know how to optimize the execution of SQL statements, you need to look at the specific execution process of SQL statements to speed up the execution efficiency of SQL statements.

You can use explain+ SQL statements to simulate the optimizer's execution of SQL query statements to know how mysql handles sql statements. See if the executor handles SQL as we want by looking at the execution plan.

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 partition

Type: access type

Possible_keys: indexes that may be used

Key: the index actually used

Key_len: index length

Ref: the column compared to the index

Rows: estimated number of rows

Filtered: percentage of rows filtered by table criteria

Extra: additional information

The following is the meaning of the representation of each column and the corresponding sql.

The test uses mysql version 5.7. the three tables used are as follows

CREATE TABLE `demo`.`emp` (`demo`id` bigint (20) NOT NULL, `name`varchar (20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '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', 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 `deptno` (`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;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 situations:

1. If the id is the same, then 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 id is different, if it is a subquery, the sequence number of the id will be incremented. The higher the id value, the higher the priority, the first to 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 id exist at the same time: the same group can be considered as a group, which is executed sequentially from top to bottom. In all groups, the higher the id value is, the higher the priority is, and the first execution is.

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')

Select_ type column

It is mainly used to distinguish the type of query, whether it is a general query, a federated query or a subquery.

1. Sample: simple query, excluding subqueries and union

Mysql > explain select * from emp

2. Primary: if the query contains any complex subqueries, 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')

3. Union: the second and subsequent select in the union,union all and subquery are marked union

Mysql > explain select * from emp where deptno = 10 union select * from emp where sal > 2000

4. Dependent union: in a large query that contains UNION or UNION ALL, if each small query depends on the outer query, the select_type value of the other small query is DEPENDENT UNION except the leftmost small query.

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)

5. Union result: the select that gets the result from the union table.

Mysql > explain select * from emp where deptno = 10 union select * from emp where sal > 2000

6. Subquery: include subqueries in the select or where list (not in the from clause)

Mysql > explain select * from emp where sal > (select avg (sal) from emp)

7. 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)

8. Derived: subqueries included in the FROM list are marked as DERIVED, also known as derived classes

Mysql > explain select * from (select emp_id,count (*) from emp group by emp_id) e

9. UNCACHEABLE SUBQUERY: the result of a subquery cannot be cached. The first row of the outer link must be re-evaluated for the outer main table. The subquery cannot be materialized and needs to be calculated each time (time-consuming operation).

Mysql > explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size)

10. Uncacheable union: indicates that the query result of union cannot be cached: no specific sql statement was found for verification.

Table column

Which table, table name or alias the corresponding row is accessing, may be a temporary table or 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 an alias for the table.

2. The table name is in the form of derivedN, indicating that the derived table generated by the query whose id is N is used.

3. When there is union result, the table name is in the form of union N1 and so on, and N1 N2 represents the id that participates in union.

Type column

Type shows the access type, and the access type indicates how I access our data. The easiest thing to think about is full table scanning, directly and violently traversing a table to find the needed data, which is very inefficient.

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

In general, to ensure that the query reaches at least range level, it is best to reach ref

1. All: scan the whole table. You need to scan the whole table to find the required data rows from beginning to end. In general, if there is such a sql statement and the amount of data is large, then it needs to be optimized.

Mysql > explain select * from emp

2. Index: full index scanning is more efficient than all. There are two main cases. One is to overwrite the index in 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 reordering the data.

Mysql > explain select empno from emp

3. Range: it means that the scope is limited and the query is carried out within the specified range when using the index query, so as to avoid the full index scan of index. The applicable operators: =, >, > =, explain select * from emp where deptno not in (select deptno from emp)

But in most cases, when you use a select subquery, the MySQL query optimizer automatically optimizes the subquery to a join table query, so type is not displayed as index_subquery, but ref.

5. Unique_subquery: this connection type is similar to index_subquery and uses a unique index

Mysql > explain SELECT * from emp where emp_id not in (select emp.emp_id from emp)

In most cases, when you use a select subquery, the MySQL query optimizer automatically optimizes the subquery to a join table query, so type is not displayed as index_subquery, but eq_ref

6. Index_merge: multiple indexes are needed in the query process.

Mysql > is not simulated.

7. Ref_or_null: the query optimizer will choose this access method when both association conditions and null values are required for a field.

Mysql > is not simulated.

8. Ref: non-unique index is used to find data

Mysql > explain select * from emp where deptno=10

9. Eq_ref: when performing an equivalent join table query, use a primary key index or a unique non-null index for data lookup (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

10. Const: only one piece of data can be matched, usually using primary key or unique index for equivalence condition query.

Mysql > explain select * from emp where empno = 10

11. 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, and disk io is not required.

Mysql > explain SELECT * FROM `mysql`.`proxies _ priv`

Possible_ Keys column

Displays one or more indexes that may be applied to this table, and if an index exists on the fields involved in the query, the index will be listed, but not necessarily used by the query.

Key column

The index actually used, if null, is not used, and if an overlay index is used in the query, the index overlaps the select field of the query.

Key_ Lenn column

Indicates the number of bytes used in the index, and the index length used in the query can be calculated by key_len. The shorter the length, the better without losing precision.

The larger the index, the larger the storage space, so the number and quantity of io will increase, which will affect the execution efficiency.

Ref column

Displays the columns or constants used by the previous table to find values in the index of the key column record

Rows column

According to the statistical information of the table and the usage of the index, we can roughly estimate the number of rows that need to be read to find out the required records. This parameter is very important. How much data is found by the sql directly, the less the better when the goal is accomplished.

Filtered column

A pessimistic estimate of the percentage of records in a table that meet a condition (where clause or join condition).

Extra column

Contains additional information.

1. Using filesort: indicates that mysql cannot use the index for sorting, but can only use the sorting algorithm for sorting, which will consume extra space

Mysql > explain select * from emp order by sal

2. Using temporary: create a temporary table to store the intermediate results, and delete the temporary table after the query is completed

Mysql > explain select name,count (*) from emp where deptno = 10 group by name

3. Using index: this indicates that the current query overrides the index, reading data directly from the index without accessing the data table. If the using where table name index is used to perform the lookup of the index key value at the same time, if not, the surface index is used to read the data, not to actually find it.

Mysql > explain select deptno,count (*) from emp group by deptno limit 10

4. Using where: conditional filtering using where

Mysql > explain select * from emp where name = 1

5. Using join buffer: use connection caching

Mysql > explain select * from emp e left join dept d on e.deptno = d.deptno

6. The result of the impossible where:where statement is always false

Mysql > explain select * from emp where 1: 0

Thank you for reading this article carefully. I hope the article "how to use explain commands in MySQL" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.