Как лучше (дешевле) хранить справочники?

Question

[Дмитрий Ларин]

Разрешите пожалуйста вопрос - в каком виде лучше хранить справочники.
Допустим имеем такого коня в вакууме - интернет магазин (на yii если это важно) с интеграцией 1с, откуда подтягиваются все данные, в т.ч. справочники цветов, размеров, производителей и т.п и т.д. Так вот как это лучше и дешевле хранить? Заводить под каждый справочник отдельную таблицу? Хранить все в одной? Или справочники хранить в виде xml(json - зависит от религии) файлов, а при необходимости их считывать? Какой способ дешевле по затратам озу/цп? Допустим у нас еще небольшой хайлоад в виде 10к+ хитов в сутки.

Comments

[ky0]

Ору. Небольшой хайлоад...

[Дмитрий Ларин]

Заказчик считает это "хайлодом". Я честно пытался его разубедить. Нишмогла.

[Kravalg]

Дмитрий Ларин, ты же понимаешь, что при "хайлоаде" и зарплаты у разрабов highload :)

[sim3x]

Чем справочник занимается?
Как еих используют
Насколько они отличаются друг от друга?

Вместо Х хитов в сутки укажите ваш пиковый rps

[Дмитрий Ларин]

sim3x,

Чем справочник занимается?

Их несколько. Каждый хранит свой кусок из 1с. Цвет, номенклатура, детали, клиенты и тд.

Как еих используют

По всему сайту и в админке и в публичке. Подтягивают оттуда данные, меняются редко.

Насколько они отличаются друг от друга?

Довольно сильно. Если в цвете 3 поля, то вот в клиентах уже с десяток полей

Вместо Х хитов в сутки укажите ваш пиковый rps

Это расчетный, проект пока в разработке.

Редко меняются, часто подтягиваются. Какая нибудь nosql база? Монго?

[Алексей Черемисин]

Дмитрий Ларин, а вот, кстати, про nosql, настоятельно рекомендую посмотреть на эластиксерч.

[sim3x]

Дмитрий Ларин, у вас уже есть постгрес - используйте жсон

Я не понимаю как у вас так получилось, что товар отделен от справочников?
И как клиенты оказались в статическом справочнике, который еще и подтягивается отдельно?
Ето не просто так вопросы
У вас явно проблема с проектированием, что может сильно сказаться на результате

Перед тем как смотреть на еластик посмотрите видео от создателя сфинкса

[Дмитрий Ларин]

sim3x, старый магазин, старая 1с, старый сайт на древней cms... когда то сайта не было и все клиенты велись в 1с...
Короче, вопрос не в проектировании. Вопрос в том как лучше, дешевле по ресурсам, хранить справочники.

[sim3x]

Дмитрий Ларин,
Дешевле перенести в правильную и удобную структуру, пока нет клиентов

Answer 1

[Алексей Черемисин]

10k в секунду, нормально - это highload, а в сутки - вообще ни о чем ~ 7 запросов в минуту
Заводить под каждый справочник отдельную таблицу - это путь в сторону битрикс и могилы.
Хранить в xml/json - зачем, если есть база данных.
Мне кажется достаточно отдельной таблицы id|name|value|type, ну пары таблиц, еще одна с описанием.
Ну и кешировать все это в redis. Считывать все при старте и загружать в него.

Comments

[vyachin]

это я лично Вам настоятельно рекомендую почитать https://habr.com/post/254773/

[Дмитрий Ларин]

Вот не согласен. На мой взгляд свалить все в 1 таблицу это как то совсем неправильно. Формы нормализации не от скуки придумали.

[Алексей Черемисин]

vyachin, Дмитрий Ларин,
Хорошо, ну тогда скажите, где моя маааленькая таблица нарушает хоть какую-то форму нормализации?
И чем плоха одна таблица?

[Дмитрий Ларин]

