Explain displayMySQLHow to use indexes to handle SELECT statements and connection tables. Can help choose better indexes and write more optimized query sentences.

First analyze a SQL statement to see what it appears

EXPLAINSELECTs.uid,s.username,s.name,f.email,f.mobile,f.phone,f.postalcode,f.address
FROM uchome_space ASs,uchome_spacefieldASf
WHERE 1 
AND s.groupid=0
AND s.uid=f.uid


1. id

SELECT recognition symbols. This is the Select sequence number. This is not important. The query serial number is the order of the SQL statement. Look at the SQL below

EXPLAINSELECT*FROM(SELECT* FROMuchome_space LIMIT10)ASs

The result of its execution is

can see the ID change at this time

2.select_type

Select type, it has the following values

2.1 SIMPLE it means simple selection, without union and child query

2.2 Primary’s outermost selection, in the sentences where there is a sub -query, the most external search query is Primary, the picture above is like this.

2.3 The second or the latter one in the second or later.
select * from uchome_space limit 10 union select * from uchome_space limit 10,10

will have the following results

The second statement uses Union

2.4 The second or behind select statement in the dependent union union depends on the outside query

2.5 The result of the union result union, as shown above

There are several parameters, I won’t say it here, it’s not important

3 table

Output of lines used for lines, this parameter is obvious, easy to understand

4 type

Connection type. There are multiple parameters, first from the best type to the worst type introductionImportant and difficult

There is only one line in Table

4.1 system

, which is a special column of the Const type. It will not appear in normal times. This can also be ignored

4.2 const

Tables have a maximum of matching, and Const is used to compare the Primary Key or Unique index. Because only one line of data is matched, so soon

Remember that you must use Primary Key or UNIQUE, and only two data are retrieved, it will be const. Look at the following sentence

Explain select * from `asj_admin_log` limit 1, the result is

Although only one data is searched, because the specified index is not used, it will not use Const. Continue to see the following

explain SELECT * FROM `asj_admin_log` where log_id = 111

log_id is the primary key, so const is used. So it can be understood as the optim is optimized

4.3 eq_ref

For the explanation of EQ_REF, the MySQL manual is said: “For each line combination from the front table, read one line from this table. This may be the best connection type, except for the Const type. It uses it to use it. It uses it. It uses it. All parts of an index are connected and indexed and the index is unique or Primary Key “. EQ_Ref can be used to use = columns with indexes. Look at the following sentence

The result of

explain select * from uchome_spacefield,uchome_space where uchome_spacefield.uid = uchome_space.uid

is shown in the figure below. Obviously, mysql uses EQ_REF to connect to process the uchome_space table.

The current question:

4.3.1 Why is only uchome_space one table used to 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. What needs to be explained is that the UID is primary in these two tables

4.4 Ref For each line combination from the front table, all rows with matching index values will be read from this table. If you connect to the front of only the left side of the key, or if the key is not unique or primary key (in other words, if the connection cannot choose a single line based on keywords), use REF. If the key used only matches a small amount, the type of connection 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 find 1W data data.

4.5 REF_OR_NULL This connection type is like a Ref, but the addition of mysql can be specially searched for rows containing NULL values. The optimization of the connection type is often used in solutions.

The above five situations are all ideal index usage situations

4.6 Index_merge This connection type indicates the use of index mergers and optimization methods. In this case, the key list contains the list of indexes used, and Key_len contains the longest key element of the index used.

4.7 unique_subquery 

4.8 index_subquery

4.9 Range Retish Retiture, using an index inspection party. Look at the following two sentences

explain select * from uchome_space where uid in (1,2)

explain select * from uchome_space where groupid in (1,2)

UID has indexes, GROUPID does not have indexes. As a result, the type of connection of the first statement is Range, and the second is all. I think it is a certain range, so it can be connected like BetWeen.

explain select * from uchome_space where friendnum = 17

Such statement will not use Range, it will use better connection types, which is the REF introduced above

4.10 Index This connection type is the same as ALL, except that only the index tree is scanned. This is usually faster than ALL, because index files are usually smaller than data files. In

When the query only uses a column as a single index, mysql can use the connection type.

4.11 ALL performs a complete table scan for each line combination of the previous table. If the table is the first one without labeling
const
table, this is usually not good, and usually in it
Very
Board. Usually you can add more indexes without using it
ALL
, the line energy is retrieved based on the constant value or column value in the previous table.

5 possible_keysWhat indexes will be found in this table, it is not important

6 keysMySQL’s index used, simple and important

7 key_lenmysql used index length

The

8 ref   Ref column shows which column or constant is used to choose from the table with Key.

9 rowsDisplay the number of rows of MYSQL executed inquiries, simple and important, the larger the value, the difficulty, indicating that it is not easy to use the index

