логин: 
Другие записи за это число:
2011/03/19 - таинственная хуйня MySQL
<< предыдущая заметкаследующая заметка >>
19 марта 2011
Кстати про MySQL давно хотел спросить

Есть база IP мира, определяющая город пользователя. Она, грубо говоря, заданы промежутком: from_ip int(11) — to_ip int(11), а результат (город для всех IP этого промежутка) — поле value. Задача — сделать выборку.

Традиционное решение: SELECT `value` FROM `IPTABLE` WHERE `ip`>=`from_ip` AND `ip`<=`to_ip`

Проблема в том, что база гигантская, а выборка не оптимизирована — MySQL не знает, что значения выстроены по возрастанию и промежутки повторяться не могут. Володя Русинов как-то объяснял мне, что MySQL работает по этому запросу в два хода: сперва делает таблицу выборки для всех ip больше from_ip (получая в среднем половину гигантской базы), затем отсекает из нее все, где ip меньше to_ip. При этом затраты ресурса колоссальны, несмотря на индексы. Если добавить LIMIT 1, то просто добавится третья операция — в конце отберет из получившегося 1 значение (а оно и так одно).

Вопроса два:

1. Существует ли метод переделать запрос (или таблицу), чтобы выборка происходила более оптимизированно?

2. Не появились ли за последние 3 года более удобные методы определить город по IP зашедшего? Платные пакеты и сервисы не предлагать — движок публичный, решения должны предлагаться удобные.

