Секционированные таблицы и индексы SQL Server 2005 - Секционированные Таблицы в SQL Server 2005

 

Секционированные таблицы в SQL Server 2005

В то время как усовершенствования SQL Server 7.0 и SQL Server 2000 значительно улучшили производительность секционированных представлений, они не упрощали их администрирования, разработки или развертывания. Все таблицы, на основе которых строилось секционированное представление, создавались и управлялись по отдельности. Разработка приложений становилась проще за счет того, что разработчику уже не приходилось обращаться непосредственно к базовым таблицам, однако администрирование было затруднено, поскольку приходилось управлять каждой отдельной таблицей, входящей в состав секционированного представления, и его ограничениями целостности. Из-за сложностей управления, разделение таблиц зачастую использовалось только тогда, когда данные нужно было "заархивировать" или загрузить. Операции добавления/удаления из доступной только на чтение таблицы (read-only) были слишком дорогостоящими - они занимали время, место в журнале транзакций, и часто создавали блокировки.
Кроме того, поскольку предшествующие стратегии секционирования требовали, чтобы разработчик создавал индивидуальные таблицы и индексы и затем объединял их посредством представления, оптимизатору запросов требовалось проверить достоверность и определить планы исполнения для каждой секции (поскольку индексы могли измениться). Поэтому время оптимизации запроса в SQL Server 2000 зачастую линейно возрастает с увеличением количества секций, входящих в представление, чего не происходит в SQL Server 2005, где каждая секция по определению имеет одни и те же индексы.

К примеру, разберем случай, когда текущий месяц OLTP-данных (Online Transaction Processing), должен быть перемещен в конце месяца в OLAP-таблицу. Самая последняя таблица (предназначенная для запросов read-only) - это одиночная таблица с одним кластерным и двумя некластерными индексами; массовая загрузка (bulk load) 1GB данных (в уже проиндексированную и действующую таблицу) создает блокировки с текущими пользователями помимо того, что таблица и/или индексы становятся фрагментированным и/или блокированными. Кроме того, процесс загрузки займет существенное время, поскольку таблица и индексы должны обслуживаться по мере поступления каждой строки. Есть способы, позволяющие ускорить bulk load, однако, они могут непосредственно затронуть всех остальных пользователей, таким образом, принося в жертву возможность параллельной работы ради скорости исполнения. Если бы эти данные добавлялись в недавно созданную (пустую) таблицу, то вначале могла бы произойти загрузка данных (в т.ч. параллельная загрузка), а затем построение индексов (возможно, также параллельное). Зачастую вы смогли бы достигать 10-ти кратного (или еще большего) преимущества от использования данного подхода. Фактически, загружая в неиндексированную таблицу (heap - кучу), Вы можете воспользоваться преимуществом многопроцессорной системы, загружая параллельно многочисленные файлы данных или многочисленные "фрагменты" одного и того же файла (заданные начальными и конечными строками).

В любом из выпусков SQL Server секционирование позволяет Вам управлять таблицами на более высоком уровне, не обязывая Вас хранить все данные в одном месте - с сильно фрагментированными индексами и отсутствием реального управления любым аспектом поведения на более высоком уровне. Функциональная стратегия секционирования могла быть достигнута в предыдущих выпусках, путем динамического создания и удаления таблиц и модифицирования UNION-представлений. Однако в SQL Server 2005 решение более изящно: Вы можете просто "включить" ("switch in") недавно-наполненную секцию(и), как дополнительную секцию к существующей схеме секционирования, либо "выключить" ("switch out") любую старую секцию(и). Процесс "включения/выключения" секций занимает незначительное время, и может быть даже ускорен за счет применения параллельной загрузки данных (bulk loading) и параллельного создания индексов. Что еще более важно, секция управляется из-за пределов таблицы, таким образом, во время добавления новой секции на действующую таблицу не оказывается никакого воздействия. В результате, добавление секции происходит за считанные секунды.

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

Когда все данные находится в одной единственной таблице, удаление 1GB данных (самых старых данных) требует построчной манипуляции данными и связанными с ними индексами. Процесс удаления данных приводит к существенной log-активности и не позволяет усекать журнал транзакций до конца удаления (помните, что удаление - это отдельная auto-commit транзакция; тем не менее, Вы можете управлять размером транзакции, выполняя множественное удаление в одной транзакции там, где только возможно), а также требуется [потенциально очень] больший журнал транзакций. Чтобы удалить такое же количество данных, удаляя определенную секцию из секционированной таблицы, все, что надо сделать - это "выключить" секцию (что является операцией над метаданными), и затем удалить или усечь автономную таблицу.

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

