[Moscow.pm] Написал интерфейс к базе от ipgeobase.ru

Sergey Zhuravlev sergey.zhuravlev на gmail.com
Пн Ноя 30 23:50:52 PST 2009 

2009/12/1 Ruslan Zakirov <ruslan.zakirov на gmail.com>:
> Странные цифры. Больше похоже на баг mysql. Можете прислать EXPLAINы
> для обоих запросов?

mysql> explain select * from ipgeobase where istart <= 62432 and iend
>= 62432 order by iend-istart asc limit 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ipgeobase
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 26824
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

mysql> explain select * from ipgeobase where istart <= 62432 order by
istart desc limit 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ipgeobase
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 26824
-- limit не учитывается в explain, поэтому тот неправильное число,
-- реально - один лукап по индексу и выборка одной строки.
        Extra: Using where
1 row in set (0.00 sec)

> Вот вы подумайте. Да, это range scan, но он ограничен с двух сторон.
> Индекс отсортирован по возрастанию по обоим колонкам. По этому mysql
> находит точку по istart и начинает сканирование вверх (к уменьшению) и
> должна остановиться как только iend станет меньше нужного нам
> значения. В итоге должно быть просмотрено записей максимум на две
> больше чем реально будет возвращено.

Недавно прочитал замечательную аналогую двухколоночного индекса.
Есть адресная книга, контакты отсортированы сначала по фамилии, а
потом по имени.
Нам нужно выбрать тех, у кого фамилия меньше чем "Иванов" и имя больше
чем "Сергей".
Очевидно, что придётся просматривать последовательно всех, кто
соответствует первому условию.

> Можете сравнить запросы совсем без сортировок?

предложенный мной запрос без сортировки будет возвращать
неправильные результаты, поэтому на него не стоит смотреть.

без сортировки - долго, потому что scan идёт снизу, приходится
просматривать полтаблицы
zhur на ppcdev% time perl -ME -e 'for (1..1000) {get_sql("select
SQL_NO_CACHE * from ipgeobase where istart <= 62432 and iend >= 62432
limit 1")}'
real    0m26.125s

если нужная запись есть, то с сортировкой istart desc работает быстро
zhur на ppcdev% time perl -ME -e 'for (1..1000) {get_sql("select
SQL_NO_CACHE * from ipgeobase where istart <= 62432 and iend >= 62432
order by istart desc limit 1")}'
real    0m0.264s

если нужной записи нет, то с сортировкой istart desc работает работает долго
zhur на ppcdev% time perl -ME -e 'for (1..1000) {get_sql("select
SQL_NO_CACHE * from ipgeobase where istart <= 62434 and iend >= 62434
order by istart desc limit 1")}'
real    0m27.418s


