IP-Geo. Оптимизация SQL запроса

Опубликовано автором

Здравствуй, Хабрахабр! Меня зовут Бабичев Максим и я быдлокодер. Это моя первая статья на Хабрахабр, прошу строго не судить.

Нашел на Хабре статью, в которой были базы в двух модификациях: Страны и города и только страны. Также в этих архивах есть небольшие примеры использования таблиц на php.

Сразу хочу сказать, что эта статья рассчитана на новичков, а не на продвинутых гуру.

На всякий случай приложу структуру таблиц

    --     -- Структура таблицы `net_city`     --      CREATE TABLE IF NOT EXISTS `net_city` (         `id`          INT(11) NOT NULL AUTO_INCREMENT,         `country_id`  INT(11)          DEFAULT NULL,         `name_ru`     VARCHAR(100)     DEFAULT NULL,         `name_en`     VARCHAR(100)     DEFAULT NULL,         `region`      VARCHAR(2)       DEFAULT NULL,         `postal_code` VARCHAR(10)      DEFAULT NULL,         `latitude`    VARCHAR(10)      DEFAULT NULL,         `longitude`   VARCHAR(10)      DEFAULT NULL,         PRIMARY KEY (`id`),         KEY `country_id` (`country_id`),         KEY `name_ru` (`name_ru`),         KEY `name_en` (`name_en`)     )     ENGINE = MyISAM     DEFAULT CHARSET = utf8;       --     -- Структура таблицы `net_city_ip`     --      CREATE TABLE IF NOT EXISTS `net_city_ip` (         `city_id`  INT(11)    DEFAULT NULL,         `begin_ip` BIGINT(11) DEFAULT NULL,         `end_ip`   BIGINT(11) DEFAULT NULL,         KEY `city_id` (`city_id`),         KEY `ip` (`begin_ip`)     )     ENGINE = MyISAM     DEFAULT CHARSET = utf8;       --     -- Структура таблицы `net_country`     --      CREATE TABLE IF NOT EXISTS `net_country` (         `id`      INT(11) NOT NULL AUTO_INCREMENT,         `name_ru` VARCHAR(100)     DEFAULT NULL,         `name_en` VARCHAR(100)     DEFAULT NULL,         `code`    VARCHAR(2)       DEFAULT NULL,         PRIMARY KEY (`id`),         KEY `code` (`code`),         KEY `name_en` (`name_en`),         KEY `name_ru` (`name_ru`)     )     ENGINE = MyISAM     DEFAULT CHARSET = utf8;       --     -- Структура таблицы `net_country_ip`     --      CREATE TABLE IF NOT EXISTS `net_country_ip` (         `country_id` INT(11)    DEFAULT '0',         `begin_ip`   BIGINT(11) DEFAULT NULL,         `end_ip`     BIGINT(11) DEFAULT '0',         KEY `country_id` (`country_id`),         KEY `ip` (`begin_ip`)     )     ENGINE = MyISAM     DEFAULT CHARSET = utf8;       --     -- Структура таблицы `net_euro`     --      CREATE TABLE IF NOT EXISTS `net_euro` (         `country_id` INT(11)    DEFAULT '0',         `begin_ip`   BIGINT(11) DEFAULT NULL,         `end_ip`     BIGINT(11) DEFAULT '0',         KEY `country_id` (`country_id`),         KEY `ip` (`begin_ip`)     )     ENGINE = MyISAM     DEFAULT CHARSET = utf8;       --     -- Структура таблицы `net_ru`     --      CREATE TABLE IF NOT EXISTS `net_ru` (         `city_id`  INT(11)    DEFAULT '0',         `begin_ip` BIGINT(11) DEFAULT NULL,         `end_ip`   BIGINT(11) DEFAULT NULL,         KEY `city_id` (`city_id`),         KEY `ip` (`begin_ip`)     )     ENGINE = MyISAM     DEFAULT CHARSET = utf8;

Меня больше интересуют запросы SQL.

LONG_IP_ADDRESS, число полученное с помощью функции ip2long() в PHP.

-- Ищем по российским и украинским городам -- Запрос (1)  SELECT * FROM (     SELECT *     FROM net_ru     WHERE begin_ip <= LONG_IP_ADDRESS – IP пользователя, ip2long()     ORDER BY begin_ip DESC     LIMIT 1 ) AS t WHERE end_ip >= LONG_IP_ADDRESS – IP пользователя long

