为什么这个基于范围的查询如此快

Question

在工作中，我们对具有以下结构的表进行了查询：

ip_from(number), ip_to(number), country, city, state, isp, latitude, longitude.

该表大约有 610 万行。

为了找出给定 IP 地址的详细信息，我们使用了如下查询：

SELECT * 
  FROM Ip2location
WHERE
  :ip_num BETWEEN ip_from AND ip_to;

在我们的开发数据库中的 Oracle 10 上，返回一行大约需要 17 秒，具体取决于传入的 ip_num。我们更强大的实时系统可能需要 5-6 秒，这对于实时来说仍然太慢，我们需要通过后台作业来选择它。

不理想，尤其是当我们的实时系统确实需要 IP 详细信息时。

使用的索引类型是标准 BTREE 索引，涵盖 ip_from 和 ip_to。我们研究了很多东西来尝试加快速度，例如范围分区。我们最终没有应用它，因为它需要 Oracle Enterprise。我们还考虑了增加表的并发性，但这没有明显的效果。

无论如何，当我早上喝咖啡时，我意识到我认为通过运行以下查询可以提高性能：（这是从记忆中得出的，可能有一些错误。此外，我们选择了单个字段，而不是所有字段）

SELECT * 
  FROM ip2location
WHERE 
  ip_from = (
    SELECT max(ip_from)
      FROM ip2location
      WHERE ip_from <= :ip_num
  )
AND
  ip_to >= ip_num;

这适用于我们的数据设置是因为 ip_from 和 ip_to 之间没有重叠范围。

然而，我没有准备好的是第二个查询的速度有多快。我们的开发数据库的时间从 17 秒减少到 0.007 秒。

这对我来说毫无意义。我预计性能会有所提高，但不会那么多。难道数据库统计数据不应该发现没有重叠并进行相应的优化吗？还必须有一种公认的更快的方法来选择使用范围？

我的问题是：为什么即使使用子选择第二个查询也会快得多？

Answer 1

性能提升是显而易见的。这是因为 ip_from 上有一个索引，所以可以在恒定时间内获得 max(ip_from) ，因为正如你所知，索引对值进行排序。由于 b 树上的二分搜索，范围也很容易计算。

而在上一个查询中必须对所有数据进行表扫描以计算范围边界