Geekly Articles each Day

IP Geo。 SQLクエリの最適化



こんにちは、Habrahabr！ 私の名前はマキシム・バビチェフです。 これはHabrahabrに関する私の最初の記事です。厳密に判断しないでください。

Habré の記事で 、 国と都市 、そして唯一の国という 2つの修正のベースがありました。 また、これらのアーカイブには、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、 PHPの ip2long（）関数を使用して取得した数値 。

 --       --  (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; } }

ANDを使用して、クエリ（1）のサブクエリを取り除きます。

 --   (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`

そのため、net_ruデータベースのIPアドレス79.134.219.2はそうではありません。 ただし、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内で2つのリクエストを組み合わせると、次のようになります：

 --   (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. 地域
  4. postal_code
  5. 緯度
  6. 経度


 --   (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接続は、クエリの実行速度に大きく影響します。 WHEREでJOINを書く

 --   (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秒かかりました。

変更されたリクエストは2倍以上速く完了しました。

国についても同様のリクエストを取得できます。

このGeo-IPデータベースは、JOIN / UNION接続とクエリの最適化を説明するために選択されました。
この記事が初心者にとって最適化がいかに重要で、どのように達成できるかを理解する助けになることを願っています。 tuta_larsonによる記事を読むことをお勧めします 。

このGeo-IPベースは非常に古く、ごく少数のIPアドレスを知っています。 ただし、IPベースを作成し、ユーザーの助けを借りて補充することができます。



次に、IP評価を行い、独自の評価に基づいて、ユーザーのいる都市を「推測」します。

GeoIPに関するデータベースと情報は、記事「GeoIPデータベース-国と都市、2013年9月」から引用されています 。 2013年に書かれた興味深い記事を提供してくれたnetloadに感謝します。

Source: https://habr.com/ru/post/J254461/

More articles:


All Articles