Index
優化 IP 範圍搜尋?
我有一個簡單的查詢和表,我想知道哪種索引對這種表和查詢有效。
在我的表中,我有 3 列
CREATE TABLE mYTable(ipFrom BIGINT, ipto BIGINT, url NVARCHAR(255))
我正在執行這個簡單的查詢。
SELECT url FROM MyTable WHERE ipto <= somevalue AND ipfrom >= somevalue
我還在 ipFrom 上聚集的所有 3 列上創建了索引,在其餘 2 上非聚集。但是這個查詢在 CPU 和讀取方面給了我非常糟糕的性能。
有什麼建議。
我已經實現的是我喜歡根據 IP 地址重定向使用者。我已經儲存了來自不同地區和州的幾個 IP 範圍,並根據他們的 IP 將使用者重定向到適當的 URL。
是的,我認為我在兩列中都以錯誤的方式插入了數據(稍後將重命名列),但這裡的重點是最小化 CPU。
當我在那裡查看執行計劃時,它在 where 子句中轉換數據,我不知道它為什麼在 where 子句中轉換數據。有這樣的東西
|–聚集索引搜尋(OBJECT:(
$$ T $$.$$ TC $$), 尋找:($$ T $$.$$ C $$> 轉換($$ @V $$)
根據馬丁的評論,我假設問題中有錯字。
單個範圍搜尋的問題
ipFrom
是ipTo
,平均而言,它需要搜尋表的一半。另一種方法是1)添加一個計算列,例如:
ALTER TABLE MyTable ADD granule AS CASE WHEN (ipTo-ipFrom)<16 THEN 1 WHEN (ipTo-ipFrom)<256 THEN 2 ELSE 3 END;
2)索引計算列和
ipFrom
CREATE ClUSTERED INDEX IX ON MyTable(granule, ipFrom );
3)這樣查詢:
SELECT url FROM MyTable WHERE granule = 1 AND ipFrom BETWEEN @somevalue - 16 AND @somevalue AND ipTo >= @somevalue UNION ALL SELECT url FROM MyTable WHERE granule = 2 AND ipFrom BETWEEN @somevalue - 256 AND @somevalue AND ipTo >= @somevalue UNION ALL SELECT url FROM MyTable WHERE granule = 3 AND ipFrom <= @somevalue AND ipTo >= @somevalue;
確切地說,您如何定義“粒度”函式將取決於
ipto-ipfrom
數據中的速度。
這符合您目前的邏輯(未經測試),但請參閱我對問題的評論。
我所做的是反轉過濾器以排除不需要的行:現在您可以單獨索引(當然
ipto
,ipfrom
除非您有可以替代的 PK,否則索引中也需要 url)我建議為 SQL Server 2005+
SELECT url FROM MyTable EXCEPT SELECT url FROM MyTable WHERE ipto > somevalue EXCEPT SELECT url FROM MyTable WHERE ipfrom < somevalue
但是,對於 SQL Server 2000
SELECT url FROM MyTable M WHERE NOT EXISTS (SELECT * FROM MyTable M2 WHERE ipto > somevalue AND M.url = M2.url) AND NOT EXISTS (SELECT * FROM MyTable M3 WHERE pfrom < somevalue AND M.url = M3.url)