mysql - OR 子句减慢 SQL 查询

Question

我需要你的帮助来解决以下需要 2 分钟以上才能返回结果的查询:

SELECT 
    p.weight, 
    o.login,
    o.date, 
    o.s_address, 
    o.s_city, 
    o.s_county, 
    o.s_state, 
    o.s_country, 
    o.s_zipcode, 
    o.phone, 
    c.categoryid, 
    c.category, 
    o.orderid, 
    p.product product_name, 
    p.productcode sku, 
    d.amount, 
    v.value emplacement, 
    ( SELECT ev.value FROM xcart_extra_field_values ev LEFT JOIN xcart_extra_fields ef ON ef.fieldid=ev.fieldid WHERE ev.productid = d.productid AND ef.field = 'a_type' LIMIT 1 ) type, 
    o.customer_notes, 
    o.membership, 
    o.s_firstname, 
    o.s_lastname, 
    o.phone, 
    d.price, 
    o.email 
FROM `xcart_orders` o 
LEFT JOIN `xcart_shipping` s ON s.shippingid=o.shippingid 
LEFT JOIN `xcart_order_details` d ON d.orderid=o.orderid 
LEFT JOIN `xcart_products` p ON p.productid=d.productid 
LEFT JOIN `xcart_products_categories` pc ON pc.productid=p.productid 
LEFT JOIN `xcart_categories` c ON c.categoryid=pc.categoryid 
LEFT JOIN `xcart_extra_field_values` v ON v.productid=p.productid 
LEFT JOIN `xcart_extra_fields` f ON f.fieldid=v.fieldid 
WHERE o.shippingid IN ( SELECT DISTINCT shippingid FROM `xcart_rafale_shipping` WHERE rafale='1' ) 
AND ( 
    SELECT COUNT(*) 
    FROM `xcart_order_details` d2 
    LEFT JOIN `xcart_products_categories` pc2 ON pc2.productid=d2.productid 
    WHERE d2.orderid=o.orderid 
    AND pc2.categoryid NOT IN ( SELECT DISTINCT ac2.categoryid FROM `xcart_rafale_aggregation_categories` ac2 WHERE ac2.aggregationid='12' ) 
) = 0 
AND ( 
    ( o.date BETWEEN '1386802800' AND '1386889199' ) 
    OR (o.orderid IN ('44', '55', '66')) 
) 
AND o.orderid NOT IN ('11', '22', '33', '123', '458') 
AND o.paid = 'Y' 
AND o.status <> 'F' 
AND o.status <> 'Q' 
AND o.status <> 'I' 
AND f.field = 'emplacement' 
AND pc.main = 'Y' 
ORDER BY v.value ASC, p.productcode ASC 
LIMIT 100

问题可能出在下面的子句

AND ( 
    ( o.date BETWEEN '1386802800' AND '1386889199' ) 
    OR (o.orderid IN ('44', '55', '66')) 
)

因为当我删除 OR (o.orderid IN ('44', '55', '66'))

时查询执行得更快

在 o.date 和 o.orderid 列上有索引

我使用了下面的查询，灵感来自@Clockwork-Muse 的回复:

SELECT 
    p.weight, 
    o.login,
    o.date, 
    o.s_address, 
    o.s_city, 
    o.s_county, 
    o.s_state, 
    o.s_country, 
    o.s_zipcode, 
    o.phone, 
    c.categoryid, 
    c.category, 
    o.orderid, 
    p.product product_name, 
    p.productcode sku, 
    d.amount, 
    v.value emplacement, 
    ( SELECT ev.value FROM xcart_extra_field_values ev LEFT JOIN xcart_extra_fields ef ON ef.fieldid=ev.fieldid WHERE ev.productid = d.productid AND ef.field = 'a_type' LIMIT 1 ) type, 
    o.customer_notes, 
    o.membership, 
    o.s_firstname, 
    o.s_lastname, 
    o.phone, 
    d.price, 
    o.email
FROM `xcart_order_details` d
INNER JOIN (SELECT *
            FROM `xcart_orders`
            WHERE (
                orderid IN ('44', '55', '66') 
                OR (`date` >= '1386802800' AND `date` <= '1386889199')
            )
            ) o
        ON o.orderid = d.orderid
INNER JOIN (SELECT DISTINCT shippingid
            FROM `xcart_rafale_shipping`
            WHERE rafale = '1') rf
        ON rf.shippingid = o.shippingid
INNER JOIN `xcart_shipping` s ON s.shippingid=o.shippingid  
INNER JOIN `xcart_products` p ON p.productid=d.productid
INNER JOIN (SELECT *
            FROM `xcart_products_categories`
            WHERE main = 'Y') pc
        ON pc.productid=p.productid
