Решения разработки баз данных

в 9:29, , рубрики: sql, Программирование

Добрый день! В процессе развития проекта приходится сталкиваться с проблемой производительности баз данных, т.к. объём данных растёт, и волей неволей выплывают проблемные участки.

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

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

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

Источник

Поделиться

* - обязательные к заполнению поля