[SQL] IP-adressen

    ICTscripters maakt gebruik van cookies. Door het gebruiken en browsen naar onze site gaat je automatisch akkoord met het gebruik van cookies. Klik hier voor meer informatie

    • [SQL] IP-adressen

      Goedemiddag,

      Voor een project waar ik in me vrije tijd aan werk krijg ik straks te maken met veel IP-adressen, dit kunnen dus IPv4 & IPv6 adressen zijn. Omdat ik dit vooraf niet kan voorspellen en geen omweg wil gaan maken voor de opslag zoek ik een correcte en snelle manier van IP verwerking.

      Zelf al wat rond gezocht op Stackoverflow.com en kwam hierop uit op 3 opties, heb met beide alleen geen ervaring hoop dat ik hier wat duidelijkheid kan krijgen daarover.

      Manier 1:
      INET_ATON('127.0.0.1') < Deze ondersteund alleen geen IPv6 wat ik ervan begrijp.

      Manier 2:
      INET6_ATON('127.0.0.1') < Deze ondersteund beide IP versies zover ik begreep.

      Manier 3:
      VARBINARY() < Deze heb ik in het verleden al eens gebruikt dus weet dat dit wel werkt maar is bovenstaand sneller?


      Welke manier van verwerking en opvragen zou het snelste werken van de 3 manieren? Op kleine schaal ga ik niet veel merken maar omdat ik veel data verwacht op kort termijn wil ik me alvast voorbereiden en direct goed doen i.p.v. achteraf weer omgooien.

      p.s.
      Project geef ik geen details over vrij, het is een vrij uniek concept, ben alleen naar hulp voor bovenstaand opzoek.
    • Als je de MySQL page er op na lijst is de functie zoals jij deze aangeeft voor beide een goede combo voor te maken.

      Linkje:
      dev.mysql.com/doc/refman/5.7/e….html#function_inet6-aton

      Op die pagina:

      SQL-Query

      1. SELECT INET6_NTOA(INET6_ATON('fdfe::5a55:caff:fefa:9089'));
      2. -> 'fdfe::5a55:caff:fefa:9089'
      3. SELECT INET6_NTOA(INET6_ATON('10.0.5.9'));
      4. -> '10.0.5.9'
      5. SELECT INET6_NTOA(UNHEX('FDFE0000000000005A55CAFFFEFA9089'));
      6. -> 'fdfe::5a55:caff:fefa:9089'
      7. SELECT INET6_NTOA(UNHEX('0A000509'));
      8. -> '10.0.5.9'
      Laat alles zien


      Mocht je er maar een veld voor hebben kan je vrij gemakelijk een CASE WHEN statement invoeren. En dat zou ook niet hoeven:


      SQL-Query

      1. SELECT HEX(INET6_ATON('IP'));
      2. SELECT INET6_NTOA(UNHEX('IP'));

      Kan je de IP's als HEX opslaan scheelt ook weer ruimte in je database. Wil je het plain op slaan dan verwijder je de HEX / UNHEX.
      Echter zal je altijd met een INT of een varbinary field moeten werken. Met hex values kan je iets makkelijker overweg.

      Overigens een index maken op een IP field lijkt me niet echt de meest slimme keus in je database design. Zou ik zelf eerder voor een foreign key relatie met een andere tabel maken (mocht het IP uniek moeten zijn per user bijvoorbeeld). Of een clusted index maken in geval de tabel op zich zelf moet staan.
    • @FangorN
      Zoals eerder aangegeven ga ik niet in op details over het project, hoe groot of klein het doel is ik wil het goed zetten en klaarstomen voor groter verkeer. Zo hoef ik me daar later geen zorgen meer over te maken wanneer een spontane groei eruit komt, dan is alles al klaar voor de groei hiervan.

      @Patrick
      Dankjewel, weet jij of de INET6_* ook sneller is dan de varbinary? Heb nooit net INET_ gewerkt dus ben er niet bekend mee of het daadwerkelijk sneller is bij het verwerken van meer data.
    • varbinary is een algemeen opslag veld zolang je er geen index op gaat zetten ga je geen performance issue's krijgen. Then again als je er wel een index op zet en dan een clusted dan zal je performance issues never nooit niet merken. (Tot je over de 50mil+ gaat dan wellicht een 0.1s delay).

      Het gaat vooral om je query die er omheen zit wat de snelheid bepaald van de query of er en een table scan uitgevoerd moet worden of niet. Dus het zal heel erg afhangen ook van de rest van je DB hoe de performance zal zijn.
    • "Hoi ik wil niets uitleggen over mijn probleem maar ik wil wel de beste oplossing, kan iemand mij helpen?"

      Wat bedoel je met "verwerken" en hoe vaak gebeurt dit? Als het niet zo vaak voorkomt dat gegevens worden ingevoerd dan mogen deze toevoeg-operaties best wat duurder zijn. Als deze gegevens vervolgens heel vaak worden opgevraagd is het belangrijk dat deze snel uitgelezen kunnen worden.

      Vervolgens bepaalt de manier van gebruik van deze IP's hoe je deze het beste kunt optimaliseren, hier is geen universeel recept voor.

      Oftewel: een optimale afstemming van tabellen hangt van het gebruik af (wat doe je met deze informatie), en dit wordt weer bepaald door het gedrag / de werking van de applicatie (hoe gebruik je deze in je applicatie)... waar jij niets over wilt vertellen. Dat wordt dan knap lastig.

      On a side note: functies (in MySQL) toepassen op geïndexeerde kolommen, ik denk niet dat dat de snelheid ten goede komt (edit: als je dat in condities gebruikt, zou dat dan niet inhouden dat er toch eerdergenoemde tablescans worden uitgevoerd omdat gekeken moet worden of het resultaat van het toepassen van zo'n functie het gewenste resultaat oplevert?). Wat je wel zou kunnen doen is deze vertalingen uitvoeren in PHP, en zo wegschrijven in de database, en vervolgens zou je queries kunnen uitvoeren op deze geëncodeerde (en geïndexeerde) kolommen. Maar hier geldt weer dat dit sterk afhangt van hoe je deze data gaat gebruiken.

      Misschien is het voornaamste probleem wel dat je meet met twee maten, zoals @Patrick aangeeft kan INET6_ATON(expr) hier uitkomst bieden. Voor het terugvertalen in PHP zijn hier opties voor (blijkbaar is inet_ntop() (PHP) niet compatibel met INET6_ATON() (MySQL)). Ik zou dan denk ik wel deze functies alleen aan de PHP-zijde gebruiken, en het gebruik van functies in MySQL -in ieder geval bij het opvragen van informatie- vermijden.

      tl;dr omdat je niet uitlegt hoe iets zou moeten werken, is het ook vrij onmogelijk om hier een optimale aanpak bij te verzinnen. Mogelijk heb je toch iets aan bovenstaande opmerkingen.

      Post werd 1x aangepast, het laatst door FangorN ().

    • Proof of concept / test.

      PHP Source Code

      1. <?php
      2. ini_set('display_errors', 'stdout');
      3. error_reporting(E_ALL);
      4. header('Content-Type: text/html; charset=UTF-8');
      5. function escape($in) {
      6. return htmlspecialchars($in, ENT_QUOTES, 'UTF-8');
      7. }
      8. // @see http://php.net/manual/en/function.inet-ntop.php#117398
      9. // PHP equivalent of MySQLs INET6_ATON(), because inet_ntop() isn't apparently
      10. /**
      11. * Convert a MySQL binary v4 (4-byte) or v6 (16-byte) IP address to a printable string.
      12. * @param string $ip A binary string containing an IP address, as returned from MySQL's INET6_ATON function
      13. * @return false if not valid.
      14. */
      15. function inet6_ntop($ip) {
      16. $l = strlen($ip);
      17. if ($l == 4 or $l == 16) {
      18. return inet_ntop(pack('A' . $l, $ip));
      19. }
      20. return false;
      21. }
      22. ?>
      23. <!DOCTYPE html>
      24. <html>
      25. <head>
      26. <meta charset="UTF-8">
      27. <title>IP test</title>
      28. </head>
      29. <body>
      30. <?php
      31. $db = new mysqli('127.0.0.1', 'test', 'test', 'test');
      32. $db->set_charset('utf8mb4');
      33. $db->query("CREATE TABLE IF NOT EXISTS `test_ip` (
      34. `ipt_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      35. `ipt_ip` varbinary(16) NOT NULL,
      36. PRIMARY KEY (`ipt_id`)
      37. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
      38. // https://www.randomlists.com/ip-addresses
      39. $testIps = array(
      40. '42a6:4dff:28a7:c07d:3a32:97d8:369b:7277',
      41. 'dc4c:5486:e:3321:caa3:4b09:6dfa:9f7b',
      42. 'e2dc:60f1:43b7:3e3b:a6ad:9423:af5d:68ab',
      43. 'fb6e:a7ef:4e3e:75ce:903b:c444:1ebc:babf',
      44. 'aaa7:bf2a:b443:9fa9:72ee:67de:d1:8805',
      45. '5894:4879:4a27:dd5a:661c:9906:3ced:5163',
      46. '1eb:d630:692a:4e0f:ac14:4bfe:bc87:92d6',
      47. '68f4:1479:3615:353:f70b:7421:12f1:df62',
      48. 'cda0:b4fe:ff76:a8ac:94de:5a91:b1c4:4633',
      49. '7c0a:3f82:5425:3a18:8cfd:8d03:ecb5:f9b1',
      50. '3bf:52c8:b5be:bd43:25c2:43fc:b22d:64e3',
      51. '3218:5c:4250:666b:25d:cb1:f2b0:6774',
      52. '252.228.69.200',
      53. '111.204.105.216',
      54. '182.170.251.175',
      55. '161.210.254.48',
      56. '69.176.138.182',
      57. '200.139.220.243',
      58. '67.95.35.174',
      59. '229.235.136.200',
      60. '151.68.1.236',
      61. '134.112.54.29',
      62. '10.56.42.115',
      63. '203.132.92.142',
      64. );
      65. $db->query('TRUNCATE test_ip');
      66. foreach ($testIps as $ip) {
      67. // unpacked --> packed, store in DB in packed form in VARBINARY(16)
      68. $packedIp = inet_pton($ip); // already compatible with MySQLs INET6_NTOA()
      69. $db->query(
      70. "INSERT INTO test_ip (ipt_ip) VALUES (
      71. '".$db->real_escape_string($packedIp)."'
      72. )"
      73. );
      74. // packed --> unpacked, only for display purposes (human readable form)
      75. $unpackedIp = inet6_ntop($packedIp);
      76. $ok = $ip == $unpackedIp;
      77. echo $ip;
      78. ?> <b style="color: #<?php echo ($ok ? '00cc00' : 'cc0000') ?>;"><?php
      79. echo ($ok ? '' : 'NOT ') ?>OK</b><hr><?php
      80. }
      81. $res = $db->query(
      82. 'SELECT ipt_ip AS packed,
      83. INET6_NTOA(ipt_ip) AS unpacked
      84. FROM test_ip'
      85. );
      86. while ($row = $res->fetch_assoc()) {
      87. $unpackedInSQL = $row['unpacked'];
      88. $unpackedInPHP = inet6_ntop($row['packed']);
      89. echo ($unpackedInSQL == $unpackedInPHP ? 'ok' : 'NOT ok').'<hr>';
      90. }
      91. ?>[end]
      92. </body>
      93. </html>
      Laat alles zien
      Zolang je in de DB enkel werkt met de (rauwe) binaire variant kun je e.e.a. makkelijk optimaliseren.
      Als je iets dan toch in human readable form moet weergeven dan regel je dat aan de PHP-zijde met bovenstaande custom functie (of in een MySQL console met INET6_NTOA()).

      EDIT: codevoorbeeld uitgebreid met database.

      Post werd 4x aangepast, het laatst door FangorN ().