Usage of explain in mysql

The usage of explain in mysql has recently encountered some database problems during performance tests. Generally, you can use slow query logs to find SQL statements with poor execution performance, however, it is not possible to find these SQL statements. We need to help developers analyze the problem. This often uses explainexplain to show how mysql uses indexes to process select statements and connect tables. It can help you select better indexes and write more optimized query statements. You can use the explain statement before the select statement, for example, explain select surname, first_name form a, B where. id = B. the id analysis result is as follows: table | type | possible_keys | key | key_len | ref | rows | Extra EXPLAIN column explanation: the table www.2cto.com displays the data of this row about which table type is an important column and shows the type used by the connection. The best to worst connection types are const, eq_reg, ref, range, indexhe, and ALL possible_keys. The indexes that may be applied to this table are displayed. If it is null, there is no possible index. You can select an index suitable for the statement key from the WHERE statement for the related domain. 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 length used by INDEX key_len. Without compromising accuracy, the shorter the length, the better. ref indicates which column of the index is used. If possible, it is a constant that rows MYSQL considers to be required to check the number of rows used to return request data Extra information about how MYSQL parses the query. We will discuss it in the table, but here we can see that the bad examples are Using temporary and Using filesort, which means MYSQL cannot use indexes at all, the result is that the search will be slow. The meaning of the description returned by the extra column is www.2cto.com Distinct. Once MYSQL finds the row that matches with the row, it no longer searches for Not exists MYSQL and optimizes left join, once it finds a row that matches the left join standard, it no longer searches for Range checked for each Record (index map: #) and does not find the ideal 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 you see this in Using filesort, the query needs to be optimized. MYSQL requires additional steps to find out how to sort the returned rows. It sorts data in the Using index column of all rows based on the connection type and the row pointer of all rows that store the sort key value and match the condition, instead of actually reading the information in the index., this occurs when all the request columns of the table are the same index, and 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 performed on different column sets, instead of using the Where clause on www.2cto.com WHERE used on group by to limit which rows match with the next table or which rows are returned 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. 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 to treat eq_ref in the connection. when MYSQL queries, from the previous table, the Union of each record reads a record from the table, it uses ref when the query uses the index as the primary key or unique key. This connection type is only used when the query uses a key that is not the only or primary key or a part of these types (for example, occurs with the leftmost prefix. For each row union in the previous table, all records are read from the table. This type is heavily dependent on the number of records matched by the index-the less the better www.2cto.com range. This connection type uses the index to return rows in a range, for example, if you use> or <when something is found, 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 worse, and the author gzh0222 should be avoided as much as possible.