The Explain abstract
The Explain keyword is a common keyword for SQL optimization in Mysql. It is usually used to “view the execution plan of SQL instead of executing SQL”, so as to quickly find out the problem of SQL.
Create the required “user table user, role table role, and user role relationship table ROLE_USER” as test tables before explaining:
/ / the user tableDROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(25) DEFAULT NULL,
`age` int(11) NOT NULL DEFAULT 0, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `user` (`id`, `name`, `age`,`update_time`) VALUES (1,'Joe', 23,'the 2020-12-22 15:27:18'), (2,'bill', 24,'the 2020-06-21 15:27:18'), (3,'Cathy', 25,'the 2020-07-20 15:27:18'); DROP TABLE IF EXISTS `role`; CREATE TABLE `role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `role` (`id`, `name`) VALUES (1,'Product Manager'), (2,'Technical Manager'), (3,'Project Director'); DROP TABLE IF EXISTS `role_user`; CREATE TABLE `role_user` ( `id` int(11) NOT NULL, `role_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `index_role_user_id` (`role_id`,`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO ` role_user ` (` id `, ` role_id `, ` user_id `) VALUES (1, 2, 1), (2,1,2), (3) filling;Copy the codeLet’s start with an SQL:explain select * from user where id =2;, you can see the following results:
Id field
Id indicates the sequence number of the SELECT query statement. It identifies the order in which the SQL is executed. The SQL is executed from the largest to the smallest by ID.
What does that mean? For example, run the SQL: explain select * from user where id in (select user_id from role_user);
+----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+------ ----+-----------------------------------------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+------ ----+-----------------------------------------------------------------------------------+| | 1 SIMPLE | user | NULL | | ALL PRIMARY | NULL | NULL | NULL | 3 | | NULL | 100.00| 1 | SIMPLE | role_user | NULL | index | NULL | index_role_user_id | 8 | NULL | 3 | 33.33 | Using where; Using index; FirstMatch(user); Using join buffer (Block Nested Loop) |
+----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+------ ----+-----------------------------------------------------------------------------------+Copy the codeThe first entry corresponds to the USER table, and the second entry corresponds to the ROLE_USER table, in which case the ID is the same.
If the id is different, for example, run the following SQL: explain select (select 1 from user limit 1) from role; :
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+------- ------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+------- ------+| 1 | PRIMARY | role | NULL | index | NULL | index_name | 33 | NULL | 3 | 100.00 | Using index |
| 2 | SUBQUERY | user | NULL | index | NULL | PRIMARY 100.00 | | | NULL | 3 | 4 Using the index |+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+------- ------+Copy the codeMysql > select * from user where id=2; mysql > select * from user where id=2; mysql > select * from user where id=2;
“Conclusion:” this is the ID to identify the order of SQL execution. Generally, there will be multiple records in complex queries. Simple queries have only one record. Subqueries are optimized in Mysql 8, so sometimes even complex queries only have one record.
Select_type field
Select_type specifies the query type, that is, whether the query corresponds to a simple query or a complex query. If the query is complex, the query contains simple subquery, from clause subquery, and union query. Here’s a look at all the query types in select_Type separately.
simple
simpleIndicates a simple query, without any complex query.
PRIMARY
In a complex query“The outermost select statement has the query type PRIMARY“For example, execute the following SQL:explain select * from role where id = (select id from role_user where role_id = (select id from user where id = 2)); 
Select * from role where id =?Will be marked as PRIMARY.
SUBQUERY
in“Subqueries contained in select or WHERE“Theta is going to be represented by thetaSUBQUERYType, for example, the SQL executed in the previous sentence has two subqueries as SUBQUERY.
DERIVED
“DERIVED stands for DERIVED table, or DERIVED table, and is represented as a DERIVED type in a subquery containing from“Mysql performs these subqueries recursively and puts the results in temporary tables. Execute SQL:Explain select * from (select name from user union select name from role) a where a.name = '三'; 
derived_merge(Derived merge), can speed up query efficiency.
UNION
In the event of a“In the UNION query, the second select query is represented as UNION“:
UNION RESULT
“The RESULT of the UNION query statement is marked as a UNION RESULT“SQL > execute SQLExplain select * from (select name from user union select name from role) a where a.name = '三'; 
3 > < union2,, so the result of a UNION query is marked asUNION RESULT
other
The seven select_types above are all more common, and a few less common, just to get an idea:
DEPENDENT UNION: also represents the second or subsequent statement in the UNION query, but depends on the external query.DEPENDENT SUBQUERYThe first SELECT statement in the: subquery also depends on external queries.UNCACHEABLE SUBQUERYThe result of the: subquery cannot be cached and the first line of the outer join must be reevaluated.
The table field
One is an existing table. For example, the user and role above are all tables created by ourselves, and can also represent derived tables.
For example, the table field of UNION RESULT is represented as
, i.e. the RESULT records of the second and third rows are queried.
The type field
The type field represents the type of the SQL association or access. From this field we can determine the SQL lookup database table, the search records of the approximate range is what, directly reflects the efficiency of SQL problems.
System, const, eq_ref, ref, range, index, ALL. System > const > eq_ref > ref > range > index > ALL.
system
System is a special case of const, “indicating that there is only one row in the table.”
const
Const means that the data is found through the index once“Use equivalent queries in unique or primary key indexes“Because there is only one data match in the table, the lookup is very fast. Example:explain select * from user where id =2;
eq_ref
Eq_ref indicates that a unique index or primary key index scan is used as a table link matching condition. For each index key, only one record in the table matches it. Such as:explain select * from user left join role_user on user.id = role_user.user_id left join role on role_user.role_id=role.id;
ref
Ref performance is worse than eq_REF, and it also indicates the link matching conditions of the table, that is, which table fields are used as the values of the query index column. The difference between REF and Eq_REF is that eq_REF uses a unique index or primary key index.
The result of a REF scan may find multiple rows that match the criteria, essentially an index access that returns matching rows. Such as:Explain select * from user where name = 'explain ';
range
“Range uses an index to retrieve rows in a given range. Range is usually found when a query statement such as BETWEEN, <>, or IN is used after where“:explain select * from user where id > 2;
index
Index indicates that the index tree will be traversed. Index avoids ALL faster, but if the index appears, you need to check whether the index is used correctly:explain select id from user;
ALL
“The difference between ALL and index is that ALL is read from the hard disk, while index is read from the index file“SQL > select * from top to bottom; SQL > select * from top to bottom; SQL > select * from top to bottom;explain select * from user;
Possible_keys field
Possible_keys specifies possible indexes that can be used in the possible_keys query. The possible_keys is not necessarily used.
If the index is not NULL, it indicates that the index needs to be added to optimize the query. If the database feels that the full table scan is faster, it may also be NULL.
The key fields
The key field differs from possible_keys in that possible_keys contains the value of key.
To enable Mysql to USE or IGNORE the possible_keys INDEX, you can USE FORCE INDEX, USE INDEX, or IGNORE INDEX.
Key_len field
Indicates the number of bytes used by the index in the SQL query. The number of bytes is not the actual length, but calculated by calculating the length of the index used in the query. The maximum possible length of the index field is displayed.
In general, key_len should be as small as possible without losing precision. For example, the above test table id is int, int consists of 4 bytes:explain select * from user where id =2; 
| The data type | Number of bytes |
|---|---|
| string | Char (n) : Indicates the length of n bytes Varchar (n) : 2 bytes the value is a string of 3n + 2 bytes if it is utf-8 |
| Numeric types | Tinyint: 1 byte Smallint: 2 bytes Int: 4 bytes Bigint: 8 bytes |
| Time to type | Date: 3 bytes Timestamp: 4 bytes Datetime: 8 bytes |
If the index is a string and the actual string is too long to be stored (768 bytes), mysql will use a similar left prefix index.
Ref field
Ref represents the comparison of columns to the index, the matching condition of the table join, and which columns or constants are used to query the value on the index column.
Rows field
Rows represents the estimated number of rows to be scanned. Generally, Mysql estimates the number of rows to be scanned for the search record based on the statistics table information and index selection. Note that this is not the actual number of rows in the result set.
Partitions, filtered fields
Partitions represent matched partitions; Filtered represents the percentage of queried table rows in a table.
Extra fields
This field displays additional information about the SQL query in the following cases:
Using index
The column to be queried is overwritten by the index. This indicates that the query performance is high, that is, the information to be queried can be found in the index, and the index is used correctly.explain select id from user where id =2; 
Using where
In contrast to Using index, the query column is not covered by the index. The where condition is followed by a non-index leading column, which simply uses the WHERE condition:explain select user.* from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id;
Using temporary
“Using temporary Indicates that a temporary table is used to store intermediate results. Temporary tables are used when sorting results“, for example, sort order by and group by. Example:Explain select * from (select name from user union select name from role) a where a.name = '三';
Using filesort
Mysql uses an external index to sort data and does not use an index in a table:explain select * from user order by name;
Using join buffer
Using join buffer means to use the join buffer:explain select user.* from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id; 
Impossible where
Impossible where occurs when the condition after where is always false. This can be ignored and is less common:explain select * from user where name = 'hah' and name = 'sfsd';
Select tables optimized away
Explain select min(id) from user;
Say so many theories always want to practice, the following to the user test table for example to test practice.
practice
(1) Through queryExplain select * from user where name =' explain ';The name field does not create an index.
We can solve this problem by creating a federated index index_name_age_time:
alter table user add index index_name_age_time (name,age,update_time) ;
Copy the codeWhen the next query is made, the index is used:
EXPLAIN SELECT * FROM user WHERE name = ‘aaa ‘; SELECT * FROM user WHERE name =’ aaa ‘;
EXPLAIN SELECT * FROM employees WHERE left(name,2) = ‘3 ‘; , causing index invalidation.
(4) do not use (! = <>); is not null; is not null;
