mysql - 使用 "or"时优化MySQL查询

Question

这两个表包含超过 100 万条记录。

此查询需要 15 秒运行并返回 1 条记录(正确):

    select orders.*
        from orders
    left join orders_details on orders.orderID=orders_details.orderID
        where 
orders_details.trackingRef='GYD41624' 
or orders.orderID = (
select distinct  orderID from orders_rebookings
 where trackingRef='GYD41624'
)   
    group by orders.orderID

如果我分别使用每个 where 条件运行查询，那么每个条件都非常快:

这需要 0.0015 秒(找到 1 个匹配项):

select orders.*
    from orders
left join orders_details on orders.orderID=orders_details.orderID
    where orders.orderID = (select distinct orderID from orders_rebookings where trackingRef='GYD41624')    
group by orders.orderID

这几乎不需要时间并且找不到匹配项(这是正确的):

select orders.*
    from orders
left join orders_details on orders.orderID=orders_details.orderID
    where orders_details.trackingRef='GYD41624'     
group by orders.orderID

因此，如果我有两个非常快的查询，如何才能使包含“或”的第一个查询几乎同样快？

Answer 1

您可以使用union或union all来组合它们:

select o.*
from orders o left join
    orders_details od
    on o.orderID = od.orderID
where o.orderID = (select distinct orderID from orders_rebookings where trackingRef = 'GYD41624')    
group by o.orderID
union
select o.*
from orders o left join
     orders_details od
     on o.orderID = od.orderID
where od.trackingRef = 'GYD41624'     
group by o.orderID;

union 效率稍低，因为它会删除重复项。

你的查询也很危险，因为它有= (select . . .)。如果子查询返回多于一行，您将收到错误。

我想知道这种形式是否会表现得更好:

select o.*
from orders o
where exists (select 1 from orders_details where o.orderID = od.orderID and od.trackingRef = 'GYD41624') or
      exists (select 1 from orders_rebookings orr where o.orderID = orr.orderID and orr.trackingRef = 'GYD41624')       

您需要适当的索引(orders_details(orderId, TrackingRef) 和 orders_rebookings(orderId, TrackingRef))。