10 ExtraThis column contains the detailed information of mysql solving the query.

10.1 distinct MySQL found that after the first match, stop searching for more rows for the current line combination. I have never seen this value

10.2 Not exists  

10.3 range checked for each record

No suitable indexes found

10.4 using filesort    

MySQL manual is the explanation of this “MySQL requires additional transmission to find out how to retrieve the line in order. By browse all rows according to the type of connection and save all the lines that match WHERE clauses, the pointer and lines of the lines and rows Come to complete the sort. Then the keywords are sorted and the line is retrieved in order. “At present, I don’t quite understand

10.5 Useing Index only uses the information in the index tree instead of further searching and reading the actual line to retrieve the information in the table. This is easier to understand, it means whether the index is used

Explain select * From UCSPACE_UCHOME WHERE UID = 1’s Extra for USING Index (UID has an index)

Explain select count (*) from uchome_space where group = 1 of 1 is USING WHERE (GROUPID has not been established)

10.6 using temporary

In order to solve the query, mysql needs to create a temporary table to accommodate the result. Typical cases such as group by and Order By clauses that can be listed in different cases.

Using tempoary explains that the statement needs to be optimized. For example

EXPLAIN SELECT ads.id FROM ads, city WHERE   city.city_id = 8005   AND ads.status = ‘online’   AND city.ads_id=ads.id ORDER BY ads.id desc

id  select_type  table   type    possible_keys   key      key_len  ref                     rows  filtered  Extra                          
——  ———–  ——  ——  ————–  ——-  ——-  ——————–  ——  ——–  ——————————-
     1  SIMPLE       city   ref     ads_id,city_id  city_id  4        const                   2838    100.00 Using temporary; Using filesort
     1  SIMPLE       ads     eq_ref  PRIMARY         PRIMARY  4        city.ads_id       1    100.00  Using where   

This statement uses using tempory, and the following sentence will not

EXPLAIN SELECT ads.id FROM ads, city WHERE   city.city_id = 8005   AND ads.status = ‘online’   AND city.ads_id=ads.id ORDER BYcity.ads_id desc

id  select_type  table   type    possible_keys   key      key_len  ref                     rows  filtered  Extra                      
——  ———–  ——  ——  ————–  ——-  ——-  ——————–  ——  ——–  —————————
     1  SIMPLE       city   ref     ads_id,city_id  city_id  4        const                   2838    100.00 Using where; Using filesort
     1  SIMPLE       ads    eq_ref  PRIMARY         PRIMARY  4        city.ads_id       1    100.00  Using where   

Why is this? Between them is just a Order By different, mysql table is associated withalgorithmis Nest Loop Join. It is used as a cycle basic data through the result set of the drive table, and then the data concentrated by this result is used as the filter condition to check the data in the next table, and the results are merged.Explain results, the table in the first line is the above two query statements (Important!), The driver table is City, as shown in the execution plan above!

Always use a small result set to drive the big result set

I learned a very important point today: when it is not sure which type of Join is used, let the MySQL optimizer automatically judge, we only need to write Select * from T1, T2 where t1.field = t2.field

10.7 using where

where clause is used to limit which line matches the next table or send it to the customer. Unless you ask for or checked all the lines from the table, if the Extra value is not using where and the form connection type is all or index, there may be some errors in the query. (This description is not very understandable, because many, many statements will have the where conditions, and Type is ALL or Index that can only explain that there are many retrieval data and cannot explain errors. Useing where is not very important, but it is very common)

If you want to make the query as fast as possible, you should find out the Extra value of using filesort and using temporary.

10.8 
Using sort_union(…)
, 
Using union(…)
,
Using intersect(…)

These functions illustrate how to connect the type merging index scan for index_merge

10.9 Using index for group-by

The USING Index method similar to the access table, the USING Index for Group-By means that mysql finds a column that can be used to query the group by or distinCT query without searching for the actual table for additional hard disks. In addition, use the index in the most effective way so that each group can only read a small amount of index entries.

Example explanation

Through all the value of the ROWS column output by Explain, you can get a prompt on how to connect. This should tell you roughlyMySQLHow many lines must be checked to perform inquiries. When you use max_join_size variables to limit query, you can also use this product to determine which multi -table SELECT statement.

The expansion of 1.26 in 2017. I am the omnipotent Coder’s boundary line

Looking back at this blog written a few years ago, it is really simple, but simply introduces the concept of each option after Explain. There is not much explanation for the instance, and the most important thing is that it does not point out that kind of that kind of not pointing out that kind of that is not pointed out. The options (combined with actual situation) are optimized. OK, Start Again

Reprinted Address: http://blog.csdn.net/zhuxineli/article/details/14455029