Здравствуй! Меня зовут Бабичев Максим и я быдлокодер. Это моя первая статья на Хабрахабр, прошу строго не судить.
Нашел на Хабре статью, в которой были базы в двух модификациях: Страны и города и только страны. Также в этих архивах есть небольшие примеры использования таблиц на 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.
-- Запрос (M1,1)
SELECT `city_id`
FROM `net_ru`
WHERE begin_ip <= LONG_IP_ADDRESS AND end_ip >= LONG_IP_ADDRESS
И в этом случае мы избавимся от подзапроса. Но вспомним про BETWEEN и запишем запрос так:
-- Модифицированный запрос (M1,2)
SELECT `city_id`
FROM `net_ru`
WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip
Так SQL-код читабельнее и короче.
Остался отдельный запрос на город. Объединим модифицированный запрос (M1,2) и (2).
-- Модифицированный запрос (M1,2)+(2)
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.
На многих ресурсах объединяют запросы так:
-- (UNION1)
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 два запроса, получим:
-- (M-UNION1)
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`
Запрос (M-UNION1) хорош собой, но нам не нужны все поля.
Вытащим:
- name_ru
- name_en
- region
- postal_code
- latitude
- longitude
-- (M1-UNION1)
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 соединение в запрос.
-- (M1,2-UNION1)
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.
-- (M1,2-UNION1)
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 соединения сильно влияют на скорость выполнения запроса. Модифицируем наш запрос (M1,2-UNION1) с помощью WHERE.
-- (M1,3-UNION1)
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`
)
Протестируем модифицированный запрос:
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 году.
Автор: REZ1DENT3
