[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.

  • Waar gebruik je de IP-adressen voor? En zijn deze noodzakelijk?


    Als je namelijk een oplossing hebt waarbij je niet afhankelijk bent van IP-adressen, dan heb je dit probleem in eerste instantie niet.


    En hoe speelt snelheid hier een (cruciale?) rol? Je kunt toch gewoon een index aanmaken op een kolom?

  • 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:
    https://dev.mysql.com/doc/refm….html#function_inet6-aton


    Op die pagina:



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



    SQL
    SELECT HEX(INET6_ATON('IP'));
    
    
    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.

  • Proof of concept / test.

    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.

  • Ik heb geen probleem ik heb een vraag, ik hoef daarvoor geen details te geven over een project. Het gaat om het type opslag en of ik hiermee performance verlies bij meerdere data verwerking. Dat jij jezelf zo kinderachtig opstelt zegt genoeg over jou, ik weet voldoende stel me vragen voortaan ergens anders.



    Onderwerp mag gesloten worden, bedank.

  • Ik heb geen probleem ik heb een vraag, ik hoef daarvoor geen details te geven over een project. Het gaat om het type opslag en of ik hiermee performance verlies bij meerdere data verwerking.

    Zie mijn laatste reply, daarin zit een hoop voorbeeldcode waarmee je wel vooruit kunt denk ik.


    Met de reactie daarvoor wilde ik alleen aangeven dat het moeilijk (zo niet onmogelijk) is om een beeld te vormen van een optimale aanpak voor het gevraagde, en ook om de absurditeit van hoe je dit vraagt te benadrukken. Je geeft een enkel puzzelstuk, en verwacht dan dat we kunnen vertellen hoe de hele legpuzzel er uit dient te zien?


    Daarna probeer ik die stelling verder te onderbouwen door aan te geven dat de database (wat in feite het fundament vormt voor je applicatie) in dienst staat van de applicatie, en dus hier ook op moet zijn afgestemd.


    Als je niet uitlegt hoe de applicatie (technisch) werkt, dan wordt het ook vrij lastig om een database op de goede manier in te richten.


    Met mijn "kinderachtige opstelling" hield ik in zekere zin ook een spiegel voor, maar dat is je waarschijnlijk ontgaan. Je het over een "uniek concept" alsof je the next best thing since sliced bread hebt uitgevonden. Als je dan toch op de kip met de gouden eieren denkt te zitten, waarom stap je dan niet naar een bureau om dat concept uit te werken als het je aan technische kennis ontbreekt? Ah, I see.


    Dat jij jezelf zo kinderachtig opstelt zegt genoeg over jou, ik weet voldoende stel me vragen voortaan ergens anders.

    K bye.

Participate now!

Heb je nog geen account? Registreer je nu en word deel van onze community!