Understanding of MySQL Execution plan

1, the execution plan is to add explain before the SQL statement, using DESC can also.
2, DESC has two options extended and PARTITIONS,DESC extended to optimize the original SQL statement, through show warnings can see the optimized SQL statement.
DESC partitions can view information that uses partitioned tables.
3, for example:
Mysql> DESC SELECT * from student;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | Simple | Student | All | NULL | NULL | NULL | NULL | 5 | |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+

The

ID is the number of the execution plan, which can be understood as the call stack of the method, the call stack is advanced, and the higher the ID, the higher the first execution, as follows:
mysql> DESC SELECT * FROM student where id>1 Union SELECT * FR Om Student where id<5;
+------+--------------+------------+------+---------------+------+---------+------+------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+------+--------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | Student | All | ID | NULL | NULL | NULL | 5 | Using where |
| 2 | UNION | Student | All | ID | NULL | NULL | NULL | 5 | Using where |
| NULL | UNION RESULT | <union1,2> | All | NULL | NULL | NULL | NULL | NULL | |
+------+--------------+------------+------+---------------+------+---------+------+------+-------------+

Mysql> DESC SELECT * FROM student where ID in (select ID from student where name= ' Andy ');
+----+--------------------+---------+------+---------------+------+---------+------+------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+--------------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | Student | All | NULL | NULL | NULL | NULL | 5 | Using where |
| 2 | DEPENDENT subquery | Student | All | ID | NULL | NULL | NULL | 5 | Using where |
+----+--------------------+---------+------+---------------+------+---------+------+------+-------------+
2 rows in Set

Select_type query type, the main values are as follows:
1, simple, simply query. As follows:
mysql> DESC SELECT * from student;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_ Type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | Simple | Student | All | NULL | NULL | NULL | NULL | 4 | |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+

2, primary/union/union result, combined query
mysql> DESC SELECT * FROM student where id>1 union SELECT * FROM Student whe Re id<4;
+------+--------------+------------+-------+---------------+---------+---------+------+------+------------- +
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+------------+-------+---------------+---------+---------+------+------+------------- +
| 1 | PRIMARY | Student | Range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where |
| 2 | UNION | Student | Range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where |
| NULL | UNION RESULT | <union1,2> | All | NULL | NULL | NULL | NULL | NULL | |
+------+--------------+------------+-------+---------------+---------+---------+------+------+------------- +

3, Primary/dependent subquery, dependent subquery
mysql> DESC SELECT * FROM student where ID in (select SID from SC where score& GT;80);
+----+--------------------+---------+------+---------------+------+---------+------+------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+--------------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | Student | All | NULL | NULL | NULL | NULL | 4 | Using where |
| 2 | DEPENDENT subquery | sc | All | NULL | NULL | NULL | NULL | 6 | Using where |
+----+--------------------+---------+------+---------------+------+---------+------+------+-------------+

4, primary/derived, the target of the query is not a physical table, that is, the use of temporary tables
Mysql> desc Select min (id) from (the Select ID from student where name= ' Andy ') t1;
+----+-------------+------------+------+---------------+-----------+---------+------+------+------------------- -------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+------------+------+---------------+-----------+---------+------+------+------------------- -------+
| 1 | PRIMARY | <derived2> | All | NULL | NULL | NULL | NULL | 2 | |
| 2 | DERIVED | Student | Ref | Stu_index | Stu_index | 51 | | 2 | Using where; Using Index |
+----+-------------+------------+------+---------------+-----------+---------+------+------+------------------- -------+

Tables referenced by table

Type refers to the types of access, the main values are All,index,range,ref,eq_ref,const,system,null
1, all traverse the full table
Mysql> DESC select name from student;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | Simple | Student | All | NULL | NULL | NULL | NULL | 4 | |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+

2 index is used to traverse index tree

Mysql> CREATE index index_name on student (name);
Query OK, 0 rows affected
records:0 duplicates:0 warnings:0

Mysql> show index from student;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+---- ----+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+---- ----+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | ID | A | 3 | NULL | NULL | | BTREE | | |
| student | 1 | index_name | 1 | NAME | A | 3 | NULL | NULL | YES | BTREE | | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+---- ----+------+------------+---------+---------------+
2 rows in set

