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

This article introduces you what is the implementation plan of MySQL, the content is very detailed, interested friends can refer to, hope to be helpful to you.

I. Preface

When we have worked for a certain number of years, there are some knowledge points that we need to know, such as the SQL execution plan asked by the interviewer today. When we execute a SQL, we can directly correspond to the results, but you have no idea how deep and dark the tunnel it will go through, sifting through connectors, query caches, analyzers, optimizers and executors. It is possible to show it in front of us, sometimes when you wait for a long time, but show timeout, at this time you want to smash the computer, but when you read today's SQL implementation plan, you no longer have to smash the computer. After reading this article, you will know that this is not a thing. Let's reveal the secret of it.

In the actual application scenario, in order to know how to optimize the execution of SQL statements, we need to look at the specific execution process of SQL statements in order to speed up the execution efficiency of SQL statements. Explain+ SQL statements are usually used to simulate the execution of SQL query statements by the optimizer to know how mysql handles sql statements.

Official website address: https://dev.mysql.com/doc/refman/5.5/en/explain-output.html

First of all, let's start with the following sql statement, which will have columns such as id, select_type, table, and so on. These are the information contained in our execution plan. What we need to figure out is what these columns are for and how many values there may be in each column.

Explain select * from emp

II. The information contained in the implementation plan

The considerate small farmer has copied the sql and only needs to put it into the database to execute it. It is convenient, simple and quick-- herding and small farmers.

