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.