Comments 35
Прекрасный пример, особенно с индексами понравилось. Когда-то давно использовал именно бинарные поля для хранения древовидных структурированных данных. Действительно — не все удобно в реляционной модели хранить, хотя возможно. Интересно, есть ли поля типа JSON в Oracle?!
Интересно, есть ли поля типа JSON в Oracle?!
Есть. В комплекте документации ему посвящена целая книга:
Oracle® Database: JSON Developer's Guide
Я прошу прощения, а сделать вариант не с одной таблицей (в которой пытаться хранить вообще всё на свете), а, скажем, с тремя (Типы датчиков, Измерения, Значения) — не позволила религия или отсутствие достаточной квалификации в проектировании БД? Или тогда бы статьи не получилось и было бы скучно и просто?
А можно подробнее ваш пример реализации? Сейчас столкнулся с тем, что вынужден хранить в одной таблице несколько видов измерений для удобства. У меня правда sqlite, потому просто текст, но сути не меняет — задача очень похожа на ту, что приводится в выступлении и ваше видение проблемы помогло бы взглянуть с другой стороны.
Для начала нужно разобраться что вы понимаете под «видами» измерений: показания из разных источников (датчиков в исходном примере) или показания разных типов (время, целое число, число с плавающей запятой, текст и т.п.).
Общая идея крайне проста — обобщать и разделять :) В случае условий в начале статьи (несколько датчиков опрашиваются одновременно с заданной периодичностью) у вас получается одна таблица, в которой у вас описаны ваши датчики (тип, название, время фактического существования, возможно ещё что-то), вторая таблица — измерения (собственно моменты времени, на которые вы получаете показатели) и третья, ссылающаяся на первые две, в которой будут лежать значения полученные с соответствующего датчика в указанный момент времени.
В первой таблице — по одной записи на каждый датчик.
Во второй таблице — по одной записи на каждый факт снятия показаний.
В третьей таблице — по несколько записей на каждый факт снятия показаний (в зависимости от того, с какого количества датчиков снимались данные).
Примерно так (дальше уже можно накручивать).
Общая идея крайне проста — обобщать и разделять :) В случае условий в начале статьи (несколько датчиков опрашиваются одновременно с заданной периодичностью) у вас получается одна таблица, в которой у вас описаны ваши датчики (тип, название, время фактического существования, возможно ещё что-то), вторая таблица — измерения (собственно моменты времени, на которые вы получаете показатели) и третья, ссылающаяся на первые две, в которой будут лежать значения полученные с соответствующего датчика в указанный момент времени.
В первой таблице — по одной записи на каждый датчик.
Во второй таблице — по одной записи на каждый факт снятия показаний.
В третьей таблице — по несколько записей на каждый факт снятия показаний (в зависимости от того, с какого количества датчиков снимались данные).
Примерно так (дальше уже можно накручивать).
> В этом случае появляется другая проблема — нет типов. Мы не знаем, что мы храним в поле 'data'. Нам придётся его объявить полем text.
Стоп, но ведь json это тоже себе text. И он сам уже при распаковке будет решать, что это: целое, дробное, или текст. А раз так, то какая разница с первой реализацией что вы предложили? Ведь раз вы доверяете JSON'у хранить типы, то что мешает вам производить манипуляции с конвертацией данных при чтении? Например для поля 1, всегда конвертировать строку, например «231» в число 231. А для поля 2 все значения оставлять строкой, хоть там будет «222», или «огурец».
Не знаете, что собираете, но хотите это анализировать? Как вы будете это анализировать, если не знаете, что это за данные? А если узнали потом в будущем, то что мешает при анализе их конвертировать из текста, в другой нужный формат?
Мне кажется, вы сами себе проблему придумали, и сами её героически решили, добавив других проблем, вроде полнотекстового поиска, и излишнего хранения длинных ключей (которые тоже обязательно индексируются), превращая mysql из реляционной бд, в nosql бд.
Стоп, но ведь 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».
Что-то я не видел такого стандарта… У Oracle своё и не такое, например.
Сначала автор пишет, что добавление новых колонок это не гибко, потом начинает добавлять по тем же задачам виртуальные колонки, но это уже почему то гибко, хотя при этом оказывается под не-гибкостью он вроде как имел ввиду тормознутость alter table…
Статья производит впечатление высосанной из пальца с целью показать что mysql умеет json. Напихать в поле разнородных и заранее неизвестных структур в одно поле json а потом раскладывать их черт пойми как по stored колонкам, чтобы был полнотекст. Не производит впечатление разумного решения. Ну или я чего-то не понял, какой-то гениальной мелочи, всё объясняющей
Статья производит впечатление высосанной из пальца с целью показать что mysql умеет json. Напихать в поле разнородных и заранее неизвестных структур в одно поле json а потом раскладывать их черт пойми как по stored колонкам, чтобы был полнотекст. Не производит впечатление разумного решения. Ну или я чего-то не понял, какой-то гениальной мелочи, всё объясняющей
Вот тоже не понял, вроде хотели уйти от колонок, но потом всё равно их притащили. В чем профит?
Я так понимаю, под негибкостью подразумевалась необходимость привлекать тормозной (при добавлении новой колонки с данными, а не индекса) ALTER TABLE. Без этого код приложения получает свободу в выборе данных для хранения без модификации DDL. В идеальном случе, конечно же, ибо всё равно будет нужно что-то индексировать и т.п.
База данных — хранит данные. Заранее известные и хранящиеся для дальнейшего активного использования и использования. И никакой JSON насколько бы он не был удобный, не пересилит своим удобством скорость при анализе 10 млрд. записей по сравнению с негибкой, но оттюненной базой. Тут вроде ж доклад с HighLoad конференции или с урока информатики для пятилеток? И Никогда база не должны быть гибкой чтоб хранить помойку. Для помойки пойдет и обычный текстовый файл.
Дисклеймер на всякий случай: не стОит причислять меня к сторонникам или противникам описанного в докладе подхода.
А что до самого вопроса, увы не сталкивался с IoT и не могу говорить с т.зрения опыта. Но если рассматривать проблему в общем случае — полностью согласен, что JSON не самый лучший выбор по производительности. Но весь доклад-то и не рассматривает best-practices по организации хранения данных, а строится вокруг одного главного критерия:
Это достаточно логично, но есть проблема — это не гибко. Допустим, мы захотим добавить еще один датчик и измерять что-то еще. Например, некоторые люди измеряют остаток пива в кеге. Что делать в этом случае?
Т.е., как в работающей системе с миллионами записей добавлять/убирать виды показаний без останова самой системы.
Насколько хорошим является это решение — судить не мне, я просто отметил причины, по поводу которых у maxshopen возникли вопросы. Может, идея, предложенная eefadeev, действительно намного лучше
DDL модифицируется в любом случае, реальные вы колонки добавляете или виртуальные. Разница только в том, что при создании virtual сами данные не перестраиваются, поэтому это быстро. Но гибкость это немного другой термин, нежели производительность. В реальности изменение структур происходит часто — на этапе разработки (когда данных еще нет или мало), и очень редко — далее, а то и вовсе никогда. С другой стороны в случае virtual колонок их значения будут вытаскиваться из json при каждом запросе, что явно нифига не производительно. Если же их делать stored/persistent — то получилось шило на мыло (в плане якобы «гибкости»).
Коллеги, а может подскажете, как хранить кастомные формы пользовательские — JSON или какие-то структуры с набором таблиц?
Пример: есть проект, у проекта пользователь может создавать произвольную форму из произвольных полей (текст, значение, интервал и т.п.) аля бриф.
Как сию структуру лучше хранить для последующей работы и выборки / формировании статистических данных и группировок?
Пример: есть проект, у проекта пользователь может создавать произвольную форму из произвольных полей (текст, значение, интервал и т.п.) аля бриф.
Как сию структуру лучше хранить для последующей работы и выборки / формировании статистических данных и группировок?
Для начала хорошо бы ответить на вопрос «А зачем вообще хранить ЭТО в БД?»
Поясните, чайнику, как лучше-то сделать? И как можно это НЕ хранить в БД, если проект многопользовательский и… файлы? Как-то странно их использовать. Какие-то ещё варианты? Смутно представляю себе, чем заменить MySQL в системе без костылей по работе с другим типом хранилища.
Смотрите: если вы храните формы в БД значит, вероятнее всего, при работе они формируются динамически. Любой вменяемый специалист по интерфейсам скажет вам что динамическая генерация UI — это путь в ад.
Но если очень хочется или по-другому никак не получается то, навскидку, есть два основных варианта (в условиях когда форма действительно может быть произвольной): вариация на тему EAV или JSON. В принципе (это к моему изначальному вопросу) вы с этими данными будете делать ровно две вещи: класть их в БД (целиком для одной формы) и читать их оттуда (ровно так же — целиком для одной формы). Поэтому на сегодняшний день я бы, пожалуй, смотрел в сторону JSON'а.
Но если очень хочется или по-другому никак не получается то, навскидку, есть два основных варианта (в условиях когда форма действительно может быть произвольной): вариация на тему EAV или JSON. В принципе (это к моему изначальному вопросу) вы с этими данными будете делать ровно две вещи: класть их в БД (целиком для одной формы) и читать их оттуда (ровно так же — целиком для одной формы). Поэтому на сегодняшний день я бы, пожалуй, смотрел в сторону JSON'а.
Есть ещё вариант добавить N столбцов заранее в таблицу и использовать их по мере потребности.
Конфигурации абсолютно произвольные могут быть, поэтому заранее добавить вряд ли имеет смысл. Я рассматривал изначально вариант создания нескольких таблиц: одна для хранения информации о самой форме, а вторая — поля + тип данных + FK на форму родительскую. Правда как потом статистику по этому делать и гибкие выборки с фильтрам — так глубоко и не заходил.
Из всего многообразия вариантов это, пожалуй, самый неудачный. Так делать не надо вообще никогда. Нужно объяснять почему?
Конечно объясните.
1. У вас получается широченная таблица, большая часть которой большую часть времени вообще не нужна
2. Вы обязательно огребаете 100500 проблем из за NULL'able полей (а по-другому нельзя)
3. Рано или поздно вы придёте к ситуации когда полей снова не хватает
4. Имена полей у вас совершенно идиотские и не несут в себе вообще никакой семантики
5. В похожих ситуациях разные разработчики используют разные (по порядку) поля что ещё больше добавляет хаоса
6. Вы не можете вести никакой внятной индексной политики
Я могу и дальше продолжать, но жалко времени. Просто примите как данность (я занимаюсь проектированием БД почти четверть века): никогда так не делайте. Совсем никогда.
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. Если 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. Можно да, а можно нет. А в данным случае вы будете это делать обязательно.
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 секунд.
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 с этим по моему никогда проблем не было.
А в чём тут high load? Рассказали про такой тип данных в MySQL, но не сравнили скорость работы по сравнению с типизированным колонками, с другими реализациями JSON-хранилищ. Половина статьи — как обойти функциональные ограничения типа JSON в MySQL. ALTER TABLE — «не хорошо», а вот полное пересоздание таблицы ради хранимых колонок, дублирующих данные — это можно. Извлёк для себя то, что для хранения динамической схемы MySQL по-прежнему не пригоден. Надо или сильно улучшать реализацию типа JSON, либо вообще делать отдельный движок хранения.
По моему мнению для хранения исторических данных не нужны транзакционные СУБД. Нам не нужно потом править или удалять записи. Нам нужно делать отчёты, вертеть так и сяк, «майнить».
В 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
;
Sign up to leave a comment.
Гибкая схема хранения данных в MySQL (JSON)