Przechowywanie adresów IP - varchar (45) vs varbinary (16)

Mam zamiar stworzyć tabelę z dwoma polami - IDjako BIGINTi IPAddressjako albo varchar(45)albo varbinary(16). Chodzi o to, aby przechowywać wszystkie unikalne adresy IP i IDzamiast tego używać referencji IP addressw innych tabelach.

Zasadniczo zamierzam utworzyć procedurę składowaną, która zwraca wartość IDdla podanego IP addresslub (jeśli adres nie został znaleziony) wstawić adres i zwrócić wygenerowany ID.

Oczekuję, że będę mieć wiele rekordów (nie mogę dokładnie powiedzieć, ile), ale potrzebuję, aby powyższa procedura przechowywana została wykonana tak szybko, jak to możliwe. Zastanawiam się więc, jak przechowywać rzeczywisty adres IP - w formacie tekstowym lub bajtowym. Które będzie lepsze?

Napisałem już SQL CLRfunkcje do przekształcania bajtów adresu IP na ciąg znaków i odwrotnie, więc transformacja nie stanowi problemu (praca z oboma IPv4i IPv6).

Myślę, że muszę utworzyć indeks, aby zoptymalizować wyszukiwanie, ale nie jestem pewien, czy powinienem dołączyć IP addresspole do indeksu klastrowanego, czy utworzyć osobny indeks i przy jakim typie wyszukiwanie będzie szybsze?

jak zapisać rzeczywisty adres IP - w formacie tekstowym lub bajtowym. Które będzie lepsze?

Ponieważ „tekst” tutaj odnosi się do VARCHAR(45)„bajtów” VARBINARY(16), powiedziałbym: ani .

Biorąc pod uwagę następujące informacje (z artykułu Wikipedii na temat IPv6 ):

Reprezentacja adresu
128 bitów adresu IPv6 jest reprezentowanych w 8 grupach po 16 bitów. Każda grupa jest zapisana jako 4 cyfry szesnastkowe, a grupy są oddzielone dwukropkami (:). Adres 2001: 0db8: 0000: 0000: 0000: ff00: 0042: 8329 jest przykładem tej reprezentacji.

Dla wygody adres IPv6 można skracać do krótszych notacji, stosując w miarę możliwości następujące reguły.

• Jedno lub więcej zer wiodących z dowolnej grupy cyfr szesnastkowych jest usuwanych; jest to zwykle wykonywane dla wszystkich zer lub na początku zer. Na przykład grupa 0042 jest konwertowana na 42.
• Kolejne sekcje zer są zastępowane podwójnym dwukropkiem (: :). Dwukropek może być użyty tylko raz w adresie, ponieważ wielokrotne użycie spowodowałoby, że adres byłby nieokreślony. RFC 5952 zaleca, aby nie używać podwójnego dwukropka do oznaczenia pominiętej pojedynczej sekcji zer. [41]

Przykład zastosowania tych zasad:

        Adres początkowy: 2001: 0db8: 0000: 0000: 0000: ff00: 0042: 8329
        Po usunięciu wszystkich wiodących zer w każdej grupie: 2001: db8: 0: 0: 0: ff00: 42: 8329
        Po pominięciu kolejnych odcinków zer: 2001 : db8 :: ff00: 42: 8329

Zacznę od użycia 8 VARBINARY(2)pól do reprezentowania 8 grup. Pola dla grup 5-8 powinny być takie, NULLjak będą używane tylko dla adresów IPv6. Pola dla grup 1–4 powinny być NOT NULLtakie, jakie będą używane zarówno dla adresów IPv4, jak i IPv6.

Utrzymując każdej niezależnej grupy (w przeciwieństwie do ich łączenia się albo VARCHAR(45)albo VARBINARY(16)albo nawet dwa BIGINTpola) masz dwie główne korzyści:

