1, what is the MySQL execution plan to have a better understanding of the implementation plan, you need to first have a simple understanding of MySQL infrastructure and query fundamentals. MySQL's own functional architecture is divided into three parts, namely the application layer, the logical layer, the physical layer, not just MySQL, most of the other database products are divided according to this architecture. The application layer is primarily responsible for interacting with clients, establishing links, remembering link states, returning data, and responding to requests, a layer that deals with clients. Logical layer, mainly responsible for query processing, transaction management and other database function processing, to query as an example. Once the query SQL is received, the database is immediately assigned a thread to process it, and the first step of the query processor optimizes the SQL query, which generates an execution plan and is then handed to the plan executor for execution. The plan executor needs to access the lower level transaction manager, the storage Manager to manipulate the data, their respective division of labor is different, ultimately by calling the physical layer of the file to obtain the query structure information, the final result response to the application layer. Physical layer, the actual physical disk stored on the file, mainly have a penny data file, log file. By the above description, generating an execution plan is an essential step in executing an SQL, and a SQL performance can be seen intuitively by looking at the execution plan, which provides various types and levels of queries, as well as the basis for performance analysis. 2. How to analyze the execution plan MySQL provides us with the Explain keyword to visually view a SQL execution plan. Explain shows how MySQL uses indexes to process SELECT statements and join tables to help select better indexes and write more optimized query statements. Below 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) query structure with 12 columns, understanding the meaning of each column is critical to understanding the execution plan, which is described in a tabular format. The column name describes the idselect identifier, which is the query sequence number for SELECT. The Select_typeselect type can be any of the following: simple select (Do not use union or subquery) PRIMARY: the outermost selectunion: The second or subsequent SELECT statement in the Union dependent the second or subsequent SELECT statement in Union:union, depending on the result of the outer query UNION result:union subquery: The first selectdependent subquery in the subquery: the first select in the subquery, depending on the outer query derived: Export table's SELECT (subquery FROM clause) Table partitions the rows that the table outputs refer to if the query is based on a partitioned table, displays the partitions that the query will access. Type join. Here are the various join types, sorted by best type to worst Type: system: Table has only one row (= Systems table). This is a special case of the const join type. Const: The table has a maximum of one matching row, which will be read at the beginning of the query. Because there is only one row, the column values in this row can be considered constants by the remainder of the optimizer. The const table is quick because they read only once!eq_ref: A row is read from the table for each row combination from the preceding table. This may be the best type of join, except for the const type. Ref: For each row combination from the preceding table, all rows with matching index values are read from this table. Ref_or_null: The join type is like ref, but adding MySQL can specifically search for rows that contain null values. Index_merge: The join type represents the use of the index merge optimization method. Unique_subquery: This type replacesThe Ref:value in sub-query of the following form (SELECT Primary_key from single_table WHERE some_expr) unique_subquery is an index lookup function that completely replaces the subquery, Higher efficiency. Index_subquery: The join type is similar to Unique_subquery. You can replace the in subquery, but only for non-unique indexes in the following form of subqueries: value in (SELECT key_column from single_table WHERE some_expr) Range: Retrieves only the rows for a given range. Use an index to select rows. Index: The join type is the same as all except that only the index tree is scanned. This is usually faster than all, because the index file is usually smaller than the data file. All: For each row combination from the previous table, a complete table scan indicates that the query needs to be optimized. In general, it is best to ensure that the query reaches at least the range level, preferably ref. Possible_keys points out which index MySQL can use to find rows in the table key shows the keys (indexes) that MySQL actually decides to use. If no index is selected, the key is null. Key_len shows the key lengths that MySQL decides to use. If the key is null, the length is null. With no loss of accuracy, the shorter the length, the better ref shows which column or constant is used together with key to select rows from the table. Rows shows the number of rows that MySQL must check when it executes the query. Data multiplication between multirow can estimate the number of rows to be processed. Filtered shows the percentage estimate of the number of rows filtered through the condition. Extra The column contains the details of the MySQL resolution query Distinct:mysql finds a 1th matching row, stops searching for more rows for the current row combination. Select tables optimized away MySQL simply does not traverse the table or index to return data, indicating that it has been optimized to no longer optimize the not exists:mysql to be able to optimize the left join of the query, found 1 matches left After the join standard row, no more rows are checked in the 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 were known, some indexes might be available. The Using Filesort:mysql requires an additional pass to find out how rows are retrieved in sorted order, indicating that the query needs to be optimized. Using Index: Retrieves column information from a table by using only the information in the index tree without requiring a further search to read the actual rows. Using Temporary: In order to resolve the query, MySQL needs to create a temporary table to accommodate 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 send to the customer. Using Sort_union (...), using Union (...), using intersect (...): These functions describe how to merge index scans for Index_merge join types. 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 the columns of a group by or distinct query. Instead of additional search hard disk access the actual table. Based on the above table, you can provide good assistance in the execution plan analysis.
Using explain in MySQL to query the execution plan for SQL
