The usage of explain in MySQL

The usage of explain in MySQL

  recently encountered some database problems in performance testing, often using slow query log to find the execution of poor performance of SQL, but only to find these SQL is not possible, we need to help developers to analyze the problem, This often uses Explainexplain to show how MySQL uses indexes to process SELECT statements and join tables. Can help select better indexes and write more optimized query statements.   Use method, add explain to the SELECT statement:   such as: Explain select Surname,first_name form A, a where a.id=b.id  The   analysis results form as follows:  table |  type | Possible_keys | Key | Key_len  | Ref | Rows | The Extra explain column explains:   www.2cto.com  table  shows the data for this row is about which table   type  which is the important column, Shows what type of connection is used. The best to worst connection types are const, EQ_REG, ref, range, Indexhe, and all  possible_keys  to display the indexes that may be applied in this table. If it is empty, there is no possible index. You can select an appropriate statement from the where statement for the related domain   key  the index that is actually used. If NULL, the index is not used. In rare cases, MySQL chooses an index that is poorly optimized. In this case, use Index (indexname) can be used in the SELECT statement to force an index or to force MySQL to ignore the index with ignore index (INDEXNAME)   key_len  The length of the index to use. In the case of no loss of accuracy, the shorter the better   ref  shows which column of the index is used and, if possible, a constant   rows mysql the number of rows that must be checked to return the requested data   Extra  additional information about how MySQL resolves queries. will be discussed in the table, but the bad examples you can see here are the using temporary and usingFilesort, meaning MySQL simply cannot use the index, the result is that the retrieval will be slow   extra the meaning of the description returned by the column    www.2cto.com  Distinct  Once MySQL finds a row that matches the row, it no longer searches for   not Exists mysql optimizes the left join, and once it finds a row that matches the left join standard,   no longer searches   range checked for Each  record (index map:#)   did not find the ideal index, so for each of the row combinations from the previous table, MySQL checks which index to use, and use it to return rows from the table. This is one of the slowest connections to use the index   using filesort  when you see this, the query needs to be optimized. MySQL requires additional steps to find out how to sort the rows that are returned. It sorts all rows based on the connection type and the row pointers for all rows that store the sort key values and matching criteria   using index  column data is returned from a table that uses only the information in the index without reading the actual action. This happens when all the request columns on 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 occurs on the order by for different column sets, rather than on the group by   www.2cto.com   where used  A WHERE clause is used to restrict which rows will match the next table or are returned to the user. If you do not want to return all rows in the table, and the connection type all or index, this occurs, or the query has a problem    the interpretation of the different connection types (in order of efficiency)   system  Table has only one row: system table. This is a special case of the const connection type   const  The maximum value of one record in the table can match the 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 this value first and treats it as a constant   eq_ref  in the connection, MYsql at query time, from the previous table, the union of each record reads a record from the table, which is used when the query uses all indexes as primary or unique keys   ref  This connection type occurs only if the query uses a key that is not a unique or primary key or is part of these types (for example, using the leftmost prefix). For each row union of the previous table, all records are read from the table. This type is heavily dependent on how many records are matched against the index-the less the better   www.2cto.com   range  This connection type uses the index to return rows in a range, such as when using > or < finding things   index  This connection type makes a full scan of each record in the preceding table (better than all, because the index is generally less than the table data)   ALL  This connection type has a full scan of each previous record, which is generally bad and should be avoided as much as possible.

Usage of explain in MySQL