查询索引为范围的Sql server [英] Querying Sql server with index for range

问题描述

我正在使用在Windows数据中心2012上运行的SQL SERVER 2012，

I am using SQL SERVER 2012 that is running on windows datacenter 2012,

我有一个数据库，其中包含一个如下构建的表：

I have a database with a table that is build as followed :

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

我有获取IP的Web服务，将其转换为十进制
（可以参考： IP地址转换为十进制，反之亦然）并向数据库服务器请求国家ID

I have a web service that gets an IP, translate it to decimal (may refer to this : IP address conversion to decimal and vice versa) and request the database server for the country id

首先提到的表包含〜 200K行，其起始值和结束值表示IP范围为十进制，以及与每个范围相关的countryid，

The table mentioned at first contains ~200K rows with start and end values representing IP ranges as decimal and a countryid related to each range,

我遇到了一些非常高的CPU使用率处理，所以我在开始和结束列上添加了索引，之后cpu得到了一点好处，但我认为应该是更多，它只是假设在排序列表中搜索应该是非常快，虽然我添加索引的预期结果远非现实，

I have encountered a really high CPU usage against some heavy traffic we have been dealing, so i added indexes on the start and end columns, afterwards the cpu got a little bit better but i think it should have been much more, its simply suppose to work as a search in a sorted list which should be extremely fast, though the expected result i had from adding the index were far from reality,

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

I suppose it is because its not searching a list but searching a range

有效处理这种情况的最佳方法是什么，因为我确信这个简单的动作带给我的资源远远超过了它。

What would be the best way to efficient this situation, since i am just sure that the resources this simple action is taking me is way to much than it should.

这是活动监视器现在的图片（索引后流量较低）：

Here is a picture from the activity monitor now (lower traffic, after indexing) :

这是运行在Azure ExtraLarge VM（8核14GB内存）上 - vm除了运行带有1个表的sql server之外什么都不做，只能翻译这个1个请求！这种较低流量的VM CPU在流量较高时约为30％和约70％，我相信一些结构/逻辑变化应该可以使一个非常小的服务器服务轻松处理。

This is running on Azure ExtraLarge VM (8 cores 14GB memory) - the vm is doing nothing but running a sql server with 1 table that only translates this 1 request ! the VM CPU on this lower traffic is ~30% and ~70% on higher traffic, i am sure some structure/logical changes should make a really small server\service handle this easily.

推荐答案

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

这可以获得最接近给定的IP范围IP。然后，您需要测试EndIP是否也匹配。所以：

This gets you the nearest IP range above the given IP. You then need to test whether the EndIP also matches. So:

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

这相当于单行索引搜索。完美的性能。

This amounts to a single-row index seek. Perfect performance.

SQL Server 无法自动执行此操作的原因是它无法知道IP范围是否已订购，这意味着下一个 StartIP 始终大于当前 EndIP 。我们的格式范围可以是（100,200），（150,250）。这显然是无效的，但它可能在表中。

The reason SQL Server cannot automatically do this is that it cannot know that IP ranges are ordered, meaning that the next StartIP is always greater than the current EndIP. We could have ranges of the form (100, 200), (150, 250). That is clearly invalid but it could be in the table.

这篇关于查询索引为范围的Sql server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！