Optimiser le stockage des adresses IP dans MySQL
C’est l’été, le mois d’août arrive, de manière générale les cadences et les volumes de travaillent ralentissent un peu pour la période estivale. C’est le moment parfait pour démarrer des travaux de refactoring où pour travailler sur des tâches pour lesquels vous n’arrivez jamais à dégager du temps ! Si au début de la semaine j’évoquais comment optimiser le stockage de vos UUID, évoquons comme optimiser et simplifier la gestion des adresses IP dans MySQL.
Pour la plupart d’entre nous, nous allons stocker les adresses IP sous la forme d’une chaîne de 15 caractères maximums dans le cas d’une adresse IPv4 et de 39 caractères pour une adresse de type IPv6. Pour la majorité de nos besoins cela est pleinement suffisant, nous effectuons en réalité assez peu de traitements sur ces dernières hormis de la consultation de données. Mais comme toujours une donnée stockée sous une forme textuelle est quelque chose de couteux aussi bien en terme de stockage mais également en terme de performance. De plus cela peut rendre vos requêtes de sélection assez complexe (voir impossible) à écrire.
Bien heureusement pour nous, les moteurs de base de données fournissent généralement des outils pour optimiser. Mais si PostgresSQL fournit un type inet permettant de gérer une donnée de type IPv4 ou IPv6, MySQL ne possède pas d’une gestion de type aussi évoluée. Néanmoins, MySQL met à disposition de ces utilisateurs 4 fonctions permettant de travailler avec des adresses IP: INET_ATON(), INET_NTOA(), INET6_ATON() et INET6_ATON().
Les méthodes INET_ATON() et INET6_ATON() vont permettre de transformer respectivement une adresse IPv4 et IPv6 d’une forme textuelle en un entier ou sous la forme d’une chaîne binaire qui représentera cette dernière sous forme numérique. Vous l’aurez donc deviné, les méthodes INET_NTOA() et INET6_ATON() vont procéder à l’opération inverse, c’est-à-dire prendre la forme numérique d’une adresse IP pour la convertir en forme textuelle. C’est ainsi qu’il est possible d’optimiser le stockage et améliorer la performance de vos requêtes, car effectuer une recherche numérique est plus rapide que de faire une recherche full text.
Un autre avantage de cette solution est que bien que les adresses IP soient transformées, la transformation conserve l’ordre de grandeur des données. Par exemple, si je veux trouver toutes les lignes de ma base qui sont comprises entre l’adresse 8.8.8.4 et 8.8.8.8, je pourrais très bien écrire la requête suivante : SELECT * FROM table WHERE ip BETWEEN INET_ATON('8.8.8.4') AND INET_ATON('8.8.8.8').
Et parce que nous sommes nombreux à utiliser Doctrine pour gérer nos interactions avec la base de données, il existe de nombreuses bibliothèques permettant d’ajouter le support de ces fonctions. Pour ma part j’utilise le plus régulièrement beberlei/DoctrineExtensions.
Et si vous préférez gérer cette logique côté PHP, le langage possède ses propres fonctions permettant d’effectuer ces conversions de type de données. Il met à disposition des développeurs les fonctions ip2long et long2ip pour les adresses IPv4, ainsi que inet_pton et inet_ntop pour les IPv6.