mysql - 添加索引会减慢查询速度

Question

我正在尝试优化查询

SELECT count(DISTINCT booking.id)
FROM ride 
       LEFT JOIN spot s1_ ON ride.from_spot_id = s1_.id
       LEFT JOIN spot s2_ ON ride.to_spot_id = s2_.id
       LEFT JOIN booking ON ride.booking_id = booking.id
       LEFT JOIN contact ON booking.contact_id = contact.id
WHERE (contact.first_name LIKE UPPER('%GAE%') OR contact.last_name LIKE UPPER('%GAE%') OR contact.email LIKE UPPER('%GAE%'))
  AND booking.paid_at IS NOT NULL
  AND booking.cancelled_at IS NULL;

查询在 2 秒左右的时间内执行。我在 booking.cancelled_at 上添加了一个索引

alter table booking add index booking_cancelled_at (cancelled_at);

现在大约需要 15 秒!

我去寻找并发现我可能想在 cancelled_at 和 paid_at 上添加一个复合索引。我试过了，但是 MYSQL 仍然选择 cancelled_at 索引。然后我尝试删除 cancelled_at 索引以强制使用复合索引。我当时还在 15 岁左右。

结论是:没有索引 2s，有索引(单个或复合)15s。

我看了计划:

有索引

无索引

我不确定为什么完整扫描比使用索引更快？

任何有关如何解决此问题的指导或指示将不胜感激!

谢谢，

Answer 1

对于给定的列，您只需要检查它是否为 null，我不认为对其进行索引有什么意义。相反，由于 booking.paid_at 不能为 null，我会修改 join 为 booking 和 contact 到 inner join 并使用位于 booking.paid 和 booking.cancelled_at 的条件标准。

例子:

SELECT count(DISTINCT booking.id)
FROM ride 
       JOIN booking ON ride.booking_id = booking.id
       AND booking.paid_at IS NOT NULL
       AND booking.cancelled_at IS NULL
       JOIN contact ON ON booking.contact_id = contact.id
       AND (contact.first_name LIKE UPPER('%GAE%') OR contact.last_name LIKE UPPER('%GAE%') OR contact.email LIKE UPPER('%GAE%'))
       LEFT JOIN spot s1_ ON ride.from_spot_id = s1_.id
       LEFT JOIN spot s2_ ON ride.to_spot_id = s2_.id;

如果仍然很慢，您可以在查询之前预选contact id 并使用in 运算符。