mysql - 在巨大的 table 上加入 3 张 table 的速度非常慢

Question

我有一个查询，如下所示:

SELECT    insanlyBigTable.description_short, 
          insanlyBigTable.id AS insanlyBigTable, 
          insanlyBigTable.type AS insanlyBigTableLol, 
          catalogpartner.id AS catalogpartner_id
FROM insanlyBigTable
INNER JOIN smallerTable ON smallerTable.id = insanlyBigTable.catalog_id
INNER JOIN smallerTable1 ON smallerTable1.catalog_id = smallerTable.id 
  AND smallerTable1.buyer_id = 'xxx'
WHERE smallerTable1.cont = 'Y' AND insanlyBigTable.type IN ('111','222','33') 
GROUP BY smallerTable.id;

现在，当我第一次运行查询时，它会将巨型表复制到临时表中...我想知道如何防止这种情况发生？我正在考虑嵌套查询，甚至反转连接(不确定效果是否会运行得更快)，但这很好，但不好。还有其他建议吗？

Answer 1

为了弄清楚如何优化您的查询，我们首先必须准确地归结为它所选择的内容，以便我们可以在更改内容时保留该信息。

您的查询的作用

所以，看起来我们需要以下内容

• GROUP BY 子句将结果限制为每个 catalog_id 最多一行
• smallerTable1.cont = 'Y'、insanelyBigTable.type IN ('111','222','33') 和 buyer_id = ' xxx' 似乎是查询的过滤器。
• 我们想要来自 insanlyBigTable 和 ... catalogpartner 的数据？我猜测catalogpartner是smallerTable1，因为smallerTable的id链接到另一个的catalog_id表。

我不确定在 ON 子句中包含 buyer_id 过滤器的目的是什么，但除非您以不同方式告诉我，否则我会假设事实上它位于 ON 子句中并不重要。

查询要点

根据该 GROUP BY 语句，我不确定查询的意图。您将在 insanelyBigTable 中的每个 catalog_id 中仅获得一行，但您似乎并不关心它是哪一行。事实上，您可以运行此查询的事实是由于 special non-standard feature in MySQL它允许您选择未出现在 GROUP BY 语句中的列...但是，您无法选择 WHICH 列。这意味着您可以从 4 个不同行中获取每个所选项目的信息。

根据列名称，我的最佳猜测是，您正在尝试返回与给定买家购买的商品位于同一目录中的商品列表，但每个目录中的商品不超过一件。此外，您希望通过 catalogpartner 表的 id 连接回该目录中购买的商品。

所以，这可能类似于亚马逊的“您可能喜欢这些商品，因为您购买了这些其他商品”功能。

新查询

我们希望每个 insanlyBigTable.catalog_id 1 行，基于过滤后较小表 1 中存在的 Catalog_id。

SELECT 
    ibt.description_short, 
    ibt.id AS insanlyBigTable, 
    ibt.type AS insanlyBigTableLol, 
    (
        SELECT smallerTable1.id FROM smallerTable1 st
        WHERE st.buyer_id = 'xxx' 
        AND st.cont = 'Y'
        AND st.catalog_id = ibt.catalog_id
        LIMIT 1
    ) AS catalogpartner_id
FROM insanlyBigTable ibt
WHERE ibt.id IN (
    SELECT (
        SELECT ibt.id AS ibt_id
        FROM insanlyBigTable ibt
        WHERE ibt.catalog_id = sti.catalog_id
        LIMIT 1
    ) AS ibt_id
    FROM (
        SELECT DISTINCT(catalog_id) FROM smallerTable1 st
        WHERE st.buyer_id = 'xxx'
        AND st.cont = 'Y'
        AND EXISTS (
            SELECT * FROM insanlyBigTable ibt
            WHERE ibt.type IN ('111','222','33')
            AND ibt.catalog_id = st.catalog_id
        )
    ) AS sti
)

此查询应生成与原始查询相同的结果，但它将内容分解为较小的查询，以避免在 insanlyBigTable 上使用(和滥用)GROUP BY 子句。

尝试一下，如果遇到问题请告诉我。