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

This article mainly introduces how to use explain query SQL execution plan in MySql, the article is very detailed, has certain reference value, interested friends must read it!

The explain command is the primary way to see how the query optimizer decides to execute a query.

This feature has limitations and doesn't always tell the truth, but its output is the best information available, and it's worth taking the time to understand because you can learn how queries are executed.

1 What is MySQL Execution Plan?

To have a good understanding of execution planning, you need to have a simple understanding of MySQL infrastructure and query fundamentals.

MySQL's functional architecture is divided into three parts, namely the application layer, logical layer, physical layer, not just MySQL, most other database products are divided according to this architecture.

The application layer is responsible for interacting with clients, establishing links, remembering link states, returning data, and responding to requests. This layer deals with clients.

Logic layer, mainly responsible for query processing, transaction management and other database function processing, query for example.

After receiving the query SQL, the database will immediately allocate a thread to process it. The query processor will optimize the SQL query in the first step. After optimization, it will generate an execution plan, which will be executed by the plan executor.

The plan executor needs to access the transaction manager at the lower level, and the storage manager operates the data. Their respective division of labor is different. Finally, the query structure information is obtained by calling the file of the physical layer, and the final result is responded to the application layer.

The physical layer refers to the files stored on the actual physical disk, mainly including data files and log files.

Through the above description, generating an execution plan is an essential step in executing a SQL. The quality of a SQL performance can be visually seen by viewing the execution plan. The execution plan provides various query types and levels. We view them as the basis for performance analysis.

2. How to analyze the implementation plan

MySQL provides us with the explain keyword to visually view an SQL execution plan.

explain shows how MySQL uses indexes to process select statements and join tables, helping to select better indexes and write more optimized query statements.

Here we use explain to make a query, as follows:

mysql> explain select * from payment;+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+| 1 | SIMPLE | payment | NULL | ALL | NULL | NULL | NULL | NULL | 16086 | 100.00 | NULL |+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+1 row in set, 1 warning (0.01 sec)

There are 12 columns in the query structure. Understanding the meaning of each column is crucial to understanding the implementation plan. The following table is used to explain it.

The column name describes the idSELECT identifier, which is the query sequence number for SELECT. select_type

SELECT type, which can be any of the following:

SIMPLE: Simple SELECT(no UNION or subquery)

PRIMARY: The outermost SELECT

UNION: Second or subsequent SELECT statement in UNION

DEPENDENT UNION: The second or subsequent SELECT statement in UNION, depending on the external query

UNION RESULT: Results of UNION

SUBQUERY: First SELECT in a subquery

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

DERIVED: SELECT of exported tables (subquery of FROM clause)

Table partitions referenced by rows of table output Displays partitions that the query will access if the query is based on partitioned tables. type

Join type. Here are the various join types, sorted from best to worst:

system: table has only one row (= system table). This is a special case of the const join type.

const: The table has at most one matching row, which will be read at the beginning of the query. Because there is only one row, the column values in that row are considered constant by the rest of the optimizer. const tables are fast because they are read only once!

eq_ref: For each row combination from the previous table, read one row from that table. This is probably the best join type, except for const.

ref: For each row combination from the previous table, all rows with matching index values are read from that table.

ref_or_null: This join type is the same as ref, but MySQL is added to specifically search for rows containing NULL values.

index_merge: This join type indicates that the index merge optimization method is used.

unique_subquery: This type replaces the ref of an IN subquery of the following form: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery is an index lookup function that replaces subqueries completely, making it more efficient.

index_subquery: This join type is similar to unique_subquery. You can replace IN subqueries, but only for non-unique indexes in subqueries of the form: value IN (SELECT key_column FROM single_table WHERE some_expr)

range: Retrieves only a given range of rows, using an index to select rows.

index: This join type is the same as ALL, except that only the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files.

ALL: Perform a full table scan for each row combination from the previous table, indicating that the query needs to be optimized.

In general, you need to ensure that queries reach at least range level, preferably ref level.

possible_keys indicates which index MySQL can use to find rows in the table key shows which key MySQL actually decided to use. If no index is selected, the key is NULL. key_len Displays the key length MySQL decides to use. If the key is NULL, the length is NULL. The shorter the length, the better, without loss of precision. ref shows which column or constant is used with key to select rows from the table. rows shows the number of rows MySQL thinks it must check when executing a query. Multiplication of data between rows estimates the number of rows to process. filtered shows an estimate of the percentage of rows filtered by the condition. Extra

This column contains the details of MySQL's resolution queries

Distinct:MySQL stops searching for more rows for the current row combination after finding the first row match.

Select tables optimized away MySQL returns data without traversing tables or indexes at all, indicating that it has been optimized to the point where it cannot be optimized any more.

Not exists:MySQL is able to perform LEFT JOIN optimization on queries, finding 1 row matching the LEFT JOIN criteria and not checking any more rows in that table for the previous row combination.

range checked for each record (index map: #):MySQL did not find a good index to use, but found that if the column values from the previous table are known, some indexes may be usable.

Using filesort:MySQL requires an extra pass to figure out how to retrieve rows in sort order, indicating that the query needs to be optimized.

Using index: Retrieves column information from a table by reading the actual rows using only the information in the index tree without further searching.

Using temporary: In order to solve the query,MySQL needs to create a temporary table to hold the results, indicating that the query needs to be optimized.

The Using where:WHERE clause is used to restrict which row matches the next table or is sent to the customer.

Using sort_union(...), Using union(...), Using intersect(...): These functions show how to merge index scans for the index_merge join type.

Using index for group-by: Similar to Using index for accessing tables,Using index for group-by means that MySQL has found an index that can be used to query all columns of a GROUP BY or DISTINCT query without additional searching of the hard disk to access the actual table.

The above is "MySql how to use explain query SQL execution plan" all the contents of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

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.