За последние 24 часа нас посетили 8805 программистов и 488 роботов. Сейчас ищут 305 программистов ...

Эффективные запросы к базе GeoIP (на MySQL)

Тема в разделе "Решения, алгоритмы", создана пользователем artoodetoo, 25 фев 2016.

  1. artoodetoo

    artoodetoo Суперстар
    Команда форума Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    10.310
    Симпатии:
    1.027
    Адрес:
    там-сям
    Это не вопрос ))) Просто тешу своё ЧСВ и предлагаю к обсуждению.

    Сразу оговорюсь, что есть разные способы обращения к GeoIP из PHP. Например можно поставить расширение GeoIP: http://php.ru/manual/geoip.setup.html А здесь речь пойдет о реализации через БД. Допустим у нас импортирована база Maxmind и мы хотим получить страну текущего пользователя.

    Код (PHP):
    1. $ip = $_SERVER['REMOTE_ADDR'];
    2. $sth = $pdo->prepare(
    3.     "SELECT `code`, `country` ".
    4.     "FROM `geoip_countries` ".
    5.     "WHERE INET_ATON(?) BETWEEN `ip_from` AND `ip_to`"
    6. );
    7. $sth->execute([$ip]);
    8. $country = $sth->fetch(PDO::FETCH_ASSOC);
    9. echo "{$ip}{$country['code']}{$country['country']}\n";
    Запрос верный, но неэффективный.
    Есть толковая статья про ускорение подобных запросов: https://andy.wordpress.com/2007/12/16/fast-mysql-range-quer ... ip-tables/ и здесь результат её прочтения.

    Можно, например, воспользоваться спатиал-индексами (поиск попадания точки в фигуру). Или изменить запрос так:
    Код (PHP):
    1. SELECT `code`, `country`
    2. FROM `geoip_countries`
    3. WHERE `ip_to` >= INET_ATON(?)
    4. ORDER BY `ip_to` ASC
    5. LIMIT 1
    Здесь используется тот факт, что диапазоны IP в базе не перекрываются. То есть как только мы найдем первый правильный "край" диапазона, он и есть тот, что нам нужен.

    С максмайндовскими исходными файлами CSV есть маленькая проблемка. Они неполные. То есть с дырками. И если мы наткнемся на такую дырку, мы получим данные из неверного диапазона. Лучший выход из ситуации это дополнить базу записями о "дырках". Лучше получить ответ "нет такой страны", чем не ту страну.
    Пример "потокового" поиска дырок в (отсортированных) текстовых записях в огромном файле: nogap.php
    Код (PHP):
    1. $prev = '-1'; // $prev + 1 будет соответствовать IP 0.0.0.0
    2. while (($s = fgets($in)) !== false) {
    3.     $data = str_getcsv($s);
    4.     $nextFrom = bcadd($prev, '1');
    5.     if ($nextFrom !== $data[$ipFrom]) {
    6.         $nextTo = bcadd($data[$ipFrom], '-1');
    7.         fprintf($out, …многобукв…); // пустышка
    8.     }
    9.     fputs($out, $s);
    10.     $prev = $data[$ipTo];
    11. } 
    Другая проблема это кодировка текста. В некоторых файлах есть названия в расширенной латинице. Лучше преобразовать их в UTF-8 перед импортом. А потом быстро качнуть через DATA LOAD INFILE.

    Если вы когда-нибудь пойдете той же дорожкой, можете воспользоваться моими скриптами.
    https://gist.github.com/artoodetoo/3e7402ca77afde669dec
     
  2. runcore

    runcore Старожил

    С нами с:
    12 окт 2012
    Сообщения:
    3.625
    Симпатии:
    158
    так это вроде баян уже.
     
  3. artoodetoo

    artoodetoo Суперстар
    Команда форума Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    10.310
    Симпатии:
    1.027
    Адрес:
    там-сям
    Каэш баян. Мало того, я дал ссылку на источник баяна. От меня полезные скрипты по теме: заполнение дырок, кодировка и собственно загрузка в БД.

    [censored]

    runcore, будет что сказать по делу - пиши.