MYSQL explain detailed

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

Although this article I write very long, but looks really not sleepy ah, really is dry!!!!

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 sgroupid=0
AND  s.< Span class= "Syntax_alpha syntax_alpha_identifier" >uid=f. UID


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 *from  ( select*  from uchome_space  limit  10 ) AS  s

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.idORDER 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.idORDER bycity.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.

2017 1.26 Expansion I'm the coder of the Almighty.

Looking back a few years ago wrote this blog, really is very simple, but simply introduced the concept of each option after the explain, for instance not too much explanation, and the most important is not to point out the option in that case (combined with the actual situation) is the most optimized, Ok,start again

Obviously, the most important of all explain results is the number of Type/key/rows/extra these 4 fields, then I'll focus on the meaning of these four fields and how to optimize

There are two tables, a project table (project), a message list (T_message), and users can perform popular operations on different projects.

One of the most basic table operations available,

EXPLAIN SELECT * from Project as P joins Jmw_message.t_message as T on p.id = t.target_id
The result is this.

This is the easiest to understand, because this is simply a table query, without any conditions, and in real cases, this SQL will not appear. The results show that MySQL has a full table scan of the T_message table and uses EQ_REF for the project table, which conforms to the definition of what the MySQL will use to Eq_ref, which is an ideal connection type.

Let's discuss an example that we will encounter in the actual situation, and we'll take the first 100 data from the table,
EXPLAIN SELECT * from Project as P joins Jmw_message.t_message as T on p.id = t.target_id LIMIT 100

It can be found that, in addition to the number of rows affected slightly more (can be ignored, even can be understood as no different), all the other parameters are the same, that is, in this case, search all the data and search 100 data is the same time, why so? No, it shouldn't be!!

It is important to note that the above two statements explain get the same result because their index usage policy is the same, that is, the index is not well used (because there is no where and the order by statement) but their final time is different, Obviously transmitting 100 data is certainly slower than transmitting 1 data. Therefore, the final time consuming will be the largest consumption of sending data (viewed with show profile)

What are the most likely problems to be encountered in the actual situation?

1 search criteria based on project ID (i.e. using the Where condition)

2 Sort by time or ID (i.e. using the order by condition)

3 on the basis of the above two

Let's start with the chestnuts.

"1" Search the latest 100 message

"2" Search out a project under the latest 10 message

"3" Search for a project how many messages per day in the last one months

"4" Search out how many messages per day in the last one months

"5" Search for a user today number of messages

"6" Search today how many new messages

Next we start eating chestnuts

"1" Search the latest 100 message

EXPLAIN SELECT * from Project as P JOIN jmw_message.t_message as T in p.id = t.target_id ORDER by t.id DESC LIMIT 100;
EXPLAIN SELECT * from Project as P JOIN jmw_message.t_message as T in p.id = t.target_id ORDER by p.id DESC LIMIT 100

The following two statements all achieve the effect, but the index usage is completely different. The first statement is more optimized than the second one,

As you can see, the type value of the first statement is index, which affects only 100 rows, which means that the index is used very appropriately. It is so-called, Hattush, when you have a problem in a place, it is inevitable that other places also have problems, because not using a reasonable index--causing the full table scan--affecting the result set too large--resulting in the use of using temporary Filesort (This is also very important). The two statements are obviously only a little bit different from the order by condition. To be honest, I don't quite understand why this happens because the two conditions table is the primary key of the two tables, all have the primary key index, the only reasonable explanation may be because this time after the table T_ The message is the main table (because he is the message table, everything is based on him), and the order by sort of course should be based on the main table of the primary key beat to use the index, it seems a bit farfetched, but there seems to be no big problem with understanding

The following highlights the using temporary and using Filesort.

Using temporary official explanation: In order 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. "" is obviously a one-time retrieval of the result set through the where condition is too large, memory can not be placed, only through the home temporary table to assist processing

Using Filesort official explanation: "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 "

What I understand here is that the field is not used for order by fields, so a using Filesort is used. The two problems at the same time there is a great possibility Ah!!!

"2" Search out a project under the latest 10 message

EXPLAIN SELECT * from t_message WHERE target_id = 770 ORDER by ID DESC LIMIT 10;
EXPLAIN SELECT * from t_message WHERE target_id = 770 ORDER by Publish_time DESC LIMIT 10

The execution search results for the above two SELECT statements are the same, but the explain analysis results are different, just because the order by condition differs

MYSQL explain detailed