MySQL IN子句是否多次执行子查询? [英] Does the MySQL IN clause execute the subquery multiple times?

问题描述

在MySQL中提供此SQL查询:

Given this SQL query in MySQL:

SELECT * FROM tableA WHERE tableA.id IN (SELECT id FROM tableB);

MySQL是否对tableA中的每一行多次执行子查询SELECT id FROM tableB?

Does MySQL execute the subquery SELECT id FROM tableB multiple times for each row in tableA?

有没有一种方法可以使sql更快地运行而无需使用变量或存储过程?

Is there a way to make sql go faster without using variables or store procedures?

为什么这通常比使用LEFT JOIN慢?

Why is this often slower than using LEFT JOIN?

推荐答案

您的假设是错误的；子查询将仅执行一次.它比连接慢的原因是因为IN不能利用索引.每次评估WHERE子句时，它必须扫描一次其参数，即tableA中的每一行扫描一次.您可以简单地通过用联接替换IN来优化查询，而无需使用变量或存储过程，从而:

Your assumption is false; the subquery will be executed only once. The reason why it's slower than a join is because IN can't take advantage of indexes; it has to scan its arguments once for each time the WHERE clause is evaluated, that is, once per row in tableA. You can optimize the query, without using variables or stored procedures, simply by replacing the IN with a join, thus:

SELECT tableA.field1, tableA.field2, [...]
FROM tableA 
  INNER JOIN tableB ON tableA.id = tableB.id

除非您不介意从两个表中取回每个字段，否则您确实需要在SELECT子句中枚举所需的字段.例如，tableA.*会引发语法错误.

Unless you don't mind getting back every field from both tables, you do need to enumerate the fields you want in the SELECT clause; tableA.*, for example, will elicit a syntax error.

这篇关于MySQL IN子句是否多次执行子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！