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

This article introduces the relevant knowledge of "the performance optimization method of MySQL". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

What's the use of EXPLAIN?

MySQL provides an EXPLAIN command that parses SELECT statements and outputs details of SELECT execution for targeted optimization by developers.

How to use EXPLAIN?

The use of the EXPLAIN command is very simple, just add EXPLAIN before the SELECT statement, for example:

To demonstrate EXPLAIN, let's first create a table xttblog.

To demonstrate EXPLAIN, let's first create a table xttblog.

The output of the EXPLAIN command

The output format of the EXPLAIN command is roughly as follows:

Id: the identifier of the SELECT query. Each SELECT is automatically assigned a unique identifier. Each field is explained as follows:

Select_type: the type of SELECT query.

Table: which table is queried

Partitions: matching partition

Type: join type

Possible_keys: the index that may be selected in this query

Key: the exact index used in this query.

Ref: which field or constant is used with key

Rows: shows the total number of rows scanned by this query. This is an estimate.

Filtered: represents the percentage of data filtered by this query condition

Extra: additional information

We may know the meaning of each field, but each field corresponds to several values. So what does each value mean? Let's explain what each keyword means separately.

Select_type

Select_type represents the type of query, and its common values are:

SIMPLE: indicates that this query does not contain a UNION query or subquery

PRIMARY: indicates that this query is the outermost query

UNION: indicates that this query is the second or subsequent query of UNION

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

Results of UNION RESULT:UNION

SUBQUERY: the first SELECT in the subquery

DEPENDENT SUBQUERY: the first SELECT in the subquery, depending on the external query. That is, the subquery depends on the results of the outer query

The most common query category should be SIMPLE, for example, when our query does not have a subquery or a UNION query, then it is usually the SIMPLE type.

Table

Table, which represents the table or derived table involved in the query. Xttblog stands for the xttblog table. It represents a collection of the results of the first and second queries.

Partitions

Partitions: NULL . Represents whether partitions are used, and null indicates that there are no partitions.

Type

The type field is more important, which provides an important basis for judging whether the query is efficient or not. Through the type field, we determine whether the query is a full table scan or an index scan.

The commonly used values for type are:

System: there is only one piece of data in the table. This type is a special const type

Const: an equivalent query scan for a primary key or unique index that returns at most one row of data. Const query is very fast because it can be read only once.

Eq_ref: this type usually appears in multi-table join queries, indicating that for each result of the previous table, only one row of the latter table can be matched. And the comparison operation of the query is usually =, so the query is more efficient.

Ref: this type usually appears in multi-table join queries for non-unique or non-primary key indexes, or for queries that use the leftmost prefix rule index

Range: use index range query to get part of the data records in the table through the index field range. This type usually appears in =, >, > =

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.