Shulou(Shulou.com)06/01 Report--

Explain shows how mysql uses indexes to process select statements and join tables. It can help select better indexes and write more optimized query statements.

To use the method, you can add explain before the select statement, such as:

Explain select * from statuses_status where id=11

Interpretation of explain column

Table: shows which table the data in this row is about

Type: this is an important column that shows what type of connection is used. The best to worst connection types are const, eq_reg, ref, range, indexhe, and all

Possible_keys: displays the indexes that may be applied to this table. If empty, there is no possible index. You can select an appropriate statement from the where statement for the relevant domain

Key: the index actually used. If null, the index is not used. In rare cases, mysql chooses indexes that are not sufficiently 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

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 and, if possible, a constant

The number of rows that rows:mysql believes must be checked to return the request data

Extra: additional information about how mysql parses queries. It will be discussed in Table 4.3.But the bad examples you can see here are using temporary and using filesort, meaning that mysql cannot use indexes at all, and the result is that retrieval will be slow

The meaning of the description returned by the extra column

Distinct: once mysql finds a row that matches the row union, it no longer searches

Not exists: mysql optimizes left join so that once it finds rows that match the left join standard, it no longer searches

Range checked for each record (index map:#): no ideal index was found, so for each combination of rows 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 to use an index

Using filesort: when you see this, the query needs to be optimized. Mysql needs to take additional steps to discover how to sort the returned rows. It sorts all rows according to the connection type and the row pointer to all rows that store the sort key values and matching criteria.

Using index: column data is returned from a table that only uses the information in the index and does not read the actual action, which occurs when all request columns on 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, which usually occurs on the order by of different sets of columns, not on the group by

Where used uses the where clause to restrict which rows will match the next table or be returned to the user. This can happen if you do not want to return all the rows in the table and the join type is all or index, or the query has a problem with the interpretation of different join types (sorted in order of efficiency)

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

Const: the maximum value of a record in a table can match this query (the index can be a primary key or unique index). Because there is only one line, this value is actually a constant, because mysql reads the value first and then treats it as a constant.

Eq_ref: in a join, when mysql queries, the union of each record from the previous table reads a record from the table, which is used when the query uses the index as the primary key or all of the unique key.

Ref: this join type occurs only when the query uses keys that are not unique or primary keys or parts of these types (for example, using the leftmost prefix). For each row join of the previous table, all records are read out from the table. This type depends heavily on how many records are matched by the index-the fewer the better

Range: this connection type uses an index to return rows in a range, such as > or 1\ G

Ref: non-unique index access (only normal indexes)

Create table a (a_id int not null, key (a_id)); insert into a values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); mysql > explain select * from a where a_id=1\ G

Eq_ref: use a unique index to find (primary key or unique index)

Const: constant query

In the whole query process, the table will have at most one matching row. For example, the primary key id=1 must have only one row. You only need to read the table data once to get the desired results, and the table data is read when the execution plan is decomposed.

When the result is not one, it becomes another type, such as index or range.

System: system query

Null: the result has been obtained during the optimization process, not in accessing the table or index

Possible_keys: indexes that may be used

Key: the index actually used

Key_line: maximum possible length of index field

Ref:

Indicates how to find the index selected by the key column. The common values are const, func, NULL, and specific field names. When key is listed as NULL, that is, when the index is not used, this value also corresponds to NULL.

Rows: estimate the number of rows to be scanned

Extra: displays information other than the above information

Using index

This query uses an overlay index (Covering Index), which returns results through the index without having to access the table.

If "Using index" is not displayed, the table data has been read.

Using where

Indicates that the MySQL server receives a row from the storage engine before performing a "post-filter" (Post-filter). The so-called "post-filtering" means to read the whole row of data first, and then check whether the row meets the conditions of the where sentence, stay if it meets, and discard it if it doesn't. Because the check is done after reading the row, it is called "post-filtering".

Using temporary

Use to temporary table

Create tables and insert data:

Create table a (a_id int, b_id int); insert into a values (1), (1), (2), (2), (3); mysql > explain select distinct a_id from a\ G

Extra: Using temporary

MySQL uses temporary tables to implement distinct operations.

Using filesort

If the sort required by the query is the same as that of the index used, because the index is sorted, the read results are returned in the order of the index, otherwise, after the results are obtained, you need to sort the results in the order needed by the query, then Using filesort occurs.

Select * from an order by id

Filesort occurs when you order by a column that does not have an index

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.