> Я просто сейчас тестирую на Pg это дело. Потом конечно смогу
> посмотреть на mysql, но от помощи не откажусь.
>
> 2009/12/1 Sergey Zhuravlev <sergey.zhuravlev на gmail.com>:
>> Мне кажется, оптимальным решением было бы избавиться от пересечений
>> сетей при загрузке базы в mysql.
>> После этого можно было бы делать
>> select * from ipgeobase where istart <= ? order by istart desc limit 1
>> и проверять iend >= $ip на стороне перла
>>
>> Проверил на не на реальной базе (лень загружать), но на подобных
>> тестовых данных:
>>
>> zhur на ppcdev% time perl -ME -e 'for (1..1000) {get_sql("select
>> SQL_NO_CACHE * from ipgeobase where istart <= 62432 and iend >= 62432
>> order by iend-istart asc limit 1")}'
>> real    0m27.686s
>>
>> zhur на ppcdev% time perl -ME -e 'for (1..1000) {get_sql("select
>> SQL_NO_CACHE * from ipgeobase where istart <= 62432 order by istart
>> desc limit 1")}'
>> real    0m0.256s
>>
>> Но для простого решения модуль конечно подходит и сейчас.
>> И он точно несравненно лучше того, что раздаётся с сайта ;-))
>>
>>
>> 2009/11/29 Ruslan Zakirov <ruslan.zakirov на gmail.com>:
>>> 2009/11/29 Sergey Zhuravlev <sergey.zhuravlev на gmail.com>:
>>>> Привет.
>>>>
>>>> Модуль клёвый.
>>>> Только запрос из intersections - это index range scan, в среднем на
>>>> половину таблички.
>>>
>>> Не уверен, что это будет половина таблички. Нужно посмотреть логи.
>>> Конечно для нагруженного пректа я бы сделал по другому. Разбил бы все
>>> на три структуры и индекс в памяти:
>>>
>>> my $index_length = 8;
>>> # индекс - массив из 2^$index_lenght элементов
>>> my @index = (
>>>   <индекс первого блока, где $index_lenght первых бит начала блока == 0>
>>>   <индекс первого блока, где $index_lenght первых бит начала блока == 1>
>>>   <индекс первого блока, где $index_lenght первых бит начала блока == 2>
>>>   ....
>>> );
>>>
>>> В зависимости от размера маски меняется объем используемой памяти.
>>>
>>> Уже с этого момента можно посылать в БД, то есть указатели у нас
>>> просто дополнят условия поиска, а можно в память смотреть:
>>>
>>> my @blocks = (
>>>  [start, end, coordinates],
>>> ...
>>> );
>>>
>>> my @coordinates = (
>>>  [long, lat, city],
>>>  ...
>>> );
>>>
>>> my @cities = (
>>>  [name, region, federal_district],
>>> );
>>>
>>>
>>> Есть лучше идеи?
>>>
>>>> На нагруженных проектах это может вызвать проблемы, хотя, в случае
>>>> mysql и 128'000 записей это достаточно быстро.
>>>
>>> Мне нужно было простое решение. Десяток поисков в день в окружении,
>>> где и так памяти не очень много.
>>>
>>>> 2009/11/29 Ruslan Zakirov <ruz на bestpractical.com>:
>>>>> Всем привет,
>>>>>
>>>>> Недавно мы обсуждали определение положения по IP адресу. Мне
>>>>> посоветовали БД от http://ipgeobase.ru.
>>>>>
>>>>> Я рад, что существует такой ресурс. Был немного удивлен примерами кода
>>>>> и очень удивлен структурой БД. Код на perl страшен, достаточно сделать
>>>>> для него perltidy и он уже становится читабельный. Алгоритм тоже не
>>>>> сахар, но это издержки структуры файла. Переключение на указание
>>>>> смещения вместо номеров строк, позволит сделать быстрый поиск прямо из
>>>>> файлов без полного сканирования или загрузки их в память.
>>>>>
>>>>> В итоге решил отказаться от работы с файлами и заливать все в табличку
>>>>> БД. Написал скрипт обновления БД и модуль для работы. Пока не на
>>>>> CPANе, но если не будет конструктивной критики и успешно уйдет в
>>>>> продакшн, то залью на цпан и в гитхаб.
>>>>>
>>>>> --
>>>>> Веселой гео локации, Руслан.
>>>>>
>>>>> --
>>>>> Moscow.pm mailing list
>>>>> moscow-pm на pm.org | http://moscow.pm.org
>>>>>
>>>>>
>>>> --
>>>> Moscow.pm mailing list
>>>> moscow-pm на pm.org | http://moscow.pm.org
>>>>
>>>
>>>
>>>
>>> --
>>> Best regards, Ruslan.
>>> --
>>> Moscow.pm mailing list
>>> moscow-pm на pm.org | http://moscow.pm.org
>>>
>> --
>> Moscow.pm mailing list
>> moscow-pm на pm.org | http://moscow.pm.org
>>
>
>
>
> --
> Best regards, Ruslan.
> --
> Moscow.pm mailing list
> moscow-pm на pm.org | http://moscow.pm.org
>

Подробная информация о списке рассылки Moscow-pm