Complete Explain Treasure, SQL Optimization no longer have to worry

In everyday programming, we need to work with databases a lot, so this article brings you some summaries about Explain so that you can handle SQL better.

Add the explain keyword before the select statement, MySQL sets a tag on the query, and when the query is executed, it returns information about the execution plan instead of executing the SQL (if from contains a child query, the subquery is still executed and the results are placed in 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

Two variants

explain extended

Some additional query optimization information is provided on the basis of explain T he show warnings command is followed by an optimized query statement that shows what the optimizer has optimized. Additionally, there is the filtered column, which is a half-percentage value, and rows * filtered/100 the number of rows that will be connected to the previous table in explain (the previous table refers to a table in which the id value in explain is smaller than the current table id value)

mysql> explain extended select * from film where id = 

mysql> show warnings;

explain partitions

There is an additional partitions than explain and if the query is based on a partition table, the partition that the query will access is displayed.

(Recommended course: SQL tutorial.) )

id column

The number of the id column is the serial number of select a few select have several id and the order of id grows in the order in which select appears. MySQL divides select queries into simple queries (SIMPLE) and complex queries (PRIMARY). C omplex queries fall into three categories: simple subqueries, derived tables (subqueries in from statements), union queries. The larger the id column, the higher the execution priority, the same id executes from top to bottom, id is NULL last execution.

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 three complex queries mentioned above.

1) Simple simple query: The query does not contain subqueries and union

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

2) primary: The outermost select select a complex query

3) subquery: Subquery select in select (not in from clause)

4) derived: A subquery contained in the from clause. MySQL stores the results in a temporary table, also known as a derived table (the English meaning of derived), using this example to understand primary subquery and derived types

mysql> explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

5) union: the second and subsequent select in union

6) union result: select from union temporary table retrieves the results with this example to understand union and union result types:

mysql> explain select 1 union all select 1;

Table column

This column explain which table is being accessed by one of explain's rows. W hen there is a subquery in the from clause, table column is the format, indicating that the current query depends on a query of id=N and the query of id=N is executed first. When union is present, the table column of UNION RESULT has values <union1,2> and 1 and 2 represent select row ids that participate in union

Type column

This column represents the association type or access type, which MySQL how to find rows in a table and the approximate range of data row records. The best and worst are: system > const > eq_ref > ref > range > index > ALL in general, you have to ensure that the query range level, preferably ref

NULL

mysql is able to decompose query statements during the optimization phase without having to access tables or indexes during the execution phase. For example, select the minimum value in the index column, you can find the index separately to complete it, and you do not need to access the table at execution time

mysql> explain select min(id) from film;

const, system

mysql a part of the query and converts it into a constant (see the results of show warnings). W hen all columns for primary key or unique key are compared to constants, the table has up to one matching row, reads 1 time, and is faster. system is a special case of const and only one tuple in the table matches when it is system

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

eq_ref

All parts of primary key or unique unique key index are connected and only one eligible record is returned. This is probably the best join type outside of const and a simple select query does not appear with this type

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

ref

Instead eq_ref a unique index, you use a partial prefix for a normal index or a unique index, which may find more than one eligible row when compared to a value.

1. Simple select query, name is a normal index (non-unique index)

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

2. Association table query, idx_film_actor_id is a union index of film_id and actor_id which uses the left prefix film_actor part of film_id

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

range

Range scans usually appear in operations such as in() between > < >= Use an index to retrieve rows for a given range.

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

index

Scan the full table index, which is usually faster than ALL (index is read from the index and all is read from the hard disk)

mysql> explain select * from film;

ALL

That is, full table scanning means that mysql to find the rows it needs from start to finish. Typically, this requires an additional index to optimize

mysql> explain select * from actor;

(Recommended course: MySQL tutorial.) )

possible_keys column

This column shows which indexes the query might use to find. explain can occur possible_keys have columns, and key shows NULL because there is not much data in the table, mysql that the index is not very helpful for this query, and selects a full table query. I f the column is NULL there is no related 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 see how it works.

Key column

This column shows which index mysql actually uses to optimize access to the table. I f no index is used, the column is NULL If you want to force mysql to use or ignore indexes in possible_keys columns, use force index ignore index in ignore index

key_len column

This column shows the number of mysql uses in the index, which allows you to work out which columns in the index are specifically used. F or example, film_actor idx_film_actor_id index idx_film_actor_id consists of two int columns, film_id and actor_id and each int is 4 bytes. It can be inferred that the query uses the first column: film_id column to perform the index lookup by key_len=4 in the results.

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

"key_len calculation rules are as follows: string char(n): n-byte length varchar(n): 2-byte store string length, in the case of utf-8, length 3n plus 2 numeric type tinyint:1 byte smallint:2 byte int:4 byte bigint:8 byte time type date:3 byte timestamp:4 byte datetime:8 bytes"

If the field is allowed to be NULL the maximum length of the NULL index that requires a 1 byte record is 768 bytes, and when the string is too long, mysql does a left prefix-like index that extracts the characters from the first half of the index.

Ref column

This column shows the columns or constants used by the table to find values in the index of key column records, commonly: const (constant), field name (example: film.id)

Rows column

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

Extra column

This column shows additional information. Common important values are:

Using index

The columns of the query are overwritten by the index, and where filter is the leading column of the index, which is a sign of high performance. A n overlay index (which contains the fields of all queries) is typically used. For innodb there are significant improvements in secondary index performance

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

Using where

The column of the query is not overwritten by the index, where filter is a non-indexed leading column

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

Using where Using index

The column of the query is overwritten by the index, and where filter is one of the index columns but not a leading column of the index, meaning that eligible data cannot be queried directly through index lookups

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

NULL

The column of the query is not overwritten by the index, and where filter is the leading column of the index, which means that the index is used, but some fields are not overwritten by the index and must be implemented by "back to the table", not purely using the index, nor using the index at all

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

Using index condition

Similar to Using where the column of a query is not completely overwritten by an index, and the range of a leading column is in the where condition;

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

Using temporary

mysql to create a temporary table to handle the query. This is generally done to optimize, starting with the idea of indexing.

1. actor.name there is no index, a temporary table is created to distinct

mysql> explain select distinct name from actor;

2. film.name the idx_name index is established, at which point the query is extra using index and no temporary tables are used

mysql> explain select distinct name from film;

(Recommended micro-class: MySQL micro-course.) )

Using filesort

mysql uses an external index sort of the results instead of reading rows from the table in index order. mysql then browses through all eligible records based on the join type, saves the sort keywords and line pointers, then sorts the keywords and retrieves the row information in order. In this case, it is also generally considered to use indexes for optimization.

1. actor.name No index is created, the entire table actor is browsed, the sort keyword name and the corresponding id are saved, and the name sorted and row records are retrieved

mysql> explain select * from actor order by name;

2. film.name index is established idx_name at which point extra is using index at the time of the query

mysql> explain select * from film order by name;

Source: juejin.im/post/6863832433062739981

Above is W3Cschool编程狮 about the complete Explain treasure trove, SQL optimization no longer have to worry about the relevant introduction, I hope to help you.