introduce

  • MySQL provides an EXPLAIN command that analyzes the execution plan of SELECT statements and outputs details of SELECT execution for developers to optimize.
  • Use the Explain command to view an execution plan for one of these SQL statements, to see if the SQL statement has used an index, and to see if a full table scan has been done, which can be viewed using the Explain command.
  • You can use the Explain command to gain insight into MySQL’s overhead based optimizer, as well as to get a lot of detail about the access policies that the optimizer might consider, and which policies are expected to be adopted by the optimizer when running SQL statements.
  • The EXPLAIN command is very simple to use. Just add EXPLAIN before the SELECT statement, for example: 
    EXPLAIN SELECT * FROM user;
Copy the code

Execution Result:

Parameters that

The expain message has 10 columns, respectively

Id, select_Type, table, type, possible_keys, key, key_len, ref, rows, ExtraCopy the code
  • Case table
- the user table
create table user(
	id int primary key,
	login_name varchar(64), 
	name varchar(100),
	age int,
	sex char(1),
	dep int, 
	address varchar(100));- department of table
create table dep(
	id int primary key,
	name varchar(100));- address table
	create table addr(
	id int primary key,
	addr varchar(100));Create a normal index
alter table user add index idx_dep(dep);
Create a unique index
alter table user add unique index idx_login_name(login_name);
Create a composite index
alter table user add index idx_name_age_sex(name,age,sex);
Create a global index
alter table addr add fulltext ft_addr(addr);
Copy the code

id

  • A unique identifier automatically assigned to each SELECT statement

  • Represents the order of operations on tables in a query, in three cases:

Same ids: the execution sequence is from top to bottom. 2. Different ids: In sub-query cases, the IDS are automatically increased

  • An ID column with null indicates that it is a result set and does not need to be used for queries

select_type

The query type is used to distinguish common query, union query (union all), and subquery

simple

EXPLAIN SELECT * FROM user;
Copy the code

primary

The select_type of the outermost unit query is primary for a select that requires a union operation or that contains subqueries. And only one

 explain select (select name from user) from user ;
Copy the code

subquery

Except for the subquery contained in the FROM clause, any subquery that appears elsewhere may be a subquery

 explain select * from user where id = (select max(id) from user);
Copy the code

dependent subquery

Similar to the dependent Union, this subQuery query is affected by external table queries

 explain select id,name,(select name from dep a where a.id=b.dep) from user b;
Copy the code

union

Select * from select_type; select * from select_type; select * from select_type

 explain select * from user where sex='1' union select * from user where sex='2';
Copy the code

dependent union

As with union, it appears in a union or union all statement, but this query is affected by an external query

explain select * from user where sex in (select sex from user where sex='1' union select sex from user where sex='2');
Copy the code

union result

The result set containing the union, in the union and Union all statements, because it does not participate in the query, the ID field is null

derived

Subqueries that appear in the FROM clause are also called derived tables, which in other databases may be called inline views or nested select

table

  • The name of the query table that is displayed, if the query uses an alias
  • This displays as NULL if no operation on the data table is involved
  • If the table is enclosed in Angle brackets, it is a temporary table, and the following N is the ID of the execution plan, indicating that the result was generated from the query

  • , enclosed in Angle brackets, is similarly a temporary table, indicating that the result is from the result set M,N of the union query

type

  • From good to bad:
system, const, eq_ref,refFulltext, ref_OR_NULL, unique_subquery, index_subquery,range, index_merge, index,ALL
Copy the code

All types except all can use indexes. All types except index_merge can use only one index

  • Note: Index at least to range level

system

The table has only one row of data or is empty

const

When using a unique index or primary key and returning the equivalent WHERE condition that the record must be 1 row, type is usually const. Other databases are also called unique index scans

explain select * from user where id = (select max(id) from user);
Copy the code

eq_ref

This type is commonly used in join queries with multiple tables. It means that for each result of the previous table, only one row of the later table can be matched. In addition, the comparison operation of the query is usually ‘=’, which is highly efficient

explain select a.id from user a left join dep b on a.dep=b.id;
Copy the code

ref

For non-unique indexes, use equivalent (=) to query for non-primary keys. Or a query that uses the left-most prefix rule index

  • Nonunique index
explain select * from user where dep=1;
Copy the code

  • Equivalent non-primary key connection

  • The most left prefix

explain select * from user where name = '11111';
Copy the code

fulltext

