mysql - 更快地执行“WHERE IN(SELECT)”或“WHERE x =(SELECT)”

哪个选择语句更好？

SELECT *
FROM  aTable
WHERE aField in (
    SELECT xField
    FROM   bTable
    WHERE  yField > 5
);

要么

SELECT *
FROM  aTable
WHERE (
    SELECT yField
    FROM   bTable
    WHERE  aTable.aField = bTable.xField
) > 5;

最佳答案

它们产生了非常相似的执行计划（在我的测试表上很小，YMMV总是对实际数据进行概要分析），还有第三种替代方法可能需要考虑：

首先：

EXPLAIN SELECT * FROM aTable WHERE aField in (SELECT xField FROM bTable WHERE yField > 5);

+----+--------------------+--------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type        | table  | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
+----+--------------------+--------+-------+---------------+---------------+---------+------+------+-------------+
|  1 | PRIMARY            | aTable | ALL   | NULL          | NULL          | NULL    | NULL |    4 | Using where |
|  2 | DEPENDENT SUBQUERY | bTable | range | bTable_yField | bTable_yField | 5       | NULL |    2 | Using where |
+----+--------------------+--------+-------+---------------+---------------+---------+------+------+-------------+

The second:

EXPLAIN SELECT * FROM aTable WHERE (SELECT yField FROM bTable WHERE aTable.aField = bTable.xField) > 5;

+----+--------------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | aTable | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
|  2 | DEPENDENT SUBQUERY | bTable | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+--------------------+--------+------+---------------+------+---------+------+------+-------------+

Both result in a dependent subquery; on my example tables, the first one gets the benefit of the index (I assume bTable.yField is indexed) while the second doesn't.

You can avoid the dependent subquery and get better up-front filtering using a JOIN:

The third alternative:

EXPLAIN SELECT * FROM aTable INNER JOIN bTable On aTable.aField = bTable.xField WHERE bTable.yField > 5;

+ ---- + -------------- + -------- + ------- + ------------- -+ ---------------- + --------- + ------ + ------ + ------- ------------------------- +
| id | select_type |桌子|类型可能的钥匙|关键key_len |参考|行|额外|
+ ---- + -------------- + -------- + ------- + ------------- -+ ---------------- + --------- + ------ + ------ + ------- ------------------------- +
| 1 |简单bTable |范围| bTable_yField | bTable_yField | 5 | NULL | 2 |在哪里使用
| 1 |简单表格|全部| NULL | NULL | NULL | NULL | 4 |在哪里使用使用连接缓冲区
+ ---- + -------------- + -------- + ------- + ------------- -+ ---------------- + --------- + ------ + ------ + ------- ------------------------- +

但是，由于优化程序可能会做出不同的决策，因此您实际上必须使用架构和代表性的实际数据进行分析。

通过this excellent article在quassnoi中更多地比较了这些技术。

作为参考，这是我创建aTable和bTable（因为您未提供定义）并测试查询的方式：

mysql> CREATE TABLE aTable（aField INT，aMore VARCHAR（200））;
查询正常，受影响的0行（0.01秒）

mysql>创建表bTable（xField INT，yField INT）;
查询正常，受影响的0行（0.02秒）

mysql>插入aTable（aField，aMore）值（1，'一个'），（2，'两个'），（3，'三个'），（4，'四个'）;
查询正常，受影响的4行（0.00秒）
记录：4重复：0警告：0

mysql> INSERT INTO bTable（xField，yField）值（1、10），（2、2），（3、20），（4、4）;
查询正常，受影响的4行（0.02秒）
记录：4重复：0警告：0

mysql> CREATE INDEX bTable_yField ON bTable（yField）;
查询正常，受影响的0行（0.05秒）
记录：0重复项：0警告：0

mysql> SELECT * FROM aTable WHERE aField in（SELECT xField FROM bTable WHERE yField> 5）;
+ -------- + ------- +
| aField |更多|
+ -------- + ------- +
| 1 |一|
| 3 |三|
+ -------- + ------- +
设置2行（0.00秒）

mysql> SELECT * FROM aTable WHERE（SELECT yField from bTable WHERE aTable.aField = bTable.xField）> 5;
+ -------- + ------- +
| aField |更多|
+ -------- + ------- +
| 1 |一|
| 3 |三|
+ -------- + ------- +
设置2行（0.00秒）

关于mysql - 更快地执行“WHERE IN(SELECT)”或“WHERE x =(SELECT)”，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13050351/

bTABLE

mysql - 更快地执行“WHERE IN(SELECT)”或“WHERE x =(SELECT)”