sql - 为什么这个基于范围的查询这么快

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详细信息。

所使用的索引类型是跨 ip_from和 ip_to的标准BTREE索引。我们研究了很多东西来尝试加快速度，例如 range partitioning。最终我们没有应用它，因为它需要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)，因为您知道索引可以对值进行排序。由于对btree进行二进制搜索，因此该范围也很容易计算。

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