Comments 29
А конкретные цифры «ускорений» есть?
+2
Действительно стоило привести. Добавил в статью.
+1
Кому интересно, вот статья на эту же тему, но гораздо более детальная, в том числе и с более подробными тестами нежели в данном топике.
+1
Конкретное ускорение получится, если разбить базу на 256 таблиц на основе первого октета адреса и делать селект из этих таблиц (а не из одной большой) выбирая таблицу по первому октету.
Типа так:
«select c from cdb%03lu where a <= %lu and %lu <= b;»,cdb_octet1(a),a,a
Типа так:
«select c from cdb%03lu where a <= %lu and %lu <= b;»,cdb_octet1(a),a,a
-1
А почему именно GIST индекс, а не GIN? Таблица же создаётся один раз, а GIN будет быстрее работать на выборках.
0
Есть уже хорошее решение для mysql
с простым индексом на ip_start
Думаю что на PG можно легко сделать подобное
SELECT * FROM `ip_group_city` where `ip_start` <= INET_ATON('74.125.45.100') order by ip_start desc limit 1;
с простым индексом на ip_start
Думаю что на PG можно легко сделать подобное
+6
Да, хорошее решение.
0
Да, я тоже хотел написать об этом же способе. Нужен только один индекс. Но у меня возвращался несколько другой результат:
В результате, одна строка выбирается почти в любом случае, но locId содержит верный id только если айпишник действительно между startIpNum и endIpNum, а не ближайший к началу какого-то блока.
SELECT IF( '{$ip}' <= `endIpNum`, `locId`, -1 ) AS `locId` FROM `ip_blocks` WHERE `startIpNum` <= '{$ip}' ORDER BY `startIpNum`DESC LIMIT 1
В результате, одна строка выбирается почти в любом случае, но locId содержит верный id только если айпишник действительно между startIpNum и endIpNum, а не ближайший к началу какого-то блока.
0
Совершенно верно, просто база должна иметь в том числе и начала для свободных блоков. Но мне кажется это не проблема сделать ( если вдруг в базе их нет ) резервные блоки offline
0
Пугает меня такая зависимость от данных, плюс еще одно преобразование при импорте из IpGeoBase. Проверять надежнее.
0
На самом деле дополнительная проверка переноситься со времени обработки адреса на время обновления базы. По-моему это хорошо.
А правильную базу, в которой уже есть все блоки можно взять с ipinfodb.com/ip_database.php. Фришный Maxmind там уже есть.
А правильную базу, в которой уже есть все блоки можно взять с ipinfodb.com/ip_database.php. Фришный Maxmind там уже есть.
0
Да, я про него упомянул в начале топика. Производительность дает на порядки большую, чем выборки из базы. Но все зависит от задачи, иногда просто удобнее использовать бд.
0
иногда просто удобнее использовать бдСобственно, вы же сами предлагаете какой-то сторонний модуль, который в 99 случаев из ста не установлен. В этом смысле ваш вариант не лучше модуля апача. Решение на чистой базе данных в UPD2.
0
contrib для постгреса несколько отличается от модуля для апача и никак не меняет то, что задача решена средтвами БД. Борьба за «чистоту» базы от модулей в случае постгреса неправильный подход.
А так — да, решение в UPD2 действительно очень хорошее, жаль сам в свое время не допер. Гуглил не в ту сторону видимо:).
А так — да, решение в UPD2 действительно очень хорошее, жаль сам в свое время не допер. Гуглил не в ту сторону видимо:).
0
В мускуле вот так проверяю айпишники:
SELECT country_id FROM ip_pool WHERE (INET_ATON('127.0.0.1') & (POW(2, 32) — POW(2, 32 — SUBSTRING_INDEX(ip, '/', -1)))) = INET_ATON(SUBSTRING_INDEX(ip, '/', 1));
где 127.0.0.1 — адрес, который ищем, а сети прописаны внутри в базе в виде 127.0.0.1/24 например.
Эксплейн говорит: Using where; Using index — если ip = PK
SELECT country_id FROM ip_pool WHERE (INET_ATON('127.0.0.1') & (POW(2, 32) — POW(2, 32 — SUBSTRING_INDEX(ip, '/', -1)))) = INET_ATON(SUBSTRING_INDEX(ip, '/', 1));
где 127.0.0.1 — адрес, который ищем, а сети прописаны внутри в базе в виде 127.0.0.1/24 например.
Эксплейн говорит: Using where; Using index — если ip = PK
0
А что будете делать вот с такими данными?
| startip | endip | locationid |
| 192.168.0.1 | 192.168.0.5 | 1 |
| 192.168.0.5 | 192.168.0.18 | 2 |
В комментариях выше есть пример того, как правильно работать в mysql с geoip базами.
| startip | endip | locationid |
| 192.168.0.1 | 192.168.0.5 | 1 |
| 192.168.0.5 | 192.168.0.18 | 2 |
В комментариях выше есть пример того, как правильно работать в mysql с geoip базами.
0
Еще одна база от нас — WorldIP. Пока база для всех открыта только по странам.
Главное отличие от максминда и производных — наша база строится на основе данных с корневых маршрутизаторов, BGP таблиц и 45 точек наблюдений (сервера расположены по всему миру от аргентины до японии). А значит более точные данные.
Еще немного примеров здесь и здесь.. Таких сетей тысячи, и выявить из можно только такими методами…
А в постгресе можно использовать так или вот такие функции (в самом проекте WIPmania тоже используется постгрес):
И тогда запросы можно делать типа таких(отлично используется индекс по началу сети):
Главное отличие от максминда и производных — наша база строится на основе данных с корневых маршрутизаторов, BGP таблиц и 45 точек наблюдений (сервера расположены по всему миру от аргентины до японии). А значит более точные данные.
Еще немного примеров здесь и здесь.. Таких сетей тысячи, и выявить из можно только такими методами…
А в постгресе можно использовать так или вот такие функции (в самом проекте WIPmania тоже используется постгрес):
create function inet_aton(inet) returns bigint language sql immutable as $f$ select $1 - inet '0.0.0.0' $f$; create function inet_ntoa(bigint) returns inet language sql immutable as $f$ select $1 + inet '0.0.0.0' $f$;
И тогда запросы можно делать типа таких(отлично используется индекс по началу сети):
select inet_ntoa(startip), inet_ntoa(endip), country from worldip where startip<=inet_aton('199.51.126.33') order by startip desc limit 1
+2
а разве два условия >= и <= в качестве замены between не решат проблему?
а преобразовывать ip-адрес в число лучше до запроса, средствами того_откуда_исполняется_запрос
а преобразовывать ip-адрес в число лучше до запроса, средствами того_откуда_исполняется_запрос
0
А почему не используются встроенные типы данных inet, cidr?
+1
Кстати, случайно наткнулся на ещё одно интересное решение, основанное на встроенном типе box. Итоговый выигрыш — того же порядка, что и ip4r (GIN бы ему :))
© www.postgres.cz/index.php/PostgreSQL_SQL_Tricks
postgres=# EXPLAIN ANALYZE SELECT * FROM testip WHERE 19999999 BETWEEN startip AND endip; QUERY PLAN ---------------------------------------------------------------- Seq Scan on testip (cost=0.00..19902.00 rows=200814 width=12) (actual time=3.457..434.218 rows=1 loops=1) Filter: ((19999999 >= startip) AND (19999999 <= endip)) Total runtime: 434.299 ms (3 rows) Time: 435,865 ms postgres=# CREATE INDEX ggg ON testip USING gist ((box(point(startip,startip),point(endip,endip))) box_ops); CREATE INDEX Time: 75530,079 ms postgres=# EXPLAIN ANALYZE SELECT * FROM testip WHERE box(point(startip,startip),point(endip,endip)) @> box(point (19999999,19999999), point(19999999,19999999)); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on testip (cost=60.50..2550.14 rows=1000 width=12) (actual time=0.169..0.172 rows=1 loops=1) Recheck Cond: (box(point((startip)::double precision, (startip)::double precision), point((endip)::double precision, (endip)::double precision)) @> '(19999999,19999999),(19999999,19999999)'::box) -> Bitmap Index Scan on ggg (cost=0.00..60.25 rows=1000 width=0) (actual time=0.152..0.152 rows=1 loops=1) Index Cond: (box(point((startip)::double precision, (startip)::double precision), point((endip)::double precision, (endip)::double precision)) @> '(19999999,19999999),(19999999,19999999)'::box) Total runtime: 0.285 ms (5 rows) Time: 2,805 ms
© www.postgres.cz/index.php/PostgreSQL_SQL_Tricks
+2
Вопрос: Зачем тут нужна база данных? Почему нельзя работать напрямую с геобазой посредством API?
0
Sign up to leave a comment.
Быстрое определение местоположения по ip в postgresql