MYSQL explain detailed

Turn from: http://blog.csdn.net/zhuxineli/article/details/14455029, is to make a note, the collection.

Explain shows how MySQL uses indexes to process SELECT statements and join tables. Can help select better indexes and write more optimized query statements.

Parse an SQL statement first to see what appears

EXPLAINSELECTS.Uid,S.Username,S.Name,F.Email,F.Mobile,F.Phone,F.PostalCode,F.Address
FromUchome_spaceAsS,Uchome_spacefieldAsF
where  1 
and  s.groupid=0
and s.uid=fuid


1. Id

Select identifier. This is the Select query serial number. This is not important, the query number is the SQL statement execution order, see the following SQL

explainselect*< Span class= "Syntax_alpha Syntax_alpha_reservedword" >from ( select* fromuchome_space limit10ASs

The result of its execution is

You can see the ID change at this point.

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, which is the case

2.3 The second or later of the Union Union statement. Now executes a statement, explain
SELECT * FROM Uchome_space limit of union SELECT * FROM Uchome_space limit 10,10

will have the following results

The second statement uses the Union

2.4 Dependent the second or subsequent SELECT statement in the Union Union, depending on the outside query

2.5 Union result Union results, as shown above

There are a few more parameters, not here, not important.

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

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 the primary key or unique, and only retrieve two of the data in the case will be const, see the following statement

Explain SELECT * from ' asj_admin_log ' limit 1, the result is

Although only one piece of data is searched, the const is not used because the specified index is not used. Keep looking at this.

Explain SELECT * from ' asj_admin_log ' where log_id = 111

LOG_ID is a primary key, so a const is used. So it can be understood that the const is the most optimized

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 =. Look at the following statement

Explain select * from uchome_spacefield,uchome_space where uchome_spacefield.uid = Uchome_space.uid

The resulting result is shown. Obviously, MySQL uses a eq_ref join to process the Uchome_space table.

Current questions:

4.3.1 Why is only uchome_space a table used Eq_ref, and if the SQL statement becomes

Explain select * from Uchome_space,uchome_spacefield where uchome_space.uid = Uchome_spacefield.uid

The result is still the same, it should be stated that the UID is primary in both tables.

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.

Look at the following statement explain SELECT * from uchome_space where uchome_space.friendnum = 0, the result is as follows, this statement can search 1w data

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

Explain select * from Uchome_space where UID in (.)

Explain select * from Uchome_space where GroupID in

The UID has an index, the GroupID has no index, the result is the join type of the first statement is range, and the second is all. Think it's a certain range, so say like between can also this kind of connection, very obviously

Explain select * from uchome_space where friendnum = 17

Such a statement would not use range, it would use a better join type than the ref described above

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 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 Displays the number of rows that MySQL executes the query, simple and important, and the larger the number, 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

Explain select * from ucspace_uchome where uid = 1 of extra is using index (UID is indexed)

Explain select COUNT (*) from Uchome_space where Groupid=1 extra is the using where (GroupID not indexed)

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

EXPLAIN SELECT ads.id from ads, city WHERE city.city_id = 8005 and ads.status = ' online ' and City.ads_id=ad S.id ORDER by ads.id desc

ID select_type table Type possible_keys key Key_len ref rows filtered Extra
------  -----------  ------  ------  --------------  -------  -------  --------------------  ------  --------  ----------- --------------------
1 simple CityRef ads_id,city_id city_id 4 const 2838 100.00Using Temporary; Using Filesort
1 Simple ads Eq_ref PRIMARY PRIMARY 4 city.ads_id 1 100.00 Using where

This statement uses the using temporary, and the following statement does not

EXPLAIN SELECT ads.id from ads, city WHERE city.city_id = 8005 and ads.status = ' online ' and City.ads_id=ad S.id ORDER by city.ads_id desc

ID select_type table Type possible_keys key Key_len ref rows filtered Extra
------  -----------  ------  ------  --------------  -------  -------  --------------------  ------  --------  ----------- ----------------
1 simple CityRef ads_id,city_id city_id 4 const 2838 100.00Using where; Using Filesort
1 Simple ads Eq_ref PRIMARY PRIMARY 4 city.ads_id 1 100.00 Using where

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.

Example explanation

By multiplying all the values of the rows column of the explain output, you can get a hint about how a join IS. This should roughly tell you how many rows MySQL must check to execute the query. This product is also used to determine which multi-table SELECT statement executes when you use the max_join_size variable to limit the query.

MYSQL explain detailed