MySQL query performance analysis tool-explain keyword analysis

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

The method of using explain is very simple, just add the explain keyword in front of the select query statement. An example is given below:

The meaning of the echoed data is given below:
(1) id: SELECT identifier, which is the SELECT query sequence number.

(2)select_type: select type, which has the following values ​​
1) simple It represents a simple select, without union and subquery

 2）primary 最外面的select,在有子查询的语句中，最外面的select查询就是primary。

 3）union union语句的第二个或者说是后面那一个.现执行一条语句。

explain select * from uchome_space limit 10 union select * from uchome_space limit 10,10

(4) table: Displays which table this row of data is about

(5 ) type: This is the important column, showing what type is used for the connection. The connection types from best to worst are const, eq_reg, ref, range, index and ALL

(6) possible_key s: 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 statement for the relevant domain

(7) key: The actual index used. If NULL, no index is used. Rarely, MYSQL will select an index that is under-optimized. In this case, you can use USE INDEX (indexname) in the SELECT statement to force the use of an index or IGNORE INDEX (indexname) to force MYSQL to ignore the index

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

(9) ref: Displays which column of the index is used, if possible, is a constant

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

(11)Extra: Extra information about how MYSQL parses the query. Bad examples are Using temporary and Using filesort, which means that MYSQL cannot use the index at all, and the result is that the retrieval will be very slow

1）Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

2）Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

3）Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。
这是使用索引的最慢的连接之一

4）Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。
它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

5）Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

6）Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

The above is the detailed content of MySQL query performance analysis tool-explain keyword analysis. For more information, please follow other related articles on the PHP Chinese website!