代码之家 › 专栏 › 技术社区 › fuenfundachtzig

不支持应独立的子查询。为什么?

performance mysql sql

fuenfundachtzig · 技术社区 · 16 年前

我有一张桌子 files reades 具有对这些文件的读取访问权限。在桌子上 读 file_id 其中是指中的相应列 文件夹 .

现在,我想列出所有尚未访问的文件,并尝试以下操作:

SELECT * FROM files WHERE file_id NOT IN (SELECT file_id FROM reades)

这太慢了。原因是mySQL认为子查询依赖于查询:

+----+--------------------+--------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table  | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+--------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | files  | ALL  | NULL          | NULL | NULL    | NULL | 1053 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | reades | ALL  | NULL          | NULL | NULL    | NULL | 3242 |   100.00 | Using where |
+----+--------------------+--------+------+---------------+------+---------+------+------+----------+-------------+

但是为什么呢?子查询是完全独立的,或多或少只是为了返回一个ID列表。

文件标识 读

6 回复 | 直到 16 年前

Andomar 16 年前

尝试用联接替换子查询:

SELECT * 
FROM files f
LEFT OUTER JOIN reades r on r.file_id = f.file_id
WHERE r.file_id IS NULL

这里有一个链接到 article about this problem

longneck 16 年前

我以前见过这个。这是mysql中的一个bug。试试这个:

SELECT * FROM files WHERE file_id NOT IN (SELECT * FROM (SELECT file_id FROM reades))

http://bugs.mysql.com/bug.php?id=25926

chaos 16 年前

尝试:

SELECT * FROM files WHERE file_id NOT IN (SELECT reades.file_id FROM reades)

也就是说:如果它是独立的,可能是因为在什么方面存在歧义 file_id

如果这不起作用,只需执行以下操作:

SELECT files.*
FROM files
LEFT JOIN reades
USING (file_id)
WHERE reades.file_id IS NULL

CaffGeek 16 年前

MySQL支持的存在方式是否与MSSQL相同? 如果是这样,您可以将查询重写为

使用IN效率极低,因为它为父查询中的每一行运行子查询。

fuenfundachtzig 16 年前

this page

  SELECT * FROM files AS f 
    INNER JOIN (SELECT DISTINCT file_id FROM reades) AS r 
    ON f.file_id = r.file_id

这解决了问题,但仍然不能回答我的问题:)

编辑:如果我正确解释解释输出,这会很快,因为解释程序会生成一个临时索引:

+----+-------------+------------+--------+---------------+---------+---------+-----------+------+--------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref       | rows | Extra                    |
+----+-------------+------------+--------+---------------+---------+---------+-----------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL      |  843 |                          |
|  1 | PRIMARY     | f          | eq_ref | PRIMARY       | PRIMARY | 4       | r.file_id |    1 |                          |
|  2 | DERIVED     | reades     | range  | NULL          | file_id | 5       | NULL      |  811 | Using index for group-by |
+----+-------------+------------+--------+---------------+---------+---------+-----------+------+--------------------------+

Øystein Grøvlen 12 年前

从文件中选择*

如您所见,子查询实际上是依赖的。

MySQL 5.6可以选择具体化子查询。也就是说,首先,运行内部查询并将结果存储在临时表中(删除重复项)。然后,它可以在外部表(即文件)和临时表之间使用类似联接的操作来查找不匹配的行。如果reades.file_id未编入索引,则这种执行查询的方式可能会更为理想。

然而,如果reades.file_id被索引,传统的IN-to-EXISTS执行策略实际上相当有效。在这种情况下,我不希望像其他答案中所建议的那样,将查询转换为联接会有任何显著的性能改进。MySQL 5.6优化器根据成本在物化和执行之间做出选择。