Базы данных. Вопросы по архитектуре

[ger_kar]

Хотелось бы обсудить вот какую тему.
В любой базе данных, если не принять изначально специальных мер, при накоплении записей в таблицах, особенно тех, на которых строятся отчеты, начинаются тормоза. И если для оборотных это еще не так заметно (например продажи за определенный период), то для накопительных(тех которые содержат остатки) это куда как более критично, ибо для того, чтобы правильно посчитать остаток надо все просуммировать от самого начала времен. Поэтому чтобы этого избежать придумали кучу всяких ухищрений. Из них наверное самая мощное - это OLAP кубы. Но многие идут другими путями. Например 1С в таблицы регистров ввела дополнительные таблицы для хранения итогов. Можно например не делать дополнительных таблиц, а сделать дополнительные поля, куда для каждой записи сразу рассчитывать и вносить итоги. У каждого из этих архитектурных решений (которые я перечислил) есть свои достоинства и свои недостатки. И наверняка есть и другие способы, о которых я например не знаю, но могут знать другие участники. Сейчас базы данных встречаются достаточно часто, от настольных приложений до WEB программирования, поэтому многие участники форума сталкивались и решали описанную проблему. Вот и хотелось бы знать кто и как решает подобную задачу. Или где про это можно было бы почитать.
Искал в интернете информацию на предмет обобщения и сравнения разных технологий, но толком ничего не нашел.

[iGrok]

Тут, как и везде, нет серебряной пули.

При правильной расстановке индексов нормальные СУБД спокойно справляются даже с очень большими объёмами данных. Тому же Ораклу, насколько я помню, и табличка в 300Гб вполне себе по зубам.

А так вариантов действительно масса - от "свёртки" больших периодов в отдельные таблицы, до кеширования результатов частоиспользуемых выборок на уровне бизнес-логики.
Конкретное решение проще подбирать под конкретную проблему, потому что иначе можно "подстелить соломки" совсем не там, где надо.

[ger_kar]

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

И что, каждый сам изобретает, свой велосипед? Просто хотелось бы найти систематизированную информацию на этот счет. "Велосипедов" на изобретали немало, а шишек на этом набили наверное еще больше и очень странно, что в сети на этот счет информации как кот наплакал.

[iGrok]

И что, каждый сам изобретает, свой велосипед? Просто хотелось бы найти систематизированную информацию на этот счет. "Велосипедов" на изобретали немало, а шишек на этом набили наверное еще больше и очень странно, что в сети на этот счет информации как кот наплакал.

Я, наверное, недостаточно понятно написал.

Я имел в виду что на самом деле это не проблема. В 99% случаев нормально настроенная БД справляется с этим сама.
Последний процент - это уникальные случаи под которые просто не подойдёт любое "общее" решение. Поэтому и нет какой-то общепринятой практики решения этой проблемы - каждый пишет свой уникальный костыль под свой уникальный случай отталкиваясь от своих уникальных требований.

[ger_kar]

Я имел в виду что на самом деле это не проблема. В 99% случаев нормально настроенная БД справляется с этим сама.

При правильной расстановке индексов нормальные СУБД спокойно справляются даже с очень большими объёмами данных. Тому же Ораклу, насколько я помню, и табличка в 300Гб вполне себе по зубам.

Ну справиться то она (СУБД) может и справится, только вопрос в какое время будет выливаться? И опять же это все таки Оракл! А если брать что нибудь из массового, народного репертуара? Просто охота расставить все точки над "И" в процессе проектирования, а не тогда когда база разрастется и начнутся тормоза. Тогда уже будет поздно.

[iGrok]

Ну справиться то она (СУБД) может и справится, только вопрос в какое время будет выливаться?

В разумное.

И опять же это все таки Оракл!

А у тебя и табличка, надо думать, всё ж таки не на 300Гб, да?

А если брать что нибудь из массового, народного репертуара?

