среда, 27 августа 2014 г.

[Из песочницы] Решения разработки баз данных

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

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


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



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';


Так уже короче, информативней и приятней работать.


Этим набор сервисных процедур, конечно, не ограничивается, можно много чего придумать.


Всего доброго приятной работы!


This entry passed through the Full-Text RSS service — if this is your content and you're reading it on someone else's site, please read the FAQ at http://ift.tt/jcXqJW.


на

Комментариев нет:

Отправить комментарий