- PVSM.RU - https://www.pvsm.ru -
Добрый день! В процессе развития проекта приходится сталкиваться с проблемой производительности баз данных, т.к. объём данных растёт, и волей неволей выплывают проблемные участки.
В данном посте описаны не очень удачные решения, которые незаметны на ранних стадиях проекта. И решения, которые могут в дальнейшем в разы повысить производительность.
Пост больше рассчитан на более опытных разработчиков, которые уже возможно ищут решения, поэтому буду краток.
Многие разработчики при написании запросов применяют следующую конструкцию
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.
Возникаю ситуации, когда нужно собрать данные из нескольких однотипных по смыслу таблиц и за частую прибегают к следующему решению
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 нужно добавить соответствующий блок только в одном месть.
Есть ситуации, когда в условии объединения необходимо использовать дополнительные операции над полем, в таком случае индекс не используется
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
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
Источник [1]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/programmirovanie/68436
Ссылки в тексте:
[1] Источник: http://habrahabr.ru/post/234721/
Нажмите здесь для печати.