Алексей Черемисин, На маленьком проекте ничем. А вот когда справочников море, то получается гигантская таблица с кучей разных полей.
И да кстати если не знали то в битриксе инфоблоки и свойства хранятся в 2 таблицах. И лишь потом когда поняли что на больших проектах каша и бд узкое место, нам представили highload инфоблоки. От обычных они отличаются тем что на каждый highload иб создается отдельная таблица. А так как highload иб чаще всего используются как справочники...даже битрикс отказался от идее хранить все в куче.

[d-stream]

Алексей Черемисин,

Некоторые справочники - это код-текст, некоторые код-числовое значение, притом числовые значения могут быть как интами, так и десятичными. Притом некоторые жестко 2 знака после запятой, некоторые - 4...

Справочник единиц измерения - это как минимум наименование, сокращение и код ОКЕИ

Справочник стран - пяток значимых полей...

Вот тут здравый смысл начинает бунтовать - на кой ляд всю эту селедку смешивать с молоком, разбавлять горчицей и украшать майонезом? Чтобы чуть позже начать из этого коктейля добывать ингредиенты обратно и по отдельности?

[Алексей Черемисин]

Дмитрий Ларин, у битрикса как раз и беда, что полей дофига и поэтому индексы для поиска построить невозможно, отсюда и каша в базе. Хайлоад блоки хоть как-то улучшают ситуацию только потому, что полностью отданы на откуп разработчику.
Что же касается больших таблиц, а лучше сказать - с большим количеством записей, то в этом абсолютно ничего страшного нет. Когда писали биллинг, то таблицы за день могли содержать несколько миллионов записей, и ничего, быстро все работало. Главное, правильно все спроектировать. Ну и на мой взгляд, просто крупную таблицу гораздо легче разбить на шарды (по хешу, дате номеру котракта или какому еще признаку), так как они будут все одинаковые по отношению к таблице-родителю. Легче будет разнести на несколько инстансов, легче бекапить и прочее-прочее-прочее. А вот создавать по таблице на каждый чих - самое фиговое занятие, поддерживать это через год уже невозможно в принципе, а с ростом проекта ситуация будет только усугубляться (битрикс привет).
И это никак не относится к нормализации самой базы и данных в ней.

[Алексей Черемисин]

d-stream, что такое справочник? Обычно это ключ-значение-тип-ид. Какие запросы мы делаем к справочнику? Получить значение по ключу и/или по ИД, обратных запросов вообще практически нет. Конвертировать строку в любой тип на программном уровне вообще без проблем! Это конечно не касается случаев, когда у нас например счетчики, но тогда это и не справочник.можно конечно сказать, что строка и число занимают совсем разное место. Да можно, но во первых - это таки справочник, во вторых никто нам не запрещает иметь и числовые справочники отдельными таблицами... Но вот отдельная таблица на каждый справочник - я такое у себя не позволю, уж очень серьезные аргументы мне нужно привести, почему нужно на 10-20 значений заводить таблицу! Да, может быть мускул к этому и подталкивает, но вот в сайбейс или какой другой энтерпрай-базе, таблица, не хрен собачий, и иногда на десяток записей жрет места больше них самих.

[d-stream]

Алексей Черемисин,

что такое справочник? Обычно это ключ-значение-тип-ид.

примитивные примеры с более чем развитыми вариантами я привел выше. Притом это примитивнейшие примеры без иерархии например.

Какие запросы мы делаем к справочнику?

например получить по id код страны по ISO3166, иногда найти название страны по alpha-3

Конвертировать строку в любой тип на программном уровне вообще без проблем!

Да, особенно когда вдруг в поле nvarchar окажется 1'0876.49 а тип - якобы decimal )

Но вот отдельная таблица на каждый справочник - я такое у себя не позволю, уж очень серьезные аргументы мне нужно привести, почему нужно на 10-20 значений заводить таблицу!

Аргумент можно привести простой - или это надо понять или же потоптаться на этих граблях некоторое время...

Да, может быть мускул к этому и подталкивает, но вот в сайбейс или какой другой энтерпрай-базе, таблица, не хрен собачий, и иногда на десяток записей жрет места больше них самих.

