понедельник, 20 декабря 2010 г. - www.msmirnov.ru

Partitioned views на практике. Горизонтальное секционирование (партицирование) через partitioned views в SQL Server. Опыт собственной разработки.

Введение.

Сам по себе прием partitioned views, о котором пойдет речь в данной статье, известен уже около 10-ти лет, но здесь мне бы хотелось поделиться именно практическим опытом его реализации в современной обстановке.

Кратко напомню суть приема partitioned views.

Partitioned views используются для организации горизонтального секционирования больших таблиц баз данных с целью ускорения доступа и повышения производительности запросов.

Приведу пример.

Создадим три одинаковые по структуре таблицы, которые будут хранить некие отчетные данные:

ReportTable_History будет хранить данные до начала 2011 года,
ReportTable_2011_01 за январь 2011 и
ReportTable_2011_02 за февраль 2011.

Для этого выполним следующий SQL-скрипт:

-- Первая таблица

CREATE TABLE ReportTable_History(ReportDate datetime NOT NULL, SalesCount int NOT NULL, SalesAmount money NOT NULL)
GO

CREATE CLUSTERED INDEX CL_INDEX ON ReportTable_History (ReportDate)


ALTER TABLE ReportTable_History WITH CHECK ADD CONSTRAINT CK_ReportTable_History CHECK ((ReportDate<'2011-01-01'))
GO
 
-- Вторая таблица
CREATE TABLE ReportTable_2011_01(ReportDate datetime NOT NULL, SalesCount int NOT NULL, SalesAmount money NOT NULL)
GO

CREATE CLUSTERED INDEX CL_INDEX ON ReportTable_2011_01 (ReportDate)

ALTER TABLE ReportTable_2011_01 WITH CHECK ADD CONSTRAINT CK_ReportTable_2011_01 CHECK ((ReportDate>='2011-01-01' and ReportDate < '2011-02-01'))
GO


-- Третья таблица
CREATE TABLE ReportTable_2011_02( ReportDate datetime NOT NULL, SalesCount int NOT NULL, SalesAmount money NOT NULL)
GO


CREATE CLUSTERED INDEX CL_INDEX ON ReportTable_2011_02 (ReportDate)

ALTER TABLE ReportTable_2011_02 WITH CHECK ADD CONSTRAINT CK_ReportTable_2011_02 CHECK ((ReportDate>='2011-02-01' and ReportDate < '2011-03-01'))
GO


Как видим, все таблицы имеют одинаковую структуру и имеют также ограничения по полю ReportDate, которые не позволяют нам вставлять в эти таблицы данные за “неправильные” даты.


Создадим теперь представление следующего вида, которое будет объединять в себе все три таблицы:

CREATE VIEW ReportView AS
select ReportDate, SalesCount, SalesAmount
from ReportTable_History
union all
select ReportDate, SalesCount, SalesAmount
from ReportTable_2011_01
union all
select ReportDate, SalesCount, SalesAmount
from ReportTable_2011_02


Теперь попробуем выполнить вставку данных за Январь 2011 в ReportTable_2011_01:

insert into ReportTable_2011_01 (ReportDate, SalesCount, SalesAmount)
values ('2011-01-01', 1, 100)

 
 
Проверим содержимое таблицы за Январь 2011 (ReportTable_2011_01):

image

Выполнить вставку непосредственно в представление ReportView мы не можем, так как на таблицах, входящих в него, нет первичного ключа. Мы, конечно, могли бы создать такой ключ, но в данном случае он совершенно не нужен, так как у нас нет поля, однозначно идентифицирующего запись в таблице.


Теперь самое интересное – попробуем теперь выбрать январские данные из представления ReportView и посмотрим план запроса:

image

Из плана видим, что сервер производит обращение только к таблице ReportTable_2011_01, в которой содержатся данные за Январь 2011. Другие две таблицы при выполнении запроса не задействованы. Это происходит благодаря нашим CHECK CONSTRAINTS, которые помогают оптимизатору правильно определить месторасположение данных.

