What is the execution plan in mysql

In mysql, the execution plan is a set of tools provided by the database to users to parse, analyze, and optimize SQL statements. The functions of the execution plan are: 1. Display the reading order of the table; 2. The type of data reading operation; 3. Display which indexes can be used; 4. Display which indexes are actually used; 5. Display the table The reference relationship between them; 6. Display the number of rows queried in each table.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

When querying the database, we usually use sql statements to query the data we need. However, we don’t know how SQL is executed in the database, whether it uses indexes, which indexes are used, which fields and tables are searched, what their order is, how much time they take, etc., so Is there any way to see this information? MySQL provides us with a set of tools - execution plans.

1. What is an execution plan?

The execution plan is a set of tools provided by the database to parse, analyze, and optimize SQL statements. It has the following functions Function:

• Display the reading order of the table;

• Type of data reading operation;

• Which indexes can be used;

• Which indexes are actually used;

• Reference relationships between tables;

• The number of rows queried for each table.

Note: The execution plan is just the best optimization reference plan given by the database for sql, and is not necessarily the optimal solution, that is, Don’t trust the execution plan too much

2. How to use the execution plan

Using the execution plan is very simple, just add the keyword explain in front of the sql to be executed.

3. Execution plan information

As can be seen from the figure, the sql execution plan mainly contains the following information: id, select_type ,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra.

3.1. id

#select queries the serial number. If the id is the same, the execution order is from top to bottom; if the id is different, the larger the id value will be given priority. The higher the level, the earlier it is executed;

3.2, select_type

select_type: Indicates the type of select statement, which can have the following values;

• SIMPLE: Represents a simple query, which does not include connection queries and subqueries;

• PRIMARY: Represents the main query, or the outermost query The query statement;

• UNION: indicates the second or subsequent query statement of the connection query;

• DEPENDENT UNION: in UNION The second or subsequent SELECT statement depends on the external query;

• UNION RESULT: The result of the connection query;

• SUBQUERY : The first SELECT statement in the subquery;

• DEPENDENT SUBQUERY: The first SELECT statement in the subquery, depends on the external query;

• DERIVED: SELECT (subquery of FROM clause).

3.3, table

table: Indicates the query table name, which can be in the following situations:

• Display the table name. If an alias is given, the alias is displayed;

• : Indicates that the query condition is a subquery;

• : Indicates that table 1 and table 2 use union.

3.4, partitions

partitions: Matching partitions.

3.5. type

type: This column represents the table association type or access type, that is, the database determines how to find rows in the table. Find the approximate range of data row records. From the best to the worst, they are: system > const > eq_ref > ref > range > index > all

• ##system: There is only one row of records in the table, which is quite For system tables, this is a special column of the const type, which does not usually appear and can be ignored;

• const: One hit through the index, matching one row of data, so it is very fast and commonly used. For queries on PRIMARY KEY or UNIQUE index, it can be understood that const is optimized;

• eq_ref: unique index scan, for each index key, there is only one record in the table with it Matching, commonly used primary key or unique index scan, this may be the best join type besides const;

• ref: non-unique index scan, returns matching a single value All rows of indexed columns for =, operators;

• Range: Only retrieve rows in a given range, use an index to select rows, generally used for queries such as between, , in, etc. This range query is better than index. Because it only needs to scan one point of the index and ends at another point;

• index: needs to traverse the index tree;

• all: that is Full table scan means that the database needs to find the required rows from beginning to end. Usually this requires adding indexes for optimization.

Note: When optimizing SQL, you must optimize to at least range. It is recommended to optimize to ref, preferably const.

3.6. possible_keys

possible_keys: This column shows which indexes the query may use to find. When explain, there may be a situation where possible_keys has a column, but key displays NULL. This is because there is not much data in the table, and the database thinks that the index is not helpful for this query, so it chooses a full table query.
If the column is NULL, there is no associated index. In this case, you can check the where clause to see if you can create an appropriate index to improve query performance, and then use explain to see the effect.

3.7, key

key: Displays the key (index) actually decided to use in the database . If no index is selected, the value of key is NULL. Indexes can be forced to be used or ignored.

3.8, key_len

key_len: This column shows the number of bytes used by the database in the index. This value can be used to calculate the specific number of bytes used in the index. Which columns, the numerical calculation is as follows:

String type

char(n): n byte length
varchar(n): 2 bytes storage string length, if it is utf-8 , then the length is 3n 2

numeric type

tinyint: 1 byte
smallint: 2 bytes
int: 4 bytes
bigint: 8 bytes

Time type

date: 3 bytes
timestamp: 4 bytes
datetime: 8 bytes

If the field is allowed to be NULL, 1 byte is required to record whether it is NULL

Note: The maximum index length is 768 bytes. When the string is too long, the database will do a process similar to the left prefix index and extract the first half of the characters for indexing.

3.9, ref

ref: This column shows the

table lookup value used in the index of the key column record Column or constant , common ones are: const (constant), func, null, field name (for example: film.id)

3.10, rows

rows: This column is the number

estimated by the database to be read and scanned. Note that this is not the number of rows in the result set, so the smaller the value, the better.

3.11, filteredfiltered: Return the number of rows in the

result as a percentage of the number of rows read

, the higher the value Bigger is better.

3.12, Extraextra: This column displays

extra information

, which is not included in other columns The information, the specific value is as follows:

distinct: After the database finds the first matching row, it stops searching for more rows for the current row combination;
not exists: The database can perform LEFT JOIN optimization on the query. After finding a row matching the LEFT JOIN standard, no more rows will be checked in the table for the previous row combination;
range checked for each record (index map: #): The database did not find a good index that can be used, but found that if the column value from the previous table is known, it may be partially Indexes can be used;
using filesort (key point): The database will use an external index to sort the results, instead of reading rows from the table in index order. At this time, mysql will browse all eligible records according to the connection type, save the sorting keywords and row pointers, and then sort the keywords and retrieve row information in order. In this case, it is generally necessary to consider
• using the index to optimize

  ;

using index (key point): starting from using only the information in the index tree No further search is required to read the actual rows to retrieve the column information in the table, which means
• select uses a covering index without having to go back to the table query

  ;

using temporary (emphasis): The database needs to
• create a temporary table to process the query

  . This situation is common in order by and group by. When this happens, optimization is generally required. The first thing to do is to use an index to optimize;

using where: The database will filter after the storage engine retrieves the rows. That is to read the entire row of data first, and then check according to the where condition. If it matches, it will be kept, if it does not match, it will be discarded;
using index condition: similar to Using where, the query column Not completely covered by the index, the where condition is the range of a leading column;
using sort_union(...), Using union(...), Using intersect(.. .): These functions illustrate how to merge index scans for the index_merge join type;

• using index for group-by: Similar to the Using index method of accessing a table, Using index for group-by means that the database has found an index that can be used to query group by or distinct queries. All columns, instead of additionally searching the hard disk to access the actual table;

• null: The queried column is not covered by the index, and the where filter condition is the leading column of the index, which means that the index is used , but some fields are not covered by the index and must be implemented through "table return". The index is not purely used, nor is the index not used at all. Even if the index is used but a table return operation is required, the table return operation should be avoided.

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is the execution plan in mysql. For more information, please follow other related articles on the PHP Chinese website!