MySQL Database Performance Tuning three: Explain analysis slow query

Explain shows how MySQL uses indexes to process SELECT statements and join tables. Can help select better indexes and write more optimized query statements. Using the method, add explain to the SELECT statement.

I. Explanation of the Explain column

1, id:select identifier. This is the Select query serial number. This is not important, the query sequence is the SQL statement execution order;

2, select Type, it 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

2.3 The second of the Union Union statement, or the latter one.

3, table: Shows the data of this line is about which table

4,type: Important, connection type. The best to worst connection types are described below:

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;

The 4.2 const table has a maximum of one matching row, and the const is used to compare primary key or unique index. Because only one row of data is matched, it is important to remember that the primary key or unique must be used, and that only two of the data is retrieved as const.

Example:

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 reads a row from the table for each row combination from the preceding 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: Displays the index that may be applied to this table. If it is empty, there is no possible index. You can select an appropriate statement from the where statement for the related domain. It is not important to indicate which index to use to find rows in the table

6, Key: The actual index used. If NULL, the index is not used. In rare cases, MySQL chooses an index that is poorly optimized. In this case, use Index (indexname) can be used in the SELECT statement to force an index or use ignore index (indexname) to force MySQL to ignore the index

7. Key_len: The length of the index used. The shorter the length the better, without loss of accuracy

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

9. Rows: Displays the number of rows executing the query, simple and important, the larger the value the better, the better the index is not used

10. Extra: Additional information on how MySQL resolves queries.

10.1 Distinct once MySQL finds a row that matches the row, it no longer searches.

10.2 Not exists MySQL optimizes the left join, and once it finds a row that matches the left join standard, it no longer searches for

10.3 Range checked for each record does not find an appropriate index, so for every combination of rows from the preceding table, MySQL checks which index is used and uses it to return rows from the table. This is one of the slowest connections to use the index

10.4 using Filesort when you see this, the query needs to be optimized. MySQL requires additional steps to find out how to sort the rows that are returned. It sorts all rows based on the connection type and the row pointers for all rows that store the sort key values and matching criteria.

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 when you see this, the query needs to be optimized. Here, MySQL needs to create a temporary table to store the results, which usually occurs on an order by on a different set of columns, rather than on the group by where used uses the WHERE clause to restrict which rows will match the next table or return to the user. If you do not want to return all rows in the table, and the connection type all or index, this occurs, or the query has a problem different connection types of interpretation (in order of efficiency)

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! Definition of driver table:When a multi-table join query is made, [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

The 10.7 using where 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 is similar to accessing a table using the index method, and 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. Instead of additional search hard disk access 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.

MySQL Database Performance Tuning three: Explain analysis slow query