TSQL 性能 - 在最小值和最大值之間加入值

我有兩個儲存表：

• IP 範圍 - 國家/地區查找表
• 來自不同 IP 的請求列表

IP 儲存為bigints 以提高查找性能。

這是表結構：

create table [dbo].[ip2country](
   [begin_ip] [varchar](15) NOT NULL,
   [end_ip] [varchar](15) NOT NULL,
   [begin_num] [bigint] NOT NULL,
   [end_num] [bigint] NOT NULL,
   [IDCountry] [int] NULL,
   constraint [PK_ip2country] PRIMARY KEY CLUSTERED 
   (
       [begin_num] ASC,
       [end_num] ASC
   )
)

create table Request(
   Id int identity primary key, 
   [Date] datetime, 
   IP bigint, 
   CategoryId int
)

我想獲取每個國家/地區的請求細分，因此我執行以下查詢：

select 
   ic.IDCountry,
   count(r.Id) as CountryCount
from Request r
left join ip2country ic 
 on r.IP between ic.begin_num and ic.end_num
where r.CategoryId = 1
group by ic.IDCountry

我在表中有很多記錄：大約 200,000 inIP2Country和幾百萬 in Request，因此查詢需要一段時間。

查看執行計劃，最昂貴的部分是索引 PK_IP2Country 上的 Clustered Index Seek，執行多次（Request 中的行數）。

另外，我覺得有點奇怪的是left join ip2country ic on r.IP between ic.begin_num and ic.end_num零件（不知道是否有更好的方法來執行查找）。

SQLFiddle 中提供了表結構、一些範例數據和查詢：http ://www.sqlfiddle.com/#!3/a463e/3 （不幸的是，我認為我不能插入很多記錄來重現問題，但這希望給出一個想法）。

我（顯然）不是 SQL 性能/優化方面的專家，所​​以我的問題是：是否有任何明顯的方法可以改進我所缺少的這種結構/查詢的性能？

你需要一個額外的索引。 在您的小提琴範例中，我添加了：

CREATE UNIQUE INDEX ix_IP ON Request(CategoryID, IP)

它涵蓋了請求表並獲取索引查找而不是聚集索引掃描。

看看如何改進它並告訴我。我猜這會很有幫助，因為我確定對該索引的掃描並不便宜。

總是有蠻力的方法：你可以爆炸你的 IP 地圖。針對現有地圖加入數字表，為每個 IP 地址創建一條記錄。根據您的 Fiddle 數據，這只有 267K 條記錄，完全沒有問題。

CREATE TABLE IPLookup
 (
 IP  BIGINT PRIMARY KEY,
 CountryID  INT
 )
INSERT INTO IPLookup (IP, CountryID)
 SELECT
   N.Number, Existing.IDCountry
 FROM
   ip2country AS Existing
   INNER JOIN Numbers AS N ON N.Number BETWEEN Existing.begin_num AND Existing.end_num

這將使搜尋更簡單，並有望更快。當然，這只有在您對 進行相對較少的更新時才有意義ip2country。

我希望其他人有更好的解決方案！

引用自：https://dba.stackexchange.com/questions/17747