jquery - 非常大的 Prestashop MySQL 查询会导致大量数据库使用

Question

有没有办法优化这个导致大量数据库使用的查询？完整的查询充满了 ID 记录 - 所以我裁剪为仅

IN (39,40,thousands of products ID's, 29674) GROUP BY

• 重载记录数:108000528

• 重载记录的平均数量:216001

• 重复次数:500

• 最长/中等查询执行时间:5.494/1.873

• 加载/返回的平均行数:216040/39

• 结果为零的问题数量:86

分析的 SQL 查询:

SELECT  p.*, product_shop.*, product_shop.id_category_default,
        pl.*, MAX(image_shop.`id_image`) id_image, il.legend,
        m.name manufacturer_name,
        MAX(pa.id_product_attribute) id_product_attribute,
        stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity
    FROM  `psy_category_product` cp
    LEFT JOIN  psy_category c  ON (c.id_category = cp.id_category)
    LEFT JOIN  `psy_product` p  ON p.`id_product` = cp.`id_product`
    INNER JOIN  psy_product_shop product_shop 
               ON (product_shop.id_product = p.id_product
              AND  product_shop.id_shop = 1 )
    LEFT JOIN  `psy_product_attribute` pa  ON (p.`id_product` = pa.`id_product`)
    LEFT JOIN  psy_product_attribute_shop product_attribute_shop
        ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute
              AND  product_attribute_shop.id_shop = 1
              AND  product_attribute_shop.`default_on` = 1 )
    LEFT JOIN  psy_stock_available stock  ON (stock.id_product = p.id_product
              AND  stock.id_product_attribute =
                 IFNULL(`product_attribute_shop`.id_product_attribute, 0)
              AND  stock.id_shop = 1  )
    LEFT JOIN  psy_product_lang pl  ON (pl.id_product = p.id_product
              AND  pl.id_shop = 1
              AND  pl.id_lang = 1 )
    LEFT JOIN  `psy_image` i  ON (i.`id_product` = p.`id_product`)
    LEFT JOIN  psy_image_shop image_shop  ON (image_shop.id_image = i.id_image
              AND  image_shop.id_shop = 1
              AND  image_shop.cover=1 )
    LEFT JOIN  `psy_image_lang` il  ON (image_shop.`id_image` = il.`id_image`
              AND  il.`id_lang` = 1 )
    LEFT JOIN  psy_manufacturer m  ON (m.id_manufacturer = p.id_manufacturer)
    WHERE  product_shop.`active` = 1
      AND  product_shop.`visibility` IN ("both", "catalog")
      AND  c.id_category = 15
      AND  c.active = 1
      AND  p.id_product IN (39,40,thousands of products ID's, 29674)
    GROUP BY  product_shop.id_product
    ORDER BY  cp.position asc
    LIMIT  48,48;

Answer 1

`` 闻起来像一个多对多映射表。按照提示操作 here .

然后，让我们开始关注查询的关键部分:

SELECT  p.`id_product`,
        ps.id_category_default
    FROM  `psy_category_product` cp
    LEFT JOIN  psy_category c   ON (c.id_category = cp.id_category)
    LEFT JOIN  `psy_product` p  ON  p.`id_product` = cp.`id_product`
    INNER JOIN  psy_product_shop AS ps
               ON  ps.id_product = p.id_product
              AND  ps.id_shop = 1 
    WHERE  ps.`active` = 1
      AND  ps.`visibility` IN ("both", "catalog")
      AND  c.id_category = 15
      AND  c.active = 1
      AND  p.id_product IN (39,40,29674)
    GROUP BY  ps.id_product
    ORDER BY  cp.position asc
    LIMIT  10;

(调整 id_product 列表，以便出现一些内容。)

请为这 3 个表提供SHOW CREATE TABLE，以及EXPLAIN SELECT ...。

表的其余部分似乎只是查找。

尚不清楚是否需要GROUP BY。如果不是，则将其删除。