Для выбирающего скрипта эта работа не видна, т.е. он не знает про внутреннюю структуру view и про распределение данных по таблицам, вместо этого он просто обращается к view, как к обычной таблице.

Таким образом происходит распределение данных между таблицами, что уменьшает каждую из них и ускоряет работу с таблицами.

Данный прием также работает в случае если таблицы доступны через linked servers, только в таком случае происходит распределение данных не только между локальными таблицами, но и между серверами.

В SQL Server, начиная с версии 2005, существует встроенная возможность разбивать большие таблицы на партиции, но данная функция присутствует только в редакциях Developer и Enterprise.

Первый не подходит для больших баз данных, а второй стоит очень дорого.

Надеюсь в будущих версиях Microsoft это исправит, но сейчас мы имеем то, что имеем – для использования партиций надо покупать редакцию Enterprise.

Таким образом, встает вопрос собственной реализации механизма partitioned views.



Описание механизма.

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

На вскидку можно было бы предположить, что мы можем организовать partitioned view, которое будет объединять в себе неограниченное число таблиц, созданное для большого числа месяцев. Например, можно было бы объединить в нем 120 таблиц для отчетных данных за 10 лет.

К сожалению, это не так.

Длина запроса view ограничена 8000 символов, поэтому мы не можем создать partitioned view, достаточно длинное для объединения любого количества таблиц.

Как вариант решения проблемы, можно было бы организовать секционное хранение данных только за последние 12 месяцев, а все более старые данные хранить в одной отдельной общей таблице (как показано во введении).

На первый взгляд это логично, так как данные за последние 12 месяцев являются наиболее часто востребованными, поэтому именно для них является наиболее актуальной задача быстрого доступа. Конечно, 12 месяцев – это частый случай, количество может и больше, лишь бы хватило длины view.

Таким образом, наше partitioned view могло бы содержать одну большую таблицу, которая хранит данные старше 12-ти месяцев и 12 малых таблиц для 12-ти последних месяцев каждая.

На следующем рисунке показано, что partitioned view объединяет 12 таблиц по месяцам и одну большую таблицу.

image

Соответственно, перед началом каждого месяца мы должны были бы переместить 12-й месяц в большую таблицу и добавить в представление новую 1-ю таблицу для нового месяца.

image

Для этого мы должны были бы выполнить следующие действия:
1. Исправить CHECK CONSTRAINTS на большой таблице так, чтобы разрешенная в ней дата увеличилась на 1 месяц (т.е. чтобы таблица опять включала бы в себя все данные старше 12-ти месяцев, с учетом перехода на новый месяц).
2. Перенести данные из таблицы, которая содержала данные 12-ти месячной давности в большую таблицу.
3. Удалить пустую таблицу, в которой содержались данные 12-ти месячной давности.
4. Создать новую таблицу для нового месяца с новым CHECK CONSTRAINTS.
5. Изменить скрипт partitioned view, добавив в него новую таблицу и удалив старую.

Этот механизм будет работать, но он имеет ряд существенных недостатков:
1. Его довольно сложно поддерживать, так как структуры месячных таблиц и большой таблицы разные и мы не можем сформировать универсальный механизм генерации таблиц за любой период.
2. Проблема быстрого доступа к “старым” данным не решена – они все равно располагаются в большой таблице.
3. Необходимо выполнять действия по перемещение данных между таблицами, которые могут быть довольно затратными.

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

В качестве решения всех этих проблем можно использовать двухуровневую организацию таблиц и представлений:
1. Организовать хранение данных только в месячных таблицах: ReportTable_2011_01, ReportTable_2011_02 и т.д.
2. Создать набор представлений по годам, объединяющих по 12 месячных таблиц за каждый год.
3. Создать одно общее представление второго уровня, объединяющее представления по годам.
На схеме это можно показать следующим образом:

