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

This article introduces the knowledge of "how to use explain in mysql". In the operation of actual cases, many people will encounter such a dilemma, 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!

In mysql, the explain command is mainly used to view the execution plan of the SQL statement, which simulates the optimizer's execution of the SQL query statement, thus knowing how mysql handles the user's SQL statement with the syntax "explain SQL statement;".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What is the use of explain in mysql

The explain command mainly looks at the execution plan of the SQL statement to see if the SQL statement uses an index, does a full table scan, and so on. It simulates the optimizer's execution of SQL query statements to know how MySQL handles users' SQL statements.

In our daily work, we sometimes slow down the query to record some SQL statements that have been executed for a long time, and finding these SQL statements does not mean we are done. Sometimes we often use the explain command to check the execution plan of these SQL statements, to see if the SQL statement has been indexed, and whether it has done a full table scan, which can be viewed through the explain command. So we take a closer look at MySQL's cost-based optimizer and get a lot of details about access policies that might be considered by the optimizer and which policies are expected to be adopted by the optimizer when running SQL statements.

-- actual SQL. Find the employee whose user name is Jefabc select * from emp where name = 'Jefabc';-- to see whether SQL uses the index. Add explain before it, and you can explain select * from emp where name =' Jefabc'.

There are 10 columns of information from expain, which are id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

Summary description:

Id: select an identifier

Select_type: indicates the type of query.

Table: table that outputs the result set

Partitions: matching partition

Type: indicates the connection type of the table

Possible_keys: indicates the index that may be used when querying

Key: represents the index actually used

Key_len: the length of the index field

Ref: comparison of columns and indexes

Rows: number of rows scanned (estimated rows)

Filtered: percentage of rows filtered by table condition

Extra: description and description of implementation

The possible occurrence of these fields is explained below:

1. Id

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

My understanding is the identification of the order in which SQL is executed, and the execution of SQL from large to small

1. When the id is the same, the execution order is from top to bottom.

two。 If it is a subquery, the sequence number of the id will be incremented. The higher the id value, the higher the priority, and the more it will be executed first.

3. If the id is the same, it can be considered as a group, which is executed sequentially from top to bottom. In all groups, the higher the id value is, the higher the priority is, and the first execution is.

Check the employees in the R & D department whose names start with Jef. Classic query explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name =' R & D'; 2. Select_type

Shows the type of each select clause in the query

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

(2) PRIMARY (outermost query in a subquery. If the query contains any complex subparts, the outermost select is marked PRIMARY)

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

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

(5) UNION RESULT (result of UNION, all select following the start of the second select in the union statement)

(6) SUBQUERY (the first SELECT in the subquery, the result does not depend on the external query)

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

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

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

III. Table

Displays the name of the table in the database accessed by this step (showing which table the data in this row is about), sometimes it is not the real table name, it may be an abbreviation, such as ePercentd above, or it may be an abbreviation for the result of the first step.

IV. Type

Access to the table means how MySQL finds the required rows in the table, also known as the "access type".

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

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

Index: Full Index Scan,index differs from ALL in that index only traverses the index tree

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

Ref: indicates the join matching condition of the above table, that is, which columns or constants are used to find the value on the index column

Eq_ref: similar to ref, the difference is that the index used is the unique index. For each index key value, only one record in the table matches. To put it simply, primary key or unique key is used as the association condition in multi-table joins.

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

NULL: MySQL breaks up statements during optimization and executes without even accessing a table or index. For example, selecting a minimum value from an index column can be done through a separate index lookup.

5. Possible_keys

Indicates which index MySQL can use to find records in the table, and if there is an index on the fields involved in the query, the index will be listed, but not necessarily used by the query (indexes that can be used by the query, if no indexes show null)

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

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 are suitable for the index. If so, create an appropriate index and check the query again with EXPLAIN

VI. Key

The key column shows the key (index) that MySQL actually decides to use, which must be included in the possible_keys

If no index is selected, the key is NULL. To force MySQL to use or ignore indexes in possible_keys columns, use FORCE INDEX, USE INDEX, or IGNORE INDEX in the query.

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 used length, that is, key_len is calculated according to the table definition, not retrieved within the table)

Without losing accuracy, the shorter the length, the better.

VIII. Ref

The comparison between the column and the index indicates the join matching condition of the above table, that is, which columns or constants are used to find the value on the index column

IX. Rows

Estimate the number of rows in the result set, which means that MySQL estimates the number of rows to read to find the required records based on table statistics and index selection.

10. Extra

This column contains the details of the MySQL resolution query, and there are several situations:

Using where: instead of reading all the information in the table, you can get the required data only through the index. This occurs when all the request columns of the table are part of the same index, indicating that the mysql server will filter the rows after the storage engine has retrieved them.

Using temporary: indicates that MySQL needs to use temporary tables to store result sets, which is common in sorting and grouping queries, such as group by; order by

Using filesort: when the Query contains order by operations, and the sort operation that cannot be done with the index is called "file sorting"

-- Test the filesortexplain select * from emp order by name of Extra

Using join buffer: the change emphasizes that the index is not used when getting the join condition and that a connection buffer is required to store the intermediate results. If this value occurs, it should be noted that indexes may need to be added to improve energy depending on the specific circumstances of the query.

Impossible where: this value emphasizes that the where statement results in no qualified rows (no results are possible by collecting statistics).

Select tables optimized away: this value means that by using the index alone, the optimizer may return only one row from the aggregate function result

Use from dual in No tables used:Query statements or do not contain any from clauses

-- explain select now () from dual

Summary:

EXPLAIN will not tell you about triggers, stored procedures, or the impact of user-defined functions on queries.

EXPLAIN does not consider all kinds of Cache

EXPLAIN cannot show the optimization work done by MySQL when executing the query

Some of the statistics are estimated, not exact values

EXPALIN can only explain SELECT operations. Other operations should be rewritten as SELECT to view the execution plan.

There can be no results by collecting statistics.

This is the end of the content of "how to use explain in mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.