The Explain keyword emulates SQL statements executed by the MySQL optimizer and is a good way to analyze performance bottlenecks in SQL statements or table structures.

Explain the purpose of the

1. What is the reading sequence of the table? 2. Which indexes can be used 4. Which indexes are actually used 5. How are references between tables 6. How many rows per table are queried by the optimizer......Copy the code

Explain execution effect

mysql> explain select * from subject where id = 1 \G
******************************************************
           id: 1
  select_type: SIMPLE
        table: subject
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
******************************************************
Copy the code

Explain included fields

Id // Select QUERY sequence number, which contains a group of numbers, indicating the order in which select clauses or tables are executed in the query. 2. Select_type // query type 3.typePossible_keys // Possible_keys // possible_keys // possible_keys // possible_keys // possible_keys // Possible_keys // Possible_keys // Possible_keys // Possible_keys // Possible_keys // This column can be used to calculate the length of the index used in the query 9.ref // show which column of the index is used, if possible, as a constant, and which columns or constants are used to find the value on the index column 10.rows // Based on table statistics and index selection Estimate roughly the number of rows read to find the desired record 11. Filtered // Percentage of queried table rows in the table 12. Extra // contains additional information that is important but not suitable for other columnsCopy the code

picture

Word version

Id field

1. The same id
Explain select subject.* from subject,student_score,teacherwheresubject.id = student_id and subject.teacher_id = teacher.id; Student_score = subject > teacher > student_scoreCopy the code

2. The id is different
In the case of a subquery, the ID sequence increases. The higher the ID value is, the higher the priority is. Example: Explain select score.* from student_score as scorewhere subject_id = (select id from subject where teacher_id = (select id from teacher whereid = 2)); Teacher > subject > student_scoreCopy the code

3. The IDS are the same but different
If the ids are the same, they are regarded as a group and executed from top to bottom. In all groups, the greater the ID value, the higher the priority and the earlier the execution. Example:  explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id -> union -> select subject.*  from subject right join teacher on subject.teacher_id = teacher.id; 2. Teacher > 2. Subject > 1Copy the code

Select_type field

1. SIMPLE
Explain select subject.* from subject,student_score,teacherwhere subject.id = student_id and subject.teacher_id = teacher.id;
Copy the code

2. PRIMARY
If the query contains any complex sub-parts, the outermost query is marked as the primary query example: explain select score.* from student_score as scorewhere subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));
Copy the code

3. SUBQUERY
In the select orwhereExplain select score.* from student_score as scorewhere subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));
Copy the code

4. DERIVED
The subqueries contained in the FROM list are labeled DERIVED, and MySQL recursively executes these subqueries, putting the results in temporary tables. Note: MySQL5.7+ is optimized to add derived_merge, which is enabled by default to make the query more efficientCopy the code
5. UNION
If the second select occurs after the Uion, it is marked as a UNION example:  explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id -> union -> select subject.*  from subject right join teacher on subject.teacher_id = teacher.id;Copy the code

6. UNION RESULT
An example of a select from the UNION table:  explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id -> union -> select subject.*  from subject right join teacher on subject.teacher_id = teacher.id;Copy the code

The type field

NULL>system>const>eq_ref>ref>fulltext> ref_or_NULL >index_merge>unique_subquery>index_subquery>range>index>ALL NULL>system>const>eq_ref>ref> ref_or_NULL >index_merge>range>index>ALLCopy the code
1. NULL
Explain select min(id) from subject; explain select min(id) from subject;Copy the code

2. system
The table has only one row (equal to the system table), which is a const columnCopy the code
3. const
Const is used to compare a primary key or UIQUE index because it matches only one row of data, so it is fast, such as when the primary key is placedwhereMySQL can convert this query to a constant example: Explain select * from teacherwhere teacher_no = 'T2010001';
Copy the code

4. eq_ref
Unique index scan. For each index key, there is only one record in the table that matches it. This is common for primary key or unique index scan examples:  explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id;Copy the code

5. ref
A non-unique index scan, which returns all rows matching a single value, is essentially an index access, which returns all rows matching a single value. However, it is possible to find multiple rows matching a single value, which should be a mixture of lookup and scan examples:  explain select subject.* from subject,student_score,teacherwhere subject.id = student_id and subject.teacher_id = teacher.id;
Copy the code

6. ref_or_null
Example of rows similar to ref, but with a NULL value can be searched: explain select * from teacherwhere name = 'wangsi' or name is null;
Copy the code

7. index_merge
Example of an optimization method that uses index merging: Explain select * from teacherwhere id = 1 or teacher_no = 'T2010001' .
Copy the code

