Можно создать секционированную таблицу или индекс в SQL Server 2016 с помощью SQL Server Management Studio или Transact-SQL. Данные в секционированной таблице и индексах горизонтально разделены на блоки, которые могут быть распределены между несколькими файловыми группами в базе данных. Секционирование может улучшить управляемость и масштабируемость больших таблиц и индексов.
Или индекса обычно включает четыре этапа:
Создание файловой группы или файловых групп и соответствующих файлов, которые будут содержать секции в соответствии со схемой секционирования.
Создание функции секционирования, которая сопоставляет строки таблицы или индекса с секциями, основываясь на значениях элементов заданного столбца.
Создание схемы секционирования, которая сопоставляет секции секционированной таблицы или индекса с новыми файловыми группами.
Создание или изменение таблицы или индекса и указание схемы секционирования в качестве местоположения хранения.
В этом разделе
Перед началом работы выполните следующие действия.
Ограничения
Безопасность
Создание секционированной таблицы или индекса с использованием следующих средств:
Среда SQL Server Management Studio
Ограничения
Область действия функции и схемы секционирования ограничена базой данных, в которой она была создана. Функции секционирования располагаются в отдельном от других функций пространстве имен внутри базы данных.
Если у каких-либо строк в функции секционирования имеются столбцы секционирования со значениями NULL, эти строки размещаются в крайней левой секции. Однако если значение NULL указано в качестве граничного значения и указан параметр RIGHT, крайняя левая секция остается пустой, а значения NULL располагаются во второй секции.
Безопасность
Разрешения
Для создания секционированной таблицы требуется разрешение CREATE TABLE в базе данных и разрешение ALTER для схемы, в которой создается таблица. Для создания секционированного индекса требуется разрешение ALTER на таблицу или представление, в которых создается индекс. Создание секционированной таблицы или индекса требует любого из следующих дополнительных разрешений:
Разрешение ALTER ANY DATASPACE. Это разрешение назначено по умолчанию членам предопределенной роли сервера sysadmin и предопределенных ролей базы данных db_owner и db_ddladmin .
Разрешение CONTROL или ALTER для базы данных, в которой создаются функция и схема секционирования.
Разрешение CONTROL SERVER или ALTER ANY DATABASE для сервера базы данных, в которой создаются функция и схема секционирования.
Выполните пошаговые инструкции в этой процедуре для создания одной или нескольких файловых групп, соответствующих файлов и таблицы. В следующем примере при создании секционированной таблицы будут приведены ссылки на эти объекты.
Создание новых файловых групп для секционированной таблицы
Создание секционированной таблицы
В диалоговом окне Создание расписания задания в поле Имя введите имя расписания задания.
В списке Тип расписания выберите тип расписания:
Запускать автоматически при запуске агента SQL Server
Запускать при бездействии процессоров
Повторяющееся . Выберите этот параметр, если новая секционированная таблица регулярно обновляется с учетом новых данных.
Однократно . Это параметр выбирается по умолчанию.
В разделе Частота в списке Выполняется укажите частоту выполнения:
При выборе Ежедневно в поле Выполняется каждые укажите частоту повторного выполнения расписания задания в днях.
При выборе Еженедельно в поле Выполняется каждые укажите частоту повторного выполнения расписания задания в неделях. Выберите день или дни недели, в которые выполняется расписание задания.
При выборе Ежемесячно щелкните День или Определенный .
При выборе День введите дату месяца, в которую должно выполняться расписание задания, и укажите частоту повторного выполнения расписания задания в месяцах. Например, если требуется, чтобы расписание задания выполнялось 15 числа каждого второго месяца, выберите День и введите в первом поле «15» и «2» - во втором поле. Обратите внимание, что число, введенное во втором поле, не должно превышать «99».
При выборе Определенный выберите определенный день недели в месяце, в котором должно выполняться расписание задания, и укажите частоту повторного выполнения расписания задания в месяцах. Например, если требуется, чтобы расписание задания выполнялось в последний день недели каждого второго месяца, выберите День , выберите последний в первом списке и рабочий день во втором списке, а затем введите «2» во втором поле. Еще можно выбрать первый , второй , третий или четвертый , а также конкретные дни недели (например, воскресенье или среду) в первых двух списках. Обратите внимание, что число, введенное в последнем поле, не должно превышать «99».
При выборе Выполнять раз в укажите определенное время дня для запуска расписания задания в поле Выполнять раз в . Укажите время дня: час, минуту и секунду.
При выборе Выполняется каждые укажите частоту выполнения задания в выбранный день в поле Частота . Например, если требуется, чтобы расписание задания выполнялось каждые 2 часа в день запуска расписания задания, выберите Выполняется кажд. , введите "2" в первом поле, а затем выберите в списке часы . В этом списке также можно выбрать минуты и секунды . Обратите внимание, что число, введенное в первом поле, не должно превышать «100».
В поле Начинать в введите время для начала запуска расписания задания. В поле Заканчивать в введите время для завершения повторного выполнения расписания задания. Укажите время дня: час, минуту и секунду.
Щелкните правой кнопкой мыши таблицу для секционирования, выберите Хранение и щелкните Создать секцию…
В Мастере создания секций на странице Приветствия мастера создания секций щелкните Далее .
На странице Выбор столбца секционирования в сетке выберите столбец, по которому необходимо секционировать таблицу. В сетке Доступные столбцы секционирования отображаются только столбцы с типами данных, по которым можно секционировать данные. Если выбрать в качестве столбца секционирования вычисляемый столбец, его необходимо назначить постоянным.
Выбор столбца секционирования и диапазона значений определяется прежде всего степенью, до которой данные должны быть логически сгруппированы. Например, можно разделить данные на логические группы по месяцам или кварталам года. Планируемые запросы к данным определяют, адекватно ли такое логическое группирование для управления секциями таблицы. В качестве столбцов секционирования могут использоваться данные любого типа, кроме text , ntext , image , xml , timestamp , varchar(max) , nvarchar(max) , varbinary(max) , псевдонимов типов данных, а также определяемых пользователем типов данных CLR.
Совместное размещение этой таблицы с выбранной секционированной таблицей
Позволяет выбрать секционированную таблицу, которая содержит связанные данные, для соединения с данной таблицей по столбцу секционирования. Таблицы с секциями, объединенные по столбцам секционирования, обычно более эффективны в запросах.
Выровнять хранение неуникальных и уникальных индексов с индексированным столбцом секционирования
Выравнивает все индексы таблицы, секционированные с помощью одной и той же схемы. При выравнивании таблицы и ее индексов секции можно более эффективно перемещать в секционированные таблицы и из них, так как данные секционируются по одному и тому же алгоритму.
После выбора столбца секционирования и других столбцов щелкните Далее .
На странице Выбор функции секционирования в разделе Выберите функцию секционирования щелкните или . При выборе Создать функцию секционирования введите имя функции. Если выбран вариант Существующая функция секционирования , то выберите в списке имя функции, которая будет использоваться для секционирования. Обратите внимание, что при отсутствии в базе данных других функций секционирования параметр Существующая функция секционирования будет недоступен.
Далее .
На странице Выбор схемы секционирования в разделе Выберите схему секционирования щелкните или . При выборе Создать схему секционирования введите имя схемы. Если выбран вариант Существующая схема секционирования , то выберите в списке имя схемы, которая будет использоваться. При отсутствии других схем секционирования в базе данных параметр Существующая схема секционирования будет недоступен.
После завершения работы с этой страницей нажмите кнопку Далее .
На странице Сопоставление секций в разделе Диапазон выберите Левая граница или Правая граница , чтобы выбрать для включения во все создаваемые файловые группы наибольшее или наименьшее ограничивающее значение. В дополнение к числу файловых групп, указанному в качестве граничных значений при создании секций, необходимо всегда вводить еще одну дополнительную файловую группу.
В сетке Выбор файловых групп и указание граничных значений в поле Файловая группа выберите файловую группу, в которую будут секционироваться данные. В разделе Граница введите граничное значение для каждой файловой группы. Если граничное значение не указано, функция секционирования сопоставляет всю таблицу или индекс с одной секцией, используя имя функции секционирования.
На этой странице доступны следующие дополнительные параметры:
Установить границы…
Открытие диалогового окна Установка граничных значений
, в котором можно выбрать граничные значения и диапазоны дат для секций. Этот параметр доступен, только если выбран столбец секционирования, содержащий данные одного из следующих типов: date
, datetime
, smalldatetime
, datetime2
или datetimeoffset
.
Оценка хранения
Оценка количества строк, необходимого и доступного пространства для хранения каждой файловой группы, указанной для секций. Эти значения доступны в сетке только для чтения.
В диалоговом окне Задание граничных значений можно задать следующие дополнительные параметры:
Дата начала
Выбор даты начала для значений диапазона секций.
Дата окончания
Выбор даты окончания для значений диапазона секций. При выборе Левая граница
на странице Сопоставление секций
эта дата будет последним значением для каждой из файловых групп и секций. При выборе Правая граница
на странице Сопоставление секций
эта дата будет первым значением в предпоследней файловой группе.
Диапазон даты
Выбор степени детализации дат или шага значения диапазона для каждой секции.
После завершения работы с этой страницей нажмите кнопку Далее .
На странице Выбор выходного параметра укажите способ заполнения секционированной таблицы. Выберите Создать скрипт для создания скрипта SQL на основе данных на предыдущих страницах мастера. Выберите Запустить немедленно , чтобы создать новую секционированную таблицу после завершения работ со всеми оставшимися страницами мастера. Выберите Расписание , чтобы создать новую секционированную таблицу в заранее заданное время в будущем.
При выборе Создать скрипт в Параметры скрипта будут доступны следующие параметры:
Вывести скрипт в файл
Создание скрипта как SQL-файла. Введите имя и местоположение файла в поле Имя файла
или щелкните Обзор
, чтобы открыть диалоговое окно Расположение файла скрипта
. В разделе Сохранить как
выберите Текст в Юникоде
или Текст ANSI
.
Вывести скрипт в буфер обмена
Сохранение скрипта в буфере обмена.
Вывести скрипт в новое окно запроса
Скрипт создается в новом окне редактора запросов. Это параметр выбирается по умолчанию.
При выборе Расписание щелкните Изменить расписание .
Установите или снимите флажок Включен , чтобы включить или отключить расписание.
При выборе Повторяющееся :
В поле Сколько раз в день укажите частоту повторного выполнения расписания задания в день запуска расписания задания:
В разделе Длительность , в области Дата начала введите дату начала запуска расписания задания. Выберите Дата окончания или Без даты окончания , чтобы указать дату завершения выполнения расписания задания. При выборе Дата окончания введите дату завершения запуска расписания задания.
При выборе значения Однократно в Однократное выполнение в поле Дата введите дату запуска расписания задания. В поле Время введите время запуска расписания задания. Укажите время дня: час, минуту и секунду.
В разделе Сводка в Описание проверьте правильность всех параметров расписания задания.
Нажмите кнопку ОК .
После завершения работы с этой страницей нажмите кнопку Далее .
На странице Просмотр сводки в разделе Просмотр выбранных параметров разверните все доступные параметры, чтобы убедиться в правильности всех настроек секции. Если все настройки правильные, нажмите кнопку Готово .
Страница Выполнение мастера создания секций используется для отслеживания сведений о состоянии действий мастера создания секций. В зависимости от действий, выбранных в мастере, страница выполнения может содержать одно или несколько действий. В верхнем поле показано общее состояние мастера и число полученных им сообщений о состоянии, предупреждений и сообщений об ошибках.
На странице Выполнение мастера создания секций доступны следующие параметры:
Сведения
Сведения о событии, состоянии и любых сообщениях, которые возвращены в результате действий мастера.
Действие
Задает тип и имя каждого действия.
Состояние
Указывает, вернуло ли действие мастера в целом значение Успешно
или Ошибка
.
Сообщение
Любые сообщения об ошибках или предупреждения от процесса.
Отчет
Создание отчета, содержащего результаты мастера создания секций. Доступные параметры: Просмотреть отчет
, Сохранить отчет в файл
, и Отправить отчет по электронной почте
.
Просмотр отчета
Открытие диалогового окна Просмотр отчета
, которое содержит текстовый отчет о работе мастера создания секций.
Копировать отчет в буфер обмена
Копирование результатов отчета о работе мастера в буфер обмена.
Отправить отчет электронной почтой
Копирование результатов отчета о состоянии мастера в сообщение электронной почты.
Завершив выбор параметров, нажмите кнопку Закрыть .
Мастер создания секций создаст функцию и схему секционирования, а затем применит секционирование к указанной таблице. Чтобы проверить секционирование таблицы, в обозревателе объектов щелкните правой кнопкой мыши таблицу и выберите Свойства . Перейдите на страницу Хранилище . На странице отображается информация, в том числе имя функции секционирования, схема и число секций.
Создание секционированной таблицы
В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.
На стандартной панели выберите пункт Создать запрос .
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить . В следующем примере показано создание файловых групп, функции и схемы секционирования. Новая таблица создается при указании схемы секционирования в качестве места хранения.
USE AdventureWorks2012; GO -- Adds four new filegroups to the AdventureWorks2012 database ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test1fg; GO ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test2fg; GO ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test3fg; GO ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test4fg; -- Adds one file for each filegroup. ALTER DATABASE AdventureWorks2012 ADD FILE (NAME = test1dat1, FILENAME = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat1.ndf" , SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) TO FILEGROUP test1fg; ALTER DATABASE AdventureWorks2012 ADD FILE (NAME = test2dat2, FILENAME = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t2dat2.ndf" , SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) TO FILEGROUP test2fg; GO ALTER DATABASE AdventureWorks2012 ADD FILE (NAME = test3dat3, FILENAME = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t3dat3.ndf" , SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) TO FILEGROUP test3fg; GO ALTER DATABASE AdventureWorks2012 ADD FILE (NAME = test4dat4, FILENAME = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t4dat4.ndf" , SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) TO FILEGROUP test4fg; GO -- Creates a partition function called myRangePF1 that will partition a table into four partitions CREATE PARTITION FUNCTION myRangePF1 (int ) AS RANGE LEFT FOR VALUES (1 , 100 , 1000 ) ; GO -- Creates a partition scheme called myRangePS1 that applies myRangePF1 to the four filegroups created above CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg) ; GO -- Creates a partitioned table called PartitionTable that uses myRangePS1 to partition col1 CREATE TABLE PartitionTable (col1 int PRIMARY KEY , col2 char (10 )) ON myRangePS1 (col1) ; GO
Определение секционирования таблицы
Следующий запрос возвращает одну или несколько строк, если таблица PartitionTable секционирована. Если таблица не секционирована, не возвращается ни одна строка.
Определение граничных значений для секционированной таблицы
Следующий запрос возвращает граничные значения для каждой секции в таблице PartitionTable .
SELECT t .name AS TableName, i .name AS IndexName, p .partition_number, p .partition_id, i .data_space_id, f .function_id, f .type_desc, r.boundary_id, r.value AS BoundaryValue FROM sys .tables AS t JOIN sys .indexes AS i ON t .object_id = i .object_id JOIN sys .partitions AS p ON i .object_id = p .object_id AND i .index_id = p .index_id JOIN sys .partition_schemes AS s ON i .data_space_id = s.data_space_id JOIN sys .partition_functions AS f ON s.function_id = f .function_id LEFT JOIN sys .partition_range_values AS r ON f .function_id = r.function_id and r.boundary_id = p .partition_number WHERE t .name = "PartitionTable" AND i .type <= 1 ORDER BY p .partition_number;
Определение столбца секционирования секционированной таблицы
Следующий запрос возвращает имя столбца секционирования таблицы. PartitionTable .
SELECT t . AS ObjectID , t .name AS TableName , ic.column_id AS PartitioningColumnID , c .name AS PartitioningColumnName FROM sys .tables AS t JOIN sys .indexes AS i ON t . = i . AND i . <= 1 -- clustered index or a heap JOIN sys .partition_schemes AS ps ON ps.data_space_id = i .data_space_id JOIN sys .index_columns AS ic ON ic. = i . AND ic.index_id = i .index_id AND ic.partition_ordinal >= 1 -- because 0 = non-partitioning column JOIN sys .columns AS c ON t . = c . AND ic.column_id = c .column_id WHERE t .name = "PartitionTable" ; GO
Дополнительные сведения см. в разделе.
Добрый вечер/день/утро уважаемые хабралюди! Продолжаем развивать и дополнять блог о моей любимой open source rdbms Postgresql. Чудесным образом так получилось, что тема сегодняшнего топика еще ни разу здесь не подымалась. Надо сказать, что секционирование в postgresql очень хорошо описано в документации , но разве ж это меня остановит?).
Вступление
Вообще под секционированием в общем случае понимают не какую-то технологию, а скорее подход к проектированию БД, появившийся задолго до того, как СУБД начали поддерживать т.н. секционированные таблицы. Мысль очень простая - разделить таблицу на несколько частей меньшего размера. Различают два подвида - горизонтальное и вертикальное секционирование.Горизонтальное секционирование
Части таблицы содержат разные ее строки. Положим у нас есть таблица логов некоего абстрактного приложения - LOGS. Мы можем разбить ее на части - одна для логов за январь 2009, другая - за февраль 2009, и т.д.Вертикальное секционирование
Части таблицы содержат разные ее столбцы. Найти применение для вертикального секционирования (когда оно действительно оправдано) несколько сложнее, чем для горизонтального. В качестве сферического коня предлагаю рассмотреть такой вариант: таблица NEWS имеет столбцы ID, SHORTTEXT, LONGTEXT, и пусть поле LONGTEXT используется намного реже первых двух. В таком случае имеет смысл разбить таблицу NEWS по столбцам (создать две таблицы для SHORTTEXT и LONGTEXT соответственно, связанных первичными ключами + создать view NEWS, содержащую оба столбца). Таким образом, когда нам нужно только описание новости, СУБД не придется читать с диска еще и весь текст новости.Поддержка секционирования в современных СУБД
Большинство современных СУБД поддерживают секционирование таблиц в том или ином виде.- Oracle - поддерживает секционирование начиная с 8й версии. Работа с секциями с одной стороны очень простая (вообще можно о них не думать, работаешь как с обычной таблицей*), а с другой - все очень гибко. Секции можно разбивать на «subpartitions», удалять, делить, переносить. Поддерживаются разные варианты индексирования секционированной таблицы (глобальный индекс, секционированный индекс). Ссылочка на объемное описание.
- Microsoft SQL Server - поддержка секционирования появилась недавно (в 2005). Первое впечатление от использования - «Ну наконец-то!!:)», второе - «Работает, вроде все ок». Документация на msdn
- MySQL - поддерживает начиная с версии 5.1. Очень хорошее описание на хабре
- И так далее…
Секционирование в Postgresql
Секционирование таблиц в postgresql несколько отличается в реализации от остальных БД. Основой для секционирования служит наследование таблиц (вещь присущая исключительно postgresql). То есть, у нас должна быть основная таблица (master table), а ее секциями будут таблицы-наследники. Будем рассматривать секционирование на примере задачи, приближенной к реальности.Постановка задачи
База данных используется для сбора и анализа данных о посетителях сайта/сайтов. Объемы данных достаточно велики для того, чтобы задуматься о секционировании. При анализе в большинстве случаев используются данные за последний день.1. Создаем основную таблицу:
CREATE TABLE analytics.events
user_id UUID NOT NULL ,
event_type_id SMALLINT NOT NULL ,
event_time TIMESTAMP DEFAULT now() NOT NULL ,
url VARCHAR (1024) NOT NULL ,
referrer VARCHAR (1024),
ip INET NOT NULL
);
2. Секционировать будем по дням по полю event_time. На каждый день будем создавать новую секцию. Именовать секции будем по правилу: analytics.events_DDMMYYYY. Вот например секция для 1го января 2010 года.
CREATE TABLE analytics.events_01012010
event_id BIGINT DEFAULT nextval("analytics.seq_events" ) PRIMARY KEY ,
CHECK (event_time >= TIMESTAMP "2010-01-01 00:00:00" AND event_time < TIMESTAMP "2010-01-02 00:00:00" )
) INHERITS (analytics.events);* This source code was highlighted with Source Code Highlighter .
При создании секции явно задаем поле event_id (PRIMARY KEY не наследуется) и создаем CHECK CONSTRAINT на поле event_time, дабы не вставить лишнего.
3. Создаем индекс на поле event_time. При разбиении таблицы на секции, мы подразумеваем, что большинство запросов к таблице events будут использовать условие на поле event_time, так что индекс на этом поле нам очень поможет.
CREATE INDEX events_01012010_event_time_idx ON analytics.events_01012010 USING btree(event_time);* This source code was highlighted with Source Code Highlighter .
4. Мы хотим добиться того, чтобы при вставке в основную таблицу, данные оказывались в предназначенной им секции. Для этого делаем следующий финт - создаем триггер, который будет управлять потоками данных.
CREATE OR REPLACE FUNCTION analytics.events_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW .event_time >= TIMESTAMP "2010-01-01 00:00:00" AND
NEW .event_time < TIMESTAMP "2010-01-02 00:00:00" ) THEN
INSERT INTO analytics.events_01012010 VALUES (NEW .*);
ELSE
RAISE EXCEPTION "Date % is out of range. Fix analytics.events_insert_trigger" , NEW .event_time;
END IF ;
RETURN NULL ;
END ;
$$
LANGUAGE plpgsql;* This source code was highlighted with Source Code Highlighter .
CREATE TRIGGER events_before_insert
BEFORE INSERT ON analytics.events
FOR EACH ROW EXECUTE PROCEDURE analytics.events_insert_trigger();* This source code was highlighted with Source Code Highlighter .
5. Все готово, у нас теперь есть секционированная таблица analytics.events. Можем начинать яростно анализировать ее данные. Кстати, CHECK constraints мы создавали не только для того, чтобы защитить секции от некорректных данных. Postgresql может их использовать при составлении плана запроса (правда при живом индексе на event_time выигрыш это даст минимальный), достаточно воспользоваться директивой constraint_exclusion:
SET constraint_exclusion = on ;
SELECT * FROM analytics.events WHERE event_time > CURRENT_DATE ;* This source code was highlighted with Source Code Highlighter .
Конец первой части
Итак, что мы имеем? Давайте по пунктам:1. Таблицу events, разбитую на секции, анализ имеющихся данных за последние сутки становится проще и быстрее.
2. Ужас от осознания того, что все это нужно как-то поддерживать, создавать вовремя секции, не забывая менять триггер соответствующим образом.
О том как просто и беззаботно работать с секционированными таблицами расскажу во второй части.
UPD1: Заменил партиционирование на секционирование
UPD2:
По мотивам замечания одного из читателей, не имеющего, к сожалению, аккаунта на хабре:
С наследованием связано несколько моментов, которые стоит учитывать при проектировании. Секции не наследуют первичный ключ и внешние ключи на их столбцы. То есть, при создании секции, нужно явно создавать PRIMARY KEY и FOREIGN KEYs на столбцы секции. От себя замечу, что создавать FOREIGN KEY на столбцы секционированной таблицы не лучший путь. В большинстве случаев секционированная таблица является «таблицей фактов» и сама ссылается на «dimension» таблицы.
В этой статье я собираюсь продемонстрировать особенности планов исполнения запросов при обращении к секционированным таблицам. Обратите внимание, что существует большая разница между секционированными таблицами (которые стали доступны только с SQL Server 2005) и секционированными представлениями (которые были доступных ещё в SQL Server 2000, и по-прежнему доступны в SQL Server 2005 и последующих версиях). Особенности планов запросов к секционированным представлениям я продемонстрирую в другой статье.
Просмотр таблицы
Давайте создадим простую секционированную таблицу:
create partition function pf(int) as range for values (0, 10, 100)
create partition scheme ps as partition pf all to ()
create table t (a int, b int) on ps(a)
Этот сценарий создает таблицу с четырьмя секциями. SQL Server присвоил значения идентификаторам каждой из четырех секций так, как это показано в таблице:
| PtnId | Values |
| 1 | t.a <= 0 |
| 2 | 0 < t.a <= 10 |
| 3 | 10 < t.a <= 100 |
| 4 | 100 < t.a |
Теперь давайте рассмотрим план такого запроса, который бы вынудил оптимизатор использовать просмотр всей таблицы (Table Scan):
……|–Constant Scan(VALUES:(((1)),((2)),((3)),((4))))
…….|–Table Scan(OBJECT:([t]))
В представленном выше плане, SQL Server явно указывает все идентификаторы секции в операторе «Constant Scan», который реализует просмотр таблицы и поставляет данные оператору соединения вложенных циклов. Тут следует напомнить, что оператор соединения вложенных циклов выполняет проход по внутренней таблице (в данном случае это полный просмотр таблицы) один раз для каждого значения из внешней таблицы (в нашем случае это «Constant Scan»). Таким образом, мы выполняем просмотр таблицы четыре раза; один раз для каждого идентификатора секции.
Следует также отметить, что соединение вложенных циклов показывает явно что внешняя таблица является значениями столбца , где хранятся ID секций. Хотя это не сразу видно в текстовом представлении плана исполнения (к сожалению, мы иногда не замечаем эту информацию), просмотр таблицы использует столбец с идентификаторами секций, которые выбираются для выполнения просмотра и определяют какую секцию сканировать. Эта информация всегда доступна в графическом плане исполнения (нужно заглянуть в свойства оператора просмотра таблицы), а также в XML представление плана исполнения запроса:
Статическая фильтрация секций
Рассмотрим следующий запрос:
select * from t where a < 100
|–Nested Loops(Inner Join, OUTER REFERENCES:() PARTITION ID:())
…….|–Constant Scan(VALUES:(((1)),((2)),((3))))
<(100)) PARTITION ID:())
Предикат «а <100» явно исключает все строки для секции со значением идентификатора равным 4. В данном случае, нет смысла в просмотре соответствующей секции, поскольку ни одна из строк этой секции не удовлетворяет условию предиката. Оптимизатор учитывает этот факт и исключает эту секцию из плана исполнения запроса. В операторе «Constant Scan» указаны только три секции. У нас принято называть это статической фильтрацией секций (static partition elimination), поскольку мы знаем, что во время компиляции список просматриваемых секций остаётся статичным.
Если в результате статичной фильтрации будут исключены все разделы, кроме одного, нам вообще не понадобятся операторы «Constant Scan» и «Nested Loops Join»:
select * from t where a < 0
|–Table Scan(OBJECT:([t]), WHERE:([t].[a]<(0)) PARTITION ID:((1)))
Обратите внимание, что указание «PARTITION ID:((1))», которое задаёт идентификатор подлежащей просмотру секции, теперь является частью оператора просмотра таблицы (Table Scan).
Динамическая фильтрация секций
В некоторых случаях SQL Server не может определить, что на момент компиляции состав просматриваемых секций не изменится, но ему видно, что некоторые секции можно исключить.
select * from t where a < @i
|–Nested Loops(Inner Join, OUTER REFERENCES:() PARTITION ID:())
…….|–Filter(WHERE:(<=RangePartitionNew([@i],(0),(0),(10),(100))))
…….| |–Constant Scan(VALUES:(((1)),((2)),((3)),((4))))
…….|–Table Scan(OBJECT:([t]), WHERE:([t].[a]<[@i]) PARTITION ID:())
Это параметризованный запрос. Так как до исполнения значение параметра мы не знаем (то, что я использую константу в качестве параметра в том же батче, не меняет положение вещей), то на этапе компиляции невозможно определить значение идентификатора секции для оператора «Constant Scan». Возможно придётся просматривать только секцию 1, или это будут секции 1 и 2, и так далее. Поэтому, в этом операторе указаны все четыре идентификатора секций, и мы используем фильтрацию идентификаторов секций на этапе исполнения. Мы называем это «Динамическая фильтрация секций» (Dynamic Partition Elimination).
Фильтр сравнивает каждый идентификатор секции c результатом работы специальной функции «RangePartitionNew». Эта функция вычисляет результаты применения функции секционирования к значению параметра. Аргументами этой функции (слева направо) являются:
- значение (в данном случае параметр @i), который мы хотим отобразить на ID секции;
- булевой флаг, указывающий, отображает ли функция секционирования граничные значения слева (0) или справа (1);
- граничные значения секций (в данном случае это 0, 10, и 100).
В этом примере, поскольку @i имеет значение 0, результатом «RangePartitionNew» является 1. Таким образом, мы просматриваем только секцию с идентификатором 1. Заметим, что в отличие от примера со статической фильтрацией секций, хотя мы сканируем только один раздел, мы по-прежнему имеем «Constant Scan» и «Nested Loops Join». Нам потому нужны эти операторы, что до этапа исполнения мы не знаем секции, которые будут просмотрены.
В некоторых случаях оптимизатор уже на этапе компиляции может определить, что мы будем сканировать только одну секцию, даже если он не может определить, какую именно. Например, если в запросе используется предикат эквивалентности по ключу секционирования, тогда мы знаем, что только одна секция может удовлетворять такому условию. Поэтому, несмотря на то, что у нас должна была быть динамическая фильтрация секций, у нас отпадает необходимость в операторах «Constant Scan» и «Nested Loops Join». Пример:
select * from t where a = @i
|–Table Scan(OBJECT:([t]), WHERE:([t].[a]=[@i]) PARTITION ID:(RangePartitionNew([@i],(0),(0),(10),(100))))
Сочетание статической и динамической фильтрации секций
SQL Server может совмещать статическую и динамическую фильтрацию секций в одном плане запроса:
select * from t where a > 0 and a < @i
|–Nested Loops(Inner Join, OUTER REFERENCES:() PARTITION ID:())
……|–Filter(WHERE:(<=RangePartitionNew([@i],(0),(0),(10),(100))))
……| |–Constant Scan(VALUES:(((2)),((3)),((4))))
……|–Table Scan(OBJECT:([t]), WHERE:([t].[a]<[@i] AND [t].[a]>(0)) PARTITION ID:())
Обратите внимание, что в последнем плане присутствует статическая фильтрация секции ID = 1 с использованием «Constant Scan», и также присутствует динамическая фильтрация для других секций, определяемых предикатами.
$partition
Можно явно вызвать функцию RangePartitionNew, используя $partition:
select *, $partition.pf(a) from t
|–Compute Scalar(DEFINE:(=RangePartitionNew([t].[a],(0),(0),(10),(100))))
……|–Nested Loops(Inner Join, OUTER REFERENCES:() PARTITION ID:())
………..|–Constant Scan(VALUES:(((1)),((2)),((3)),((4))))
………..|–Table Scan(OBJECT:([t]))
Отличительной особенностью такого плана исполнения запроса является появление оператора Compute Scalar.
Дополнительная информация
Страница 23 из 33
Секционирование диапазона - сведения о продажах
Характер использования сведений о продажах зачастую изменчив. Как правило, данные текущего месяца - это оперативные данные; данные предшествующих месяцев - это в большой степени данные, предназначенные для анализа. Чаще всего анализ производится ежемесячно, ежеквартально, либо ежегодно. Поскольку разным аналитикам могут потребоваться значительные объемы различных аналитических данных одновременно, то секционирование лучше всего позволит изолировать их деятельность. В рассматриваемом далее сценарии данные стекаются из 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. Все будут иметь схожий синтаксис, за исключением местоположения, имени файла и имени файловой группы:
| ALTER
DATABASE
SalesDB |
Как только все двадцать четыре файла и файловые группы будут созданы, Вы сможете определить функцию и схему секционирования. Убедиться в том, что ваши файлы и файловые группы созданы, вы можете при помощи системных хранимых процедур sp_helpfile и sp_helpfilegroup.
Функция секции будет определена по столбцу OrderDate с типом данных datetime. Для того чтобы обе таблицы можно было секционировать по столбцу OrderDate, этот столбец должен присутствовать в обеих таблицах. В действительности, значения ключей секционирования обоих таблиц (если обе таблицы будут секционированы по одному и тому же ключу) будут дублировать друг друга; однако это необходимо для получения преимуществ выравнивания, к тому же в большинстве случаев размер ключевых столбцов будет относительно небольшим (размер поля datetime всего 8 байт). Как уже описывалось в Главе "CREATE PARTITION FUNCTION для диапазонных секций", наша функция будет диапазонной функцией секционирования, у которой первое граничное условие будет в первой (LEFT) секции.
| CREATE
PARTITION FUNCTION
TwoYearDateRangePFN(datetime
) |
Поскольку и крайне левый, и крайне правый граничные случаи охвачены, эта функция секционирования фактически создает 25 секции. Таблица будет поддерживать 25-ую секцию, которая останется пустой. Для этой пустой секции не требуется никакой специальной файловой группы, поскольку никакие данные не должны когда-либо в нее попасть. Для того чтобы гарантировать, что никакие данные в нее не попадут, constraint ограничит диапазон данных этой таблицы. Для того чтобы направить данные на соответствующие диски используется схема секционирования, отображающая секции на файловые группы. Схема секционирования будет использовать явное определение файловых групп для каждой из 24 файловых групп, содержащих данные, и PRIMARY - для 25-ой пустой секции.
| CREATE
PARTITION SCHEME |
Таблица может быть создана с тем же синтаксисом, который поддерживали предыдущие релизы SQL Server - используя предложенную по умолчанию, либо определенную пользователем файловую группу (для создания НЕ секционированной таблицы) - либо используя схему (для создания секционированной таблицы). Что касается того, какой из вариантов предпочтительнее (даже если эта таблица в будущем станет секционированной), то все зависит от того, как таблица будет заполняться и сколькими секциями вы собираетесь манипулировать. Наполнение кучи (heap) и последующее создание в ней кластерного индекса, вероятно, обеспечит лучшую производительность, чем загрузка в таблицу, содержащую кластерный индекс. Кроме того, в мультипроцессорных системах вы можете загружать данные в таблицу параллельно, и затем тоже параллельно строить индексы. В качестве примера создадим таблицу Orders и загрузим в нее данные, используя операторы INSERT … SELECT. Чтобы создать таблицу Orders в качестве секционированной, определите схему секционирования в выражении ON оператора CREATE TABLE.
| CREATE
TABLE
SalesDB.. |
Поскольку таблица OrderDetails собирается использовать ту же схему, она должна включать в себя столбец OrderDate.
| CREATE
TABLE
.( |
На следующем шаге в таблицы загружаются данные из новой учебной базы данных AdventureWorks. Убедитесь, что вы установили базу данных AdventureWorks.
| INSERT
dbo. INSERT
dbo. |
Теперь, когда вы загрузили данные в секционированную таблицу, Вы можете воспользоваться новой встроенной системной функцией для того чтобы определить секцию, на которой будут располагаться данные. Следующий запрос для каждой из содержащих данные секций возвращает информацию о том, сколько строк содержится в каждой из секций, а также минимальное и максимальное значения поля OrderDate. Секция, которая не содержит строк, не попадет в итоговый результат.
| SELECT
$partition.TwoYearDateRangePFN(o.OrderDate) SELECT
$partition.TwoYearDateRangePFN(od.OrderDate) |
И наконец теперь, после того как вы загрузили данные, Вы можете создать кластерный индекс и внешний ключ (Foreign key) между таблицами OrderDetails и Orders. В данном случае кластерный индекс будет построен на первичном ключе (Primary Key) точно так же, как вы идентифицируете обе эти таблицы по их ключу секционирования (для OrderDetails к индексу Вы добавите столбец LineNumber для уникальности). По умолчанию при построении индексов на секционированной таблице происходит их выравнивание по отношению к секционированной таблице согласно той же самой схеме секционирования; явно задавать схему не обязательно.
| ALTER
TABLE
Orders
|
Полный синтаксис, определяющий схему секционирования, выглядел бы так:
| ALTER
TABLE
Orders ALTER
TABLE
dbo.OrderDetails |
В современных условиях очень странно бывает иногда слышать "нам нужно свернуть БД 1С - её объём превышает 50 ГБ". Если бы такое собирались сделать администраторы систем SAP R3 или Oracle e Business Suite или даже MS Dynamics Ax их бы наверное уволили. Тем не менее, для 1С это является "стандартной практикой".
Для файловых версий история тянется ещё с версии 1С 7.7 с ограничением в 2ГБ на размер базы. Сейчас ограничение 2ГБ уже только на размер таблицы, размер файла уже может получиться очень и очень не маленьким. Правда если база у вас выросла до такого размера, то наверное туда активно вносились данные - может нужно задуматься о клиент-сервере?
Собственно целью данной статьи является "отговорить" от выполнения свертки БД пользователей клиент-серверного варианта 1С, за счет использования несколько более "продвинутых" технологий.
Итоговая цифра получается 30-40 т. минимум против 20-25 в случае покупки жесткого диска, и получения 500 ГБ дополнительного места
Поэтому появляются продукты вроде
Хорошие наверное продукты, и цели свои выполняют. Вот только меняется структура таблиц от версии к версии платформы. 1С нам об этом не раз говорили. Появился разделитель данных в 14-ом релизе и всё... скорее всего эта обработка для 14 релиза уже не подойдёт. Да и страшно как-то, не говоря уже о нарушении лицензионного соглашения.
И даже после этого найдутся пользователи которым "вдруг неожиданно понадобились" стертые данные, которые "как раз хотели поправить" каку-то циферку, которая "не влияет на последовательнсти" в документе закрытого периода. А хуже если выяснится что кто-то эти документы смотрел постоянно для каких-то только ему ведомых целей. Конечно это всё лишь ошибки в методике работы, но тем не менее недовольство пользователей будет.
-
Открываем Management Studio в списке баз выбираем нужную, открываем её свойства.
- Переходим на вкладку "Файловые группы" как показано на рисунке, и добавляем ещё одну файловую группу (на примере она названа SECONDARY)
- Переходим на вкладку "Файлы" и добавляем новый файл, для которого выбираем созданную файловую группу. Этот файл МОЖНО РАСПОЛОЖИТЬ НА ДРУГОМ ДИСКЕ
-
Теперь используя обработку к примеру: определяем какие таблицы мы можем смело "пожертвовать" на более медленный (ну или наоборот всё на медленный, остальные - на более быстрый) носитель. Правило 80/20 здесь действует. 80% операций проводятся с 20% данными, так что думайте какие таблички вам нужны оперативно, а какие не очень. "Хранилище дополнительной информации", документы ввода начальных остатков, документы которые уже не используете сразу определяйте как те которые можно перенести в "медленную" файловую группу.
Выбираем таблицу которую нужно перенести в другую файловую группу - выбираем меню изменения таблицы (проект) и в свойствах меняем файловую группу:
индексы таблицы при этом тоже переносятся в данную файловую группу.
Достаточно удобный механизм распределения таблиц по дисковым массивом разной скорости. Лицензионному соглашению это не противоречит, т.к. в решении мы не используем доступ к данным и к информационной базе средствами отличными от платформы 1С. Мы лишь организуем хранение этих данных удобным образом.
DBCC TRACEON (1807)
Пишем данную команду в Management Studio, выполняем и можем успешно создавать базы по сети. Само собой при этом экземпляр SQL Server-а должен быть запущен от имени доменной учетной записи, и у этой записи должны быть права на нужную сетевую папку.
Но прошу быть очень внимательными при использовании данной команды в случае если у вас пропадёт сеть при работе с БД вся БД на время её отсутствия будет не доступной. Microsoft не зря закрыли эту возможность для массового использования. Вообще эта возможность предполагается для создания баз на NAS хранилищах, что и настоятельно рекомендую. Подойдёт так же стабильный и надежный файловый сервер, имеющий прямое подключение к серверу на котором запущен MS SQL СУБД.
Подробнее про другие флаги трассировки можно прочитать в статье http://msdn.microsoft.com/ru-ru/library/ms188396.aspx
Т.е. часть файловую группу можно вообще хранить в сети, а уж там дисковое пространство расширяется без проблем.
Разделить таблицы на разные файловые группы конечно хорошо... но вы скажете что есть тут парочка таблиц... которые тянутся с 2005 года... и уже занимают с десяток гигабайт.. вот бы в них все данные да отдельный диск положить, а текущие оставить.
Вы не поверите, но и это тоже возможно, хотя и не очень просто:
Создаём функуцию секционирования по дате:
create partition function YearSection(datetime)
as range right for values ("20110101");
Всё что до 2011 года будет попадать в одну секцию, всё что после - в другую.
Создаём схему секционирования
create partition scheme YearScheme
as partition YearSection to (SECONDARY, PRIMARY);
Этим говорим, что все данные до 11 года будут попадать в файловую группу "Secondary" а после - в "Primary"
Теперь осталось таблицу перестроить с разделением на секции. Для этого проще всего воспользоваться уже management studio, потому как процесс не простой. Вам нужно перестроить кластерный индекс для таблицы (который по сути и является самой таблицей), выбрав для индекса созданную схему секционирования:
На рисунке вы видите что выбор не доступен - всё правильно, секционирование таблиц возможно только в версии Enterprise MS SQL Server . Кластерный индекс отличить легко - картинка с круглыми скобками. Для РН и всех объектов 1С он создаётся. Для РН кластерный индекс по периоду есть всегда. Для документов и справочников хорошо бы конечно создать другой, который включает реквизит по которому будет секционирование... но это уже будет являться нарушением лицензионного соглашения.
Но для этого нужно не сворачивать базу, а проделать следующее:
а) Объяснить всем как пользоваться отборами, как они сохраняются, как пользоваться интервалами журнала, как они сохраняются
б) Пометить на удаление ненужные данные если они не несут никакой смысловой нагрузки (контрагентов и номенклатуру, с которыми больш не работаете) - этим вы принесёте пользователям больше пользы чем сверткой. В случае наличия ресурсов настроить автоматическую пометку на удаление неиспользуемых объектов и сделать отбор по умолчанию в программном коде для того чтобы не отображались по умолчанию не нужные пользователям объекты - помеченные на удаление
в) Настроить другие полезные "отборы по умолчанию" - например чтобы каждый менеджер по умолчанию видел только свои документы. А если хочет посмотреть документы "товарища" - нужно отключать отбор.
По всем реквизитам, которые участвуют в отборе не забывайте ставить признак "Индексировать с доп. упорядочиванием" - тогда на производительности системы такие "удобства" не скажутся.