Note that full text index is a high priority. If both full text index and normal index exist at the same time, mysql preferentially uses full text index regardless of the cost

explain select * from addr where match(addr) against('1111');
Copy the code

ref_or_null

Similar to the REF method, but with the addition of null comparisons. Not much actually

unique_subquery

Used for in-form subqueries in WHERE that return unique values that do not duplicate values

index_subquery

For in subqueries that use a secondary index or a list of IN constants, subqueries that may return duplicate values can be de-duplicated using the index

range

Index range scanning is common in queries that use operators such as >, <, is NULL, between, in, and like

explain select id from user where id >1000
Copy the code

index_merge

Select * from ref_or_NULL; select * from ref_or_NULL; select * from ref_or_NULL

index

Keyword: Condition is the node that appears in the index tree. An index can be scanned from top to bottom without a complete index match. This is common for queries that do not need to read data files using indexed columns, or queries that can be sorted or grouped using indexes

  • Single index
 explain select login_name from user
Copy the code

  • Composite index
explain select age from user;
Copy the code

ALL

This is the full table scan data file, and then filtered at the Server layer to return the records that meet the requirements

explain select * from user;
Copy the code

possible_keys

One or more indexes that may be selected in this query

key

If select_Type is index_merge, more than two indexes may appear. If other select_type is select_type, only one index may appear

key_len

  • The length of the index used to process the query is calculated. If it is a single-column index, the entire index length is counted. If it is a multi-column index, not all columns are counted
  • Take a look at the value of this column and calculate the total length of your multi-column index to see if you are using all columns
  • In addition, key_len only calculates the length of the index used by the WHERE condition, and sorting and grouping do not count to key_len if indexes are used

ref

  • If the constant equivalent query is used, const is displayed here
  • If it is a join query, the execution plan of the driven table shows the associated fields of the driven table
  • If the condition uses an expression or a function, or if the condition column is implicitly converted, it might be shown as func

rows

This is the estimated number of rows scanned in the execution plan, not the exact value (InnoDB is not the exact value, MyISAM is the exact value, mainly because InnoDB uses MVCC concurrency).

Extra

This column contains additional information that is important not to display in other columns. There are dozens of different types of information that can be displayed in this column

using temporary

  • Indicates that temporary tables are used to store intermediate results.
  • MySQL uses temporary tables for query results order by and group by
  • Temporary tables can be memory temporary tables or disk temporary tables, which cannot be seen in the execution plan. You need to check the status variable.

Used_tmp_table, used_tmp_disk_table

no tables used

A non-FROM or FROM dual query uses a not in() subquery or join query with the NOT EXISTS operator. This type of query is called an anti-join. In general, a join query queries the inner table first and then the outer table

Using filesort (important)

  • This occurs when the index is not available for sorting. This is common in order by and group by statements
  • Note MySQL uses an external index sort instead of reading data in index order
  • A sort operation in MySQL that cannot be done with an index is called “file sort”

Using index

The query data can be obtained directly through the index without going back to the table.

  • SELECT Index (Covering Index), avoid access to the table row, efficiency is good!
  • If Using Where is also present, the index is used to perform the lookup index key value
  • If Using Where is not present at the same time, the index is used to read data rather than perform lookup actions

Using where (important)

  • Indicates that not all records returned by the storage engine meet the search criteria and need to be filtered at the server layer
  • Query conditions are divided into restricted conditions and check conditions. Before 5.6, the storage engine can only scan data based on the restricted conditions and return data, and then the Server layer filters data based on the check conditions and returns data that truly matches the query. 5.6.x supports THE ICP feature, which can push the inspection conditions down to the storage engine layer. Data that does not meet the inspection conditions and restrictions will not be read directly, which greatly reduces the number of records scanned by the storage engine. The extra column shows the using index condition

firstmatch(tb_name)

One of the new features introduced by 5.6.x to optimize subqueries is commonly seen in where clauses with type in(). This may occur if the inner table contains a large amount of data

loosescan(m.. n)

One of the new features introduced after 5.6.x to optimize subqueries, this can occur in subqueries of type IN () where there may be duplicate records returned by the subquery

  • In addition to these, there are many query data dictionary libraries, and the execution of the plan shows that the results are not likely to exist

filtered

This column appears when you use Explain Extended, which is available by default in versions after 5.7 and is not required. This field represents the percentage of the data returned by the storage engine that is filtered through the server layer to meet the number of records in the query. Note that it is a percentage, not the specific number of records