Ну, вот у меня под рукой mysql - табличку почасовой статистики за 5 лет на 130кк строк (6.5Гб) полностью суммирует за 20с. Это дофига, конечно, но это и не нужно. А по часто используемым запросам считается всё максимум за 5с (а в основном меньше 1с). В данном случае такой результат всех устраивает, поэтому вообще нет смысла городить что-то сверху.

Но в другом проекте к ней добавлена табличка с посуточной агрегацией той же статистики, и она, конечно, считается в разы быстрее (там уже нужно).

Просто охота расставить все точки над "И" в процессе проектирования, а не тогда когда база разрастется и начнутся тормоза. Тогда уже будет поздно.

Это решается мониторингом "самочувствия" БД и проекта в целом, чтобы поймать тормоза не когда будет поздно, а когда они только начнут намечаться. И вовремя принять меры.

Ну, в любом случае, "базовый" принцип тут один - любимый принцип Хакера - принцип "лени". Не пересчитывать каждый раз то, что можно посчитать только один раз. Мне вот нужны помимо почасовых данных (и, в общем-то, даже чаще) посуточные - они собираются в отдельную табличку а итог собирается запросом из двух таблиц за устраивающее всех время. Когда оно недавно перестало устраивать, выяснилось что наш чудо-DBA забыл сделать индексы. Полтора года без них жили, оказывается... А ты говоришь, точки расставить.

В другом месте нужно всё время иметь представления о примерном "балансе", но именно о примерном. Поэтому он пересчитывается раз в 5 минут, и лежит в кэше. После рестарта сервера 5 минут там будет пусто, но это не критично. Было бы критично - придумали бы другой вариант.

У тебя, скорее всего, требования будут другими, и оптимальное решение в итоге будет другим.

Какие-то паттерны тут, может быть, и есть, но я с ними пока не сталкивался, а остальные чо-то пока молчат.

[ger_kar]

А у тебя и табличка, надо думать, всё ж таки не на 300Гб, да?

Ну просчитать размер я даже приблизительно не берусь, да и не знаю даже как , а вот прикинуть по количеству записей примерно можно. Если взять например среднее количество документов в день (продажи) 200 и в каждом документе будет в среднем 12 позиций товара (опять же в среднем), то за год работы может накопиться 200 * 12 * 360 = 864 000 Плюс сюда добавятся начальные остатки, ну и приходы товаров. Так что за первый год будет порядка 900 000 записей, а то и больше. Я думаю, что уже при таком количестве начнет слегка подтормаживать. И если например при формировании отчета это будет не так критично и задержка сек 10 на все расчеты и формирование самого отчета оформленная индикатором прогресса будет вполне уместной, то вот для оперативного контроля остатков это будет уже совсем не хорошо, особенно через пару лет работы. Поэтому в любом случае придется делать какие либо ухищрения.

[iGrok]

порядка 900 000 записей

Ну я повыше привёл пример со 130 000 000 записей. Это немножко больше, чем 900 000.
9 000 000 строк тот же mysql (ну, не тот же, соседний) кушает за 3 секунды. Сервер там, правда, весьма приличный. Не Pentium-III 800Mhz/256Mb, конечно.

Раз это товарооборот, тогда таки есть смысл подбивать данные посуточно и хранить их. Так и ряд отчётов упрощается. А что именно хранить - "движения" за сутки или итоги за сутки, или что-то вообще другое - зависит, опять же, от задачи.

[ger_kar]

Ну, в любом случае, "базовый" принцип тут один - любимый принцип Хакера - принцип "лени". Не пересчитывать каждый раз то, что можно посчитать только один раз.

