Повышение производительности запросов: кластеры - Проектирование производительности хранилищ данных -

Это продолжение увлекательной статьи про повышение производительности запросов.

...

команду:

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).

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

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

Представление, или виртуальная таблица, которая предоставляет доступ к данным одной или более таблиц в СУБД MS SQL Server, создается командой CREATE VIEW, синтаксис которой приведен ниже.

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 
[ WITH  [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] [ ; ]

 ::= 
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     }

schema_name задает имя схемы, которой принадлежит представление, view_name задает имя представления. Имена представлений должны соответствовать требованиям, предъявляемым к идентификаторам. Указывать имя владельца представления не обязательно.

column задает имя, которое будет иметь колонка в представлении. Имя колонки требуется только в тех случаях, когда колонка формируется на основе арифметического выражения, функции или константы, если две или более колонки могут по иной причине получить одинаковые имена (как правило, в результате соединения) или если колонке представления назначается имя, отличное от имени колонки, из которой она произведена. Назначать колонкам имена можно также в инструкции SELECT. Если column не указан, столбцам представления назначаются такие же имена, которые имеют столбцы в инструкции SELECT.

AS определяет действия, которые должны быть выполнены в представлении.

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

CHECK OPTION обеспечивает соответствие всех выполняемых для представления команд модификации данных критериям, заданным при помощи select_statement. Если строка изменяется посредством представления, предложение WITH CHECK OPTION гарантирует, что после фиксации изменений доступ к данным из представления сохранится.

Атрибуты представления ENCRYPTION, SCHEMABINDING, VIEW_METADATA обеспечивают дополнительные возможности управления представлением (см. техническую документацию по T-SQL).

Секционированное представление — это представление, определенное посредством объединения всех ( UNION ALL ) таблиц-элементов. Эти таблицы структурированы одинаковым образом, но хранимы отдельно в форме разных таблиц, либо в одном экземпляре SQL Server, либо в группе автономных экземпляров SQL Server, которые называются федеративными серверами баз данных.

При разработке схемы секционирования должно быть ясно, какие данные относятся к каждой секции. Например, данные таблицы "Покупатели" (Customers) распределены между тремя таблицами-элементами на трех серверах: таблицей "Покупатели 33" (Customers_33) на сервере Server1, таблицей "Покупатели 66" (Customers_66) на сервере Server2 и таблицей "Покупатели 99" (Customers_99) на сервере Server3. Секционированное представление на сервере Server1 определяется, как в примере 20.20.

Пример 20.20.

Приведем определение секционированного представления на сервере Server1. Первая команда SELECT относится к таблице, размещенной на сервере Server1, вторая команда SELECT — к таблице, размещенной на сервере Server2, третья команда SELECT — к таблице, размещенной на сервере Server3.

CREATE VIEW Customers
AS
SELECT * FROM CompanyData.dbo.Customers_33
UNION ALL
SELECT * FROM Server2.CompanyData.dbo.Customers_66
UNION ALL
SELECT * FROM Server3.CompanyData.dbo.Customers_99

Как правило, представление считают секционированным, если оно соответствует следующему формату:

SELECT 
FROM T1
UNION ALL
SELECT 
FROM T2
UNION ALL
...
SELECT 
FROM Tn

Секционированные представления должны удовлетворять определенным требованиям.

1. Список выборки

   В списке столбцов определения представления должны быть выбраны все колонки таблиц-элементов.

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

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

   Одна колонка не может быть указана в списке выбора несколько раз.
2. Колонка секционирования

   Колонка секционирования является частью первичного ключа ( PRIMARY KEY ) таблицы.

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

   Если для одной колонки таблицы-элемента определено более одного ограничения, ядро СУБД пропускает все ограничения и не учитывает их при определении того, является ли представление секционированным. Чтобы соответствовать требованиям к секционированному представлению, с колонкой секционирования должно быть связано только одно ограничение секционирования.

   На возможность обновления колонки секционирования никакие ограничения не распространяются.
3. Таблицы-элементы или базовые таблицы T1, ..., Tn

   Таблицы-элементы могут быть или локальными таблицами, или таблицами с других компьютеров, на которых выполняется SQL Server. Во втором случае для ссылки на таблицу должно быть использовано или четырехкомпонентное имя, или имя в формате функции OPENDATASOURCE или OPENROWSET (см. техническую документацию по T-SQL).

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

   Одна таблица не может быть указана два раза в наборе таблиц, объединяемых при помощи инструкции UNION ALL.

   Таблицы-элементы не могут иметь индексы, созданные для вычисляемых колонок в таблице.

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

   Всем таблицам-элементам в представлении должно быть назначено одинаковое значение заполнения ANSI. Его можно задать либо при помощи аргумента user options процедуры sp_configure, либо при помощи инструкции SET.

   При наличии таблиц-элементов и определения секционированного представления оптимизатор запросов MS SQL Server составляет планы эффективного выполнения запросов для доступа к данным из таблиц-элементов. При наличии определений ограничения CHECK обработчик запросов составляет карту распределения значений ключей по таблицам-элементам. Когда пользователь выполняет запрос, обработчик запросов сравнивает карту со значениями, указанными в предложении WHERE, и создает план выполнения, позволяющий свести к минимуму объем передачи данных между серверами-элементами. Следовательно, несмотря на то, что некоторые таблицы-элементы могут храниться на удаленных серверах, экземпляр MS SQL Server разрешает распределенные запросы таким образом, чтобы объем передаваемых распределенных данных оказался минимальным.

