OYIFAN
中文

Analyzing SQL with EXPLAIN

Database
MySQL

explain Introduction

explain is a command provided by MySQL to analyze SQL statements. Its usage is similar to the following:

explain select * from table;

Explain can analyze whether the entire table is scanned or indexed when executing SQL, and whether the time-consuming query is a subquery or a join table query, etc.

In short, explain is a powerful tool for analyzing SQL performance and is essential for complex SQL optimization.

Detailed explanation

Terminal window
mysql> explain select * from table;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 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)

As shown in the above execution results, explain execution will return a fixed 10 columns, namely id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra:

id: The identifier of the SELECT query. Each SELECT is automatically assigned a unique identifier.

select_type: The type of SELECT query.

table: Which table is being queried?

partitions: matching partitions

type: join type

possible_keys: possible indexes used in this query

key: The exact index used in this query.

ref: which field or constant is used with key

rows: Displays the total number of rows scanned by this query. This is an estimate.

filtered: represents the percentage of data filtered by this query condition

extra: additional information

Several more important fields are recorded in detail below:

select type

select_type represents the type of query. Its common values are:

  1. 1SIMPLE, indicating that this query does not contain UNION queries or subqueries
  2. PRIMARY, indicating that this query is the outermost query
  3. UNION, indicating that this query is the second or subsequent query of UNION
  4. DEPENDENT UNION, the second or subsequent query statement in UNION, depends on the external query
  5. UNION RESULT, UNION result
  6. SUBQUERY, first SELECT in subquery
  7. DEPENDENT SUBQUERY: The first SELECT in the subquery depends on the outer query. That is, the subquery depends on the results of the outer query.

type

type represents the type of index, its values are:

  1. system: There is only one piece of data in the table. This type is a special const type.
  2. const: equivalent query scan for primary key or unique index, only returns one row of data at most
  3. eq_ref: This type usually appears in join queries of multiple tables, which means that each result in the previous table can only match one row of results in the subsequent table. And the comparison operation of the query is usually =, which makes the query more efficient.
  4. ref: This type usually appears in multi-table join queries, targeting non-unique or non-primary key indexes, or queries using the leftmost prefix rule index.
  5. Range: means using index range query to obtain some data records in the table through the index field range. This type usually appears in =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() operations. When type is range, the ref field output by EXPLAIN is NULL, and the key_len field is the longest of the indexes used in this query.
  6. index: represents a full index scan, which is similar to the ALL type, except that the ALL type is a full table scan, while the index type only scans all indexes without scanning data. The index type usually appears when: the data to be queried can be obtained directly in the index tree without scanning the data. When this is the case, the Extra field displays Using index.
  7. ALL: Indicates a full table scan. This type of query is one of the worst performing queries. Generally speaking, our queries should not have ALL type queries, because such queries will be a huge disaster to the performance of the database when the amount of data is large. If a query is an ALL type query, generally speaking, it can be avoided by adding an index to the corresponding field.

Generally speaking, the type performance ranking is as follows: ALL < index < range ~ index_merge < ref < eq_ref < const < system Because the ALL type is a full table scan, it is the slowest under the same query conditions. Although the index type query is not a full table scan, it scans all indexes, so it is slightly faster than the ALL type. The latter types all use indexes to query data, so some or most of the data can be filtered, so the query efficiency is relatively high.

possible_keys

possible_keys represents the indexes that MySQL can use when querying. Note that even if some indexes appear in possible_keys, it does not mean that this index will actually be used by MySQL. Which indexes MySQL uses specifically when querying is determined by the key field.

key

This field is the index actually used by MySQL in the current query.

rows

rows is also an important field. Based on statistical information, the MySQL query optimizer estimates the number of data rows that SQL needs to scan and read to find the result set. This value very intuitively shows the efficiency of SQL. In principle, the fewer rows, the better.

Extra

A lot of additional information in EXplain will be displayed in the Extra field. Common ones include the following:

Using filesort When there is Using filesort in Extra, it means that MySQL requires additional sorting operations and cannot achieve the sorting effect through index order. Generally, it is recommended to optimize and remove Using filesort, because such queries consume a lot of CPU resources.

Using index “Covered index scan” means that the query can find the required data in the index tree without scanning the table data file, which often indicates good performance.

Using temporary The query uses a temporary table, which generally occurs in sorting, grouping, and multi-table join situations. The query efficiency is not high, and optimization is recommended.

Summary

SQL is the most common language. No matter what development you do, I believe it is inseparable from SQL. If you write SQL well, I believe it will be of great help to everyone’s development.