Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the difference between exists and in in mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

The performance of in and exists in MySQL and their respective processes for retrieving data, take the following statement as an example:

Select * from user a where name='liuwenhe' and exists (select stuid from department b where depname='yunwei' and a.stuid = b.stuid)

Select * from user where name='liuwenhe' and stuid in (select stuid from department where depname='yunwei')

The process of retrieving data by MySQL exists and in:

1. First of all, let's talk about the exists retrieval process.

Notice where A represents the result set of (user a where name='liuwenhe') and B represents the result set of (department b where depname='yunwei'):

Exists uses loop to query exterior An one by one, and each query will verify the conditional statement of exists (that is, the statement in parentheses after exists). When the conditional statement in exists can return the record row (as long as it can return the result, no matter what you query!), the condition is true, it will return the record of A to the current loop, otherwise, if the conditional statement in exists cannot return record row. If the condition is false, the record of the current loop to An is discarded. Note: the condition of exists is like a bool condition. If the result set can be returned, it is true, and if it cannot be returned, it is false.

The retrieval process for exists can be summarized in the following script:

For ($I = 0; $I < count (A); $iTunes +) {

$a = get_record (A, $I); # get records item by item from table A

If (B.id = $a [id]) # returns true if the subcondition is true

$result [] = $a;}

Return $result

For example:

Select * from user where exists (select stuid from department where depname='yunwei')

The records of the user table are taken out one by one. Because the select stuid from department where depname='yunwei' in the exists condition can always return record rows, then all records of the user table will be added to the result set, so it is the same as select * from user;.

For example:

Select * from user where exists (select stuid from department where depname='yunwei+')

Not exists is the opposite of exists, that is, when the exists condition has a result set returned, the record of loop will be discarded, otherwise the record of loop will be added to the result set.

In general, if the result set of the user table has n records, then the exists query takes out the n records one by one, and then determines the n-pass exists condition.

two。 About the retrieval process of in subquery:

MySQL first stores the results of the subquery in the temporary table T (either in memory or on disk) to ensure that the subquery is executed only once. The table does not record duplicate data and traverses the data with a hash index, and then traverses the appearance through the data of the T table. Through the association relationship, the required data of the appearance is obtained. The in query is equivalent to the superposition of multiple or conditions, which is easy to understand, such as the following query.

Select * from user where userId in (1,2,3)

Equivalent to

Select * from user where userId = 1 or userId = 2 or userId = 3

Not in is the opposite of in, as follows

Select * from user where userId not in (1,2,3)

Equivalent to

Select * from user where userId! = 1 and userId! = 2 and userId! = 3

Generally speaking, the in query is to find out all the records of the subquery conditions, assuming that the result set is B, with a total of m records.

Then the result set of the subquery condition is decomposed into m, and then the m-time main query is performed. It is worth mentioning that the subcondition of the in query must return only one field, for example

Select * from user where userId in (select id from B)

And can't be

Select * from user where userId in (select id, age from B)

Exists does not have this restriction.

Performance of exists and in

Select * from user a where name='liuwenhe' and exists (select stuid from department b where depname='yunwei' and a.stuid = b.stuid)

Select * from user where name='liuwenhe' and stuid in (select stuid from department where depname='yunwei')

1) according to the process of retrieving data described earlier, we can know that for exists in the above two sql, it is necessary to traverse all data rows N of user table name='liuwenhe' and judge the exists condition N times; and if the stuid of the department table has an index, the index on the join relationship (that is, stuid) can be used in the subquery, so the exists method is suitable for the situation where the result set of the user table is small and the result set of the subquery is large. The subquery can use the index on the correlation series, so it is efficient, so the large inner table is suitable to use exists

2) not exists is similar to the traversal mode of exists, which is also the appearance of loop, and then judges the exists condition.

3) in makes a hash connection between the outer user result set and the inner table department result set (it should be said to be similar to hash join, because MySQL does not support hash join). First query the inner table department result set, and then match the inner table result set with the outer result set. You can use a relational index (that is, the index on the stuid column) for the exterior, while most of the inner table result set needs to be queried, that is, how big the result set of department where depname='yunwei' is. Have to traverse all the D, inevitable, so the appearance of large use of in, can speed up the efficiency. The main query can use the index on the related relation series, so it is efficient, so the large external result set is suitable to use in.

3) if you use not in, like in, the result set of the inner table needs to be all scanned. Because of not in, the outer result set also needs token scanning, and indexes on related columns cannot be used (this! = range query cannot use any index). Inefficient, you can consider using not exists or A left join B on A.id=B.id where B.id is null for optimization.

Summary:

Exists first queries the outer result set loop loop and then queries the inner table result set. It has always been believed that exists is more efficient than in statements, but this is actually not accurate. This is to distinguish between the environment. If the two tables of the query are of the same size, there is little difference between using in and exists. If one of the two tables is small and the other is large, the result set of the sub-query table is large using exists, and if the external result set is large, it is suitable to use in. Then the statement in the network that the outer and inner tables are large is not accurate, it should be the size of the outer result set and the inner table result set, as for the result set has been explained before.

After reading the above, do you have any further understanding of the difference between exists and in in mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.