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.
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.
Ó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!
Bacana essas funções…
Eu estou implementando um software para armazenar os IPs de uma rede, para saber à que IP pertence determinado MAC e gravar o nome do equipamento, etc (para fins de organização interna) e queria saber como usar essas duas funções para capturar o IP, convertê-lo em número inteiro e armazenar no banco de dados.
Se eu fizesse uma busca, convertendo o IP, seria algo como: SELECT * FROM IP INET_ATON(IPv4)? onde IPv4 é o campo que armazena o IP como 192.168.0.1…
Olá José,
para obter o IP poderia usar o comando $_SERVER[‘REMOTE_ADDR’], basta adaptar ao seu código de INSERT.