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

The following mainly brings you the MySQL query optimization tool explain. I hope these contents can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just look at the following MySQL query optimization tool explain.

In daily MYSQL optimization, we often see such a keyword: explain, for example:

EXPLAIN SELECT * FROM Cloud_Order WHERE money > 10

What is explain? Use the EXPLAIN keyword to simulate the optimizer's execution of SQL queries to know how MySQL handles your SQL statements. This can help you analyze the performance bottlenecks of your query or table structure. You can get this through the explain command:

Reading order of the table

Operation type of data read operation

Which indexes can be used

Which indexes are actually used

References between tables

How many rows per table are queried by the optimizer

First, let's look at the result of using EXPLAIN input.

The result shows that the result is a bunch of fields and corresponding values, but what do these fields mean? What is the corresponding value? How to analyze and optimize the performance of SQL through these fields? Don't worry, let's analyze them one by one.

EXPLANIN field analysis

Id: SELECT identifier. This is the query serial number of SELECT.

Select_type:SELECT type, which can be any of the following

SIMPLE: simple SELECT (no UNION or subquery)

PRIMARY: outermost SELECT

The second or subsequent SELECT statement in UNION:UNION

The second or subsequent SELECT 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

DERIVED: export the SELECT of the table (a subquery of the FROM clause)

Table: shows which table the data in this row is about

Type: this is one of the most important fields that show what type the query uses. The best to worst connection types are system, const, eq_reg, ref, range, index, and ALL. Here are the various types, sorted from the best to the worst:

System, const: query variables can be converted to constants. For example, id=1; id is the primary key or unique key.

Eq_ref: access the index and return a single row of data. (usually occurs during a join, and the index used by the query is a primary key or a unique key)

Ref: access the index and return the data of a value. (multiple rows can be returned) usually occurs when = is used

Range: this connection type uses an index to return rows in a range, such as using > or

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.