sql - 请帮助我理解为什么子查询会影响主查询对索引的使用

Question

这是没有子查询的主查询:

SELECT * FROM 
    mytable AS idx
WHERE 
    idx.ID IN (1,2,3) 
    AND idx.P1 = 'galleries';

该表的索引为id_path (ID,P1)

此时一切都很好，使用了索引，检查了 3 行并返回了 2 行。如果没有索引，则必须检查 9 行。

现在，如果我用返回完全相同的 ID 集的子查询替换 ID 列表，主查询仍然返回正确的行，但它停止使用索引并检查 9 行，就好像索引从未存在过一样。

SELECT * FROM 
    mytable AS idx
WHERE 
    idx.ID IN (SELECT idxrev.ID FROM mytable AS idxrev WHERE idxrev.ID IN (1,2,3)) 
    AND idx.P1 = 'galleries';

我的问题是，为什么会发生这种情况，我该怎么做才能使主查询像以前一样使用索引。我尝试添加 USE INDEX (id_path) 但这只会让情况变得更糟，进行整个表扫描。

Answer 1

SELECT  *
FROM    mytable AS idx
WHERE   idx.ID IN
        (
        SELECT  idxrev.ID
        FROM    mytable AS idxrev
        WHERE   idxrev.ID IN (1,2,3)
        ) 
        AND idx.P1 = 'galleries'

MySQL 进行半连接的唯一方法是嵌套循环。

它需要获取 idx 的每一行，并根据 idxrev 检查它(使用索引)。

当然，在这种情况下更好的方法是HASH SEMI JOIN或者只是将查询减少到原始查询，但是MySQL无法做到这一点。

要使查询使用索引，只需恢复到原始查询:)