1. Znacznie łatwiej jest zrekonstruować adres w dowolnej reprezentacji. W przeciwnym razie, aby zamienić kolejne grupy zer na (: :), musiałbyś to parsować. Zachowanie ich osobno pozwala na proste IF/ IIF/ CASEstwierdzenia, aby to ułatwić.
2. Zaoszczędzisz mnóstwo miejsca na IPv6 poprzez umożliwienie albo ROW COMPRESSIONalbo PAGE COMPRESSION. Ponieważ oba typy KOMPRESJI pozwolą na pola, które 0x00zajmą 0 bajtów, wszystkie te grupy zer nie będą cię teraz nic kosztować. Z drugiej strony, jeśli zapisałeś przykładowy adres z góry (w cytacie z Wikipedii), wówczas 3 zestawy wszystkich zer w środku zajmowałyby ich pełną ilość miejsca (chyba że robiłeś to VARCHAR(45)i poszedłeś ze zmniejszoną notacją , ale może to nie działać dobrze w przypadku indeksowania i wymaga specjalnego analizowania w celu zrekonstruowania go do pełnego formatu, więc załóżmy, że nie jest to opcja ;-).

JEŚLI musisz przejąć Sieć, utwórz TINYINTpole dla tego, co nazywa się, um, [Network]:-)

Aby uzyskać więcej informacji na temat wartości sieci, oto informacje z innego artykułu w Wikipedii na temat adresu IPv6 :

Sieci

Sieć IPv6 wykorzystuje blok adresów, który jest ciągłą grupą adresów IPv6 o wielkości, która jest potęgą dwóch. Wiodący zestaw bitów adresów jest identyczny dla wszystkich hostów w danej sieci i jest nazywany adresem sieci lub prefiksem routingu .

Zakresy adresów sieciowych są zapisywane w notacji CIDR. Sieć jest oznaczona pierwszym adresem w bloku (kończącym się wszystkimi zerami), ukośnikiem (/) i wartością dziesiętną równą rozmiarowi w bitach prefiksu. Na przykład sieć zapisana jako 2001: db8: 1234 :: / 48 zaczyna się pod adresem 2001: db8: 1234: 0000: 0000: 0000: 0000: 0000 i kończy się w 2001: db8: 1234: ffff: ffff: ffff: ffff : ffff.

Prefiks routingu adresu interfejsu może być bezpośrednio wskazany adresem za pomocą notacji CIDR. Na przykład konfiguracja interfejsu o adresie 2001: db8: a :: 123 podłączonego do podsieci 2001: db8: a :: / 64 jest zapisana jako 2001: db8: a :: 123/64.

Do indeksowania powiedziałbym, że utwórz indeks nieklastrowany na 8 polach grupy i ewentualnie polu sieci, jeśli zdecydujesz się go dołączyć.

Wynik końcowy powinien wyglądać następująco:

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);

Uwagi:

• Rozumiem, że planujesz używać BIGINTpola ID, ale czy naprawdę spodziewasz się przechwycić ponad 4 294 967 295 unikalnych wartości? Jeśli tak, to po prostu zmień pole na DUŻE, a następnie możesz nawet zmienić wartość początkową na 0. Ale w przeciwnym razie lepiej jest użyć INT i zacząć od wartości minimalnej, abyś mógł korzystać z całego zakresu tego typu danych .
• W razie potrzeby możesz dodać do tej tabeli co najmniej jedną kolumnę obliczeniową NONpersisted, aby zwrócić tekstowe reprezentacje adresu IP.
• Pola Grupy * są ułożone celowo w dół , od 8 do 1, w tabeli, aby wykonanie SELECT *zwróciło pola w oczekiwanej kolejności. Ale indeks ma ich wzrost , od 1 do 8, ponieważ w ten sposób są wypełniane.

• Przykładem (niedokończona) kolumny obliczanej do reprezentowania wartości w formie tekstowej jest:

  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
  );

  Test:

  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];

  Wynik:

  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

Mniejsze zawsze będą szybsze. Przy mniejszych wartościach możesz zmieścić więcej z nich na jednej stronie, dlatego mniej IO, potencjalnie płytsze B-Drzewa itp.

Oczywiście wszystkie inne rzeczy (koszty ogólne tłumaczenia, czytelność, kompatybilność, obciążenie procesora, indeksowalność itp.) Są równe.