Ох уж это принцип лени! Отнимает покой и доставляет столько хлопот и беспокойства И эта тема возникла благодаря ему (этому принципу). Именно он не даёт перейти на .net. С одной стороны там конечно куча всяких новомодных штучек типа LINQ, Entity Framework и аналогичных. Но как представлю, сколько там производится лишней работы в виде одних только вызовов, так сразу и не по себе. VB6 в основном используется как визуальная часть, а все критичные участки кода можно вынести в библиотеки и в них используя низкоуровневые штучки и инструменты от PowerBasic, C++, C, до ассемблера (мне больше всего нравится PowerBasic с ассемблерными вставками) добиться максимального быстродействия, то с .net, такое уже не прокатит.

Но в другом проекте к ней добавлена табличка с посуточной агрегацией той же статистики, и она, конечно, считается в разы быстрее (там уже нужно).

Ну для моей задачи посуточная агрегация наверное точно будет лишней, а вот месячную наверное нужно будет сделать.

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

Ну как прочитал это, то сразу представилась 1С, там как раз пошли по такому же принципу. Но даже если пойти по этому пути (агрегирование данных за определенный период), то сразу возникает вилка решений:

• Использовать приём с двумя таблицами, как было описано
• Собирать итоги в этой же таблице и помечать их флагом, который проставлять в отдельном столбце

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

[ger_kar]

Пока писал ответ пришло сообщение, по этому по нему напишу здесь:

Ну я повыше привёл пример со 130 000 000 записей. Это немножко больше, чем 900 000.
9 000 000 строк тот же mysql (ну, не тот же, соседний) кушает за 3 секунды. Сервер там, правда, весьма приличный.

Ну, тут надо рассчитывать на среднестатистический компьютер в роли сервера (на данный момент конечно) и то обстоятельство, что могут быть несколько запросов от разных клиентов одновременно. У меня таких ёмких таблиц для экспериментов пока нет, поэтому большое спасибо за приведенную статистику.

[ger_kar]

Кроме агрегирования данных за определенный период, есть и другой метод. Правда он применим в отличии от первого только к хранению остатков. Суть его в том, что вводится дополнительное поле и в него для каждой добавляемой записи сразу рассчитывается итог и добавляется в это поле. Так как итоги для каждой записи (с учетом данных этой самой записи) всегда известны, то итог считается очень быстро простым суммированием данных текущей записи с итогами предыдущей. Все замечательно и хорошо, если бы не огромный минус этого метода. Он достаточно хорош и можно сказать практически идеален, но только пока в таблице (выражусь в терминах 1С) одно измерение или разрез учета. А вот если таковых будет 2 или больше, то тогда по идее нужно подводить итоги для всех возможных комбинаций существующих измерений и вставлять уже не одну единственную строку, а кучу, по совокупности возможных комбинаций. Конечно такая проблема есть и для метода с двумя таблицами, то там она сглаживается тем, что так можно хранить только итоги, а тут все данные будут храниться только в таком виде. Особенно весело становиться при таком раскладе, если потребуется добавить еще одно измерение (разрез учета), а так как учет на месте не стоит, то такая потребность периодически случается.
Как этого избежать мысли есть, но на практике я пока их не испытывал. Мысль такая: Построить саму базу таким образом, что-бы там, где будут рассчитываться остатки было всегда только одно измерение. А если нужно два и ли более, то для них делать отдельные таблицы и все выносить в них. Если бы найти нормальное универсальное решение, решающее эту проблему удобным способом, то это самый хороший вариант с моей точки зрения.

[iGrok]

У меня таких ёмких таблиц для экспериментов пока нет

Ну, это же не проблема. Небольшой скрипт - и вот тебе уже табличка с любым кол-вом строк.

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

Нарушение логической структуры - разнородные (хоть и очень близкие) данные в одной таблице. А реальных плюсов по сравнению с двумя таблицами нет. Поэтому и не встречал.

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

Ну, я бы тогда использовал две временные отметки - время внесения записи, и время, "за которое" она вносится.
Тогда можно будет агрегировать/разделять данные по времени внесения записи и по последней дате в агрегационной табличке видеть, что у нас ещё не обсчитано и с какого места считаем остальное.

