Rails 中的 Mysql 索引行为不当

Question

我有一个用 Mysql 托管的 Rails 应用程序，有一个 reservations 表，其索引在 rescheduled_reservation_id 列中设置(可为空)。

在我的 Rails 应用程序中，有两个部分可以通过 rescheduled_reservation_id 字段查询预订，如下所示:

Transit::Reservation.find_by(rescheduled_reservation_id: 25805)

并产生以下日志输出:

Transit::Reservation Load (60.3ms)  SELECT  `transit_reservations`.* FROM `transit_reservations` WHERE `transit_reservations`.`deleted_at` IS NULL AND `transit_reservations`.`rescheduled_reservation_id` = 25805 LIMIT 1

但是应用程序的其他部分:

Transit::Reservation.where(rescheduled_reservation_id: 25805).last

日志输出如下

Transit::Reservation Load (2.3ms)  SELECT  `transit_reservations`.* FROM `transit_reservations` WHERE `transit_reservations`.`deleted_at` IS NULL AND `transit_reservations`.`rescheduled_reservation_id` = 25805  ORDER BY `transit_reservations`.`id` DESC LIMIT 1

清楚地看到第一个查询

Transit::Reservation Load (60.3ms)  SELECT  `transit_reservations`.* FROM `transit_reservations` WHERE `transit_reservations`.`deleted_at` IS NULL AND `transit_reservations`.`rescheduled_reservation_id` = 25805 LIMIT 1

花费了 60 毫秒，与此中的 2 毫秒相比，索引可能没有正确使用

Transit::Reservation Load (2.3ms)  SELECT  `transit_reservations`.* FROM `transit_reservations` WHERE `transit_reservations`.`deleted_at` IS NULL AND `transit_reservations`.`rescheduled_reservation_id` = 25805  ORDER BY `transit_reservations`.`id` DESC LIMIT 1

我还尝试通过在两个查询上运行explain来进一步调试，我得到了相同的结果，即正在使用的索引 rescheduled_reservation_id

有人遇到过这个问题吗？我想知道rails mysql连接(我使用mysql2 gem)是否可能导致Mysql服务器不选择正确的索引

Answer 1

这种情况很少见，但很正常。

可能的答案是第一次出现时没有在 buffer_pool 中找到它需要缓存的 block 。因此，它必须从磁盘中获取它们。在普通 ole HDD 上，经验法则是每次磁盘命中 10 毫秒。因此，可能需要获取 6 个 block ，导致 60.3 毫秒。

另一种可能性是其他事件正在干扰，从而减慢了此操作。

对于像这样的简单查询来说，2.3 毫秒是合理的，因为可以完全使用 RAM 中的缓存 block 来执行。

服务器最近是否重新启动过？重启后，缓存中就没有任何内容了。表是否大于 innodb_buffer_pool_size ？如果是这样，这将导致 60 毫秒的情况偶尔发生—— block 会被淘汰。 (警告:buffer_pool 不应太大，以免发生“交换”。)

一个 block 是16KB；它包含一些数据行或索引行或 BTree 的节点。根据表的大小，即使是“点查询”也可能需要查看 6 个 block 或更多。

如果大多数时间都没有达到 2.3 毫秒，我们应该深入挖掘。 (我已经暗示了要调查的尺寸。)