mysql - 缓慢的 Mysql Inner 加入多个 OR

Question

我正在帮助一位 friend 开发电子商务网站。他可以让用户选择不同的颜色、款式、用途和他所销售产品的类型。查询将以下内容添加到查询中:

INNER JOIN tbl_coloursProducts col ON ( p.product_id = col.productID AND (col.colourID = 2 OR col.colourID = 3 OR col.colourID = 5 OR col.colourID = 8 OR col.colourID = 10)) 
INNER JOIN tbl_useProducts tbluse ON ( p.product_id = tbluse.productID AND (tbluse.useID = 15 OR tbluse.useID = 16 OR tbluse.useID = 17 OR tbluse.useID = 18)) 
INNER JOIN tbl_styleProducts style ON ( p.product_id = style.productID AND (style.styleID = 39 OR style.styleID = 44)) 
INNER JOIN tbl_typeProducts type ON ( p.product_id = type.productID AND (type.typeID = 46 OR type.typeID = 48 OR type.typeID = 50)) 

当只选择几个选项时，查询加载速度足够快，但一些用户选择多个或每个选项导致查询运行超过 30 秒并超时。

在不改变表结构的情况下，有没有更好的方法来优化查询？

这是完整的查询:

SELECT *, 
       p.product_id, 
       Coalesce((SELECT p2sp.price 
                 FROM   ab_product_specials p2sp 
                 WHERE  p2sp.product_id = p.product_id 
                        AND p2sp.customer_group_id = '1' 
                        AND ( ( p2sp.date_start = '0000-00-00' 
                                 OR p2sp.date_start < Now() ) 
                              AND ( p2sp.date_end = '0000-00-00' 
                                     OR p2sp.date_end > Now() ) ) 
                 ORDER  BY p2sp.priority ASC, 
                           p2sp.price ASC 
                 LIMIT  1), p.price) AS final_price, 
       pd.name                       AS name, 
       m.name                        AS manufacturer, 
       ss.name                       AS stock, 
       (SELECT Avg(r.rating) 
        FROM   ab_reviews r 
        WHERE  p.product_id = r.product_id 
        GROUP  BY r.product_id)      AS rating, 
       (SELECT Count(rw.review_id) 
        FROM   ab_reviews rw 
        WHERE  p.product_id = rw.product_id 
        GROUP  BY rw.product_id)     AS review 
FROM   ab_products p 
       LEFT JOIN ab_product_descriptions pd 
              ON ( p.product_id = pd.product_id 
                   AND pd.language_id = '1' ) 
       LEFT JOIN ab_products_to_stores p2s 
              ON ( p.product_id = p2s.product_id ) 
       LEFT JOIN ab_manufacturers m 
              ON ( p.manufacturer_id = m.manufacturer_id ) 
       LEFT JOIN ab_stock_statuses ss 
              ON ( p.stock_status_id = ss.stock_status_id 
                   AND ss.language_id = '1' ) 
       LEFT JOIN ab_products_to_categories p2c 
              ON ( p.product_id = p2c.product_id ) 
       INNER JOIN tbl_coloursproducts col 
               ON ( p.product_id = col.productid 
                    AND ( col.colourid = 2 
                           OR col.colourid = 3 
                           OR col.colourid = 5 
                           OR col.colourid = 8 
                           OR col.colourid = 10 ) ) 
       INNER JOIN tbl_useproducts tbluse 
               ON ( p.product_id = tbluse.productid 
                    AND ( tbluse.useid = 15 
                           OR tbluse.useid = 16 
                           OR tbluse.useid = 17 
                           OR tbluse.useid = 18 ) ) 
       INNER JOIN tbl_styleproducts style 
               ON ( p.product_id = style.productid 
                    AND ( style.styleid = 39 
                           OR style.styleid = 44 ) ) 
       INNER JOIN tbl_typeproducts type 
               ON ( p.product_id = type.productid 
                    AND ( type.typeid = 46 
                           OR type.typeid = 48 
                           OR type.typeid = 50 ) ) 
