Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to view the SQL implementation plan of MySQL 5.7. the editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

You can use the explain statement to view the execution plan of SQL, and the information about the execution plan comes from the optimizer.

In MySQL 5. 7, you can view the execution plan for SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

More detailed execution plan information can be obtained at MySQL 5.7.3 beyond EXTENDED, and EXPLAIN PARTITIONS is useful when getting execution plans with partition tables.

The FORMAT option can be used to select the format of the output. TRADITIONAL stands for output in tabular form, which is the default output format. The JSON format represents the output in JSON format, and the output contains extended execution plan information and partition table information.

If the index is not working properly, you can update the statistics of the table, such as the cardinality of key values, by running the ANALYZE TABLE command, which affects the choice of the optimizer.

Mysql > explain select * from emp e where e.deptno not in (select deptno from dept d)

+- -- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+- -- +

| | 1 | PRIMARY | e | ALL | NULL | NULL | NULL | NULL | 14 | Using where |

| | 2 | DEPENDENT SUBQUERY | d | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using index; Using where; Full scan on NULL key |

+- -- +

2 rows in set (0.00 sec)

Output field description:

Execution sequence number of the id query

Select_type query types, including:

SIMPLE simple query (without using UNION or subqueries)

The outermost SELECT statement of PRIMARY

The second and more SELECT statements of UNION in the UNION structure

The second or more SELECT statements of DEPENDENT UNION in the UNION structure, depending on the outer query

Results of UNION RESULT UNION

The first SELECT statement in the SUBQUERY subquery

The first SELECT statement in the DEPENDENT SUBQUERY subquery, depending on the outer query

Statements after FROM in a DERIVED subquery

MATERIALIZED materialized view subquery

Subqueries in which UNCACHEABLE SUBQUERY query results are not cached and need to be re-outer to calculate each row of data

The second and subsequent SELECT statements in the UNCACHEABLE UNION UNION structure and no query cache is generated

Table table name

Types of type table joins, including:

There is only one row of data in the system table, which is a special case of cost connection types

There is only one row of matching records in the const table and is read first in the query

When eq_ref and the previous table make a nested loop connection, there is a row of matching records each time the join fields in the two tables are compared. This type is used when all parts of the index are used for joins and the index is a primary key index or a UNIQUE non-null index.

Every time ref joins a previous table, it reads all the index values that match the criteria. This join is used if the join uses the leftmost prefix field of the index, or if the index is not a primary key or UNIQUE index, that is, if the join cannot select a separate row based on each index value that meets the join criteria.

Fulltext uses FULLTEXT indexes to establish connections

The ref_or_null connection type is similar to ref, except that MySQL scans out additional rows that contain null values. This join method is usually used in situations where there are subqueries.

Index_merge uses the join method of index merge. In this case, the key field contains the index used, and the key_len contains the longest index section that uses the index.

In some cases, unique_subquery will replace eq_ref, such as value IN (SELECT primary_key FROM single_table WHERE some_expr), which uses index query function instead of sub-query to achieve better execution efficiency.

The connection method of index_subquery is similar to unique_subquery. It replaces in subqueries, but it applies to subqueries that are not unique indexes, such as value IN (SELECT key_column FROM single_table WHERE some_expr)

Range scans out a specified range of rows using the index. The key field indicates the index used. Key_len indicates the maximum length of the index. The ref field displays NULL

The index join type of index is the same as ALL, except that the index tree is scanned. This occurs in two cases: first, if the index is an overlay index query and only the index tree is scanned. In this case, the Extra field displays Using index. Second, perform a full table scan through the index order.

When ALL joins the previous table, it scans the whole table every time the two tables are associated.

Indexes available for possible_keys

The index actually selected by key

Index length selected by key_len

Ref displays the field or constant compared to the index, and if the value of this field is func, this value will be used in the result of the function.

The number of rows of the table estimated by rows

Extra additional Information

Mysql > explain select * from buy_log where userid=2

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | buy_log | ref | userid,userid_2 | userid | 4 | const | 1 | NULL |

+-- +

1 row in set (0.00 sec)

(04:14:50) [dmcdbMTNNG] > explain select count (*) from ADDSubscribers where timestamp between 1483351200 and 1483354800

+- -+

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+- -+

| | 1 | SIMPLE | ADDSubscribers | range | Timestamp | Timestamp | 8 | NULL | 3515427 | Using where with pushed condition |

+- -+

1 row in set (0.00 sec)

This information applies only to NDB tables. MySQL Cluster uses this condition to improve efficiency.

Using where with pushed condition (JSON property: message)

This item applies to NDB tables only. It means that MySQL Cluster is using the Condition Pushdown

Optimization to improve the efficiency of a direct comparison between a nonindexed column and a

Constant. In such cases, the condition is "pushed down" to the cluster's data nodes and is evaluated on

All data nodes simultaneously. This eliminates the need to send nonmatching rows over the network, and

Can speed up such queries by a factor of 5 to 10 times over cases where Condition Pushdown could be

But is not used. For more information, see Section 9.2.1.5, "Engine Condition Pushdown Optimization".

This is the end of this article on "how to view the SQL implementation plan in MySQL 5.7". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.