Armazenando endereço IP como inteiro

Posted by Bento | Posted in Artigos, Banco de Dados, MariaDB, MySQL | Posted on 26-12-2010-05-2008

4

Navegando na internet encontrei este artigo que mostra como otimizar o armazenamento do IP como inteiro, e alertar para o uso inadequado de tipos de dados.
O artigo é bem simples e fácil de entender.

====== post original ======
Neste artigo explicarei qual a principal vantagem de armazenarmos endereços IP com o tipo de dados “inteiro” ao invés do tradicional “char/varchar”. E isto pode servir de exemplo para outros campos/colunas. Para que você possa entender melhor vamos criar uma tabela simples de forma que possamos armazenar algum endereço IP utilizando o tipo CHAR.

mysql> CREATE DATABASE exemplo;
mysql> USE exemplo;
 
mysql> CREATE TABLE log
           (
            id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            usuario CHAR(30),
            visitas INT,
            ip CHAR(15)
           );

Vamos inserir alguns registros nesta tabela.

mysql> INSERT INTO log VALUES(null, 'douglas', 10, '192.168.10.10');
mysql> INSERT INTO log VALUES(null, 'luciana', 10, '200.213.162.161');
mysql> INSERT INTO log VALUES(null, 'alberto', 10, '213.187.0.1');

Bem, por enquanto temos três registros em nossa tabela, começaremos nossa primeira análise.

[Leia Mais]

Qual o tamanho ocupado pela coluna IP, quanto ela custa efetivamente?

Utilizando-se a função LENGTH teremos o espaço (em bytes) utilizado por cada dígito/caractere baseado no CHARSET:

mysql> SELECT LENGTH(IP) FROM log;
+-----------------+
| LENGTH(IP) |
+-----------------+
|                   13 |
|                   15 |
|                   11 |
+-----------------+
3 rows in set (0.05 sec)

O custo total de cada coluna é de 15 bytes, pois estamos usando CHAR que sempre completa com espaços à direita até preencher todas as posições da coluna. Se fosse VARCHAR, teríamos 13+1 bytes, 15+1 bytes, e, 11+1 bytes. Visto que o custo do VARCHAR é a quantidade de caracteres “inputados” + 1 byte.

Armazenar endereços IP com tipos “strings” temos uma maior custo de armazenamento em disco e também em memória, mas entãoo como podemos economizar utilizando inteiros? Devido a forma e custo de armazenamento!

No MySQL/MariaDB podemos utilizar duas funções para este tipo de operação INET_ATON e INET_NTOA, o primeiro transforma o endereço em inteiro e o último realiza a operação reversa.

mysql> SELECT INET_ATON('192.168.0.1');
+-------------------------------------+
| INET_ATON('192.168.0.1') |
+-------------------------------------+
|                         3232235521 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT INET_NTOA(3232235521);
+------------------------------------+
| INET_NTOA(3232235521) |
+------------------------------------+
| 192.168.0.1                        |
+-----------------------------------+
1 row in set (0.00 sec)

Economia em memória/disco:

Criamos duas tabelas, a primeira como a mostrada anteriormente, sendo o campo IP com o tipo de dados CHAR, a segunda tabela criada com o campo IP como INT, ambas tabelas utilizando o storage MARIA (ou MyISAM se preferir), inseri, em ambas ,500 mil linhas. Vejamos os números que eu consegui:

tabela com campo IP como CHAR: data_size = 31.87Mb
tabela com campo IP como INT: data_size = 16.17Mb

A economia em disco foi de 51% utilizando o campo IP como INT.

E a performance ? Será que melhorou ?
Vamos utilizar uma consulta, extremamente, simples fazendo apenas um count(*) em ambas as tabelas.

mysql> SELECT BENCHMARK(1000000, (SELECT COUNT(*) FROM log ORDER BY IP DESC));
+------------------------------------------------------------------------------------------------------+
| BENCHMARK(1000000, (SELECT COUNT(*) FROM log ORDER BY IP DESC)) |
+------------------------------------------------------------------------------------------------------+
|                                                                                                          0 |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.27 sec)
mysql> SELECT BENCHMARK(1000000, (SELECT COUNT(*) FROM log2 ORDER BY IP DESC));
+--------------------------------------------------------------------------------------------------------+
| BENCHMARK(1000000, (SELECT COUNT(*) FROM log2 ORDER BY IP DESC)) |
+--------------------------------------------------------------------------------------------------------+
|                                                                                                            0 |
+--------------------------------------------------------------------------------------------------------+
1 row in set (0.15 sec)

Bem, acho que isso já nos dá uma idéia de como escolher o tipo de dados corretamente pode influenciar muito na performance e utilização do disco/memória pelo nosso banco de dados.

 

Comments posted (4)

Ótimo post Fabiano, era exatamente o que eu estava procurando, testes com os dois tipos de dados e resultados comprovados!

Muito obrigado!

Fabiano, tentei colocar esse processo dentro de uma TRIGGER porem não tive sucesso:

select name, INET_NTOA(ip) from usersdelimiter //
CREATE TRIGGER trg_ip BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SET @ip = NEW.ip;
SET NEW.ip = INET_ATON(@ip);
END//

Quando eu insiro o valor o MySQL salva o campo IP como nulo, vc já passou por isso?

Postei errado a TRIGGER!
Segue a correção:

delimiter //
CREATE TRIGGER trg_ip BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SET @ip = NEW.ip;
SET NEW.ip = INET_ATON(@ip);
END//

Bento,
muito bom o artigo, mas por favor, sobre o size todos os IPv4 convertidos cabem no tipo INT do MySql? ou é melhor utilizar LONGINT?

Valeu!

Write a comment