mysql - (MYSQL)如果在where中另一个查询，那么该查询(另一个查询)操作每一行？

Question

我的查询示例是:

select * from table1 
 where table1_seq = (select table2_seq from table2 where ~)

那么子查询是否操作table1的每一行数据？？

或

只运行一次？

IF一次，那么上层查询和使用join查询哪个性能更好？

Answer 1

对于您的查询，仅当子查询返回 1 并且仅返回 1 个结果时，它才不会返回错误。

您应该对查询进行解释:

Explain select * from table1 
where table1_seq = (select table2_seq from table2 where id2=X)

将返回类似的内容:

id  |   select_type |   table   |   partitions  |   type    |   possible_keys   |   key     |   key_len |   ref     |   rows    |   filtered    |   Extra
==========================================================================================================================================================
1   |   PRIMARY     |   Table1  |   NULL        |   const   |   PRIMARY         |   PRIMARY |   4       |   const   |   1       |   100.00      |   NULL
2   |   SUBQUERY    |   Table2  |   NULL        |   const   |   PRIMARY         |   PRIMARY |   4       |   const   |   1       |   100.00      |   Using index 

Inner Join 另一方面也会执行 2 个查询:

EXPLAIN select table1.*
from table1 
INNER JOIN table2 ON table1.table1_seq = table2.table2_seq
WHERE  table2.id2=X

结果:

id  |   select_type |   table   |   partitions  |   type    |   possible_keys   |   key     |   key_len |   ref     |   rows    |   filtered    |   Extra
==========================================================================================================================================================
1   |   SIMPLE      |   Table1  |   NULL        |   const   |   PRIMARY         |   PRIMARY |   4       |   const   |   1       |   100.00      |   NULL
2   |   SIMPLE      |   Table2  |   NULL        |   const   |   PRIMARY         |   PRIMARY |   4       |   const   |   1       |   100.00      |   NULL

所以这不是基准问题，而是你想要做什么的问题。如果您只想从另一个表中选择 1 个值，您实际上可以执行您编写的查询。