Table creation statements: SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` (`DEPTNO` int NOT NULL, `DNAME` varchar (14) DEFAULT NULL, `LOC` varchar (13) DEFAULT NULL, PRIMARY KEY (`DEPTNO`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `dept` VALUES ('10 degrees, 'ACCOUNTING',' NEW YORK'); INSERT INTO `dept` VALUES ('20 seconds, 'RESEARCH',' DALLAS'); INSERT INTO `dept` VALUES ('30 cycles, 'SALES',' CHICAGO') INSERT INTO `dept` VALUES ('40th,' OPERATIONS', 'BOSTON'); DROP TABLE IF EXISTS `emp` CREATE TABLE `emp` (`EMPNO` int NOT NULL, `ENAME` varchar (10) DEFAULT NULL, `JOB` varchar (9) DEFAULT NULL, `MGR` int DEFAULT NULL, `HIREDATE` date DEFAULT NULL, `SAL` double (7) DEFAULT NULL, `COMM` double (7) DEFAULT NULL, `DEPTNO` int DEFAULT NULL, PRIMARY KEY (`EMPNO`), KEY `idx_ job` (`JOB`), KEY `jdx_ mgr` (`MGR`), KEY `jdx_ 3` (`DEPTNO`), KEY `idx_ 3` (`DEPTNO`) ENGINE=InnoDB DEFAULT CHARSET=utf8 INSERT INTO `emp` VALUES ('7369,' SMITH', 'CLERK',' 7902, '1980-12-17,' 800.005, null,'20'); INSERT INTO `emp` VALUES ('7499,' ALLEN', 'SALESMAN',' 7698, '1981-02-20,' 1600.00, '300.00,' 3030') INSERT INTO `emp` VALUES ('7521,' WARD', 'SALESMAN',' 7698, '1981-02-22,' 1250.005, '500.005,' 30'); INSERT INTO `emp` VALUES ('7566,' JONES', 'MANAGER',' 7839, '1981-02-02,' 2975.00, null,'20') INSERT INTO `emp` VALUES ('7654,' MARTIN', 'SALESMAN',' 7698, '1981-09-28,' 1250.009, '1400.005,' 30'); INSERT INTO `emp`VALUES ('7698,' BLAKE', 'MANAGER',' 7839th, '1981-01-05mm,' 2850.009, null, '30') INSERT INTO `emp` VALUES ('7782,' CLARK', 'MANAGER',' 7839, '1981-09-06,' 2450.005, null,'10'); INSERT INTO `emp` VALUES ('7839,' KING', 'PRESIDENT', null,' 1981-11-17, '5000.009, null,' 10') INSERT INTO `emp` VALUES ('7844,' TURNER', 'SALESMAN',' 7698, '1981-09-08,' 150-0.005, '0.005,' 30'); INSERT INTO `emp` VALUES ('7900,' JAMES', 'CLERK',' 7698, '1981-12-03,' 950.00, null,'30') INSERT INTO `emp` VALUES ('7902,' FORD', 'ANALYST',' 7566, '1981-12-03,' 3000.00mm, null, '20'); INSERT INTO `emp`VALUES (' 7934mm, 'MILLER',' CLERK', '7782mm,' 1982-01-23mm, '1300.00miles, null,' 10'); DROP TABLE IF EXISTS `emp2` CREATE TABLE `emp2` (`id`int NOT NULL AUTO_INCREMENT, `empno` int DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; INSERT INTO `emp2` VALUES ('1mm,' 111'); INSERT INTO `emp2` VALUES ('2mm,' 222'); DROP TABLE IF EXISTS `salgrade`; CREATE TABLE `salgrade` (`GRADE` int NOT NULL, `LOSAL` double DEFAULT NULL, `HISAL` double DEFAULT NULL, PRIMARY KEY (`GRADE`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `salgrade` VALUES ('1mm,' 700mm, '1200') INSERT INTO `salgrade` VALUES ('2levels,' 12010s, '1400'); INSERT INTO `salgrade` VALUES (' 3months, '1401tons,' 2000'); INSERT INTO `salgrade` VALUES ('4cycles,' 2001 cycles, '3000'); INSERT INTO `salgrade` VALUES (' 5cycles, '3001cycles,' 9999'); DROP TABLE IF EXISTS `tjob` CREATE TABLE `tjob` (`id` int NOT NULL AUTO_INCREMENT, `job` varchar (9) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, PRIMARY KEY (`id`), KEY `j` (`job`) ENGINE=InnoDB DEFAULT CHARSET=utf8;2.1 id

The sequence number of a select query that contains a set of numbers indicating the order in which the select clause or action table is executed in the query

There are three types of id numbers:

1. If the id is the same, the execution order is from top to bottom-- left correlation explain select* from emp e left join dept d on e.deptno = d.deptno;-- right association e explain select* from emp e right join dept d on e.deptno = d.deptno

Verified by left join and right join; like id (pay attention to the table column of the execution plan), left join first scans the e table, then the d table; right join scans the d table first, then the e table

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, and the explain select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES') will be executed first.

In the following table, we first query the data with id 2, which is our d table (note: we can see that select_type in d table means SUBQUERY, that is, subquery), and then query the data in e table according to the deptno in d table.

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.

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 = (select d.deptno from dept d where d.dname = 'SALES')

Here, start with the execution of id 2. If the id is the same, it will be executed sequentially.

2.2 select_type

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

-- simple: simple query, excluding subqueries and union explain select* from emp

-- primary: if the query contains any complex subqueries, the outermost query is marked Primary explain select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES')

-- union: if the second select appears after union, it is marked as union explain select * from emp where deptno = 10 union select * from emp where sal > 2000

-- dependent union: similar to union, the depentent here indicates that the combined result of union or union all will be affected by the external table 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 explain select * from emp where deptno = 10 union select * from emp where sal > 2000 of the result obtained from the union table

-- subquery: include subquery explain select* from emp where sal > (select avg (sal) from emp) in the select or where list

-- subqueries of dependent subquery:subquery are affected by external table queries explain select * from emp e where e.deptno = (select distinct deptno from dept where deptno = e.deptno)

-- DERIVED: the subquery that appears in the from clause, also known as the derived class, explain select * from (select ename staname,mgr from emp where ename ='W' union select ename,mgr from emp where ename ='E') a

-- UNCACHEABLE SUBQUERY: indicates that the results using subqueries cannot be cached explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size)

-- uncacheable union: indicates that the query result of union cannot be cached by explain select * from emp where exists (select 1 from dept where emp.deptno = dept.deptno union select 1 from dept where deptno = 10)

2.3 table

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, or it can be an alias for the table.

Explainselect*fromempwheresal > (selectavg (sal) fromemp)

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.

Explain select * from (select ename staname,mgr from emp where ename = 'WARD' union select ename staname,mgr from emp where ename =' SMITH') a

Derived2: indicates that we need to fetch data from derivative table 2

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

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

2.4 type

Type shows the type of access, which 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 data you need. It is very inefficient, and there are many types of access. The efficiency is from the best to the worst:

System > const > eqref > ref > fulltext > refornull > indexmerge > uniquesubquery > indexsubquery > range > index > ALL

In general, you have to ensure that the query reaches at least range level, preferably ref.

-- all: full table scan. Generally speaking, if there is such a sql statement and the amount of data is large, then it needs to be optimized. Explain select * from emp

-- 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 explain select empno from emp of the data.

-- range: indicates that the scope is limited when the index query is used, and the query is carried out within the specified range, thus avoiding the full index scan of index. The applicable operators: =, >, > =

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.