[T-SQL]如何判斷IP是否落在某段IP範圍內？

        最近要測試Excel的Power Map，一般系統不會記錄使用者的地理資訊，頂多記IP吧，稍微查了Get and prep your data for Power Map，裡面提到Power Map的地理資訊支援下列幾種

Latitude/Longitude pair, City, Country/Region, Zip code/Postal code, State/Province, or Address

        用經緯度分析資料除非資料量非常大，要不然量小看起來沒什意義，那收斂到City感覺比較合理，所以找了一下如何用IP轉City，當然網上一堆服務可以用，但我不想讓DB層對外，想看有沒有現成的對應表可用，結果DB-IP - IP Geolocation and Network Intelligence有現成的DB可下載，免費版的可就可對應到city耶，五百萬筆資料應該也夠測試了 ，於是就下載來試試

        下載回來的資料是csv，我一開始轉入DB時發現一些問題，資料會有亂碼，用Unicode轉進去會剖析不了，發現編碼好像怪怪的，所以調成下圖那樣就可以匯入了

        因為IP為字串型態效能肯定不好，所以另外建立兩個數字型態的IP欄位，需要下列函數轉換

CREATE FUNCTION [dbo].[IPtoInt]

(

    @IPAddress VARCHAR(20)

)

RETURNS BIGINT

AS

BEGIN

     DECLARE @MAXRANGE BIGINT = 256

    RETURN

    (PARSENAME(@IPAddress,1) + @MAXRANGE * PARSENAME(@IPAddress,2) +

      @MAXRANGE*@MAXRANGE* PARSENAME(@IPAddress ,3) + @MAXRANGE*@MAXRANGE*@MAXRANGE* PARSENAME(@IPAddress ,4))

    

END

        轉進去後發現有些ip似乎是ipv6格式的，有些city還是中文，於是做了一下清洗整理，提供我使用的script供參考

CREATE TABLE [dbo].[dbip_city](

     [IP_START] [VARCHAR](50) NOT NULL,

     [IP_END] [VARCHAR](50) NOT NULL,

     [CITY] [NVARCHAR](255) NULL,

     [REGION] [NVARCHAR](255) NULL,

     [COUNTRY] [NVARCHAR](255) NULL,

     [IP_START_INT] [BIGINT]  NULL,

     [IP_END_INT] [BIGINT]  NULL,

);

GO

--清掉那些看起來像ipv6格式的

DELETE FROM [dbo].[dbip_city] WHERE CHARINDEX(':',[IP_START])>0;

--查出那些是中文的

SELECT * FROM [dbo].[dbip_city] WHERE [COUNTRY] ='TW' AND LEFT(CITY,1)> 'Z';

--更新成英文

UPDATE  [dbo].[dbip_city] SET CITY= 'TAOYUAN CITY' WHERE [COUNTRY] ='TW' AND CITY = '桃園縣';

UPDATE  [dbo].[dbip_city] SET CITY= 'TAIPEI CITY' WHERE [COUNTRY] ='TW' AND CITY = '台北市';

UPDATE  [dbo].[dbip_city] SET CITY= 'KAOHSIUNG CITY' WHERE [COUNTRY] ='TW' AND CITY = '高雄市';

UPDATE  [dbo].[dbip_city] SET CITY= 'NEW TAIPEI CITY' WHERE [COUNTRY] ='TW' AND CITY = '新北市';

UPDATE  [dbo].[dbip_city] SET CITY= 'KINMEN COUNTY' WHERE [COUNTRY] ='TW' AND CITY = '金門縣';

--轉換IP成數值

UPDATE [dbo].[dbip_city] SET [IP_START_INT] = [dbo].[IPTOINT](IP_START),

[IP_END_INT] = [dbo].[IPTOINT](IP_END)

--改欄位格式

ALTER TABLE [dbo].[dbip_city] ALTER COLUMN IP_START_INT BIGINT NOT NULL ;

ALTER TABLE [dbo].[dbip_city] ALTER COLUMN IP_END_INT BIGINT NOT NULL ;

--加PK

ALTER TABLE [dbo].[dbip_city] ADD PRIMARY KEY([IP_START_INT]);

        最後問題來了，如何判斷IP落在哪一個範圍內？

        ip對應表長下面這樣，每一條資料列紀錄了起訖的ip(ip_start與ip_end)，換言之需要判斷兩個欄位囉，看ip是不是落在兩個起訖ip之間，實際判斷時用整數型態的(ip_start_int與ip_end_int)

        所以應該會這樣寫，要馬用BETWEEN要馬不用，幾乎都一樣啦

DECLARE @IPINT BIGINT = [dbo].[IPTOINT]('114.35.120.124');

SELECT REGION FROM [dbo].[DBIP_CITY] WHERE  [IP_END_INT] >= @IPINT AND [IP_START_INT] <= @IPINT;

SELECT REGION FROM [dbo].[DBIP_CITY] WHERE  @IPINT BETWEEN [IP_START_INT] AND [IP_END_INT];

        這種似乎叫二維範圍查詢，就是範圍查詢橫跨兩個欄位，基本上B-Tree索引不利這種查詢，不管你建叢集、非叢集索引或複合索引都一樣，效能都不好

        看實際的執行計畫，有叢集索引搜尋啊，好像很不錯？

        仔細看這個運算子，估計的資料列數目與與實際的資料列數目差異非常大，估計的運算子成本高達14.3999

        怎麼優化呢？

        首先要認清任一個ip，只會落在某一段的ip範圍內，不可能同時落在兩段ip範圍內，所以基本上只要取ip_start小於等於目標ip的，倒序之後的第一筆就是了，當然也可反過來用ip_end來做囉

        底下是用ip_start的例子，兩個差在第二個多了一個判斷而已，效能幾乎是一樣的，第二個理解起來比較合理啦，比較不會出錯

DECLARE @IPINT BIGINT = [dbo].[IPTOINT]('114.35.120.124');

SELECT TOP 1 REGION FROM [dbo].[DBIP_CITY] WHERE  [IP_START_INT] <= @IPINT ORDER BY [IP_START_INT] DESC ;

SELECT  REGION FROM (SELECT TOP 1 REGION,[IP_START],[IP_END_INT] FROM [dbo].[DBIP_CITY] WHERE  [IP_START_INT] <= @IPINT ORDER BY [IP_START_INT] DESC) T WHERE [IP_END_INT] >= @IPINT;

        執行計畫的成本是一樣的

        看運算子，估計得與實際的資料列數目都是1，運算子成本才0.0032831而已

        三個放在一起比較吧，優化過的完勝啊！

        不知還有沒有更佳的做法呢？