Detailed explanation of the EXPLAIN command and its usage in MySQL

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

2. Now, the useful contents are recorded as follows:

1. EXPLAIN shows how MySQL uses indexes to process select statements and join tables. It can help choose better indexes and write more optimized query statements.

To use it, 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 the EXPLAIN column:

table: Displays which table the data in this row is about

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: Displays the indexes that may 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 used. The shorter the length, the better without losing accuracy.

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

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.

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

Distinct: Once MYSQL finds a row that matches the row, it will no longer search.

Not exists: MYSQL optimizes LEFT JOIN. Once it finds a row that matches the LEFT JOIN criteria, it no longer searches.

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 the index

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 depends on the connection type and

Sort all rows by storing the sort key value and row pointers to all 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. If you do not want to return all rows in the table, and the connection type

ALL or index, this will happen, or there is a problem with the query. Explanation of different connection types (sorted in order of efficiency)

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

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). Since 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 join, MYSQL reads a record from the previous table for each record in the join. It uses the index as the primary key or unique key in the query.

One-click all use

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 the previous table

For each row join, 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 generally smaller than the table data)

ALL: This join type performs a full scan of each of the previous records. This is generally bad and should be avoided.

Summarize

The above is a detailed explanation of the EXPLAIN command and its usage in MySQL. I hope it will be helpful to you. If you have any questions, please leave me a message.