catalogue
EXPLAIN statement provides information about how MySQL executes SQL. It is an artifact of SQL optimization that we must master.
EXPLAIN on the official website: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
Test data preparation
Build table
DROP TABLE IF EXISTS course; CREATE TABLE `course` ( `cid` int NOT NULL AUTO_INCREMENT COMMENT 'Course number', `tid` int DEFAULT NULL COMMENT 'Teacher number', `cname` varchar(20) DEFAULT NULL COMMENT 'Course name', PRIMARY KEY (cid) ) COMMENT = 'Course information sheet'; DROP TABLE IF EXISTS teacher; CREATE TABLE `teacher` ( `tid` int NOT NULL AUTO_INCREMENT COMMENT 'Teacher number', `tname` varchar(20) DEFAULT NULL COMMENT 'Teacher name', PRIMARY KEY (tid) ) COMMENT = 'Teacher information form'; DROP TABLE IF EXISTS teacher_detail; CREATE TABLE `teacher_detail` ( `tdid` int NOT NULL AUTO_INCREMENT COMMENT 'Detail number', `tid` int DEFAULT NULL COMMENT 'Teacher number', `phone` varchar(20) DEFAULT NULL COMMENT 'cell-phone number', PRIMARY KEY (tdid), UNIQUE KEY (tid), KEY(phone) ) COMMENT = 'Teacher details form';
Initialization data
delimiter $$
DROP PROCEDURE IF EXISTS init;
create procedure init()
begin
declare i int;
declare id1 int;
declare id2 int;
declare id3 int;
declare id4 int;
declare id5 int;
set i = 1;
while i < 5000 do
set id1 = i;
set id2 = i + 4;
set id3 = i + 5;
set id4 = i + 6;
set id5 = i + 9;
INSERT INTO `teacher`
VALUES (id1, CONCAT('teacher', id1)),
(id2, CONCAT('teacher', id2)),
(id3, CONCAT('teacher', id3)),
(id4, CONCAT('teacher', id4)),
(id5, CONCAT('teacher', id5));
INSERT INTO `course`
VALUES (id1, id1,'English'),
(id2, id2,'mathematics'),
(id3, id3,'Physics'),
(id4, id4,'Chemistry'),
(id5, id5,'language');
INSERT INTO `teacher_detail`
VALUES (id1,id1, (13800000000 + id1)),
(id2,id2, (13800000000 + id2)),
(id3,id3, (13800000000 + id3)),
(id4,id4, (13800000000 + id4)),
(id5,id5, (13800000000 + id1));
set i = i + 10;
end while;
end
$$
call init();How to use EXPLAIN
EXPLAIN is a native keyword provided by MySQL. It is very simple to use. You only need to add # EXPLAIN before the target SQL and then execute it.
For example:
EXPLAIN SELECT * FROM teacher;
Next, we will analyze the attributes in the returned results in turn.
Attribute analysis
id
The id field reflects the execution order of SQL. When the id is the same, the one listed above shall be executed first.
EXPLAIN SELECT * FROM course c INNER JOIN teacher t ON c.tid = t.tid;
If the id is different, execute the with larger id first.
EXPLAIN SELECT * FROM course c WHERE c.tid = (SELECT td.tid FROM teacher_detail td WHERE td.phone = '13800000040');
select_type
select_type is the query type. Only some common types are introduced here.
- SIMPLE: SIMPLE query, excluding sub query and UNION query.
- PRIMARY: contains the main query in the sub query SQL, that is, the outermost select in the complex query
- SUBQUERY: SUBQUERY contained in the select (not in the from clause)
- DERIVED: DERIVED query, which means that a temporary table will be used before the final query result is obtained.
- UNION: UNION query is used.
- UNION RESULT: mainly displays which tables have UNION queries< UNION2,3 > indicates that there is a UNION for queries with id=2 and id=3.
Example:
EXPLAIN SELECT cr.cname FROM ( SELECT * FROM course WHERE tid = 2 UNION SELECT * FROM course WHERE tid = 3 ) cr;
table column
This column indicates which table a row of explain is accessing.
When there is a subquery in the from clause, the table column is in the < deriven > format, which means that the current query depends on the query with id=N, so the query with id=N is executed first.
When there is a union, the value of the table column of UNION RESULT is < union1,2 >, and 1 and 2 represent the select row id participating in the union.
type
Type represents the connection type and is one of the most important attributes in the execution plan. The following are some of the most common types.
Efficiency goes from high to low: system > const > Eq_ ref > ref > range > index ≈ all
System: there is only one row of records in the table (equal to the system table), which is a special case of const type.
const: compare using primary key or unique index.
eq_ref: it usually appears in the JOIN query of multiple tables, which means that each result of the previous table can only match one row of results of the subsequent table (i.e. one-to-one correspondence, and the corresponding field index of the latter table is generally a UNIQUE index). Generally, it is a query with UNIQUE index (UNIQUE or PRIMARY KEY).
Example:
EXPLAIN SELECT * FROM course c INNER JOIN teacher t ON c.tid = t.tid;
As above: for a record of course, there is only one record corresponding to it in the teacher during association.
ref: the query uses a non unique index, or the association operation only uses the leftmost prefix of the index.
Example:
EXPLAIN SELECT td.tid FROM teacher_detail td WHERE td.phone = '13800000040';
Range: use index range scanning.
Example:
EXPLAIN SELECT * FROM teacher WHERE tid < 1000;
Index: full index scan. Generally, when the index condition discrimination is not high, it may not be faster than full table scanning.
Example:
all: full table scan.
Conclusion: Generally speaking, it is necessary to ensure that the query reaches at least range level, preferably ref. index, and all need to be optimized.
possible_key,key
Possible indexes and actual indexes.
possible_ There can be one or more keys. The indexes that may be used do not necessarily mean they will be used.
Conversely, if possible_ If the key is null, must the key be empty?
EXPLAIN SELECT phone FROM teacher_detail WHERE phone LIKE '%3800000040%';
This happens when the query criteria cannot hit the index, but the query result is an index field. Here, the overlay index is used.
key_len
Length of the index (number of bytes used). It is related to the type and length of the index field.
key_len calculation rules are as follows:
ref
This column shows the columns or constants used to find the value in the index column. The common ones are const, field name and NULL.
rows
MySQL thinks that how many rows can be scanned to return the requested data is an estimated value. Generally speaking, the fewer lines, the better.
filtered
This field represents the proportion of the number of records that meet the query after the data returned by the storage engine is filtered in the server layer. It is a percentage.
For example, after a full table scan, if only one record meets the query criteria, the value of filtered will be relatively low.
EXPLAIN SELECT * FROM course WHERE tid = 100;
Extra
Additional information given in the execution plan.
Using index: the query uses an overlay index and does not need to return to the table.
Using where: two situations:
- where filtering is used in the query, but no index is used;
- There is index filtering, and there is no need to return to the table.
Using index condition: the query uses the index and needs to return to the table. The return table mentioned here may be to get data or to further filter (index push down).
Using filesort: index cannot be used to sort. Temporary file sorting is used.
Using temporary: temporary tables (memory or disk) are used. For example:
- distinct non indexed column;
- group by non indexed column;
- When using join, the index column of group by non driven table will also generate temporary tables.
Reprint address: https://hujinyang.blog.csdn.net/article/details/103877385