После запроса (1), получают нужный город из таблицы net_city:

-- Запрос (2)  SELECT * FROM net_city WHERE id = -- (Результат из первого запроса).city_id

Код из примера на PHP:

<?php // Подключаемся к базе данных $db_host = "localhost"; $db_user = ""; $db_password = ""; $db_database = "geo"; $link = mysql_connect ($db_host, $db_user, $db_password); if ($link && mysql_select_db ($db_database)) {     mysql_query ("set names utf8"); } else {     die ("db error"); }  // IP-адрес, который нужно проверить $ip = "79.134.219.2";  // Преобразуем IP в число $int = sprintf("%u", ip2long($ip));  $country_name = ""; $country_id = 0;  $city_name = ""; $city_id = 0;  // Ищем по российским и украинским городам $sql = "select * from (select * from net_ru where begin_ip<=$int order by begin_ip desc limit 1) as t where end_ip>=$int"; $result = mysql_query($sql); if ($row = mysql_fetch_array($result)) {     $city_id = $row['city_id'];     $sql = "select * from net_city where id='$city_id'";     $result = mysql_query($sql);     if ($row = mysql_fetch_array($result)) {         $city_name = $row['name_ru'];         $country_id = $row['country_id'];     } else {         $city_id = 0;     } }

Избавимся от подзапроса в запросе (1), используя AND.

-- Модифицированный запрос (3)  SELECT `city_id` FROM `net_ru` WHERE begin_ip <= LONG_IP_ADDRESS AND end_ip >= LONG_IP_ADDRESS

И в этом случае мы избавимся от подзапроса. Но вспомним про BETWEEN и запишем запрос так:

-- Модифицированный запрос (4)  SELECT `city_id` FROM `net_ru` WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip

Так SQL-код читабельнее и короче.

Остался отдельный запрос на город. Объединим модифицированный запрос (4) и (2).

-- Модифицированный запрос (5)  SELECT * FROM `net_city` `city`     JOIN (         SELECT `city_id`         FROM `net_ru`         WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip     ) AS `res` ON `res`.`city_id` = `city`.`id`

Так получилось, что IP-адреса 79.134.219.2 в базе net_ru – нет. Но он есть в базе net_city_ip.
На многих ресурсах объединяют запросы так:

-- Модифицированный запос (5.1)  SELECT * FROM `net_city` `city`     JOIN (         SELECT `city_id`         FROM `net_ru`         WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip     ) AS `res` ON `res`.`city_id` = `city`.`id` UNION SELECT * FROM `net_city` `city`     JOIN (         SELECT `city_id`         FROM `net_city_ip`         WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip     ) AS `res` ON `res`.`city_id` = `city`.`id`

Видим, что запросы идентичны. Объединим внутри JOIN два запроса, получим:

-- Модифицированный запрос (5.2)  SELECT * FROM `net_city` `city`     JOIN (         SELECT `city_id`         FROM `net_ru`         WHERE LONG_IP_ADDRESS BETWEEN  begin_ip  AND end_ip         UNION         SELECT `city_id`         FROM `net_city_ip`         WHERE LONG_IP_ADDRESS BETWEEN  begin_ip  AND end_ip     ) as `res` ON `res`.`city_id` = `city`.`id`

Модифицированный запрос (5.2) хорош собой, но нам не нужны все поля.
Вытащим:

  1. name_ru
  2. name_en
  3. region
  4. postal_code
  5. latitude
  6. longitude
-- Модифицированный запрос (6)  SELECT DISTINCT     `city`.`name_ru` `city_name_ru`,     `city`.`name_en` `city_name_en`,     `city`.`region`,     `city`.`postal_code`,     `city`.`latitude`,     `city`.`longitude` FROM `net_city` `city`     JOIN (         SELECT `city_id`         FROM `net_ru`         WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip         UNION         SELECT `city_id`         FROM `net_city_ip`         WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip     ) AS `res` ON `res`.`city_id` = `city`.`id`

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

-- Модифицированный запрос (7)  SELECT DISTINCT     `city`.`name_ru`    `city_name_ru`,     `city`.`name_en`    `city_name_en`,     `city`.`region`,     `city`.`postal_code`,     `city`.`latitude`,     `city`.`longitude`,     `country`.`name_ru` `country_name_ru`,     `country`.`name_en` `country_name_en`,     `country`.`code` FROM `net_city` `city`     JOIN (         SELECT `city_id`         FROM `net_ru`         WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip         UNION         SELECT `city_id`         FROM `net_city_ip`         WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip     ) AS `res` ON `res`.`city_id` = `city`.`id`     JOIN `net_country` `country`         ON `country`.`id` = `city`.`country_id`

