MySQLexplain actual operation process description

The following articles mainly describe how to use MySQLexplain. As we all know, explain shows how MySQL Databases use indexes to process select statements and connection tables. It can help you select a better practical application index and write more optimized query statements. Use the following command to add MySQLexplain before the select statement:

The following articles mainly describe how to use MySQL explain. We all know that explain shows how MySQL Databases use related indexes to process select statements and connection tables. It can help you select a better practical application index and write more optimized query statements. Use the following command to add MySQL explain before the select statement:

The following articles mainly describe how to use MySQL explain. We all know that explain shows how MySQL Databases use related indexes to process select statements and connection tables. It can help you select a better practical application index and write more optimized query statements.

Use the following command to add MySQL explain before the select statement:

Example: explain select surname, first_name form a, B where a. id = B. id

The analysis result is as follows:

Reference

 

  
MySQL> explain SELECT * FROM `whisper` WHERE to_id = 6696 AND del = 0 AND whisper=0 ORDER BY `send_time` DESC LIMIT 4;  
  
+----+-------------+---------+------+---------------+-------+---------+-------+------+-----------------------------+  
  
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |  
  
+----+-------------+---------+------+---------------+-------+---------+-------+------+-----------------------------+  
  
| 1 | SIMPLE | whisper | ref | to_id | to_id | 4 | const | 1 | Using where; Using filesort |  
  
+----+-------------+---------+------+---------------+-------+---------+-------+------+-----------------------------+  
  
1 row in set (0.00 sec) 
 

Description of the EXPLAIN column:

The table displays the data of this row about which table

Type 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 relevant domain.

The index actually used by the key. 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.

The length of the index used by key_len. The shorter the length, the better.

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

Rows MySQL considers the number of rows that must be checked to return the requested data

Extra additional 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 with the row, it no longer searches

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

Range checked for each

The Record (index map: #) does not find the desired index. Therefore, for each row combination in the preceding table, MySQL checks which index is used and uses it to return rows from the table. This is one of the slowest connections using indexes.

When Using filesort sees this, the query needs to be optimized. MySQL requires additional steps to find out how to sort the returned rows. It sorts all rows according to the connection type and the row pointer that stores the sort key value and all rows matching the condition.

The Using index 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 in the table are part of 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 may occur or the query is faulty.

Explanations of different connection types (sorted by efficiency order)

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

The maximum value of a record in the const 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.

While eq_ref is in connection, MySQLexplain reads a record from the Union of each record in the previous table during query, it is used when you query all data that uses the index as the primary key or unique key.

The ref 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 fewer the better

The range connection type returns rows in a range using an index, for example,> or <查找东西时发生的情况< p>

The index 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)

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