Sql-Server

儲存 IP 地址 - varchar(45) 與 varbinary(16)

  • February 26, 2019

我將創建一個包含兩個欄位的表 - IDasBIGINT和IPAddressas anyvarchar(45)或varbinary(16)。這個想法是儲存所有唯一的 IP 地址並使用引用而不是其他表中ID的實際地址。IP address

通常，我將創建一個儲存過程，該過程返回ID給定的IP address或（如果找不到地址）插入地址並返回生成的ID.

我期望有很多記錄（我不能確切地說出有多少），但我需要盡可能快地執行上面的儲存過程。所以，我想知道如何以文本或字節格式儲存實際的 IP 地址。哪個會更好？

我已經編寫SQL CLR了將 IP 地址字節轉換為字元串和反之的函式，因此轉換不是問題（同時使用IPv4和IPv6）。

我想我需要創建一個索引來優化搜尋，但我不確定我應該將該IP address欄位包含到聚集索引中，還是創建一個單獨的索引以及使用哪種類型的搜尋會更快？

如何儲存實際 IP 地址 - 以文本或字節格式。哪個會更好？

由於此處的“文本”指的是 ，VARCHAR(45)而“字節”指的是VARBINARY(16)，所以我想說：兩者都不是。

鑑於以下資訊（來自關於 IPv6 的 Wikipedia 文章）：

地址表示

IPv6 地址的 128 位表示為 8 組，每組 16 位。每個組都寫成 4 個十六進制數字，組用冒號 (:) 分隔。地址 2001:0db8:0000:0000:0000:ff00:0042:8329 就是這種表示的一個例子。

為方便起見，在可能的情況下，可以通過應用以下規則將 IPv6 地址縮寫為更短的符號。

  • 從任何十六進制數字組中刪除一個或多個前導零；這通常對所有前導零進行或不對前導零進行。例如，組 0042 轉換為 42。
  • 零的連續部分替換為雙冒號 (::)。雙冒號只能在地址中使用一次，因為多次使用會使地址不確定。RFC 5952 建議不得使用雙冒號來表示省略的單個零部分。$$ 41 $$

這些規則的應用範例：

        初始地址：2001:0db8:0000:0000:0000:ff00:0042:8329

        刪除每組中的所有前導零後：2001:db8:0:0:0:ff00:42:8329

        省略連續的零部分後：2001 :db8::ff00:42:8329

我將首先使用 8VARBINARY(2)個欄位來表示 8 個組。組 5 - 8 的欄位應該是NULL因為它們將僅用於 IPv6 地址。組 1 - 4 的欄位應該用於 I NOT NULL​​Pv4 和 IPv6 地址。

通過保持每個組獨立（而不是將它們組合成一個VARCHAR(45)或一個VARBINARY(16)甚至兩個BIGINT欄位），您可以獲得兩個主要好處：

  1. 將地址重構為任何特定的表示形式要容易得多。否則，為了用 (::) 替換連續的零組，您必須將其解析出來。將它們分開允許使用簡單的IF//語句來促進這一點IIF。CASE
  2. ROW COMPRESSION通過啟用或，您將在 IPv6 地址上節省大量空間PAGE COMPRESSION。由於這兩種類型的 COMPRESSION 都允許0x00佔用 0 個字節的欄位，因此所有這些零組現在都不會花費您任何費用。另一方面，如果您儲存了上面的範例地址（在 Wikipedia 引用中），那麼中間的 3 組全零將佔用它們的全部空間（除非您VARCHAR(45)使用簡化符號，但這可能不適用於索引，並且需要特殊解析才能將其重建為完整格式，因此我們假設這不是一個選項;-)。

如果您需要擷取網路，TINYINT請為該名稱創建一個欄位，嗯，[Network]:-)

有關網路值的更多資訊，請參閱另一篇關於 IPv6 地址的 Wikipedia 文章中的一些資訊：

網路

IPv6 網路使用的地址塊是一組連續的 IPv6 地址，其大小是 2 的冪。對於給定網路中的所有主機，地址的前導比特集都是相同的，稱為網路地址或路由前綴。

網路地址範圍以 CIDR 表示法編寫。網路由塊中的第一個地址（以全零結尾）、斜杠 (/) 和等於前綴位大小的十進制值表示。例如，寫成 2001:db8:1234::/48 的網路從地址 2001:db8:1234:0000:0000:0000:0000:0000 開始，到 2001:db8:1234:ffff:ffff:ffff:ffff 結束:ffff。

介面地址的路由前綴可以直接用 CIDR 表示法的地址表示。例如，地址為 2001:db8:a::123 的介面連接到子網 2001:db8:a::/64 的配置寫為 2001:db8:a::123/64。

對於索引，我會說在 8 個組欄位上創建一個非聚集索引，如果您決定包含它，可能還包括網路欄位。

最終結果應如下所示：

