Нашли ошибку или опечатку? Выделите текст и нажмите

Поменять цветовую

гамму сайта?

Поменять
Обновления сайта
и новые разделы

Рекомендовать в Google +1

Индексированные представления

124
Исходники баз данных

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

Определение представления всегда содержит запрос, играющий роль фильтра. Если представление не имеет индексов, то компонент Database Engine динамически создает результирующий набор из всех запросов, которые обращаются к представлению. (Выражение "динамически" здесь означает, что модифицированное содержимое таблицы будет всегда отображаться в соответствующем представлении.) Кроме этого, если представление содержит вычисления по одному или больше столбцов таблицы, то эти вычисления выполняются при каждом обращении к представлению.

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

Компонент Database Engine позволяет создавать индексы для представлений. Такие представления называются индексированными представлениями. Результирующий набор, возвращаемый представлением с кластеризованным индексом, сохраняется в базе данных таким же образом, как и таблица с кластеризованным индексом. Это означает, что узлы листьев B+-дерева кластеризованного индекса содержат страницы данных.

Индексированные представления создаются посредством синтаксических расширений инструкций CREATE INDEX и CREATE VIEW. В инструкции CREATE INDEX вместо имени таблицы указывается имя представления. Синтаксис инструкции CREATE VIEW расширяется предложением SCHEMABINDING.

Создание индексированного представления

Индексированное представление создается в два этапа:

  1. Создается представление посредством инструкции CREATE VIEW с предложением SCHEMABINDING.

  2. Создается кластеризованный индекс для этого представления.

В примере ниже показан первый шаг создания индексированного представления - создание представления. В этом примере предполагается, что таблица Works_on имеет очень большой размер.

USE SampleDb;

GO
CREATE VIEW view_enter_month
    WITH SCHEMABINDING
    AS SELECT EmpId, DATEPART(MONTH, EnterDate) AS EnterMonth
    FROM dbo.Works_on;

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

USE SampleDb;

GO
CREATE UNIQUE CLUSTERED INDEX
    ix_work_deptnu ON view_enter_month (EnterMonth, EmpId);

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

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

  • QUOTED_IDENTIFIER

  • CONCAT_NULL_YIELDS_NULL

  • ANSI_NULLS

  • ANSI_PADDING

  • ANSI_WARNINGS

Кроме этого, параметру NUMERIC_ROUNDABORT нужно присвоить значение off.

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

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

  • представление должно ссылаться только на базовые таблицы;

  • представление и ссылки на базовую таблицу (таблицы) должны иметь одного владельца и принадлежать к одной и той же базе данных;

  • представление должно быть создано с опцией SCHEMABINDING. Эта опция связывает представление со схемой, содержащей базовые таблицы, лежащие в основе представления;

  • определенные пользователем функции, на которые ссылается представление, должны быть созданы с предложением SCHEMABINDING;

  • инструкция SELECT в представлении не должна содержать следующие предложения, параметры и прочие элементы: DISTINCT, UNION, TOP, ORDER BY, MIN, MAX, COUNT, OUTER, SUM (для выражений, допускающих значения NULL), подзапросы или производные таблицы.

Удовлетворение всех этих требований можно проверить посредством функции свойств objectproperty с параметром свойств IsIndexable, как показано в примере ниже. Если функция возвращает значение 1, то представление удовлетворяет всем требованиям для создания для него индекса:

USE SampleDb;

GO
SELECT objectproperty(
    object_id('view_enter_month'), 'IsIndexable'
);

Модифицирование структуры индексированного представления

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

Если вы хотите изменить обычное представление на индексированное, то для него вам нужно создать кластеризованный индекс. Чтобы сделать это, вы сначала должны указать предложение SCHEMABINDING. Представление можно удалить, а потом воссоздать, указав предложение SCHEMABINDING в инструкции CREATE SCHEMA, или же можно создать другое представление, которое имеет такой же текст, как и существующее представление, но имеет другое имя. При создании представления с другим именем необходимо обеспечить, чтобы это представление отвечало всем требованиям для индексированных представлений, описанных в предшествующем разделе.

Редактирование информации, связанной с индексированными представлениями

Проверить, активирован ли какой-либо параметр инструкции SET , можно с помощью функции свойств sessionproperty. Если функция возвращает значение 1, то указанный параметр установлен (т.е. имеет значение on). В примере ниже показано использование этой функции для проверки значения параметра QUOTED_IDENTIFIER:

USE SampleDb;

SELECT sessionproperty ('QUOTED_IDENTIFIER');

Наиболее простым способом является использование динамически административного представления sys.dm_exec_session, поскольку оно позволяет получить значения всех параметров инструкции SET, используя только один запрос. (Опять же, если значение столбца равно 1, то соответствующий параметр активирован.) В примере ниже демонстрируется использование этой функции для получения значений первых четырех параметров инструкции SET:

USE SampleDb;

SELECT quoted_identifier, concat_null_yields_null, 
    ansi_nulls, ansi_padding
    FROM sys.dm_exec_sessions
    WHERE session_id = @@spid;

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

USE SampleDb;

EXEC sp_spaceused 'view_enter_month';

Этот запрос возвращает следующий результирующий набор:

Проверка того, находится ли представление в памяти диска

Преимущества индексированных представлений

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

  • индекс представления может быть использован даже в том случае, если в представлении явно не указана ссылка на предложение FROM;

  • все изменения данных отражаются в соответствующих индексированных представлениях.

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

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

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

Можно значительно повысить уровень производительности следующих далее запросов, если проиндексировать представления, к которым они обращаются:

  • запросы, которые обрабатывают большое количество строк и содержат операции соединения или агрегатные функции;

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

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

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

Начиная с версии SQL Server 2008 R2, Microsoft предоставляет альтернативное решение, взамен индексированных представлений, которое называется фильтруемыми индексами. Фильтруемые индексы представляют собой особую форму некластеризованных индексов, в которой индекс сужается, используя условие в конкретном запросе. Использование фильтруемых индексов имеет несколько преимуществ над использованием индексированных представлений.

Пройди тесты