image

При это при обращении за получением данных к ReportView оптимизатор по прежнему будет выполнять обращение только к той таблице, которая содержит требуемые данные. Т.е. оптимизатор корректно обрабатывает наличие двух-уровневых partitioned views.
Длины скриптов таких представлений должно хватить на несколько десятков лет, что будет вполне достаточно для большинства прикладных систем.


Реализация механизма.

В процессе работы с таким представлением нам необходимо выполнять следующие операции:
1. Производить работу с данными в таблицах – вставлять, обновлять и удалять записи.
2. Создавать новые таблицы и изменять скрипты представлений, добавляя в них эти новые таблицы.

Сначала рассмотрим работу с данными.

Как я уже отметил выше, непосредственная работа с данными через partitioned view, при отсутствии на таблицах первичного ключа, невозможна.
Поэтому при выполнении запросов нам необходимо явно указывать, с какой таблицей мы ведем работу в данный момент.

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

Этот процесс лучше автоматизировать – т.е. дать нам такой способ выполнения запросов, который позволит нам выполнять запросы не задумываясь о том, в какой конкретно таблице физически расположены наши данные.

Для этого прежде всего изменим структуру таблиц и представлений.

Новые скрипты таблиц будут такими:
-- Таблица на Январь 2011

CREATE TABLE ReportTable_2011_01(ReportDate datetime NOT NULL, SalesCount int NOT NULL, SalesAmount money NOT NULL, [Version] tinyint)
GO

CREATE CLUSTERED INDEX CL_INDEX ON ReportTable_2011_01 (ReportDate)

ALTER TABLE ReportTable_2011_01 WITH CHECK ADD CONSTRAINT CK_ReportTable_2011_01 CHECK ((ReportDate>='2011-01-01' and ReportDate < '2011-02-01'))
GO

-- Таблица на Февраль 2011
CREATE TABLE ReportTable_2011_02( ReportDate datetime NOT NULL, SalesCount int NOT NULL, SalesAmount money NOT NULL, [Version] tinyint) GO

CREATE CLUSTERED INDEX CL_INDEX ON ReportTable_2011_02 (ReportDate) ALTER
TABLE ReportTable_2011_02 WITH CHECK ADD CONSTRAINT CK_ReportTable_2011_02 CHECK ((ReportDate>='2011-02-01' and ReportDate < '2011-03-01'))

GO

Теперь создадим наше представление первого уровня:


CREATE VIEW ReportView_2011
AS
select ReportDate, SalesCount, SalesAmount
from ReportTable_2011_01
union all
select ReportDate, SalesCount, SalesAmount
from ReportTable_2011_02

Создадим теперь преставление второго уровня. В нашем примере оно включает в себя всего лишь один год.

CREATE VIEW ReportView
AS
select ReportDate, SalesCount, SalesAmount
from ReportView_2011


Теперь рассмотрим процесс работы с данными в этих таблицах.
Как я уже упоминал, для комфортной работы нам необходим способ манипуляции данными без указание того, в какой таблице они физически располагаются.


Для этого создадим универсальную процедуру, которая будет принимать на входе SQL-запрос и выполнять его на к таблице или таблицам, в которых физически располагаются необходимые данные.

Рассмотрим простой вариант такой процедуры:
CREATE PROCEDURE ExecPartScript
   
@script nvarchar(4000),
    @TableName nvarchar(200),
   
