Use and analysis of Mysql Explain command

The mysql explain command is used to show how MySQL uses indexes to process select statements and join tables. This command can be used to analyze the performance bottleneck of query statements or table structures, so as to write SQL statements with better performance. Through the expalin command, we can get:

• 1. Table reading order
• 2. Operation type of table read operation
• 3. Which indexes can be used?
• 4. Which indexes are actually used?
• 5. References between tables
• 6. How many rows of each table are queried by the optimizer?

To use explain, just add explain before the select statement, such as:

explain select * form codetc;

You will get a result like this:

Below we briefly explain the explain result column:

id: The execution order identifier of the statement.

• select_type: SELECT type, which can be any of the following.
• simple -- means no subqueries and unions
• subquery --subquery
• Derived --temporary table
• union
• union result --union of results

In actual development, subqueries should be used as little as possible and joins should be used to complete them.

table : Displays which table this row of data is about, that is, the table name.

type : This is the important column and shows what type of connection is used. The join types from best to worst are const, eq_reg, ref, range, indexhe, and ALL.

possible_keys : Shows the possible indexes that can be applied to this table. If empty, no index is possible. You can select an appropriate statement from the WHERE clause for the relevant field.

key : The actual index used. If NULL, no index is used. In rare cases, MYSQL will choose an under-optimized index. In this case, you can use USE INDEX (indexname) in the SELECT statement to force the use of an index or use IGNORE INDEX (indexname) to force MySQL to ignore the index.

key_len : The length of the index to use. The shorter the length, the better without losing accuracy.

ref : Shows which column of the index is used, or a constant if possible.

rows : The number of rows that MYSQL considers necessary to check to return the requested data.

Extra : Additional information about how MYSQL parses the query. This will be discussed in Table 4.3, but the bad examples that can be seen here are Using temporary and Using filesort, which means that MYSQL cannot use the index at all, resulting in slow retrieval.

The meaning of the description returned in the extra column

Distinct : Once MYSQL finds a row that matches the row in the join, it will not search any more.

Not exists : MYSQL optimizes LEFT JOIN, once it finds a row matching the LEFT JOIN criteria, it does not search any more.

Range checked for each Record (index map: #): No ideal index was found, so for each row combination from the previous table, MYSQL checks which index to use and uses 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 needs to perform an extra step to discover how to order the returned rows. It sorts all the rows based on the join type and stores the sort key value and row pointers for all the rows that match the condition.

Using index : Column data is returned from the table using only the information in the index without actually reading it. This happens when all requested columns for the table are part of the same index.

Using temporary : When you see this, the query needs to be optimized. Here, MYSQL needs to create a temporary table to store the results, which usually happens when ORDER BY is performed on different sets of columns, rather than GROUP BY.

Where used: The WHERE clause is used to restrict which rows will be matched with the next table or returned to the user. This can happen if you do not want to return all rows in the table and the join type is ALL or index, or if there is a problem with the query. Explanation of different join types (sorted in order of efficiency)

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

const : The maximum value of a record in the table that 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 reads the value first and then treats it as a constant.

eq_ref : In a connection, MYSQL reads a record from the previous table for each record union when querying. It is used when the query uses an index as the primary key or unique key.

ref : This join type occurs only when the query uses a key that is not a unique or primary key, or a part of one of these types (for example, using a leftmost prefix). For each row join with the previous tables, all records will be read from the table. This type depends heavily on how many records are matched against the index - the fewer the better.

range : This join type uses an index to return a range of rows, such as what happens when you use > or < to find something.

index : This join type performs a full scan of every record in the previous table (better than ALL because the index is usually smaller than the table data).

ALL : This join type performs a full scan of each record in conjunction with the previous one, which is generally bad and should be avoided.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links