[Из песочницы] 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:
// 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) хорош собой, но нам не нужны все поля.Вытащим: name_ru name_en region postal_code latitude 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:
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 году.