WHERE  p.status = '1' 
       AND p.date_available <= Now() 
       AND p2s.store_id = 0 
       AND p2c.category_id = 131 
GROUP  BY p.product_id 
ORDER  BY p.product_id DESC 
LIMIT  0, 8 

如果没有自定义位，查询运行良好。

Answer 1

查看该查询，不确定 OR 本身是否有问题(尽管您可以通过对每个 OR 使用和 IN 子句来使代码更紧凑)。相反，我怀疑选择越来越多的选项会导致返回更多的行。这会导致 SELECT 子句中的子查询出现问题。

您能否尝试从 SELECT 子句中删除子查询的查询，看看有什么效果。

您可以很容易地删除子查询。

SELECT *, 
       p.product_id, 
       Coalesce(sub1.price, p.price) AS final_price, 
       pd.name                       AS name, 
       m.name                        AS manufacturer, 
       ss.name                       AS stock, 
       sub0.rating, 
       sub0.review 
FROM   ab_products p 
INNER JOIN
(
    SELECT r.product_id,
            Avg(r.rating)  AS rating, 
            Count(rw.review_id) AS review 
    FROM   ab_reviews r 
    GROUP  BY r.product_id
) sub0
ON p.product_id = sub0.product_id 
LEFT OUTER JOIN
(
    SELECT p2sp.product_id,
            SUBSTRING_INDEX(GROUP_CONCAT(p2sp.price ORDER  BY p2sp.priority ASC, p2sp.price ASC ), ',', 1) AS price
    FROM   ab_product_specials p2sp 
    WHERE  p2sp.customer_group_id = '1' 
    AND ( p2sp.date_start = '0000-00-00' OR p2sp.date_start < NOW() ) 
    AND ( p2sp.date_end = '0000-00-00' OR p2sp.date_end > NOW() )
    GROUP BY p2sp.product_id
) sub1
ON p.product_id = sub1.product_id 
       LEFT JOIN ab_product_descriptions pd 
              ON ( p.product_id = pd.product_id 
                   AND pd.language_id = '1' ) 
       LEFT JOIN ab_products_to_stores p2s 
              ON ( p.product_id = p2s.product_id ) 
       LEFT JOIN ab_manufacturers m 
              ON ( p.manufacturer_id = m.manufacturer_id ) 
       LEFT JOIN ab_stock_statuses ss 
              ON ( p.stock_status_id = ss.stock_status_id 
                   AND ss.language_id = '1' ) 
       LEFT JOIN ab_products_to_categories p2c 
              ON ( p.product_id = p2c.product_id ) 
       INNER JOIN tbl_coloursproducts col 
               ON ( p.product_id = col.productid 
                    AND ( col.colourid = 2 
                           OR col.colourid = 3 
                           OR col.colourid = 5 
                           OR col.colourid = 8 
                           OR col.colourid = 10 ) ) 
       INNER JOIN tbl_useproducts tbluse 
               ON ( p.product_id = tbluse.productid 
                    AND ( tbluse.useid = 15 
                           OR tbluse.useid = 16 
                           OR tbluse.useid = 17 
                           OR tbluse.useid = 18 ) ) 
       INNER JOIN tbl_styleproducts style 
               ON ( p.product_id = style.productid 
                    AND ( style.styleid = 39 
                           OR style.styleid = 44 ) ) 
       INNER JOIN tbl_typeproducts type 
               ON ( p.product_id = type.productid 
                    AND ( type.typeid = 46 
                           OR type.typeid = 48 
                           OR type.typeid = 50 ) ) 
WHERE  p.status = '1' 
       AND p.date_available <= Now() 
       AND p2s.store_id = 0 
       AND p2c.category_id = 131 
GROUP  BY p.product_id 
ORDER  BY p.product_id DESC 
LIMIT  0, 8 

顺便说一句，当您从 ab_product_specials 中读取时，您正在检查 date_start 和 date_end 是否为 0000-00-00(即日期)，同时还将它们与返回日期/时间字段的 NOW() 进行比较。这些字段是日期还是日期/时间字段？