mysql - 从 MySQL 迁移到 MariaDB 后 Prestashop 搜索速度变慢

Question

将 Prestashop 实例从 MySQL 迁移到 MariaDB 后，过滤的搜索查询变得很慢。这是一个如此慢的查询的示例。

SELECT
    fl.name feature_name,
    fp.id_feature,
    fv.id_feature_value,
    fvl.value,
    COUNT(DISTINCT p.id_product) nbr,
    lifl.url_name name_url_name,
    lifl.meta_title name_meta_title,
    lifvl.url_name value_url_name,
    lifvl.meta_title value_meta_title,
    psi.price_min,
    psi.price_max,
    m.name
FROM
    ps_feature_product fp
INNER JOIN
    ps_product p
ON
    (p.id_product = fp.id_product)
LEFT JOIN
    ps_feature_lang fl
ON
    (
        fl.id_feature = fp.id_feature AND fl.id_lang = 2
    )
INNER JOIN
    ps_feature_value fv
ON
    (
        fv.id_feature_value = fp.id_feature_value AND(
            fv.custom IS NULL OR fv.custom = 0
        )
    )
LEFT JOIN
    ps_feature_value_lang fvl
ON
    (
        fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = 2
    )
LEFT JOIN
    ps_layered_indexable_feature_lang_value lifl
ON
    (
        lifl.id_feature = fp.id_feature AND lifl.id_lang = 2
    )
LEFT JOIN
    ps_layered_indexable_feature_value_lang_value lifvl
ON
    (
        lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = 2
    )
INNER JOIN
    ps_product_shop product_shop
ON
    (
        product_shop.id_product = p.id_product AND product_shop.id_shop = 1
    )
INNER JOIN
    `ps_layered_price_index` psi
ON
    (
        psi.id_product = p.id_product AND psi.id_currency = 2 AND psi.id_shop = 1
    )
LEFT JOIN
    `ps_manufacturer` m
ON
    (
        m.id_manufacturer = p.id_manufacturer
    )
WHERE
    product_shop.`active` = 1 AND product_shop.`visibility` IN("both", "catalog") AND fp.id_feature = 9 AND p.id_product IN(
    SELECT
        id_product
    FROM
        ps_category_product cp
    INNER JOIN
        ps_category c
    ON
        (
            c.id_category = cp.id_category AND c.id_category = 13 AND c.active = 1
        )
) AND p.id_product IN(
SELECT
    id_product
FROM
    ps_feature_product fp
WHERE
    fp.`id_feature_value` = 39
) AND p.id_product IN(
SELECT
    id_product
FROM
    ps_feature_product fp
WHERE
    fp.`id_feature_value` = 18
) AND p.id_product IN(
SELECT
    id_product
FROM
    ps_feature_product fp
WHERE
    fp.`id_feature_value` = 13
) AND p.id_product IN(
SELECT
    id_product
FROM
    ps_feature_product fp
WHERE
    fp.`id_feature_value` = 44
) AND p.id_product IN(
SELECT
    id_product
FROM
    ps_feature_product fp
WHERE
    fp.`id_feature_value` = 3186
)
GROUP BY
    fv.id_feature_value
ORDER BY
    fv.position

有趣的是，如果删除 WHERE 子句中的多个(几乎相同)子查询之一，速度会快得多(4 秒 vs 0.4 秒)。

... AND
/*
-- removing any of the six product filters makes
-- the query a lot faster
p.id_product IN(
    SELECT
        id_product
    FROM
        ps_feature_product fp
    WHERE
        fp.`id_feature_value` = 18
)
*/
AND ...

这是此查询的 EXPLAIN 输出(未经修改)。

有什么明显的事情需要以某种方式改变吗？所有表格都已优化，没有任何改进。

Answer 1

您有以下几个:

    AND  p.id_product IN (
        SELECT  id_product
            FROM  ps_feature_product fp
            WHERE  fp.`id_feature_value` = 39 )

可以变成这样

    AND EXISTS ( SELECT * FROM ps_feature_product
                  WHERE id_product = p.id_product
                    AND id_feature_value = 39 )

IN ( SELECT ... ) 优化不佳。

确保有INDEX(id_product, id_feature_value)。

MySQL和MariaDB性能差异的原因在于5.6左右的几项优化改进出现了分歧。它们涉及与您正在做的事情相关的内容。

类似的事情

        LEFT JOIN  ps_layered_indexable_feature_value_lang_value lifvl
               ON ( lifvl.id_feature_value = fp.id_feature_value
              AND   lifvl.id_lang = 2 

需要一个复合INDEX(id_feature_value, id_lang)(任意顺序)。但我可以从 EXPLAIN 中猜测你有这样的。

请为每个表提供SHOW CREATE TABLE，可能会有更多建议。

我无法解决您的具体问题，因为我不知道 EXPLAIN 的哪一行对应于已删除的子句。