modificar - ¿Cómo almacenar un número de 128 bits en una sola columna en MySQL?

eliminar columna mysql workbench

Question

Estoy cambiando algunas tablas para almacenar direcciones IP como números en lugar de cadenas. Esto es simple con IPv4 donde la dirección de 32 bits puede caber en una columna entera. Sin embargo, una dirección IPv6 es de 128 bits.

La documentación de MySQL solo muestra tipos numéricos de hasta 64 bits ("bigint").

¿Debo seguir con char / varchar para IPv6? (Idealmente, me gustaría usar la misma columna para IPv4 e IPv6, así que prefiero no hacer esto).

¿Hay algo mejor que usar dos columnas bigint? Preferiría no tener que dividir el valor en superior / inferior / 64 siempre que utilice la dirección.

Estoy usando MariaDB 5.1: si hay una mejor solución en una versión posterior de MySQL, sería bueno saberlo, aunque no de manera inmediata.

[EDITAR] Tenga en cuenta que busco una recomendación para la mejor manera de hacer esto; es obvio que existen varias formas de hacerlo (incluida la representación de cadena existente), pero ¿cuál es la mejor (en términos de rendimiento)? (es decir, si alguien ya ha hecho el análisis, eso me ahorraría hacerlo o si me falta algo obvio, también sería bueno saberlo).

Answer 1

Me encontré haciendo esta pregunta y de todas las publicaciones que leí nunca encontré ninguna comparación de rendimiento. Así que aquí está mi intento.

He creado las siguientes tablas, completadas con 2,000,000 de direcciones IP aleatorias de 100 redes aleatorias.

CREATE TABLE ipv6_address_binary (
id SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY,
addr BINARY(16) NOT NULL UNIQUE
);
CREATE TABLE ipv6_address_twobigints (
id SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY,
haddr BIGINT UNSIGNED NOT NULL,
laddr BIGINT UNSIGNED NOT NULL,
UNIQUE uidx (haddr, laddr)
);
CREATE TABLE ipv6_address_decimal (
id SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY,
addr DECIMAL(39,0) NOT NULL UNIQUE
);

Luego SELECTO todas las direcciones ip para cada red y registrar el tiempo de respuesta. El tiempo de respuesta promedio en la tabla de twobigints es de aproximadamente 1 segundo, mientras que en la tabla binaria es de aproximadamente una centésima de segundo.

Aquí están las consultas.

Nota:

X_ [ALTO / BAJO] es el más / menos significativo de 64 bits de X

cuando NETMASK_LOW es 0, la condición AND se omite, ya que siempre da como resultado verdadero. No afecta mucho el rendimiento.

SELECT COUNT(*) FROM ipv6_address_twobigints
WHERE haddr & NETMASK_HIGH = NETWORK_HIGH
AND laddr & NETMASK_LOW = NETWORK_LOW
SELECT COUNT(*) FROM ipv6_address_binary
WHERE addr >= NETWORK
AND addr <= BROADCAST
SELECT COUNT(*) FROM ipv6_address_decimal
WHERE addr >= NETWORK
AND addr <= BROADCAST

Tiempos de respuesta promedio:

Grafico:

BINARY_InnoDB 0.0119529819489
BINARY_MyISAM 0.0139244818687
DECIMAL_InnoDB 0.017379629612
DECIMAL_MyISAM 0.0179929423332
BIGINT_InnoDB 0.782350552082
BIGINT_MyISAM 1.07809265852

Answer 2

Para citar: "¿Has considerado binario (64)"

Almacenando enteros muy grandes en MySQL

Answer 3

Siempre he usado una cadena o dos enteros de 64 bits. El primero en el caso en que solo quiero registrarlo, el último en el caso en que necesito hacer cálculos sobre si una determinada dirección está contenida en una determinada red, o incluso si dos redes se superponen.

Al almacenarlo como un entero, la única opción es de hecho dividirlo en dos números de 64 bits. Como esto hace que las comparaciones sean más engorrosas, no haría esto a menos que necesite cálculos numéricos, para ver si una IP cae dentro de una determinada red.

No me preocuparía demasiado el rendimiento para almacenar direcciones IPv6 en una cadena, dependiendo de la cantidad de búsquedas que realice para los datos. Por lo general, hay muy pocos, o simplemente hay muy pocos datos. Sí, el almacenamiento y las búsquedas son menos eficientes que con los números, pero no es mucho más doloroso que almacenar direcciones de correo electrónico, nombres de personas o nombres de usuarios.

¿Y por qué no podría mezclar IPv4 e IPv6 en campos de cadenas? Son fáciles de distinguir cuando se recupera uno. Su rango de valores posibles no se superponen.

En resumen: use números para verificar las superposiciones, use cadenas en otros lugares. La ineficiencia de las cadenas es irrelevante en comparación con la facilidad de uso.