This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

What is the execution plan?

Use the EXPLAIN keyword to simulate the optimizer’s execution of SQL queries to see how MySQL is handling your SQL statements and to analyze performance bottlenecks in your queries or table structures.

Website: dev.mysql.com/doc/refman/…

Prerequisites: In all cases, the mysql version is 5.7.23

What does the execution plan help us accomplish?

  • The read order of the table
  • Operation type of data read operation
  • Which indexes are available
  • Which indexes are actually used
  • References between tables
  • How many rows per table are queried by the optimizer

How to use execution plan?

  • Expain + SQL statement
  • The execution plan contains information

The execution plan contains information explanation

id

The serial number of a SELECT query that contains a set of numbers indicating the order in which the SELECT clause or operation table is executed in the query

use oemp; # test table1
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `other_column` varchar(30) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; # test table2
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `other_column` varchar(30) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; # test table3
CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `other_column` varchar(30) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; # same id to explainselect t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id
and t3.other_column = ' '; Different explain # idselect t2.* from t2 where id = (select id from t1 where id = 
(select t3.id from t3 where t3.other_column = ' ')); #id the same and different simultaneously exist explainselect t2.* from (select t3.id from t3 where t3.other_column = ' ') s1,t2 
where s1.id = t2.id;
Copy the code

There are three cases: Same ID, different ID, same ID and different ID exist at the same time.

Same id

If the IDS are the same, the execution results are from the top down

  • The results

Id is different

Different ids If the query mode is self-query, the ID number increases. The higher the ID value, the higher the priority and the earlier the query is executed

  • The results

The same ID and different ids exist at the same time

If the ids are the same, they can be considered as a group and executed from the top down; In all groups, the greater the ID value, the higher the priority, the earlier the execution. Derivative = DERIVED

  • The execution result

Derived_merge, introduced in Mysql5.7, attempts to merge Derived Table view references, Common Table expressions, and outer queries. MySQL 5.7 is not implemented in a compatible way and can be circumvent by tuning optimizer_switch

set optimizer_switch='derived_merge=off';
Copy the code

To put it bluntly, if set to ON then the derived_merge line does not appear as follows:

select_type

Simple. Primary, Subquery, Derived, Union, and Union result query types are used to distinguish complex queries from common queries, federated queries, and subqueries

  • Simple: A simple select statement. The query does not contain a self-query or union
  • Primary, the outermost query is marked as primary if the query contains any complex subparts
  • Subquery, which contains subqueries in a SELECT or WHERE list
  • Derived, containing self-queries in the from list marked as derived MySQL recursively executes these self-queries, putting the results in temporary tables.
  • Union. If the second select appears after the union, it is marked as union. If union is included in the FROM subsentence query, the outer select marks the element as derived
  • Union result, select the result from the union table

​

table

  • This row of data is about that table

​

type

Types: all, index, range, ref, eq_ref, const, system,null type system > count > eq_ref > range > index > all

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

system

The table has only one row (equal to the system table), which is a const column

count

explain select * from (select * from t1 where id =1) d1;
Copy the code

Const is used to compare primary key or unique indexes. Because only one row of data is matched, MySQL can quickly convert the query to a constant if the primary key is placed in the WHERE list.

eq_ref

explain select * from t1, t2 where t1.id = t2.id;
Copy the code

Unique index scan. For each index key, only one record in the table matches it. Common for primary key or unique index scans. Query cases:

ref

