MySQL EXPLAIN statement usage examples

When it comes to MySQL optimization, the first thing we need to know is how our current SQL statement is executed in the actual database before we can talk about how to optimize it. In MySQL, we are provided with a very useful keyword for simulating statement execution: EXPLAIN. EXPLAIN can be used to view the execution effect of SQL statements, which can help select better indexes and optimize query statements, and write better optimized statements. So today we will talk about some basic usage and applications of this keyword.

1. Usage

The usage of EXPLAIN is very simple:

mysql> EXPLAIN SELECT * FROM user;

Simply put, add the EXPLAIN keyword in front of the original SQL statement, or add the EXPLAIN keyword followed by the SQL statement you want to check.

2. Output results

The output of the EXPLAIN statement is the data we want and the focus of our analysis.
Let's first look at the form of the corresponding results given by the above statement:

+----+-------------+-------+------------+------+---------------+------+---------+------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+------+------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+------+------+

The EXPLAIN statement gives us a total of 10 columns of data. Next, let's take a look at the meanings of some data columns that are more important in performance optimization.

1.id

This is the sequence number of the select query.

2.select_type

When our SQL statement is a non-select statement (ie delete, update...), the value of this field is the corresponding operation type (delete, update...).

mysql> EXPLAIN INSERT INTO user VAULES(2,'ahong','31');

The output select_type at this time is our corresponding INSERT:

+----+-------------+-------+------------+------+---------------+------+---------+------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+------+------+
| 1 | INSERT | user | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+------+------+

When the SQL statement is a select statement, it corresponds to some detailed select types, which can be as follows:

SIMPLE: Simple SELECT (does not use UNION or subqueries, etc.)
PRIMARY: the outermost SELECT
UNION: The second or subsequent SELECT statement in a UNION DEPENDENT UNION: The second or subsequent SELECT statement in a UNION depends on the outer query UNION RESULT: The result of the UNION.
SUBQUERY: The first SELECT in a subquery
DEPENDENT SUBQUERY: The first SELECT in a subquery, which depends on the outer query DERIVED: The SELECT of the derived table (a subquery in the FROM clause)

Here is an example of the simplest possible SIMPLE query:

mysql> EXPLAIN SELECT * FROM user;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+------+------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+------+------+

3.table

Shows which table the data accessed in this operation is about.

4.partitions

Displays the partitions used by the table. If you want to count the amount of company orders for ten years, you can divide the data into ten partitions, one for each year. This can greatly improve query efficiency.

5.type

This is the most important column. Shows which class the connection uses and whether or not an index is used. It is the key to analyzing query performance.
The results are as follows from best to worst:

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

The meanings of these situations are as follows:

• system, const: You can convert the query variable into a constant. For example, id=1; id is the primary key or unique key.
• eq_ref: Access the index and return the data of a single row. (Usually appears when joining, the index used in the query is the primary key or unique key)
• ref: Access the index and return the data of a certain value. (Multiple rows can be returned) Usually occurs when using =
• range: This join type uses an index to return a range of rows, such as what happens when you use > or < to find something, and there is an index on the field (note: not necessarily better than index)
• Index: Scan the entire table in the order of the index. The advantage is that there is no need to sort, but the disadvantage is that the entire table must be scanned.
• ALL: Full table scan, should be avoided as much as possible_

Generally speaking, you must ensure that the query reaches at least the range level, preferably the ref level, otherwise performance issues may occur.

6.possible_key

Displays the index columns that may be used by the query statement. The value may be one, multiple or null.

7.key

The key column shows the index column actually used by the query statement. If null, the index is not used.
Show the actual effect of possible_key and key:
Below is a data table with an index on the age column. We perform the following query:

mysql> explain select * from user where age = 1;

The following results will be obtained:

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+------+------+------+
| 1 | SIMPLE | user | NULL | ref | age | age | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+------+------+------+

8.key_len

Displays the length of the index used by the current query statement. The shorter the length, the better without losing accuracy.

9.ref

The column of the previous table being referenced.

10.rows

Based on the table and query, MySQL estimates the number of rows it must examine to return the final result. The larger the value of this column, the worse the query efficiency.

11.filtered

A percentage value, used together with the value of the rows column, can estimate the result set of the previous table in the query execution plan (QEP) to determine the number of iterations of the join operation. Small tables drive large tables, reducing the number of joins.

12.extra

There are several types of additional information about how MySQL parses queries:

The values ​​contained in Extra are:

• using index: Only use the index, which can avoid accessing the table and has high performance.
• using where: Use where to filter data. Not all where clauses need to display using where. For example, access the index in the = way.
• using tmporary: Use a temporary table to process the current query.
• using filesort: Use additional sorting. At this time, MySQL will browse all qualifying records according to the join type, save the sort key and row pointer, and then sort the key and retrieve the rows in order. (When order by v1 is used and no index is used, additional sorting is performed.)
• range checked for eache record(index map:N): No good index can be used.
• Using index for group-by: __ indicates that all the data required for grouping can be found in the index, without querying the actual table. explain select user_id from t_order group by user_id;_

The above is the detailed content of the usage example of MySQL EXPLAIN statement. For more information about MySQL EXPLAIN statement, please pay attention to other related articles on 123WORDPRESS.COM!