SoFunction
Updated on 2025-03-03

Interpretation of MYSQL EXPLAIN results

Interpretation of MYSQL EXPLAIN results

  • EXPLAIN will not tell you about triggers, stored procedures, or user-defined functions that affect queries.
  • EXPLAIN does not consider various caches (caches).
  • EXPLAIN cannot display the optimization work MySQL does when executing queries. Some statistics are estimated, not exact values.
  • EXPALIN can only explain SELECT operations. Other operations must be rewritten to SELECT and then view the execution plan.

1 id

The identifier of select, which is the query sequence number of select.

  • If two columns of data ids are the same, it is the same set of queries, executed from top to bottom.
  • If the id value is different, the larger the id value, the higher the priority.

2 select_type

Type of select

  • SIMPLE(simple): Simple SELECT (no UNION or subquery).
  • PRIMARY(primary): The outermost query in a subquery. If the query contains any complex subparts, the outermost select is marked as PRIMARY.
  • UNION(union): The second or subsequent SELECT statement in UNION.
  • DEPENDENTUNION (dependent union): The second or subsequent SELECT statement in UNION depends on the query outside.
  • UNION RESULT(union result): The result of UNION, the second select in the union statement starts all the selects after it.
  • SUBQUERY(subquery): The first SELECT in the subquery, the result does not depend on the external query.
  • DEPENDENT SUBQUERY(dependent subquery): The first SELECT in the subquery, dependent on the external query.
  • DERIVED(derived): SELECT of the derived table (subquery of the FROM clause).
  • UNCACHEABLE SUBQUERY(uncacheable subquery): (The result of a subquery cannot be cached, the first line of the outer link must be reevaluated)

3 table

  • The table name of the output result set.

4 partitions

  • The partition where the table of the output result set is located

5 TYPE

  • The type shows the access type, which is a more important indicator. The result values ​​are from good to bad:

Null > system > const > eq_ref > ref > ref_or_null> index_merge > unique_subquery > index_subquery > range > index > ALL

Generally speaking, it is necessary to ensure that the query reaches at least the range level, and it is best to reach ref.

When we conduct conditional query, it is recommended to use indexes, otherwise it will cause full table scanning, and the overhead of IO and the performance of the program cannot be guaranteed!

  • NULL: MySQL decomposes statements during optimization, and does not even need to access tables or indexes when executing. For example, selecting the minimum value from an index column can be completed through a separate index search.
  • system: There is only one row in the table (=system table). This is a special case of the const join type. These types ( system/const ) access is used when MySQL optimizes a part of a query and converts it to a constant. If the primary key is placed in the where list, MySQL can convert the query into a constant. When there is only one row in the query table, use system.
  • const: The table has at most one matching row, which will be read at the beginning of the query. Since there is only one row, the column values ​​in this row can be considered constant by the rest of the optimizer. The const tables are fast because they are read only once!
  • eq_ref: Similar to ref, the difference is that the index used is the unique index. For each index key value, there is only one record in the table matching. Simply put, it is to use primary key or unique key as the association conditions in multi-table connection. This is probably the best join type except for the const type.
  • ref: Indicates the join matching conditions of the above table, that is, which columns or constants are used to find the values ​​on the index column.
  • ref_or_null: This join type is like ref, but with MySQL added, you can search specifically for rows containing NULL values.
  • index_merge: This join type indicates that the index merge optimization method is used. 【Not commonly used】
  • unique_subquery: This type replaces the ref of the IN subquery of the following form: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery is an index lookup function that can completely replace subqueries, which is more efficient. 【Not commonly used】
  • index_subquery: This join type is similar to unique_subquery. The IN subquery can be replaced, but it is only suitable for non-uniquery indexes in the following forms: value IN (SELECT key_column FROM single_table WHERE some_expr). 【Not commonly used】
  • range: Retrieve only rows of a given range and use an index to select rows.
  • index: The join type is the same as ALL. The difference between Full Index Scan and ALL is that the index type only traverses the index tree. This is usually faster than ALL, because the index file is usually smaller than the data file.
  • ALL: Full Table Scan, MySQL will traverse the entire table to find matching rows.

6 possible_keys

  • Indicates the index that may be used when querying. (Which index can MySQL use to find rows in this table)

7 key

  • The actual index (key) must be included in possible_keys. If no index is selected, the index is NULL.
  • To force MySQL to use or ignore the index in the possible_keys column,
  • Use FORCE INDEX, USE INDEX, or IGNORE INDEX in the query.

8 key_len

  • The length of the index (number of bytes used). If the index is NULL, the length is NULL.
  • Without loss of accuracy, the shorter the length, the better. The value displayed by key_len is the maximum possible length of the index field.
  • It is not the actual length used, that is, the key_len is calculated based on the table definition and is not retrieved from the table.

9 ref

  • Which column or constant is used, together with the index, is used to find the values ​​on the indexed column from the table. (Comparison between columns and indexes indicates the join matching conditions of the above table.)

10 rows

  • MySQL believes that the number of rows it must check when executing a query is estimated as the number of rows scanned.

11 filtered

  • Percent estimates of the number of rows filtered out by table conditions.

12 Extra

Description and detailed description of Mysql execution.

  • Distinct: After MySQL discovers the first matching row, stop searching for more rows for the current row combination.
  • Not exists: MySQL can optimize LEFT JOIN for query. After discovering a row that matches the LEFT JOIN standard, no longer checks more rows in the table for the previous row combination.
  • range checked for each record(index map: #): MySQL did not find a good index that can be used, but found that if the column values ​​from the previous table are known, it is possible that some of the indexes can be used.
  • Using filesort: When the order by operation is included in the Query and the sorting operation that cannot be completed with the index is called "file sorting".
  • Using index: Use only the information in the index tree without further searching to read the actual rows to retrieve column information in the table.
  • Using temporary: In order to solve the query, MySQL needs to create a temporary table to accommodate the result set, which is commonly found in sorting and grouping queries, and is commonly found in group by and order by.
  • Using where: No need to read all the information in the table, you can get the required data through the index just by obtaining the required data. This occurs when all the request columns of the table are part of the same index, indicating that the mysql server will filter the rows after the storage engine retrieves.
  • Using sort_union(…)、Using union(…)、Using intersect(…): These functions illustrate how to merge index scans for index_merge join type.
  • Using index for group-by: Similar to the Using Index method of accessing a table, Using index for group-by means that MySQL has found an index that can be used to query all columns in GROUP BY or DISTINCT query, without additional searching for the hard disk to access the actual table.
  • Using join buffer: The value change emphasizes that no index is used when obtaining the connection condition and that the connection buffer is required to store the intermediate result. If this value appears, it should be noted that it may be necessary to add an index to improve the capability depending on the specific situation of the query.
  • Impossible where: This value emphasizes that the where statement will cause no rows that meet the criteria (it is impossible to exist by collecting statistics).
  • Select tables optimized away: This value means that by using the index only, the optimizer may return only one row from the aggregate function result.
  • No tables used: Use from dual or do not contain any from clauses in the Query statement.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.