Здравствуйте. Есть следующая задача. Есть магазины. Их может быть 1.000. Каждый грузит свои прайс-листы каждый день (а то и 5 раз в день). Объем может быть разный - от 100 строк до 1.000.000 строк (а то и больше, но редко). Чаще 50.000-200.000 строк. На самом деле, я думаю, будет 300.000.000-500.000.000 строк + ко всему поиск по старым прайсам вестись не будет (старше 5 дней), то есть реально в поиске будут участвовать, возможно, 200.000.000-300.000.000 позиций постоянно. Но по максимуму нужно рассчитывать конечно на 1.000.000.000 записей, чтобы потом не переделывать, если что. Таблицы InnoDB. Насколько я понимаю, самый быстрый вариант для поиска - все лить в одну таблицу. Но такая большая таблица будет неимоверно медленно обновляться, так как каждый день как минимум 20% из нее будет удаляться и заливаться заново (естественно с индексами). Самый быстрый вариант для заливки - отдельная таблица под каждый прайс. Особенно сильно сокращается время на очистку старого прайс-листа - просто делаем TRUNCATE. Но поиск по 1.000 магазинам * 10 прайс-листов - это поиск по 10.000 таблиц - слишком медленно + полная каша в БД + очень ресурсоемко. Я предположил сделать отдельную таблицу под каждый магазин. В каком-то магазине будет 20.000 позиций, в каком-то 3.000.000 - в любом случае это плёвое дело для MySQL в плане поиска и более-менее нормально в плане удаления и обновления. Но опять-таки, таблиц в итоге будет до 1.000 (может и всего 200, но архитектуру нужно сразу заложить, потом будет очень проблематично это менять). На этом варианте остановился и начал тестировать. Поиск идет по двум столбцам - коду товара и его бренду. У товара могут быть аналоги, иногда их может быть 50-100 штук. Выдать надо с аналогами, то есть запрос должен идти сразу по нескольким товарам (...WHERE (`code`='123' AND `brand`='BRAND1') OR (`code`='456' AND `brand`='BRAND2') OR (`code`='789' AND `brand`='BRAND3')... и т. д.). Индексы на поле `code` стоят - они не уникальные. Плюс ко всему результатов может быть найдено 500 штук - нужно это все по страницам разбивать. А при выборке из 1.000 таблиц не представляется возможным выбрать только часть, так как должна быть сортировка по разным полям (например, по цене). То есть нельзя тормознуть поиск после найденных 20-ти результатов - надо в любом случае до конца все перебрать. Или есть какие-то решения на этот счет? Разве что одна таблица, но, опять-таки - она будет очень медленно обновляться при загрузке нового прайс-листа. Если в одной таблице 5 прайсов по 1.000.000 позиций (один магазин с большим ассортиментом), то получается в ней 5.000.000 позиций. Загрузка нового экселевского прайса (полный разбор и обработка) сейчас занимает 5 минут. В принципе это приемлемо, вопросов нет. Тем более прайсы в основном будут с почты клиентов скачиваться, так что клиенты вообще это время не будут замечать, хотя есть вариант загрузки через браузер, но, как я сказал - 5 минут нормально для такого объема. Я написал механизм поиска простейший с разбивкой по страницам - по определенному запросу находит мне 100 позиций (запрошенное + аналоги) за 0.1-0.3 сек. Проблем нет. Но это один магазин. Когда же я пытаюсь сделать поиск по 1.000.000.000 позиций - поиск идет около 3 сек. по тому же запросу, что уже прилично. Кстати, хотел бы уточнить - правильно ли я вообще делаю. Я не заливал 1.000.000.000 позиций - я весь поиск поместил в цикл, и в момент запроса он делается 200 раз по одним и тем же запросам по сути - MySQL это как-то кеширует и на реальных магазинах (разных запросах для каждого) будет в разы медленнее, или же это нормальный способ проверки? Потому что если это плохой способ, то я боюсь представить, сколько будет реальный поиск в данном случае работать. Если плохой, то только реальная генерация 1.000.000.000 строк поможет провести проверку или можно просто как-то это кеширование выключить? Собственно, как ускорить это дело? Не прошу готовые решения, просто дайте подсказку, куда вообще смотреть. Не предлагайте поменять базу данных - я умею работать только с этой + как мне кажется, это не такая непосильная задача для MySQL. Возможно, нужно запросы попробовать разбить (если поиск с сотней аналогов, то запрос довольно большой получается, но сами запросы простые SELECT .. WHERE .. без JOIN'ов и подобных вещей), или есть какие-либо варианты по ускорению выборки с учетом того, что мне не нужно 500 вариантов из всех таблиц, а нужно только 20 вывести с сортировкой выбранной, остальные на других страницах (но вообще нужно знать сколько всего страниц обязательно).
Это вообще не проверка - это непонятно что...и с реальностью не имеет ничего общего. 1ккк - это много для любой РСУБД, возможно будет уместным использование того же Sphinx, но его тоже придется распараллелить на несколько серверов, чтобы индексация проходила быстрее.
Может NoSQL? Я не работал с этим никогда, а там так много баз разных - подскажете, какую лучше использовать? С MySQL да, уже понял, что нормально работать не будет 100%. Что я только не делал с момента моего вопроса, уже и в одну таблицу залил 50 млн (а это только 10% от того, что нужно) - первый запрос с большим количеством аналогов (50-100 OR) идет секунд 15. Если без аналогов, то нормально, но без них нельзя, а с ними слишком долгий поиск.
Sphinx онли, сам мускуль на такой БД будет работать очень медленно, хоть с индексами хоть с джоинами, хоть как угодно Для быстрого обновления одной таблицы настраивай конфиг innodb и будет все быстренько обновляться, + со сфинксом индексы тебе уже не нужны.
Для быстрого обновления я решил проблему. Решил использовать все-таки одну таблицу на все прайс-листы (то есть в ней будет до 500-1.000 млн записей) и просто разделил ее на партиции по id прайс-листа, таким образом для очистки прайс-листа я делаю просто truncate нужной партиции, вот и все, работает очень быстро и не замедляется при увеличении объема таблицы. Но что теперь делать с выборкой? Запросы, повторяюсь, такого вида: Код (Text): SELECT * FROM `pricelists` WHERE `article` IN (123, 456, 78678, 'ABCD', ..может быть сотня разных вариантов.., 'ASD456', 153); Такой запрос в первый раз отрабатывает секунд 10 уже на 50 млн строках, а что будет, когда их будет 500? При повторном запросе причем за 0.1-0.2 сек находит даже с SQL_NO_CACHE, но, как я понимаю, все равно что-то кешируется, так? Иначе почему в первый раз долго ищет, а при обновлении быстро?
Ты можешь сколько угодно сейчас экспериментировать, но на рабочем проекте все это сдохнет и ты все равно придешь к Sphinx
Хорошо, согласен. Пока бегло про sphinx прочитал. Как я понял, он индексацию по заданию должен проводить? Как же тогда будет актуальная информация всегда, если прайс-листы грузятся автоматически с почты каждые 15 минут (не каждый прайс, а вообще - очередь из прайсов, а повторная загрузка каждого из них производится раз в 3 часа). Плюс ко всему клиент вручную может вообще в любое время свой прайс обновить.
Индексацию он должен проводить после каждой новой загрузки данных, при чем он умеет делать дельтаиндексы, чтоб каждый раз не перелопачивать всю бд и мержить их с основным индексом. Кто грузит, ты или клиент не важно, пришли новые данные сделал дельтаиндекс и смержил и все.
В общем со сфинксом разобрался - да, быстро все работает, по 100 млн строк ищет за сотые доли секунды, этого будет достаточно. Но появилась другая проблема. Помогите, пожалуйста, с решением. Как я раньше говорил, я решил разбивать эту таблицу на партиции по id прайс-листа, чтобы можно было быстро делать очищение устаревшего прайс-листа (truncate). То есть заливка новых позиций-то быстрая, а вот очистка старого прайс-листа (а это может быть 1.000.000 позиций) занимает много времени, если делать DELETE WHERE. Сделал, да, работает прекрасно. Только вот, оказывается, партиций всего может быть 1024, а этого мало категорически. Как вариант, я подумал, можно сделать разбиение по продавцам, а потом удалять через DELETE WHERE в конкретной партиции. Но, во-первых, это может оказаться довольно медленно для тех продавцов, у которых 20-30 млн позиций, а во-вторых - а вдруг потом будет больше 1024 продавцов? Добавлять вторую таблицу? Боюсь, поиск замедлится. Надо хотя бы на 2000 продавцов рассчитывать и у каждого по несколько прайс-листов разного объема. Сам объем прайсов в данной ситуации уже роли не играет особо. В общем главная проблема - как БЫСТРО удалять миллион строк из миллиарда с определенным id прайс-листа. Может перед удалением создавать партицию по удаляемому id и делать truncate, а затем удалять партицию? Или это получится еще медленнее?
Самое быстрое решение, которое будет очищать прайс-лист - это разделить общую таблицу со списком позиций всех прайс-листов на партиции по id прайс-листа, после чего можно будет делать просто truncate нужной позиции. Но, оказывается, в mysql есть ограничение - 1024 партиции на одну таблицу. Так вот планируется более 1024 прайс-листов, то есть такой подход не сработает.
[6 Sep 2012 11:52] Jon Stephens Fixed in 5.6.7. (The new limit is 8192 total partitions + subpartitions.) https://bugs.mysql.com/bug.php?id=47492
Да, уже сам нашел, спасибо. Это уже лучше, но все равно придется на продавцов партиции создавать, а не на прайс-листы, а потом делать DELETE WHERE, но не по всей таблице, а по партиции конкретной, так как если будет 2000 продавцов и в среднем у каждого по 5 прайс-листов (а их и 30 может быть), то этого уже не хватит. А 8192 продавцов вряд ли будет, ну а если и будет, то максимум в далеком будущем 2 таблицы хватит по горло. Буду дальше разбираться.
изучать информационную архитектуру нужно и базы данных в особенности реляционные. тогда бы не нужно было думать про тысячи партиций и таблице под каждый магазин. Аналогичный комментарий к рекомендациям конкретных поисковых движков для поиска по 500 млн нод при том что вы даже понятия не имеете нужно ли будет к примеру раздельное индексирование, а уже рекомендуете конкретные решения. Тс, проясните для себя ретроспективу задач поискового сервиса и конкретные будущие потребности и только потом думайте что вам делать с первичным хранилищем и поисковой стстемой. Начать можете отсюда: http://bazhenov.me/blog/2013/01/08/search-architecture.html
В общем да, вы правы. Пришел все-таки к сфинксу, причем без MySQL. MySQL только тормозил процесс поиска и заливки прайсов + создавал ограничения с обновлением прайсов. Теперь прайсы сначала заливаются во временные csv-файлы, после чего каждые полчаса (например, пока не точно) запускается скрипт php, который все эти прайсы соединяет в один xml-файл + создает xml-килллист, чтобы исключить позиции старых прайсов, а затем сфинкс делает из этих файлов 2 дельта-индекса (с новыми данными и килллистом) и объединяет их с основным. А ночью вызывается другой скрипт php, который объединяет в 1 xml-файл все временные csv-файлы в 1 огромный xml-файл (200-300 Гб на 500 млн позициях) и за полтора часа создает обновленный основной индекс (чтобы он не рос, ведь, как я понял, добавляя дельта-индекс с килллистом старые записи остаются в основном индексе, просто больше не выдаются - или нет?). Вот и все. Поиск работает очень быстро. Пока на 50 млн проверил - при поиске одновременно 30 разных значений (запрошенное с аналогами) время 0.02-0.05 сек. --- Добавлено --- Плюс ко всему ограничений на количество прайс-листов, продавцов теперь вообще нет (в разумных пределах конечно). Единственный минус - ну очень много дискового пространства это все занимает - индексы + временные csv-файлы + xml-файлы основного индекса, дельта-индекса и килллиста. На каждый миллион позиций около 1Гб в среднем выходит. Но это ничего страшного, зато поиск и обновление летает.