другой метод

Вообще мимо кассы. Слишком сложно. Нет возможности простым запросом получить сводку по всем позициям. Нет возможности проконтролировать целостность данных. Внесение изменений задним числом становится вообще жуткой задачей.

[ger_kar]

Нарушение логической структуры - разнородные (хоть и очень близкие) данные в одной таблице. А реальных плюсов по сравнению с двумя таблицами нет. Поэтому и не встречал.

Логично, я про это даже не подумал.

Ну, я бы тогда использовал две временные отметки - время внесения записи, и время, "за которое" она вносится.Тогда можно будет агрегировать/разделять данные по времени внесения записи и по последней дате в агрегационной табличке видеть, что у нас ещё не обсчитано и с какого места считаем остальное.

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

Вообще мимо кассы. Слишком сложно. Нет возможности простым запросом получить сводку по всем позициям.

Ну при условии если в таблице будет один разрез учета все достаточно просто, а сводку по всем позициям и при использовании двух таблиц одним запросом не получить (имеется ввиду запрос без подзапросов внутри).

Внесение изменений задним числом становится вообще жуткой задачей.

На самом деле вот это как раз и не сложно. Я первый раз тоже так подумал, но потом немного поразмыслив пришел к такому решению: При внесении записи проверять является ли она последней по хронологии и если нет, то запускается запрос в котором от времени вносимой записи (с её естественно включением) производится агрегатное суммирование по полю с данными и результат сравнивается с последним итогом. Точнее даже не так, он не сравнивается, а вычисляется дельта и если она не нулевая, то запускается запрос на обновление и все итоги корректируются на это значение дельты. Алгоритм прост до безобразия и работает безотказно и быстро.

[iGrok]

Ну при условии если в таблице будет один разрез учета все достаточно просто, а сводку по всем позициям и при использовании двух таблиц одним запросом не получить (имеется ввиду запрос без подзапросов внутри).

Ну, там два подзапроса - по одному на таблицу, и потом агрегация данных.

А из твоего варианта общую сводку вообще нельзя получить "простым" способом. Я вижу только вариант с подзапросом на каждую позицию.

При внесении записи проверять является ли она последней по хронологии и если нет, то запускается запрос в котором от времени вносимой записи (с её естественно включением) производится агрегатное суммирование по полю с данными и результат сравнивается с последним итогом. Точнее даже не так, он не сравнивается, а вычисляется дельта и если она не нулевая, то запускается запрос на обновление и все итоги корректируются на это значение дельты. Алгоритм прост до безобразия и работает безотказно и быстро.

Это вообще ни разу не "прост до безобразия".
Но если тебе нравится и тебя устраивает - то, конечно, пожалуйста.

Но вообще, KISS.

[ger_kar]

Это вообще ни разу не "прост до безобразия". Но если тебе нравится и тебя устраивает - то, конечно, пожалуйста.

А если сравнить этот процесс коррекции данных, с процессом коррекции в модели с двумя таблицами? На мой взгляд он ничуть не сложнее. И дело не в том нравится мне этот метод или нет. Тут скорее другое, так я уже делал и просто более четко его себе представляю в отличии от модели с двумя таблицами. И на данный момент я стараюсь объективно оценить эти два подхода с разных сторон и выбрать лучшее решение, так как у каждого подхода есть как плюсы, так и минусы.

[ger_kar]

Кстати, а есть ли устоявшиеся названия у этих методов? Я не думаю, что второй метод - это результат моих измышлений, и хотя я про него нигде не читал, скорее всего он где то применялся и применяется и значит описан.

[iGrok]

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

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

Кстати, а есть ли устоявшиеся названия у этих методов? Я не думаю, что второй метод - это результат моих измышлений, и хотя я про него нигде не читал, скорее всего он где то применялся и применяется и значит описан.

Я его встречаю первый раз, честно говоря.

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