Mysql> DESC select name from student;
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
| 1 | Simple | Student | Index | NULL | index_name | 51 | NULL | 4 | Using Index |
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+

3. Range use index, scan range
Mysql> DESC select name from student where name in (' Andy ', ' Bill ');
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------------- ------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------------- ------+
| 1 | Simple | Student | Range | index_name | index_name | 51 | NULL | 3 | Using where; Using Index |
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------------- ------+

4, ref non-unique index scan, find all matching index values
There will be multiple records named Andy.
Mysql> DESC select name from student where name = ' Andy ';
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------------------- ------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------------------- ------+
| 1 | Simple | Student | Ref | index_name | index_name | 51 | Const | 2 | Using where; Using Index |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------------------- ------+
Query with ID 2, for a name of teacher, find all records with the same name from student
Mysql> DESC SELECT * from Student,teacher where student.name=teacher.name;
+----+-------------+---------+-------+---------------+------------+---------+---------------------+------+----- --------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------+-------+---------------+------------+---------+---------------------+------+----- --------+
| 1 | Simple | Teacher | Index | index_name | index_name | 195 | NULL | 1 | Using Index |
| 1 | Simple | Student | Ref | index_name | index_name | 51 | Testsc.teacher.NAME | 1 | Using where |
+----+-------------+---------+-------+---------------+------------+---------+---------------------+------+----- --------+
5, Eq_ref, for the field of the previous table, only one record will match, as follows:
Because the ID is the student primary key, student has only one record that matches the ID of the teacher.
Mysql> DESC SELECT * from Student,teacher where student.id=teacher.id;
+----+-------------+---------+--------+---------------+------------+---------+-------------------+------+------ -------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------+--------+---------------+------------+---------+-------------------+------+------ -------+
| 1 | Simple | Teacher | Index | PRIMARY | index_name | 195 | NULL | 1 | Using Index |
| 1 | Simple | Student | Eq_ref | PRIMARY | PRIMARY | 4 | Testsc.teacher.ID | 1 | |
+----+-------------+---------+--------+---------------+------------+---------+-------------------+------+------ -------+
6, const MySQL query optimization, conversion to constant
Mysql> DESC SELECT * from student where student.id=10001;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| 1 | Simple | Student | Const | PRIMARY | PRIMARY | 4 | Const | 1 | |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in Set
7, System is a special case of const, the query records only one
mysql> desc SELECT id from (SELECT * from student where student.id=10001) T1;
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | System | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | Student | Const | PRIMARY | PRIMARY | 4 | | 1 | |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
2 rows in Set
8, NULL no record, no need to execute query
mysql> desc SELECT 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | Simple | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No Tables Used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in Set

Possible_keys indexes that may be used

Index to use for key

The index length used by Key_len is related to three factors:
Whether to allow NULL, allow NULL to be more than one byte to identify is not NULL
Whether it is longer, grows more than two bytes to indicate the length
character encoding, different character encoding, the same character occupies the same memory latin1[1],gb2312[2],gbk[2],utf8[3]

Criteria for ref matching
Mysql> DESC SELECT * from Student,teacher where student.name=teacher.name;
+----+-------------+---------+-------+---------------+------------+---------+---------------------+------+----- --------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------+-------+---------------+------------+---------+---------------------+------+----- --------+
| 1 | Simple | Teacher | Index | index_name | index_name | 195 | NULL | 1 | Using Index |
| 1 | Simple | Student | Ref | index_name | index_name | 51 | Testsc.teacher.NAME | 1 | Using where |
+----+-------------+---------+-------+---------------+------------+---------+---------------------+------+----- --------+

Rows estimates the number of lines scanned

Extra additional information, mainly
Using Index
Using where
Using Temporary
Using Filesort cannot take advantage of index completion sorting

Limitations of the MySQL execution plan:
Does not consider the impact of triggers, stored procedures, or user-defined functions on queries
Do not consider various caches
Cannot display the optimizations that MySQL made when executing a query
Some of the statistics are estimates, not exact values
Only the select operation can be interpreted, and other operations are rewritten as a select after viewing.

Understanding of MySQL Execution plan