LEFT JOIN `xcart_categories` c ON c.categoryid=pc.categoryid
LEFT JOIN `xcart_extra_field_values` v ON v.productid=p.productid  
LEFT JOIN `xcart_extra_fields` f ON f.fieldid=v.fieldid
WHERE NOT EXISTS ( 
    SELECT d2.orderid 
    FROM `xcart_order_details` d2 
    LEFT JOIN `xcart_products_categories` pc2 ON pc2.productid=d2.productid 
    WHERE d2.orderid=o.orderid 
    AND pc2.categoryid NOT IN ( SELECT DISTINCT ac2.categoryid FROM `xcart_rafale_aggregation_categories` ac2 WHERE ac2.aggregationid='12' ) 
) 
AND o.orderid NOT IN ('11', '22', '33', '123', '458') 
AND o.paid = 'Y'
AND o.status NOT IN ('F', 'Q', 'I')
AND f.field = 'emplacement'

Answer 1

除了您的查询遇到的任何其他问题或它可能需要的索引之外，它所做的工作超出了它的需要；这是一个稍微调整过的版本，可能运行得更快:

SELECT 
    p.weight, 
    o.login,
    o.date, 
    o.s_address, 
    o.s_city, 
    o.s_county, 
    o.s_state, 
    o.s_country, 
    o.s_zipcode, 
    o.phone, 
    c.categoryid, 
    c.category, 
    o.orderid, 
    p.product product_name, 
    p.productcode sku, 
    d.amount, 
    v.value emplacement, 
    (SELECT ev.value 
     FROM xcart_extra_field_values ev 
     INNER JOIN xcart_extra_fields ef 
             ON ef.fieldid = ev.fieldid
                AND ef.field = 'a_type'  
     WHERE ev.productid = d.productid) type, 
    o.customer_notes, 
    o.membership, 
    o.s_firstname, 
    o.s_lastname, 
    o.phone, 
    d.price, 
    o.email 
FROM `xcart_orders` o
INNER JOIN (SELECT DISTINCT shippingid
            FROM `xcart_rafale_shipping`
            WHERE rafale = '1') rf
        ON rf.shippingid = o.shippingid
LEFT JOIN `xcart_shipping` s 
       ON s.shippingid = o.shippingid 
LEFT JOIN `xcart_order_details` d 
       ON d.orderid = o.orderid 
LEFT JOIN `xcart_products` p 
       ON p.productid = d.productid 
LEFT JOIN `xcart_products_categories` pc 
       ON pc.productid = p.productid
          AND pc.main = 'Y'
LEFT JOIN `xcart_categories` c 
       ON c.categoryid = pc.categoryid 
LEFT JOIN `xcart_extra_field_values` v 
       ON v.productid = p.productid 
LEFT JOIN `xcart_extra_fields` f 
       ON f.fieldid = v.fieldid
          AND f.field = 'emplacement'  
WHERE NOT EXISTS (SELECT *
                  FROM `xcart_products_categories` pc2
                  LEFT JOIN `xcart_rafale_aggregation_categories` ac2
                         ON ac2.categoryid = pc2.categoryid
                            AND ac2.aggregationid = '12'
                  WHERE pc2.productid = d.productid
                        AND ac2.categoryid IS NULL)
AND ((o.date >= '1386802800' AND o.date <'1386889200') 
     OR o.orderid IN ('44', '55', '66')) 
AND o.orderid NOT IN ('11', '22', '33', '123', '458') 
AND o.paid = 'Y' 
AND o.status NOT IN ('F', 'Q', 'I')        
ORDER BY v.value ASC, p.productcode ASC 
LIMIT 100

一些其他的事情 -

1. 你有LEFT JOIN在 WHERE 中有一个条件子句 - 这实际上将它们变成了 INNER JOIN秒。我已将相关条件移至连接中，这可能会改变您的结果。如果你想要一个实际的 INNER JOIN ，只需更改/删除单词。这就是为什么最好尽可能将 ALL 条件放入联接中。
2. 日期/时间/时间戳(即使没有存储为该类型)是一种“测量”——所有测量在逻辑上在记录中都有一些不精确；为了反射(reflect)这一点，请使用“下限包含，上限排除”( a >= x < b ，负值需要翻转)进行比较。为了保持一致性，我也建议将此用于整数计数。
3. 没有 ORDER BY条款，任何使用 LIMIT (或类似的陈述)返回本质上无法控制的结果。如果您只想要一个值，您必须执行以下操作之一 - 1) 使用聚合( MAX() 等)，2) 编写您的查询/构建您的数据库，以便只有一个值将符合条件，3) 提供相关ORDER BY使用“选择位置 x”类型构造。如果不这样做，将导致您的查询在您最意想不到的时候返回意外结果(并且不会发出警告)。在这种情况下，我发现在 EAV 表中存在多个值实例的可能性极小(本质上是情况 #2)。
4. 您的原始查询包含一个相当混淆的双重否定 (SELECT COUNT(*) ... = 0))。不幸的是，在不了解更多关于数据/表模式的性质的情况下，我无法真正消除双重否定(尽管我可以让它更明显。为了 future 的维护者，请尽可能避免双重否定。在这种情况下，这是因为您(可能过度)自由使用了 LEFT -joins - 您确定不需要信息吗？