Базы данных -> MySQL -> Хранение и сортировка адресов IP

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

В приложениях для сети Web, использующих базы данных, часто приходится обрабатывать сетевые адреса IP. К примеру, можно следить за активностью посетителей вашего сайта, подсчитывая число обращений к нему со стороны конкретного адреса или группы адресов IP. Кроме того, ваша система безопасности может использовать адреса IP для того, чтобы проверять их и на этом основании разрешать или отказывать обратившимся пользователям в доступе к определенным приложениям.

При неэффективном хранении в больших базах данных адреса IP могут занимать излишне много места и замедлять обработку запросов. При оптимизации хранения адресов IP в базе данных следует обращать внимание не только на занимаемое им место, но и на удобство сортировки. Например, такой запрос:

SELECT *
FROM WebStats
ORDER BY 

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

SELECT *
FROM WebStats
ORDER BY 

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

Хотя по определению адрес IP является целым 32-битным числом, как правило, он представляется в виде символьной строки следующей формы: А1.А2.А3.А4, где А1, А2, А3 и А4 - целые числа от 1 до 255. Со строкой, записанной в виде 204.56.55.28 гораздо легче работать, чем с аналогичной по значению строкой в шестнадцатеричной форме, СС38371С, или в двоичной форме 11001100001110000011011100011100. В связи с этим формат хранения адресов IP должен обеспечить простой и быстрый метод представления данных в виде символьной строки.

Символьная строка в форме А1.А2.А3.А4 не позволяет эффективно обрабатывать запросы, поскольку обладает большой избыточностью. Для ее хранения требуется 15 байтов памяти, в то время как для хранения адреса IP необходимо всего 4 байта. А1, А2, А3 и А4 - всего 4 байта (где А1 - первый байт и т.д.), а полная строка А1.А2.А3.А4 занимает от 7 до 15 символов (байтов чтения, так как по стандарту ANSI символ хранится в одном байте). Более того, если хранить адреса IP в виде строк, то трудно получить правильную сортировку, так как они будут восприниматься системой как символы, а не как числа. Это демонстрирует таблица 1. Очевидно, это не вполне то, что хотелось бы получить. Желаемый результат приведен в таблице 2.

Для получения этого результата необходимо сначала провести сортировку по А1, затем по А2, потом по А3 и, наконец, по А4. Поэтому предварительно следует извлечь все значения А1, А2, А3 и А4 из символьной строки, преобразовать их в целые числа без знака, и затем отсортировать, используя тип данных integer, а не varchar(15). Это выполняет оператор, приведенный в листинге 1.

Лучший способ удовлетворить требования к снижению пространства памяти и сокращению времени обработки (на сортировку и представление данных, а также на обработку запроса) состоит в том, чтобы хранить числа А1, А2, А3 и А4 отдельно, в четырех различных столбцах базы данных, тип данных которых будет определен как tinyint. В этом случае можно применить запрос, приведенный в листинге 2, для сортировки, и представления адресов IP.

Однако если по каким-либо причинам нежелательно хранить А1, А2, А3 и А4 в отдельных столбцах базы данных, обладающих типом данных tinyint, или когда база данных настолько велика, что не удается быстро конвертировать хранящиеся в ней сведения в другой формат, то можно применять запрос, приведенный в листинге 1. Этот запрос можно применять и в тех случаях, когда производительность не является критичным фактором. Например, когда запрос обращен к небольшой таблице, содержащей несколько записей.

Столбец IP имеет тип данных varchar(15) и хранит адреса IP в форме А1.А2.А3.А4, то есть в таком виде, в каком их передает Internet Information Server (IIS). Чтобы отсортировать данные с адресами IP, следует в запросе SQL Server использовать оборот ORDER BY А1, А2, А3, А4. Для осуществления этого следует извлечь значения А1, А2, А3 и А4 из столбца IP. Заметим, что иногда после применения функции SUBSTRING() в результате может появиться точка. SQL Server 7.0 рассматривает точку как знак, отделяющий целую часть числа от дробной. Поэтому он предостерегает от прямого преобразования результата в целое число. Именно по этой причине в листинге использовано промежуточное преобразование в формат с плавающей запятой.

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