#база_данных #архитектура
Есть некоторая сущность типа "конференция", в которой могут оказаться несколько участников
с разными тегами:
{
"participants": {
"a": "79219998877",
"b": "79219998878",
"c": "79219998879"
}
}
Мне необходимо хранить эти данные в БД (конкретный движок сейчас неважен - он, вероятнее
всего, сменится через некоторое время - считаем, что нет ни джойнов, ни индексации
полей-массивов) и производить по ним поиск - мне надо получать конференции, удовлетворяющие
произвольному количеству условий "a был 79219998877", "b был 79219998878" и аналогам,
a и b при этом могут оказаться абсолютно произвольными тегами. Можно ли как-то представить
эти данные в каком-то виде (без полей-массивов и джойнов), позволяющем производить
подобный поиск простой выборкой, или это в приницпе невозможно?
Ответы
Ответ 1
У вас некорректная постановка задачи. Как мне хранить такие данные, чтобы быстро и безболезненно осуществлять поиск по произвольному количеству участников? Но при этом Мне необходимо хранить эти данные в БД (конкретный движок сейчас неважен - он, вероятнее всего, сменится через некоторое время - считаем, что нет ни джойнов, ни индексации полей-массивов) и производить по ним поиск. Ответ будет зависеть от типа БД, которую вы выберите. Самый "безболезненный" вариант - храните все в JSON и Filter вам в помощь. Но такой самый простой вариант вас, скорее всего, не устроит, потому что есть еще другие данные, которые будет неудобно хранить в таком виде и болезненно использовать. При выборе NoSQL хранилища у вас почти полный контроль над данными и выборкой, но за это сами следите за консистентностью данных. Но зато можете применять любой Filter, который выберет нужные вам записи. И если дальше в проекте у вас будут сущность с большим количеством связей, то можно замучаться следить за ними. В случае реляционной БД, все сложнее, потому что на сложность фильтра ложатся ограничения реляционной алгебры, зато все в порядке с консистентностью и выборке по индексу. Фильтр из примера можно реализовать чем то типа: http://sqlfiddle.com/#!9/b891f SELECT Conference.conf_id, COUNT(Conference.conf_id) as cnt FROM Conference JOIN Participants ON Conference.conf_id = Participants.conf_id AND (Participants.key, Participants.val) IN ( ('a', '79219998878'), ('b', '79219998877') ) GROUP BY (Conference.conf_id) HAVING cnt = 2 Если что-то более сложное, то скорее всего ограничения реляционной алгебры не позволят вам это сделать. Но и из этой ситуации есть, как вам подсказали, выход - хранить вашу структуру JSON'ом и накладывать условие на выборку SELECT * FROM Conf WHERE JSON_CONTAINS(json_field, "79219998877", "$.a") AND JSON_CONTAINS(json_field, "79219998878", "$.b") -- или через виртуальные колонки -- WHERE json_field->"$.a" = "79219998877" AND json_field->"$.b" = "79219998878" виртуальные колонки Так вы получите гибрид обоих миров, но доступно только в MySQL > 5.7. Там даже можно индексы строить. UDP: напомнили про postgres, спасибо Да, там тоже есть подобный функционал с jsonb и тоже можно по полю строить запросы. Смотрите @> и <@ операторы. UDP: после прочтения комментов, мне более менее стало ясно, что имелось в виду В данный момент проект сидит на реляционке, в будущем планируется переход на row-column хранилище (NoSQL - это далеко не только json-подобные хранилища), которая предполагает подготовку данных для простых запросов в "плоском" виде, и в котором я не смогу искать по части ассоциативного массива (только по полной версии). Отсюда произрастает вышеописанный вопрос, который сводится не к поиску обходных решений, а к тому, можно ли как-то представить все это в подготовленном виде. В данном случае "обходное" решение это своя собственная реализация индекса по массиву документов. Но написание "своего" такого индексатора - задача очень сложная. Вы же не сможете встроить этот индексатор в свою БД. А его реализация на php/ruby/python, скорее всего, будет уступать по производительности полному перебору в базе. Значит придется писать его на "системном" языке и общаться посредством IPC/socket. Тогда способ как им пользоваться я вижу так: при создании/удалении документа вы будете передавать индексатору этот документ, а он будет на этом основании изменять индекс. Потом когда вам потребуется выборка, вы делаете к нему запрос, а вам он быстренько возвращает ID элементов удовлетворяющих этому запросу, и уже с этими ID вы лезете в базу и выбираете записи. Тогда зачем изобретать этот велосипед, если можно поднять какую-нить MongoDB и пользоваться ей точно так же. Дальше пример для Mongo. Документы хранить лучше в виде: { "_id" : ObjectId("571923c7e4b08c60be5228a4"), "id" : 1, "participants" : [ { "key" : "a", "value" : "79219998878" }, { "key" : "b", "value" : "79219998877" }, { "key" : "c", "value" : "79219998879" } ] } { "_id" : ObjectId("571923f0e4b08c60be5228a9"), "id" : 2, "participants" : [ { "key" : "a", "value" : "79219998877" }, { "key" : "b", "value" : "79219998878" } ] } { "_id" : ObjectId("57193370e4b08c60be522acb"), "id" : 3, "participants" : [ { "key" : "a", "value" : "79219998877" }, { "key" : "c", "value" : "79219998879" } ] } { "_id" : ObjectId("571933c2e4b08c60be522ad4"), "id" : 4, "participants" : [ { "key" : "a", "value" : "79219998878" }, { "key" : "b", "value" : "79219998877" }, { "key" : "d", "value" : "79219998873" } ] } Сделать индекс: db.participants.createIndex({ "participants.key" : 1 , "participants.value" : 1}) И искать: db.participants.find( { "participants" : { "$all" : [ { "$elemMatch" : { "key" : "a", "value" : "79219998878" } }, { "$elemMatch" : { "key" : "b", "value" : "79219998877" } } ] } } ).pretty() Вывод { "_id" : ObjectId("571923c7e4b08c60be5228a4"), "id" : 1, "participants" : [ { "key" : "a", "value" : "79219998878" }, { "key" : "b", "value" : "79219998877" }, { "key" : "c", "value" : "79219998879" } ] } { "_id" : ObjectId("571933c2e4b08c60be522ad4"), "id" : 4, "participants" : [ { "key" : "a", "value" : "79219998878" }, { "key" : "b", "value" : "79219998877" }, { "key" : "d", "value" : "79219998873" } ] } Если сделать explain(), то будет видно что используются индексы. "winning plan": { "inputStage" : { // INDEX SCAN!!! "stage" : "IXSCAN", "keyPattern" : { "participants.key" : 1, "participants.value" : 1 }, "indexName" : "participants.key_1_participants.value_1", "isMultiKey" : true, "direction" : "forward", "indexBounds" : { "participants.key" : [ "[\"a\", \"a\"]" ], "participants.value" : [ "[\"79219998878\", \"79219998878\"]" ] } } И просто пользуетесь ей как внешним индексатором. Да, есть оверхед, что ради этого индексатора вам придется запускать целую монгу. Но и передавать вы можете не целый документ, а только ID и массив участников. Думаю в других NoSQL БД тоже есть индексатор с требуемым функционалом и, возможно, они "легче" монги, можете использовать их. Если уж очень хочется, то можно покопаться в исходниках монги, понять как работает такой индексатор и переписать самому. Но, по моему, оверхед на монгу дешевле чем реализация велосипеда на стероидах.Ответ 2
Можно создать таблицу с полями a, b, conf_id, conf_date и делать select. Идеально, если можно было бы создать еще и индекс по a и b. Без него будет долго, однако шустрее, чем прямой поиск по таблице с конференциями. Без индекса см. вариант 3. Если нет индексации полей, но есть сортировка и возможность быстрого создания таблиц, можно вместо индекса создавать таблицы с названием simulated_index_{a}_{b}, по сути иммитация индекса. Кстати, работать будет едва ли не шустрее, чем в первом случае с индексами. Если есть только возможность сортировки по произвольному полю и нет индекса, то тогда можно взять вариант 1, но добавить поле ab, по которому будем сортировать. ab = a*2^32 + b (тогда ab будет int64, 2^32 - размер int). Или ab = md5(a) ^ b . Ну то есть надо как-то объеденить два поля, а потом на лету вычислять нужное значение. Если всё надо держать в одной таблице (которая выше), но к этой таблице можно добавлять колонки, тогда просто добавляем колонку ab из примера 3. Я уверен, есть еще варианты. Пишите, если эти не подходят.Ответ 3
Вариант 1. Если форма данных в виде JSON не принципиальна, то в MSSQL можно использовать дататип XML. Подробней, это очень объемная тема, можно почитать здесь или, например, здесь. К сожалению поддержку самого JSON - MS обещает только в версии 2016. Вариант 2. В MySql дататип JSON уже реализован (почитать можно здесь) что позволит вам просто хранить в поле эту конференцию и производить поиск. Однако я мало работал с MySQL и по работе с ней вряд ли подскажу что полезное. Но покопать в эту сторону вам, вероятно, будет интересно.
Комментариев нет:
Отправить комментарий