<< предыдущая заметка следующая заметка >>
пожаловаться на эту публикацию администрации портала
Страницы, которые привлекли мое внимание за последние дни, рекомендую:
2017-11-22 В июне 1982
архив ссылок
Оставить комментарий
Windows Opera
4
1
Wot (#458830)
Я так понимаю в гугле забанили ?

Читать и проникаться
http://ipinfodb.com/ip_database.php
Linux Firefox
 Москва
1
4
Я не знаю иностранного языка, пришлите на русском или поясните своими словами.
Windows Firefox
4
0
quantum (#644921)
Основной смысл такой

SELECT * FROM `ipTABLE` where `ipFROM` <= INET_ATON('74.125.45.100') order by ipFROM desc limit 1;

Если сделать индекс по ipfrom упорядоченный по убыванию (desc), то будет довольно быстро.

Есть вариант еще селать индекс по from_ip, to_ip и оставить ваш запрос. ТОже будет быстро, но что быстрее нужно смотреть
Windows Firefox
1
0
[email protected] (sunchaser.info)
можно результаты
EXPLAIN SELECT `value` FROM `IPTABLE` WHERE `ip`&gt;=`from_ip` AND `ip`&lt;=`to_ip`
в студию?
Linux Firefox
 Москва
0
0
Нет, у меня эта система на практике сейчас разобрана. Это теоретический вопрос.
Windows Firefox
3
0
[email protected] (sunchaser.info)
а вообще, если по ip индекс, то вообще-то индексы сами по себе всегда упорядочены
Windows Safari Chrome
1
0
Sling (#824708)
Вот-вот. Индекс, да ещё и уникальный по возможности. (Там же уникальные должны быть записи, так?)
Linux Safari Chrome
 Зеленоград
3
0
Д.С. (#425067)
Если индексы упорядочены, тогда достаточно запроса типа select `value` from `IPTABLE` where 'ip' > `ip_from` limit 1
Windows Opera
0
0
[email protected] (xtmb.info)
А заодно http://api.yandex.ru/detector/ чоужтам.
Linux Firefox
 Москва
0
0
Это клиентская версия, а мне нужна серверная.

Проще говоря, мне нужно, не чтобы заходящий на сайт видел, из какого он города (он и так это знает), а чтобы я мог записывать вместе с его комментарием поле "город", не сфальсифицированное отправителем.
Windows Opera
0
0
[email protected] (xtmb.info)
Ну, гм. Просто вместо поиска по базе будет обращение к яндексовскому апи. Остальное то же самое. И работать будет у всех, достаточно только получить апи-ключ.

UPD: Понял, о чем речь. Так никто же не запрещает обращаться к апи напрямую, не жабаскриптом.
Windows Opera
0
0
[email protected] (xtmb.info)
Хм. Или там нельзя передавать IP параметром. Тогда жаль.
Windows Firefox
0
0
quantum (#644921)
да даже если можно было бы. При каждой загрузке страницы дергать удаленное апи не сильно умно. Даже с кэшированием
Linux Firefox
 Москва
0
0
Строго говоря, при каждой загрузке страницы это совершенно не нужно.

Нужно при каждой записи нового комментария, "а это уже совсем другие деньги"(с)
Windows Firefox
0
0
quantum (#644921)
Вобщем-то да, тут я погорячился
Linux Firefox
 Москва
0
0
Я бы с большим удовольствием не парился с таблицами, а дергал удаленное. Но пока не вижу хорошего и удобного удаленного.
Windows Safari Chrome
1
0
Вот бесплатный сервис "IP Location Finder" в 4-х вариантах, с кодами для сайта (правда только на английском):
http://www.find-ip-address.org/ip-location-finder.php
"Бесплатный" - в пределах честного пользования и без гарантий.
Nokia-E90 Safari
 Москва
0
0
Leonid Kaganov
А что, собственно говоря, этот сервис определяет? Я зашел на страницу и он мне, пыжась, сообщил, что я из Российской Федерации. Эту информацию (код страны), простите, без ошибок дает даже запрос whois.

Смысл-то в определении города. Страну и дурак назовет.
Windows Safari Chrome
0
0
Мою деревню определяет как надо, включая регистрационные данные моего провайдера с его гео координатами, и вдобавку показывает какая у меня тут национальная валюта, "системный" язык, и ещё бог знает что, недоступное моему пониманию - прям жуть. Значит эта информация у них сидит в базе данных, а информации по Российским ip в таком виде у них значит нет. Ну что поделать, - какая база, такие и результаты.. Если посмотреть на их заглавной странице - там есть список последних 50ти визитёров, - так там похоже много чего неопределённого, и не только по России. Ну и в сад тогда такую базу.. А в отношении прямого запроса к "ip мира" таблице, могу посоветовать просчитать вариант индекса с использованием "DETERMINISTIC FUNCTION" на уровне схемы. Речь ведь идёт о "lookup" таблице, которая не особо часто обновляется? Трюк в том как такую функцию грамотно разработать, а так-то в принципе ничего невозможного нет.
Windows Firefox
0
0
[email protected] XL (blog.barris)
С мобильным интернетом весело. :) Может в РФ со своей спецификой и есть хоть какая привязка к региону, а вот в Украине по ИП опсоса можно определить только то что это Украина, да и то в лучшем случае. :)
(Айпи с которого оставлен этот камент разные ресурсы причисляют к Киеву, Харькову, Одессе, что никакого отношения к реальности не имеет.)
Nokia-E90 Safari
 Москва
0
0
Leonid Kaganov
Это фигня. В Москве одно время какой-то 4Г определялся Финляндией.
Windows Opera
0
0
lleo.me/[email protected]Антон
Боюсь, что сфальсифицировать это поле с помощью бесплатного прокси-сервера можно совершенно элементарно, а так сидит достаточно много людей с работы, кого не пускают в интернет по-нормальному :)
Есть вот такой сервис http://blog.ipgeobase.ru/?p=37 но это только по российским ip...
Кстати "Есть база IP мира" это какая именно база имеется ввиду? GeoIP или что-то другое?
Linux Firefox
 Москва
0
0
Нет, сервис "только по российским" совершенно не подходит ;) Как это представляется, я сперва буду выяснять по одной базе, российский ли это IP, и если да - то спрашивать в одном месте, а если нет - в другом? ;)
Я так понимаю - вопрос в нагрузке на сервер. Запрашиваем внешний сервис - есть совпадение - используем, нет совпадения - лезем в свою базу. Полагаю как минимум процентов 70 запросов будет обслужено внешним сервисом. Не слишком изящно, но при отсутствии альтернатив вполне допустимо.
Windows Firefox
0
0
Barris XL (#403731)
1. Спортивного интереса ради, сколько в этой таблице строк?
2. Я так понимаю в таблице условно три поля:
Город - IP от - IP до
Киев - 10.10.10.1 - 10.10.10.10
Можно попробовать переделать таблицу в вид
Город - IP
Киев - 10.10.10.1
Киев - 10.10.10.2
...
Киев - 10.10.10.10
Ставим индекс на IP и в запросе делаем проверку на строгое соответствие по одному полю — по IP.
Да, таблица вырастет. И скорее всего вырастет ниипически. Но именно такой выборке _практически_ пох на количество записей в таблице: что 10, что 10 000 000.
3. Придумываем одно составное поле из "IP от" и "IP до", строим по нему индекс и ищем по нему. Сейчас сходу не соображу, получиться ли такой финт в данном конкретном случае — надо подумать.
Таблица в таком виде будет содержать 4 294 967 296 элементов.
Даже при длине записи в 2 байта (индексы городов) это 8Гб.
И проверок никаких не надо - просто номер записи = IP.
Вобще при таком подходе и БД ни к чему это может быть хоть файл, хоть массив в памяти (если памяти хватит).
Windows Firefox
0
0
[email protected] XL (blog.barris)
Если предполагать, что в таблицу ничего не пишется и не удаляется, то можно и по твоему.
Или сгенерировать весь набор ИП-адресов (даже не занятых) и единожды их записать.
В противном случае или строить индекс для поиска записи или постоянно переписывать файл по порядку следования записей.
Базы IP адресов естественно обновляются. Российская несколько раз в сутки, GeoIP - бесплатная - раз в месяц, платная - раз в неделю. В первом приближении база с возрастом не более месяца вполне актуальна.
Так вот обновляются то они обновляются, но по сравнению с объемом базы размер обновлений ничтожен.
Вообще можно сделать 256 файлов по 48Мб каждый (2 байта на IP объект, в общем случае город маловато, три надо) а дальше очень просто. Первым шагом отрываем от IP первый байт и открываем файл с соответствующим номером, смещаемся на величину оставшихся трех байт умноженных на три и читаем 3-х байтный указатель на IP объект.
Обновлять такую базу тоже не шибко сложно...
Можно и помельче покрошить. 256 каталогов, по 256 файлов в каждом, по 192 Кб.
Но в конечном счете всё равно 12Гб. :-) Сделать что-ли... давно ведь хотел максминдовский GeoIP с руцентровским скрестить...
Linux Firefox
 Москва
0
0
В этой таблице столько строк, что умножать их количество на 256 (а то и 256*256) - полная катастрофа мозга.
Windows Firefox
0
0
[email protected] XL (blog.barris)
Два дня назад у человека была катастрофа мозга, по его расчетам надо было в КПК закидывать таблицу в 100 000 записей. Он что-то на что-то умножил и 100 000 было оптимистическим минимумом.
По факту оказалось 2 500..6 500 записей, что вполне удобоваримо для КПК.
.
Но это лирика. Я не математик, я, блин, вообще морская свинка. :) А точно нет какого-нибудь матметода чтобы, условно говоря, "умножить" ИП1 на ИП2, результат писать в индексируемое поле, а к искомому ИП, например, "прибавить" какое-то волшебное число и результат искать по индексируемому полю на строгое соответствие?
Windows Opera
0
0
[email protected] (xtmb.info)
Пара багов:
1) Поисковое окошко почему-то не реагирует на Enter. Мышкой тыкаю "go". Opera 11.01
Windows Opera
0
0
[email protected] (xtmb.info)
3) http://lleo.aha.ru/dnevnik/contents/?search=таксист&amp;[...]

