This is the second day of my participation in the August More text Challenge. For details, see:August is more challenging
Explain is useful: In order to know how to optimize the execution of SQL statements, you need to view the 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 queries to see how mysql handles SQL statements. Look at the execution plan to see if the executor is processing the SQL as we want it to.
The information contained in the Explain execution plan is as follows:
- Id: indicates the serial number of the query
- Select_type: indicates the query type
- Table: table name or alias
- Partitions: matching partitions
- Type: indicates the access type
- Possible_keys: Possible index
- Key: indicates the actual index
- Key_len: indicates the index length
- Ref: Column to be compared with the index
- Rows: Estimated number of rows
- Filtered: Percentage of rows filtered by table criteria
- -Blair: Extra information
Here’s what each column represents and the corresponding SQL.
The test used mysql version 5.7, using the following three table structures
CREATE TABLE `demo`.`emp` ( `emp_id` bigint(20) NOT NULL, 'name' varchar(20) CHARACTER SET utf8MB4 COLLATE UTf8MB4_bin NULL DEFAULT NULL COMMENT 'name' varchar(20) CHARACTER SET UTf8MB4 COLLATE UTf8MB4_bin NULL DEFAULT NULL COMMENT 'empno' int(20) NOT NULL COMMENT 'iD ',' deptno 'int(20) NOT NULL COMMENT' iD ', 'sal' int(11) NOT NULL DEFAULT 0 COMMENT 'sale ', 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' deptno ', 'dname' varchar(20) CHARACTER SET UTF8 COLLATE UTF8_bin NULL DEFAULT NULL COMMENT 'iD ', 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;Copy the codeAn id column
Select The sequence number (a group of numbers) of a 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, 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;
Copy the code
2, if the id is different, if it is a subquery, the id id will be increased, the larger the ID value, the higher the priority, the earlier the execution \
mysql> explain select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');
Copy the code
3. The same and different ids exist simultaneously: The same ids are considered as a group and executed from top to bottom. In all groups, the larger the ID value, the higher the priority and the higher the priority
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');
Copy the code
Second, select_type columns
It is mainly used to identify the type of query, whether it is a normal query, a federated query or a subqueryCopy the code1. Sample: A simple query without subqueries and union
mysql> explain select * from emp;
Copy the code
- 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');
Copy the code
- Union: The second and subsequent select in union, Union ALL, and subqueries is marked as union
mysql> explain select * from emp where deptno = 10 union select * from emp where sal >2000;
Copy the code
4. Dependent Union: In a large query that contains a union or a union ALL, the select_type value of each query is dependent union except for the leftmost 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)
Copy the code
5. Union result: Select the result from the union table.
mysql> explain select * from emp where deptno = 10 union select * from emp where sal >2000;
Copy the code
6. Subquery: include subquery in select or WHERE list (not in from clause)
mysql> explain select * from emp where sal > (select avg(sal) from emp) ;
Copy the code
7. Dependent subquery: The first select in a dependent subquery (not in the FROM clause) that is dependent on the outside query.
mysql> explain select e1.* from emp e1 WHERE e1.deptno = (SELECT deptno FROM emp e2 WHERE e1.empno = e2.empno);
Copy the code
8. Derived: **** Subqueries included in the FROM list are marked as derived, also called derived classes
mysql> explain select * from ( select emp_id,count(*) from emp group by emp_id ) e;
Copy the code
9. UNCACHEABLE SUBQUERY: The result of a SUBQUERY cannot be cached, and the first row of the outer link must be reevaluated.
mysql> explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
Copy the code
10. Uncacheable UNION: Indicates that the union query result cannot be cached: no specific SQL statement validation is found.
Three, the table columns
Which table is being accessed by the corresponding row, table name or alias, possibly 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 of the table.
The table name is of the form derivedN, representing the table derived from the query with ID N.
3. Table name is in the form of union N1,n2, etc. N1 and n2 indicate the ID of the participant in the union.
Four, the type column
Type shows the access type, and the access type indicates how I access our data. The easiest way to think about is full table scan, which is very inefficient to directly traverse a table to find the required data.
There are many types of access, and the efficiency from best to worst is:
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 is at least range level, preferably ref
1. All: Scans the entire table to find the required rows. In general, such SQL statements and large amounts of data will need to be optimized.
mysql> explain select * from emp;
Copy the code
This is more efficient than all. There are two main cases. One is that the current query overwrites the index, that is, the data we need can be requested in the index, or the index is used to sort the data, so as to avoid reordering the data
mysql> explain select empno from emp;
Copy the code
=, <>, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() = =, <>, >=, < <=, IS NULL, BETWEEN, LIKE, or IN()
mysql> explain select * from emp where empno between 100 and 200;
Copy the code
4. Index_subquery: Use indexes to associate subqueries without scanning the full table
mysql> explain select * from emp where deptno not in (select deptno from emp)
Copy the code
5. Unique_subquery: This join type is similar to index_subquery in that it uses a unique index
mysql> explain SELECT * from emp where emp_id not in (select emp.emp_id from emp );
Copy the code
6. Index_merge: Multiple indexes need to be combined during a query.
Mysql > not simulated
7. Ref_or_null: The query optimizer selects this access mode when a field requires both an association condition and a null value.
Mysql > not simulated
8. Ref: Non-unique index is used for data lookup
mysql> explain select * from emp where deptno=10;
Copy the code
9. Eq_ref: When searching for a table with a primary key or a unique non-empty index (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;
Copy the code
10. Const: Matches a maximum of one data. usually equivalences are queried using primary keys or unique indexes
mysql> explain select * from emp where empno = 10;
Copy the code
11. System: A table that has only one row (equal to the system table) is const and does not require disk I/O
mysql> explain SELECT * FROM `mysql`.`proxies_priv`;
Copy the code
Five, possible_keys columns
Displays indexes that may apply to this table, one or more. If an index exists on the field involved in the query, it will be listed, but not necessarily used by the query.
Six, the key columns
If the index is null, the index is not used. If the overwrite index is used in the query, the index overlaps with the SELECT field in the query.
Seven, key_len columns
Represents the number of bytes used in the index. The length of the index used in the query can be calculated by key_len, as short as possible without loss of accuracy.
A larger index occupies more storage space. As a result, the number and quantity of I/OS increase, affecting the execution efficiency
Eight, the ref
Displays the column or constant used by the previous table to find the value in the index of the key column record
Nine, rows
Based on the table statistics and index usage, it is important to estimate the number of rows that need to be read to find the required record. How much data is found by the direct response SQL, as little as possible to accomplish its purpose.
Ten, filtered column
A pessimistic estimate of the percentage of the number of records in a table that meet a condition (a WHERE clause or join condition).
Eleven, extra column
Contains additional information.
1. Using filesort: mysql cannot use index to sort
mysql> explain select * from emp order by sal;
Copy the code
Create a temporary table to store intermediate results. Delete the temporary table when the query is complete
mysql> explain select name,count(*) from emp where deptno = 10 group by name;
Copy the code
Using index: This indicates that the current query overwrites the index and reads data directly from the index instead of accessing the data table. If the using WHERE index is present at the same time, the index is used to perform the lookup of the index key. If not, the surface index is used to read the data, not to do the actual lookup
mysql> explain select deptno,count(*) from emp group by deptno limit 10;
Copy the code
Using WHERE: Using where
mysql> explain select * from emp where name = 1;
Copy the code5. Using join buffer
mysql> explain select * from emp e left join dept d on e.deptno = d.deptno;
Copy the code
Impossible where: the result of the statement is always false
mysql> explain select * from emp where 1=0;
Copy the code
The article has the question can give me the message, the part does not simulate out also can give the opinion
Refer to High-performance MySQL.
Mysql Advanced Series History Review:
1. Infrastructure
2. Storage engine
3. What’s the difference between MyISAM and InnoDB
4. How to better select data types in table design
5. How exactly should the paradigm in database design be used