Explain Tool is introduced

   Use Explain Keyword can simulate optimizer execution sql sentence , Analyze the performance bottleneck of your query statement or structure select Add... Before the statement Explain keyword ,MySql The tag will be set on the query , Execution query will return execution plan information , Instead of implementing this sql.

Explain Analysis examples

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT ' full name ',
  `age` int NOT NULL DEFAULT '0' COMMENT ' Age ',
  `address` varchar(20) NOT NULL DEFAULT '' COMMENT ' Address ',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ' Creation time ',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_address` (`name`,`age`,`address`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT=' User information ';
--  Insert data 
drop procedure if exists insert_us;
delimiter ;;
create procedure insert_us()
BEGIN
	declare i int;
	set i = 1;
	while (i <= 100000) DO
		insert into users(`name`,age,address) VALUES(concat('SanFeng',i),i,' Beijing ');
		set i = i+1;
		end while;
	end;;
delimiter ;;
call insert_us();

Explain Two variants （MySql8 It's no longer usable ）

Explain extended

   Will be in explain On the basis of this, we provide some additional query optimization information . Pass immediately after show warnings Orders can be To get the optimized query statement

EXPLAIN EXTENDED SELECT * FROM users WHERE id = 1;
SHOW WARNINGS;

Explain partitions

   comparison Explain More than a partitions Field , If the query is based on a partitioned table , The partition that the query will access will be displayed

EXPLAIN partitions SELECT * FROM users WHERE id = 1;
SHOW WARNINGS;

Explain Column in

id Column

  id The number of the column is select The serial number of , There are several select There are just a few id, also id The order of select In increasing order .id The larger the column, the higher the priority of execution ,id The same goes from top to bottom ,id by null Finally, execute .

select_type Column

  select_type Indicates whether the corresponding query is simple or complex .

set session optimizer_switch='derived_merge=off';
explain select (select 1 from users where id = 1) from (select * from users where id = 1) tep; 
set session optimizer_switch='derived_merge=on';

simple

   Simple query . Does not contain subqueries and union

primary

   The outermost layer of complex queries select

subquery

   Included in select Subqueries in ( be not in from clause )

derived

   Included in from Subqueries in sentences

union

   stay union The second and the following select in

table Column

   table Indicates which table the current row is accessing , When from Clause has subqueries ,table Column is a format , Indicates the current query dependency id = N Query for , Execute first id = N Query for ; When there is union when .

partitions Column

   If the query is based on a partitioned table , The partition that the query will access will be displayed .

type Column

   This column represents the association type or access type , From the best to the worst are ：system > const > eq_ref > ref > range > index > all Generally speaking , We need to make sure that the query reaches range Level , It's best to achieve ref.

null

  MySql Be able to decompose query statements in optimization phase , There is no need to access tables or indexes during the execution phase .

system and const

  MySql It can optimize some part of the query and convert it into a constant , be used for primary key or unique key When all the columns of are compared with constants , So the table has at most one matching row , It is faster to read once ,system yes const The special case of

eq_ref

  primary key or unique key All parts of the index are connected and used , At most, only one matching record will be returned .

ref

   Compared with eq_ref, Don't use unique index , Instead, use the partial prefix of a normal index or a unique index , The index should be compared with a certain value , You may find more than one eligible row .

range

   Range scan usually appears in in()、between、<、>、>= And so on , Use an index to retrieve rows of a given range .

index

   Scan the full index and get the results , Generally, the secondary index is scanned , This kind of index will not start from the node of the index tree to quickly find , It is rather slow to traverse and scan the leaf nodes of the secondary index directly , This kind of query usually uses overlay index , The secondary index is generally small , So it's usually better than all faster .

all

   That is, full scan , Scan all leaf nodes of the clustered index of the current table , Usually, this requires adding indexes to optimize .

possible_keys Column

   This column shows which index columns the query can use to find , If the column is null, There is no relevant index .explain May appear possible_keys Listed but key Show null The situation of , This is because there is not much data in the table ,mysql I think the index is not helpful for this query, so I choose full table scan .

key Column

   This column shows MySql Which index is actually used to optimize the access of the change table . If index is not used , Then the column is null; If you want to force MySql Or neglect possible_keys Index in column , Use when querying force index、ignore index

key_len Column

   This column shows MySql The number of bytes used in the index , Through this value, you can calculate which columns in the index are used .

key_len Calculation rules

1. character string char(n) and varchar(n),5.0.x Later versions ,n Represents the number of characters , Not the number of bytes , If utf8, One 1 Numbers or letters occupy one byte , A Chinese character occupies 3 Bytes
   
   char(n)： If you save Chinese characters, the length is 3n byte
   
   varchar(n)： If you save Chinese characters, the length is 3n+2 byte , Plus 2 Bytes are used to store the string length , because varchar Is a variable length string
2. value type
   
   tinyint：1 byte
   
   smallint：2 byte
   
   int：4 byte
   
   bigint：8 byte
   
3. Time type
   date：3 byte
   
   timestamp：4 byte
   
   datetime：8 byte
   
4. If the field is allowed to be null, need 1 Is the byte record null

Be careful ： The maximum index length is 786 byte , But when the string is too long ,MySql Will do a similar left prefix index processing , Extract the first half of the string to index .

ref Column

   This column shows the re key In the index of the column record , The column or constant used by the table to find the value , Common are ：const, Field name

rows Column

   This column is MySql Estimate the number of rows to read and detect , Note that this is not the number of rows in the result set .

filtered Column

   This column is MySql Estimated percentage of result set records returned

extra Column

   This column shows additional information

Using index

   When our query list and search contain columns belonging to an index , That is, index coverage is used when querying

Using where

   Use where Statement to process the result , And the columns of the query are not covered by the index

Using index condition

   The columns of the query are not completely covered by the index ,where In the condition is a leading range , It is partially used to cover the index

Using temporary

  MySql You need to create a temporary table to process the query . In this case, optimization is generally required , First of all, I think of using index to optimize .

Using filesort

   Will use extra sort instead of index sort , Sort data from memory when data is small , Otherwise, you need to finish sorting on disk . In this case, it is generally necessary to consider using indexes to optimize

Not exists

   When we use the left connection , If where Clause contains a column that requires the driven table to be equal to null Value search criteria , And that column is not allowed to store null It's worth it , Then in the execution plan of this table Extra The column will prompt Not exists Additional information .

select tables optimized away

   Use some aggregate functions ( such as max、min) To access a field in the index is

Zero limit

   When our limit The argument to the clause is 0 when , It means that you don't intend to read any records from the table at all , Additional information will be prompted

Index push down

1. First, according to xxx > 'z’ This condition , Locate the corresponding in the secondary index idx_xxx Secondary index record .
2. Corresponding to the specified secondary index record , Don't rush back to your watch , Instead, check whether the conclusion meets xxx like '%a’ This condition , If the conditions are not met , Then there is no need to return to the table for the secondary index record .
3. For satisfying xxx like '%a’ The secondary index record of this condition is executed back to the table
   

   The operation of returning tables is actually a random IO, More time-consuming , The above operations reduce a lot of back table operations , Greatly improved performance , Call this behavior index push down .

   Be careful ： This use is MySql8.0.20