Detailed explanation and practical exercises of Mysql tuning Explain tool (recommended)

MySQL tuning Explain tool detailed explanation and practical exercises Explain tool introduction Explain analysis example Explain two variants Column index in explain Best practical index usage summary:
MySQL installation documentation reference

Explain tool introduction

Use the EXPLAIN keyword to simulate the optimizer to execute SQL statements and analyze the performance bottlenecks of your query statements or structures. Add the explain keyword before the select statement, and MySQL will set a mark on the query. Executing the query will return the execution plan information instead of executing the SQL statement.
Note: If from contains a subquery, the subquery will still be executed and the results will be placed in a temporary table

Explain analysis example

Refer to the official documentation

Example table:
 DROP TABLE IF EXISTS `actor`;
 CREATE TABLE `actor` (
 `id` int(11) NOT NULL,
 `name` varchar(45) DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22
15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');

DROP TABLE IF EXISTS `film`;
 CREATE TABLE `film` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_name` (`name`)
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;
 INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');
DROP TABLE IF EXISTS `film_actor`;
 CREATE TABLE `film_actor` (
 `id` int(11) NOT NULL,
 `film_id` int(11) NOT NULL,
 `actor_id` int(11) NOT NULL,
 `remark` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_film_actor_id` (`film_id`,`actor_id`)
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;
 INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

mysql> explain select * from actor; 

One row will be output for each table in the query. If two tables are queried via a join, two rows will be output.

explain two variants

1) explain extended: provides additional query optimization information based on explain. Then, you can use the show warnings command to get the optimized query statement and see what the optimizer optimized. There is also an additional filtered column, which is a half-ratio value. Rows * filtered/100 can estimate the number of rows that will be joined with the previous table in explain (the previous table refers to the table whose id value in explain is smaller than the id value of the current table).

2) explain partitions: Compared with explain, there is an additional partitions field. If the query is based on a partitioned table, the partitions that the query will access will be displayed.

In new versions such as MySQL 5.7 and above, you do not need to carry extended to query additional information. The explain extended command has been abolished in MySQL 8.0 and above, and we only need to use explain.

Columns in explain

Next we will show the information of each column in explain.

id column
The number of the id column is the serial number of the select. There are as many ids as there are selects, and the order of the id increases in the order in which the selects appear. The larger the id column, the higher the execution priority. If the id is the same, it will be executed from top to bottom. If the id is NULL, it will be executed last.
If the query has a join query, multiple ids appear. For example, if the query results in 1, 2, 3, the SQL with id 3 will be executed first. If the queried ids are 1, 1, 2, 3, and both ids are 1, the above SQL will be executed first.

select_type column
select_type indicates whether the corresponding row is a simple or complex query.
1).simple: simple query. The query does not contain subqueries and unions

 mysql> explain select * from film where id = 2; 

2).primary: the outermost select in a complex query
3).subquery: a subquery included in the select (not in the from clause)
4).derived: The subquery contained in the from clause. MySQL stores the results in a temporary table, also called a derived table.
Use this example to understand primary, subquery, and derived types

mysql> set session optimizer_switch='derived_merge=off'; #Disable the merge optimization of derived tables in MySQL 5.7. 2 explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der; 

Explain the graph queried by select_type above.
First, let's talk about the SQL with id 3. It is executed first because it is a subquery after from, so the corresponding select_type is derived . The SQL with id 2 is executed after it because it is included in the subquery in select (not in the from clause), so the corresponding select_type is subquery .
The SQL with id 1 is executed last. It is the outermost select in the complex query, so the corresponding select_type is primary .
Finally, don’t forget to restore the previously modified configuration:

mysql> set session optimizer_switch='derived_merge=on'; #Restore default configuration

5).union: The second and subsequent selects in a union

mysql> explain select 1 union all select 1; 

3.table columns

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 format of , indicating 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>, where 1 and 2 represent the select row IDs participating in the union.
deriven3 means querying the SQL with id 3 first, which means the current query depends on the query with id=3

4. Type column (more important)

This column indicates the association type or access type , which is how MySQL decides to find rows in the table and the approximate range of data row records to find. From best to worst, they are: system > const > eq_ref > ref > range > index > ALL
Generally speaking, you need to ensure that the query reaches the range level, preferably the ref level.
NULL : MySQL can decompose the query statement during the optimization phase, without accessing tables or indexes during the execution phase. For example, to select the minimum value in an index column, you can do this by searching the index alone, without having to access the table during execution.

mysql> explain select min(id) from film; 

