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

发布时间：2020-12-12 06:34:27 所属栏目：MsSql教程来源：网络整理

导读：在工作中,我们对具有以下结构的表进行了查询： ip_from(number),ip_to(number),country,city,state,isp,latitude,longitude. 该表有大约610万行. 要查找给定IP地址的详细信息,我们使用了如下查询： SELECT * FROM Ip2locationWHERE :ip_num BETWEEN ip_from A

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

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秒.

这对我来说没什么意义.我期待一些性能提升,但不是那么多.数据库统计数据是否应该已经确定没有重叠并相应地进行优化？还有一种公认的更快捷的方式来选择使用范围？

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

解决方法

性能提升是显而易见的.因为ip_from上有一个索引,所以max(ip_from)可以在恒定时间内获得,因为你知道索引会对这些值进行排序.由于btree上的二进制搜索,范围也很容易计算.

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!