Гм... ну вот тупенький пример: предположим речь идет о некой абстрактной торговой фирме, которая производит и продает катера и яхты. Заказчик приобретает в лизинг катерок + небольшие доукомплектации и модификации... тьфу, да ладно... просто выплюнуть из базы данные под УПД - ну там поставщик, грузоотправитель, покупатель, каждый с инн/кпп, адресами (возможно разными), ну и сами строки: коды и наименования товаров и услуг, единицы измерения и их коды и обозначения, налоговые ставки, страны происхождения и номера ТД

ну и плюс подписанты с хвостиками в виде "на основании доверенности"/"на основании приказа" и т.п.

И все это надо скормить репортеру - пара датасетов (шапка и строки) как будет выглядеть парочка запросов с многоэтажными join справочника-винигрета? как оно будет весело работать с пакетной печатью сотни УПД на сотню-тысячу строк каждая? И что будет когда формат документа изменится, как это было пару раз в 2017 году? )

[Алексей Черемисин]

Отвечу просто, я не сторонник городить трехэтажные джоины, я сторонник переносить всю возможную логику на программную сторону. Поэтому всем рекомендую сделать пару (а может и десятка два) лишних запроса, вместо попытки получить все в одном. На то есть ряд проверенных временем и производительностью оснований.
1) простые запросы легче прожевываются оптимизатором и самой базы, и легче кешируются на стороне базы.
2) простые запросы легче кешируются на стороне приложения.
3) любая база данных, если мы говорим про sql, ресурс сильно ограниченный лицензиями, потоками, производительностью сервера, возможностью расширения (и тд), поэтому его крайне нежелательно нагружать триггерами, хранимками, сложными запросами в стиле olap, и прочей чушью. Главная задача базы -обеспечивать консистентность хранимой информации. Дя, я понимаю, что планировщики могут прожевать запрос на 200 таблиц и тыщщу полей с джоинами сабселектами, хранимками по триггерам (а еще это просто красиво), но кому оно нужно, когда пара таких запросов выбивает полностью кеш базы, превращая всю производительность сотен тысяч долларов в тормозного червя.
4) вычислительную мощность программной части гораздо легче нарастить, как по горизонтали, так и по вертикали. На текущий момент купить еще один сервер/инстанс для бекенда проще на порядок, чем сделать мастер-слев реплику любой базы. В программной части мы практически не ограничены в ресурсах (при хоть немного правильной архитектуре), а вот с базами данных такой фокус желательно закладывать на этапе проектирования! Имея один-два сервера бд, пару кеширующих серверов и с десяток бекендов нам проще выполнить с десяток запросов, четко попадающих в кеши к десятку таблиц, чем городить джоины на сотню.
5) программный код легче поддерживать, отлаживать и модифицировать, чем бороться с эксплейнами, сбросами кешей базы и отладкой sql,причем мапинг домен-данных будет на порядок проще, как и отладка бизнес-логики на их основе.
6) нужно генерировать отчеты, берите olap или nosql, и не терзайте то, для чего оно не предназначено.

[vyachin]

Алексей Черемисин, вы нарушаете вторую нормальную форму, у вас значение свойства будет зависеть от id сущности и от типа. Я бы с натяжкой еще добавил, что вы нарушаете и первую НФ, у вас поле "значение" какого типа будет ? там же может храниться все от чисел до длинных строк.

[Алексей Черемисин]

vyachin, давайте немного остановимся
1) я говорил совсем не о конкретной реализации, а о принципе хранения, что и вопрошал автор вопроса!
2) как приблизительный пример привел на скорую руку (!!!) первое, что пришло в голову и соответствует моей идее.
3) автор вопроса вообще не дал никаких данных по полям/объектам и прочей фигни, так что разбирать досконально, какие поля должны быть в какой таблице, смысла не вижу.
4) Вы мне ставите в укор не принцип хранения (смотрим суть вопроса(!)) а пример, к делу совсем не относящийся, приведенный для полноты картины. Не находите странным?

