Mysql Explain

This is the 13th day of my participation in the August More Text Challenge.More challenges in August

Since the blogger has recently been involved in Mysql optimization, please sort out the related Explain usage instructions

1 Overview of Explain

With Mysql 5.6,SELECT,INSERT,UPDATE, and DELETE can use EXPLAIN to see how Sql statements use indexes and join table queries. It can help you SELECT better indexes and optimize Sql statements to improve performance.

Data preparation

The following two tables are not related, mainly used to illustrate Explain

-- Prepare the student form
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL COMMENT 'primary key ID',
  `name` varchar(30) DEFAULT NULL COMMENT 'name',
  `age` int(11) DEFAULT NULL COMMENT 'age',
  `email` varchar(50) DEFAULT NULL COMMENT 'email'.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Prepare student data
INSERT INTO `test`.`user`(`id`, `name`, `age`, `email`) VALUES (1.'Jone'.18.'[email protected]');
INSERT INTO `test`.`user`(`id`, `name`, `age`, `email`) VALUES (2.'Jack'.20.'[email protected]');
INSERT INTO `test`.`user`(`id`, `name`, `age`, `email`) VALUES (3.'Tom'.28.'[email protected]');
INSERT INTO `test`.`user`(`id`, `name`, `age`, `email`) VALUES (4.'Sandy'.21.'[email protected]');


-- Prepare the student log form
CREATE TABLE `user_log` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `operation` varchar(20) NOT NULL COMMENT 'Operation type, insert/update/delete',
  `operate_time` datetime NOT NULL COMMENT 'Operation time',
  `operate_id` int(11) NOT NULL COMMENT 'Operation table ID',
  `operate_params` varchar(500) DEFAULT NULL COMMENT 'Operation parameters'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

Prepare student log data
INSERT INTO `test`.`user_log`(`id`, `operation`, `operate_time`, `operate_id`, `operate_params`) VALUES (1.'insert'.'the 2021-05-04 15:18:01'.5.'After insertion (ID :5, name:Billie, age:30, salary:[email protected])');
INSERT INTO `test`.`user_log`(`id`, `operation`, `operate_time`, `operate_id`, `operate_params`) VALUES (2.'update'.'the 2021-05-04 15:18:51'.5.'Before (ID :5, name:Billie, age:30, salary:[email protected]) after (ID5name :Billie, age:18, salary:[email protected])');
INSERT INTO `test`.`user_log`(`id`, `operation`, `operate_time`, `operate_id`, `operate_params`) VALUES (3.'delete'.'the 2021-05-04 15:18:58'.5.Name :Billie, Age :18, salary:[email protected]);
Copy the code

The Explain field was first used

EXPLAIN
SELECT
	ID
FROM
	`user`
Copy the code

field	instructions
id	Execution sequence. If the IDS are the same, the execution sequence is from top to bottom. Different ids. The higher the ID value is, the higher the priority is.
select_type	Query type, used to distinguish common query, joint query, sub-query and other queries.
table	Query the table name of the row
partitions	After version 5.7, which partition is used? You need to combine the table partition to see which partition is used. The non-partitioned table is NULL
type	Join type, which reflects the type used by the query.
possible_keys	To complete the query, you can use those indexes
key	Displays the index used in the query, or null if not
key_len	The maximum possible length of the index field, not the actual length used. With the same precision, the shorter the length, the better.
ref	Shows the column actually used by the index, which may be a constant
rows	The query data, the number of rows to read, is an estimate
filtered	New since version 5.7: Indicates the percentage estimate of the number of records that match the query criteria. You can calculate the number of rows in an associated table by multiplying rows and filtered
Extra	Parsing additional information for the query.

2 Field details

1 id

The ID is the sequence number of the select, representing the execution order. There are several ids for each select, and the id order increases according to the order in which the select appears.

The ids are the same and the execution sequence is from top to bottom. Different ids. A larger ID indicates a higher priority and is executed first. If the ID is null, the command is executed last.

Case 1

EXPLAIN
SELECT
	ID
FROM
	`user`
	where ID in (SELECT ID FROM user_log)
Copy the code

Sql > select * from ‘where’; select * from ‘where’;

Case 2

EXPLAIN
SELECT
	t1.ID 
FROM
   `user` t1
   left join  user_log t2 on t1.ID = t2.ID;
Copy the code

The Sql statement above is a join query, the order of the two tables is always, in order, first query table 1, then query table 2.

2 select_type

Query type, used to distinguish common query, joint query, sub-query and other queries.

1 SIMPLE

Simple query, no subquery or UNION in the query statement (see case 2)

2 PRIMARY

If it is not a simple query, then the outermost query is called a PRIMARY. The outermost query is the query on the outer layer of the subquery, or the leftmost query on the UNION,UNION ALL.

3 UNION

For queries that contain a UNION or UNION ALL, ALL small queries are unions, combined with type 2, except for the leftmost query (PRIMARY).

Case 3

