python - recomendaciones - porque se dañan las tablas en mysql

Cómo almacenar una IP en mySQL

Question

Tenemos un debate saludable en la oficina esta semana. Estamos creando un Db para almacenar información de proxy, en su mayor parte tenemos el esquema resuelto, excepto cómo debemos almacenar las direcciones IP. Un campo quiere usar 4 smallints, uno para cada octeto y el otro quiere usar 1 int grande, INET_ATON.

Estas tablas serán enormes, por lo que el rendimiento es clave. Estoy en el medio aquí ya que normalmente uso MS SQL y 4 pequeños ints en mi mundo. No tengo suficiente experiencia con este tipo de almacenamiento de volumen de direcciones IP.

Usaremos scripts de Perl y Python para acceder a la base de datos y así normalizar aún más los datos en otras tablas para los mejores habladores, tráfico interesante, etc.

Estoy seguro de que hay algunos aquí en la comunidad que han hecho algo similar a lo que estamos haciendo y estoy interesado en conocer sus experiencias y qué ruta es la mejor, 1 gran int, o 4 entradas pequeñas para direcciones IP.

EDITAR - Una de nuestras preocupaciones es el espacio, esta base de datos va a ser enorme como en 500,000,000 de registros por día. Así que estamos tratando de sopesar el problema del espacio junto con el problema de rendimiento.

EDIT 2 Parte de la conversación ha cambiado al volumen de datos que vamos a almacenar ... esa no es mi pregunta. La pregunta es cuál es la mejor forma de almacenar una dirección IP y por qué. Como dije en mis comentarios, trabajamos para una gran compañía de Fortune 50. Nuestros archivos de registro contienen datos de uso de nuestros usuarios. Estos datos, a su vez, se usarán dentro de un contexto de seguridad para conducir algunas métricas y manejar varias herramientas de seguridad.

Answer 1

A BIGINT tiene 8 bytes en MySQL .

Para almacenar direcciones IPv4 , UNSINGED INT un UNSINGED INT , que creo que es lo que debes usar.

No puedo imaginar un escenario en el que 4 octetos obtengan más rendimiento que un INT único, y el último es mucho más conveniente.

También tenga en cuenta que si va a emitir consultas como esta:

SELECT *
FROM ips
WHERE ? BETWEEN start_ip AND end_ip

, donde start_ip y end_ip son columnas en su tabla, el rendimiento será pobre.

Estas consultas se utilizan para averiguar si una IP dada se encuentra dentro de un rango de subredes (generalmente para prohibirla).

Para que estas consultas sean eficientes, debe almacenar todo el rango como un objeto LineString con un índice LineString y hacer una consulta como esta:

SELECT *
FROM ips
WHERE MBRContains(?, ip_range)

Vea esta entrada en mi blog para más detalles sobre cómo hacerlo:

• Prohibición de IP

Answer 2

Sugeriría ver qué tipo de consultas ejecutará para decidir qué formato adoptará.

Solo si necesita extraer o comparar octetos individuales, debería considerar dividirlos en campos separados.

De lo contrario, guárdelo como un entero de 4 bytes. Eso también tiene la ventaja de permitirle usar las INET_ATON() incorporadas de MySQL INET_ATON() e INET_NTOA() .

Rendimiento vs. Espacio

Almacenamiento:

Si solo va a admitir direcciones IPv4, su tipo de datos en MySQL puede ser UNSIGNED INT que solo usa 4 bytes de almacenamiento.

Para almacenar los octetos individuales, solo necesitaría usar tipos de datos SMALLINTS NO SMALLINTS , no SMALLINTS , que usarían hasta 1 byte cada uno de almacenamiento.

Ambos métodos usarían un almacenamiento similar con tal vez un poco más para campos separados para algunos gastos generales.

Más información:

• Descripción general de tipo numérico
• Tipos enteros (valor exacto) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

Actuación:

Usar un solo campo rendirá mucho mejor rendimiento, es una comparación única en lugar de 4. Usted mencionó que solo ejecutará consultas contra toda la dirección IP, por lo que no debería haber necesidad de mantener los octetos separados. El uso de las funciones INET_* de MySQL hará la conversión entre el texto y las representaciones enteras una vez para la comparación.

Answer 3

Tener campos separados no me parece particularmente sensato, como dividir un código postal en secciones o un número de teléfono.

Puede ser útil si desea información específica sobre las secciones, pero no veo ninguna razón real para no utilizar un int de 32 bits.

Answer 4

Transformación eficiente de ip a int e int a ip (podría serle útil): (PERL)

sub ip2dec {
my @octs = split //./,shift;
return ($octs[0] << 24) + ($octs[1] << 16) + ($octs[2] << 8) + $octs[3];
}
sub dec2ip {
my $number = shift;
my $first_oct = $number >> 24;
my $reverse_1_ = $number - ($first_oct << 24);
my $secon_oct = $reverse_1_ >> 16;
my $reverse_2_ = $reverse_1_ - ($secon_oct << 16);
my $third_oct = $reverse_2_ >> 8;
my $fourt_oct = $reverse_2_ - ($third_oct << 8);
return "$first_oct.$secon_oct.$third_oct.$fourt_oct";
}

Answer 5

Use PostgreSQL, hay un tipo de datos nativo para eso.

Más en serio, caería en el campo de "un entero de 32 bits". Una dirección IP solo tiene sentido cuando los cuatro octetos se consideran juntos, por lo que no hay razón para almacenar los octetos en columnas separadas en la base de datos. ¿Almacenarías un número de teléfono usando tres (o más) campos diferentes?