How to use Explain in MySQL 01/18 Update SLTechnology News&Howtos

How to use Explain in MySQL

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use Explain in MySQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

one。 Grammar

Explain

< table_name >

For example: explain select * from T3 where id=3952602

II. Explain output explanation

+-- +

+-- +

1.id

My understanding is that SQL executes smoothly, and SQL executes from big to small.

For example:

Mysql > explain select * from (select * from (select * from T3 where id=3952602) a) b

+-- +

+-- +

| | 3 | DERIVED | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |

+-- +

Obviously this SQL is executed from the inside out, that is, from the id=3 up.

2. Select_type

It is the select type, which can be divided into the following types

(1) SIMPLE

Simple SELECT (without using UNION or subqueries, etc.) for example:

Mysql > explain select * from T3 where id=3952602

+-- +

+-- +

| | 1 | SIMPLE | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |

+-- +

(2)。 PRIMARY

My understanding is the outermost select. For example:

Mysql > explain select * from (select * from T3 where id=3952602) a

+-- +

+-- +

| | 2 | DERIVED | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |

+-- +

(3) UNION

The second or subsequent SELECT statement in UNION. For example

Mysql > explain select * from T3 where id=3952602 union all select * from T3

+-- +

+-- +

| | 1 | PRIMARY | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |

| | 2 | UNION | T3 | ALL | NULL | NULL | NULL | NULL | 1000 |

+-- +

(4) DEPENDENT UNION

The second or subsequent SELECT statement in UNION, depending on the external query

Mysql > explain select * from T3 where id in (select id from T3 where id=3952602 union all select id from T3)

+-+-

+-+-

+-+-

(4) UNION RESULT

Results of UNION.

Mysql > explain select * from T3 where id=3952602 union all select * from T3

+-- +

+-- +

| | 1 | PRIMARY | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |

| | 2 | UNION | T3 | ALL | NULL | NULL | NULL | NULL | 1000 |

+-- +

(5) SUBQUERY

The first SELECT in the subquery.

Mysql > explain select * from T3 where id= (select id from T3 where id=3952602)

+-- +

+-- +

| | 1 | PRIMARY | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |

| | 2 | SUBQUERY | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | Using index |

+-- +

(6)。 DEPENDENT SUBQUERY

The first SELECT in the subquery, depending on the external query

Mysql > explain select id from T3 where id in (select id from T3 where id=3952602)

+-+-

+-+-

+-+-

(7). DERIVED

The SELECT of the derived table (subquery of the FROM clause)

Mysql > explain select * from (select * from T3 where id=3952602) a

+-- +

+-- +

| | 2 | DERIVED | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |

+-- +

3.table

Show which table the data in this row is about.

Sometimes it is not the real table name, what you see is derivedx (x is a number, my understanding is the result of which step to execute)

Mysql > explain select * from (select * from (select * from T3 where id=3952602) a) b

+-- +

+-- +

| | 3 | DERIVED | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |

+-- +

4.type

This column is important to show which category is used for the connection and whether or not indexes are used.

The best to worst connection types are const, eq_reg, ref, range, indexhe, and ALL

(1) system

This is a special case of the const join type. There is only one row in the table that meets the criteria. As follows (id on table T3 is primary key)

Mysql > explain select * from (select * from T3 where id=3952602) a

+-- +

+-- +

| | 2 | DERIVED | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |

+-- +

(2) const

The table has at most one matching row, which will be read at the beginning of the query. Because there is only one row, the column values in this row can be considered constant by the rest of the optimizer. Const tables are fast because they are only read once!

Const is used when comparing all parts of an PRIMARY KEY or UNIQUE index with constant values. In the following query, tbl_name can be used for the const table:

SELECT * from tbl_name WHERE primary_key=1

SELECT * from tbl_name WHERE primary_key_part1=1 and primary_key_part2=2

For example:

Mysql > explain select * from T3 where id=3952602

+-- +

+-- +

| | 1 | SIMPLE | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |

+-- +

(3)。 Eq_ref

For each combination of rows from the previous table, read a row from that table. This is probably the best join type, except for the const type. It is used by joins in all parts of an index and the index is UNIQUE or PRIMARY KEY.

Eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses the columns of the table read before the table.

In the following example, MySQL can use eq_ref joins to handle ref_tables:

SELECT * FROM ref_table,other_table

WHERE ref_table.key_column=other_table.column

SELECT * FROM ref_table,other_table

WHERE ref_table.key_column_part1=other_table.column

AND ref_table.key_column_part2=1

For example

Mysql > create unique index idx_t3_id on T3 (id)

Query OK, 1000 rows affected (0.03 sec)

Records: 1000 Duplicates: 0 Warnings: 0

Mysql > explain select * from t3, T4 where t3.id=t4.accountid

+-+-

+-+-

| | 1 | SIMPLE | T4 | ALL | NULL | NULL | NULL | NULL | 1000 |

| | 1 | SIMPLE | T3 | eq_ref | PRIMARY,idx_t3_id | idx_t3_id | 4 | dbatest.t4.accountid | 1 | |

+-+-

(4) ref

For each combination of rows from the previous table, all rows with matching index values are read from this table. Use ref if the join uses only the leftmost prefix of the key, or if the key is not UNIQUE or PRIMARY KEY (in other words, if the join cannot select a single row based on the keyword). This join type is good if the keys used match only a small number of rows.

Ref can be used for indexed columns that use the = or operator.

In the following example, MySQL can use ref joins to handle ref_tables:

SELECT * FROM ref_table WHERE key_column=expr

SELECT * FROM ref_table,other_table

WHERE ref_table.key_column=other_table.column

SELECT * FROM ref_table,other_table

WHERE ref_table.key_column_part1=other_table.column

AND ref_table.key_column_part2=1

For example:

Mysql > drop index idx_t3_id on T3

Query OK, 1000 rows affected (0.03 sec)

Records: 1000 Duplicates: 0 Warnings: 0

Mysql > create index idx_t3_id on T3 (id)

Query OK, 1000 rows affected (0.04 sec)

Records: 1000 Duplicates: 0 Warnings: 0

Mysql > explain select * from t3, T4 where t3.id=t4.accountid

+-+-

+-+-

| | 1 | SIMPLE | T4 | ALL | NULL | NULL | NULL | NULL | 1000 |

| | 1 | SIMPLE | T3 | ref | PRIMARY,idx_t3_id | idx_t3_id | 4 | dbatest.t4.accountid | 1 | |

+-+-

2 rows in set (0.00 sec)

(5)。 Ref_or_null

The join type is like ref, but MySQL is added to specifically search for rows that contain null values. The optimization of this join type is often used in solving subqueries.

In the following example, MySQL can use ref_or_null joins to handle ref_tables:

SELECT * FROM ref_table

WHERE key_column=expr OR key_column IS NULL

(6)。 Index_merge

The join type indicates that the index merge optimization method is used. In this case, the key column contains a list of the indexes used, and the key_len contains the longest key elements of the index used.

For example:

Mysql > explain select * from T4 where id=3952602 or accountid=31754306

+- -+-+

+- -+-+

+- -+-+

1 row in set (0.00 sec)

(7)。 Unique_subquery

This type replaces the ref of the in subquery in 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 and is more efficient.

(8). Index_subquery

The join type is similar to unique_subquery. In subqueries can be replaced, but only for non-unique indexes in the following forms of subqueries:

Value IN (SELECT key_column FROM single_table WHERE some_expr)

(9). Range

Only a given range of rows are retrieved, using an index to select rows. The key column shows which index is used. Key_len contains the longest key element of the index used. In this type, ref is listed as NULL.

When using =, >, >,

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.