为什么基于范围的查询要快得多？

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;

在我们的dev数据库中的Oracle 10上，根据传入的ip_num，返回一行大约需要17秒钟。在我们的加强器直播系统上，它可能花了5-6秒，这仍然太慢，无法实时完成，我们需要通过背景作业来选择这个。

并不是理想的，特别是因为我们的实时系统确实需要ip细节。

所使用的索引类型是跨ip_from和ip_to的标准BTREE索引。为了加快速度，我们研究了很多事情，比如范围划分。我们最终没有应用它，因为它需要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之间没有重叠的范围。

然而，我没有做好准备的是第二个查询的速度有多快。我们的dev数据库的时间从17秒缩短到了0.007秒。

这对我来说没什么意义。我预计会有一些业绩的提高，但没有那么多。数据库统计数据不应该发现没有重叠，并相应地进行了优化吗？此外，必须有一个公认的更快的方式选择使用范围？

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

Answer 1

绩效的提高是明显的。这是因为在ip_from上有一个索引，所以max(ip_from)可以在恒定的时间内获得，因为正如您所知道的，索引会排序这些值。由于对btree的二进制搜索，范围也很容易计算。

在前面的查询中，必须对所有数据执行表扫描，以计算范围界限。