Добрый вечер/день/утро уважаемые хабралюди! Продолжаем развивать и дополнять блог о моей любимой open source rdbms Postgresql. Чудесным образом так получилось, что тема сегодняшнего топика еще ни разу здесь не подымалась. Надо сказать, что секционирование в postgresql очень хорошо описано в документации , но разве ж это меня остановит?).
Вступление
Вообще под секционированием в общем случае понимают не какую-то технологию, а скорее подход к проектированию БД, появившийся задолго до того, как СУБД начали поддерживать т.н. секционированные таблицы. Мысль очень простая - разделить таблицу на несколько частей меньшего размера. Различают два подвида - горизонтальное и вертикальное секционирование.Горизонтальное секционирование
Части таблицы содержат разные ее строки. Положим у нас есть таблица логов некоего абстрактного приложения - LOGS. Мы можем разбить ее на части - одна для логов за январь 2009, другая - за февраль 2009, и т.д.Вертикальное секционирование
Части таблицы содержат разные ее столбцы. Найти применение для вертикального секционирования (когда оно действительно оправдано) несколько сложнее, чем для горизонтального. В качестве сферического коня предлагаю рассмотреть такой вариант: таблица NEWS имеет столбцы ID, SHORTTEXT, LONGTEXT, и пусть поле LONGTEXT используется намного реже первых двух. В таком случае имеет смысл разбить таблицу NEWS по столбцам (создать две таблицы для SHORTTEXT и LONGTEXT соответственно, связанных первичными ключами + создать view NEWS, содержащую оба столбца). Таким образом, когда нам нужно только описание новости, СУБД не придется читать с диска еще и весь текст новости.Поддержка секционирования в современных СУБД
Большинство современных СУБД поддерживают секционирование таблиц в том или ином виде.- Oracle - поддерживает секционирование начиная с 8й версии. Работа с секциями с одной стороны очень простая (вообще можно о них не думать, работаешь как с обычной таблицей*), а с другой - все очень гибко. Секции можно разбивать на «subpartitions», удалять, делить, переносить. Поддерживаются разные варианты индексирования секционированной таблицы (глобальный индекс, секционированный индекс). Ссылочка на объемное описание.
- Microsoft SQL Server - поддержка секционирования появилась недавно (в 2005). Первое впечатление от использования - «Ну наконец-то!!:)», второе - «Работает, вроде все ок». Документация на msdn
- MySQL - поддерживает начиная с версии 5.1. Очень хорошее описание на хабре
- И так далее…
Секционирование в Postgresql
Секционирование таблиц в postgresql несколько отличается в реализации от остальных БД. Основой для секционирования служит наследование таблиц (вещь присущая исключительно postgresql). То есть, у нас должна быть основная таблица (master table), а ее секциями будут таблицы-наследники. Будем рассматривать секционирование на примере задачи, приближенной к реальности.Постановка задачи
База данных используется для сбора и анализа данных о посетителях сайта/сайтов. Объемы данных достаточно велики для того, чтобы задуматься о секционировании. При анализе в большинстве случаев используются данные за последний день.1. Создаем основную таблицу:
CREATE TABLE analytics.events
user_id UUID NOT NULL ,
event_type_id SMALLINT NOT NULL ,
event_time TIMESTAMP DEFAULT now() NOT NULL ,
url VARCHAR (1024) NOT NULL ,
referrer VARCHAR (1024),
ip INET NOT NULL
);
2. Секционировать будем по дням по полю event_time. На каждый день будем создавать новую секцию. Именовать секции будем по правилу: analytics.events_DDMMYYYY. Вот например секция для 1го января 2010 года.
CREATE TABLE analytics.events_01012010
event_id BIGINT DEFAULT nextval("analytics.seq_events" ) PRIMARY KEY ,
CHECK (event_time >= TIMESTAMP "2010-01-01 00:00:00" AND event_time < TIMESTAMP "2010-01-02 00:00:00" )
) INHERITS (analytics.events);* This source code was highlighted with Source Code Highlighter .
При создании секции явно задаем поле event_id (PRIMARY KEY не наследуется) и создаем CHECK CONSTRAINT на поле event_time, дабы не вставить лишнего.
3. Создаем индекс на поле event_time. При разбиении таблицы на секции, мы подразумеваем, что большинство запросов к таблице events будут использовать условие на поле event_time, так что индекс на этом поле нам очень поможет.
CREATE INDEX events_01012010_event_time_idx ON analytics.events_01012010 USING btree(event_time);* This source code was highlighted with Source Code Highlighter .
4. Мы хотим добиться того, чтобы при вставке в основную таблицу, данные оказывались в предназначенной им секции. Для этого делаем следующий финт - создаем триггер, который будет управлять потоками данных.
CREATE OR REPLACE FUNCTION analytics.events_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW .event_time >= TIMESTAMP "2010-01-01 00:00:00" AND
NEW .event_time < TIMESTAMP "2010-01-02 00:00:00" ) THEN
INSERT INTO analytics.events_01012010 VALUES (NEW .*);
ELSE
RAISE EXCEPTION "Date % is out of range. Fix analytics.events_insert_trigger" , NEW .event_time;
END IF ;
RETURN NULL ;
END ;
$$
LANGUAGE plpgsql;* This source code was highlighted with Source Code Highlighter .
CREATE TRIGGER events_before_insert
BEFORE INSERT ON analytics.events
FOR EACH ROW EXECUTE PROCEDURE analytics.events_insert_trigger();* This source code was highlighted with Source Code Highlighter .
5. Все готово, у нас теперь есть секционированная таблица analytics.events. Можем начинать яростно анализировать ее данные. Кстати, CHECK constraints мы создавали не только для того, чтобы защитить секции от некорректных данных. Postgresql может их использовать при составлении плана запроса (правда при живом индексе на event_time выигрыш это даст минимальный), достаточно воспользоваться директивой constraint_exclusion:
SET constraint_exclusion = on ;
SELECT * FROM analytics.events WHERE event_time > CURRENT_DATE ;* This source code was highlighted with Source Code Highlighter .
Конец первой части
Итак, что мы имеем? Давайте по пунктам:1. Таблицу events, разбитую на секции, анализ имеющихся данных за последние сутки становится проще и быстрее.
2. Ужас от осознания того, что все это нужно как-то поддерживать, создавать вовремя секции, не забывая менять триггер соответствующим образом.
О том как просто и беззаботно работать с секционированными таблицами расскажу во второй части.
UPD1: Заменил партиционирование на секционирование
UPD2:
По мотивам замечания одного из читателей, не имеющего, к сожалению, аккаунта на хабре:
С наследованием связано несколько моментов, которые стоит учитывать при проектировании. Секции не наследуют первичный ключ и внешние ключи на их столбцы. То есть, при создании секции, нужно явно создавать PRIMARY KEY и FOREIGN KEYs на столбцы секции. От себя замечу, что создавать FOREIGN KEY на столбцы секционированной таблицы не лучший путь. В большинстве случаев секционированная таблица является «таблицей фактов» и сама ссылается на «dimension» таблицы.
Добрый вечер/день/утро уважаемые хабралюди! Продолжаем развивать и дополнять блог о моей любимой open source rdbms Postgresql. Чудесным образом так получилось, что тема сегодняшнего топика еще ни разу здесь не подымалась. Надо сказать, что секционирование в postgresql очень хорошо описано в документации , но разве ж это меня остановит?).
Вступление
Вообще под секционированием в общем случае понимают не какую-то технологию, а скорее подход к проектированию БД, появившийся задолго до того, как СУБД начали поддерживать т.н. секционированные таблицы. Мысль очень простая - разделить таблицу на несколько частей меньшего размера. Различают два подвида - горизонтальное и вертикальное секционирование.Горизонтальное секционирование
Части таблицы содержат разные ее строки. Положим у нас есть таблица логов некоего абстрактного приложения - LOGS. Мы можем разбить ее на части - одна для логов за январь 2009, другая - за февраль 2009, и т.д.Вертикальное секционирование
Части таблицы содержат разные ее столбцы. Найти применение для вертикального секционирования (когда оно действительно оправдано) несколько сложнее, чем для горизонтального. В качестве сферического коня предлагаю рассмотреть такой вариант: таблица NEWS имеет столбцы ID, SHORTTEXT, LONGTEXT, и пусть поле LONGTEXT используется намного реже первых двух. В таком случае имеет смысл разбить таблицу NEWS по столбцам (создать две таблицы для SHORTTEXT и LONGTEXT соответственно, связанных первичными ключами + создать view NEWS, содержащую оба столбца). Таким образом, когда нам нужно только описание новости, СУБД не придется читать с диска еще и весь текст новости.Поддержка секционирования в современных СУБД
Большинство современных СУБД поддерживают секционирование таблиц в том или ином виде.- Oracle - поддерживает секционирование начиная с 8й версии. Работа с секциями с одной стороны очень простая (вообще можно о них не думать, работаешь как с обычной таблицей*), а с другой - все очень гибко. Секции можно разбивать на «subpartitions», удалять, делить, переносить. Поддерживаются разные варианты индексирования секционированной таблицы (глобальный индекс, секционированный индекс). Ссылочка на объемное описание.
- Microsoft SQL Server - поддержка секционирования появилась недавно (в 2005). Первое впечатление от использования - «Ну наконец-то!!:)», второе - «Работает, вроде все ок». Документация на msdn
- MySQL - поддерживает начиная с версии 5.1.
- И так далее…
Секционирование в Postgresql
Секционирование таблиц в postgresql несколько отличается в реализации от остальных БД. Основой для секционирования служит наследование таблиц (вещь присущая исключительно postgresql). То есть, у нас должна быть основная таблица (master table), а ее секциями будут таблицы-наследники. Будем рассматривать секционирование на примере задачи, приближенной к реальности.Постановка задачи
База данных используется для сбора и анализа данных о посетителях сайта/сайтов. Объемы данных достаточно велики для того, чтобы задуматься о секционировании. При анализе в большинстве случаев используются данные за последний день.1. Создаем основную таблицу:
CREATE TABLE analytics.events
user_id UUID NOT NULL ,
event_type_id SMALLINT NOT NULL ,
event_time TIMESTAMP DEFAULT now() NOT NULL ,
url VARCHAR (1024) NOT NULL ,
referrer VARCHAR (1024),
ip INET NOT NULL
);
2. Секционировать будем по дням по полю event_time. На каждый день будем создавать новую секцию. Именовать секции будем по правилу: analytics.events_DDMMYYYY. Вот например секция для 1го января 2010 года.
CREATE TABLE analytics.events_01012010
event_id BIGINT DEFAULT nextval("analytics.seq_events" ) PRIMARY KEY ,
CHECK (event_time >= TIMESTAMP "2010-01-01 00:00:00" AND event_time < TIMESTAMP "2010-01-02 00:00:00" )
) INHERITS (analytics.events);* This source code was highlighted with Source Code Highlighter .
При создании секции явно задаем поле event_id (PRIMARY KEY не наследуется) и создаем CHECK CONSTRAINT на поле event_time, дабы не вставить лишнего.
3. Создаем индекс на поле event_time. При разбиении таблицы на секции, мы подразумеваем, что большинство запросов к таблице events будут использовать условие на поле event_time, так что индекс на этом поле нам очень поможет.
CREATE INDEX events_01012010_event_time_idx ON analytics.events_01012010 USING btree(event_time);* This source code was highlighted with Source Code Highlighter .
4. Мы хотим добиться того, чтобы при вставке в основную таблицу, данные оказывались в предназначенной им секции. Для этого делаем следующий финт - создаем триггер, который будет управлять потоками данных.
CREATE OR REPLACE FUNCTION analytics.events_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW .event_time >= TIMESTAMP "2010-01-01 00:00:00" AND
NEW .event_time < TIMESTAMP "2010-01-02 00:00:00" ) THEN
INSERT INTO analytics.events_01012010 VALUES (NEW .*);
ELSE
RAISE EXCEPTION "Date % is out of range. Fix analytics.events_insert_trigger" , NEW .event_time;
END IF ;
RETURN NULL ;
END ;
$$
LANGUAGE plpgsql;* This source code was highlighted with Source Code Highlighter .
CREATE TRIGGER events_before_insert
BEFORE INSERT ON analytics.events
FOR EACH ROW EXECUTE PROCEDURE analytics.events_insert_trigger();* This source code was highlighted with Source Code Highlighter .
5. Все готово, у нас теперь есть секционированная таблица analytics.events. Можем начинать яростно анализировать ее данные. Кстати, CHECK constraints мы создавали не только для того, чтобы защитить секции от некорректных данных. Postgresql может их использовать при составлении плана запроса (правда при живом индексе на event_time выигрыш это даст минимальный), достаточно воспользоваться директивой constraint_exclusion:
SET constraint_exclusion = on ;
SELECT * FROM analytics.events WHERE event_time > CURRENT_DATE ;* This source code was highlighted with Source Code Highlighter .
Конец первой части
Итак, что мы имеем? Давайте по пунктам:1. Таблицу events, разбитую на секции, анализ имеющихся данных за последние сутки становится проще и быстрее.
2. Ужас от осознания того, что все это нужно как-то поддерживать, создавать вовремя секции, не забывая менять триггер соответствующим образом.
О том как просто и беззаботно работать с секционированными таблицами расскажу во второй части.
UPD1: Заменил партиционирование на секционирование
UPD2:
По мотивам замечания одного из читателей, не имеющего, к сожалению, аккаунта на хабре:
С наследованием связано несколько моментов, которые стоит учитывать при проектировании. Секции не наследуют первичный ключ и внешние ключи на их столбцы. То есть, при создании секции, нужно явно создавать PRIMARY KEY и FOREIGN KEYs на столбцы секции. От себя замечу, что создавать FOREIGN KEY на столбцы секционированной таблицы не лучший путь. В большинстве случаев секционированная таблица является «таблицей фактов» и сама ссылается на «dimension» таблицы.
В этой статье я собираюсь продемонстрировать особенности планов исполнения запросов при обращении к секционированным таблицам. Обратите внимание, что существует большая разница между секционированными таблицами (которые стали доступны только с SQL Server 2005) и секционированными представлениями (которые были доступных ещё в SQL Server 2000, и по-прежнему доступны в SQL Server 2005 и последующих версиях). Особенности планов запросов к секционированным представлениям я продемонстрирую в другой статье.
Просмотр таблицы
Давайте создадим простую секционированную таблицу:
create partition function pf(int) as range for values (0, 10, 100)
create partition scheme ps as partition pf all to ()
create table t (a int, b int) on ps(a)
Этот сценарий создает таблицу с четырьмя секциями. SQL Server присвоил значения идентификаторам каждой из четырех секций так, как это показано в таблице:
| PtnId | Values |
| 1 | t.a <= 0 |
| 2 | 0 < t.a <= 10 |
| 3 | 10 < t.a <= 100 |
| 4 | 100 < t.a |
Теперь давайте рассмотрим план такого запроса, который бы вынудил оптимизатор использовать просмотр всей таблицы (Table Scan):
……|–Constant Scan(VALUES:(((1)),((2)),((3)),((4))))
…….|–Table Scan(OBJECT:([t]))
В представленном выше плане, SQL Server явно указывает все идентификаторы секции в операторе «Constant Scan», который реализует просмотр таблицы и поставляет данные оператору соединения вложенных циклов. Тут следует напомнить, что оператор соединения вложенных циклов выполняет проход по внутренней таблице (в данном случае это полный просмотр таблицы) один раз для каждого значения из внешней таблицы (в нашем случае это «Constant Scan»). Таким образом, мы выполняем просмотр таблицы четыре раза; один раз для каждого идентификатора секции.
Следует также отметить, что соединение вложенных циклов показывает явно что внешняя таблица является значениями столбца , где хранятся ID секций. Хотя это не сразу видно в текстовом представлении плана исполнения (к сожалению, мы иногда не замечаем эту информацию), просмотр таблицы использует столбец с идентификаторами секций, которые выбираются для выполнения просмотра и определяют какую секцию сканировать. Эта информация всегда доступна в графическом плане исполнения (нужно заглянуть в свойства оператора просмотра таблицы), а также в XML представление плана исполнения запроса:
Статическая фильтрация секций
Рассмотрим следующий запрос:
select * from t where a < 100
|–Nested Loops(Inner Join, OUTER REFERENCES:() PARTITION ID:())
…….|–Constant Scan(VALUES:(((1)),((2)),((3))))
<(100)) PARTITION ID:())
Предикат «а <100» явно исключает все строки для секции со значением идентификатора равным 4. В данном случае, нет смысла в просмотре соответствующей секции, поскольку ни одна из строк этой секции не удовлетворяет условию предиката. Оптимизатор учитывает этот факт и исключает эту секцию из плана исполнения запроса. В операторе «Constant Scan» указаны только три секции. У нас принято называть это статической фильтрацией секций (static partition elimination), поскольку мы знаем, что во время компиляции список просматриваемых секций остаётся статичным.
Если в результате статичной фильтрации будут исключены все разделы, кроме одного, нам вообще не понадобятся операторы «Constant Scan» и «Nested Loops Join»:
select * from t where a < 0
|–Table Scan(OBJECT:([t]), WHERE:([t].[a]<(0)) PARTITION ID:((1)))
Обратите внимание, что указание «PARTITION ID:((1))», которое задаёт идентификатор подлежащей просмотру секции, теперь является частью оператора просмотра таблицы (Table Scan).
Динамическая фильтрация секций
В некоторых случаях SQL Server не может определить, что на момент компиляции состав просматриваемых секций не изменится, но ему видно, что некоторые секции можно исключить.
select * from t where a < @i
|–Nested Loops(Inner Join, OUTER REFERENCES:() PARTITION ID:())
…….|–Filter(WHERE:(<=RangePartitionNew([@i],(0),(0),(10),(100))))
…….| |–Constant Scan(VALUES:(((1)),((2)),((3)),((4))))
…….|–Table Scan(OBJECT:([t]), WHERE:([t].[a]<[@i]) PARTITION ID:())
Это параметризованный запрос. Так как до исполнения значение параметра мы не знаем (то, что я использую константу в качестве параметра в том же батче, не меняет положение вещей), то на этапе компиляции невозможно определить значение идентификатора секции для оператора «Constant Scan». Возможно придётся просматривать только секцию 1, или это будут секции 1 и 2, и так далее. Поэтому, в этом операторе указаны все четыре идентификатора секций, и мы используем фильтрацию идентификаторов секций на этапе исполнения. Мы называем это «Динамическая фильтрация секций» (Dynamic Partition Elimination).
Фильтр сравнивает каждый идентификатор секции c результатом работы специальной функции «RangePartitionNew». Эта функция вычисляет результаты применения функции секционирования к значению параметра. Аргументами этой функции (слева направо) являются:
- значение (в данном случае параметр @i), который мы хотим отобразить на ID секции;
- булевой флаг, указывающий, отображает ли функция секционирования граничные значения слева (0) или справа (1);
- граничные значения секций (в данном случае это 0, 10, и 100).
В этом примере, поскольку @i имеет значение 0, результатом «RangePartitionNew» является 1. Таким образом, мы просматриваем только секцию с идентификатором 1. Заметим, что в отличие от примера со статической фильтрацией секций, хотя мы сканируем только один раздел, мы по-прежнему имеем «Constant Scan» и «Nested Loops Join». Нам потому нужны эти операторы, что до этапа исполнения мы не знаем секции, которые будут просмотрены.
В некоторых случаях оптимизатор уже на этапе компиляции может определить, что мы будем сканировать только одну секцию, даже если он не может определить, какую именно. Например, если в запросе используется предикат эквивалентности по ключу секционирования, тогда мы знаем, что только одна секция может удовлетворять такому условию. Поэтому, несмотря на то, что у нас должна была быть динамическая фильтрация секций, у нас отпадает необходимость в операторах «Constant Scan» и «Nested Loops Join». Пример:
select * from t where a = @i
|–Table Scan(OBJECT:([t]), WHERE:([t].[a]=[@i]) PARTITION ID:(RangePartitionNew([@i],(0),(0),(10),(100))))
Сочетание статической и динамической фильтрации секций
SQL Server может совмещать статическую и динамическую фильтрацию секций в одном плане запроса:
select * from t where a > 0 and a < @i
|–Nested Loops(Inner Join, OUTER REFERENCES:() PARTITION ID:())
……|–Filter(WHERE:(<=RangePartitionNew([@i],(0),(0),(10),(100))))
……| |–Constant Scan(VALUES:(((2)),((3)),((4))))
……|–Table Scan(OBJECT:([t]), WHERE:([t].[a]<[@i] AND [t].[a]>(0)) PARTITION ID:())
Обратите внимание, что в последнем плане присутствует статическая фильтрация секции ID = 1 с использованием «Constant Scan», и также присутствует динамическая фильтрация для других секций, определяемых предикатами.
$partition
Можно явно вызвать функцию RangePartitionNew, используя $partition:
select *, $partition.pf(a) from t
|–Compute Scalar(DEFINE:(=RangePartitionNew([t].[a],(0),(0),(10),(100))))
……|–Nested Loops(Inner Join, OUTER REFERENCES:() PARTITION ID:())
………..|–Constant Scan(VALUES:(((1)),((2)),((3)),((4))))
………..|–Table Scan(OBJECT:([t]))
Отличительной особенностью такого плана исполнения запроса является появление оператора Compute Scalar.
Дополнительная информация
Страница 23 из 33
Секционирование диапазона - сведения о продажах
Характер использования сведений о продажах зачастую изменчив. Как правило, данные текущего месяца - это оперативные данные; данные предшествующих месяцев - это в большой степени данные, предназначенные для анализа. Чаще всего анализ производится ежемесячно, ежеквартально, либо ежегодно. Поскольку разным аналитикам могут потребоваться значительные объемы различных аналитических данных одновременно, то секционирование лучше всего позволит изолировать их деятельность. В рассматриваемом далее сценарии данные стекаются из 283 узлов и поставляются в виде двух файлов стандартного формата ASCII. Все файлы отправляются на центральный файл-сервер не позднее 3.00 am первого дня каждого месяца. Размеры файлов колеблются, но в среднем составляют примерно 86.000 заказов в месяц. Каждый заказ в среднем составляет 2.63 позиции, поэтому файлы OrderDetails составляют в среднем по 226180 строк. Каждый месяц добавляется примерно 25 миллионов новых заказов и 64 миллиона строк номенклатуры заказов. Сервер анализа истории поддерживает данные за 2 последних года. Данные за два года - это чуть меньше 600 миллионов заказов и более 1.5 миллиардов строк в таблице OrderDetails. Поскольку данные часто анализируются путем сравнения показателей месяцев одного и того же квартала, либо одних и тех же месяцев за предыдущие годы, то выбрано диапазонное секционирование. В качестве размера диапазона выбран месяц.
На основе схемы 11 ("Шаги по созданию секционированной таблицы") мы решили секционировать таблицу, используя диапазонное секционирование по столбцу OrderDate. Наши аналитики в основном объединяют и анализируют данные последних 6 месяцев, либо последних 3 месяцев текущего и прошлого годов (например, январь-март 2003 плюс январь-март 2004). Чтобы максимально усилить расслоение дисков, а заодно изолировать большинство группировок данных, на одном физическом диске будет располагаться по несколько файловых групп, но они будут смещены на шесть месяцев за тем, чтобы уменьшить количество конфликтов при разделении ресурсов. Текущий месяц - октябрь 2004, и все 283 обособленных офисов управляют своими текущими продажами локально. Сервер хранит данные с октября 2002 по сентябрь 2004 включительно. Для того чтобы воспользоваться преимуществом новой 16-процессорной системы и SAN (Storage Area Network - высокоскоростная сеть, связывающая хранилища данных), данные каждого месяца будут находиться в своем собственном файле файловой группы, и располагаться на наборе чередующихся зеркал (RAID 1+0). Рисунок 12 иллюстрирует размещение данных на логических дисках.
Рисунок 12: Секционированная таблица Orders
Каждый из 12 логических дисков использует конфигурацию RAID 1+0, поэтому общее количество дисков, необходимое для таблиц Orders и OrderDetails, равно 48. Не смотря на это, SAN поддерживает до 78 дисков, так что остальные 30 дисков используются для transaction log, TempDB, системных баз данных и прочих небольших таблиц, таких как Customers (9 миллионов записей) и Products (386 750 записей), и т.д. Таблицы Orders и OrderDetails будут использовать одни и те же граничные условия и одно и то же размещение на диске; фактически, они будут использовать одну и ту же схему секционирования. В результате (взгляните на два логических диска E:\ и F:\ на Рисунке 13) данные таблиц Orders и OrderDetails за одни и те же месяцы будут располагаться на одних и тех же дисках:
Рисунок 13: Размещение экстентов диапазонных секций на дисковых массивах
Хотя это и выглядит запутанным, все это весьма просто реализовать. Самое сложное в создании нашей секционированной таблицы - это доставка данных из большого количества источников - 283 хранилища должны иметь стандартный механизм доставки. Тем не менее, на центральном сервере есть только одна таблица Orders и одна таблица OrderDetails. Чтобы превратить обе таблицы в секционированные, мы должны сначала создать функцию и схему секционирования. Схема секционирования определяет физическое расположение секций на дисках, таким образом, файловые группы также должны существовать. Поскольку для наших таблиц необходимы файловые группы, то следующим шагом является их создание. Синтаксис операторов создания каждой файловой группы идентичен приведенному ниже, тем не менее, данным образом должны быть созданы все двадцать четыре файловые группы.Вы можете поменять названия/расположения дисков на один-единственный диск, для того чтобы протестировать и изучить синтаксис. Убедитесь, что Вы исправили размеры файла на MB вместо GB, и выбрали меньший начальный размер файлов, исходя из доступного вам дискового пространства. Двадцать четыре файла и файловые группы будут созданы в базе данных SalesDB. Все будут иметь схожий синтаксис, за исключением местоположения, имени файла и имени файловой группы:
| ALTER
DATABASE
SalesDB |
Как только все двадцать четыре файла и файловые группы будут созданы, Вы сможете определить функцию и схему секционирования. Убедиться в том, что ваши файлы и файловые группы созданы, вы можете при помощи системных хранимых процедур sp_helpfile и sp_helpfilegroup.
Функция секции будет определена по столбцу OrderDate с типом данных datetime. Для того чтобы обе таблицы можно было секционировать по столбцу OrderDate, этот столбец должен присутствовать в обеих таблицах. В действительности, значения ключей секционирования обоих таблиц (если обе таблицы будут секционированы по одному и тому же ключу) будут дублировать друг друга; однако это необходимо для получения преимуществ выравнивания, к тому же в большинстве случаев размер ключевых столбцов будет относительно небольшим (размер поля datetime всего 8 байт). Как уже описывалось в Главе "CREATE PARTITION FUNCTION для диапазонных секций", наша функция будет диапазонной функцией секционирования, у которой первое граничное условие будет в первой (LEFT) секции.
| CREATE
PARTITION FUNCTION
TwoYearDateRangePFN(datetime
) |
Поскольку и крайне левый, и крайне правый граничные случаи охвачены, эта функция секционирования фактически создает 25 секции. Таблица будет поддерживать 25-ую секцию, которая останется пустой. Для этой пустой секции не требуется никакой специальной файловой группы, поскольку никакие данные не должны когда-либо в нее попасть. Для того чтобы гарантировать, что никакие данные в нее не попадут, constraint ограничит диапазон данных этой таблицы. Для того чтобы направить данные на соответствующие диски используется схема секционирования, отображающая секции на файловые группы. Схема секционирования будет использовать явное определение файловых групп для каждой из 24 файловых групп, содержащих данные, и PRIMARY - для 25-ой пустой секции.
| CREATE
PARTITION SCHEME |
Таблица может быть создана с тем же синтаксисом, который поддерживали предыдущие релизы SQL Server - используя предложенную по умолчанию, либо определенную пользователем файловую группу (для создания НЕ секционированной таблицы) - либо используя схему (для создания секционированной таблицы). Что касается того, какой из вариантов предпочтительнее (даже если эта таблица в будущем станет секционированной), то все зависит от того, как таблица будет заполняться и сколькими секциями вы собираетесь манипулировать. Наполнение кучи (heap) и последующее создание в ней кластерного индекса, вероятно, обеспечит лучшую производительность, чем загрузка в таблицу, содержащую кластерный индекс. Кроме того, в мультипроцессорных системах вы можете загружать данные в таблицу параллельно, и затем тоже параллельно строить индексы. В качестве примера создадим таблицу Orders и загрузим в нее данные, используя операторы INSERT … SELECT. Чтобы создать таблицу Orders в качестве секционированной, определите схему секционирования в выражении ON оператора CREATE TABLE.
| CREATE
TABLE
SalesDB.. |
Поскольку таблица OrderDetails собирается использовать ту же схему, она должна включать в себя столбец OrderDate.
| CREATE
TABLE
.( |
На следующем шаге в таблицы загружаются данные из новой учебной базы данных AdventureWorks. Убедитесь, что вы установили базу данных AdventureWorks.
| INSERT
dbo. INSERT
dbo. |
Теперь, когда вы загрузили данные в секционированную таблицу, Вы можете воспользоваться новой встроенной системной функцией для того чтобы определить секцию, на которой будут располагаться данные. Следующий запрос для каждой из содержащих данные секций возвращает информацию о том, сколько строк содержится в каждой из секций, а также минимальное и максимальное значения поля OrderDate. Секция, которая не содержит строк, не попадет в итоговый результат.
| SELECT
$partition.TwoYearDateRangePFN(o.OrderDate) SELECT
$partition.TwoYearDateRangePFN(od.OrderDate) |
И наконец теперь, после того как вы загрузили данные, Вы можете создать кластерный индекс и внешний ключ (Foreign key) между таблицами OrderDetails и Orders. В данном случае кластерный индекс будет построен на первичном ключе (Primary Key) точно так же, как вы идентифицируете обе эти таблицы по их ключу секционирования (для OrderDetails к индексу Вы добавите столбец LineNumber для уникальности). По умолчанию при построении индексов на секционированной таблице происходит их выравнивание по отношению к секционированной таблице согласно той же самой схеме секционирования; явно задавать схему не обязательно.
| ALTER
TABLE
Orders
|
Полный синтаксис, определяющий схему секционирования, выглядел бы так:
| ALTER
TABLE
Orders ALTER
TABLE
dbo.OrderDetails |
В СУБД Oracle есть возможность секционировать представления . Основная идея проста. Пусть физическая таблица разбита на несколько таблиц (необязательно с помощью методов секционирования таблиц) в соответствии с критерием разбиения, который делает обработку запроса более производительной. Критерий разбиения будем называть предикатом секционирования . Тогда можно создать и настроить представления таким образом, чтобы с их помощью обращение к данным этих таблиц было проще для пользователя. Секция представления определяется в соответствии с диапазоном значений ключа секционирования . Запросы, которые используют диапазон значений для выборки данных из секций представления, будут получать доступ только к тем секциям, которые соответствуют диапазонам значений ключа секционирования .
Секции представления могут быть определены предикатами секционирования , заданными либо при помощи ограничения CHECK , либо с использованием предложения WHERE . Покажем, как могут быть применены оба приема, на примере несколько модифицированной таблицы "Продажи" (Sales), которую мы рассматривали в предыдущем разделе. Допустим, что данные о продажах для календарного года размещаются в четырех отдельных таблицах, каждая из которых соответствует кварталу года - Q1_Sales, Q2_Sales, Q3_Sales и Q4_Sales.
Пример 20.14 .
С помощью ограничения CHECK . С помощью команды ALTER TABLE можно добавить ограничения на колонку "Дата продажи" (s_date) каждой таблицы, чтобы ее строки соответствовали одному из кварталов года. Созданное затем представление sales дает возможность обращаться к этим таблицам, как к одной, так и ко всем вместе.
ALTER TABLE Q1_Sales ADD CONSTRAINT C0 CHECK (s_date BETWEEN "jan-1-2002" AND "mar-31-2002"); ALTER TABLE Q2_Sales ADD CONSTRAINT C1 CHECK (s_date BETWEEN "apr 1-2002" AND "jun-30-2002"); ALTER TABLE Q3_Sales ADD CONSTRAINT C2 check (s_date BETWEEN "jul-1-2002" AND "sep-30-2002"); ALTER TABLE Q4_Sales ADD CONSTRAINT C3 check (s_date BETWEEN "oct-1-2002" AND "dec-31-2002"); CREATE VIEW sales_v AS SELECT * FROM Q1_Sales UNION ALL SELECT * FROM Q2_Sales UNION ALL SELECT * FROM Q3_Sales UNION ALL SELECT * FROM Q4_Sales;
Преимуществом такого секционирования представлений является то, что предикат ограничения CHECK не оценивается для каждой строки запроса. Такие предикаты исключают вставку в таблицы строк, не соответствующих критерию предиката. Строки, соответствующие предикату секционирования , извлекаются из базы данных быстрее.
Пример 20.15 .
Секционирование представлений с помощью предложения WHERE . Создадим представление для тех же таблиц, что и в примере выше.
CREATE VIEW sales_v AS SELECT * FROM Q1_Sales WHERE s_date BETWEEN "jan-1-2002" AND "mar-31-2002" UNION ALL SELECT * FROM Q2_Sales WHERE s_date BETWEEN "apr-1-2002" AND "jun-30-2002" UNION ALL SELECT * FROM Q3_Sales WHERE s_date BETWEEN "jul-1-2002" AND "sep-30-2002" UNION ALL SELECT * FROM Q4_Sales WHERE s_date BETWEEN "oct-1-2002" AND "dec-31-2002";
Метод секционирования представлений с помощью предложения WHERE имеет некоторые недостатки. Во-первых, критерий секционирования проверяется во время выполнения для всех строк во всех секциях, которые охватываются запросом. Во-вторых, пользователи могут ошибочно вставить строку не в ту секцию, т.е. вставить строку, относящуюся к первому кварталу, в третий квартал, что приведет к неправильной выборке данных по этим кварталам.
У этого приема есть и достоинство по сравнению с использованием ограничения CHECK . Можно разместить секцию, соответствующую предикату WHERE , на удаленной базе данных. Фрагмент определения преставления приведен ниже.
Принимая решение о создании , необходимо помнить о следующих факторах.
- и удаление данных, работать на уровне секции, а не целой базовой таблицы.
- Доступ к одной из секций не оказывает никакого действия на данные в других секциях.
- СУБД Oracle обладает необходимыми встроенными возможностями для распознавания секционированных представлений .
- Секционирование представлений очень полезно при работе с таблицами, содержащими большое количество исторических данных.
Секционирование таблиц в СУБД семейства MS SQL Server
Создание секционированных таблиц
В СУБД семейства MS SQL Server также поддерживается секционирование таблиц, индексов и представлений. Однако, в отличие от СУБД семейства Oracle, секционирование в СУБД семейства MS SQL Server выполняется по унифицированной схеме.
В MS SQL Server все таблицы и индексы в БД считаются секционированными, даже если они состоят всего лишь из одной секции. Фактически, секции представляют собой базовую организационную единицу в физической архитектуре таблиц и индексов . Это означает, что логическая и физическая архитектура таблиц и индексов , включающая несколько секций, полностью отражает архитектуру таблиц и индексов , состоящих из одной секции.
Секционирование таблиц и индексов задается жестко на уровне строк (секционирование по столбцам не допускается) и позволяет осуществлять доступ через единую точку входа (имя таблицы или имя индекса ) таким образом, что в коде приложения не требуется знать число секций. Секционирование может осуществляться на базовой таблице, а также на связанных с ней индексах .
Каждая область значений в секции имеет границы, которые определены в операторе FOR VALUES . Если дата продажи была 23 июня 2006 года, то строка будет храниться в секции 2 (P2).
Теперь создадим схему секционирования . Схема секционирования отображает секции на различные файловые группы (с именами MyFilegroup1, MyFilegroup2, MyFilegroup3, MyFilegroup4 ) , как показано в следующей команде:
CREATE PARTITION SCHEME MyPartitionScheme AS MyPartitionFunction TO (MyFilegroup1, MyFilegroup2, MyFilegroup3, MyFilegroup4)
MyPartitionScheme – это имя схемы секционирования , а имя MyPartitionFunction определяет функцию секционирования . Эта команда отображает данные в секции, которые связаны с одной или несколькими файловыми группами. Строки с данными со значениями колонки "Дата продажи" (Date_of_Event date) до 1/01/05 связаны с MyFilegroup1 . Строки этой колонки со значениями, большими или равными 1/01/05 и до 1/01/07, назначены MyFilegroup2 . Строки со значениями, большими или равными 1/01/07 и до момента 1/01/09, связаны с MyFilegroup3 . Все остальные строки со значениями, большими или равными 1/01/09, связаны с MyFilegroup4 .
Для каждого набора граничных значений (которые задаются условием FOR VALUES функции секционирования ) количество секций будет равно "Количество граничных значений" + 1 секция. Предыдущее предложение CREATE PARTITION SCHEME включает три ограничения и четыре секции. Независимо от того, созданы ли секции с RANGE RIGHT или RANGE LEFT , количество секций всегда будет равно "Количество граничных значений" + 1, вплоть до 1000 секций на таблицу.
Теперь мы можем создать секционированную таблицу фактов "Продажи" (SALES). Создание секционированной таблицы мало чем отличается от создания обычной таблицы, нужно только сослаться на имя схемы секционирования в условии ON , как показано в команде ниже.
CREATE TABLE SALES (Sales_ШВ bigint identity (1, 1) primary not clustered NOT NULL, Cust_ID bigint null, Prod_ID bigint null, Store_ID bigint null, REG_ID char(10) null, Time_of_Event time null, Quantity integer not null, Amount dec(8,2) not null, Date_of_Event date NOT NULL) ON MyPartitionScheme (Date_of_Event)
Определяя имя схемы секционирования , проектировщик указывает, что эта таблица является индексы . Это позволяет проектировщику проектировать структуру индекса на основе разделенных данных, а не на основе всех данных таблицы. Создание секционированных индексов влечет за собой создание отдельных сбалансированных деревьев на секционированных индексах . В результате разделения индексов создаются индексы меньшего размера, и администратору БД или ХД становится проще их обслуживать во время изменения, добавления и удаления данных.
При создании секционированных индексов можно создавать выровненные или невыровненные индексы . Выровненные индексы подразумевают прямую связь с секционированными данными таблицы. В случае с невыровненными индексами выбирается другая схема секционирования .
Из этих двух методов предпочтителен выровненный индекс , который выбирается по умолчанию, если после создания секционированной таблицы индексы создаются без указания другой схемы секционирования . Использование выровненных индексов предоставляет необходимую гибкость для создания дополнительных секций в таблице, а также позволяет переводить принадлежность той или иной секции на другую таблицу. Для решения большинства задач, связанных с секционированием , достаточно применить для индексов схему секционирования таблицы.
Пример. 20.19 .
Создадим секционированный некластеризованный индекс на секционированной таблице "Продажи" (SALES) из предыдущего примера 20.18.
CREATE PARTITION SCHEME Index_primary_Left_Scheme AS PARTITION Index_Left_Partition ALL TO ()
Теперь выполним команду создания индекса , как показано ниже.
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_partition(Cust_ID) ON Index_primary_Left_Scheme (Cust_ID)
В этом некластеризованном индексе в качестве ключа индекса используется колонка "Идентификатор покупателя" (Cust_ID), которая не является ключом секционирования таблицы "Продажи" (SALES).
Решения о секционировании индексов принимаются проектировщиком ХД на стадии проектирования или администратором ХД на стадии эксплуатации ХД. Целью секционирования индексов является либо обеспечение производительности запросов, либо упрощение процедур сопровождения индекса .
Загрузка...
