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

Секционирование представлений

В СУБД 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, на удаленной базе данных. Фрагмент определения преставления приведен ниже.

SELECT * FROM east_sales@icp.ac.ru WHERE LOC = 'EAST'
UNION ALL
SELECT * FROM west_sales@ioc.ac.ru WHERE LOC = 'WEST';

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

• Секционирование представлений позволяет операциям DML, таким как загрузка данных, создание индексов и удаление данных, работать на уровне секции, а не целой базовой таблицы.
• Доступ к одной из секций не оказывает никакого действия на данные в других секциях.
• СУБД Oracle обладает необходимыми встроенными возможностями для распознавания секционированных представлений.
• Секционирование представлений очень полезно при работе с таблицами, содержащими большое количество исторических данных.

Секционирование таблиц в СУБД семейства MS SQL Server

Создание секционированных таблиц

В СУБД семейства MS SQL Server также поддерживается секционирование таблиц, индексов и представлений. Однако, в отличие от СУБД семейства Oracle, секционирование в СУБД семейства MS SQL Server выполняется по унифицированной схеме.

В MS SQL Server все таблицы и индексы в БД считаются секционированными, даже если они состоят всего лишь из одной секции. Фактически, секции представляют собой базовую организационную единицу в физической архитектуре таблиц и индексов. Это означает, что логическая и физическая архитектура таблиц и индексов, включающая несколько секций, полностью отражает архитектуру таблиц и индексов, состоящих из одной секции.

Секционирование таблиц и индексов задается жестко на уровне строк ( секционирование по столбцам не допускается) и позволяет осуществлять доступ через единую точку входа (имя таблицы или имя индекса ) таким образом, что в коде приложения не требуется знать число секций. Секционирование может осуществляться на базовой таблице, а также на связанных с ней индексах.

Для создания секционированной таблицы в СУБД MS SQL Server используются следующие объекты БД: функции секционирования и схемы секционирования. Эти объекты позволяют разделять данные на конкретные сегменты и управлять их местоположением в БД или ХД. Например, можно распределить данные по нескольким дисковым массивам в зависимости от даты поступления данных или других отличительных признаков. Следует отметить, что таблицу можно секционировать по одному из ее столбцов, и каждая секция должна содержать данные, которые не могут храниться в других секциях.

Функции секционирования

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

Строки данных могут сегментироваться по колонке любого типа, кроме следующих: text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), псевдонимы типов данных и пользовательские типы данных среды CLR. Однако функция секционирования должна распределять каждую строку данных только в одну секцию таблицы ; иными словами, в результате применения функции одна и та же строка не может принадлежать нескольким секциям одновременно.

Чтобы секционировать таблицу, в ней необходимо создать или выбрать колонку секционирования ( ключ секционирования ). Ключ секционирования можно создать в схеме таблицы в момент создания таблицы либо добавить позднее путем модификации таблицы. Столбец может принимать значения NULL, но все строки, содержащие значения NULL, будут по умолчанию помещаться в самую левую секцию таблицы. Этого можно избежать, указав при создании функции секционирования, что значения NULL должны помещаться в самую правую секцию таблицы. Выбор левой или правой секций – важное решение проектирования, проявляющееся при изменении схемы секционирования, добавлении дополнительных секций или удалении существующих.

При создании функции секционирования можно выбрать функции LEFT или RIGHT. Разница между секциями LEFT и RIGHT состоит в размещении данных по секциям. Функция LEFT распределяет данные по принципу от самого низкого значения до самой высокой величины (то есть по возрастанию). Функция RIGHT распределяет данные по принципу от самого высокого значения до самого низкого (то есть по убыванию). Рассмотрим пример.

Пример 20.16.

Возьмем следующие примеры определения функций секционирования с использованием LEFT и RIGHT:

CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT 
FOR VALUES (1,10,100)

CREATE PARTITION FUNCTION Right_Partition (int) AS RANGE RIGHT 
FOR VALUES (1,10,100)

В первой функции ( Left_Partition ) значения 1, 10 и 100 размещаются соответственно в первой, второй и третьей секциях. Во второй функции ( Right_Partition ) эти значения размещаются во второй, третьей и четвертой секциях.

При создании секционированной таблицы важно, чтобы секции получились сбалансированными по кардинальности. Это позволяет оценить, сколько дискового пространства потребуется для каждой секции. Использование параметров LEFT и RIGHT определяет, куда будут размещаться данные, что, в свою очередь, задает размер секции и размеры индексов, созданных на ней.

Определить номер секции, в которую попадут те или иные данные, можно с помощью функции $PARTITION, как показано ниже:

SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Right_Partition (10)

Первая команда SELECT возвращает значение 2, вторая – значение 3.

Схемы секционирования

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

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

Пример 20.17.

Выполним присвоение файловых групп схеме секционирования. Сначала приведем пример размещения всех секций таблицы в одной файловой группе с именем PRIMARY.

CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition 
      ALL TO ([PRIMARY])

Чтобы разместить все секции в различных файловых группах, нужно выполнить следующую команду:

CREATE PARTITION SCHEME Different_Left_Scheme
AS PARTITION Left_Partition 
      TO (Filegroup1, Filegroup2, Filegroup3, Filegroup4)

Чтобы разместить несколько секций в различных файловых группах, нужно выполнить следующую команду:

CREATE PARTITION SCHEME Multiple_Left_Scheme
AS PARTITION Left_Partition 
      TO (Filegroup1, Filegroup2, Filegroup1, Filegroup2)

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

Пример 20.18.

Рассмотрим в качестве примера схему типа "звезда" с таблицей фактов "Продажи" (SALES), как показано на рис. 20.6. Создадим секционированную таблицу "Продажи" (SALES).

увеличить изображение
Рис. 20.6. Таблица фактов "Продажи" (SALES) в схеме типа "звезда"

Сначала мы должны создать функцию секционирования:

CREATE PARTITION FUNCTION
  MyPartitionFunctionLeft
  (date)
  AS RANGE LEFT
  FOR VALUES ('1/01/2005', '1/01/2007', '1/01/2009)

MyPartitionFunctionLeft — это название функции разделения, datetime — тип данных ключа секционирования, а RANGE LEFT указывает, как делить значения данных, которые связаны с датами FOR VALUES.

Ключ секционирования имеет тип данных date, т.е. это колонка "Дата события" (Date_of_Event). В команде, приведенной выше, деление строк на непересекающиеся группы построено по принципу разбиения их на двухлетние группы. Разделение на секции RANGE LEFT делит данные в диапазонах значений, показанных на рис. 20.7.

увеличить изображение
Рис. 20.7. Разделение на секции таблицы фактов "Продажи" (SALES) с использованием RANGE LEFT

Каждая область значений в секции имеет границы, которые определены в операторе 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)

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

Можно объединять только две смежные секции. Чтобы слить две секции, выполните команду:

ALTER PARTITION FUNCTION
  MyPartitionFunction()
  MERGE RANGE ('1/01/2007')

Здесь секция 1 (P1) объединится с секцией P2. Это означает, что секция P2 будет содержать все строки со значением колонки "Дата продажи" (Date_of_Event) до значения даты 1/01/07. В системной таблице sys.partitions секции будут перенумерованы, начиная с единицы (не с нуля). Секции P1 и P2 станут P1, секция P3 станет P2 и P4 станет P3.

Секционирование индексов в СУБД семейства MS SQL Server

В СУБД семейства MS SQL Server предусмотрена возможность создавать секционированные индексы. Это позволяет проектировщику проектировать структуру индекса на основе разделенных данных, а не на основе всех данных таблицы. Создание секционированных индексов влечет за собой создание отдельных сбалансированных деревьев на секционированных индексах. В результате разделения индексов создаются индексы меньшего размера, и администратору БД или ХД становится проще их обслуживать во время изменения, добавления и удаления данных.

При создании секционированных индексов можно создавать выровненные или невыровненные индексы. Выровненные индексы подразумевают прямую связь с секционированными данными таблицы. В случае с невыровненными индексами выбирается другая схема секционирования.

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

Пример. 20.19.

Создадим секционированный некластеризованный индекс на секционированной таблице "Продажи" (SALES) из предыдущего примера 20.18. Некластеризованный индекс выравнивается с таблицей; в качестве ключа некластеризованного индекса используется ключ секционирования таблицы.

CREATE NONCLUSTERED INDEX cl_multiple_partition ON SALES
(Date_of_Event)

Для того чтобы создать невыровненный некластеризованный индекс на секционированной таблице "Продажи" (SALES) из примера 20.18, можно поступить следующим образом. Сначала создадим функцию секционирования для индекса.

CREATE PARTITION FUNCTION Index_Left_Partition (bigint) AS RANGE
LEFT
FOR VALUES (10, 50, 100)

Затем разместим все секции индекса в одной файловой группе с именем PRIMARY, выполним команду

CREATE PARTITION SCHEME Index_primary_Left_Scheme
AS PARTITION Index_Left_Partition  ALL TO ([PRIMARY])

Теперь выполним команду создания индекса, как показано ниже.

CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_partition(
Cust_ID) ON Index_primary_Left_Scheme (Cust_ID)

В этом некластеризованном индексе в качестве ключа индекса используется колонка "Идентификатор покупателя" (Cust_ID), которая не является ключом секционирования таблицы "Продажи" (SALES).

Решения о секционировании индексов принимаются проектировщиком ХД на стадии проектирования или администратором ХД на стадии эксплуатации ХД. Целью секционирования индексов является либо обеспечение производительности запросов, либо упрощение процедур сопровождения индекса.