在 820 万条目表中的一个条目上选择 varchar 字段 - 性能帮助
我在 SQL Server 2005 数据库中有一个包含 820 万个条目的表。该表存储我们访问该网站的每个客户的基本过去客户详细信息（推荐人、IP、他们是否通过广告进入等）。不幸的是，无论是谁设计了这个表（在我来到这里之前，我只是一个低级实习生）都使用了 varchar(50) 字段作为表中的 IP 地址字段。
现在，我的任务是寻找一种方法来运行查询，以查看是否有给定 IP 地址的条目。这是我第一次处理这么大的数据集，无论我做什么，我似乎都无法提高结果速度。
以下是我正在运行的查询示例：
SELECT TOP(1) [ID]
FROM [dbo].[SiteVisit]
WHERE [IPAddress] = '61.135.249.118'
在我取消它之前，该查询运行了超过 2 分 31 秒。有什么方法可以提高此查找的速度，还是更建议在存储 INET_ATON 值的表中创建一个新索引并对其执行查找？
谢谢！
I have a table with 8.2 million entries in a SQL Server 2005 database. This table stores basic past customer details (referrer, ip, whether they entered via an advertisement, etc) for every customer we have had come to the site. Unfortunately, whoever designed the table (way before I was here, I'm a lowly intern) used a varchar(50) field for the ip address field in the table.
Now, I have been tasked with finding a way to run a query to see if we have an entry for a given ip address. This is the first time I've had to deal with a data set this large and no matter what I do, I can't seem to improve the result speed much.
Here is an example query from what I was running:
SELECT TOP(1) [ID]
FROM [dbo].[SiteVisit]
WHERE [IPAddress] = '61.135.249.118'
This query ran for over 2 min and 31 seconds before I cancelled it. Is there any way to improve the speed of this look up or would it be more advisable to create a new index in the table that stores the INET_ATON values and perform the look up on that?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果
IP_Address字段被索引，查找将会非常非常快。建议的索引为
IP_Address INCLUDE(ID)，除非 ID 是您的聚集键，在这种情况下您可以将其排除。If the
IP_Addressfield were indexed, the lookup would be very very quick.The recommended index for this would be
IP_Address INCLUDE(ID)unless ID is your clustered key, in which case you can exclude it.如果您要经常运行查询“我们有这个 IP 地址吗”，那么您绝对应该在 IP 地址上添加索引。但如果这只是一次性练习，那就不行。
If you are going to often run the query "do we have this IP address" then you should definitely add an index on IP address. But if this is just a one-off exercise then no.
尝试在 IPAddress 上创建索引。如果您唯一需要选择的是基于 IP 地址的 ID，并且这是您搜索的常用方式，那么您应该创建如下索引：
Try creating an index on IPAddress. If the only things you need to select are ID based on the IPAddress, and this is a frequent way you search, then you should create an index like this: