Здравствуй, Хабрахабр! Меня зовут Бабичев Максим и я
Нашел на Хабре статью, в которой были базы в двух модификациях: Страны и города и только страны. Также в этих архивах есть небольшие примеры использования таблиц на 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) хорош собой, но нам не нужны все поля.
Вытащим:
- 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:
<?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 году.
Комментарии (15)
dinikin
31.03.2015 11:04+2Что бы научить новичка оптимизировать запросы, ему нужно объяснить, что такое индексы, как они подхватываются при разных запросах и научить пользоваться командой EXPLAIN. А то, что вы описали, бесполезно для новичка.
netload
31.03.2015 13:25+2> Данная Geo-IP база очень старая
В базе данных есть скрипт обновления. Можете обновлять хоть каждый день.
> Избавимся от подзапроса
Можете объяснить логику такого решения?
Скажу честно. я офигел. В статье «Оптимизация SQL запроса» нет ни слова про EXPLAIN. Вместо 4 быстрых запросов сделан 1 медленный. Зачем, какой в этом смысл?
REZ1DENT3 Автор
31.03.2015 13:55В базе данных есть скрипт обновления. Можете обновлять хоть каждый день.
Согласен, архив net_update не смотрел.
Можете объяснить логику такого решения?
В моей задаче нужно было определить город и страну одновременно. Если города не определён нет, то и выводить страну мне, по задаче, не нужно. И да, это я забыл написать в статье.
Я замерял скорость выполнения PHP + SQL примера из example.php. Скорость выполнения примерно ~0.2997 ms (средне арифметически). С данным запросом, в статье он обозначен #8, код выполняется за ~0.1713 ms (средне арифметически) на моей машине, на VPS сервере разница более мелкая, но ощутимая.
В статье «Оптимизация SQL запроса» нет ни слова про EXPLAIN.
EXPLAIN сначала планировал добавить. Но потом просто не было времени подготовить материал. Возможно, если будет время, я опишу принципы анализа и оптимизации SQL запроса с реальными примерами из кода и добавлю в статью или напишу новую.
Спасибо за критику. Первая статья и не все описал подробно и доходчиво. В будущем планирую исправляться и делать более полные статьи.
FractalizeR
31.03.2015 14:48Тайминг — миллисекунды или все же секунды? ~0.1713 ms и ~0.1713 s совсем разные вещи. На скринах у вас, как я понимаю, все же секунды.
netload
31.03.2015 18:19> В моей задаче нужно было определить город и страну одновременно
Вы не разобрались, зачем нужен подзапрос. Попробуйте разобраться сейчас. Сделайте EXPLAIN для моего запроса и для вашего запроса.
baltazorbest
31.03.2015 19:48А можете мне объяснить зачем вообще хранить IP пользователя или его страну/город в базе? Берете тот же самый sypex geo или GeoIP maxmind и налету определяете по его IP его город или страну. Зачем вообще использовать базу там где это не нужно? По поводу explain то же верно, наугад делать оптимизацию запросов это как минимум странно, а если это не указывать в статье то как новичок вообще поймет как вы оптимизировали запрос? Это уже не говоря о том что можно вообще использовать nginx + geoip
dstarcev
31.03.2015 19:53Самое интересное в данной задаче то, что нельзя просто так взять и построить индекс для быстрой выборки по двум столбцам, которые являются границами диапазона.
Если мы делаем индекс по (begin_ip, end_ip), то при запросе BETWEEN сперва поиском по дереву индекса найдется первая строка, у которой begin_ip >= LONG_IP_ADDRESS. А затем начнется последовательное сканирование всех последующих строк с проверкой end_ip <= LONG_IP_ADDRESS. И это медленно.dstarcev
01.04.2015 12:13Ошибся в операторах, сначала begin_ip <= LONG_IP_ADDRESS, затем end_ip >= LONG_IP_ADDRESS
zapimir
Вы что прикалываетесь 0,15 секунд искать IP юзера? :)
Вот советую почитать, зря вы убрали вложенный запрос, оно то хоть и выглядит сложнее, но работает намного быстрее. Ну и по сравнению с бинарными базами Maxmind или Sypex Geo MySQL слишком медленный.
Кроме того у Вас база мягко говоря не оптимальная.
Зачем для IP использовать тип BIGINT? Чтобы таблицы и индексы были в 2 раза больше? Или зачем INT для индекса стран и городов?
REZ1DENT3 Автор
Выше я описал, что использую не собственную базу. А из статьи habrahabr.ru/post/193024/. Спасибо за ссылки, обязательно посмотрю)
zapimir
Просто в таком случае вообще непонятно, чему вы хотели научить новичков. Структура таблиц далеко не оптимальна (не обращаете внимание на важность выбора типов данных, особенно для индексируемых полей), никакого разбора запросов с помощью EXPLAIN, запросы меняете, чтобы они были «читабельнее и короче», а не чтобы быстрее работали.