Опубликован: 20.12.2010 | Уровень: специалист | Доступ: платный
Лекция 15:

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

Секционирование представлений в СУБД семейства 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.

Владислав Нагорный
Владислав Нагорный

Подскажите, пожалуйста, планируете ли вы возобновление программ высшего образования? Если да, есть ли какие-то примерные сроки?

Спасибо!

Лариса Парфенова
Лариса Парфенова

1) Можно ли экстерном получить второе высшее образование "Программная инженерия" ?

2) Трудоустраиваете ли Вы выпускников?

3) Можно ли с Вашим дипломом поступить в аспирантуру?

 

Елена Любина
Елена Любина
Россия, Новосибирск, Новосибирский государственный технический университет, 2011