Есть некоторая сущность типа "конференция", в которой могут оказаться несколько участников
с разными тегами:
{
"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 и
по работе с ней вряд ли подскажу что полезное. Но покопать в эту сторону вам, вероятно,
будет интересно.
