0
Другие записи за это число:
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 зашедшего? Платные пакеты и сервисы не предлагать — движок публичный, решения должны предлагаться удобные.

<< предыдущая заметка следующая заметка >>
пожаловаться на эту публикацию администрации портала
архив понравившихся мне ссылок
Оставить комментарий
Windows Opera
4
1
Wot
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Я так понимаю в гугле забанили ?

Читать и проникаться
http://ipinfodb.com/ip_database.php
Linux Firefox
 Москва
1
4
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Я не знаю иностранного языка, пришлите на русском или поясните своими словами.
Windows Firefox
4
0
quantum
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Основной смысл такой

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

Проще говоря, мне нужно, не чтобы заходящий на сайт видел, из какого он города (он и так это знает), а чтобы я мог записывать вместе с его комментарием поле "город", не сфальсифицированное отправителем.
Windows Opera
0
0
Tiger
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Ну, гм. Просто вместо поиска по базе будет обращение к яндексовскому апи. Остальное то же самое. И работать будет у всех, достаточно только получить апи-ключ.

UPD: Понял, о чем речь. Так никто же не запрещает обращаться к апи напрямую, не жабаскриптом.
Windows Opera
0
0
Tiger
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Хм. Или там нельзя передавать IP параметром. Тогда жаль.
Windows Firefox
0
0
quantum
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
да даже если можно было бы. При каждой загрузке страницы дергать удаленное апи не сильно умно. Даже с кэшированием
Linux Firefox
 Москва
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Строго говоря, при каждой загрузке страницы это совершенно не нужно.

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

Смысл-то в определении города. Страну и дурак назовет.
Windows Safari Chrome
0
0
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Мою деревню определяет как надо, включая регистрационные данные моего провайдера с его гео координатами, и вдобавку показывает какая у меня тут национальная валюта, "системный" язык, и ещё бог знает что, недоступное моему пониманию - прям жуть. Значит эта информация у них сидит в базе данных, а информации по Российским ip в таком виде у них значит нет. Ну что поделать, - какая база, такие и результаты.. Если посмотреть на их заглавной странице - там есть список последних 50ти визитёров, - так там похоже много чего неопределённого, и не только по России. Ну и в сад тогда такую базу.. А в отношении прямого запроса к "ip мира" таблице, могу посоветовать просчитать вариант индекса с использованием "DETERMINISTIC FUNCTION" на уровне схемы. Речь ведь идёт о "lookup" таблице, которая не особо часто обновляется? Трюк в том как такую функцию грамотно разработать, а так-то в принципе ничего невозможного нет.
Windows Firefox
0
0
Barris XL
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
С мобильным интернетом весело. :) Может в РФ со своей спецификой и есть хоть какая привязка к региону, а вот в Украине по ИП опсоса можно определить только то что это Украина, да и то в лучшем случае. :)
(Айпи с которого оставлен этот камент разные ресурсы причисляют к Киеву, Харькову, Одессе, что никакого отношения к реальности не имеет.)
Nokia-E90 Safari
 Москва
