Shulou(Shulou.com)06/02 Report--

This article will explain in detail what is the use of Explain in Mysql. The content of the article is of high quality. Therefore, Xiaobian shares it with you as a reference. I hope that after reading this article, you will have a certain understanding of relevant knowledge.

1 Introduction

In our daily work, we often need to face slow queries. How to analyze the causes of slow queries requires the use of "EXPLAIN". The following describes in detail the 10 indicators corresponding to EXPLAIN (id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra) and the meanings of their corresponding values. See below.

2 Analysis of indicators

1）id

SQL execution order, the higher the id, the higher the priority, if the id is the same, then according to the order from top to bottom execution.

2）select_type

Represents the type of select statement, the specific type is as follows:

(1)SIMPLE(simple SELECT, no UNION or subqueries, etc.)

(2)PRIMARY(the outermost select is marked PRIMARY if the query contains any complex subparts)

(3)UNION(second or subsequent SELECT statement in UNION)

(4)DEPENDENT UNION(second or subsequent SELECT statement in UNION, depending on the outer query)

(5)UNION RESULT

(6)SUBQUERY(first SELECT in subquery)

(7)DEPENDENT SUBQUERY(first SELECT in subquery, depending on outer query)

(8)DERIVED(SELECT of derived tables, subquery of FROM clause)

(9)UNCACHEABLE SUBQUERY(the result of a subquery cannot be cached, the first line of the outer link must be reevaluated)

3）table

Corresponding table name. Sometimes the table name displayed is not the real table name, such as subquery temporary table association query, table name is (X is a number). The details are shown in the figure below.

4）type

Indicates how MySQL finds the desired row in the table, also known as an "access type."

Common types are: ALL, index, range, ref, eq_ref, const, system, NULL (from left to right, performance from poor to good)

ALL: Full Table Scan, MySQL will traverse the entire table to find matching rows

index: Full Index Scan, index differs from ALL as index type only traverses index tree

range: Retrieves only rows of a given range, using an index to select rows

ref: Indicates the join matching condition of the above table, i.e. which columns or constants are used to find values on index columns

eq_ref: Similar to ref, the difference is that the index used is a unique index. For each index key, only one record in the table matches. Simply put, it is to use primary key or unique key as the association condition in multi-table join.

const, system: Use these types of access when MySQL optimizes some part of the query and converts it to a constant. If you place the primary key in the where list, MySQL can convert the query to a constant,system is a special case of const type, when the query table has only one row, use system

NULL: MySQL decomposes statements during optimization, even without accessing tables or indexes, for example, selecting the minimum value from an index column can be done by a separate index lookup, i.e., without going back to the table query.

The following is a specific case.

5）possible_keys

Indicates which index MySQL can use to find records in the table. If an index exists on the field involved in the query, the index will be listed, but not necessarily used by the query.

This column is completely independent of the order of the tables shown in the EXPLAIN output. This means that some of the keys in possible_keys cannot actually be used in the generated table order.

If the column is NULL, there is no associated index. In this case, you can improve your query performance by checking the WHERE clause to see if it references certain columns or columns that fit into the index. If so, create an appropriate index and check the query again with EXPLAIN.

6）key

The key column shows the key (index) MySQL actually decided to use

If no index is selected, the key is NULL. To force MySQL to use or ignore indexes in the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in queries (although this is generally not recommended, it is best not to couple indexes in the database in business systems, which is easy to bury).

7）key_len

Indicates the number of bytes used in the index, which can be used to calculate the length of the index used in the query (the value displayed by key_len is the maximum possible length of the index field, not the actual length, that is, key_len is calculated according to the table definition, not retrieved from the table)

The shorter the length, the better, without loss of accuracy. (When querying database, one page per query (default 16K), the shorter the length, the more index nodes returned, the faster the speed)

8）ref

Indicates the join match condition for the above table, i.e. which columns or constants are used to find values on index columns.

9）rows

MySQL estimates the number of rows needed to read to find the required records according to table statistics and index selection (emphasis is placed on "estimation" here, mysql sometimes estimates incorrectly, so specific analysis is needed).

10）Extra

This column contains the details of MySQL's query resolution in the following cases:

Using where: column data is returned from tables that only use information in the index without reading the actual action, which occurs when all requested columns for the table are part of the same index, indicating that the mysql server will filter the rows after the storage engine retrieves them.

Using temporary: MySQL requires temporary tables to store result sets, common in sorting and grouping queries.

Using filesort: Sort operations in MySQL that cannot be done with indexes are called "file sort."

Using join buffer: Changed to emphasize that no index is used when retrieving join conditions and that a join buffer is needed to store intermediate results. If this value occurs, it should be noted that depending on the specifics of the query, you may need to add an index to improve performance.

Impossible where: This value emphasizes that the where statement results in no rows that match the condition.

Select tables optimized away: This value means that the optimizer may return only one row from the aggregate function result simply by using the index.

To use EXPLAIN, note the following:

EXPLAIN does not tell you about triggers, stored procedures, or how user-defined functions affect queries.

· EXPLAIN does not consider various caches

· EXPLAIN does not show the optimization work done by MySQL when executing queries

·Some statistics are estimates, not exact values

EXPALIN can only interpret SELECT operations, other operations should be rewritten as SELECT to view execution plans.

About Mysql Explain what is useful to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.