This is the fourth day of my participation in the August Text Challenge.More challenges in August

MySQL provides the explain keyword to simulate the optimizer’s execution of SQL statements, so that we can know how MySQL executes SQL statements, and then analyze the performance bottlenecks of SQL statements to help optimize our SQL statements. Before MySQL5.6, only select is supported. After MySQL5.6, explain supports select, UPDATE, delete statement analysis.

Meaning of each field

id

The sequence number of a SQL query, which can be analyzed to determine the sequence of operations on the table in the SQL statement

  • If the ids are the same, the execution sequence is from top to bottom

The command output is as follows: The SQL statement operates on table E first, then ps, j, D, and N

  • If it is a sub-query, the ID sequence number increases. The larger the ID, the earlier the query is executed

Table T3 is first operated on, then t2 is operated on, and finally T1 is operated on (the greater the ID, the higher the priority, the earlier the execution)

  • If the ids are different and duplicate ids exist, the larger ids are executed first. If the ids with the same id number are executed from top to bottom.

select_type

Indicates the type of the SQL query

  • SIMPLE: A SIMPLE select query, without subqueries or unions
  • PRIMARY: If the query contains any complex subparts, the outermost query is marked as type PARIMARY
  • SUBQUERY: Contains subqueries in select or WHERE
  • DERIVED: Subqueries contained in the FROM list are labeled as DERIVED and MySQL performs these subqueries recursively, putting the results in temporary tables.
  • UNION: If the second select appears after the UNION, it is marked as UNION; If union is included in the subquery of the FROM clause, the outer select is marked as DERIVED
  • UNION RESULT: The select that gets the RESULT from the UNION table (that is, the RESULT of the UNION merging two tables) is marked as UNION RESULT

EXPLAIN SELECT * FROM T1 UNION SELECT * FROM t2

table

The queried table name may not be an actual table name.

  • <unionM,N>: result of referencing ids M and N UNION.

  • < derivedN>: a table derived from a reference to the result with ID N. A derived table can be a result set, such as the result derived FROM a FROM neutron query.

  • < subqueryN>: references the table materialized from the result of a subquery with ID N. That is, a temporary table is generated to hold the results of the subquery.

type

Indicates the query type (12 types). The query efficiency decreases from left to right

  • system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > range > index > ALL

Here are a few common ones:

  • System: the table has only one row of data or an empty table. In the case of an Innodb engine table, the type column in this case is usually all or index.

  • Const: indicates that the query is found by index at once. Because it matches only one row, it can be queried quickly. If you place a primary key = a value after where, MySQL will convert the query to a constant.

EXPLAIN SELECT * FROM T1 WHERE t1.id=1

  • Eq_ref: Unique index scan, for each index key, there is only one record in the table that matches it, such as primary key index or unique index.
  • Ref: non-unique index scan that returns all rows matching a single value.
  • Range: the value retrieves records in a given range, using an index to select records that match. For example, the query between, <, >, in, etc. is used in the WHERE statement. Such asEXPLAIN SELECT * FROM t1 WHERE id>2 (id primary key index, if not index, will cause full table scan)
  • Index: scans ALL indexes. The difference between index and ALL is that the index type only traverses the index tree. Although ALL and INDEX both read the entire table, index is read from the index, while ALL is read from the disk.
  • “ALL” : records matching ALL tables are found.

possible_keys

Displays the names of indexes that may be used by the SQL query

key

Displays the name of the index actually used by the SQL query

key_len

Represents the length of the index used in the SQL query (in bytes)

  • The value key_len represents is the maximum possible length of the index field, not the actual length. Key_len is derived from the definition of the table field, not from the table retrieval.

ref

Shows which column of the index is used

  • Const if constant equivalence is used, as inEXPLAIN SELECT * FROM t1 WHERE id=2. It can also indicate which columns are used to find the value on the index column, as inEXPLAIN SELECT * FROM USER WHERE uname IN (SELECT uname FROM USER WHERE uid>3 ), the following execution result in the ref fielddemo.user.unameIndicates that the column of the UName field is used to find the value on the index column.

rows

Estimated number of rows to scan

  • Rows estimates the number of rows that need to be scanned to find the target record based on table statistics and index selection.

filtered

Query hit ratio

  • Represents the proportion of the data returned by the storage engine that is filtered at the Server layer to meet the number of records to be queried.

extra

Represents important additional information that is displayed in other columns

  • Using filesort: indicates that the MySQL database requires additional sorting operations. The sorting effect cannot be achieved by index order. A sort operation in MySQL that cannot be done with an index is calledFile sorting. Such asEXPLAIN SELECT * FROM t1 ORDER BY age(Age field is not indexed)

  • Using temporary: A temporary table is used to store intermediate results. MySQL uses temporary tables when sorting query results.

  • Using index: overwrite index scanning (overwrite index means that the fields to be queried in SQL are contained in the fields of the current index). It means that the required data can be found in the index tree without scanning the data files of the table (no need to return to the table because the queried fields are on the leaf nodes of the current index), which is efficient. Select * from t1 where id = 1;

  • Using join buffer: The join buffer is used

  • Impossible WHERE: The value of the where clause is always false and cannot be used to retrieve any tuples.

  • Select Tables Optimized Away: Optimize MIN/MAX operations based on indexes without the Group by clause. EXPLAIN SELECT MAX(id) FROM t1

  • Distinct: Optimizes the DISTINCT operation to stop looking for equivalent actions after the first matching record is found.

🏁 the above is a detailed explanation of explain, if there are any mistakes, please leave a comment, if you think this article is helpful to you then like 😻😍