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.
Notem que o objetivo deste artigo, é na verdade, de alertar para o uso indevido e/ou inadequado de tipos de dados
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’);
mysqll>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.
Visite nosso Blog
Saiba mais sobre bancos de dados
Aprenda sobre monitoramento com ferramentas avançadas
Tem dúvidas sobre nossos serviços? Acesse nosso FAQ
Quer ver como ajudamos outras empresas? Confira o que nossos clientes dizem nesses depoimentos!
Conheça a História da HTI Tecnologia