mysql_query("SELECT `num`,`Date`,`Header`,`view_counter`,`Access` FROM `dnevnik_zapisi` WHERE (`Body` LIKE '%таксист%' OR `Header` LIKE '%таксист%' OR `Comment` LIKE '%таксист%') AND `Access`='all' ORDER BY `Date` DESC")
Unknown column 'Comment' in 'where clause'

Прошел по ссылке из http://lleo.aha.ru/dnevnik/contents?mode=more
Linux Firefox
0
0
Декомпозиция базы данных, не иначе.
Разделить огромную таблицу на 256 таблиц одинаковой структуры, так отсечется проверка первого байта адреса. В таблицах следующей ступени опять делаем ссылки на 256 таблиц каждая. Таблицы же последней ступени будут оперировать уже двумя оставшимися байтами адреса.
Логика заполнения такой базы усложнится, да и количество операций по извлечению возрастет. Однако, теперь вместо перебора N предположительных записей о диапазонах городов нужно перебрать 256 значений старшей таблицы, затем 256 - из следующей, и младшая таблица будет содержать уже N/256/256 записей.
Даже в самом затруднительном случае перебор 4-х гигазаписей при лобовом способе заменяются на 256+256+65536 - сравните 4 миллиарда и десятки тысяч - за уменьшение расхода памяти при выборке на 5 порядков оплачиваем несколькими дополнительными операциями с входными данными.

Можно количество и размер градаций менять ради оптимизации. К тому же, некоторые промежуточные таблицы из 256 записей уже будут указывать на какие-то города, если в городе более 2**16 адресов. Но это мелуие детали.
Windows Firefox
0
0
Просто для уточнения: вы уже используете для IP адреса - INT UNSIGNED в сочетании с INET_ATON?

Т.е. ip у вас это что-то вроде inet_aton(getenv('REMOTE_ADDR'))
Linux Firefox
 Москва
0
0
В общих чертах да. INT UNSIGNED, только процедура перевода в число у меня на PHP собственная - я стараюсь без необходимости функции MySQL не использовать.
Windows Safari Chrome
1
0
Ответ на первый вопрос:
1. нужен самый что ни на есть простой индекс на поле from_ip (других индексов для этой конкретной задачи не надо):
"alter table iptable add index from_ip_idx(from_ip)" - по умолчанию индекс будет возрастающий.
2. в фильтре надо использовать "where ip between from_ip and to_ip".
3. Всё остальное за вас сделает встроенный оптимизатор.
Ну а если надо ещё лучше и быстрее, тогда можно (только осторожно) привлечь к работе механизм MySQL Query Cache, для чего придётся а) сконфигурировать MySQL Query Cache, и б) на основе нужного запроса создать функцию с директивой DETERMINISTIC, чтобы вместа запроса к базе, по возможности, пользоваться тем что уже сидит в cache.
:)

всего комментариев: 40

<< предыдущая заметка следующая заметка >>