How to become an author
Search
Write a publication
Pull to refresh

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

Reading time6 min
Views18K
MySQL*PHP*SQL*
From sandbox
Recovery Mode


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

Нашел на Хабре статью, в которой были базы в двух модификациях: Страны и города и только страны. Также в этих архивах есть небольшие примеры использования таблиц на 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 году.
Tags:
Hubs:
If this publication inspired you and you want to support the author, do not hesitate to click on the button
Total votes 29: ↑16 and ↓13+3
Comments15
10
Karma
0
Rating
Babichev Maxim @REZ1DENT3

Backend developer

Send message
WebsiteFacebookTwitterVKGithub

Articles

Your account

Sections

Information

Services

Support
© 2006–2024, Habr