mysql - 使用 from 中的子查询优化 SQL

Question

我想优化一条SQL语句，下面是原文。

SELECT DISTINCT
    p.productID,
    p.filename,
    p.model,
    p.code,
    p.manufacturerID,
    f2.manufacturerName,
    m.manufacturerName,
    CONCAT('INSERT INTO distribItems(productID, manufacturer, code, distributorText) VALUES (',
            CAST(p.productID AS CHAR),
            ', \'',
            f2.manufacturerName,
            '\', \'',
            f2.code,
            '\', \'',
            f2.denumire,
            '\') ;') INS
FROM
    (SELECT 
        f.manufacturerName, f.categoryName, f.code, f.denumire, f.code_2
    FROM
        furnizorlist f
    LEFT JOIN distribitems d ON 
        (d.manufacturer = f.manufacturerName
        AND (d.code = f.code OR d.manufacturer LIKE 'DELL')
        AND d.distributorText = LEFT(f.denumire, 450))
    WHERE
        productID IS NULL) f2,
    products p,
    manufacturers m
WHERE
    f2.code_2 <> ''
        AND (f2.code_2 = p.code_2 OR f2.code_2 = p.model_2)
        AND p.manufacturerID = m.manufacturerID
        AND m.manufacturerName = f2.manufacturerName
        AND m.manufacturerName != 'Compatibil'
        AND p.code != '1'
ORDER by p.filename ASC;    

在我的电脑上大约需要 34 秒。

我的想法是将子查询编写为 Join 并在 Where 子句中设置条件。

这是我令人难以置信的快速 SQL:

SELECT DISTINCT
    p.productID,
    p.filename,
    p.model,
    p.code,
    p.manufacturerID,
    f.manufacturerName,
    m.manufacturerName,
    CONCAT('INSERT INTO distribItems(productID, manufacturer, code, distributorText) VALUES (',
            CAST(p.productID AS CHAR),
            ', \'',
            f.manufacturerName,
            '\', \'',
            f.code,
            '\', \'',
            f.denumire,
            '\') ;') INS
FROM
    furnizorlist f,
    distribitems d,
#subquery end
    products p,
    manufacturers m
WHERE
    d.manufacturer = f.manufacturerName
        AND (d.code = f.code OR d.manufacturer LIKE 'DELL')
        AND d.distributorText = LEFT(f.denumire, 450)
        AND d.productID IS NULL
#subquery condions end (f and d tables)
# the next is a subquery result:
        AND f.code_2 <> ''
        AND (f.code_2 = p.code_2 OR f.code_2 = p.model_2)
        AND p.manufacturerID = m.manufacturerID
        AND m.manufacturerName = f.manufacturerName
        AND m.manufacturerName != 'Compatibil'
        AND p.code != '1'
ORDER by p.filename ASC;    

如果我写信给 explain improved_sql，我将看到一个 Impossible WHERE 列。我试过但找不到为什么这是不可能的。我检查了字段的兼容性:没有需要比较 int 和 varchar 之类的情况。我找不到任何重大错误，这就是我来这里的原因。

WHERE 子句是否存在逻辑错误？就像要求一个字段为 1 然后再为 2 一样？

以 ID 结尾的字段是 INT以NAME结尾的字段，code是varchar(255)带有文本的字段是 450 和 8192 varchar(仅在一处使用)

翻译:“denumire”的意思是“描述”——或类似的意思:)

不确定哪个版本的 mysql 在服务器端运行，可能是 5。

如果我有充分的理由，我可以建议更改表结构，可能代码会进行调整。

编辑:

从这里来的不可能:

EXPLAIN 

SELECT 
    f.manufacturerName,
    f.categoryName,
    f.code,
    f.denumire,
    f.code_2
FROM
    furnizorlist f
        INNER JOIN
    distribitems d ON (d.manufacturer = f.manufacturerName
        AND (d.code = f.code
        OR d.manufacturer LIKE 'DELL')
        AND d.distributorText = LEFT(f.denumire, 450))
WHERE
    productID IS NULL

备注:INNER JOIN，不是LEFT JOIN。

编辑2:表:furnizorlist 42,751 条记录表:distribitems 72,290 条记录

Answer 1

不完全确定，但如果您要使用内表(您是第一个示例)，这里有几点很有帮助 - 您真的必须尝试缩小它所拉回的范围。这可能意味着编写一些冗余的 where 子句。例如，“where order_dt is between 2001 and 2002”可能最终出现在较小的内表和主查询中。为什么？因为在某些情况下无法优化内表 - 导致服务器临时检索数百万行，而只有您需要的几行。

此外，我注意到您的一个连接中有一个字符串函数“LEFT(f.denumire, 450))”

在连接期间应避免任何类型的功能，这会迫使服务器(一个接一个地)评估每条记录..它无法优化。这有点类似于为什么你应该始终使用主键进行连接，但更耗时..最好坚持使用“like”= AND, NOT, OR, IN.. 等。