На Рисунке 2 представлена файловая группа, состоящая из трех файлов. В ней располагаются две таблицы: Orders и OrderDetails. Когда данные таблиц размещаются в файловой группе, SQL Server пропорционально заполняет файлы файловой группы, захватывая в них необходимое дисковое пространство для своих объектов экстентами (кусками по 64 Kb, что равно 8 страницам данных по 8 Kb). В момент создания таблиц Orders и OrderDetails файловая группа будет пуста. Когда приходит новый заказ, в таблице Orders создается соответствующая запись, и по одной записи в таблице OrderDetails для каждого заказанного товара. SQL Server выделяет один экстент для таблицы Orders в File1, и затем еще один экстент для таблицы OrderDetails в File2. По всей вероятности, таблица OrderDetails будет расти быстрее, чем таблица Orders, и поэтому следующие несколько экстентов будут выделены для нее: следующий экстент для таблицы OrderDetails будет располагаться в файле File3. На приведенном ниже рисунке продемонстрировано размещение экстентов данных таблиц Orders и OrderDetails в файловой группе.

Рисунок 2. Пропорциональное заполнение файлов

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

В SQL Server 2005 секционированная таблица может быть спроектирована (используя "функции" и "схемы") таким образом, чтобы строки, имеющие одинаковый ключ секционирования, размещались бы в строго указанном месте. Функция секционирования определяет границы секций и то, в какую секцию должно быть занесено первое значение. В случае LEFT-функции, первое значение будет являться верхней границей в первой секции. В случае RIGHT-функции, первое значение будет являться нижней границей во второй секции. Мы еще рассмотрим подробно особенности функций секционирования дальше в этой статье. Как только функция определена, может быть создана схема секционирования для того, чтобы определить физическое расположение секций в базе данных. Если несколько таблиц используют одну и ту же функцию (но не обязательно одну и ту же схему), строки, имеющие один и тот же ключ секционирования, будут располагаться на диске вместе. Этот принцип называется выравниванием. Выравнивая строки нескольких таблиц по ключу секционирования, SQL Server может (если оптимизатор запросов предпочтет) работать только с необходимыми группами данных (в каждой из таблиц). Для того чтобы выровняться, две секционированные таблицы или два индекса должны иметь некоторое соответствие между их соответствующими секциями. Они должны использовать "эквивалентные" функции секционирования и быть связаны по столбцам секционирования. Две функции секционирования могут использоваться для выравнивания данных, если:

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

• ключ секционирования, используемый в каждой функции, имеет одинаковый тип (включая длину, точность и масштаб (если допускается), и collation (если допускается)).

• граничные значения эквивалентны (включая критерии границы LEFT/RIGHT).

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

Локализация (Collocation) - более строгая форма выравнивания, когда два выровненных объекта объединены с предикатом equi-объединения (inner), где equi-объединение производится по столбцу секционирования. Это становится важным в контексте запроса, подзапроса или другой подобной конструкции, где могут встретиться предикаты equi-объединения. Локализация эффективна, поскольку запросы, объединяющие таблицы по столбцам секционирования, выполняются тогда значительно быстрее. Возьмем, например, таблицы Orders и OrderDetails, описанные выше. Вместо того чтобы заполнять файлы пропорционально, Вы можете создать схему секционирования, которая разнесет БД по трем файловым группам. Вы определяете таблицы Orders и OrderDetails таким образом, чтобы они использовали одну и ту же схему. Связанные данные (по ключу секционирования) будут помещены в один и тот же файла, таким образом, изолируя необходимые для объединения данные. Когда связанные строки из нескольких таблиц секционированы по одному и тому же принципу, SQL Server может объединять секции, не имея необходимости рыться во всей таблице или нескольких секциях (если к таблицам применялись разных функций секционирования) для сопоставления строк. В этом случае, объекты не просто выровнены, они, как говорится, являются выровненным хранилищем, поскольку связанные данные располагаются в одних и тех же файлах.

Следующий рисунок демонстрирует, как два объекта могут использовать одну и ту же схему секционирования, когда все строки данных с одинаковым ключом секционирования окажутся в одной и той же файловой группе. Когда связанные данные выровнены, SQL Server 2005 может эффективно работать с большими наборами данных параллельно. Все данные о продажах за январь (как для Orders, так и для OrderDetails) будут располагаться в первой файловой группе, данные за февраль - во второй файловой группе и т.д.

Рисунок 3. Таблицы выровненных хранилищ

SQL Server поддерживает секционирование, основанное на диапазонах. Таблицы, так же как и индексы могут использовать одну и ту же схему для лучшего выравнивания. Хорошее проектирование способно значительно улучшить производительность системы, но что, если использование данных все время меняется? Что, если потребуется дополнительная секция? Простота администрирования при добавлении и удалении секций, а также управления секциями извне секционированной таблицы была главной целью при разработке SQL Server 2005.

SQL Server 2005 упростил секционирование для администрирования, разработки и развертывания, а также для понимания. Вот некоторые из усовершенствований в производительности и управляемости:

• Упрощается разработка и реализация больших таблиц, которые должны быть разделены для улучшения производительности.

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

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

• Архивирование и/или удаление части секционированной таблицы минимально воздействует на оставшуюся часть таблицы.

• Поддерживается "переключение" секций в/из секционированной таблицы.

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

• Улучшается производительность всех секций.

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