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.
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
Using the execution plan is very simple, just add the keyword explain in front of the sql to be executed.
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;
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
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
tinyint: 1 byte
smallint: 2 bytes
int: 4 bytes
bigint: 8 bytes
date: 3 bytes
timestamp: 4 bytes
datetime: 8 bytes
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 thetable 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 numberestimated 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:
;
;
. 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 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!