Let me explain it here, because the underlying index data structure of MySQL is B+ tree, which has been explained in the previous article. The leaf nodes at the bottom of the B+ tree are arranged in order, increasing from left to right. This is the leftmost prefix principle. So to query the smallest value, you can directly get it from the leftmost index without searching. This is very efficient.

const, system : MySQL can optimize part of the query and convert it into a constant (see the results of show warnings). When all columns used for primary key or unique key are compared with constants, the table has at most one matching row and is read once, which is faster. System is a special case of const. It is system when there is only one matching tuple in the table.

mysql> explain extended select * from (select * from film where id = 1) tmp; 

mysql> show warnings; 

eq_ref : All parts of the primary key or unique key index are used in conjunction, and at most one record that meets the conditions will be returned. This is probably the best possible join type outside of const, and simple select queries will not have this type.

explain select * from film_actor left join film on film_actor.film_id = film.id; 

To explain, the film_id field of the film_actor above is a joint index, so the clustered index query corresponding to another table based on the secondary index is very fast.
ref : Compared with eq_ref, it does not use a unique index, but uses a normal index or a partial prefix of a unique index. The index is compared with a certain value, and multiple qualifying rows may be found.
1. Simple select query, name is a common index (non-unique index)

mysql> explain select * from film where name = 'film1'; 

2. Query the associated table. idx_film_actor_id is the joint index of film_id and actor_id. The left prefix of film_actor is used here.

mysql>explain select film_id from film left join film_actor on film.id = film_actor.film_id; 

range : Range scanning usually appears in operations such as in(), between,>,<, >=, etc. Use an index to retrieve a given range of rows

mysql> explain select * from actor where id > 1; 

index : You can get the result by scanning the entire index, usually scanning a secondary index. This scan does not start from the root node of the index tree for fast search, but directly traverses and scans the leaf nodes of the secondary index. The speed is still relatively slow. This query generally uses a covering index. The secondary index is generally small, so this is usually faster than ALL.

 mysql> explain select * from film; 

Explain why the secondary index is smaller than the clustered index, because the secondary index only stores the data of the current index, while the clustered index stores all the table data.
ALL : Full table scan, scanning all leaf nodes of your clustered index. Usually this requires adding indexes to optimize

5. possible_keys column

This column shows which indexes the query might use for its lookups.
When explaining, it may happen that possible_keys has a column, but key shows NULL. This is because there is not much data in the table, and MySQL believes that the index is not very helpful for this query, so it chooses to query the entire table.
If the column is NULL, there is no associated index. In this case, you can improve query performance by examining the where clause to see if you can create an appropriate index, and then use explain to view the effect.

6.Key column

This column shows which index MySQL actually uses to optimize access to the table.
If no index is used, this column is NULL. If you want to force MySQL to use or ignore the index on the possible_keys column, use force index or ignore index in your query.

7. key_len column

This column shows the number of bytes used by MySQL in the index. This value can be used to calculate which columns in the index are used. For example, the joint index idx_film_actor_id of film_actor consists of two int columns, film_id and actor_id, and each int is 4 bytes. From the key_len=4 in the result, we can infer that the query uses the first column: film_id column to perform index search.

mysql> explain select * from film_actor where film_id = 2; 

The key_len calculation rules are as follows:

• String, char(n) and varchar(n), in versions 5.0.3 and later, n represents the number of characters, not the number of bytes. If it is UTF-8, a number
• Or letters take up 1 byte, and a Chinese character takes up 3 bytes char(n): If the Chinese character is stored, the length is 3n bytes
• varchar(n): If storing Chinese characters, the length is 3n + 2 bytes. The additional 2 bytes are used to store the string length, because varchar is a variable-length string.
• Value type tinyint: 1 byte
• smallint: 2 bytes
• int: 4 bytes
• bigint: 8 bytes
• Time Type
• date: 3 bytes
• timestamp: 4 bytes
• datetime: 8 bytes
• If the field allows NULL, 1 byte is required to record whether it is NULL
• The maximum length of the index is 768 bytes. When the string is too long, MySQL will do a process similar to the left prefix index and extract the first half of the characters for indexing.

8.Ref column

This column shows the columns or constants used to find the value in the table in the index of the key column record. Common ones are: const (constant), field name (e.g. film.id)

9.rows

This column is the number of rows that MySQL estimates to read and detect. Note that this is not the number of rows in the result set.

10Extra Columns

