php - 在 MySQL 中使用 SELECT 更快地搜索 IN 语句

Question

我目前正在为我的应用程序做一些查询，我需要获取离我当前位置最近的商店，为此，我首先需要获取所有具有相同名称的项目，然后获取它的信息并减少它询问。现在我为此使用了 IN 语句，但由于要搜索的项目也基于列表，因此我需要为此使用另一个选择，这是我目前的代码:

select *
from product p,
store s,
branches b
where 1 = 1
and b.idproduct = p.idproduct
and p.store = s.idstore
and common_name IN(SELECT p.common_name
FROM shopping_list_content s, product p
WHERE 1 =1
AND s.iditem = p.idproduct
AND s.idlist =$listid)

现在它按我想要的方式工作，但我希望它比这更快地执行查询。现在，此查询需要 3 秒以上才能运行得比这快。如果它少于一秒，那就更好了。我可以为此使用任何其他选项吗？

Answer 1

MySQL 难以优化子查询，当您编写如下内容时:

SELECT  *
FROM    T
WHERE   T.ID (SELECT ID FROM T2);

有时改写为

SELECT  *
FROM    T
WHERE   EXISTS
        (   SELECT  1
            FROM    T2
            WHERE   T.ID = T2.ID
        );

子查询然后在 T 中每行执行一次，而如果你写:

SELECT  T.*
FROM    T
        INNER JOIN
        (   SELECT  DISTINCT ID
            FROM    T2
        ) T2
            ON T2.ID = T.ID;

您的结果集将是相同的，但 MySQL 将首先用子查询的结果填充内存表并将其哈希到 T2.ID，然后它只需要针对 T 中的每一行查找该哈希表。

您想要哪种行为实际上取决于您期望从每个表/子查询获得多少数据。如果在 T2 中有 100 万行，在 T 中有 10 行，那么用 100 万行填充临时表是没有意义的，只是随后只使用它 10 次，而如果在 T 中有大量行并且只T2 中的少量实现子查询的额外成本从长远来看将是有益的。

要指出的另一件事(这对性能没有影响)，您使用的 JOIN 语法是 ANSI 89 语法，并在 20 多年前被 ANSI 92 显式 JOIN 语法取代。虽然针对 SQL Server，但我认为 this article 很好地总结了切换到更新的连接语法的原因。进行最终查询:

SELECT  *
FROM    product p,
        INNER JOIN store s
            ON p.store = s.idstore
        INNER JOIN branches b
            ON b.idproduct = p.idproduct
        INNER JOIN
        (   SELECT DISTINCT p.common_name
            FROM    shopping_list_content s
                    INNER JOIN product p
                        ON s.iditem = p.idproduct
            WHERE   s.idlist =$listid
        ) s
            ON s.common_name = p.common_name;

注意如果您使用的是 MySQL 5.6.5 或更高版本，则上述大部分内容不适用。在这个版本中，他们引入了更多的 Subquery Optimization，解决了上述许多问题