The article was first published on an official account (Yuebanfeiyu), and then synchronized to the personal website: xiaoflyfish.cn/

Public number: month with flying fish, make a friend, into the interview exchange group

  • The public account responds to 666 backstage, you can get free electronic books

Feel good, hope to like, look, forward to support, thank you

If you add the explain keyword before the SELECT statement, MySQL will set a tag on the query. When executing the query, it will return information about the execution plan, instead of executing the SQL. (If there is a subquery in the FROM statement, the subquery will still be executed, putting the results into a temporary table.)

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;


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;


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
Copy the code

Two variants

explain extended

Additional query optimization information will be provided in addition to explain.

This is followed by the show Warnings command to get the optimized query statement to see what the optimizer optimized.

There are additional filtered columns, which are half-scale values, and rows * filtered/100 estimate the number of rows that will be connected to the previous table in Explain (the previous table in Explain has a lower ID than the current table ID).

mysql> explain extended select * from film where id = 1;
Copy the code

mysql> show warnings;
Copy the code

explain partitions

There is an additional PARTITIONS field compared to Explain that shows which partitions the query will access if the query is based on a partitioned table.

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.

MySQL classifies select queries into SIMPLE and PRIMARY queries.

Complex queries fall into three categories:

  • Simple subqueries, derived tables (subqueries in from statements), union queries.
  • 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.

Select_type column

Select_type indicates whether the corresponding row is a simple or complex query, and if it is a complex query, which of the above three complex queries.

1. Simple Simple query. The query does not contain subqueries and unions

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

2) primary: the outermost select in a complex query

3) subquery: subquery included in select (not in the FROM clause)

4) Derived: Subqueries contained in the from clause.

MySQL stores the results in a temporary table, also known as a derived table. Use this example to learn about the primary, SubQuery, and Derived types

mysql> 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

Use this example to learn about the union and union result types:

mysql> explain select 1 union all select 1;
Copy the code

The table column

This column indicates which table is being accessed by the EXPLAIN row. When there is a subquery in the FROM clause, the table column is format, indicating that the current query depends on the query id=N, so the query id=N is executed first.

When there is a union, the value of the table column of the Union RESULT is

, and 1 and 2 represent the IDS of the select rows participating in the union.
,2>

The type column

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

System > const > eq_ref > ref > range > index > ALL Generally speaking, it is necessary to ensure that the query reaches the range level, and ref is the best

NULL

Mysql can decompose queries during the optimization phase without having to access tables or indexes 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 accessing the table during execution.

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

const, system

Mysql can optimize part of a query and convert it to a constant (see the result of show Warnings).

Select * from primary key; select * from constant; select * from primary key;

System is a special case of const; it is system if only one tuple in the table matches.

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

eq_ref

All 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 it does not appear in simple SELECT queries.

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

ref

Instead of using a unique index, eq_ref uses a normal index or a partial prefix of a unique index, which is compared to a value and may find multiple rows that match the condition.

  1. Simple SELECT query, name is normal index (not unique index)
mysql> explain select * from film where name = "film1";
Copy the code

2. Associative table query, idx_FILm_actor_id is the joint index of film_id and actor_id. Here, use the left prefix film_id of film_actor.

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

range

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

mysql> explain select * from actor where id > 1;
Copy the code

index

Scan a full table index, which is usually faster than ALL. (Index is read from the index, while all is read from the hard disk)

mysql> explain select * from film;
Copy the code

ALL

Full table scan, which means mysql has to look for rows from beginning to end. Normally this would need to be optimized by adding an index

mysql> explain select * from actor;
Copy the code

Possible_keys column

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, it is because 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.

The key column

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.

Key_len column

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 film_actor consists of two int columns film_id and actor_id, and each int is 4 bytes.

From key_len=4 in the result, it can be inferred that the query uses the first column, the film_ID column, to perform the index lookup.

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

“

The calculation rules for key_len are as follows: string char(n) : n bytes long varchar(n) : 2 bytes the storage string is a string of 3n + 2 bytes if it is utf-8. Value type tinyint: 1 bytes smallint: 2 bytes int: 4 bytes bigint: 8 bytes Time type Date: 3 bytes TIMESTAMP: 4 bytes Datetime: 8 bytes

“

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.

Ref column

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).

Rows column

This column is the number of rows that mysql expects to read and detect, note that this is not the number of rows in the result set.

Extra column

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

Using index

The columns of the query are overwritten by the index, and the WHERE filter condition is the leading column of the index, which is a high performance performance.

Typically, an overwrite index is used (the index contains all the fields of the query). For InnoDB, secondary indexes can improve performance

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

Using where

The column in the query is not covered by the index, and where filters the leading column that is not the index

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

Using where Using index

The columns of the query are overwritten by the index, and the WHERE filter condition is one of the index columns but not the leading column of the index, meaning that the data that matches the condition cannot be queried directly through the index lookup

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

NULL

The columns in the query are not covered by the index, and the WHERE filter condition is the leading column of the index, meaning that the index is used, but some fields are not covered by the index, and must be “back to the table”, not only using the index, but also not completely missing the index

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

Using index condition

Similar to Using WHERE, the columns of the query are not completely covered by the index; the where condition is a range of leading columns;

mysql> explain select * from film_actor where film_id > 1;
Copy the code

Using temporary

Mysql needs to create a temporary table to process the query. This situation is generally to optimize, the first is to think of using indexes to optimize.

  1. Actor.name has no index and a temporary table is created to be distinct
mysql> explain select distinct name from actor;
Copy the code
  1. Film. name select idx_name, extra select using index
mysql> explain select distinct name from film;
Copy the code

Using filesort

Mysql uses an external index sort on the results, rather than reading rows from the table in index order.

At this point, mysql will browse all eligible records based on the join type, save the sort key and row pointer, then sort the key and retrieve the row information in order.

In this case, index optimization is also generally considered.

  1. Actor.name does not create an index, which browses the entire actor table, saves the sort keyword name and the corresponding ID, then sorts the name and retrieves the row record.
mysql> explain select * from actor order by name;
Copy the code
  1. Film. name select idx_name and extra select using index
mysql> explain select * from film order by name;
Copy the code