This column displays additional information. Common important values ​​are as follows:
1) Using index : Using covering index
Definition of covering index : If the key in the explain result of MySQL execution plan has a using index, and the fields queried after select can be obtained from the tree of this index, this situation can generally be said to be using a covering index, and there is generally a using index in extra; covering indexes are generally aimed at auxiliary indexes, and the entire query result can be obtained only through the auxiliary index, without finding the primary key through the auxiliary index tree, and then using the primary key to get other field values ​​from the primary key index tree.
To put it simply, you don't need to go back to the table. You can get the desired result set through the secondary index, that is, the joint index.

mysql> explain select film_id from film_actor where film_id = 1; 

2) Using where: Use a where clause to process the results, and the columns queried are not covered by the index

mysql> explain select * from actor where name = 'a'; 

There is no index added to the name of the actor table here.
3) Using index condition : The queried columns are not completely covered by the index, and the where condition is a range of leading columns;
4) Using temporary : MySQL needs to create a temporary table to process the query. When this happens, optimization is usually required, and the first thing that comes to mind is using indexes to optimize.

In fact, there are many more, so I won’t introduce them one by one. If you are interested, you can check the official MySQL documentation yourself.

Indexing Best Practices

Example table:
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT 'Name',
 `age` int(11) NOT NULL DEFAULT '0' COMMENT 'Age',
 `position` varchar(20) NOT NULL DEFAULT '' COMMENT 'Position',
 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Job opening time',
 PRIMARY KEY (`id`),
 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='Employee record table';

 INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

Full value match

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei'; 

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 22; 

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 22 AND position = 'manager'; 

2. Leftmost prefix rule <br /> If multiple columns are indexed, the leftmost prefix rule must be followed. This means that the query starts at the leftmost column of the index and does not skip columns in the index.

 EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31;
 EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';
 EXPLAIN SELECT * FROM employees WHERE position = 'manager'; 

There will be three result sets above. Only the first SQL statement follows the leftmost prefix principle and uses the index for query. The other two SQL statements violate the leftmost prefix principle, that is, the query does not start from the name field, so the index is not used, resulting in index failure.
3. Do not perform any operations (calculations, functions, (automatic or manual) type conversion) on the index column, which will cause the index to fail and switch to a full table scan

 EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei'; 

The first sql uses the index, and the second sql causes the index to fail.
4. The storage engine cannot use the columns on the right side of the range condition in the index

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 22 AND position = 'manager';
2 EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age > 22 AND position = 'manager'; 

varchar(n): If storing Chinese characters, the length is 3n + 2 bytes. The additional 2 bytes are used to store the string length, because varchar is a variable-length string.
The name field occupies 3*24+2=74 bytes
The age field is Int and occupies 4 bytes.
74+4=78
This will cause the subsequent position index to fail
5. Try to use covering indexes (queries that only access the index (index columns include query columns)) to reduce select * statements

EXPLAIN SELECT name,age FROM employees WHERE name = 'LiLei' AND age = 23 AND position = 'manager'; 

6. MySQL cannot use indexes when using not equal (!= or <>), not in, not exists, which will cause a full table scan
< less than, > greater than, <=, >=, etc. The MySQL internal optimizer will evaluate whether to use the index based on multiple factors such as the retrieval ratio and table size.

is null,is not null In general, the index cannot be used

EXPLAIN SELECT * FROM employees WHERE name is null

Like starts with a wildcard character ('$abc...') and the MySQL index becomes invalid, which will cause a full table scan operation. Question: How to solve the problem that the like '% string %' index is not used?
a) When using a covering index, the query field must be the field for which the covering index is created

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';

b) If covering indexes cannot be used, you may need to use search engines
9. String indexing fails without single quotes

EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;

Use or or in sparingly. When you use them to query, MySQL may not use the index. The internal optimizer of MySQL will evaluate whether to use the index based on multiple factors such as the retrieval ratio and table size. For details, see Range Query Optimization Range Query Optimization Add a Single Value Index to Age

 ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE; 
explain select * from employees where age >=1 and age <=2000; 

Reason for not using the index: MySQL's internal optimizer will evaluate whether to use the index based on multiple factors such as retrieval ratio and table size. For example, in this example, the optimizer may choose not to use the index optimization method because the single data query is too large: the large range can be split into multiple small ranges.

 explain select * from employees where age >=1 and age <=1000;
 explain select * from employees where age >=1001 and age <=2000;

Restore the original index state

ALTER TABLE `employees` DROP INDEX `idx_age`;

Summary of index usage:

This is the end of this article about the detailed explanation and practical exercises of the MySQL tuning Explain tool. For more relevant MySQL tuning Explain tool content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!