# tb_emp ddl
CREATE TABLE `tb_emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL.PRIMARYKEY (`id`), ) ; Add age column to employee tablealter table tb_emp add column `age` int(11) default nullafter `name`; Add a composite indexcreate index idx_emp_name_age on tb_emp(`name`, `age`);

explain select * from tb_emp where `name` = 'z3';
Copy the code

A non-unique index scan, which returns all rows matching a single value, is essentially an index access, which returns all rows matching a single value. However, it may find multiple rows matching all criteria, so it should be a mixture of lookup and scan

range

explain select * from t1 where id between 1 and 3;

explain select * from t1 where id in (1.2.3);
Copy the code

Retrieves only rows within a given range, using an index to select rows. A range scan is better than a full table scan because it only needs to start at one point in the index and end at another.

index

explain select id from t1;
Copy the code

The Index type only traverses the Index tree, which is usually faster than ALL because Index files are usually smaller than data files.(all and index are read from the entire table, but index is read from the index, and all is read from the hard disk)Query result:

all

explain select * from t1;
Copy the code

Full Table Scan will traverse the entire Table to find matching rows Note: In general, you can ensure that queries are at least rang and preferably ref. ​

possible_keys

Displays one or more indexes that may be applied to this table. If an index exists on a field involved in the query, the index is listed but not necessarily used by the query.

key

If an overwrite index is used in a query, it only appears in the KEY list

explain select col1, col2  from t1;

create index idx_col1_col2 on t1(col1, col2);

explain select col1, col2  from t1;
Copy the code

Case 1 (before indexing) Case 2 (Indexed)

key_len

desc t1; 
explain select * from t1 where col1 = 'ab';
explain select * from t1 where col1 = 'ab' and col2 = 'bc';
Copy the code

Represents the number of bytes used in the index. This column can be used to calculate the length of the index used in the query. The shorter the length is, the better, without sacrificing accuracy. Key_len displays only the maximum possible length of the index field, ** not the actual length used **. That is, key_len e is computed from the table definition, not retrieved from the table.Query result: Summary: The more conditions, the greater the cost, the greater the length of key_len, it is suggested that under certain conditions, the shorter key_len, the higher the efficiency. ​

Rows

Based on table statistics and index selection, approximate the number of rows to read to find the desired record

filtered

Extra

Contains important additional information not suitable for other columns id, select_Type, table, type, possible_keys, key, key_len, ref, rows, Extra

1. Using filesort

File sorting

2. Using temporary

explain select col2 from t1 where col1 in ('ab'.'ac'.'as') group by col2 \G;

explain select col2 from t1 where col1 in ('ab'.'ac'.'as') 
group by col1, col2, col3 \G;
Copy the code

Temporary tables are used to hold intermediate results. MySQL uses temporary tables when sorting query results. Common in sort order by and group by queries. Example:

3. Using index

explain select col2 from  t1 where col1=100;

explain select col1, col2 from  t1;
Copy the code

Select operation using Index (Covering Index), avoid access to the table of data row, efficiency is good ~ if at the same time appear using where, indicating that the Index is used to perform the search Index key value; If using WHERE is not present at the same time, the index reference is used to read the data rather than perform the lookup action. Example:Covering Index

  • Covering Index (Covering Index), said for Index coverage
  • MySQL can use your index to return columns from the select list. MySQL does not have to read the data file again based on the index
  • An index is an efficient way to find rows, but a database can also use an index to find data for a column, so it doesn’t have to read the entire row. After all, index leaf nodes store the data they index. When you can get the data you want by reading the index, you don’t need to read rows. An index that contains (or overwrites) the data that meets the query result is called an overwritten index.
  • Note: If you want to use an overwrite index, you must note that the select list summary only fetuses the required columns, not select *; 2. If all fields are indexed at the same time, the index file is too large and the query performance deteriorates.

4. Using Where

Indicates where filtering is used

5. using join buffer

Link caching is used

6. impossible where

explain select * from t1 where 1=2;
Copy the code

The value of the WHERE clause is always false and cannot be used to get any tuples

7. select tbale optimized away

In the absence of the GROUPBY clause, optimizing MIN/MAX operations based on indexes or COUT (*) operations for MyISAM storage engines does not have to wait until the execution stage to be evaluated, and the optimization is completed at the generation stage of the query execution plan.

8. distinct

Optimization A DISTINCT operation stops searching for the same value immediately after finding the first matched ancestor.

For example

Example Description:

explain select d1.name, (select id from t3) d2 from 
(select id, name from t1 where other_column = ' ') d1 
union 
(select name, id from t2);
Copy the code

Query result:Case Study:

  • Select * from select_type; select * from select_type; select * from select_type; select * from select_type
  • Columns are marked as, indicating that the query result is derived from a derived table, where 3 in derived3 represents that the query is derived from a third SELECT query, i.e. Select 3 [select D1.name…
  • Line 2 (execute order 2) : id 3, part of the third select in the whole query, because the query is contained in from, so it is called. 【 SELECT id, name from where other_column = ”】
  • Select subquery (select id from t3) as select_type; select subquery (select id from t3) as select_type;
  • Select name,id from t2 as select_type; select * from t2 as select_type; select * from t2 as select_type; select * from t2 as select_type;
  • Row 5 (execution order 5) : represents the stage of reading rows from the temporary table for union.

    of the table columns indicates the union operation with the first and fourth select results. [Two result union operations]
    ,>

The resources

  • mysql.com
  • Dev.mysql.com/doc/refman/…

“Welcome to the discussion in the comments section. The excavation authorities will draw 100 nuggets in the comments section after project Diggnation. See the event article for details.”