EXPLAIN
SELECT
	t1.ID 
FROM
	`user` t1
   left join  user_log t2 on t1.ID = t2.ID;
Copy the code

The outermost layer, the left most, is the PRIMARY, uses the UNION, the rest of the UNION.

4 UNION RESULT

Mysql uses temporary tables to perform UNION de-repetitions. This type of query is used for temporary tables (see the last line of Case 3).

5 SUBQUERY

Subquery that is not in from.

Case 4

EXPLAIN SELECT
	* 
FROM
	`user` 
WHERE
	ID = (
	SELECT
		ID 
	FROM
		user 
	WHERE
		ID = 1 
	)
Copy the code

The query statement of a subquery cannot be changed to the corresponding IN or EXISTS form, and the subquery is irrelevant.

6 DEPENDENT SUBQUERY

If the query statement containing the subquery cannot be converted to the corresponding semi-join form, and the subquery is a correlation subquery.

Case 5

EXPLAIN SELECT
	* 
FROM
	`user` 
WHERE
	ID IN (
	SELECT
		ID 
	FROM
		user 
	WHERE
		ID = 1 
	)
Copy the code

7 DEPENDENT UNION

In a large query with UNION or UNION ALL, if ALL the smaller queries depend on the outer query, the select_type value of ALL the smaller queries except the leftmost query is DEPENDENT UNION

Case 6

EXPLAIN SELECT
	* 
FROM
	`user` 
WHERE
	ID IN (
	SELECT
		ID 
	FROM
		user 
	WHERE
		ID = 1 
	UNION 
		SELECT
		ID 
	FROM
		user_log
	WHERE
		ID = 1 
	)
Copy the code

8 DERIVED

The result is placed in a temporary table, also known as a derived table.

Case 7

EXPLAIN SELECT
	* 
FROM
	( SELECT * FROM user WHERE ID = 1 ) AS t1 
WHERE
	t1.ID = 1
Copy the code

A table with id 1 is

, which means the query is performed on a table that is materialized from the DERIVED table.

9 MATERIALIZED

When the query optimizer executes the statement containing the subquery, it chooses to materialize the subquery and connect it with the outer query, and the subquery is MATERIALIZED.

EXPLAIN SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2);
Copy the code

3 table

Table name of query row (visible table column 1)

4 partitions

After version 5.7, new fields, data partition information, which partition to use, need to be combined with the table partition to see. The non-partitioned table is NULL.

5 type

Join type, which reflects the type used by the query.

In order from best to worst:

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

1 system

System table with only one data.(See Case 7)

2 const

SQL > select primary key, primary key, or unique index

Case 8

EXPLAIN SELECT
	ID 
FROM
	`user_log`
	WHERE  ID = 1
Copy the code

3 eq_ref

A primary key or unique index that returns at most one piece of data.

4 ref

Left-most prefix match, non-primary key and unique index, returns multiple pieces of data.

Case 9

Select age from user where age = 1

-- 2 Run SQL
EXPLAIN SELECT
	*
FROM
	`user`
	WHERE  age = 1
Copy the code

5 fulltext

Fulltext indexing

6 ref_or_null

In the special case of ref,is null for one more query

7 index_merge

The index combined

8 unique_subquery

In (SELECT PK) a special scenario occurs.

9 index_subquery

Unique_subquery for a non-unique index.

10 range

Range queries

11 index

Scan table queries in index order

12 ALL

A full table scan

Case 10

EXPLAIN SELECT
	*
FROM
	`user`
Copy the code

6 possible_keys

Those indexes can be used to complete the query.

7 key

Displays the index used in the query, or null if not.

8 key_len

The maximum possible length of the index field, not the actual length used. With the same accuracy, the shorter the length, the better (see table 8,9).

9 ref

Shows the actual column used by the index, which may be a constant (see column 8,9)

10 rows

Query data, the number of rows to read, is an estimate (see column 9,10).

11 filtered

New since version 5.7: Indicates the percentage estimate of the number of records that match the query criteria. You can calculate the number of rows in an associated table by multiplying rows and filtered.

12 Extra

Parsing additional information for the query.

1 Using index

Use overwrite indexes.

2 Using index condition

Use index push-down, which simply adds conditional filtering to reduce the number of operations back to the table.

3 Using temporary

Temporary tables are used for sorting.

4 Using filesort

Sort using external index files.

5 Using where

Use where filtering.

6 Zero limit

Use LIMIT 0

7 Using sort_union() and Using union() and sing intersect()

Index merge is used

8 NULL

The query column is not covered by the index, and the WHERE filter 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 implemented by the table back.

9 No tables used

This additional information is prompted when there is no FROM clause in the query statement.

Case 11

EXPLAIN SELECT 1;
Copy the code

10 Impossible WHERE

This additional information is prompted when the WHERE clause of the query statement is always FALSE.

Case 12

EXPLAIN SELECT  
	*
FROM
	`user`
	WHERE 1 ! = 1
Copy the code