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

Это не вопрос ))) Просто тешу своё ЧСВ и предлагаю к обсуждению.

Сразу оговорюсь, что есть разные способы обращения к 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

 

Каэш баян. Мало того, я дал ссылку на источник баяна. От меня полезные скрипты по теме: заполнение дырок, кодировка и собственно загрузка в БД.

[censored]

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