Как сохранить IP в mySQL

На этой неделе у нас в офисе идут здоровые дебаты. Мы создаем Db для хранения прокси-информации, по большей части мы разработали схему, за исключением того, как мы должны хранить IP-адреса. Один лагерь хочет использовать 4 строчных буквы, по одному на каждый октет, а другой хочет использовать 1 большое целое,INET_ATON.

Эти таблицы будут огромными, поэтому производительность является ключевым фактором. Я нахожусь здесь посередине, поскольку я обычно использую MS SQL и 4 маленьких целых в моем мире. У меня недостаточно опыта с таким типом хранения IP-адресов.

Мы будем использовать сценарии Perl и Python для доступа к базе данных, чтобы далее нормализовать данные в несколько других таблиц для ведущих, интересного трафика и т. Д.

Я уверен, что есть некоторые в сообществе, которые сделали что-то похожее на то, что мы делаем, и мне интересно услышать об их опыте и о том, какой маршрут лучше, 1 большой int или 4 маленьких int для IP-адресов.

РЕДАКТИРОВАТЬ - Одной из наших проблем является пространство, эта база данных будет огромной, как в 500000000 записей в день. Поэтому мы пытаемся взвесить проблему пространства вместе с проблемой производительности.

РЕДАКТИРОВАТЬ 2 Некоторые разговоры перешли на объем данных, которые мы собираемся хранить... это не мой вопрос. Вопрос в том, какой способ хранения IP-адресов предпочтительнее и почему. Как я уже говорил в своих комментариях, мы работаем в большой компании из 50-ти состояний. Наши файлы журнала содержат данные об использовании от наших пользователей. Эти данные, в свою очередь, будут использоваться в контексте безопасности для управления некоторыми показателями и несколькими инструментами безопасности.

Источник

5 ответов

Решение

Я бы посоветовал посмотреть, какие типы запросов вы будете выполнять, чтобы решить, какой формат вы принимаете.

Только если вам нужно вытащить или сравнить отдельные октеты, вам нужно будет разделить их на отдельные поля.

В противном случае сохраните его как 4-байтовое целое число. Это также имеет бонус, позволяющий вам использовать встроенный MySQL INET_ATON() а также INET_NTOA() функции.

Производительность против пространства

Место хранения:

Если вы собираетесь поддерживать только IPv4-адреса, тогда ваш тип данных в MySQL может быть UNSIGNED INT который использует только 4 байта памяти.

Для хранения отдельных октетов вам нужно всего лишь использовать UNSIGNED TINYINT типы данных, а не SMALLINTS, что заняло бы 1 байт каждого хранилища.

Оба метода будут использовать одинаковое хранилище, возможно, немного больше для отдельных полей для некоторых накладных расходов.

Больше информации:

Спектакль:

Использование одного поля даст гораздо лучшую производительность, это будет одно сравнение вместо 4. Вы упомянули, что вы будете выполнять запросы только по всему IP-адресу, поэтому не нужно разделять октеты. С использованием INET_* Функции MySQL сделают преобразование между текстовым и целочисленным представлениями один раз для сравнения.

Источник

BIGINT является 8 байты в MySQL,

Хранить IPv4 адреса, UNSINGED INT достаточно, что я думаю, что вы должны использовать.

Я не могу представить сценарий, где 4 октеты получат больше производительности, чем один INTи последнее гораздо удобнее.

Также обратите внимание, что если вы собираетесь выдавать такие запросы:

SELECT  *
FROM    ips
WHERE   ? BETWEEN start_ip AND end_ip

, где start_ip а также end_ip столбцы в вашей таблице, производительность будет низкой.

Эти запросы используются, чтобы узнать, является ли данный IP находится в пределах диапазона подсети (обычно, чтобы запретить это).

Чтобы сделать эти запросы эффективными, вы должны хранить весь диапазон как LineString объект с SPATIAL индексировать по нему, и запрос, как это:

SELECT  *
FROM    ips
WHERE   MBRContains(?, ip_range)

Смотрите эту запись в моем блоге для более подробной информации о том, как это сделать:

Источник

Используйте PostgreSQL, для этого есть собственный тип данных.

А если серьезно, я бы попал в лагерь "одно 32-разрядное целое число". IP-адрес имеет смысл только тогда, когда все четыре октета рассматриваются вместе, поэтому нет смысла хранить октеты в отдельных столбцах в базе данных. Вы бы сохранили номер телефона, используя три (или более) разных поля?

Источник

Для совместимости с ipv4 и ipv6 используйте VARBINARY(16), ipv4 всегда будет BINARY(4), а ipv6 всегда будет BINARY (16), поэтому VARBINARY (16) кажется наиболее эффективным способом поддержки обоих. и чтобы преобразовать их из обычного читаемого формата в двоичный, используйте INET6_ATON('127.0.0.1'), а чтобы изменить это, используйте INET6_NTOA(двоичный)

Источник

Отдельные поля для меня не кажутся особенно разумными - очень похоже на разделение почтового индекса на секции или номер телефона.

Может быть полезно, если вам нужна конкретная информация о разделах, но я не вижу реальной причины не использовать 32-битное int.

Источник

Эффективное преобразование ip в int и int в ip (может быть полезно для вас): (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";
}
Источник

Старый поток, но для удобства читателей рассмотрите возможность использования ip2long. Он переводит ip в целое число.

По сути, вы будете конвертировать с помощью ip2long при сохранении в БД, а затем конвертировать обратно с помощью long2ip при извлечении из БД. Тип поля в БД будет INT, поэтому вы сэкономите место и получите лучшую производительность по сравнению с хранением ip в виде строки.

Источник
Другие вопросы по тегам