Ну и если пошла такая дискуссия, вот приблизительная схема, как я ее вижу.

Таблица характеристик (можно сделать отдельно группировку по группам, добавить доступы, множественные значения и т.д., но здесь это рассматривать совсем не собираюсь)
PropNames
prop_id (number) | name (char) | type (char)
1 | color | string
2 | weigth | long

Таблица значений характеристик, связанная с товаром
PropValues
product_id (number) | prop_id (number) | value (char)
1000 | 1 | red
1000 | 2 | 10.0

И да, на этом дискуссию завершаю.

[vyachin]

Алексей Черемисин, но вы все еще нарушаете 1 нормальную форму для таблицы "значений характеристик" Вы неправильно используете тип для колонки value. В этой колонке вам придется хранить числа, даты, строки, бинарные данные или даже не атомарные значения - какой нить json. Ваша структура не позволит вам использовать индексы, внешние ключи. Вам придется самому заниматься переводов между типами данных. К тому ваша схема не оптимальна с точки зрения памяти. PS. Вам надо на время отложить все ваши книги по nosql решениям и вернуться к основам - реляционным базам данных.

[Алексей Черемисин]

vyachin, Вы меня не полностью читали, я уже писал, что

Конвертировать строку в любой тип на программном уровне вообще без проблем!

и

Какие запросы мы делаем к справочнику? Получить значение по ключу и/или по ИД, обратных запросов вообще практически нет.

И да, отступаем от первой нормали, что в этом плохого?

PS. Реляционные базы отложил, лет эдак 10 назад после лет эдак 10 непрерывного использования. И последние лет эдак 10 занимаюсь noSQL и не только, а учебников тогда еще не было. И да, я старый хрен, для кого-то почти старик, но девочки западают, челюсть не висит!

[d-stream]

Алексей Черемисин, я не могу конечно рушить религиозные убеждения, но именно sql - это то место, где должны обеспечиваться атомарные (в смысле неделимости) операции. Любые попытки имитировать транзакции в другом слое - обречены на прблемы. Если интересно - могу развернуть подробно.

[Алексей Черемисин]

d-stream, sql (точнее - базам данных) не нужно быть атомарным, у них совсем другая задача, обеспечить целостность (консистентность) данных, и только! Про атомарность - это совсем из другой оперы.
И вот если всерьез поразмыслить над консистентностью (атомарность как совсем частный случай), то мы придем к совершенно разным ее реализациям. Версионностью, как в оракл или постгрес; блокировками и журналами, как в сайбейс, эмэссиквел; журналированием, как в дб2. Что же касается транзакций, то это тоже, всего лишь одно из решений обеспечения целостности.
Так что, боюсь, вам следует отбросить ваши учебники по sql, и прочитать таки введение в теорию баз данных.

ЛУчше цитатами из учебников

Набор принципов, определяющих организацию логической структуры хранения данных в базе, получил название модели данных. Модели баз данных определяются тремя компонентами:
- допустимой организацией данных;
- ограничениями целостности;
- множеством допустимых операций.

И да, если интересно, могу и развернуто, часами и курсами.

[Алексей Черемисин]

Вот что хочу сказать, как старый хрен, я таки знаю как работают те же самые транзакции внутри разных баз данных, да-да, именно на уровне исходного кода. И уверяю вас, если вы на них полностью полагаетесь, в плане целостности и "атомарности", то даже не буду вас разубеждать, эту боль нужно преодолеть, чтобы прийти к пониманию, когда следует, а когда и нет.

[d-stream]

Алексей Черемисин, реализация той же самой атомарности на уровне более высоких звеньев, с неиспользованием механизмов уровня ниже???

[Алексей Черемисин]

d-stream, поясните, что имеете ввиду под "уровнями" высокими и низкими, и что не хотите использовать? атомарность?
Ну, давайте я все записи буду поменять каким-то уникальным ID, и буду вести журнал этих ID и самих записей? это будет похоже на внутренности постгрес или оракл? Вопрос на засыпку...

