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

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

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

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

<view_attribute> ::= 
{
    [ 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 <select_list1>
FROM T1
UNION ALL
SELECT <select_list2>
FROM T2
UNION ALL
...
SELECT <select_listn>
FROM Tn

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

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

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

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

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

   Одна колонка не может быть указана в списке выбора несколько раз.
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.