排序中的MySQL慢左连接

Question

我有数以百万计的客户，当我使用left，然后按一列进行排序时，需要4-5秒，这是我的查询：

SELECT c.id AS id, o.description AS office_description, ... , d.type AS document_type, d.number AS document_number
FROM customers c INNER JOIN offices o ON (c.id_office = o.id)
                 INNER JOIN company cp ON (o.id_company = cp.id)
                 LEFT JOIN documents d ON (C.id = d.id_customer)
WHERE c.archive = 0
ORDER BY office_description
LIMIT 10

因此，当我删除选择中的文档列时，查询非常快。

以下是查询解释：

​

我有一百万客户和其他桌子我只有一排(公司/办公室/文件)

我在c.archive / o.description和主键/外键ofc上设置了索引。以下是这些表的结构：http://sqlfiddle.com/#!9/a222f9

因此，我试图像这样构建我的查询：

SELECT A.*, d.* 
FROM (
  SELECT c.id AS id, o.description AS office_description, ...
  FROM customers c INNER JOIN offices o ON (c.id_office = o.id)
                   INNER JOIN company cp ON (o.id_company = cp.id)
  WHERE c.archive = 0
  ORDER BY o.description
  LIMIT 10
) A LEFT JOIN documents d ON (A.id = d.id_customer)

现在，哇，它非常快。

但我不知道这是否是减少滞后的最好方法，也不知道我做错了什么。我想知道你是否知道更好的方法。

我希望有一种更简单的方法，因为在我的Phalcon项目中使用这个查询会很复杂。

Answer 1

一个解释。

在查看documents之前，更快的查询可以找到这10行。所以，它只需要对那张表进行10次探测。

在最初的查询中，优化器并不太聪明。它计划像没有LIMIT一样执行查询。相反，它决定通过将整个表提取到RAM中的“连接缓冲区”中，从而优化到documents的连接，并在其中建立了一个哈希索引。虽然这可以帮助一些像您这样的查询，但对于您所需的仅仅10行来说，这是一个很大的浪费。

所以，你的修改说服了优化者去做一个更好的方法。

如果您只需要来自d的一列，那么还有另一种方法：

SELECT ...,
       ( SELECT col FROM d WHERE ... ) AS col,
    ... ((without the LEFT JOIN at all))

至于“更容易”的方法，尤其是可以反向设计成第三包的方法，我对此表示怀疑。(包往往是在数据库中入门的关键。正如你所发现的，你最终需要学到比他们教你更多的东西。)

单独的低效率：

WHERE c.archive = 0
ORDER BY o.office_description
LIMIT ...

如果存档的行已经从c中删除，那么最佳的执行将是找到o的前10行。相反，它必须在排序和限制之前执行一个冗长的JOIN。(这是“软删除”中常见的问题。无论是MySQL还是第三方软件包都无法对其进行优化。)