[d-stream]

Ну, давайте я все записи буду поменять каким-то уникальным ID, и буду вести журнал этих ID и самих записей? это будет похоже на внутренности постгрес или оракл? Вопрос на засыпку...

Не, я не хочу заглубляться до уровня гравитационного воздействия от магнитной головки винчестера на электроны в транзисторе процессора. Это тут не надо.
Все проще - достаточно ориентироваться на атомарность в виде sql операций (про псевдо - тож не надо). Посчитаем что sql-транзакция - это нижний уровень. Выше - это уже базовые бизнес-операции, следующий - группы этих операций.
Ну и классическое из простых примеров "типа банковской транзакции" - с отнять у одного и добавить столько же у другого*
если речь идет об операции в рамках sql-транзакции с апдейтами нескольких полей - это тот самый низкий уровень. Если же выполнять такую операцию на другом звене (к примеру трехзвенка) - то далее она выльется в две отдельные операции и обеспечить устойчивость (целостность) - потребуется гораздо больше усилий со всеми вытекающими костылями и точками отказа.

Теперь возвращаясь к "простым джойнам" и реализации операций на стороне приложения...
данные в Австралии, приложение - в Якутии.... и что получится? Забрать все и донести до Якутии, где 99.99999% отсечется, а оставшийся 0.000001% будет использован?)
В том примере про УПД - таки надо отдать репортеру (MSRS, Crystal или еще что-нибудь) конкретный набор данных (датасеты шапки и строк документа). Что подразумевает join справочников или одного винигретного, из которого надо бы добыть что относится к чему)
______________________________
* - утрированный пример

[Алексей Черемисин]

d-stream, Утрированный пример... Да уж... Я немного, как понимаете, из другого мира... Большие дяди уже давно и такое придумали, лет эдак 20 назад. Называется - распределенная транзакция, в частности реализована и не первой, например в JEB. Это когда данные за Уралом, а заказчики в Австралии, смежники в штатах, и все в одной "распределенной" транзакции. А одна из первых реализаций - стандарт CORBA на основе RPC.
Нравится вам SQL, он мне тоже нравится, и распределенные транзакции нравятся, и noSQL нравится. И я не боюсь ни того, ни другого, ни третьего, но всему свое место, и зачастую не нужно ездить не танке и вспахивать поле.

Еще раз - SQL совсем не про атомарность! Вообще! Иначе зачем оно нужно придумывать какие-то к хрену уровни изоляции. Ставим seriazable и, типа жывем, как-то.

[d-stream]

Я немного, как понимаете, из другого мира...

Да, понимаю. Вглядываюсь в тег темы и понимаю что точно не от сего)

[Алексей Черемисин]

d-stream, с тегими все хорошо, постгрес есть, php тоже. Все реализуется на раз и на том и на другом.

Answer 2

[thyratr0n]

Вижу, народ тут нормализацией увлекся. Поясню: есть 6 форм нормализации, а есть суровая реальность. Точка. И нечего тут в адрес Алексей Черемисин указывать на какие-то нарушения.

Справочники нужно хранить так, как это возможно. Если данные однотипны:
1) если данные в них однотипны, можно скидать все в одну таблицу, запилив составной РК (type, key) или по старинке одинарный - это как душе угодно (пока количество записей не более, чем 6-ти значные, разницы не будет);
2) можно то же самое запилить в разных таблицах - на вкус и цвет, как говорит.
Если же данные разнотипны, то:
1) можно в разных таблицах, если по отличающимся полям может идти фильтрация;
2) можно все в одну таблицу, запилив какое-то "сложное" поле с типом TEXT/BLOB/VARCHAR, куда писать отличающиеся данные.

Все, никакими тут нормализациями и не пахнет. Всем добра.

P.S. Дмитрий Ларин вам следует более точно формулировать свои вопросы, ибо "Без ТЗ результат ХЗ" (с)

Comments

[Алексей Черемисин]

Спасибо :-)