In-depth analysis of MySQL explain usage and results

Preface

In daily work, we sometimes run slow queries to record some SQL statements that take a long time to execute. Finding these SQL statements does not mean that the job is done. Sometimes we often use the explain command to view the execution plan of these SQL statements to see whether the SQL statement uses an index and whether a full table scan is performed. This can be viewed through the explain command. So we have a deep understanding of MySQL's cost-based optimizer, and we can also get a lot of details about the access strategies that may be considered by the optimizer, and which strategy is expected to be adopted by the optimizer when running SQL statements. (QEP: sql generates an execution plan query execution plan)

mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+------+
| 1 | SIMPLE | servers | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
+----+-------------+---------+------+---------------+------+---------+------+------+------+
1 row in set (0.03 sec)

1. Introduction to EXPLAIN

Using the EXPLAIN keyword can simulate the optimizer to execute SQL query statements, so as to know how MySQL processes your SQL statements. Analyze the performance bottleneck of your query statement or table structure. Through explain we can get the following information:

• Table reading order
• Operation type of data read operation
• Which indexes can be used
• Which indexes are actually used?
• References between tables
• How many rows of each table are queried by the optimizer
  

Usage: explain + sql statement. The fields included are as follows

2. Meaning of each field in the execution plan

2.1 id

The id is the same, and the execution order is from top to bottom

The id is different. If it is a subquery, the id number will increase. The larger the id value, the higher the priority and the earlier it will be executed.

If the ids are the same or different and exist at the same time, the same id can be considered as a group. The execution is from top to bottom in the same group, and the group with the largest id will be executed first.

2.4 type

Type shows which type is used in the query. The types included in type include the following:

system > const > eq_ref > ref > range > index > all

• The system table has only one row of records (equal to the system table). This is a special column of the const type and does not appear normally. This can also be ignored.
• const means that the index is found once, and const is used to compare primary keys or unique indexes. Because only one row of data is matched, it is very fast. If you put the primary key in the where list, MySQL can convert the query into a constant.
• eq_ref Unique index scan, for each index key, there is only one record matching it in the table. Commonly used for primary key or unique index scans
• ref Non-unique index scan, returns all rows matching a single value, which is essentially an index access. It returns all rows matching a single value. However, it may find multiple qualifying rows, so it should be considered a mixture of search and scan.
• range retrieves only rows in a given range, using an index to select rows. The key column shows which index is used. This is usually a query that uses between, <, >, in, etc. in your where statement. This range scan index is better than a full table scan because it only needs to start at one point in the index and end at another point without scanning the entire index.
• index Full Index Scan, the difference between Index and All is that the index type only traverses the index tree. This is usually faster than ALL because index files are usually smaller than data files. (That is, although all and Index both read the entire table, index reads from the index, while all reads from the hard disk)
• all Full Table Scan will traverse the entire table to find matching rows
  

2.5 possible_keys and key

possible_keys shows the possible indexes that may be applied to this table, one or more. If an index exists on the fields involved in the query, the index will be listed, but it may not be actually used by the query.

The index actually used by the key. If it is NULL, no index is used. (Possible reasons include no index or index failure)

2.6 key_len

Indicates the number of bytes used in the index. This column can be used to calculate the length of the index used in the query. The shorter the length, the better without losing accuracy.

2.7 rows

Based on table statistics and index selection, roughly estimate the number of rows needed to find the required records, that is, the fewer the better.

2.8 Extra

2.8.1 Using filesort

This means that MySQL will use an external index to sort the data instead of reading it in the order of the indexes in the table. Sorting operations in MySQL that cannot be performed using indexes are called "file sorts".

2.8.2 Using temporary

A temporary table is used to store intermediate results. MySQL uses a temporary table when sorting query results. Commonly used in order by sorting and group by query.

2.8.3 Using index

Indicates that a covering index is used in the corresponding select operation, avoiding access to the table's data rows, which is efficient. If using where appears at the same time, it means that the index is used to perform index key value lookups; if using where does not appear at the same time, it means that the index is used to read data rather than perform lookups.

2.8.4 Using join buffer

Indicates that the connection cache is used. For example, when querying, the number of multi-table joins is very large, so increase the join buffer in the configuration file.

Summarize

This is the end of this article about the in-depth analysis of MySQL explain usage and results. For more relevant MySQL explain usage and results content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!