Добрый день! В процессе развития проекта приходится сталкиваться с проблемой производительности баз данных, т.к. объём данных растёт, и волей неволей выплывают проблемные участки.
В данном посте описаны не очень удачные решения, которые незаметны на ранних стадиях проекта. И решения, которые могут в дальнейшем в разы повысить производительность.
Пост больше рассчитан на более опытных разработчиков, которые уже возможно ищут решения, поэтому буду краток.
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';
Так уже короче, информативней и приятней работать.
Этим набор сервисных процедур, конечно, не ограничивается, можно много чего придумать.
Всего доброго приятной работы!
Автор: xzilgiz
