Для чего нужно секционирование?
Прежде, чем говорить о том, как осуществлять секционирование и использовать его возможности, сначала нужно понять, насколько оно необходимо, что такое секционирование и кому стоит его использовать? Когда Вы создаете таблицы, Вы проектируете их таким образом, чтобы хранить информацию о сущностях, например, о покупателях или продажах. Каждая таблица должна иметь атрибуты, которые описывают только эту сущность, и, например, для всех покупателей и продаж исторически сложилось так, что все Ваши покупатели и все Ваши продажи создаются в соответствующих таблицах.
В то время как наличие одной единственной таблицы для каждой сущности является наиболее простым подходом для проектирования и понимания, это может быть не лучшим решением с точки зрения производительности, масштабируемости и управляемости, особенно в тех случаях, когда таблицы становятся достаточно большими. Секционирование может обеспечить преимущества как для больших таблиц (и/или их индексов), так и для таблиц, которые имеют изменяющиеся модели доступа. Более того, секционирование больших таблиц повышает их масштабируемость и управляемость, а также упрощает использование таблиц при добавлении или удалении значительных фрагментов (или диапазонов) данных.
Так что же представляет собой большая таблица? VLDB (Very Large Database) принято называть базы данных, совокупный размер которых измеряется сотнями гигабайт, или даже терабайтами; однако данный термин никак не определяет индивидуальные размеры таблиц. Большая таблица - это такая таблица, показатели производительности или временные затраты на обслуживание которой выходят за допустимые рамки. Кроме того, таблицу можно считать большой, если действия, производимые одним пользователем, оказывают значительное влияние на другого пользователя, или если операции обслуживания базы данных влияют на возможности остальных пользователей. Это приводит к ограничению доступности базы данных. Ведь даже притом, что сервер продолжает оставаться доступным, как можно считать Вашу базу данных доступной, когда рабочие характеристики таблицы продаж значительно ухудшились, либо таблица вовсе недоступна в течение всего периода текущего обслуживания базы данных по 2 часа в день, в неделю, или пусть даже в месяц? В некоторых случаях регулярный простой все же допустим, но чаще всего простоя можно избежать или минимизировать за счет улучшения проектирования.
Таблицу, модель доступа к которой изменяется, можно также считать большой, когда подмножества (или диапазоны) строк этой таблицы имеют разные модели использования. И хотя модели использования не обязательно должны изменяться (и это вовсе не является требованием секционирования), когда модели использования все же меняются, тогда могут быть получены дополнительные преимущества от секционирования. С точки зрения продаж, данные текущего месяца обычно используются для чтения/записи (read-write), в то время как данные предыдущих месяцев (и часто большая часть таблицы) - только для чтения (read-only). Если в больших таблицах использование данных изменяется, либо накладные расходы на обслуживание огромны, то это может ограничить способность таблицы отвечать на различные пользовательские запросы, в свою очередь, ограничивая и доступность и масштабируемость. Кроме того, особенно когда большие массивы данных используются по-разному, операции обслуживания могут отказаться от планового обслуживания статичных данных. Обслуживание данных, которые в этом вовсе не нуждаются - слишком дорогое удовольствие. Излишние затраты могут отразиться на производительности, блокировках, резервном копировании (дисковом пространстве, времени и эксплуатационных характеристиках), а так же отрицательно воздействовать на общую масштабируемость сервера.
Кроме того, в многопроцессорных системах разделение больших таблиц приведет к улучшению производительности за счет параллелизма. Крупномасштабные операции над чрезвычайно большими наборами данных (в миллионы строк) могут извлечь выгоду из параллельной обработки независимых подмножеств данных. В качестве простого примера улучшения производительности при использовании секционирования может выступать агрегирование (группировка) в предыдущих версиях сервера. Например, вместо того, чтобы группировать данные в одной большой таблице, SQL Server может группировать их в нескольких секциях независимо друг от друга, и затем объединить агрегаты. В SQL Server 2005, объединения могут извлекать данные непосредственно из секций; SQL Server 2000, поддерживающий параллельные объединения наборов данных, все же должен был создавать эти наборы данных на лету. В SQL Server 2005, связанные таблицы (к примеру, Order и OrderDetails), которые разделены по одному и тому же ключу секционирования и одной и той же функции секционирования, называются выровненными. Если оптимизатор SQL Server 2005 обнаруживает, что объединяются две секционированные и выровненные таблицы, он может предпочесть объединить сначала данные, располагающиеся в одних и тех же секциях, а затем объединить результаты. Это позволяет SQL Server 2005 более эффективно использовать многопроцессорные системы.
Итак, чем же может помочь секционирование? Там, где таблицы и индексы становятся слишком большими, секционирование может помочь, расщепляя большие массивы данных на меньшие, более управляемые "куски" (т.е. секции). Тип секционирования, описанного в этой статье, называют горизонтальным секционированием. При горизонтальном секционировании большие "куски" строк сохраняются в нескольких отдельных секциях. Архитектура секционированных данных выбирается, настраивается и управляется согласно вашим потребностям. Секционирование в SQL Server 2005 позволяет вам разделять ваши таблицы, основанные на индивидуальных моделях использования данных, задавая ограниченные диапазоны. И наконец, SQL Server 2005 предоставляет большое число настроек (опций) для управления секционированными таблицами и индексами, добавляя дополнительные функции, спроектированные специально для новой концепции.
Не смотря на то, что секционированные таблицы и индексы всегда были неотъемлемой частью больших баз данных, призванной улучшать их производительность и управляемость, в Microsoft SQL Server 2005 появились новые возможности, упрощающие процесс разработки таких решений. Данный доклад посвящен эволюции секционирования таблиц в SQL Server: от ручного секционирования данных путем предварительного создания таблиц в качестве подготовительного шага (в SQL Server 7.0 и SQL Server 2000) до процедур реального секционирования таблиц. В SQL Server 2005 новые табличные функции секционирования значительно упрощают разработку и администрирование секционированных таблиц, в месте с тем еще более увеличивая их производительность.
Основная задача статьи - составить для Вас представление о секционировании в SQL Server 2005, о том зачем, где и как применять его с большей пользой для Ваших сверхбольших баз данных (VLDB - Very Large Database). Но несмотря на то, что секционирование в SQL Server 2005 нацелено прежде всего на работу с VLDB, следует помнить, что не все базы данных большие с самого начала. SQL Server 2005 обеспечивает гибкость и производительность, значительно упрощая создание и обслуживание секционированных таблиц. Прочтите эту статью, чтобы получить подробную информацию о том, почему Вам стоит знать о секционированных таблицах, что они могут Вам предложить, и, наконец, как разрабатывать, реализовывать и обслуживать секционированные таблицы.
История секционирования
Концепция секционирования для SQL Server не нова. По сути, секционирование в разных формах присутствовало в каждом релизе. Однако, без функций, помогающих в создании и поддержке вашей схемы секционирования, секционирование было неудобным. А, как правило, если инструмент неудобен в работе, то и преимущества от технологии уменьшаются. Тем не менее, из-за существенного выигрыша в производительности, присущего секционированию, с SQL Server 7.0 началось его усовершенствование, от секционированных представлений, поддерживавших некоторые формы секционирования, до наиболее усовершенствованных секционированных таблиц в SQL Server 2005.
Ручное секционирование объектов в версиях, предшествовавших SQL Server 7.0
В SQL Server 6.5 и более ранних версиях, секционирование должно было быть частью вашего проекта, а заодно и встраивалось во все ваши инструкции доступа к данным и запросы. Путем создания нескольких таблиц, и затем управления доступом к необходимым таблицам через хранимые процедуры, представления или клиентские приложения, вы могли часто улучшить производительность некоторых операций, но за счет сложности разработки. Каждый пользователь и разработчик должны были знать и должным образом ссылаться на соответствующие таблицы. Каждая секция создавалась и управлялась отдельно, а представления использовались, для упрощения доступа; тем не менее, это решение давало некоторые преимущества. Когда для упрощения пользовательского и программного доступа применялось объединенное представление (использующее инструкцию UNION), процессор запросов должен был получить доступ к каждой базовой таблице, чтобы определить, находись ли в ней данные, необходимые для результирующей выборки. Если для выполнения запроса была необходима только часть тех базовых таблиц, то каждый пользователь или разработчик должны были разбираться в модели данных, чтобы ссылаться только на необходимые таблицы.
Секционированные представления в SQL Server 7.0
Основные претензии, которые предъявлялись к ручному созданию секций в версиях, предшествовавших SQL Server 7.0, касались в первую очередь производительности. В то время как представления упростили разработку приложений, пользовательский доступ и написание запросов не упростились. С выпуском SQL Server 7.0, представления были объединены с ограничениями целостности, что позволило оптимизатору запросов удалять лишние таблицы из плана исполнения запроса (т.е. исключать секции) и значительно уменьшать стоимость плана исполнения, когда объединенное (UNIONed) представление обращалось к нескольким таблицам.
На Рисунке 1, изображена схема представления YearlySales (годовой товарооборот). Вместо того чтобы размещать все продажи в одной-единственной большой таблице, Вы можете определить двенадцать отдельных таблиц, по одной на каждый месяц (SalesJanuary2003, SalesFebruary2003, и т.д.).
Рисунок 1: Секционированные представления в SQL Server 7.0/2000
Пользователи, которые будут обращаться к представлению YearlySales со следующим ниже запросом, будут адресоваться ТОЛЬКО к таблице SalesJanuary2003.
SELECT ys.* |
Пока ограничения целостности являются "доверительными" ("trusted"), и запросы к представлениям используют инструкцию WHERE для ограничения результатов выборок, основываясь на ключе секционирования (столбце, по которому определено ограничение целостности), до тех пор SQL Server будет обращаться только к необходимым базовым таблицам. "Доверительное" ограничение целостности - это ограничение целостности, для которого SQL Server в состоянии гарантировать, что все его данные придерживаются свойств, заданных ограничением целостности. По умолчанию, ограничение целостности создается с опцией WITH CHECK. Этот параметр вызывает блокировку схемы таблицы для того, чтобы данные могли быть сверены с ограничением целостности. Ограничение целостности будет добавлено, как только верификация проверит достоверность существующих данных; но если вдруг блокировка схемы будет удалена, последующие операторы INSERT, UPDATE и DELETE должны будут самостоятельно соблюдать ограничения целостности. Благодаря "доверительным" ограничениям целостности, разработчики могут значительно упростить создание представлений, поскольку им уже не нужно напрямую обращаться к интересующим их таблицам. Благодаря "доверительным" ограничениями целостности SQL Server улучшает производительность запросов, исключая ненужные таблицы из плана исполнения.
Примечание: ограничение целостности может стать "не доверяемым" ("untrusted") по нескольким причинам; например, если при выполнении оператора bulk-insert не задан аргумент CHECK_CONSTRAINTS. Как только ограничение целостности станет "не доверяемым", процессор запросов вернется к сканированию всех базовых таблиц, поскольку нет никакого способа подтвердить, что запрашиваемые данные действительно расположены в искомой таблице.
Секционированные представления в SQL Server 2000
Не смотря на то, что SQL Server 7.0 значительно упростил разработку и улучшил производительность операторов SELECT, операторы модификации данных не претерпели никаких изменений; операторы INSERT, UPDATE и DELETE поддерживались только для базовых таблиц, и не поддерживались для представлений, объединявших наборы данных (с помощью оператора UNION). SQL Server 2000 позволил операторам модификации данных также извлекать выгоду из возможностей секционированных представлений, разрешая через представления модифицировать соответствующие базовые таблицы. И хотя существуют дополнительные ограничения на создание ключа секционирования, тем не менее, основной принцип заключается в том, что не только операторы SELECT, но и операторы модификации данных будут направляться непосредственно соответствующим базовым таблицам. За более полной информацией об ограничениях, настройке, конфигурации и некоторых приемах секционирования в SQL Server 2000 вы можете обратиться к статье:
Секционированные таблицы в 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 упростил секционирование для администрирования, разработки и развертывания, а также для понимания. Вот некоторые из усовершенствований в производительности и управляемости:
-
Упрощается разработка и реализация больших таблиц, которые должны быть разделены для улучшения производительности.
-
Данные загружаются в новую секцию существующей секционированной таблицы с минимальным нарушением доступа к данным в остальных секциях.
-
Данные загружаются в новую секцию существующей секционированной таблицы со скоростью, равной скорости загрузки данных в новую пустую таблицу.
-
Архивирование и/или удаление части секционированной таблицы минимально воздействует на оставшуюся часть таблицы.
-
Поддерживается "переключение" секций в/из секционированной таблицы.
-
Обеспечивается лучшее масштабирование и параллелизм для чрезвычайно больших операций над несколькими связными таблицами.
-
Улучшается производительность всех секций.
-
Уменьшается время оптимизации запроса, поскольку каждая секция не должна быть оптимизирована отдельно.
Определения и терминология
Чтобы создавать секции в SQL Server 2005, Вам необходимо познакомиться с несколькими новыми понятиями, терминами и синтаксисом. В предыдущих выпусках SQL Server таблица была всегда физическим и логическим понятием, теперь в SQL Server 2005 для Секционированных Таблиц и Индексов у вас есть на выбор несколько вариантов того, как и где хранить таблицу. В SQL Server 2005, таблицы и индексы могут быть созданы с точно таким же синтаксисом, как и в предыдущих релизах - как простая табличная структура, помещенная в DEFAULT filegroup или определенную пользователем файловую группу (user-defined filegroup). Кроме того, в SQL Server 2005 таблица и индексы также могут быть основаны на схеме секционирования. Схема секционирования отобразит объект на одну или возможно несколько файловых групп. Для определения того, какие данные где размещать, схема секционирования использует функцию секционирования. Функция секционирования определяет алгоритм, используемый для маршрутизации строк, а схема связывает секции с их соответствующим физическим местоположением (т.е. файловой группой). Другими словами, таблица по-прежнему является логическим понятием, но её физическое расположение на диске может радикально отличаться от более ранних выпусков SQL Server; таблица может иметь схему.
Диапазонные секции
Диапазонные секции - это табличные секции, описанные определенными и настраиваемыми диапазонами данных. Границы диапазонных секций выбираются разработчиком, и могут быть изменены, если изменится модель использования данных. Как правило, эти диапазоны основываются на дате или на упорядоченных группировках данных.
Основное применение диапазонных секций - архивирование данных, поддержка принятия решений (когда зачастую необходимы только определенные диапазоны данных, например, только заданный месяц или квартал), и объединение OLTP и DSS (Decision Support System - система поддержки принятия решений), где использование данных меняется в течение всего жизненного цикла записи базы данных. Самое большое преимущество новой технологии (особенно для архивирования и обслуживания) состоит в способности манипулировать крайне специфичными диапазонами данных. Диапазонные секции архивируют старые данные и загружают новые чрезвычайно быстро. Секции диапазона лучше всего подходят для ситуации, когда доступ к данным осуществляется для поддержки принятия решений и основывается на больших диапазонах данных. В этом случае Вы заботитесь о том, где конкретно расположены данные, так чтобы обращение велось только к подходящим секциям. Когда появляются новые бизнес - данные, Вы естественно захотите добавлять их - легко и быстро.
Создание диапазонных секций несколько усложнено, поскольку вам потребуется определить граничные условия для каждой секции. Еще вы должны будете создать схему, отображающую каждую секцию на файловую(ые) группу(ы). Тем не менее, они часто имеют совместимый шаблон, так что, будучи однажды определены, они, вероятно, будут легки в программной поддержке (см. рис. 4).
Рисунок 4: Диапазонная секционированная таблица - 12 Секций.
Определение ключа секционирования
Первый шаг в секционировании таблиц и индексов состоит в определении "ключа". Ключ секционирования - это столбец(ы) таблицы, который удовлетворяет определенным критериям. Функция секционирования определяет тип данных, на котором базируется логическое разделение данных. Физическое размещение данных определено схемой секционирования. Другими словами, схема отображает данные на файловые группы, которые в свою очередь отображают данные на конкретные файлы. Для этого схема всегда использует функцию - если функция определяет пять секции, тогда схема должна использовать пять файловых групп. Однако совсем не обязательно, чтобы все файловые группы были разными; тем не менее, Вы получите больший выигрыш в производительности, если будете использовать систему из нескольких дисков, предпочтительно многопроцессорную. При использовании схемы вы определяете столбец, который будет выступать в качестве аргумента для функции секционирования.
Данные в диапазонных секциях разделены логически. Фактически, секции данных в действительности не могут быть сбалансированы вообще. Однако использование данных навязывает диапазонную секцию, поскольку модель использования этой таблицы определяет специальные границы для анализа (иначе называемые "диапазонами"). Ключ секционирования для диапазонной функции может состоять только из одного столбца, и функция секционирования будет включать всю область данных, даже если эти данные недопустимы для таблицы. Другими словами, границы определены для каждой секции, но первая и последняя секции позволят включать бесконечно малые (первая) и бесконечно большие (последняя) значения. Для секций должны быть заданы ограничения целостности CHECK для реализации Ваших бизнес-правил и обеспечения целостности данных (т.е. ограничения области данных конечным, а не бесконечным диапазоном). Диапазонные секции идеальны, когда обслуживание и администрирование требуют архивирования больших диапазонов данных на регулярной основе, и когда запросы обращаются к большим массивам данных - но только в пределах нескольких диапазонов.
Секционирование индекса
Секционировать можно не только данные, но и индексы. Индексы можно секционировать с помощью той же функции, что и базовую таблицу, либо любой другой. Однако с точки зрения производительности лучше всего разделять таблицу и ее индексы, используя одну и ту же функцию. Если таблица и индексы используют одну и ту же функцию секционирования, и секционируются по одним и тем же столбцам (в том же порядке), такая таблица и индексы называются выровненными. Если индекс создается по уже секционированной таблице, SQL Server автоматически выровняет новый индекс согласно схеме секционирования таблицы, если только индекс явно не секционирован по-другому. Если таблица и ее индексы выровнены, тогда SQL Server может перемещать секции внутри секционированных таблиц более эффективно, поскольку все связанные данные и индексы разделены по одному алгоритму.
Если таблица и индексы определены не только по одной и той же функции, но и по одной и той же схеме, тогда они называются выровненным хранилищем. Вообще, если таблицы и индексы располагаются в одном файле или файловой группе, мультипроцессорные системы могут получать дополнительные преимущества за счет параллельной работы с секциями. В мультипроцессорных системах в случае выровненных хранилищ SQL Server может заставить каждый процессор работать с определенным файлом или файловой группой, и при этом быть уверенным, что не будет конфликтов при доступе к данным, поскольку все необходимые для объединения или поиска по индексу данные будут локализованы на одном и том же диске. Это позволяет большему количеству процессов выполняться параллельно без прерываний.
Для получения дополнительной информацией по теме вы можете обратиться к разделу BOL: Special Guidelines for Partitioned Indexes.
Особые режимы секционирования - Split, Merge и Switch
В SQL Server 2005 для помощи в управлении секционированными таблицами введено несколько новых понятий. Поскольку секционирование применяется к большим таблицам для обеспечения масштабируемости и поддержания лучшей производительности этих таблиц, весьма вероятно, что количество секций, выбранное в момент создания функции секционирования, со временем изменится. Используя оператор ALTER TABLE с новой опцией "split" ("расщепление"), Вы можете добавить в таблицу новую секцию. Когда секция "расщепляется", часть данных может быть перенесена в новую секцию, однако это не лучшее решение с точки зрения производительности. Ниже в этой статье будет приведен полный сценарий работы режима "split".
Напротив, если необходимо удалить секцию используются режимы "switch" ("переключение") и "merge" ("слияние"). "Слияние" диапазонных секций выглядит как указание граничной точки, которую необходимо удалить. Если работа ведется только с данными определенного периода и регулярно проводится архивирование базы данных (например, ежемесячно), вы могли бы архивировать одну секцию данных (самый ранний месяц) после добавления новой секции. Например, Вы могли бы пожелать иметь доступ к данным только одного последнего года; тогда в конце каждого месяца вы бы "включали" новый (текущий) месяц, и затем "выключали" бы самый ранний месяц, таким образом, проводя различия между read/write OLTP данными текущего месяца и read-only данными предыдущих месяцев, предназначенными для анализа. Однако, прежде чем Вы объедините граничную точку Вы должны выключить ее (связанные с ней) ассоциированные данные, иначе слияние может стать дорогостоящей операцией. Существует специальная методика, позволяющая добиться при этом наибольшей эффективности. Понятия "switch", "merge" и "split" только на первый взгляд несколько сложными.
В данном сценарии у вас есть read-only доступ к таблице с данным за последний год. В настоящее время в этой таблице содержатся данные с сентября 2003 по август 2004. Данные за текущий месяц, сентябрь 2004, находится в другой базе данных, оптимизированной для производительности OLTP (операций реального времени). В read-only версии таблицы имеется 13 секции: двенадцать секций, которые содержит данные (сентябрь 2003 - августа 2004), и одна последняя пустая секция (помните, что диапазонные секции включают весь диапазон значений - от бесконечно малого до бесконечно большого). Таблица могла бы содержать определение CHECK constraint, для того чтобы ограничить OrderDate значениями с 1 сентября 2003 по 1 сентября 2004; данное ограничение позволит эффективно держать последнюю секцию пустой.
Рисунок 5: Границы диапазонных секций - перед загрузкой данных/архивацией
Когда начинается октябрь (в базе данных OLTP), необходимо переместить данные сентября в секционированную таблицу, используемую для анализа. Включение/выключение секций в таблицу - очень быстрый процесс, к тому же, вся подготовительная работа может быть выполнена из-за пределов секционированной таблицы. Этот сценарий в подробностях описывается в учебном примере, который будет рассмотрен чуть ниже. Его основная идея заключается в использовании "каскадных таблиц", которые в конечном счете станут секциями в секционированной таблице ("включение" таблицы, становящейся секцией в секционированной таблице) или будут хранить устаревшие данные таблицы ("выключение" секции, становящейся автономной таблицей).
На Рисунке 6 показано "выключение" секции, становящейся отдельной не секционированной таблицей в той же файловой группе, что и основная таблица. Поскольку эта "несекционированная таблица" уже существует внутри той же файловой группы (и это является очень важным моментом), SQL Server может реализовать это "переключение" как простое изменение метаданных. Выключение секции происходит за считанные секунды, поскольку SQL Server должен всего-навсего изменить метаданные, в отличие от удаления, которое могло бы занять часы и создать блокировки на больших таблицах. Как только эта секция "выключена", у вас все еще будет оставаться 13 секций: первая (самая старая) секция теперь пуста и последняя (самая свежая, также пустая) секция должна быть теперь "расщеплена".
Рисунок 6: Выключение секции
Для того чтобы удалить первую (старшую) секцию (сентябрь 2003), необходимо воспользоваться новой опцией "слияния" оператора ALTER TABLE - "merge" (см. Рисунок 7). "Сливая" секции, вы фактически удаляете граничную точку между ними, и, следовательно, устраняете одну из секций (в данном случае сокращая количество секций до 12). Слияние секции будет очень быстрой операцией в том случае, если никакие строки данных не должны быть перемещены; в нашем случае, поскольку первая секция пуста, никакие записи не перемещаются (из первой секции во вторую). Если бы первая секция НЕ была пуста, то при слиянии данные пришлось бы перемещать из первой секции во вторую, и это было бы очень дорогостоящей операцией. Впрочем, в весьма распространенном сценарии "sliding window" ("Скользящее Окно") это никогда не должно произойти, поскольку Вы всегда будете объединять пустую секцию с активной секцией, и соответственно никакие записи перемещаться не будут.
Рисунок 7: Слияние секций
И наконец случай, когда новая таблица должна быть включена в секционированную таблицу. Помните, что для того чтобы осуществить включение секции в секционированную таблицу путем простого изменения метаданных, вся работа по загрузке данных и созданию индексов уже должна быть произведена в новой таблице - вне границ секционированной таблицы. Для включения новой секции в таблицу сначала необходимо "расщепить" последний (самый новый, пустой) диапазон на две секции. Кроме того, вам необходимо перестроить ограничение целостности таблицы под требования нового диапазона. Секционированная таблица будет включать 13 секций. Последняя секция в сценарии "Скользящее Окно" с LEFT-функцией секционирования будет всегда оставаться пустой.
Рисунок 8: Расщепление Секции
И наконец, недавно загруженные данные включаются в 12-ую секцию - в качестве данных за сентябрь 2004.
Рисунок 9: Включение Секции
В результате мы получаем таблицу такого вида:
Рисунок 10: Границы диапазонных секций - после загрузки данных/архивации
Важно знать, что split и merge - это атрибуты оператора ALTER PARTITION FUNCTION, а switch - это атрибут оператора ALTER TABLE. Кроме того, только одна секция может быть добавлена или удалена единовременно. Если в диапазонную секционированную таблицу требуется добавить или удалить сразу несколько секции, то тогда наиболее оптимальным способом балансировки данных (вместо балансировки целой таблицы для каждого расщепления) может послужить создание новой секционированной таблицы - использование новой функции и схемы секционирования и затем перемещение данных в новую секционированную таблицу. Для "перемещения" данных сначала скопируйте их, используя оператор INSERT newtable SELECT … FROM oldtable, а затем удалите исходные таблицы. Будьте осторожны, не потеряйте данные, предотвращая пользовательские (или любые другие) модификации во время этого процесса.
Более полную информацию вы сможете найти в BOL, в разделах ALTER PARTITION FUNCTION and ALTER TABLE.
Шаги по созданию секционированных таблиц
Теперь, когда у Вас уже сложилось представление о том, для чего создаются и что предлагают секционированные таблицы, неплохо было бы проследить всю логическую цепочку процесса создания секционированной таблицы (см. Рисунок 11).
Рисунок 11: Шаги по Созданию Секционированной Таблицы или Индекса
Определите, НУЖНО ЛИ секционировать объект
Как уже описано выше - это первый и самый важный шаг. Не каждая таблица нуждается в секционировании. Не смотря на то, что секционирование прозрачно с точки зрения приложения, оно усложняет администрирование и реализацию ваших объектов. В то время как секционирование может предложить значительные преимущества, Вы наверняка не станете к нему прибегать для маленьких таблиц. Так что же считать большим, а что маленьким? Ваши требования к производительности и обслуживанию, равно как и текущее состояние этих показателей - вот факторы, определяющие потребность в секционировании.
К примеру, архивирование диапазона данных отдельной таблицы продаж занимает существенное время - минуты (или более) при добавлении новых записей (с помощью INSERT … SELECT) или удалении старых данных (с помощью DELETE … WHERE). Если эти операции создают излишнюю нагрузку на системные ресурсы и производительность, тогда вам стоит рассмотреть возможность секционирования, в противном случае вы можете просто усложнить администрирование без существенной выгоды для себя.
Определите ключ секционирования и количество секций
Если Вы пытаетесь улучшить производительность и управляемость больших подмножеств данных, и существуют определенные модели доступа к ним, используйте механизм диапазонного секционирования. Если ваши данные содержат логические группировки, и Вы часто работаете с только несколькими из этих определенных подмножеств одновременно, тогда определяйте свои диапазоны таким образом, чтобы запросы обращались только к подходящим данным (т.е. секциям).
За более подробной информацией вы можете обратиться к разделу BOL: Designing Partitioned Tables and Indexes.
Определите, СТОИТ ЛИ использовать несколько файловых групп
В целях улучшения производительности и упрощения обслуживания следует использовать файловые группы (filegroups) для разделения данных. Количество файловых групп частично продиктовано аппаратными ресурсами, находящимися в вашем распоряжении; наверняка вам захочется иметь такое же количество файловых групп, что и количество секций, и предпочтительно эти файловые группы будут располагаться на разных дисках. Однако, это в первую очередь относится к системам, где анализ имеет тенденцию быть проводимым по всему набору данных. Если в вашем распоряжении находится мультипроцессорная система, SQL Server может работать с несколькими секциями параллельно и поэтому значительно сокращать общую продолжительность обработки больших и сложных отчетов и аналитических данных. В этом случае, Вы можете получать выигрыш в производительности при параллельной обработке данных, а так же при переключении секций в секционированной таблице.
Создайте файловые группы
Если Вы хотите разместить секционированную таблицу в нескольких файлах для улучшения сбалансированности подсистемы ввода/вывода, тогда вам следует создать файловую(ые) группу(ы). Файловые группы могут состоять из одного или более файлов, и каждая секция должна отображаться на файловую группу. Одна файловая группа может использоваться несколькими секциями, однако для лучшего управления данными, например, для большей гранулированности резервного копирования, вы должны разрабатывать ваши секционированные таблицы продуманно, так чтобы только связанные или логически сгруппированные данные размещались в одной и той же файловой группе. Используя оператор ALTER DATABASE, Вы добавляете логическое имя файловой группы - той, к которой будут добавлены файлы. Чтобы создать файловую группу с именем "2003Q3" для учебной базы данных AdventureWorks используйте следующий оператор:
|
После того как вы создадите файловую группу, используйте оператор ALTER DATABASE чтобы добавить файлы в файловую группу.
|
Таблица создается в файле(ах), определяющем файловую группу; ее местоположение задается в разделе ON оператора CREATE TABLE. Однако без секционирования таблицу нельзя разместить в нескольких файловых группах. Чтобы создать таблицу на основе одной файловой группы, вы используете выражение ON оператора CREATE TABLE. Чтобы создать секционированную таблицу, Вы сначала должны определиться с логикой секционирования. Даже притом, что вы можете определять логические секции для конкретной отдельно взятой таблицы, SQL Server позволяет Вам повторно использовать определение секций и для других объектов. Чтобы отделить понятие "секция" от понятия "таблица" Вы создаете структуру секций посредством функции секционирования. Поэтому первый шаг в секционировании таблицы состоит в создании функции секционирования.
CREATE PARTITION FUNCTION для диапазонных секций
Диапазонные секции должны быть определены с граничными условиями. Кроме того, все границы должны быть включены; функция диапазонного секционирования должна включать все значения даже притом, что диапазон значений таблицы может быть (и должен быть) более ограниченным (посредством CHECK constraint). Никакие значения (с любого конца диапазона) не должны быть исключены. Кроме того, поскольку данные, вероятно, будут добавляться и удаляться из секции, вам потребуется последняя пустая секция, которую вы сможете постоянно "расщеплять", выделяя тем самым место для новой секции данных. Эта последняя секция будет всегда оставаться пустой, находясь в ожидании новых записей, которые вы будете периодически в нее включать.
При диапазонном секционировании вначале определяют граничные точки. Если Вы определяете пять секции, то потребуются только четыре граничные точки. Для того чтобы разделить данные на пять групп, Вы определяете четыре граничных значения для секций, а затем определяете, какой из секций будет принадлежать каждое из этих значений: первой (LEFT) или второй (RIGHT).
У Вас всегда будет одна секция (крайне правая для LEFT-функции, или крайне левая для RIGHT-функции), у которой не будет явно заданной граничной точки. Это объяснение может показаться несколько запутывающим, однако помните, что функция секционирования включает все значения данных (- бесконечность слева и бесконечность справа).
Определяя режим LEFT или RIGHT, Вы определяете, является ли граничное значение верхней границей первой секции (LEFT) или нижней границей второй секции (RIGHT). Другими словами, если первое значение (граничное условие) функции секционирования будет '20001001', тогда значения в пределах соседствующих секций распределятся так:
Для LEFT:
1-ая секция - диапазон данных <= '20001001'
2-ая секция - диапазон данных > '20001001'
Для RIGHT:
1-ая секция - диапазон данных < '20001001'
2-ая секция - диапазон данных => '20001001'
Поскольку Ваши диапазонные секции наверняка будут определяться по столбцам с типом данных datetime, то вам следует знать об импликациях (implication - "вовлечение").
Примечание : Импликация (от лат . implico - тесно связываю) - приблизительный логический эквивалент оборота "если..., то..."; операция, формализующая логические свойства этого оборота.
Применяя тип данных datetime, Вы всегда используете ОДНОВРЕМЕННО и дату и время. Дата без определенного значения времени подразумевает нулевое время - 12:00am. Если, к примеру, LEFT-функция базируется на этом типе данных, то тогда данные за 1 октября 12:00am попадут в 1-ую секцию, а остальная часть октября - во 2-ую. По логике, лучше всего использовать начальные значения (набора данных второй секции) с RIGHT-функцией и конечные значения (набора данных первой секции) с LEFT-функцией. Три следующих выражения создают логически идентичные структуры секционирования:
|
Примечание : Использование типа данных datetime добавляет сложности, поскольку Вы должны будете удостовериться в том, что установили правильные граничные значения. В случае с RIGHT-функцией все предельно просто, т.к. время по умолчанию равняется 12:00:00.000am. Для LEFT дополнительная сложность обусловлена точностью типа данных datetime. Причина, по которой вы ДОЛЖНЫ выбирать в качестве граничного значения 23:59:59.997, состоит в том, что тип данных datetime не гарантирует точность в 1 миллисекунду. Вместо этого, datetime-данные абсолютно точны в пределах 3.33 миллисекунд. Значение такта таймера процессора (tick) равное 23:59:59.999 не доступно для SQL Server, вместо этого значение округляется до ближайшего такта, которым является 12:00:00.000am следующего дня. Из-за такого округления границы могут быть неверно определены. Проявляйте осмотрительность при задании значений в миллисекундах для типа данных datetime.
Примечание : Функции секционирования также позволяют в качестве определения использовать другие функции. Вы можете использовать функцию DATEADD (ms,-3, '20010101') вместо явного определения '20001231 23:59:59.997'.
За дополнительной информацией обратитесь к разделу BOL: "Date and Time" in the Transact-SQL Reference.
Для того чтобы хранить по одной четверти данных таблицы Orders в четырех активных секциях (представляющих календарные кварталы) и иметь пятую секцию для последующего использования (в качестве полигона для добавления/исключения данных из секционированной таблицы), используйте такую LEFT-функцию секционирования с четырьмя граничными условиями:
|
Помните, что четыре граничные точки создают 5 секции - с одной пустой секцией справа, если функция секционирования определена как LEFT, и одной пустой секцией слева, если функция определена как RIGHT. Посмотрите какие наборы данных создаются этой функцией секционирования:
Граничная точка '20000930 23:59:59.997' LEFT-ФУНКЦИИ (задает модель):
КРАЙНЕ ЛЕВАЯ секция будет включать все значения <= '20000930 23:59:59.997'
Граничная точка '20001231 23:59:59.997':
ВТОРАЯ секция будет включать все значения > '20000930 23:59:59.997' и <= '20001231 23:59:59.997'
Граничная точка '20010331 23:59:59.997':
ТРЕТЬЯ секция будет включать все значения > '20001231 23:59:59.997' и <= '20010331 23:59:59.997'
Граничная точка '20010630 23:59:59.997':
ЧЕТВЕРТАЯ секция будет включать все значения > '20010331 23:59:59.997' и <= 20010630 23:59:59.997'
Наконец, ПЯТАЯ секция будет включать все значения > '20010630 23:59:59.997'.
Независимо от модели (LEFT или RIGHT), функция диапазонного секционирования должна включать все значения: от бесконечно малого до бесконечно большого. Для функции LEFT последняя граничная точка определит последнее абсолютное значение секций, но поскольку функция должна охватывать всю область данных, то для значений, больших чем значение последней граничной точки, должна существовать заключительная секция. При использовании LEFT-функций всегда будет существовать одна дополнительная секция в конце, и одна дополнительная секция вначале - для RIGHT-функций.
CREATE PARTITION SCHEME
Как только Вы создали функцию секционирования, Вы должны связать ее со схемой секционирования, для того чтобы адресовать секции определенным файловым группам. Когда Вы определяете схему секционирования убедитесь, что Вы определили файловые группы для КАЖДОЙ секции, даже если несколько секций будут располагаться в одной и той же файловой группе. Созданная ранее функция OrderDateRangePFN формирует 5 секций, последняя (пустая) будет располагаться в файловой группе PRIMARY. Нет никакой необходимости в особом размещении этой секции, поскольку она никогда не будет содержать данных.
|
Примечание: Если все секции должны располагаться в одной и той же файловой группе, то в этом случае можно применять упрощенный синтаксис:
|
Создайте секционированную таблицу
Теперь, когда логическая (функция секционирования) и физическая (схема секционирования) структуры определены, таблица может быть секционирована. Таблица определяет, какая "схема" должна использоваться, а схема определяет функцию. Для того чтобы связывать эти три понятия вместе, Вы должны определить столбец(ы) секционирования. Диапазонные секции всегда отображаются исключительно на один столбец таблицы, совместимый с типом данных граничных условий, определенных в функции секционирования. Кроме того, если в таблице необходимо специально ограничивать интервал допустимых значений (а не [- ;+ ]), то следует добавить ограничение целостности (check constraint).
|
Создайте индексы: секционированные либо обычные
По умолчанию индексы, создающиеся в секционированной таблице, будут использовать ту же самую схему секционирования и столбец секционирования, что и таблица. В этом случае индекс будет "выровнен" по отношению к таблице. Выравнивание не является обязательным условием, тем не менее, зачастую желательно, чтобы таблица и индексы были выровнены. Выравнивание таблицы и ее индексов обеспечивает более легкое управление и администрирование, особенно при работе по сценарию "скользящего окна". Не смотря на это, индексы не обязательно должны быть выровнены. Индексы могут быть основаны на других функциях секционирования или не быть секционированы вовсе.
Например, в случае, когда создаются уникальные индексы, столбец секционирования должен быть одним из ключевых столбцов; это будет гарантировать то, что для обеспечения уникальности потребуется проверить только подходящую секцию. Поэтому, если Вам необходимо секционировать таблицу по одному столбцу, а создать уникальный индекс по другому столбцу, то тогда они не смогут быть выровнены; индекс может быть либо секционирован по уникальному столбцу (если уникальный ключ основан на нескольких столбцах, то тогда это может быть любой из ключевых столбцов), либо вообще не секционирован. Запомните, что этот индекс должен быть удален и создан заново, если какие-то данные "включаются" или "исключаются" из секционированной таблицы.
Примечание : Если Вы планируете загрузить таблицу c существующими данными и затем добавить к ней индексы, то чаще всего наиболее эффективным способом будет загрузка данных в НЕ секционированную, НЕ индексированную таблицу с последующим созданием индексов и секционированием таблицы. Основывая кластерный индекс на схеме секционирования, вы тем самым эффективно секционируете таблицу. Это прекрасный способ секционирования таблиц. Для того чтобы создать таблицу как НЕ секционированную, а кластерный индекс как секционированный кластерный индекс, замените определение ON оператора CREATE TABLE на одну-единственную файловую группу и затем, после того как данные будут загружены, создайте кластерный индекс на основе схемы секционирования.
Соберем все воедино: конкретные примеры
После знакомства с концепцией, преимуществами и небольшими фрагментами кода, связанного с секционированием, у вас уже наверное сложилось достаточное понимание процесса секционирования. Однако для каждого из шагов доступны специфичные параметры настройки и опции, которые в определенных случаях должны удовлетворять разнообразным критериям. Следующая часть статьи поможет Вам соединить все полученные Вами знания воедино.
Секционирование диапазона - сведения о продажах
Характер использования сведений о продажах зачастую изменчив. Как правило, данные текущего месяца - это оперативные данные; данные предшествующих месяцев - это в большой степени данные, предназначенные для анализа. Чаще всего анализ производится ежемесячно, ежеквартально, либо ежегодно. Поскольку разным аналитикам могут потребоваться значительные объемы различных аналитических данных одновременно, то секционирование лучше всего позволит изолировать их деятельность. В рассматриваемом далее сценарии данные стекаются из 283 узлов и поставляются в виде двух файлов стандартного формата ASCII. Все файлы отправляются на центральный файл-сервер не позднее 3.00 am первого дня каждого месяца. Размеры файлов колеблются, но в среднем составляют примерно 86.000 заказов в месяц. Каждый заказ в среднем составляет 2.63 позиции, поэтому файлы OrderDetails составляют в среднем по 226180 строк. Каждый месяц добавляется примерно 25 миллионов новых заказов и 64 миллиона строк номенклатуры заказов. Сервер анализа истории поддерживает данные за 2 последних года. Данные за два года - это чуть меньше 600 миллионов заказов и более 1.5 миллиардов строк в таблице OrderDetails. Поскольку данные часто анализируются путем сравнения показателей месяцев одного и того же квартала, либо одних и тех же месяцев за предыдущие годы, то выбрано диапазонное секционирование. В качестве размера диапазона выбран месяц.
На основе схемы 11 ("Шаги по созданию секционированной таблицы") мы решили секционировать таблицу, используя диапазонное секционирование по столбцу OrderDate. Наши аналитики в основном объединяют и анализируют данные последних 6 месяцев, либо последних 3 месяцев текущего и прошлого годов (например, январь-март 2003 плюс январь-март 2004). Чтобы максимально усилить расслоение дисков, а заодно изолировать большинство группировок данных, на одном физическом диске будет располагаться по несколько файловых групп, но они будут смещены на шесть месяцев за тем, чтобы уменьшить количество конфликтов при разделении ресурсов. Текущий месяц - октябрь 2004, и все 283 обособленных офисов управляют своими текущими продажами локально. Сервер хранит данные с октября 2002 по сентябрь 2004 включительно. Для того чтобы воспользоваться преимуществом новой 16-процессорной системы и SAN (Storage Area Network - высокоскоростная сеть, связывающая хранилища данных), данные каждого месяца будут находиться в своем собственном файле файловой группы, и располагаться на наборе чередующихся зеркал (RAID 1+0). Рисунок 12 иллюстрирует размещение данных на логических дисках.
Рисунок 12: Секционированная таблица Orders
Каждый из 12 логических дисков использует конфигурацию RAID 1+0, поэтому общее количество дисков, необходимое для таблиц Orders и OrderDetails, равно 48. Не смотря на это, SAN поддерживает до 78 дисков, так что остальные 30 дисков используются для transaction log, TempDB, системных баз данных и прочих небольших таблиц, таких как Customers (9 миллионов записей) и Products (386 750 записей), и т.д. Таблицы Orders и OrderDetails будут использовать одни и те же граничные условия и одно и то же размещение на диске; фактически, они будут использовать одну и ту же схему секционирования. В результате (взгляните на два логических диска E:\ и F:\ на Рисунке 13) данные таблиц Orders и OrderDetails за одни и те же месяцы будут располагаться на одних и тех же дисках:
Рисунок 13: Размещение экстентов диапазонных секций на дисковых массивах
Хотя это и выглядит запутанным, все это весьма просто реализовать. Самое сложное в создании нашей секционированной таблицы - это доставка данных из большого количества источников - 283 хранилища должны иметь стандартный механизм доставки. Тем не менее, на центральном сервере есть только одна таблица Orders и одна таблица OrderDetails. Чтобы превратить обе таблицы в секционированные, мы должны сначала создать функцию и схему секционирования. Схема секционирования определяет физическое расположение секций на дисках, таким образом, файловые группы также должны существовать. Поскольку для наших таблиц необходимы файловые группы, то следующим шагом является их создание. Синтаксис операторов создания каждой файловой группы идентичен приведенному ниже, тем не менее, данным образом должны быть созданы все двадцать четыре файловые группы.Вы можете поменять названия/расположения дисков на один-единственный диск, для того чтобы протестировать и изучить синтаксис. Убедитесь, что Вы исправили размеры файла на MB вместо GB, и выбрали меньший начальный размер файлов, исходя из доступного вам дискового пространства. Двадцать четыре файла и файловые группы будут созданы в базе данных SalesDB. Все будут иметь схожий синтаксис, за исключением местоположения, имени файла и имени файловой группы:
|
Как только все двадцать четыре файла и файловые группы будут созданы, Вы сможете определить функцию и схему секционирования. Убедиться в том, что ваши файлы и файловые группы созданы, вы можете при помощи системных хранимых процедур sp_helpfile и sp_helpfilegroup.
Функция секции будет определена по столбцу OrderDate с типом данных datetime. Для того чтобы обе таблицы можно было секционировать по столбцу OrderDate, этот столбец должен присутствовать в обеих таблицах. В действительности, значения ключей секционирования обоих таблиц (если обе таблицы будут секционированы по одному и тому же ключу) будут дублировать друг друга; однако это необходимо для получения преимуществ выравнивания, к тому же в большинстве случаев размер ключевых столбцов будет относительно небольшим (размер поля datetime всего 8 байт). Как уже описывалось в Главе "CREATE PARTITION FUNCTION для диапазонных секций", наша функция будет диапазонной функцией секционирования, у которой первое граничное условие будет в первой (LEFT) секции.
|
Поскольку и крайне левый, и крайне правый граничные случаи охвачены, эта функция секционирования фактически создает 25 секции. Таблица будет поддерживать 25-ую секцию, которая останется пустой. Для этой пустой секции не требуется никакой специальной файловой группы, поскольку никакие данные не должны когда-либо в нее попасть. Для того чтобы гарантировать, что никакие данные в нее не попадут, constraint ограничит диапазон данных этой таблицы. Для того чтобы направить данные на соответствующие диски используется схема секционирования, отображающая секции на файловые группы. Схема секционирования будет использовать явное определение файловых групп для каждой из 24 файловых групп, содержащих данные, и PRIMARY - для 25-ой пустой секции.
|
Таблица может быть создана с тем же синтаксисом, который поддерживали предыдущие релизы SQL Server - используя предложенную по умолчанию, либо определенную пользователем файловую группу (для создания НЕ секционированной таблицы) - либо используя схему (для создания секционированной таблицы). Что касается того, какой из вариантов предпочтительнее (даже если эта таблица в будущем станет секционированной), то все зависит от того, как таблица будет заполняться и сколькими секциями вы собираетесь манипулировать. Наполнение кучи (heap) и последующее создание в ней кластерного индекса, вероятно, обеспечит лучшую производительность, чем загрузка в таблицу, содержащую кластерный индекс. Кроме того, в мультипроцессорных системах вы можете загружать данные в таблицу параллельно, и затем тоже параллельно строить индексы. В качестве примера создадим таблицу Orders и загрузим в нее данные, используя операторы INSERT … SELECT. Чтобы создать таблицу Orders в качестве секционированной, определите схему секционирования в выражении ON оператора CREATE TABLE.
|
Поскольку таблица OrderDetails собирается использовать ту же схему, она должна включать в себя столбец OrderDate.
|
На следующем шаге в таблицы загружаются данные из новой учебной базы данных AdventureWorks. Убедитесь, что вы установили базу данных AdventureWorks.
|
Теперь, когда вы загрузили данные в секционированную таблицу, Вы можете воспользоваться новой встроенной системной функцией для того чтобы определить секцию, на которой будут располагаться данные. Следующий запрос для каждой из содержащих данные секций возвращает информацию о том, сколько строк содержится в каждой из секций, а также минимальное и максимальное значения поля OrderDate. Секция, которая не содержит строк, не попадет в итоговый результат.
|
И наконец теперь, после того как вы загрузили данные, Вы можете создать кластерный индекс и внешний ключ (Foreign key) между таблицами OrderDetails и Orders. В данном случае кластерный индекс будет построен на первичном ключе (Primary Key) точно так же, как вы идентифицируете обе эти таблицы по их ключу секционирования (для OrderDetails к индексу Вы добавите столбец LineNumber для уникальности). По умолчанию при построении индексов на секционированной таблице происходит их выравнивание по отношению к секционированной таблице согласно той же самой схеме секционирования; явно задавать схему не обязательно.
|
Полный синтаксис, определяющий схему секционирования, выглядел бы так:
|
Объединение секционированных таблиц
Когда объединяются выровненные таблицы, SQL Server 2005 может выбирать между объединением таблиц за один шаг, либо в несколько этапов, когда вначале объединяются отдельные секции, а затем подмножества складываются. Но независимо от того, как объединяются секции, SQL Server всегда пытается оценить, насколько возможно исключить из объединения какие-нибудь секции.
Исключение секций
В следующем примере данные запрашиваются из таблиц Order и OrderDetails, созданных в предыдущем сценарии. Запрос возвращает данные о продажах только за третий квартал. Как правило, в третьем квартале потребительский спрос на товары невысок; однако в третьем квартале 2004 года, напротив, был зафиксирован один из самых высоких уровней объема продаж. В данном случае нам интересно, была ли (в третьем квартале) какая-либо взаимосвязь между объемами заказов и датами продаж. Для того чтобы гарантировать, что выровненные секционированные таблицы при объединении извлекают выгоду из исключения секций, Вы должны убедиться, что установили диапазоны секционирования для каждой из таблиц. В данном случае, поскольку первичный ключ (Primary Key) таблицы Orders является составным (OrderDate + OrderID), объединение между таблицами Order и OrderDetails должно производиться не только по равенству OrderID, но и по равенству дат. SARG (Search Argument - Аргумент Поиска) будет применен к обеим секционированным таблицам. В итоге, наш запрос будет выглядеть следующим образом:
|
На Рисунке 14 представлено несколько ключевых моментов, на которые стоит обратить внимание, изучая реальный или расчетный планы исполнения. Для этого нам придется воспользоваться SQL Server Management Studio. Обратите внимание на значения "Estimated Number of Executions" (расчетное количество выполнений) или "Number of Executions" (количество выполнений) для обеих таблиц. В нашем случае, мы рассматриваем один квартал, т.е. три месяца. Данные за каждый месяц располагаются в своей собственной секции, и поэтому поиск данных выполняется трижды - по одному разу для каждой таблицы.
Рисунок 14: Количество выполнений
Как можно увидеть из Рисунка 15, SQL Server устраняет все лишние секции и оставляет только те, которые содержат необходимые данные, для чего анализирует PARTITION ID:([PtnIds1017]). Вы можете задаться вопросом, откуда взялось выражение PtnIds1017? Если вы обратите внимание на значок "Constant Scan" в верхней части плана исполнения, то увидите что в списке аргументов у него VALUES(((21)), ((22)), ((23))). Это не что иное, как номера секций.
Рисунок 15: Количество выполнений
Для того чтобы проверить, что данные располагаются в тех (и только в тех) секциях, вы можете воспользоваться слегка модифицированной версией созданного ранее запроса, а заодно обратиться к новым встроенным системным функциям для работы с секциями.
|
Из Рисунков 14 и 15 видно как происходит исключение секций. Если секционированные таблицы и индексы выровнены по отношению к таблицам, с которыми объединяются, то могут использоваться и другие методики оптимизации. SQL Server может выполнить "множественные" объединения, объединив вначале все секции.
Предварительное объединение выровненных таблиц
В приведенном ранее запросе SQL Server не только исключает лишние секции, но также производит объединение между оставшимися секциями - по отдельности. Помимо наблюдения за количеством обращений к таблицам во время исполнения запроса вы также должны были обратить внимание на значок "merge join". Если вы присмотритесь, то увидите, что объединение слиянием (merge join) также выполняется три раза.
Рисунок 16: Объединение секционированных таблиц
Обратите внимание, на Рисунке 16 представлен еще один элемент - "nested loops" (объединение "вложенных циклов"). Может показаться, будто это объединение выполняется после объединения слиянием, но на самом деле оказывается, что это объединение является избыточным. Идентификаторы секций к тому моменту уже были переданы для поиска в каждую из таблиц, и это заключительное объединение только сводит вместе две порции данных, удостоверяя, что каждая из них придерживается идентификатора секции, объявленного в начале (в выражении "Constant Scan").
Сценарий "Скользящее окно"
Как только станут доступными данные следующего месяца (в нашем случае это Октябрь 2004), Вам потребуется руководствоваться определенной последовательностью действий, для того чтобы используя существующие файловые группы "двигать" ("включать"/"выключать") в них данные. По данному сценарию в файловой группе FG1 в настоящее время находятся данные за октябрь 2002. Теперь, когда появились данные за Октябрь 2004, у Вас есть два пути, в зависимости от доступного Вам дискового пространства и требований к архивации. Помните, чтобы "включение" или "выключение" секции из таблицы прошло быстро, оно должно затрагивать ИСКЛЮЧИТЕЛЬНО метаданные. Другими словами, новая таблица (источник или целевая - т.е. скрытая секция) должна быть создана в переключаемой файловой группе. Если Вы планируете продолжать использовать те же самые файловые группы, в данном случае FG1, то тогда Вы должны будете решить, как управлять дисковым пространством и требованиями к архивации. Если у Вас достаточно места на диске, то Вы можете загрузить текущие данные (октябрь 2004) в FG1, не удаляя данных, которые будут заархивированы (октябрь 2002).
Однако если у вас недостаточно дискового пространства для того, чтобы хранить данные за текущий месяц и месяц, который подлежит архивации, тогда вам придется сначала "выключить" старую секцию.
Вероятно, что Вы уже проводили архивацию. Хорошая практика архивирования заключается в том, чтобы создавать резервную копию файловой группы сразу же после того, как новая секция будет загружена и "включена" (а не перед "выключением"). Причина в том, что если на RAID-массиве вдруг произойдет сбой, файловую группу можно будет восстановить (restore) вместо того, чтобы перестраивать/перегружать данные (rebuild/reload). В нашем конкретном случае, поскольку база данных буквально недавно была секционирована, вы, вероятно, уже выполнили полное резервное копирование (full backup) после того, как структура секционирования была стабилизирована (полное резервное копирование базы данных - это не единственное решение; существует большое число различного рода стратегий резервного копирования, которые могут быть осуществлены в SQL Server 2005, а также предложений большей степени детализации резервного копирования и восстановления). Поскольку большая часть данных никогда не изменяется, вы можете архивировать только отдельные файловые группы после того, как они будут загружены. Фактически, это должно стать частью вашей стратегии секционирования. За дополнительной информацией по данной теме обратитесь к разделу BOL: "File and Filegroup Backups" in Administering SQL Server.
Теперь, когда стратегия выбрана, Вам необходимо разобраться в деталях процесса и синтаксиса. Возможно что-то вам покажется сложным. Тем не менее, полный цикл процесса будет идентичным для каждого месяца. Все что будет меняться, так это только даты. Используя динамический SQL, Вы можете легко автоматизировать весь процесс. Вот основные шаги:
- Управление "включаемой" каскадной таблицей
- Управление второй каскадной таблицей ("выключаемой")
- Исключение старых данных из секционированной таблицы и добавление новых
- Удаление каскадных таблиц
- Резервное копирование файловой группы
Рассмотрим каждый из шагов более детально. Дополнительно каждый шаг содержит примечания, помогающие автоматизировать процесс с помощью динамического SQL.
Управление "включаемой" каскадной таблицей
- Создайте каскадную таблицу - эта таблица является будущей скрытой секцией. Она должна иметь ограничение целостности для того, чтобы ограничивать свои данные только допустимыми для будущей секции таблицы значениями. Возможно, из соображений производительности вы захотите загрузить данные в неиндексированную кучу (heap) безо всяких ограничений целостности и затем только добавить ограничение целостности (см. шаг 3) WITH CHECK перед включением таблицы в секционированную таблицу.
Автоматизация: эту таблицу будет легко создать, поскольку это всегда будет текущий или только что завершившийся месяц. Структура каскадной таблицы должна соответствовать структуре существующей таблицы, таким образом, основное изменение для каждого месяца будет касаться имени таблицы. Тем не менее, Вы можете использовать одно и то же имя для каскадной таблицы каждый месяц, поскольку она не должна существовать после того, как будет добавлена в секцию. Каскадная таблица все еще будет существовать после того, как вы загрузите данные в секционированную таблицу, но вы можете удалить ее, как только загрузка данных завершится. Кроме того, должен меняться диапазон дат. Поскольку Вы работаете с типом данных datetime, а у него существуют проблемы округления времени, Вы должны быть готовы программно определить нужное значение в миллисекундах. Самый простой способ вычислить верхнее граничное значение месяца - это взять месяц, с которым вы работаете, прибавить к нему 1 месяц, и затем отнять 2 или 3 миллисекунды. Вы не можете вычесть только 1 миллисекунду, поскольку 59.999 округлится до .000 - а это уже будет первым днем следующего месяца. Вы можете вычесть 2 или 3 миллисекунды, поскольку -2 миллисекунды округлят значение до .997, а -3 равняется .997; .997 - применимое значение, которое может быть сохранено. Следующий пример поможет вам получить корректное значение верхней границы вашего диапазона.CREATE TABLE SalesDB.[dbo].[OrdersOctober2004]
(
[OrderID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL,
[RevisionNumber] [tinyint] NULL,
[ModifiedDate] [datetime] NULL,
[ShipMethodID] [tinyint] NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NOT NULL,
[TotalDue] [money] NULL
) ON [FG1]
GO
Таблица должна будет пересоздаваться каждый месяц, поскольку она должна будет располагаться в файловой группе, данные которой постоянно переключаются. Чтобы определить файловую группу, с которой предстоит работать, используйте следующий запрос к системным таблицам, объединенный с известной уже функцией $partition. Подчеркнутые фрагменты кода Вы будете изменять непосредственно для вашей конкретной таблицы, функции секционирования и определенной даты.DECLARE @Month nchar(2),
@Year nchar(4),
@StagingDateRange nchar(10)
SELECT @Month = N'11', @Year = N'2004'
SELECT @StagingDateRange = @Year + @Month + N'01'
SELECT dateadd(ms, -2, @StagingDateRange)SELECT ps.name AS PSName,
dds.destination_id AS PartitionNumber,
fg.name AS FileGroupName
FROM (((sys.tables AS t
INNER JOIN sys.indexes AS i
ON (t.object_id = i.object_id))
INNER JOIN sys.partition_schemes AS ps
ON (i.data_space_id = ps.data_space_id))
INNER JOIN sys.destination_data_spaces AS dds
ON (ps.data_space_id = dds.partition_scheme_id))
INNER JOIN sys.filegroups AS fg
ON dds.data_space_id = fg.data_space_id
WHERE (t.name = 'Orders') AND (i.index_id IN (0,1)) AND
dds.destination_id = $partition.TwoYearDateRangePFN('20021001') - загрузите в каскадную таблицу данные. Если файлы согласованы, то тогда этот процесс следует поручить оператору BULK INSERT.
Автоматизация: этот процесс является самым сложным, для автоматизации. Вы должны будете удостовериться, что все файлы были загружены, кроме того, Вы можете захотеть загружать их параллельно. Для управления этим процессом Вы могли бы создать таблицу, которая сохраняла бы треки профайлера с информацией о том, какие файлы были загружены и где они располагаются. Вы могли бы создать job в SQL Agent, который проверял бы поступление файлов каждые несколько минут, забирал новые файлы, а затем выполнял бы множественные операторы bulk insert. - Как только данные загрузятся, Вы сможете добавить ограничение целостности. Для того чтобы данные считались "доверительными", ограничение целостности должен быть добавлено с опцией WITH CHECK. Опция WITH CHECK задается по умолчанию, поэтому ее не обязательно специально указывать, самое главное - не указать WITH NOCHECK.
ALTER TABLE SalesDB.[dbo].[OrdersOctober2004]
WITH CHECK
ADD CONSTRAINT OrdersRangeYearCK
CHECK ([OrderDate] >= '20041001'
AND [OrderDate] <= '20041031 23:59:59.997')
GO - Проиндексируйте каскадную таблицу - она должна иметь такой же кластерный индекс, как и таблица, в которую планируется добавить секцию.
Автоматизация: это - не сложный этап. Используя название месяца и информацию о файловой группе, полученную на шаге 1, Вы можете создать этот кластерный индекс.ALTER TABLE [OrdersOctober2004]
ADD CONSTRAINT OrdersOctober2004PK
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
ON [FG1]
GO
Управление второй каскадной таблицей ("выключаемой")
- Создайте вторую каскадную таблицу - это пустая таблица, которая будет хранить данные отключаемой секции.
CREATE TABLE SalesDB.[dbo].[OrdersOctober2002]
(
[OrderID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL,
[RevisionNumber] [tinyint] NULL,
[ModifiedDate] [datetime] NULL,
[ShipMethodID] [tinyint] NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NOT NULL,
[TotalDue] [money] NULL
) ON [FG1]
GO - Проиндексируйте каскадную таблицу - она должна иметь такой же кластерный индекс, как и таблица, в которую планируется добавить секцию (секция станет этой таблицей).
ALTER TABLE [OrdersOctober2002]
ADD CONSTRAINT OrdersOctober2002PK
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
ON [FG1]
GO
Исключите старые данные из секционированной таблицы и добавьте новые.
- Отделите старые данные от основной таблицы - переключите их во вторую каскадную таблицу.
ALTER TABLE Orders
SWITCH PARTITION 1
TO OrdersOctober2002
GO - Измените функцию секционирования для того, чтобы удалить граничную точку октября 2002.
ALTER PARTITION FUNCTION TwoYearDateRangePFN()
MERGE RANGE ('20021031 23:59:59.997')
GO - Следующий оператор удаляет ассоциацию между файловой группой и схемой секционирования. Другими словами, FG1 больше не является частью схемы секционирования. А поскольку вы будете размещать новые данные всё в тех же 24 секциях, то вам придется сделать FG1 "next used" секцией. Иначе говоря, это будет следующая используемая секция.
ALTER PARTITION SCHEME TwoYearDateRangePScheme
NEXT USED [FG1]
GO - Измените функцию секционирования для того, чтобы добавить новую граничную точку октября 2004.
ALTER PARTITION FUNCTION TwoYearDateRangePFN()
SPLIT RANGE ('20041031 23:59:59.997')
GO - Измените ограничение целостности базовой таблицы (если такое существует) для того, чтобы расширить диапазон данных. Поскольку добавление ограничения целостности может стать дорогостоящей операцией (из-за проверки данных), то в данном случае наилучшее решение - это растянуть диапазон дат вместо удаления и пересоздания ограничения целостности. Пока у нас есть только одно ограничение целостности (OrdersRangeYearCK), но на будущее мы создадим два.
ALTER TABLE Orders
ADD CONSTRAINT OrdersRangeMaxOctober2004
CHECK ([OrderDate] < '20041101')
GOALTER TABLE Orders
ADD CONSTRAINT OrdersRangeMinNovember2002
CHECK ([OrderDate] >= '20021101')
GOALTER TABLE Orders
DROP CONSTRAINT OrdersRangeYearCK
GO - "Включите" новые данные из первой каскадной таблицы.
ALTER TABLE OrdersOctober2004
SWITCH TO Orders PARTITION 24
GO
Удаление каскадной таблицы
Поскольку в следующем (и заключительном) шаге все данные архивируются, то каскадные таблицы нам больше не нужны. DROP TABLE - самый быстрый путь удалить эти таблицы.
|
Резервное копирование файловой группы
Выбор того, что архивировать в последнем шаге, зависит от вашей стратегии резервного копирования. Если вы выбрали стратегию, основанную на резервном копировании файла или файловой группы, то тогда должно быть выполнено резервное копирования файла или файловой группы. Если выбрана стратегия полного резервного копирования базы данных, то тогда должно быть выполнено полное или разностное резервное копирование.
|
Секционирование списка - данные из регионов
Если в вашей таблице собираются данные из нескольких регионов, а анализ часто производится в разрезе одного региона, или Вы получаете данные от каждого региона только время от времени, подумайте об использовании определенного рода "диапазонных" секций, имеющих форму списка. Иными словами, Ваша функция секционирования явно определит значение региона для каждой секции. В качестве примера рассмотрим испанскую компанию, клиенты которой находятся в Испании, Франции, Германии, Италии, и Великобритании. Сведения о продажах компании всегда анализируются в разрезе страны, поэтому таблицы удобно секционировать на пять частей, по одной для каждой страны.
Секционирование списка почти идентично секционированию диапазона дат за исключением того, что в данном случае в пределах секции не будет никакого другого (кроме определенного в функции) значения ключа секционирования. Тем не менее, граничные условия должны включать крайне левую и крайне правую точки. Для того чтобы создать 5 секций, Вам необходимо определить только 4 граничных точки в функции секционирования. Значения ключа секционирования упорядочивать не обязательно, однако для того чтобы корректно сформировать секции, Вам необходимо упорядочить значения ключа, а затем исключить из списка либо последнее значение (для LEFT-функции), либо первое (для RIGHT-функции).
Поскольку у нас имеется пять секций, то нам потребуется пять файловых групп. Давайте дадим им названия стран. В файле RegionalRangeCaseStudyFilegroups.sql представлен сценарий для нашего примера. Каждая файловая группа создается с одними и теми же установками, однако их не должно быть, если данные не сбалансированы. Следующий фрагмент кода относится только к Испании, остальные четыре файловые группы и файла хотя и имеют одинаковые параметры, все же располагаются на разных дисках и имеют разные имена.
|
Следующим шагом мы создаем функцию. Она является LEFT-функцией и описывает только 4 секции. В нашем случае список будет включать все страны за исключением Великобритании, поскольку она является последней по алфавиту в этом списке.
|
Для того чтобы в будущем данные создавались в соответствующих файловых группах, названия файловых групп в схеме секционирования также должны быть перечислены в алфавитном порядке. В данном случае должны быть перечислены все 5 файловых групп.
|
Теперь таблица Customers может быть построена на основе новой схемы секционирования CustomersCountryPScheme.
|
Резюме
SQL Server 2005 предлагает Вам секционирование в качестве простого инструмента управления большими таблицами и индексами. Секционируя большие таблицы и индексы вы получаете возможность манипулировать подмножествами ваших данных извне секции - тем самым позволяя упростить управление, улучшить производительность, и абстрагироваться от логики приложения; схема секционирования является полностью прозрачной для приложения. Если ваши данные логически сгруппированы (в диапазоны или списки), а тяжелые запросы должны анализировать эти данные в рамках группировок, а также управлять вставкой и удалением, то наилучшим решением будет использование механизма секционирования. Если же Вам не приходится анализировать большие объемы данных в разрезе диапазонов или если все Ваши запросы обращаются к большей части данных (или даже ко всем), то тогда, с точки зрения простоты управления, обычное использование нескольких файловых групп вместо секционирования будет пожалуй более удобным решением, также улучшающим производительность Вашей базы данных.