8. range
Retrieves only a given range of rows, using an index to select rows, and the key column showing which index is used is generally in yourwhereStatement where between, <>,inAnd so on. Example: Explain select * from subjectwhere id between 1 and 3;
Copy the code

9. index
Full Index Scan, the difference between Index and All: An index only traverses the index tree and is usually faster than All because index files are usually smaller than data files. That is, although both All and INDEX read the entire table, index is read from the index, while All is read from the hard disk. Example: explain select id from subject;Copy the code

10. ALL
Example: explain select * from subject;Copy the code

The table field

Which table is the data fromCopy the code

Possible_keys field

Shows the indexes that might be applied to the table. If an index exists for a field involved in one or more queries, the index will be listed but not necessarily usedCopy the code

The key fields

If an overridden index is used in a query (the column queried is an index), the index appears only in the key listCopy the code

Key_len field

Key_len represents the number of bytes used in the index. This column can be used to calculate the length of the index used in the query. The shorter the length is, the better, without loss of precisionCopy the code

Ref field

Shows which column of the index is used, if possible, as a constant, and which column or constant is used to find the value on the index columnCopy the code

Rows field

Based on table statistics and index selection, approximate the number of rows to read to find the desired recordCopy the code

Partitions field

Matched partitionsCopy the code

Filtered field

The percentage of queried table rows to the tableCopy the code

Extra fields

Contains additional information that is not suitable for display in other columns, but is importantCopy the code
1. Using filesort
Explain select * from subject order by name; select * from subject order by name; select * from subject order by name;Copy the code

2. Using temporary
MySQL > select * from temporary tables where temporary tables are used to hold intermediate results; MySQL > select * from temporary tables where temporary tables are used to sort intermediate results;  explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id -> union -> select subject.*  from subject right join teacher on subject.teacher_id = teacher.id;Copy the code

3. Using index
The corresponding select operation using a Covering Index (Covering Index), avoid access to the table of data row, efficiency is good! If using is also presentwhere, indicating that the index is used to perform a lookup of the index key value if using is not present simultaneouslywhereExplain select subject.* from subject,student_score,teacherwheresubject.id = student_id and subject.teacher_id = teacher.id; Note: Overwrite index: the columns of the select can only be retrieved from the index, without reading the rows. MySQL can use the index to return the columns of the select list, without reading the data file again based on the indexCopy the code

4. Using where
Using thewhereExplain select subject.* from subject,student_score,teacherwhere subject.id = student_id and subject.teacher_id = teacher.id;
Copy the code

5. Using join buffer
Explain select student.*,teacher.*,subject.* from student,teacher.Copy the code

6. impossible where
whereThe value of the clause is alwaysfalse, cannot be used to get any tuple examples: explain select * from teacherwhere name = 'wangsi' and name = 'lisi';
Copy the code

7. distinct
Once mysql finds a row that matches jointly with a row, it no longer searches for examples:  explain select distinct teacher.name from teacher left join subject on teacher.id = subject.teacher_id;Copy the code

8. Select tables optimized away
Explain SELECT min(id) from subject; explain SELECT min(id) from subject;Copy the code

Data tables used

create table subject( -> id int(10) auto_increment, -> name varchar(20), -> teacher_id int(10), -> primary key (id), -> index idx_teacher_id (teacher_id)); Create table teacher(-> id int(10) auto_increment, -> name vARCHar (20), -> teacher_no varchar(20), -> primary key (id), -> unique index unx_teacher_no (teacher_no(20))); Create table student(-> id int(10) auto_increment, -> name varchar(20), -> student_no varchar(20), -> primary key (id), -> unique index unx_student_no (student_no(20))); Create table student_score(-> id int(10) auto_increment, -> student_id int(10), -> subject_id int(10), -> score int(10), -> primary key (id), -> index idx_student_id (student_id), -> index idx_subject_id (subject_id)); Alter table teacher add index idx_name(name(20)); Insert into student(name,student_no) values (student_no)'zhangsan'.'20200001'), ('lisi'.'20200002'), ('yan'.'20200003'), ('dede'.'20200004');
 
 insert into teacher(name,teacher_no) values('wangsi'.'T2010001'), ('sunsi'.'T2010002'), ('jiangsi'.'T2010003'), ('zhousi'.'T2010004');
 
 insert into subject(name,teacher_id) values('math', 1), ('Chinese', 2), ('English'And 3), ('history', 4); insert into student_score(student_id,subject_id,score) ,2,60,1,90 values (1), (1), (1,3,80),,4,100 (1), (2),4,60,,3,50 (2), (2,2,80),,1,90 (2), (3,1,90), (3,4,100),,1,40 (4), (4,2,80), (4, 3 , 80), (4,5,100);Copy the code