@MinDate datetime AS
begin 

    declare @sql_script nvarchar(4000
    declare @PartTableName varchar(200
    while (@MinDate < getdate()) 
    begin 
        exec GetTableNameAtDate @CheckDate = @MinDate, @TableName = @TableName, @PartTableName = @PartTableName output 
        set @sql_script = replace(@script, @TableName, @PartTableName
        execute dbo.sp_executesql @sql_script 
        set @MinDate = cast((convert(varchar (08), dateadd(month, 1, @MinDate), 21) + '01') as datetime)     end
 end

Данная процедура принимает на вход запрос, который ей необходимо выполнить на наборе таблиц (@script), общая часть имен таблиц для выполнения запроса (@TableName – в нашем примере это ‘ReportTable’)  и минимальная дата секционирования, с которой необходимо выполнить запрос (@MinDate).

Параметр @MinDate, вообще говоря, можно было бы и не использовать, но его применение позволяет сократить время и нагрузку от выполнения процедуры.

Как видим, процедура выполняет цикл по месяцам, которые являются основной для разбивки таблиц.

Для каждого месяца она запускает процедуру GetTableNameAtDate, которая возвращает имя конкретной таблицы для выполнения запроса (т.е. например ‘ReportTable_2011_02’ вместо ‘ReportTable’). Затем в цикле происходит подмена имени таблицы на новое имя и затем происходит выполнение запроса.

Процедура GetTableNameAtDate довольно проста:

CREATE PROCEDURE GetTableNameAtDate
 @CheckDate datetime,
 @TableName nvarchar(200),
 @PartTableName nvarchar(200) output
AS
BEGIN 

    declare @part_name nvarchar(4
    set @part_name = month(@CheckDate
    if (len(@part_name) = 1) set @part_name = '0' + @part_name 
    set @part_name = '_' + cast (year(@CheckDate) as varchar(4)) + '_'  + @part_name
     set @PartTableName = @TableName + @part_name
    if not exists ( select * from dbo.sysobjects where id = object_id(@PartTableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)

       exec CreateTable @TableName = @TableName, @part_name = @part_name, @CheckDate = @CheckDate
END

Данная процедура подставляет дату к имени таблицы и возвращает новое имя.

Попутно она вызывает процедуру CreateTable, которая создает новую таблицу в базе, если ее еще нет.

CREATE PROCEDURE [dbo].[CreateTable]
 @TableName nvarchar(200),
 @part_name nvarchar(4),
 @CheckDate datetime AS
BEGIN 

    declare @script nvarchar(4000
    exec ScriptTable @TableName, @script output 
    set @script = replace(@script, @TableName, @TableName + @part_name
    declare @pos int 
    set @pos =
    set @pos = charindex('constraint', @script
    while (@pos > 0
        begin 
            set @pos = charindex(']', @script, @pos
            if (@pos = 0
                break
            set @script = stuff(@script, @pos, 0, @part_name
            set @pos = charindex('constraint', @script, @pos + 2
        end 
    declare @date_column varchar (100
    set @date_column = 'ReportDate' 
    declare @startdate varchar (15), @enddate varchar(15
    set @startdate = convert(varchar (05), @CheckDate, 21) + '01-01' 
    set @enddate = convert(varchar (05), dateadd(year, 1, @CheckDate), 21) + '01-01' 
    set @script = @script + 'ALTER TABLE [dbo].[' + @TableName + @part_name + '] WITH CHECK ADD CONSTRAINT [CK_ReportTable' + @TableName + @part_name + '] CHECK (([' + @date_column + ']>=''' + @startdate + ''' and [' + @date_column + ']<''' + @enddate + ''')) ' 
    execute dbo.sp_executesql @script
 END
Данная процедура вызывает процедуру скриптования таблицы ScriptTable, переименовывает в ней ограничения, добавляет ограничение по дате и запускает скрипт генерации таблицы.

Процедура ScriptTable возвращает скрипт таблицы и чтобы не перегружать данную статью информацией, я рассмотрю ее в отдельном посте.
Итак, на выходе мы получаем процедуру ExecPartScript, которую мы можем использовать для выполнения запросов к секционированным таблицам, которая автоматически перенаправляет запросы на различные физические таблицы, а также организует создания новые секционированных таблиц по мере необходимости.
Мой сайт - www.msmirnov.ru