Comments 35
Интересно, есть ли поля типа JSON в Oracle?!
Есть. В комплекте документации ему посвящена целая книга:
Oracle® Database: JSON Developer's Guide
Общая идея крайне проста — обобщать и разделять :) В случае условий в начале статьи (несколько датчиков опрашиваются одновременно с заданной периодичностью) у вас получается одна таблица, в которой у вас описаны ваши датчики (тип, название, время фактического существования, возможно ещё что-то), вторая таблица — измерения (собственно моменты времени, на которые вы получаете показатели) и третья, ссылающаяся на первые две, в которой будут лежать значения полученные с соответствующего датчика в указанный момент времени.
В первой таблице — по одной записи на каждый датчик.
Во второй таблице — по одной записи на каждый факт снятия показаний.
В третьей таблице — по несколько записей на каждый факт снятия показаний (в зависимости от того, с какого количества датчиков снимались данные).
Примерно так (дальше уже можно накручивать).
Стоп, но ведь json это тоже себе text. И он сам уже при распаковке будет решать, что это: целое, дробное, или текст. А раз так, то какая разница с первой реализацией что вы предложили? Ведь раз вы доверяете JSON'у хранить типы, то что мешает вам производить манипуляции с конвертацией данных при чтении? Например для поля 1, всегда конвертировать строку, например «231» в число 231. А для поля 2 все значения оставлять строкой, хоть там будет «222», или «огурец».
но если понадобится хранить в той же таблице еще чьи-то данные, то я не буду знать, какие данные собираются.
Не знаете, что собираете, но хотите это анализировать? Как вы будете это анализировать, если не знаете, что это за данные? А если узнали потом в будущем, то что мешает при анализе их конвертировать из текста, в другой нужный формат?
Мне кажется, вы сами себе проблему придумали, и сами её героически решили, добавив других проблем, вроде полнотекстового поиска, и излишнего хранения длинных ключей (которые тоже обязательно индексируются), превращая mysql из реляционной бд, в nosql бд.
То есть если мы попытаемся туда записать что-то не валидное, выпадет ошибкаИмеется в виду не валидный JSON?
При создании виртуальных колонок нужно обязательно знать структуру JSON. Опять же не понятно что будет в виртуальной колонке для тех записей, в которых этого поля нет в JSON. Да и вообще так и не увидел никаких преимуществ JSON перед колонками — имхо больше мороки с ними (та же дата, как в примере!), чем «раз в год» сделать alter table.
А синтаксис «data->>'$.data'» это вообще тихий ужас. Обязательно придумать свой, да?
А синтаксис «data->>'$.data'» это вообще тихий ужас. Обязательно придумать свой, да?
В PostgreSQL тот же самый синтаксис. Стандарт же. Называется «SQL/JSON Path Expression».
Статья производит впечатление высосанной из пальца с целью показать что mysql умеет json. Напихать в поле разнородных и заранее неизвестных структур в одно поле json а потом раскладывать их черт пойми как по stored колонкам, чтобы был полнотекст. Не производит впечатление разумного решения. Ну или я чего-то не понял, какой-то гениальной мелочи, всё объясняющей
Я так понимаю, под негибкостью подразумевалась необходимость привлекать тормозной (при добавлении новой колонки с данными, а не индекса) ALTER TABLE. Без этого код приложения получает свободу в выборе данных для хранения без модификации DDL. В идеальном случе, конечно же, ибо всё равно будет нужно что-то индексировать и т.п.
Дисклеймер на всякий случай: не стОит причислять меня к сторонникам или противникам описанного в докладе подхода.
А что до самого вопроса, увы не сталкивался с IoT и не могу говорить с т.зрения опыта. Но если рассматривать проблему в общем случае — полностью согласен, что JSON не самый лучший выбор по производительности. Но весь доклад-то и не рассматривает best-practices по организации хранения данных, а строится вокруг одного главного критерия:
Это достаточно логично, но есть проблема — это не гибко. Допустим, мы захотим добавить еще один датчик и измерять что-то еще. Например, некоторые люди измеряют остаток пива в кеге. Что делать в этом случае?
Т.е., как в работающей системе с миллионами записей добавлять/убирать виды показаний без останова самой системы.
Насколько хорошим является это решение — судить не мне, я просто отметил причины, по поводу которых у maxshopen возникли вопросы. Может, идея, предложенная eefadeev, действительно намного лучше
Пример: есть проект, у проекта пользователь может создавать произвольную форму из произвольных полей (текст, значение, интервал и т.п.) аля бриф.
Как сию структуру лучше хранить для последующей работы и выборки / формировании статистических данных и группировок?
Но если очень хочется или по-другому никак не получается то, навскидку, есть два основных варианта (в условиях когда форма действительно может быть произвольной): вариация на тему EAV или JSON. В принципе (это к моему изначальному вопросу) вы с этими данными будете делать ровно две вещи: класть их в БД (целиком для одной формы) и читать их оттуда (ровно так же — целиком для одной формы). Поэтому на сегодняшний день я бы, пожалуй, смотрел в сторону JSON'а.
2. Вы обязательно огребаете 100500 проблем из за NULL'able полей (а по-другому нельзя)
3. Рано или поздно вы придёте к ситуации когда полей снова не хватает
4. Имена полей у вас совершенно идиотские и не несут в себе вообще никакой семантики
5. В похожих ситуациях разные разработчики используют разные (по порядку) поля что ещё больше добавляет хаоса
6. Вы не можете вести никакой внятной индексной политики
Я могу и дальше продолжать, но жалко времени. Просто примите как данность (я занимаюсь проектированием БД почти четверть века): никогда так не делайте. Совсем никогда.
Вы перечислили факты, а но проблем за ними не следует.
Давайте по пунктам:
1. Если n = 5, то таблица будет иметь 5,10,15 столбцов. Для современных БД это не проблема. Если мы понимаем, что 15 столбцов хватит на 20 лет службы, проблемы нет.
2. это можно огребать и на других таблицах.
3. да. для этого и есть поддержка проекта, рефакторинг, оптимизация и другие вещи. системы не висят в вакуме.
4. есть лог введения в использования полей, где информация хранится.
5. о каких разработчиках речь?
6. см. ответ 3.
Продолжайте, пожалуйста, интересно послушать…
Желательно, ещё в сравнении с EAV и json.
По вашим пунктам:
1. Если у вас формы с, максимум дюжиной элементов — да. По мне такие формы, скорее, исключение.
2. Можно да, а можно нет. А в данным случае вы будете это делать обязательно.
3. Никакой рефакторинг в данной ситуации вам не поможет. Максимум чего вы добьётесь — это регулярной уборки. Если вам нравится сначала раскидывать мусор, а потом регулярно его убирать — кто вам может запретить? Но на мой взгляд это довольно странная стратегия
4. Любой лог — это просто инструмент. Вы забываете про то, что он не сделает счастья автоматически. Но, повторюсь, если вам нравится всё время приводить в порядок разбегающийся зоопарк — это ваше законное право. В каждой избушке свои погремушки.
5. О разработчиках, которые будут эти поля заполнять и использовать
6. см. ответ на ответ 3. Помноженный на то, что вы в одном месте храните совершенно разные данные (в них нет никакой логической связи «по-вертикали»).
Что вы хотите услышать про EAV и JSON? Что в случае первого у вас все данные поднимаются одним запросом (поскольку, фактически, представляют из себя набор key-value) и это запрос достаточно прост, а в случае второго вы вообще не паритесь, и, фактически, передаёте на клиента BLOB (поскольку его внутренняя структура вам совершенно не интересна)?
1. рабочий вариант.
2. проблема использования null полей общая. механизм в коде по работе с null поля должен быть заложен. Если его нет, то вопросы к разработитчкиу.
3. это нормальная практика в больших проектах с большим уровнем неопределённости. всегда будет что-то, что нужно делать на этапе поддержки, оптимизация БД, рефакторинг коду туда входят.
4. мне это нравится больше, чем поддерживать кучу версий json и писать 200 строчные выборки в EAV, для простых форм.
5. это делается автоматически, механизм по работе с ними пишется 1 раз.
6. логическая связь сущность.
таблиц с N столбцами может быть много.
А как вы в JSON будут поддержку N форматов делать, когда в начале был JSON с 1 полем, потом с 5, потом снова с 5 но другими, потому что кто-то удалил поле и добавили новое? Наверное вы хотите в коде поддерживать N форматов для одного blob поля? Спасибо, такое проходили, не нужно.
А как жить с EAV таблицей на 100 млн строк? Тоже 1 запросов поднимаете? И выборка на 10 строк отрабатывает за 6 секунд.
А как вы в JSON будут поддержку N форматов делать, когда в начале был JSON с 1 полем, потом с 5, потом снова с 5 но другими, потому что кто-то удалил поле и добавили новое? Наверное вы хотите в коде поддерживать N форматов для одного blob поля? Спасибо, такое проходили, не нужно.
Никак не буду. С точки зрения БД они все одинаковые (потому что семантика существует только на стороне того кода, который на основании этих данных будет рендерить формы)
А как жить с EAV таблицей на 100 млн строк? Тоже 1 запросов поднимаете? И выборка на 10 строк отрабатывает за 6 секунд
Ну, если у вас несколько сотен тысяч форм…
Хотя 100 млн. это, вообще говоря, не много. В чём проблема поднять по индексам десяток значений атрибутов одного объекта (по его ключу) — вам виднее. По мне 6 секунд на 100 млн. записей (для значений, да и для объектов даже) — это не долго. Это ОЧЕНЬ долго.
Как извратиться, чтобы в таблицу что-то добавить? Нужно сделать alter table, но если вы делали alter table в MySQL, то знаете, о чем я говорю, — это совершенно непросто. Alter table в MySQL 8 и в MariaDB реализовано намного проще, но исторически это большая проблема.
прошу прощения, а что с этим не так? По моему в MySQL это всегда было просто. Вот в MSSQL не просто да, но у MySQL с этим по моему никогда проблем не было.
В MySQL 5.7 такой запрос оптимизировать нельзя, если только за счет других вещей. В MySQL 8 появилась реальная возможность указывать сортировку для каждого поля.
Самое интересное, что ключ descending/ascending после названия индекса давно был в SQL. Даже в самой первой версии MySQL 3.23 можно было указать published_at descending или published_at ascending. MySQL это принимал, но ничего не делал, то есть сортировал всегда в одном направлении.
Наверное можно сделать виртуальный «обратный» столбец и проиндексировать поля в asc. Т.е.
ALTER TABLE cloud_data_json
ADD published_at_desc INT
GENERATED ALWAYS AS
(-1 * UNIX_TIMESTAMP(STR_TO_DATE(data->>'$.published_at',"%Y-%m-%dT%T.%fZ"))) VIRTUAL
;
alter table cloud_data_json
add key published_at_data_name
(published_at_desc, data_name)
;
select data_name, published_at, data->>'$.data' as data from
cloud_data_json order by published_at_desc, data_name limit 10
;
Гибкая схема хранения данных в MySQL (JSON)