Detailed explanation of EXPLAIN interpretation command and usage explanation in MySQL

1. Scenario description: My colleague taught me to use explain in mysql, so I checked the meaning of the returned content once

2. Now make the following records of useful contents:

1. explain shows how mysql uses indexes to process select statements and join tables. It can help you choose a better index and write a more optimized query statement.

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

explain select count(DISTINCT uc_userid) as user_login from user_char_daily_gameapp_11 where uc_date >= "2017-09-04" and uc_date<="2017-09-08" AND uc_date >= "2017-06-01" LIMIT 1 

2. Explanation of EXPLAIN column:

table: Shows which table the 1-row data is about

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

possible_keys: Displays indexes that might be applied to this table. If empty, there is no possible index. You can select 1 appropriate statement from the WHERE statement for the related domain

key: The index actually used. If it is NULL, no index is used. In rare cases, MYSQL will choose indexes with insufficient optimization. In this case, you can use USE INDEX (indexname) in the SELECT statement to force 1 index or IGNORE INDEX (indexname) to force MYSQL to ignore the index

key_len: The length of the index used. Without losing accuracy, the shorter the length, the better

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

rows: The number of rows that MYSQL thinks must be checked to return the requested data

Extra: Additional information on how MYSQL resolves queries. This is discussed in Table 4.3, but the bad examples you can see here are Using temporary and Using filesort, meaning that MYSQL can't use indexes at all, resulting in slow retrieval

3. The meaning of the description returned by the extra column

Distinct: 1 Once MYSQL finds a row that matches the row union, it no longer searches

Not exists: MYSQL optimizes LEFT JOIN, and once it finds a row that matches the LEFT JOIN standard, it stops searching

Range checked for each Record (index map: #): The ideal index was not found, so for every 1 row combination from the previous table, MYSQL checks which index is used and uses it to return rows from the table. This is one of the slowest connections to use indexes

Using filesort: When you see this, the query needs to be optimized. MYSQL requires additional steps to discover how to sort the returned rows. It depends on the connection type and

Sort all rows by storing row pointers for all rows with sort key values and matching criteria

Using index: Column data is returned from a table that only uses the information in the index without reading the actual action, which occurs 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 typically occurs on ORDER BY for different column sets instead of GROUP BY

Where used uses the WHERE clause to restrict which rows will match the next table or be returned to the user. If you do not want to return all the rows in the table and the join type

ALL or index, this will happen, or the query has a problem with the interpretation of different join types (sorted in order of efficiency)

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

const: The maximum value of 1 record in the table matches this query (the index can be a primary key or a unique 1 index). Because there is only one line, this value is actually a constant, because

MYSQL reads this value and treats it as a constant

eq_ref: In a join, MYSQL reads 1 record from the table for every union of 1 record from the previous table when querying, which uses the index as the primary key or only in the query

Use when all of the 1 key

ref: This join type only occurs if the query uses a key that is not a unique 1 or primary key or part of these types (for example, using the leftmost prefix). For the previous table

Every 1 row union, all records will be read out of the table. This type depends heavily on how many records match according to the index & as few as possible

range: This join type uses an index to return rows in a range, such as using the > Or < What happens when you look for something

index: This join type completely scans every record union in the previous table (better than ALL, because index 1 is generally smaller than table data)

ALL: This connection type combines full scanning for every previous record, which is bad and should be avoided as much as possible

Summarize

Above is this site to introduce the detailed explanation of MySQL EXPLAIN explanation command and usage explanation, I hope to help you, if you have any questions, please give me a message