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

This article introduces the relevant knowledge of "what is the role of MySQL Explain". In the operation of actual cases, many people will encounter such a dilemma. Then 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?

When Explain is used with SQL statements, MySQL displays information from the optimizer about SQL execution. That is, MySQL explains how it will handle the statement, including how to join tables and what order to join tables, and so on.

Loading order of the table

Query type of sql

Which indexes may be used and which indexes are actually used

Reference relationship between table and table

How many rows in a table are queried by the optimizer

... ..

What information does Explain have?

The Explain execution plan contains the following field information: id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra12.

Detailed explanation of Explain implementation plan I. id

Id:: indicates the order in which the select clause or operation table is executed in the query. The higher the value of id, the higher the priority and the first execution. There are roughly three situations that can occur in id:

1. Id is the same

Seeing that the id of the three records is the same, it can be understood that the three tables are in a group with the same priority, and the execution order is from top to bottom, and the specific order is determined by the optimizer.

2. Id is different.

If there is a subquery in our SQL, the sequence number of the id will be incremented, and the higher the id value, the higher the priority, the first to be executed. When the three tables are nested in turn, it is found that the innermost subquery id is the largest and executes first.

3. The above two kinds exist at the same time

Modify the above SQL slightly, add a subquery, and find that the above two kinds of id exist at the same time. The same id is divided into one group, so there are three groups, and the same group is executed sequentially from top to bottom. The higher the id value of different groups, the higher the priority, the first to execute.

II. Select_type

Select_type: indicates the type of select query, which is mainly used to distinguish various complex queries, such as general queries, federated queries, subqueries, etc.

1 、 SIMPLE

SIMPLE: represents the simplest select query statement, that is, operations such as subqueries or union join and difference sets are not included in the query.

2 、 PRIMARY

PRIMARY: when the query contains any complex subsections, the outermost query is marked as PRIMARY.

3 、 SUBQUERY

SUBQUERY: when a subquery is included in the select or where list, the subquery is marked: SUBQUERY.

4 、 DERIVED

DERIVED: represents the select of the subquery contained in the from clause, and the subquery contained in our from list will be marked as derived.

5 、 UNION

UNION: if the select statement appears after union, it will be marked as union;. If union is included in the subquery of the from clause, the outer select will be marked as derived.

6 、 UNION RESULT

UNION RESULT: represents reading data from the temporary table of union, while the representation of the table column performs union operations with the results of the first and fourth select.

III. Table

The table name of the query is not necessarily a real table, an alias shows an alias, or a temporary table, such as DERIVED above, and so on.

IV. Partitions

The partition information matched when querying. NULL for non-partition table values. When the partition table is queried, partitions displays the partition information hit by the partition table.

5. Type

Type: which type of query is used is a very important indicator in SQL optimization. The following performance is in the following order: system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL.

1 、 system

System: when the table has only one row of records (system table), the amount of data is very small, often do not need to disk IO, the speed is very fast.

2 、 const

Const: indicates that the primary key primary key or unique unique index is hit during the query, or the joined part is a constant (const) value. This kind of scanning is very efficient, the amount of data returned is small, and the speed is very fast.

3 、 eq_ref

Eq_ref: hit the primary key primary key or unique key index when querying, and type is eq_ref.

4 、 ref

Ref: unlike eq_ref,ref, which means using a non-unique index, you will find many rows that meet the criteria.

5 、 ref_or_null

Ref_or_null: this connection type is similar to ref, except that MySQL searches for additional rows that contain null values.

6 、 index_merge

Index_merge: the index merge optimization method is used, and the query uses more than two indexes.

7 、 unique_subquery

Unique_subquery: replace the in subquery below, which returns a collection that is not duplicated.

Value IN (SELECT primary_key FROM single_table WHERE some_expr) 8, index_subquery

Index_subquery: different from unique_subquery, it is used for non-unique indexes and can return duplicate values.

Value IN (SELECT key_column FROM single_table WHERE some_expr) 9, range

Range: use the index to select rows and retrieve only rows within a given range. To put it simply, it is to retrieve data in a given range for an indexed field. Use bettween...and, in the where statement

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.