Detailed analysis of mysql explain command

Detailed analysis of mysql explain command

The mysql explain command is used to show how mysql uses indexes to process select statements and connect tables. We can use this command to analyze the performance bottleneck of the query or table structure, so as to write SQL statements with better performance. Through the expalin command, we can get:

1. Reading sequence of the table

2. Read operation type of the table

3. Which indexes can be used?

4. Which indexes are actually used?

5. Reference between tables

6. The optimizer queries the number of rows in each table.

To use the explain statement, add the explain statement before the select statement:

For example:

Explain select * form phpernote_article;

The following result is displayed:

The following is a simple explanation of the explain result column:

Id: The execution sequence id of the statement.

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

Simple, meaning no subquery and union

Subquery, subquery

Derived, temporary table

Union

Union result
 
In actual development, subqueries should be used as little as possible and connections should be used for completion.

Table: The table that displays the data of this row, that is, the table name.

Type: this is an important column that shows the type used by the connection. The connection types from the best to the worst are const, eq_reg, ref, range, indexhe, and ALL.

Possible_keys: displays the indexes that may be applied to this table. If it is null, there is no possible index. You can select an appropriate statement from the WHERE statement for the related domain.

Key: actually used index. If it is NULL, no index is used. In rare cases, MYSQL selects an optimized index. In this case, you can use index (indexname) in the SELECT statement to force an INDEX or use ignore index (indexname) to force MYSQL to IGNORE the INDEX.

Key_len: the length of the index used. The shorter the length, the better.

Ref: displays which column of the index is used. If possible, it is a constant.

Rows: the number of rows that MYSQL considers to be checked to return request data.

Extra: Extra information about how MYSQL parses the query. We will discuss it in table 4.3, but here we can see that the bad examples are Using temporary and Using filesort, which means MYSQL cannot use indexes at all, and the result is that the retrieval will be slow.

Meaning of the description returned by the extra column

Distinct: Once MYSQL finds the row that matches the row, it does not search any more.

Not exists: MYSQL optimizes left join. Once it finds a row that matches the left join standard, it no longer searches.

Range checked for each Record (index map: #): no ideal index is found. Therefore, for each row combination in the preceding table, MYSQL checks which index is used, use it to return rows from the table. This is one of the slowest connections using indexes.

Using filesort: When you see this, the query needs to be optimized. MYSQL requires additional steps to find out how to sort the returned rows. It sorts all rows based on the connection type and the row pointer that stores the sort key value and all rows matching the condition.

Using index: the column data is returned from a table that only uses the information in the index but does not read the actual action, this occurs when all the request columns of the table are the same index.

When Using temporary sees this, the query needs to be optimized. Here, MYSQL needs to create a temporary table to store the results. This usually happens when order by is applied to different column sets, rather than group.

Where used uses the WHERE clause to limit which rows match the next table or return the rows to the user. If you do not want to return ALL rows in the table and the connection type is ALL or index, this will happen, or if there is a problem with the query interpretation of different connection types (sort by efficiency order)

The system table has only one row: system table. This is a special case of the const connection type.

Const: the maximum value of a record in the table can match this query (the index can be a primary key or a unique index ). Because there is only one row, this value is actually a constant, because MYSQL first reads this value and treats it as a constant.

Eq_ref: During the connection, MYSQL reads a record from the table from the Union of each record in the previous table during query, it is used to query all indexes that use the primary key or unique key.

Ref: This connection type only occurs when the query uses keys that are not the only or primary key, or some of these types (for example, using the leftmost prefix. For each row union in the previous table, all records are read from the table. This type depends heavily on the number of records matched by the index-the smaller the number, the better.

Range: This connection type uses an index to return rows in a range, such as> or <what happens when something is searched.

Index: This connection type performs a full scan of each record in the preceding table (better than ALL, because the index is generally smaller than the table data ).

ALL: this connection type performs a full scan for each of the preceding records. This is generally poor and should be avoided as much as possible.

Articles you may be interested in

• MySQL replace function replacement string statement usage
• MySQL index Operation Command (create index, re-index, query index, delete index) Summary
• Mysql Command for querying table Indexes
• Linux shutdown command Summary
• How to check MySQL startup time and running time
• Summary of linux Command for viewing File Content
• Create a folder (mkdir) command in linux
• Steps for synchronizing and backing up mysql Databases in windows