Как сохранить 128-разрядное число в одном столбце в MySQL?

Я изменяю некоторые таблицы для хранения IP-адресов как чисел, а не строк. Это просто с IPv4, где 32-разрядный адрес может вписываться в целочисленный столбец. Однако адрес IPv6 составляет 128 бит.

Документация MySQL показывает только числовые типы до 64 бит ( "bigint" ).

Должен ли я придерживаться char/varchar для IPv6? (В идеале я хотел бы использовать один и тот же столбец для IPv4 и IPv6, поэтому я бы предпочел не делать этого).

Есть ли что-то лучше, чем использование двух столбцов bigint? Я бы предпочел не разрывать значение в верхнем и нижнем /64 при использовании адреса.

Я использую MariaDB 5.1 - если есть более лучшее решение в более поздней версии MySQL, тогда было бы неплохо узнать, хотя и не сразу.

[EDIT] Обратите внимание, что после рекомендации по наилучшему способу сделать это - очевидно, что существуют различные способы сделать это (включая существующее строковое представление), но это (с точки зрения производительности) лучше всего? (т.е. если кто-то уже сделал анализ, это спасло бы меня от этого, или если я упустил что-то очевидное, это было бы здорово узнать).

Ответы

Ответ 1

Я выяснил, что задаю этот вопрос, и со всех прочитанных мной сообщений никогда не было сопоставлений производительности. Итак, вот моя попытка.

Я создал следующие таблицы, заполненные 2 000 000 случайных IP-адресов из 100 случайных сетей.

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
);

Затем я выбираю все IP-адреса для каждой сети и записываю время ответа. Среднее время отклика в таблице twobigints составляет около 1 секунды, а на бинарной таблице - около одной сотой секунды.

Вот запросы.

Примечание:

X_ [HIGH/LOW] является самым/наименее значимым 64-бит X

когда NETMASK_LOW равно 0, условие AND опущено, поскольку оно всегда возвращает true. не очень влияет на производительность.

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

Среднее время ответа:

Graph:

http://i.stack.imgur.com/5NJvQ.jpg

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

Ответ 2

Я всегда использовал строку или два 64-битных целых числа. Первый в случае, когда я просто хочу его записать, последний в том случае, когда мне нужно делать вычисления о том, содержится ли определенный адрес в определенной сети или даже перекрываются ли две сети.

При сохранении его как целого, единственная опция - это разделить его на два 64-битных номера. Поскольку это делает сравнение более громоздким, я бы этого не сделал, если вам не нужны численные вычисления, чтобы увидеть, попадает ли IP в определенную сеть.

Я бы не слишком беспокоился о производительности для хранения адресов IPv6 в строке - в зависимости от того, сколько поисковых запросов вы используете для данных. Обычно их очень мало или просто очень мало данных. Да, хранение и поиск менее эффективны, чем с числами, но это не намного больнее, чем хранение адресов электронной почты, имен пользователей или имен пользователей.

И почему вы не сможете смешивать IPv4 и IPv6 в строковых полях? Их легко отличить при их извлечении. Их диапазон возможных значений не перекрывается.

Короче: используйте номера для проверки совпадений, используйте строки в другом месте. Неэффективность строк не имеет значения по сравнению с простотой использования.