1, 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. MySQL will set a flag on the query. Executing the query will return the information of the execution plan instead of
Execute this SQL

Note: if the from contains a subquery, the subquery will still be executed and the results will be put into the temporary table

2, Explain analysis example

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;

3, explain two variants

1. explain extended (since mysql 5.7 comes with explain, the previous version needs to be added):

Some additional query optimization information will be provided on the basis of explain. Following this, you can use the show warnings command to
To get the optimized query statement, so as to see what the optimizer has optimized. In addition, there is a filtered column, which is the value of a half score ratio, rows*
filtered/100 can estimate the number of rows to be connected with the previous table in explain (the previous table refers to the row whose id value in explain is smaller than the id value of the current table)
Table).

explain extended select * from film where id = 1;

If we use explain extended and show warnings to query together, we will see the optimization information in result 2. MySQL will optimize our sql and then execute it. Show warnings can see what the optimized sql looks like. This optimized sql may not be executable.

explain extended select * from film where id = 1;

show Warnings

2,explain partitions:

There are more partitions fields than explain. If the query is based on the partition table, the partitions that the query will access will be displayed.

However, we all divide databases and tables when there is a large amount of data, and rarely use partitions.

4, Columns in Explain

1. id column

The number of the id column is the serial number of the select. If there are several selections, there are several IDS, and the order of IDS increases in the order in which the select appears.

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.

2. select_type column

select_type indicates whether the corresponding row is a simple or complex query.

• Simple: simple query. The query does not contain subqueries and union s

explain select * from film where id = 1;

• primary: the outermost select in a complex query
• Subquery: subquery contained in the select (not in the from clause)
• Derived: the subquery contained in the from clause. MySQL will store the results in a temporary table, also known as derived
  Meaning) Use this example to understand the primary, subquery, and derived types

mysql> set session optimizer_switch='derived_merge=off'; #Close mysql5 7. Consolidation and optimization of derivative tables with new features
mysql> explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

We can see the query with id 3. The query type is DERIVED, indicating that the query is a temporary table of the table. We can also see that the query is a query after from.

However, there is a similar sub query after select. Why is this not called a temporary table?

Here we need to distinguish the following concepts:

1. The sub query after Select is called sub query
2. The sub query after From is called derived, derived table or temporary table

Let's look at the id column again. The higher the id, the higher the priority. The largest one here is 3, which indicates that the sub query after from will be executed first, then the sub query after select, and finally the most external query. This is also logical.

• Union: the second and subsequent select in the union

mysql> explain select 1 union all select 1;

3. table column

This column indicates which table a row of explain is accessing.

When there is a sub query in the from clause, the table column is the format, which means that the current query depends on the query with id=N. therefore, the query with id=N is executed first
Inquiry.

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.

4. type column

This column represents the association type or access type, that is, MySQL decides how to find rows in the table and the approximate range of data row records.

The order from the best to the worst is: system > const > Eq_ ref > ref > range > index > ALL

Generally speaking, it is necessary to ensure that the query reaches the range level, preferably ref

NULL: mysql can decompose query statements in the optimization phase, and there is no need to access tables or indexes in the execution phase. For example, selecting the minimum value in the index column can
This is done by looking up the index separately, and there is no need to access the table at execution time

mysql> explain select min(id) from film;

• const, system:

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

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 connected, and only one qualified record will be returned at most. This is probably the best join type outside const. This type will not appear in simple select queries

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

• ref:

Compared to eq_ref, instead of using a unique index, it uses a common index or a partial prefix of a unique index. If the index is compared with a certain value, it may
More than one eligible row was found.

1. Simple select query, where name is a common index (non unique index)

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

1. . association table query, idx_film_actor_id is film_id and actor_ For the joint index of ID, film is used here_ The left prefix of actor is film_id part

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

• range:

Range scanning usually occurs in in (), between, >, <, > = and other operations. Use an index to retrieve rows in a given range.

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

This range query using index needs to be queried between the leaf nodes of B + tree. If the amount of data is large, the efficiency is also relatively low, so we usually need to adopt paging.

• index:

The result can be obtained by scanning the full index. Generally, a secondary index is scanned. This kind of scanning does not start from the root node of the index tree, but directly
The speed of traversing and scanning the leaf nodes of the secondary index is still relatively slow. This kind of query generally uses the overlay index, and the secondary index is generally small, so this
Species are usually faster than ALL.

mysql> explain select * from film;

We have a primary key index and a common index on name in this table. When we query all, we find that it uses the name secondary index.