mysql - 为什么删除任何 where 会使查询变得更快？

Question

我将呈现我的查询的简化版本:

SELECT item.prop1, item.prop2,
       COALESCE(item.prop3, parents.prop3, grandparents.prop3) AS p3
FROM items
  INNER JOIN parents ON item.parent_id = parents.id
  INNER JOIN grandparents ON parents.grandparent_id = grandparents.id
  INNER JOIN pivot ON pivot.item_id = items.id
  INNER JOIN users ON pivot.user_id = users.id
WHERE
  items.prop4 IS NULL
  AND COALESCE(parents.prop5, grandparents.prop5) = 8
  AND users.country_id IN (123)

这个查询比我能承受的要慢一些 - 它运行大约 0.7 秒。

在尝试优化时，我注意到删除最后两行中的任何一行都会使其在 0.01-0.02 秒内运行。

我尝试将过滤分为两步:

SELECT item.prop1, item.prop2, p3
FROM (
    SELECT item.prop1, item.prop2,
           COALESCE(item.prop3, parents.prop3, grandparents.prop3) AS p3,
           users.country_id as country
    FROM items
      INNER JOIN parents ON item.parent_id = parents.id
      INNER JOIN grandparents ON parents.grandparent_id = grandparents.id
      INNER JOIN pivot ON pivot.item_id = items.id
      INNER JOIN users ON pivot.user_id = users.id
    WHERE
      items.prop4 IS NULL
      AND COALESCE(parents.prop5, grandparents.prop5) = 8
) AS temp
WHERE temp.country IN (123)

这对总运行时间没有影响。当我尝试仅执行子查询时，它在大约 0.01 秒内完成并返回大约 1200 行。我希望通过单列将 1200 行过滤到 570 行不会花费我半秒，不是吗？

我还尝试了另一种层次结构，如下所示:

SELECT item.prop1, item.prop2,
       COALESCE(item.prop3, parents.prop3, grandparents.prop3) AS p3
FROM items
  INNER JOIN parents ON item.parent_id = parents.id
  INNER JOIN grandparents ON parents.grandparent_id = grandparents.id
  INNER JOIN (
    SELECT pivot.item_id as item
    FROM pivot
      INNER JOIN users ON pivot.user_id = users.id
    WHERE users.country_id IN (123)
  ) as country_items ON country_items.item = items.id
WHERE
  items.prop4 IS NULL
  AND COALESCE(parents.prop5, grandparents.prop5) = 8

子查询再次很快(0.005 秒，返回大约 200000 行)，但整个过程运行起来非常慢。

什么可能导致性能下降？我几乎倾向于让我的应用程序分两步执行最后一条语句...

工作解决方案

选择此约束并使用 HAVING 解决了该问题 - 此查询在 0.015 秒内完成。

SELECT item.prop1, item.prop2,
       COALESCE(item.prop3, parents.prop3, grandparents.prop3) AS p3, users.country_id as country
FROM items
  INNER JOIN parents ON item.parent_id = parents.id
  INNER JOIN grandparents ON parents.grandparent_id = grandparents.id
  INNER JOIN pivot ON pivot.item_id = items.id
  INNER JOIN users ON pivot.user_id = users.id
WHERE
  items.prop4 IS NULL
  AND COALESCE(parents.prop5, grandparents.prop5) = 8
HAVING country IN (123)

我还是不明白这里的原因。我认为 COALESCE 是 WHERE 中速度较慢的一个，我尝试先选择并HAVING 它，但它根本没有改变性能。

但是这个查询与我第一次尝试在子查询中选择相同的内容有何不同？这似乎是同一件事 - 查询选择 1200 行，其中 570 行根据国家/地区限制被过滤掉。

Answer 1

使用 WHERE 会强制查询引擎对表的字段执行额外的查找，而没有 WHERE 的查询只会返回结果。

为了使该查询运行得更快，您可能需要向表添加索引。

首先，通过在 SELECT 之前添加 EXPLAIN 来运行相同的查询。这将使您对检查了多少行、关键基数等有一个很好的概述。

如果这是您正在使用的标准查询，我建议您 add an index对于所有 3 列。如果您打算单独查询列，则可以为需要查询的每一列添加索引，但不要过度，因为表会变慢。

如果您在添加索引后运行EXPLAIN，我猜您所检查的行数会显着减少。

如果你的表已经有索引，你可以使用USE INDEX提示MySQL服务器。提示。