mysql - 本地数据库和生产环境之间 SQL 查询运行时间的惊人差异

Question

我有下一个查询:

SELECT `real_estates`.*
FROM `real_estates`
WHERE (photos_count > 0) AND
      (price > 0) AND
      (longitude is not null AND latitude is not null) AND
      `real_estates`.`rent` = 0 AND
      (estate_type = 0 or estate_type = 1) AND
      (price > 1000)
ORDER BY RAND()
LIMIT 1

数据库是MySQL。 real_estates 表中的行数在生产数据库和本地大约为 200k。但是在开发机器上运行这个查询需要 0.5 秒，而生产给我 25 秒(!)等待时间。

表引擎是 InnoDB，EXPLAIN 在两个系统上给出相同的结果。如果我删除 ORDER BY RAND()，它需要在产品上正常运行。有什么想法吗？

Answer 1

从您的查询来看，您似乎正试图从 real_estates 表中检索随机选择的行。按照你编写查询的方式，MySQL 服务器必须将所有匹配的行加载到 RAM 或硬盘驱动器数据结构中，然后对它们进行排序，取第一个，丢弃其余的。很可能您的共享生产服务器的可用 RAM 数量要少得多、磁盘驱动器速度要慢得多、其他用户很多，或者三者兼而有之。这意味着像您这样的 RAM 密集型任务可能会被迫转至磁盘。这减慢了速度。很多。你已经知道了。

您可能会考虑重构您的查询。

SELECT `real_estates`.*
FROM `real_estates`
JOIN (
        SELECT id
          FROM real_estates
         WHERE (photos_count > 0) AND
               (price > 0) AND
               (longitude is not null AND latitude is not null) AND
               `real_estates`.`rent` = 0 AND
               (estate_type = 0 or estate_type = 1) AND
               (price > 1000)
         ORDER BY RAND()
         LIMIT 1
      ) r ON real_estates.id = r.id

(我猜你的表的每一行都有一个唯一的 id 值。我也猜它被称为 id。你可能需要更改此查询以匹配您的 ID 的实际名称。)

内部查询包含您的选择逻辑。但是它不会生成一大堆数据进行排序，它只会生成 id 值。然后它随机排序它们并拿走第一个。最后，连接获取整个选定的行。