The database version used for this test is 5.7 initialize SQL statement:

CREATE TABLE `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`). KEY `idx_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=4 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;  INSERT INTO `xnyh`.`actor`(`id`, `name`, `update_time`) VALUES (1.'a'.'the 2017-12-02 15:27:18'); INSERT INTO `xnyh`.`actor`(`id`, `name`, `update_time`) VALUES (2.'b'.'the 2017-12-22 15:27:18'); INSERT INTO `xnyh`.`actor`(`id`, `name`, `update_time`) VALUES (3.'c'.'the 2017-12-22 15:27:18');  INSERT INTO `xnyh`.`film`(`id`, `name`) VALUES (2.'film 2'); INSERT INTO `xnyh`.`film`(`id`, `name`) VALUES (3.'film0'); INSERT INTO `xnyh`.`film`(`id`, `name`) VALUES (1.'film1');  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

We start with the exlpain statement,EXPLAIN SELECT * from dbThe result is as follows:Let’s explain each of the 12 fields in turn

The ID column

The value of the ID column represents the order in which the SELECT statement is executed and is associated with the select statement. If the VALUE of the ID column is empty, the value is executed first. If the value of the ID column is empty, the value is executed from top to bottom.

Select_type column

Represents the type of query and has the following values:

simple:

The subquery and join keywords are not includedexplain select * from film where id = 2;

Primary:

The outermost select statement in a complex query or the leftmost select in a union statementexplain select *,(select id from actor where id=1) from film The outermost query is film, so the query type corresponding to film is primary;

Subquery:

Explain select *,(select id from actor where id=1) from film

Derived:

Derived tables, which are produced if the FROM clause contains a SELECT statement after it, store intermediate results in temporary tables, but are required in 5.7set session optimizer_switch='derived_merge=off';Mysql > alter table merge (); alter table merge ();explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der; Finding that the query type did not derive, we turned off optimization and lookedset session optimizer_switch='derived_merge=off' Finding a derived query,

The union:

The second and subsequent select in the unionexplain select 1 union select 2 UNION select 3

The table column

The table column represents which table the current SELECT statement is querying.EXPLAIN SELECT id from actor UNION select id from film Select actor (actor), actor (actor), actor (actor), actor (actor), actor (actor), actor (actor2 > < union1,Instead.

The type column

The values of the type column are NULL>system > const > eq_ref > ref > range > index > ALL; The execution efficiency decreases in turn.

NULL:

Represents a query that can be completed directly when mysql is able to decompose a query statement in the optimization phase, without the need to query tables and indexes, for example, to get increments of maximum or minimum columns:EXPLAIN select min(id),max(id) from film

const:

SQL > select * from ‘where’; select * from ‘where’; select * from ‘where’;explain select * from film where id = 2 We can take a look at how mysql is optimized internally:explain EXTENDED select * from film where id = 2; show WARNINGS; You can see that mysql converts it directly to a constant for query

system:

To reach the SysyTEM level, it must meet the following conditions:

1. The table is a system table or a temporary table. 2

  • Mysql > alter table proxies_priv;explain select * from proxies_priv You can see that you have reached the System level.

  • Let’s look at another case: derived tables (temporary tables)explain extended select * from (select * from film where id = 1) tmp; You can see that the query type PRIMARY has reached the system level. It is queried from a derived table (temporary table), and the derived table has only one record, which can also reach the system level.

Eq_ref:

If a primary key or unique index is associated with another table or field, only one record will be returned.explain select * from film_actor left join film on film_actor.film_id = film.id; It can be seen that when the film table is accessed, the type reaches the eq_ref level. Because the ID field is unique in the FILM table, only one record will be associated with the film table when the film table is queried according to the ID.

ref:

In contrast to eq_ref,ref only requires prefix matches that are normal or union indexes

  • Normal index query

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

  • Union index prefix matchesexplain select film_id from film left join film_actor on film.id = film_actor.film_id;

range:

Range indexes, usually comparators such as in, > < >=, reach the range levelexplain select * from actor where id > 1;

index

Full table index scan: columns queried are indexed, but are not filtered by index field (except for invalidation operations)explain select * from film

all

When scanning a full table, usually, no index is created and index optimizations need to be addedexplain select * from actor;

possible_keys

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.

key

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 the possible_keys column, use force index and ignore index in the query.

key_len

This column records the length of the used index and is generally used to determine whether the combined index is valid. The value is calculated based on different data types.

Key_len is computed as follows:

  • string
    • Char (n) : Indicates the length of n bytes
    • Varchar (n) : 2 bytes the value is a string of 3n + 2 bytes if it is utf-8
  • 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

The maximum length of the index is 768 bytes. If the string is too long, mysql extracts the first half of the characters from the index.

We already created the federated index when we created the table film_actor

KEY `idx_film_actor_id` (`film_id`,`actor_id`)
Copy the code

We use this joint index for our calculations

  • Use only the first field of the union index:explain select * from film_actor where film_id = 2; Len = 4; len = 4; len = 4; len = 4; len = 4;

  • Use two fields of the joint index:explain select * from film_actor where film_id = 2 and actor_id = 3 The index length is 8, and the index length is 8, and the index length is 8.

ref

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

rows

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 shows additional information about the index. The main field information is as follows:

  • Fields in a Using index query are overwritten by the index

    explain select film_id from film_actor

  • Using WHERE where is not used to create an index.Explain select film_id from film_actor where remark = 'description ';

  • In a using index condition query, where condition is a range of leading columns; explain select * from film_actor where film_id = 1 and actor_id >3;

  • Create a temporary table. Create a temporary table during a mysql query. Optimization is required in this case.explain select distinct name from actor; Optimization can be achieved by adding indexes to de-duplicated fieldsCREATE index idx_name on actor(name) Delete the index that was created to facilitate subsequent tests:DROP INDEX idx_name on actor

  • If the amount of data is small, it is sorted in memory. If the amount of data is large, it is sorted in disk. Therefore, index optimization is required.explain select * from actor order by name; The following sort fields create the index, so sort within the indexexplain select * from film order by name;

  • Select Tables Optimized Away Used aggregation functions for a column query that has been indexedexplain select max(id) from actor

Wechat search “Happy Talk” focus on the handsome me, reply “dry goods to receive”, there will be a large number of interview materials and architects must read books waiting for you to choose oh.