MYSQL performance analyzer EXPLAIN usage example analysis

This article uses an example to illustrate the usage of the MYSQL performance analyzer EXPLAIN. Share with you for your reference, the details are as follows:

Directions:

EXPLAIN SELECT * FROM user;

Environment and data preparation

-- Check the MySQL version SELECT VERSION();
 
-- What storage engines does MySQL provide? SHOW ENGINES;
 
-- View the default storage engine SHOW VARIABLES LIKE '%storage_engine%';

Output:

id: The output is an integer used to identify the execution order of the entire SQL. If the id is the same, the rows with different ids will be executed from top to bottom. The larger the id value is, the higher the execution priority is, and the row will be executed first. If the row references the union result of other rows, the value can be NULL.

select_type:[query type]

SIMPLE: Simple SELECT query, no UNION or subquery, including single-table query or multi-table JOIN query

PRIMARY: The outermost select query, commonly seen in subqueries or UNION queries. The outermost query is marked as PRIMARY

UNION: The second or subsequent SELECT in a UNION operation does not depend on the result set of the outer query (the outer query refers to the SELECT corresponding to the PRIMARY)

DEPENDENT UNION: The second or subsequent SELECT in a UNION operation depends on the result set of the outer query.

UNION RESULT: The result of UNION (no result if it is UNION ALL)

SUBQUERY: The first SELECT query in a subquery, which does not depend on the result set of the outer query

DEPENDENT SUBQUERY: The first select query in a subquery depends on the result of the outer query.

DERIVED: Derived table (temporary table), commonly seen when there is a subquery in the FROM clause

Note : MySQL 5.7 has a new feature for Derived table, which allows the child table in the Derived table that meets the conditions to be directly joined with the parent query table, thereby simplifying the execution plan and improving execution efficiency; by default, this feature is enabled in MySQL 5.7, so by default, the execution plan of the above SQL should be like this

MATERIALIZED: Materialized subquery, a new select_type introduced in MySQL 5.6, mainly used to optimize subqueries in FROM or IN clauses. For more details, see: Optimizing Subqueries with Materialization

UNCACHEABLE SUBQUERY: For the outer main table, the subquery cannot be cached and needs to be calculated each time.

UNCACHEABLE UNION: Similar to UNCACHEABLE SUBQUERY, but appears in UNION operations

SIMPLLE, PRIMARY, SUBQUERY, DERIVED These four will be encountered more often in actual work. Just understand these four. As for the others, just look up the information when you encounter them.

table: shows which table the corresponding row is accessing (the alias is displayed if there is one), and there will be similar values ​​such as <union2,3>, <subquery2>, and <derived2> (where 2,3, 2, 2 refer to the values ​​of the id column)

partitions: The partitions that the query matches. For non-partitioned tables, this value is NULL. In most cases, partitions are not used, so we don't need to pay attention to this column.

type:

The join type or access type specifies how MySQL decides to find the rows that meet the conditions in the table. This is an important basis for us to judge whether the query is efficient. For a complete introduction, please see: explain-join-types

system: This table has only one row (= system table), which is a special case of const type

const: When it is determined that there is only one matching row, the MySQL optimizer will read it before the query and only read it once, which is very fast. Used for comparisons of constant values ​​in primary key or unique indexes

eq_ref: For each row from the previous table, at most one qualifying record is returned from this table. This is very efficient when the index used by the connection is a PRIMARY KEY or UNIQUE NOT NULL index.

ref: Index access, also known as index lookup, returns all rows that match a single value. This type usually appears in multi-table JOIN queries, for non-UNIQUE or non-PRIMARY KEY, or queries that use the leftmost prefix rule index. In other words, if the JOIN cannot select a single row based on the keyword, use ref

fulltext: This is used when full-text indexing is used. This type of index is generally not used and will be replaced by a dedicated search service (solr, elasticsearch, etc.)

ref_or_null: Similar to ref, but adds a row that can specifically search for NULL

This is subject to the prerequisite that the weapon column has an index and there is NULL in the weapon column.

index_merge: This access type uses the index merge optimization method

This is also conditional. Both the id column and the weapon column have single-column indexes. If index_merge occurs and this type of SQL is used frequently later, you can consider replacing the single-column index with a composite index, which is more efficient.

unique_subquery: Similar to the eq_ref access method of the driven table in a two-table join, unique_subquery is used in some query statements containing IN subqueries. If the query optimizer decides to convert the IN subquery to an EXISTS subquery, and the subquery can use the primary key or unique index for equal value matching, unique_subquery will be used.

index_subquery: index_subquery is similar to unique_subquery, except that a normal index is used to access the table in the subquery.

range: Use an index to retrieve rows in a given range. When using =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, or IN operators and comparing keyword columns with constants, range is used. The premise is that it must be based on an index, that is, there must be an index on id.

Index: When we can use index coverage but need to scan all index records, we will use index; it is very common when doing statistics.

ALL: The familiar full table scan

possible_keys: shows which indexes may be used in this SQL, but they are not necessarily used during the query. If it is empty, it means that there is no index that can be used. In this case, you can check the WHERE statement to see if you can reference certain columns or create a new index to improve performance.

key: Displays the index actually used by this SQL. If no index is selected, this column is null. To force MySQL to use or ignore the index in the possible_keys column, use FORCE INDEX, USE INDEX, or I GNORE INDEX in the query.

key_len: Shows the key length (in bytes) that MySQL decided to use. If key is NULL, the length is NULL. The shorter the length, the better without losing accuracy.

ref: shows what the index column is equivalent to, such as a constant or a column. It displays the name of the column (or const), which is often Null.

rows: shows the number of rows that the MySQL parser thinks are expected to be scanned when executing this SQL. This value is an estimate, not a specific value, and is usually smaller than the actual value.

Filtered: shows the ratio of the number of rows returned to the number of rows that need to be read (the value of rows). Of course, the smaller the better.

extra:

Indicates additional information that is not in the other columns but is also important. There are many possible values. Let's go over some common ones.

Using index: Indicates that SQL uses a covering index instead of going back to the table to query data, which has very good performance.

Using where: indicates that the storage engine performs post-filtering after searching for records. If the query fails to use the index, using where only reminds us that MySQL should use the where condition to filter the result set.

Using temporary: means that MySQL needs to use a temporary table to store the result set, which is common in sorting and grouping queries

Using filesort: indicates that MySQL cannot use the index to sort directly (the sorted field is not an index field), and buffer space (memory or disk) will be used for sorting. Generally, this value indicates that SQL needs to be optimized, which consumes a lot of CPU.

impossible where: This additional information will be displayed when the WHERE clause of the query statement is always FALSE

Of course there are others, which are not common. Please check them out when you encounter them!!!

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.