CREATE TABLE [IPAddress]
(
 IPAddressID INT          NOT NULL IDENTITY(-2147483648, 1),
 Group8      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
 Group7      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
 Group6      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
 Group5      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
 Group4      VARBINARY(2) NOT NULL, -- both
 Group3      VARBINARY(2) NOT NULL, -- both
 Group2      VARBINARY(2) NOT NULL, -- both
 Group1      VARBINARY(2) NOT NULL, -- both
 Network     TINYINT      NULL
);

ALTER TABLE [IPAddress]
 ADD CONSTRAINT [PK_IPAddress]
 PRIMARY KEY CLUSTERED
 (IPAddressID ASC)
 WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE);

CREATE NONCLUSTERED INDEX [IX_IPAddress_Groups]
 ON [IPAddress] (Group1 ASC, Group2 ASC, Group3 ASC, Group4 ASC,
        Group5 ASC, Group6 ASC, Group7 ASC, Group8 ASC, Network ASC)
 WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE);

筆記：

  • 我知道您計劃將BIGINT其用於 ID 欄位，但您真的希望擷取超過 4,294,967,295 個唯一值嗎？如果是這樣，那麼只需將欄位更改為 BIGINT，然後您甚至可以將種子值更改為 0。但否則，您最好使用 INT 並從最小值開始，這樣您就可以利用該數據類型的整個範圍.
  • 如果需要，您可以將一個或多個 NONpersisted Computed Columns 添加到此表以返回 IPAddress 的文本表示。
  • Group* 欄位在表中從 8 到 1 有目的地排列，以便按預期SELECT *順序返回欄位。但是索引使它們從 1上升到 8，因為它們是這樣填寫的。
  • 以文本形式表示值的計算列的範例（未完成）是：
ALTER TABLE [IPAddress]
 ADD TextAddress AS (
IIF([Group8] IS NULL,
   -- IPv4
   CONCAT(CONVERT(TINYINT, [Group4]), '.', CONVERT(TINYINT, [Group3]), '.',
     CONVERT(TINYINT, [Group2]), '.', CONVERT(TINYINT, [Group1]),
     IIF([Network] IS NOT NULL, CONCAT('/', [Network]), '')),
   -- IPv6
   LOWER(CONCAT(
     CONVERT(VARCHAR(4), [Group8], 2), ':', CONVERT(VARCHAR(4), [Group7], 2), ':',
     CONVERT(VARCHAR(4), [Group6], 2), ':', CONVERT(VARCHAR(4), [Group5], 2), ':',
     CONVERT(VARCHAR(4), [Group4], 2), ':', CONVERT(VARCHAR(4), [Group3], 2), ':',
     CONVERT(VARCHAR(4), [Group2], 2), ':', CONVERT(VARCHAR(4), [Group1], 2),
     IIF([Network] IS NOT NULL, CONCAT('/', [Network]), '')
    ))
  ) -- end of IIF
);

測試：

INSERT INTO IPAddress VALUES (127, 0, 0, 0, 4, 22, 222, 63, NULL); -- IPv6
INSERT INTO IPAddress VALUES (27, 10, 1234, 0, 45673, 200, 1, 6363, 48); -- IPv6
INSERT INTO IPAddress VALUES (NULL, NULL, NULL, NULL, 192, 168, 2, 63, NULL); -- v4
INSERT INTO IPAddress VALUES (NULL, NULL, NULL, NULL, 192, 168, 137, 29, 16); -- v4

SELECT [IPAddressID], [Group8], [Group1], [Network], [TextAddress]
FROM IPAddress ORDER BY [IPAddressID];

結果：

IPAddressID   Group8   Group1   Network  TextAddress
-----------   ------   ------   -------  ---------------------
-2147483646   0x007F   0x003F   NULL     007f:0000:0000:0000:0004:0016:00de:003f
-2147483645   0x001B   0x18DB   48       001b:000a:04d2:0000:b269:00c8:0001:18db/48
-2147483644   NULL     0x003F   NULL     192.168.2.63
-2147483643   NULL     0x001D   16       192.168.137.29/16

越小總是越快。使用較小的值，您可以將更多的值放入單個頁面中，因此 IO 更少，可能更淺的 B-Tree 等。

當然，所有其他事情（翻譯成本、可讀性、兼容性、CPU 負載、索引可搜尋性等）都是相同的。

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

相關問答

Sql-Server

對於購物項目中的使用者表，哪種方式更好 - SQL Server

  • September 12, 2021
檢視問答
Sql-Server

SQL 數據庫的大小要求

  • February 24, 2021
檢視問答
Sql-Server

開發人員的查詢在自定義數據庫中表現很差

  • December 3, 2020
檢視問答
Sql-Server

隨時間推移預測查詢性能的合理方法

  • November 16, 2020
檢視問答
Sql-Server

列儲存索引中列的順序是否重要？

  • October 26, 2020
檢視問答
Sql-Server

如何知道 SQL Server 數據庫現在的健康程度？

  • September 30, 2020
檢視問答