Повышение производительности запросов: кластеры

Кластеризация таблиц

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

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

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

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

Если это индексный ключ, то для идентификации страницы данных в кластере используется индекс со структурой B-Tree, в котором строки, имеющие одинаковые значения ключа, размещаются либо в одной странице, либо в смежных страницах индекса. Такой кластер называется индексным кластером. Строки, которые хранятся в индексном кластере, не обязательно должны принадлежать одной таблице.

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

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

В СУБД семейства MS SQL Server кластеры не поддерживаются диалектом SQL. Все примеры, обсуждаемые ниже, ориентированы на использование СУБД семейства Oracle.

Кластеризация таблиц в СУБД семейства Oracle

В СУБД семейства Oracle поддерживаются различные типы кластеризации базовых таблиц. Рассмотрим использование кластеризации таблиц на примерах.

Пример 20.21.

Рассмотрим таблицы "Отдел" (DEPARTAMENT) и "Служащий" (EMPLOYEE), структура которых показана на рис. 20.8.

Рис. 20.8. Таблицы "Отдел" (DEPARTAMENT) и "Служащий" (EMPLOYEE)

Таблица "Служащий" (EMPLOYEE) описана в примере 20.1 настоящей лекции, а описание таблицы "Отдел" (DEPARTAMENT) приведено в табл. 20.2 ниже.

Обе таблицы некластеризованы и хранятся каждая на своих физических страницах. Предположим, что анализ запросов показывает, что для 80% запросов эти таблицы используются совместно, при этом соединение выполняется по колонке "Номер подразделения" (DEPNO). Можно построить кластер для этих двух таблиц.

До кластеризации таблицы сохраняются отдельно в своих физических областях на диске.

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

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

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

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

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

• значения колонок кластерных ключей распределены равномерно и плотно, а их размер почти всегда меньше размера физической страницы (иначе будут образовываться кластерные цепочки);
• в случае индексного кластера на каждый кластерный ключ приходится больше одной выбираемой строки, а для хеш-кластера — одна строка. Альтернативное решение — индексация таблицы;
• все данные для заданного кластерного ключа выбираются при каждом доступе по кластерному ключу. Альтернативное решение — индексация таблицы;
• интенсивность обращений операций вставки, обновления и удаления не очень велика, иначе общая производительность базы данных может уменьшиться (обратный отрицательный эффект).

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

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

Пример 20.22.

Создадим кластер для таблиц "Отдел" (DEPARTAMENT) и "Служащий" (EMPLOYEE). Для создания кластеров используется команда SQL CREATE CLUSTER, которая в нашем случае будет иметь вид:

CREATE CLUSTER emp_dept_c (DEPNO integer) 
SIZE 512,
-- TABLESPACE …
-- STORAGE …
INDEX;
CREATE TABLE DEPARTAMENT 
   ( 
   DEPNO 	integer NOT NULL, 
   DNAME 	char(20),
   LOC   		char(30),
   MANAGER  	char(20),
   PHONE  	char(15),
   CLUSTER emp_dept_c (DEPNO)
);

 CREATE TABLE EMPLOYEE
(
   EMPNO  	integer NOT NULL,
   ENAME  	char(40),
   LNAME  	char(20),
   DEPNO  	int NOT NULL,,
   JOB  		char(20),
   AGE  		date,
   HIREDATE  	date NOT NULL WITH DEFAULT,
   SAL  		dec(8,2),
   COMM  	dec(8,2),
   FINE  		dec(8,2),
   Biog 		text,
   Foto 		image,
   PRIMARY KEY (EMPNO),
   CLUSTER emp_dept_c (DEPNO) 
);

CREATE INDEX emp_dept_c_id ON CLUSTER emp_dept_c;

Параметр SIZE определяет размер кластера — фактически, максимальное число кластерных ключей, которые могут сохраняться на одной физической странице данных. Ключевое слово INDEX означает, что создаваемый кластер является индексным.

Предложение CLUSTER emp_dept_c (DEPNO) указывает СУБД, что таблица должна быть добавлена в кластер. Обратите внимание, что в таблице "Отдел" (DEPARTAMENT) снято ограничение первичного ключа на колонку "Номер подразделения" (DEPNO). Это связано с тем, что Oracle автоматически создает индекс на первичный ключ, а этот индекс в данном случае не нужен. Последнее предложение создает кластерный индекс для кластерного ключа кластера.

В одном из предыдущих разделов мы уже обсуждали вопрос использования таблиц хеширования, которые представляют на самом деле один из способов реализации кластера. Рассмотрим теперь подробно, как работать с хеш-кластером СУБД Oracle.

продолжение следует...

Продолжение:

Часть 1 Проектирование производительности хранилищ данных - indexing индексирование,
Часть 2 О некоторых параметрах проектирования индексов - Проектирование производительности хранилищ данных
Часть 3 Секционирование представлений - Проектирование производительности хранилищ данных - indexing индексирование,
Часть 4 Повышение производительности запросов: кластеры - Проектирование производительности хранилищ данных -
Часть 5 Резюме - Проектирование производительности хранилищ данных - indexing индексирование,