• Статья

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

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

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

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

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

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

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

    SELECT ColA, ColB FROM TableT WHERE ColC * (ColD + 10) > 50

    с индексом, созданным для данного представления:

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB, ColC * (ColD + 10) AS ExpCol
FROM dbo.TableT

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

  • Скалярные статистические функции. Например следующий запрос, содержащий в списке выбора скалярную статистическую функцию:

    SELECT COUNT_BIG (*) FROM dbo.TableT

    может быть сопоставлен с индексом, созданным для данного представления:

    CREATE VIEW V2 WITH SCHEMABINDING AS
SELECT COUNT_BIG (*) AS Cnt 
FROM dbo.TableT

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

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

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB, ColC FROM dbo.TableT
WHERE ColA > 1 and ColA < 10

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

    SELECT ColB, ColC FROM dbo.TableT
WHERE ColA > 3 and ColA < 7

    Оптимизатор запросов сопоставил бы этот запрос с представлением V1, потому что интервал 3—7, определенный в запросе, попадает в интервал 1—10, определенный в индексированном представлении.

  • Насколько выражение, определенное в запросе, соответствует выражению, определенному в индексированном представлении. SQL Server пытается сопоставить выражения на основе упоминаемых в них столбцов, литералов, логических операторов AND, OR, NOT, BETWEEN и IN и операторов сравнения =, <>, >, <, >= и <=. Арифметические операторы, такие как + и %; параметры при этом не учитываются.

    Например оптимизатор запросов сопоставил бы следующий запрос:

    SELECT ColA, ColB from dbo.TableT
WHERE ColA < ColB

    с индексом, созданным для данного представления:

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB FROM dbo.TableT
WHERE ColB > ColA

Помните, что, как и при работе с любым индексом, SQL Server использует в плане запроса индексированное представление только в том случае, если оптимизатор запросов определяет, что это целесообразно.

Индексированные представления можно создавать в любом выпуске SQL Server 2008. В SQL Server 2008 Enterprise оптимизатор запросов учитывает индексированные представления автоматически. Чтобы использовать индексированные представления в любых других выпусках, следует применить табличную подсказку NOEXPAND.

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

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

Индексированные представления повышают эффективность запросов следующих типов:

  • Соединения и статистические вычисления, в ходе которых обрабатывается большое число строк.

  • Соединения и статистические вычисления, которые часто выполняются несколькими запросами.

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

  • Запросы систем поддержки принятия решений.

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

    CREATE TABLE wide_tbl(a int PRIMARY KEY, b int, ..., z int)
CREATE VIEW v_abc WITH SCHEMABINDING AS
SELECT a, b, c
FROM dbo.wide_tbl
WHERE a BETWEEN 0 AND 1000
CREATE UNIQUE CLUSTERED INDEX i_abc ON v_abc(a)

    Чтобы обработать следующий запрос, достаточно представления v_abc:

    SELECT b, count_big(*), SUM(c)
FROM wide_tbl 
WHERE a BETWEEN 0 AND 1000
GROUP BY b

    Представление v_abc занимает гораздо меньше страниц, чем таблица wide_tbl. Таким образом, при разрешении предыдущего запроса оптимизатор выберет, пожалуй, представление, а не таблицу.

    Если нужно разделить по вертикали целую таблицу или ее подмножество, желательно использовать не индексированное представление, а некластеризованный индекс таблицы, указав столбцы, которые должны войти в результат, при помощи предложения INCLUDE. Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL).

Эффективность запросов следующих типов индексированные представления обычно не повышают:

  • Запросы систем OLTP, включающие большое число операций записи.

  • Операции над часто обновляемыми базами данных.

  • Запросы, не включающие статистических выражений или операций соединения.

  • Статистическая обработка данных с большим количеством элементов ключа GROUP BY. Если количество элементов ключа велико, это означает, что ключ содержит много разных значений. Уникальный ключ имеет максимально возможное количество элементов, потому что каждый ключ имеет свое значение. Индексированные представления повышают эффективность работы с такими ключами, уменьшая число обрабатываемых строк. Если результирующий набор представления включает почти столько же строк, сколько содержится в базовой таблице, выгода от использования представления невелика. Взгляните, например, на следующий запрос данных из таблицы, включающей 1 000 строк:

    SELECT PriKey, SUM(SalesCol)
FROM ExampleTable
GROUP BY PriKey

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

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

Комбинирование индексированных представлений с запросами

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

Рассмотрим следующие примеры:

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

  • Часто выполняемый запрос осуществляет статистическую обработку информации, хранящейся в нескольких таблицах, после чего объединяет результаты при помощи операции UNION. Индексированные представления эту операцию не поддерживают. В этом случае также можно создать представления для выполнения отдельных операций статистической обработки данных. Оптимизатор сможет выбирать индексированные представления для ускорения обработки запросов, а сами запросы переписывать не придется. Выполнение операции UNION не ускорится, но отдельные процессы статистической обработки данных станут более эффективными.

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

Возьмем в качестве примера создание индекса для следующего представления:

CREATE VIEW ExampleView WITH SCHEMABINDING
AS
SELECT GroupKey, SUM(Colx) AS SumColx, COUNT_BIG(Colx) AS CountColx
FROM MyTable
GROUP BY GroupKey

Это представление не только отвечает требованиям запросов, в которых непосредственно упоминаются столбцы представления, но и может быть использовано при выполнении запросов, адресованных к базовой таблице и содержащих такие выражения, как SUM(Colx), COUNT_BIG(Colx), COUNT(Colx) и AVG(Colx). Все такие запросы будут обрабатываться быстрее, потому что вместо чтения всех строк базовых таблиц можно будет извлечь только небольшое число строк представления.

Аналогичным образом индексированное представление, составляющее статистику для данных и групп по дням, может быть использовано для повышения эффективности запросов, которые выполняют статистическую обработку данных за разные интервалы, превышающие 1 день: например за 7, 30 или 90 дней.