sql - 使用范围索引查询 Sql 服务器

Question

我使用的是在 Windows Datacenter 2012 上运行的 SQL SERVER 2012，

我有一个数据库，其中有一个表，其构建如下:

[ID] (pk,int not null)
[Start] (float,null)
[End] (float, null)
[CID] (int,null) --country id

我有一个获取 IP 的 Web 服务，将其转换为十进制(可引用:IP address conversion to decimal and vice versa)并向数据库服务器请求国家ID

首先提到的表包含约 200K 行，其中起始值和结束值表示 IP 范围(十进制)以及与每个范围相关的国家/地区 ID，

对于我们一直在处理的一些大流量，我遇到了非常高的 CPU 使用率，所以我在开始和结束列上添加了索引，之后 cpu 变得更好了一点，但我认为它应该更多，它只是假设在排序列表中进行搜索，这应该非常快，尽管添加索引的预期结果与现实相去甚远，

我想这是因为它不是搜索列表而是搜索范围

解决这种情况的最佳方法是什么，因为我确信这个简单的操作占用的资源比应有的要多。

这是现在事件监视器的图片(索引后流量较低):

它在 Azure ExtraLarge VM(8 核 14GB 内存)上运行 - 该虚拟机除了运行带有 1 个表的 SQL Server 之外什么都不做，该表仅转换这 1 个请求!较低流量上的虚拟机 CPU 约为 30%，较高流量上约为 70%，我确信一些结构/逻辑更改应该使非常小的服务器/服务轻松处理此问题。

Answer 1

SELECT TOP 1 *
FROM IP
WHERE StartIP <= yourIP
ORDER BY StartIP

这将为您提供高于给定 IP 的最近的 IP 范围。然后您需要测试 EndIP 是否也匹配。所以:

SELECT *
FROM (
 SELECT TOP 1 *
 FROM IP
 WHERE StartIP <= yourIP
 ORDER BY StartIP
) x
WHERE EndIP >= yourIP

这相当于单行索引查找。完美的表现。

SQL Server 无法自动执行此操作的原因是它无法知道 IP 范围是有序的，这意味着下一个 StartIP 始终大于当前 结束IP。我们可以采用 (100, 200), (150, 250) 形式的范围。这显然是无效的，但它可能在表中。