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 році.

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.