The EXPLAIN command of MySQL is used for the query execution plan (QEP) of the SQL statement. The output of this command allows us to understand how the MySQL optimizer executes SQL statements. This command does not provide any tuning suggestions, but it can provide important information to help you make tuning decisions.
1 Syntax MySQL's EXPLAIN syntax can be run on a SELECT statement or a specific table. If acting on a table, this command is equivalent to the DESC table command. UPDATE and DELETE commands also need performance improvements. When these commands are not run directly on the main key of the table, in order to ensure optimal index usage, they need to be written into SELECT statements (so that they Execute EXPLAIN command). Please see the following example:
UPDATE table1
SET col1 = X, col2 = Y
WHERE id1 = 9
AND dt >= '2010-01-01';
Copy after login
##This UPDATE statement can be rewritten into a SELECT statement like the following:
SELECT col1, col2
FROM table1
WHERE id1 = 9
AND dt >= '2010-01-01';
Copy after login
In version 5.6.10, you can directly perform explain analysis operations on dml statements.MySQL The optimizer works based on cost and does not provide any QEP position. This means that QEP is calculated dynamically as each SQL statement is executed. SQL statements in MySQL stored procedures also calculate QEP each time they are executed. The stored procedure cache only parses the query tree.
2 Detailed explanation of each column
The MySQL EXPLAIN command can generate the following information for each table in the SQL statement:
mysql> EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176\G;
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table: inventory
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 787338
Extra: Using where
Copy after login
This QEP shows that no index is used (that is, a full table scan) and a large number of rows are processed to satisfy the query. For the same SELECT statement, an optimized QEP is as follows:
In this QEP, we see that an index is used, and it is estimated that only one row of data will be obtained.
All lists for each row in the QEP look like this:
id select_type table partitions(This column will only appear in the EXPLAIN PARTITIONS syntax) possible_keys key key_len ref rows filtered(This column will only Appears only in EXPLAINED EXTENDED syntax) Extra
These columns show the QEP of the SELECT statement for each table. A table may be associated with a physical schema table or an internal temporary table generated during SQL execution (for example, from a subquery or a merge operation).
You can refer to the MySQL Reference Manual for more information: //m.sbmmt.com/.
2.1 key The key column indicates the index chosen by the optimizer. Generally speaking, only one index is used per table in a SQL query. There are a few exceptions to index merging, such as when two or more indexes are used on a given table. The following is an example of the key column in QEP: key: item_id key: NULL key: first, last The SHOW CREATE TABLE
command is the simplest to view the table and How to index column details. Columns related to the key column also include possible_keys, rows and key_len.
2.2 ROWS
The rows column provides an estimate of the number of rows the MySQL optimizer attempted to analyze all of those that existed in the cumulative result set. QEP makes it easy to describe this difficult statistic. The total number of read operations in the query is based on the continuous accumulation of the rows value of each row before merging the rows. This is a nested row algorithm.
Take QEP connecting two tables as an example. The rows value of the first row found through the condition id=1 is 1, which is equivalent to a read operation on the first table. The second row is found through id=2, and the value of rows is 5. This equates to 5 reads matching the current accumulation of 1. Referring to both tables, the total number of read operations is 6. In another QEP , the value of the first rows is 5 and the value of the second rows is 1. This equates to 5 reads for the first table, one for each of the 5 accumulations. Therefore, the total number of read operations for the two tables is 10 (5+5) times.
The best estimate is 1. Generally this happens when the row you are looking for can be found in the table by the primary key or unique key. In the QEP below, the outer nested loop can be found by id=1, and its estimated physical row number is 1. The second loop processed 10 rows.
You can use the SHOW STATUS command to view the actual row operations. This command provides the best way to confirm physical row operations. Please see the following example:
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 11 |
+-----------------------+-------+
7 rows in set (0.00 sec)
Copy after login
In the next QEP, the outer nested loop found with id=1 is estimated to have 160 lines. The second loop is estimated to be 1 row.
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table: p
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 160
Extra:
********************* 2. row ***********************
id: 1
select type: SIMPLE
table: c
type: ref
possible_keys: PRIMARY,parent_id
key: parent_id
key_len: 4
ref: test.p.parent_id
rows: 1
Extra: Using where
Copy after login
You can view the actual row operations through the SHOW STATUS command, which shows that the number of physical read operations has increased significantly. Please see the example below:
此列的一些示例值如下所示： key_len: 4 // INT NOT NULL key_len: 5 // INT NULL key_len: 30 // CHAR(30) NOT NULL key_len: 32 // VARCHAR(30) NOT NULL key_len: 92 // VARCHAR(30) NULL CHARSET=utf8
从这些示例中可以看出，是否可以为空、可变长度的列以及key_len 列的值只和用在连接和WHERE 条件中的索引的列 有关。索引中的其他列会在ORDER BY 或者GROUP BY 语句中被用到。下面这个来自于著名的开源博客软件WordPress 的表展示了 如何以最佳方式使用带有定义好的表索引的SQL 语句：
CREATE TABLE `wp_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_status` varchar(20) NOT NULL DEFAULT 'publish' ,
`post_type` varchar(20) NOT NULL DEFAULT 'post',
PRIMARY KEY (`ID`),
KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)
) DEFAULT CHARSET=utf8
CREATE TABLE `wp_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_status` varchar(20) NOT NULL DEFAULT 'publish' ,
`post_type` varchar(20) NOT NULL DEFAULT 'post',
PRIMARY KEY (`ID`),
KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)
) DEFAULT CHARSET=utf8
Copy after login
这个表的索引包括post_type、post_status、post_date 以及ID列。下面是一个演示索引列用法的SQL 查询： EXPLAIN SELECT ID, post_title FROM wp_posts WHERE post_type='post' AND post_date > '2010-06-01';
mysql> EXPLAIN SELECT ID, post_title
-> FROM wp_posts
-> WHERE post_type='post'
-> AND post_status='publish'
-> AND post_date > '2010-06-01';
3. DERIVED 当一个表不是一个物理表时，那么就被叫做DERIVED。下面的SQL 语句给出了一个QEP 中DERIVED select-type 类型的 示例： mysql> EXPLAIN SELECT MAX(id) -> FROM (SELECT id FROM users WHERE first = 'west') c;
4. DEPENDENT SUBQUERY This select-type value is defined for using subqueries. The following SQL statement provides this value: mysql> EXPLAIN SELECT p.* -> FROM parent p -> WHERE p.id NOT IN (SELECT c.parent_id FROM child c);
5. UNION This is one of the SQL elements in the UNION statement. 6. UNION RESULT This is the return result of a series of tables defined in the UNION statement. When select_type is this value, you can often see that the value of table is , This means that the matching id row is part of this set. The following SQL generates a UNION and UNION RESULT select-type: mysql> EXPLAIN SELECT p.* FROM parent p WHERE p.val LIKE 'a%' -> UNION - > SELECT p.* FROM parent p WHERE p.id > 5;
2.7 partitions The partitions column represents the partitions used by the given table. This column will only appear in the EXPLAIN PARTITIONS statement.
2.8 Extra The Extra column provides a series of additional information about different kinds of MySQL optimizer paths. Extra columns can contain multiple values and can have many different values, and these values are still increasing with the release of new versions of MySQL. Below is a list of commonly used values . You can find a more comprehensive list of values at: //m.sbmmt.com/.
1. Using where This value indicates that the query uses the where statement to process the results - for example, perform a full table scan. If indexes are also used, row constraints are achieved by obtaining the necessary data and then processing the read buffer.
2. Using temporary This value indicates the use of an internal temporary (memory-based) table. A query may use multiple temporary tables. There are many reasons why MySQL creates temporary tables during query execution. Two common reasons are using DISTINCT on columns from different tables, or using different ORDER BY and GROUP BY columns. For more information, please visit //m.sbmmt.com/ of_query_execution_and_use_of_temp_tables. You can force a temporary table to use the disk-based MyISAM storage engine . There are two main reasons for this: The space occupied by the internal temporary table exceeds the limit of the min (tmp_table_size, max_ heap_table_size) system variable TEXT/BLOB columns are used
3. Using filesort This is the result of the ORDER BY statement. This can be a CPU-intensive process. You can improve performance by choosing an appropriate index and use the index to sort query results. Please refer to Chapter 4 for detailed procedures.
4. Using index This value emphasizes that only the index can be used to meet the requirements of the query table, and there is no need to directly access the table data. Please refer to Chapter 5 for detailed examples to understand these values.
5. Using join buffer This value emphasizes that no index is used when obtaining the join condition, and a join buffer is needed to store intermediate results. If this value appears, you should note that depending on the specific conditions of the query, you may need to add an index to improve performance.
6. Impossible where This value emphasizes that the where statement will result in no rows that meet the conditions. Please see the following example: mysql> EXPLAIN SELECT * FROM user WHERE 1=2;
7. Select tables optimized away This value means that only by using the index , the optimizer may return only one row from the aggregate function result. See the following example:
8. Distinct This value means that MySQL will stop searching for other rows after finding the first matching row.
9. Index merges When MySQL decides to use more than one index on a given table, one of the following formats will appear, detailing the use Index and merge types. Using sort_union(...) Using union(...) Using intersect(...)
2.9 id The id column is a continuous reference to the table displayed in QEP.
2.10 ref The ref column can be used to identify columns or constants used for index comparisons.
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
