Index

優化 IP 範圍搜尋?

  • December 4, 2016

我有一個簡單的查詢和表,我想知道哪種索引對這種表和查詢有效。

在我的表中,我有 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 $$)

根據馬丁的評論,我假設問題中有錯字。

單個範圍搜尋的問題ipFromipTo,平均而言,它需要搜尋表的一半。另一種方法是

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數據中的速度。

這符合您目前的邏輯(未經測試),但請參閱我對問題的評論。

我所做的是反轉過濾器以排除不需要的行:現在您可以單獨索引(當然iptoipfrom除非您有可以替代的 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)

引用自:https://dba.stackexchange.com/questions/14894