[Из песочницы] Решения разработки баз данных
Добрый день! В процессе развития проекта приходится сталкиваться с проблемой производительности баз данных, т.к. объём данных растёт, и волей неволей выплывают проблемные участки.В данном посте описаны не очень удачные решения, которые незаметны на ранних стадиях проекта. И решения, которые могут в дальнейшем в разы повысить производительность.
Пост больше рассчитан на более опытных разработчиков, которые уже возможно ищут решения, поэтому буду краток.
Select в результирующих поляхМногие разработчики при написании запросов применяют следующую конструкцию SELECT T.Column1, (SELECT MAX (Column) FROM Table2) as Column3, T.Column2 FROM Table1 T Дело в том, что на каждую строку из таблицы Table1 производится выборка «SELECT MAX (Column) FROM Table2», что требует дополнительных ресурсов для новой выборки. В данном случае производительность будет падать как при увеличении количества данных в Table1 так и Table2.
Приемлемый вариант.
SELECT T.Column1, T2.Column3, T.Column2 FROM Table1 T JOIN (SELECT Max (Column) as Column3 FROM Table2) T2 ON 1 = 1 В таком случае выборка из таблицы Table2 выполнится один раз, и результат подставится для каждой строки из таблицы Table1.
Замена Left Join на Union Возникаю ситуации, когда нужно собрать данные из нескольких однотипных по смыслу таблиц и за частую прибегают к следующему решению SELECT M.Id, COALESCE (P1.Sum, 0) + COALESCE (P2.Sum, 0) + COALESCE (P3.Sum, 0) + … + COALESCE (PN.Sum, 0) as Sum FROM MainTable M LEFT JOIN PayTable1 P1 ON P1.Id = M.Id LEFT JOIN PayTable2 P2 ON P2.Id = M.Id LEFT JOIN PayTable3 P3 ON P3.Id = M.Id . . . LEFT JOIN PayTableN PN ON PN.Id = M.Id У данного подхода несколько проблем:1. Производятся, перемножения таблиц PayTableN и с увеличением таблиц производительность будет падать, даже если данных немного.2. Необходимость использования LEFT JOIN, что во многих СУБД не работают индексы.
Повысить производительность можно путём отказа от операции перемножения в пользу объединения и использования JOIN вместо LEFT JOIN.
SELECT R.Id, SUM (R.Sum) as Sum FROM ( SELECT M.Id, P1.Sum FROM MainTable M JOIN PayTable1 P1 ON P1.Id = M.Id UNION SELECT M.Id, P2.Sum FROM MainTable M JOIN PayTable2 P2 ON P2.Id = M.Id UNION SELECT M.Id, P3.Sum FROM MainTable M JOIN PayTable3 P3 ON P3.Id = M.Id . . . UNION SELECT M.Id, PN.Sum FROM MainTable M JOIN PayTableN PN ON PN.Id = M.Id ) R GROUP BY R.Id К тому же такую конструкцию проще сопровождать, т.е. при добавлении новых таблиц PayTableN нужно добавить соответствующий блок только в одном месть.
Использование скалярных операций в условии объединения Join Есть ситуации, когда в условии объединения необходимо использовать дополнительные операции над полем, в таком случае индекс не используется SELECT T1.*, T2.* FROM Table1 T1 JOIN Table2 T2 ON T2.Id = T1.Id AND COALESCE (T2.Column, 0) = COALESCE (T1.Column, 0) Возможные варианты решения:1. Вынести в условие WHERE, но этот вариант не всегда даст положительный результат, т.к. за вас это мог сделать оптимизатор.
SELECT T1.*, T2.* FROM Table1 T1 JOIN Table2 T2 ON T2.Id = T1.Id WHERE COALESCE (T2.Column, 0) = COALESCE (T1.Column, 0) 2. Разделить запрос на два.
SELECT T1.*, T2.* FROM Table1 T1 JOIN Table2 T2 ON T2.Id = T1.Id AND T2.Column = T1.Column UNION SELECT T1.*, T2.* FROM Table1 T1 JOIN Table2 T2 ON T2.Id = T1.Id AND T2.Column IS NULL AND T1.Column IS NULL Запросы с использованием With Оператор WITH конечно помогает сделать запрос болле понятным и структурированным, но многие не совсем понимают, как он работает и делают следующие ошибки. WITH MainSubQuery AS ( SELECT * FROM Table1 ),
SubQuery AS ( --Здесь на самом деле, какой либо сложный запрос, требующий больших ресурсов SELECT * FROM Table2 )
SELECT * FROM MainSubQuery M LEFT JOIN SubQuery Q1 ON Q1.id = M.id AND Q1.Param = 1 LEFT JOIN SubQuery Q2 ON Q2.id = M.id AND Q2.Param = 2 LEFT JOIN SubQuery Q3 ON Q3.id = M.id AND Q3.Param = 3 LEFT JOIN SubQuery Q4 ON Q4.id = M.id AND Q4.Param = 4 Проблема в том, что многие думают, что подзапрос SubQuery выполнится один раз, и далее будет браться только результат и подставляется там, где он далее потребуется.Но на деле получается следующее:
SELECT * FROM (SELECT * FROM Table1) M LEFT JOIN (SELECT * FROM Table2) Q1 ON Q1.id = M.id AND Q1.Param = 1 LEFT JOIN (SELECT * FROM Table2) Q2 ON Q2.id = M.id AND Q2.Param = 2 LEFT JOIN (SELECT * FROM Table2) Q3 ON Q3.id = M.id AND Q3.Param = 3 LEFT JOIN (SELECT * FROM Table2) Q4 ON Q4.id = M.id AND Q4.Param = 4 Проблему можно решить, прибегая к временным таблицам, т.е. результат тяжелого запроса предварительно положить во временную таблицу и далее использовать. Также данный метод эффективен, если в качестве исходного набора данных используются NICKNAME из мира IBM DB2.
INSERT Session.MainSubQuery SELECT * FROM Table1;
INSERT Session.SubQuery SELECT * FROM Table2;
SELECT * FROM Session.MainSubQuery M LEFT JOIN Session.SubQuery Q1 ON Q1.id = M.id AND Q1.Param = 1 LEFT JOIN Session.SubQuery Q2 ON Q2.id = M.id AND Q2.Param = 2 LEFT JOIN Session.SubQuery Q3 ON Q3.id = M.id AND Q3.Param = 3 LEFT JOIN Session.SubQuery Q4 ON Q4.id = M.id AND Q4.Param = 4; Сопровождение Далее от машинной производительности перейдём к производительности человеческого труда. Речь пойдёт о том, как облегчить рутинную работу поддержки и сопровождения баз данных.В процессе развития проекта приходится пересоздавать процедуры, функции, таблицы и другие объекты баз данных, ноне в каждой СУБД есть конструкция CREATE OR REPLACE PROCEDURE[FUNCTION, TABLE, VIEW, TRIGER, …] и приходитсяв обновлениях постоянно контролировать DROP-ы, ALTER-ы и т.д. в каждой СУБД по разному.
Решением данного вопроса может стать ввод собственных сервисных процедур, например:
CALL[EXECUTE] DropProcedure (SchemaName, ProcedureName) CALL[EXECUTE] DropTable (TableName, TableName) CALL[EXECUTE] DropView (SchemaView, ProcedureView) и т.д. А далее уже производим создание соответствующих объектов не зависимо от того созданы были ранее или нет.
Но при данном подходе также остаётся проблема, связанная с правами доступа к объектам, т.е. при удалении и создании объекта слетают права групп на объекты, т.к. это уже новый объект для СУБД.
Решения могут быть разные, например: Поддерживать скрипт выдачи прав в актуальном состоянии и загружать после каждого обновления (неудобно).Вообще не использовать групповую политику (нехорошо).
Довольно удобным и эффективным решением является ввод пары сервисных процедур, где одна будет запускаться в начале обновления, а вторая в конце.
--Сохранение всех прав CALL[EXECUTE] StoreRights
--Удаление и создание объектов
--Восстановление прав CALL[EXECUTE] RestoreRights Следующий вопрос касается сопровождения структуры БД СУБД MS SQL, а именно в связи с крайне неудобным методом комментирования таблиц, колонок. Что в свою очередь делается редко либо совсем не делается.
Пример:
EXEC sys.sp_addextendedproperty @name=N’MS_Description', @value=N’Идентификатор' , @level0type=N’SCHEMA',@level0name=N’main', @level1type=N’TABLE',@level1name=N’TextTable', @level2type=N’COLUMN',@level2name=N’id'; Также можно создать набор сервисных процедуру обёрток
EXEC Service.CommentOnTable N’SchemaName', N’TableName', N’TableComment'; EXEC Service.CommentOnColumn N’SchemaName', N’TableName', N’ColumnName', N’ColumnComment'; EXEC Service.CommentOnProcedure N’SchemaName', N’TableName', N’ProcedureComment'; Так уже короче, информативней и приятней работать.
Этим набор сервисных процедур, конечно, не ограничивается, можно много чего придумать.
Всего доброго приятной работы!