0
0
Lianid Kaganov
Это фигня. В Москве одно время какой-то 4Г определялся Финляндией.
Windows Opera
0
0
Антон
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Боюсь, что сфальсифицировать это поле с помощью бесплатного прокси-сервера можно совершенно элементарно, а так сидит достаточно много людей с работы, кого не пускают в интернет по-нормальному :)
Windows IE
0
0
AllaDimm
Есть вот такой сервис http://blog.ipgeobase.ru/?p=37 но это только по российским ip...
Кстати "Есть база IP мира" это какая именно база имеется ввиду? GeoIP или что-то другое?
Linux Firefox
 Москва
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Нет, сервис "только по российским" совершенно не подходит ;) Как это представляется, я сперва буду выяснять по одной базе, российский ли это IP, и если да - то спрашивать в одном месте, а если нет - в другом? ;)
Windows IE
0
0
AllaDimm
Я так понимаю - вопрос в нагрузке на сервер. Запрашиваем внешний сервис - есть совпадение - используем, нет совпадения - лезем в свою базу. Полагаю как минимум процентов 70 запросов будет обслужено внешним сервисом. Не слишком изящно, но при отсутствии альтернатив вполне допустимо.
Windows Firefox
0
0
Barris XL
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
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 до", строим по нему индекс и ищем по нему. Сейчас сходу не соображу, получиться ли такой финт в данном конкретном случае — надо подумать.
Windows IE
1
0
AllaDimm
Таблица в таком виде будет содержать 4 294 967 296 элементов.
Даже при длине записи в 2 байта (индексы городов) это 8Гб.
И проверок никаких не надо - просто номер записи = IP.
Вобще при таком подходе и БД ни к чему это может быть хоть файл, хоть массив в памяти (если памяти хватит).
Windows Firefox
0
0
Barris XL
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Если предполагать, что в таблицу ничего не пишется и не удаляется, то можно и по твоему.
Или сгенерировать весь набор ИП-адресов (даже не занятых) и единожды их записать.
В противном случае или строить индекс для поиска записи или постоянно переписывать файл по порядку следования записей.
Windows IE
0
0
AllaDimm
Базы IP адресов естественно обновляются. Российская несколько раз в сутки, GeoIP - бесплатная - раз в месяц, платная - раз в неделю. В первом приближении база с возрастом не более месяца вполне актуальна.
Так вот обновляются то они обновляются, но по сравнению с объемом базы размер обновлений ничтожен.
Вообще можно сделать 256 файлов по 48Мб каждый (2 байта на IP объект, в общем случае город маловато, три надо) а дальше очень просто. Первым шагом отрываем от IP первый байт и открываем файл с соответствующим номером, смещаемся на величину оставшихся трех байт умноженных на три и читаем 3-х байтный указатель на IP объект.
Обновлять такую базу тоже не шибко сложно...
Можно и помельче покрошить. 256 каталогов, по 256 файлов в каждом, по 192 Кб.
Но в конечном счете всё равно 12Гб. :-) Сделать что-ли... давно ведь хотел максминдовский GeoIP с руцентровским скрестить...
Linux Firefox
 Москва
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
В этой таблице столько строк, что умножать их количество на 256 (а то и 256*256) - полная катастрофа мозга.
Windows Firefox
0
0
Barris XL
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Два дня назад у человека была катастрофа мозга, по его расчетам надо было в КПК закидывать таблицу в 100 000 записей. Он что-то на что-то умножил и 100 000 было оптимистическим минимумом.
По факту оказалось 2 500..6 500 записей, что вполне удобоваримо для КПК.
.
Но это лирика. Я не математик, я, блин, вообще морская свинка. :) А точно нет какого-нибудь матметода чтобы, условно говоря, "умножить" ИП1 на ИП2, результат писать в индексируемое поле, а к искомому ИП, например, "прибавить" какое-то волшебное число и результат искать по индексируемому полю на строгое соответствие?
Windows Opera
0
0
Tiger
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Пара багов:
1) Поисковое окошко почему-то не реагирует на Enter. Мышкой тыкаю "go". Opera 11.01
Windows Opera
0
0
Tiger
Windows Opera
0
0
Tiger
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
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
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Декомпозиция базы данных, не иначе.
Разделить огромную таблицу на 256 таблиц одинаковой структуры, так отсечется проверка первого байта адреса. В таблицах следующей ступени опять делаем ссылки на 256 таблиц каждая. Таблицы же последней ступени будут оперировать уже двумя оставшимися байтами адреса.
Логика заполнения такой базы усложнится, да и количество операций по извлечению возрастет. Однако, теперь вместо перебора N предположительных записей о диапазонах городов нужно перебрать 256 значений старшей таблицы, затем 256 - из следующей, и младшая таблица будет содержать уже N/256/256 записей.
Даже в самом затруднительном случае перебор 4-х гигазаписей при лобовом способе заменяются на 256+256+65536 - сравните 4 миллиарда и десятки тысяч - за уменьшение расхода памяти при выборке на 5 порядков оплачиваем несколькими дополнительными операциями с входными данными.

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

Т.е. ip у вас это что-то вроде inet_aton(getenv('REMOTE_ADDR'))
Linux Firefox
 Москва
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
В общих чертах да. INT UNSIGNED, только процедура перевода в число у меня на PHP собственная - я стараюсь без необходимости функции MySQL не использовать.
Windows Safari Chrome
1
0
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Ответ на первый вопрос:
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

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