Протестируем запрос.

Для этого получим с помощью PHP значение LANG_IP_ADDRESS:

<?php echo ip2lang(‘79.134.219.2’); # Результат: 1334237954

Подставим его в наш запрос и выполним в phpMyAdmin.

Модифицированный запрос (7) с подставленным значением

-- Модифицированный запрос (7) с подставленным значением  -- ip2lang(‘79.134.219.2’) вместо LONG_IP_ADDRESS  SELECT DISTINCT     `city`.`name_ru`    `city_name_ru`,     `city`.`name_en`    `city_name_en`,     `city`.`region`,     `city`.`postal_code`,     `city`.`latitude`,     `city`.`longitude`,     `country`.`name_ru` `country_name_ru`,     `country`.`name_en` `country_name_en`,     `country`.`code` FROM `net_city` `city`     JOIN (         SELECT `city_id`         FROM `net_ru`         WHERE 1334237954 BETWEEN begin_ip AND end_ip         UNION         SELECT `city_id`         FROM `net_city_ip`         WHERE 1334237954 BETWEEN begin_ip AND end_ip     ) AS `res` ON `res`.`city_id` = `city`.`id`     JOIN `net_country` `country`         ON `country`.`id` = `city`.`country_id`

Результат работы запроса:

1 всего, запрос занял 0.3408 сек.

JOIN соединения сильно влияют на скорость выполнения запроса. Запишем JOIN с помощью WHERE

-- Модифицированный запрос (8)  -- Записали JOIN с помощью WHERE  SELECT DISTINCT     `city`.`name_ru` `city_name_ru`,     `city`.`name_en` `city_name_en`,     `city`.`region`,     `city`.`postal_code`,     `city`.`latitude`,     `city`.`longitude`,     `country`.`name_ru` `country_name_ru`,     `country`.`name_en` `country_name_en`,     `country`.`code` FROM `net_city` `city`     JOIN `net_country` `country`         ON `country`.`id` = `city`.`country_id` WHERE `city`.`id` = (     SELECT `city_id`     FROM `net_city_ip`     WHERE LONG_IP_ADDRESS BETWEEN `begin_ip` AND `end_ip`     UNION     SELECT `city_id`     FROM `net_ru`     WHERE LONG_IP_ADDRESS BETWEEN `begin_ip` AND `end_ip` )

Протестируем модифицированный запрос:

Модифицированный запрос с помощью WHERE

SELECT DISTINCT     `city`.`name_ru` `city_name_ru`,     `city`.`name_en` `city_name_en`,     `city`.`region`,     `city`.`postal_code`,     `city`.`latitude`,     `city`.`longitude`,     `country`.`name_ru` `country_name_ru`,     `country`.`name_en` `country_name_en`,     `country`.`code` FROM `net_city` `city`     JOIN `net_country` `country`         ON `country`.`id` = `city`.`country_id` WHERE `city`.`id` = (     SELECT `city_id`     FROM `net_city_ip`     WHERE 1334237954 BETWEEN `begin_ip` AND `end_ip`     UNION     SELECT `city_id`     FROM `net_ru`     WHERE 1334237954 BETWEEN `begin_ip` AND `end_ip` )

Результат работы запроса:

1 всего, запрос занял 0.1527 сек.

Модифицированный запрос был выполнен быстрее более чем в два раза.

Аналогичный запрос можно получить и для стран.

Данная Geo-IP база была выбрана для объяснения JOIN/UNION соединений и оптимизации запросов.
Надеюсь, данная статья поможет начинающим понять, насколько важна оптимизация и как её можно добиться. Рекомендую к чтению статью пользователя tuta_larson.

Данная Geo-IP база очень старая и знает очень мало IP-адресов. Но вы можете составить свою IP-базу и с помощью пользователей пополнять её.

Дальше составить рейтинг IP и основываясь на собственный рейтинг “угадывать” город из которого пользователь.

База данных и информацию по GeoIP брал из статьи: «База GeoIP – страны и города, сентябрь 2013». Спасибо пользователю netload за увлекательную статью, написаную в 2013 году.

ссылка на оригинал статьи http://habrahabr.ru/post/254461/

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *