MySQL SQL execution plan

View MySQL Execution plan

Use Navicat to view the MySQL execution plan:

Open Profile Analysis tool:

Check to see if it takes effect: Show variable like '%profil% ';

View process: Show Processlist;

Select database: Use Db_jiakao;

Type of all analysis: Show profile All;

View Table index: Show index from user_member;# #查看表索引

Use the explain command to view the performance of the query statement:

EXPLAIN SELECT * from user_feedback;# #查看执行计划中的sql性能

The first query is a full-table scan, and the second is an index scan:

The difference is that Type:all is a full-table scan index

Select_type: Whether it is a complex statement

The following is a description of the MySQL documentation about the ref connection type:

"For each combination of records in another table, MySQL reads all records with matching index values from the current table. If the connection operation uses only the leftmost prefix of the key, or if the key is not a unique or primary key type (in other words, if the connection operation cannot select a unique row based on the key value), then MySQL uses the ref connection type. If the key used by the connection operation matches only a small number of records, ref is a good type of connection. ”

In this example, because the index is not a unique type, ref is the best connection type we can get.

If explain shows that the connection type is "All" and you do not want to select most of the records from the table, then MySQL will be very inefficient because it will scan the entire table. You can add more indexes to solve this problem. For more information, see the MySQL manual for instructions.
Possible_keys:
The name of the index that may be available. The index name here is the index nickname specified when the index was created, or if the index does not have a nickname, the first column in the index is displayed by default. The meaning of the default index name is often not obvious.

Key:
It shows the name of the index that MySQL actually uses. If it is empty (or null), then MySQL does not use the index.
Key_len:
The length of the part to be used in the index, in bytes.
Ref
It shows the name of the column (or the word "const"), and MySQL will select rows based on these columns. In this example, MySQL selects rows based on three constants.
Rows
MySQL considers the number of records that it must scan before it can find the correct results. Obviously, the ideal number here is 1.
Extra:
Many different options may appear here, most of which will adversely affect the query. In this case, MySQL just reminds us that it will restrict the search result set with a WHERE clause

Id:query the serial number of the query in the execution plan selected by Optimizer;
Select_type: The type of query used, mainly with the following types of queries
◇dependent subquery: The first select in the inner layer of a subquery, depends on the result set of the external query;
◇dependent Union: Union in the subquery, and all after the second select from the Union
SELECT, which also relies on the result set of the external query;
◇primary: The outermost query in the subquery, note is not the primary key query;
◇simple: A query other than a subquery or union;
◇subquery: Subquery The first select of the inner layer query, the result is not dependent on the external query result set;
◇uncacheable subquery: Subquery that the result set cannot be cached;
All select after the second select starts in the ◇union:union statement, the first select is primary
The results of merging in ◇union Result:union;
Table: Displays the names of the tables in the database accessed by this step;
Type: tells us how to access the table, mainly contains the following set of types;
◇all: Full table scan
◇const: Read constant, and at most only one record match, because is a constant, so actually only need to read once;
◇eq_ref: There will be at most one matching result, usually accessed through a primary key or a unique key index;
◇fulltext:
◇index: Full index scan;
◇index_merge: Two (or more) indexes are used in the query, and then the index results are merge and then read
Table data;
◇index_subquery: The returned result field combination in a subquery is an index (or combination of indexes), but not a
Primary key or unique index;
◇rang: Index range scan;
The query is referenced by the driver table index in the ◇ref:join statement;
◇ref_or_null: The only difference from ref is a query that adds a null value in addition to the index reference query;
◇system: System table, only one row of data in the table;
◇unique_subquery: The returned result field combination in a subquery is a primary KEY or a unique constraint;
◇
Possible_keys: The index that the query can take advantage of. If no index is available, it will be displayed as NULL, which
The item content is very important for optimizing the index adjustment;
Key:mysql Query Optimizer The index selected from the Possible_keys;
Key_len: The index key length that is selected to use the index;
REF: Lists whether a constant (const) or a field of a table (if it is a join) is filtered (via key)
Of
Rows:mysql Query Optimizer The number of result set records that are estimated by the statistical information collected by the system;
Extra: The additional details that are implemented in each step of the query are likely to be the following:
◇distinct: Finds the Distinct value, so when MySQL finds the first matching result, it stops checking the value
Queries that are converted to the other values later;
◇full scan on null key: An optimization method in a subquery that is primarily encountered when NULL cannot be accessed through an index
Use of the value;
◇impossible WHERE noticed after reading const TABLES:MYSQL Query Optimizer through
The statistical information collected determines that there is no possible result;
The ◇no tables:query statement uses the from DUAL or does not contain any FROM clause;
◇not exists: In some left connections MySQL query Optimizer by changing the composition of the original query
Using the optimization method, can partially reduce the number of data access;
◇range checked for each record (index Map:n): Described by the official MySQL manual, when
MySQL Query Optimizer did not find a good index to use when, if found to come from the previous
The table's column values are known, and some indexes may be used. For each row combination of the preceding table, MySQL checks to see if the
Use the range or Index_merge access method to request a line.
◇select tables Optimized away: When we use some aggregate functions to access a field that has an index
, MySQL Query Optimizer will navigate directly to the desired data row through the index to complete the
Polling Of course, the premise is that you cannot have group by operations in query. If you use min () or Max ()
Waiting
◇using Filesort: When we include an order by operation in our query and cannot use the index to complete the sort
, MySQL Query Optimizer has to choose the appropriate sorting algorithm to implement.
◇using Index: The required data only need to be in the index can be all obtained without the need to fetch data in the table;
◇using index for group-by: As with data access and Using index, the required data is only required to read the index
When a group BY or DISTINCT clause is used in query, if the Group field is also indexed
, the information in the Extra will be the using index for group-by;
◇using temporary: When MySQL must use temporary tables in certain operations, it will be in the extra information
The using temporary appears. It is mostly common in operations such as group by and order by.
◇using Where: If we are not reading all the data from the table, or not just by indexing, we can get all the required
Data, the using where information appears;
◇using where with pushed condition: this is a only in the Ndbcluster storage engine will appear
Information, but it also needs to be enabled by turning on the Condition Pushdown optimization feature before it can be used. Control parameters
For Engine_condition_pushdown.

MySQL SQL execution plan