MySQL installation document reference: blog.csdn.net/yougoule/ar…

Explain Tool Introduction

Use the Explain keyword to simulate the optimizer’s execution of SQL statements and analyze performance bottlenecks in your query statements or structures.

Add the explain keyword before the SELECT statement, MySQL sets a tag on the query, and executing the query returns information about the execution plan instead of executing the SQL statement.

Note: If a subquery is included in from, the subquery is still executed, putting the results into a temporary table.

Explain analysis examples

See the official document: dev.mysql.com/doc/refman/…

use demo;

- the sample 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'.'the 2017-12-22 15:27:18'), (2.'b'.'the 2017-12-22 15:27:18'), (3.'c'.'the 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);
Copy the code
EXPLAIN select * from actor;
Copy the code

Each table will output one row in the query, or two rows if two tables are joined to the query.

Explain the columns

Next we will show the information for each column in Explain

1, the id column

The id column is numbered as the sequence number of the select, and the number of ids increases in the order in which the select appears.

The larger the ID column is, the higher the execution priority is. If the IDS are the same, the execution is performed from the top down. If the ID is null, the execution is performed last.

2, select_type columns

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

  1. Simple: indicates simple query. Simple queries do not contain subqueries and unions.
explain select * from file where id = 2;
Copy the code

  1. Primary: the outermost select in a complex query
  2. Subquery: a subquery contained in a select (not in the FROM clause)
  3. Derived: Subqueries contained in the from sentence. MySQL stores the results in a temporary table, also known as a derived table

Use this example to understand the primary, SubQuery, and Derived types

set session optimizer_switch='derived_merge=off'; # turn off merge optimization for derived tables in mysql5.7
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
Copy the code

5. Union: The second and subsequent select in union

set session optimizer_switch='derived_merge=on'; Restore default Settings
explain select 1 union all select 1;
Copy the code

3, table columns

This column indicates which table is being accessed by the EXPLAIN row.

When the FROM clause has sub-queries, the table column is </Deriver >, indicating that the current query depends on the query whose ID is N. Therefore, the query whose ID is N is executed first.

When there is a union, the value of the table column of the union RESULT is <union 1,2>, and 1 and 2 represent the IDS of the select rows participating in the union.

Null: mysql can decompose the query during the optimization phase without accessing the table or index during the execution phase. For example, if you select a minimum value in the index column, you can do this by looking up the index separately without needing to access the table during execution.

explain select min(id) from film;
Copy the code

4, the type column

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

From the best to the worst: system > const > eq_ref > ref > range > index > ALL

In general, you can ensure that the query reaches the range level, preferably ref

Const, system: mysql can optimize part of a query and convert it to a constant. Select * from primary key; select * from primary key; select * from primary key;

System is a const exception; there is only one match in the table that matches system.

explain select * from (select * from film where id = 1) tmp;
Copy the code

eq_refAll parts of the: primary key or unique key index are joined, and at most one qualifying record is returned. This is probably the best type of join besides const, and a simple SELECT query will not yield this type.

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

Ref: Compared with eq_ref, the unique index is not applicable. Instead, the normal index or the partial prefix of the unique index is used. The index may be compared with a value and multiple rows may be found that match the condition.

  1. Simple SELECT query where name is a normal (not unique) index

    explain select * from film where name = 'film1';
Copy the code

  1. Associative table query, idx_FILm_actor_id is a joint query of film_id and actor_id, using the left prefix film_id part of film_actor.

    explain select film_id from film left join file_actor on film.id = film_actor.film_id;
Copy the code

The range scan is usually performed in(), between, >, <, >=, etc. Use an index to retrieve rows in a given range.

Index: scan the whole index can get the result, general is to scan a headset index, index tree root node this scan will not start quickly find, but directly to the secondary index of leaf node traversal and scan speed or slower, the query is commonly used to cover index, secondary indexes is compared commonly small, ALL this is faster than ALL.

All: a full table scan that scans all the leaf nodes in your cluster index.

5, possible_keys columns

This column shows which indexes the query might use to look up.

Possible_keys may have columns while key is null when explaining possible_keys. In this case, there is not much data in the table. Mysql thinks that the index is not helpful for the query and selects the full table query.

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 using Explain to see the effect.

6, the key columns

This column shows which index mysql actually uses to optimize access to the table.

If no index is used, the column is null. If you want to force mysql to use or ignore indexes in the possible_keys column, use force index and ignore index in the query.

7, key_len columns

This column shows the number of bytes used by mysql in the index. This value can be used to figure out which columns are used in the index.

For example, the joint index idx_FILm_ACtor_id for file_actor consists of two int columns film_id and actor_id, and each int is 4 bytes. From key_len = 4 of this result, it can be inferred that the query uses the first column, the film_ID column, to perform the index query.

explain select * from film_actor  force index (`idx_film_actor_id`) where film_id = 2;
Copy the code

Key_len is computed as follows:

  • String, char(n) and vARCHar (n). In versions after 5.0.3, n represents the number of characters, not the number of bytes. In UTF-8, a number or letter is one byte, and a Chinese character is three bytes
    • Char (n) : contains 3n bytes if Chinese characters are stored
    • Varchar (n) : the length is 3n + 2 bytes if storing Chinese characters. The extra 2 bytes are used to store the length of the string because varchar is a variable length string.
  • 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 field is allowed null, 1 byte is required to record whether the field is null

The maximum length of the index is 768 bytes. If the string is too long, mysql extracts the first half of the string from the index, similar to the left-most prefix index.

8, ref

This column shows the columns or constants used by the table to find values in the index of the key column. Common examples are const (constant), field names (e.g. Film.id).

9, rows of columns

This category 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.

10, Extra columns

This column presents additional information. Common important values are as follows:

  1. Using index: Uses an overwrite index

    Mysql > select * from mysql. select * from mysql. select * from mysql. select * from mysql. select *. Overwrite index is generally aimed at the secondary index, the entire query result can be obtained only through the secondary index, there is no need to find the primary key through the secondary index tree, in the primary key to obtain other field values in the index tree.

explain select film_id from film_actor where film_id = 1;
Copy the code

  1. Using WHERE: The WHERE statement is used to process the results and the queried columns are not overwritten by the index
explain select * from actor where name = 'a';
Copy the code

  1. Using index condition: The column of the query is not completely covered by the index. The WHERE condition is a range of leading columns.
explain select * from film_actor where film_id > 1;
Copy the code

  1. Using temporary: mysql needs to create a temporary table to process queries. This situation is generally to optimize, the first is to think of using indexes to optimize.

    4.1 Actor. name does not have an index, and a temporary table is created to be distinct.

    explain select distinct name from actor;
Copy the code

4.2. film.name create index (idx_name);

explain select distinct name from actor;
Copy the code

  1. Using filesort: External sorting will be used instead of indexed sorting, in memory when data is low, otherwise it will be done on disk. In this case, indexes are generally considered for optimization.

    5.1 Actor.name If no index is created, the entire actor table will be browsed, the sorting keyword name and the corresponding ID will be saved, and then the name will be sorted and the row records will be retrieved.

    explain select * from actor order by name;
Copy the code

5.2 Name specifies the idx_name index. Extra is using index

explain select * from film order by name;
Copy the code

  1. Select Tables Optimized Away: Use aggregation functions (such as Max, min) to access a field with an index.
explain select min(id) from film;
Copy the code