mysql - 可以提高重叠预订查询的性能吗？

Question

我维护的在线预订系统偶尔会由于我们试图查找的错误而包含重复个重叠预订。当我们这样做时，我收到一个查询，要求列出过去两个月的重叠预订，以便我们可以手动解决这些问题。

我的问题是，这个查询需要永远(5 分钟以上)运行，并且预订系统会逐渐停止，而这样做会损害我们的用户。所以我想提高它的性能。

相关架构的伪代码如下。有两个关键表及其各自的列。

Bookings                        Accounts
ID : int                        ID : int
Status : bool                   Status : bool
StartTime : datetime            Name : varchar
EndTime : datetime
RoomID : int
MemberID : int
AccountID : int

PK: ID                          PK: ID
Index: StartTime, EndTime, 
       MemberID, AccountID,
       RoomID, Status

这些键都是简单键(即没有复合键)。 Bookings.AccountID 是 Accounts.ID 的外键。

查询大致为:

SELECT b1.AccountID, a.Name, b1.ID, b2.ID, b1.StartTime, b1.EndTime, b1.RoomID
FROM Bookings b1
LEFT JOIN Bookings b2
ON b1.MemberID = b2.MemberID
   AND b1.RoomID = b2.RoomID
   AND b2.StartTime > SUBDATE(NOW(), INTERVAL 2 MONTH)) 
LEFT JOIN Accounts a
ON b1.AccountId = a.ID 
WHERE b1.ID != b2.ID
AND b1.Status = 1
AND b2.Status = 1
AND b1.StartTime > SUBDATE(NOW(), INTERVAL 2 MONTH)) 
AND (
  (b1.StartTime >= b2.StartTime AND b2.EndTime <= b1.EndTime AND b1.StartTime < b2.EndTime) OR
  (b1.StartTime <= b2.StartTime AND b2.EndTime >= b1.EndTime AND b2.StartTime < b1.EndTime) OR
  (b2.StartTime <= b1.StartTime AND b2.EndTime >= b1.EndTime)
)

据我所知，该查询本质上是将预订表连接到其自身(过去两个月)，并尝试消除不同的预订。也就是说，它会查找属于同一成员(member)的同一房间的有效 (status=1) 预订，且预订持续时间重叠。

最后三个子句寻找 (a) 在另一个期间开始并在另一个之后结束的预订； (b) 预订先于另一预订开始并在另一预订期间结束； (c) 完全包含在另一项中的预订。这似乎省略了(对我来说)完全围绕另一个的预订(尽管我不确定为什么)。

预订表非常大(约 200 万行)，因为其中包含多年的预订数据。该查询的性能是否可以提高(或替换为更好的查询)？欢迎任何建议。

Answer 1

我会像这样重写查询

SELECT sub.*, a.Name, a.id
from (

    SELECT b1.AccountId, b1.ID, b2.ID, b1.StartTime, b1.EndTime, b1.RoomID
    FROM (select SUBDATE(NOW(), INTERVAL 2 MONTH) as subDate) const, Bookings b1
    LEFT JOIN Bookings b2
    ON b1.MemberID = b2.MemberID
       AND b1.RoomID = b2.RoomID
       AND b2.StartTime > const.subDate
       AND b1.ID != b2.ID 
       AND b2.Status = 1
    WHERE 
    b1.Status = 1
    AND b1.StartTime > const.subDate  
    AND (
      (b1.StartTime >= b2.StartTime AND b2.EndTime <= b1.EndTime AND b1.StartTime < b2.EndTime) OR
      (b1.StartTime <= b2.StartTime AND b2.EndTime >= b1.EndTime AND b2.StartTime < b1.EndTime) OR
      (b2.StartTime <= b1.StartTime AND b2.EndTime >= b1.EndTime)
    )

) sub
LEFT JOIN Accounts a ON 
  sub.AccountId = a.ID 

更新:还要检查列 MemberID、RoomId、StartTime 是否有索引。如果没有这样的索引则引入它们