MySQL explain detailed

1, the data type is implicitly converted with the view whether the index is used

2,select_type

Select Type, which has the following values

2.1 Simple It represents a straightforward select with no union and subquery

2.2 Primary The outermost select, in the statement with the subquery, the outermost select query is primary,

3 table

The table used for the output line, this parameter is obvious, easy to understand

4 type

The connection type. There are several parameters that introduce important and difficult first from best type to worst type

4.1 System

Table has only one row, this is a const type of special column, usually do not appear, this can also be ignored

4.2 Const [soon] [const is the most optimized]

The table has a maximum of one matching row, and the const is used to compare primary key or unique index. Because it matches only one row of data, it quickly

Remember that you must use primary key or unique, and only retrieve two of the data in the case will be const, please compare the following two statement differences

4.3 eq_ref

For Eq_ref's explanation, the MySQL manual says, "for each row combination from the previous table, read one row from the table." This may be the best type of join, except for the const type. It is used in all parts of an index to be joined and the index is unique or primary KEY ". Eq_ref can be used to compare indexed columns with =.

4.4 Ref for each row combination from the preceding table, all rows with matching index values are read from this table. If the join uses only the leftmost prefix of the key, or if the key is not unique or primary key (in other words, if the join cannot select a single row based on the keyword), ref is used. If you use a key that matches only a few rows, the join type is good.

4.5 Ref_or_null The join type is like ref, but adds MySQL to search for rows that contain null values. The optimization of the join type is often used in the resolution subquery.

The above five scenarios are ideal for index usage

4.6 Index_merge The Join type represents the use of the index merge optimization method. In this case, the key column contains the list of indexes used, and Key_len contains the longest key element for the index used.

4.7 Unique_subquery

4.8 Index_subquery

4.9 range is retrieved within a given range, using an index to examine the row. Look at the following two statements

4.10 Index The Join type is the same as all except that only the index tree is scanned. This is usually faster than all, because the index file is usually smaller than the data file. (That is, although all and index are read-only, index is read from the index and all is read from the hard disk)

When a query uses only columns that are part of a single index, MySQL can use that join type.

4.11 All for a complete table scan for each row combination from the previous table. If the table is the first table that is not marked const, this is usually not good and is usually poor in its case. You can usually add more indexes instead of all, so that the rows can be retrieved based on the constant values or column values in the preceding table.

5 Possible_keys indicates which index to use to find rows in the table, less important

6 keys Important

MySQL uses an index that is simple and important

7 Key_len The length of the index used by MySQL

8 ref ref column shows which column or constant is used together with key to select rows from the table.

9 Rows Important

Displays the number of rows that MySQL executes the query, simple and important, and the larger the number, the worse, the better the index

Extra This column contains the details of the MySQL resolution query.

10.1 Distinct MySQL finds the 1th matching row, stops searching for more rows for the current row combination. I've never seen this value before.

10.2 NOT EXISTS

10.3 Range checked for each record

No suitable index found

10.4 Using Filesort

The MySQL manual explains the "MySQL needs an extra pass at a time to find out how to retrieve rows in sorted order." The sort is done by browsing all rows based on the join type and holding a pointer to the sort key and row for all rows that match the WHERE clause. The keywords are then sorted and the rows are retrieved in sorted order. "It's not quite clear at the moment

The 10.5 using index only uses information from the index tree and does not require further searching to read the actual rows to retrieve the information in the table. This is easier to understand, that is, whether the index is used

10.6 Using Temporary

To resolve the query, MySQL needs to create a temporary table to accommodate the results. A typical case is when a query contains a group by and an ORDER BY clause that can be listed in different cases.

The use temporary appears to illustrate that the statement needs to be optimized, for instance

What is this for? They're just an order by different. The MySQL Table Association algorithm is the Nest loop Join, which uses the result set of the driver table as the loop base data, then queries the data in the next table with the data in the result set as a filter, and then merges the results. EXPLAIN results, the first row appears in the table is the driver table (important!) above two query statements, the driver table is city, as shown in the above execution plan!

The driver table can be sorted directly, and the non-driver table (the field sort) needs to sort the merged result (temporary table) of the circular query (important!) Therefore, the order by ads.id Desc, the use of the temporary is the first! Driver table Definition wwh999 in 2006, when making a multi-table connection query, [driver table] is defined as:
1) When a join condition is specified, the table that satisfies the query condition with a low number of record rows is [driver table];
2) when no join condition is specified, the table with a low number of rows is [drive table] (important!). always drive large result sets with small result sets

Today I learned a very important point: when unsure which type of join to use, let the MySQL optimizer automatically judge, we only need to write select * from t1,t2 where T1.field = T2.field

10.7 using where

The WHERE clause is used to restrict which row matches the next table or send to the customer. Unless you specifically request or check all rows from a table, the query may have some errors if the extra value is not a using where and the table join type is all or index. (This explanation is not very understanding, because a lot of many statements will have a where condition, and the type of all or index can only describe the number of retrieved data, does not explain the error, useing where is not very important, but very common)

If you want to make the query as fast as possible, you should find the extra value of the using Filesort and using temporary.

10.8 using Sort_union (...), using Union (...), using intersect (...)

These functions describe how to merge index scans for Index_merge join types

10.9 Using Index for group-by

Similar to accessing a table using the index method, using index for group-by means that MySQL has found an index that can be used to query all the columns of a group by or distinct query, and not to search the hard disk for access to the actual table. Also, indexes are used in the most efficient way so that only a small number of index entries are read for each group.

Other options:

1. Using Slow query analysis

In the My.ini:

Long_query_time=1

Log-slow-queries=d:\mysql5\logs\mysqlslow.log

Record more than 1 seconds in the slow query log

You can use Mysqlsla to analyze it. can also be in the mysqlreport, like

DMS separately analyzes the percentage of select, Update,insert,delete,replace, etc.

Transaction configuration entries for 2.MYSQL

Innodb_flush_log_at_trx_commit=1

Indicates that the transaction log is written to disk as soon as the transaction commits, and the data and indexes are also updated.

Innodb_flush_log_at_trx_commit=0

The transaction log is not written to disk immediately when the transaction is committed, written every 1 seconds

innodb_flush_log_at_trx_commit=2

When a transaction commits, it is immediately written to the disk file (this is written to the kernel buffer, but not immediately to the disk, but is refreshed every 1 seconds to the drive, updating the data and index

MySQL explain detailed