MySQL EXPLAIN command details

The MySQL EXPLAIN command explains how to use the MySQL EXPLAIN command to query the Execution Plan (QEP) of SQL statements ). The output results of this command show us how the MySQL optimizer executes SQL statements. This command does not provide any adjustment suggestions, but it can provide important information to help you make optimization decisions. 1 syntax MySQL's EXPLAIN syntax can run in a SELECT statement or a specific table. If it is used in a table, this command is equivalent to the DESC table command. The UPDATE and DELETE commands also require performance improvement. When these commands are not run directly on the table's primary code, to ensure optimal index usage, you need to rewrite them into SELECT statements (to execute the EXPLAIN command on them ). See the following example: UPDATE table1SET col1 = X, col2 = YWHERE id1 = 9AND dt> = '2017-01-01 '; this UPDATE statement can be rewritten into the following SELECT statement: SELECT col1, col2FROM table1WHERE id1 = 9AND dt> = '2017-01-01 '; in version 5.6.10, you can directly explain dml statements. the MySQL optimizer works based on overhead and does not provide any QEP location. This means that QEP is calculated dynamically when each SQL statement is executed. The SQL statement in the MySQL Stored Procedure calculates QEP each time it is executed. The stored procedure cache only parses the query tree. 2. 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 this QEP shows that no index is used (that is, full table scan) A large number of rows are processed to meet the query requirements. For the same SELECT statement, an optimized QEP is as follows: ************************ 1. row ********************** id: 1 select_type: SIMPLE table: inventory type: ref possible_keys: item_id key: item_id key_len: 4 ref: const rows: 1 Extra: In this QEP, we can see that an index is used and it is estimated that only one row of data will be obtained. The following lists all rows in QEP: partition id partition select_type Partition table partition partitions (this column only appears in the explain partitions syntax)  possible_keys partition key partition key_len partition ref rows partition filtered (this column only appears in the explained extended syntax)  Extra these columns demonstrate the QEP of the SELECT statement for each table. A table may be associated with a physical mode table or an internal temporary table generated during SQL Execution (for example, an internal temporary table is generated during subquery or merge operations. 2.1 key column indicates the index selected by the optimizer. Generally, only one index is used for each table in the SQL query. There are also a few exceptions of index merge. For example, two or more indexes are used for a given table. The following is an example of the key column in QEP: key: item_id key: NULL key: first, last show create table <table> command is the simplest way to view details of tables and index columns. Columns related to the key column also include possible_keys, rows, and key_len. 2.2 The ROWS rows column provides the estimated values of the MySQL optimizer that attempts to analyze the number of ROWS in the cumulative result set. QEP can easily describe this difficult statistic. The total number of read operations in the query is obtained based on the continuous accumulation of the rows value of each row in the row before the merge. This is a nested row algorithm. The following uses QEP as an example to connect two tables. The rows value of the first row found by the condition id = 1 is 1, which is equal to a read operation on the first table. The second row is found by id = 2, and the rows value is 5. This is equivalent to the accumulation of the current 1 for five read operations. Refer to the two tables. The total number of read operations is 6. In another QEP, the first rows value is 5, and the second rows value is 1. This means that the first table has five read operations, each of which has one read operation on the five accumulated volumes. Therefore, the total number of read operations for two tables is 10 (5 + 5. The best estimate is 1. Generally, this happens when the row to be searched can be found through the primary key or unique key in the table. In the following QEP, the nested loop can be found by id = 1. The estimated number of physical rows is 1. The second loop processes 10 rows. * ********************* 1. row ********************** id: 1 select_type: SIMPLE table: p type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: ********************** 2. row ********************** id: 1 select_type: SIMPLE table: c type: ref possible_keys: parent_id key: parent_id key_len: 4 ref: const rows: 10 Extra: You can use the show status command to view the actual row operations. This command provides the best way to confirm physical row operations. See the following example: mysql> show session status like 'handler _ read % '; + metric + ------- + | Variable_name | Value | + metric + ------- + | 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) in the next In QEP, the nested outer loop found through id = 1 is estimated to have 160 rows. The second cycle has one 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 you can use the show status command to view the actual operation The command indicates that the number of physical read operations has increased significantly. See the following example: mysql> show session status like 'handler _ read % '; + metric + --------- + | Variable_name | Value | + metric + --------- + | Handler_read_first | 1 | Handler_read_key | 164 | Handler_read_last | 0 | Handler_read_next | 107 | percent | 0 | | Handler_read_rnd | 0 | Handler_read_rnd_next | 161 | + ----------------------- --------------- + --------- + The related QEP columns also include key columns. 2.3 The possible_keys column indicates that the optimizer is the index selected for the query. A qep that lists a large number of possible indexes (for example, more than three) means that there are too many alternative indexes, and an invalid Single Column index may be prompted. You can use the show indexes command described in Chapter 2nd to check whether the index is valid and has an appropriate base. Determining the QEP speed for queries also affects query performance. If a large number of possible indexes are found, it means these indexes are not used. Related QEP columns also include key columns. The 2.4 key_len column defines the length of the key used for the SQL statement connection condition. The value of this column is important for verifying the validity of the index and the number of columns used in the Multi-column index. Some example values of this column are as follows: 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 as shown in these examples, whether it can be null, variable-length columns, and the value of the key_len column are only related to the columns used in the join and WHERE conditions. Other columns in the index will be used in the order by or group by statement. The following table from the famous open-source blog software WordPress shows how to use SQL statements with a defined table index in the best way: create table 'wp _ posts' ('id' bigint (20) unsigned not null AUTO_INCREMENT, 'Post _ date' datetime not null default '2017-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 CHAR SET = utf8 create table 'wp _ posts' ('id' bigint (20) unsigned not null AUTO_INCREMENT, 'Post _ date' datetime not null default '2017-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') the default charset = utf8 table indexes include post_type, post_status, and post_date. And the ID column. The following is an SQL query that demonstrates index column usage: EXPLAIN SELECT ID, post_title FROM wp_posts WHERE post_type = 'post' AND post_date> '2017-06-01 '; the key_len returned by the QEP query is 62. This indicates that only the index on the post_type column is used (because (20 × 3) + 2 = 62 ). Although the columns post_type and post_date are used in the WHERE statement, only the post_type column is used. Other indexes are not used because MySQL can only use the leftmost part of the defined index. To make better use of this index, you can modify this query to adjust the index column. See the following example: mysql> explain select id, post_title-> FROM wp_posts-> WHERE post_type = 'post'-> AND post_status = 'Publish '-> AND post_date> '2017-06-01 '; after a restriction condition is added to the post_status column in the SELECT query, QEP displays the value of key_len as 132, which means the three columns post_type, post_status, and post_date (62 + 62 + 8, (20 × 3) + 2, (20 × 3) +) are all used. In addition, the ID of the primary code column of this index is defined as the legacy trace of storing the index with MyISAM. When using the InnoDB Storage engine, it is redundant to include the primary code column in a non-primary code index, which can be seen from the key_len usage. Related QEP columns also include Extra columns with Using index values. 2.5 The table column is the unique identifier of a separate row in the EXPLAIN command output result. This value may be the table name, table alias, or an identifier that generates a temporary table for the query, such as a derived table, subquery, or set. Table: item table: <derivedN> table: <unionN, m> the values of N and M in the table refer to another table row that matches the values of the id column. The related QEP column also has the select_type 2.6 select_type column which provides various usage types that indicate table column references. The most common values include SIMPLE, PRIMARY, DERIVED, and UNION. Other possible values include union result, dependent subquery, dependent union, uncacheable union, and uncacheable query. 1. SIMPLE is a common type for SIMPLE queries that do not contain subqueries and other complex syntaxes. 2. PRIMARY: This is the first table (the outermost table) created for more complex queries ). This type can be seen when the DERIVED and UNION types are mixed. 3. DERIVED when a table is not a physical table, it is called DERIVED. The following SQL statement provides an example of the DERIVED select-type in QEP: mysql> EXPLAIN SELECT MAX (id)-> FROM (SELECT id FROM users WHERE first = 'west ') c; 4. the select-type value of dependent subquery is defined for 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 is an SQL element in a UNION statement. 6. union result: This is the returned RESULT of a series of tables defined in the UNION statement. When select_type is set to this value, we often see that the value of table is <unionN, M>. This indicates that the matched 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 column indicates the partition used by the given table. This column will only appear in the explain partitions statement. The 2.8 Extra column provides a series of additional information about different types of MySQL optimizer paths. The Extra column can contain multiple values, which can have many different values, and these values are further increased with the release of the new MySQL version. The list of common values is given below. You can find a more comprehensive list of values from the following addresses: http://dev.mysql.com/doc/refman/5.5/en/explain-output.html . 1. The value of Using where indicates that the query uses the where statement to process the results-for example, to perform a full table scan. If indexes are also used, the row restrictions are achieved by obtaining required data and processing the read buffer. 2. Using temporary indicates that an internal temporary (memory-based) Table is used. Multiple temporary tables may be used for one query. MySQL creates a temporary table during query execution for many reasons. Two common causes are DISTINCT or different order by and group by columns on columns from different tables. You can force a temporary table to use the disk-based MyISAM storage reference. There are two main reasons for doing so: the space occupied by temporary tables in tables exceeds min (tmp_table_size, max _ heap_table_size). The limit of system variables in tables uses TEXT/BLOB column 3. using filesort: this is the result of the order by statement. This may be a CPU-intensive process. You can select an appropriate index to improve performance and use indexes to sort query results. For detailed procedures, see Chapter 4th. 4. The value of Using index emphasizes that you only need to use indexes to query tables and do not need to directly access table data. Refer to the detailed example in Chapter 5th to understand this value. 5. The value of Using join buffer emphasizes that indexes are not used when the connection conditions are obtained, and a connection buffer is required to store intermediate results. If this value is displayed, you should note that you may need to add indexes to Improve the Performance Based on the specific query conditions. 6. The value of Impossible where emphasizes that the where statement will cause no rows meeting the conditions. See the following example: mysql> explain select * FROM user WHERE 1 = 2; 7. the Select tables optimized away value means that only the index is used, and the Optimizer may return only one row from the aggregate function results. See the following example: 8. The value of Distinct means that MySQL will stop searching for other rows after finding the first matched 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 indexes used and the merged types. Using sort_union (...) Using union (...) Using intersect (...) 2.9 the id column is a continuous reference of the table displayed in QEP. 2.10 The ref column can be used to identify the columns or constants used for index comparison. 2.11 The filtered column provides a percentage value, which is used together with the value of the rows column, we can estimate the number of rows that will be connected to the previous table in QEP. The first table is a table with a smaller value than the current table id. This column appears only in the explain extended statement. The 2.12 type column represents the connection method used by the table specified in QEP. The following are the most common Connection Methods: Using const when this table has only one row of matched rows, system is a special case of const, when there is only one row in the table, the value of  eq_ref indicates that one row is read for each previously determined table. This value indicates that all rows with matched index values are using the distinct range value indicates that ALL index rows that match a given range value are used with the distinct ALL value. This indicates that a full table scan is required for other types of values, including fulltext, ref_or_null, index_merge, and unique_subquery., index_subquery, and index. 3. EXPLAIN the EXPLAIN output to understand your application (including technical and implementation possibilities) and optimize SQL statements. The following is an example of how to obtain an isolated parent-child record from a parent-child relationship. This query can be constructed in three different ways. Although the same results are generated, QEP displays three different paths. Mysql> explain select p. *-> FROM parent p-> WHERE p. id not in (SELECT c. parent_id FROM child c) \ G *********************** 1. row ********************** id: 1 select type: PRIMARY table: p type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 160 Extra: Using where *********************** 2. row ********************** id: 2 select_type: dependent subquery table: c type: index_subquery possible_keys: parent_id key: parent_id key_len: 4 ref: func rows: 1 Extra: Using index 2 rows in set (0.00 sec) explain select p. * FROM parent p left join child c ON p. id = c. parent_id WHERE c. child_id is null \ G *********************** 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: parent_id key: parent_id key_len: 4 ref: test. p. id rows: 1 Extra: Using where; Using index; Not exists 2 rows in set (0.00 sec)