Секционированные таблицы и индексы

Применимо к:SQL Server Azure SQL База данных Azure SQL Управляемый экземпляр SQL База данных в Microsoft Fabric

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

Преимущества секционирования

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

Это позволяет быстро и эффективно переносить подмножества данных и обращаться к ним, сохраняя при этом целостность набора данных. Например, операция, например загрузка данных из OLTP в систему OLAP, занимает только секунды, а не в минутах и часах, когда данные не секционируются.
Вы можете выполнять операции обслуживания или хранения данных на одном или нескольких секциях быстрее. Операции более эффективны, так как выполняются только с поднаборами данных, а не со всей таблицей. Например, можно сжать данные в одной или нескольких секциях, перестроить одну или несколько секций индекса или усечь данные в одной секции. Вы также можете переместить отдельные секции из одной таблицы в архивную таблицу.
Вы можете повысить производительность запросов на основе типов часто выполняемых запросов. Например, оптимизатор запросов может обрабатывать запросы на эквивалентность между двумя или более секционированных таблицами быстрее, когда столбцы секционирования совпадают с столбцами, на которых объединяются таблицы. Дополнительные сведения см. в разделе "Запросы".
Вы можете улучшить параллелизм рабочей нагрузки, включив эскалацию блокировки на уровне секции, а не на уровне таблицы. Это может уменьшить количество конфликтов блокировок для таблицы. Чтобы уменьшить конфликт блокировки, разрешив эскалацию блокировки в раздел, установите значение LOCK_ESCALATION для опции ALTER TABLE оператора AUTO.

Компоненты и основные понятия

Следующие термины относятся к секционированию таблиц и индексов.

Функция Partition

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

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

Тип диапазона (либо LEFT, либо RIGHT) указывает, как значения границ функции секционирования распределяются в результирующих разделах.

Диапазон LEFT указывает, что значение границы принадлежит левой части интервала границы, когда значения интервалов сортируются ядром СУБД в порядке возрастания слева направо. Другими словами, самое высокое ограничивающее значение включается в секцию.
Диапазон RIGHT указывает, что значение границы принадлежит правой части интервала границы, когда значения интервалов сортируются ядром СУБД в порядке возрастания слева направо. Другими словами, наименьшее ограничивающее значение включается в секцию.

Если LEFT или RIGHT не указаны, LEFT используется в качестве типа диапазона по умолчанию.

Например, следующая функция секционирования секционирует таблицу или индекс на 12 секций, по одному для каждого месяца годовых значений в столбце datetime . RIGHT Используется тип диапазона, указывающий, что значения границ служат нижними ограничивающими значениями в каждой секции. RIGHTС диапазонами часто проще работать при секционировании таблицы на основе столбца типов данных datetime, datetime2 или datetimeoffset, так как строки со значением полуночи хранятся в тех же разделах, что и строки с последующими значениями в тот же день. Аналогичным образом, если используется тип данных дата и используется секционирование на месяц или более, RIGHT диапазон оставляет первый день месяца в той же секции, что и последующие дни месяца. Это помогает точно устранить секции при запросе данных всего дня.

CREATE PARTITION FUNCTION [myDateRangePF1](DATETIME)
    AS RANGE RIGHT
    FOR VALUES ('2022-02-01', '2022-03-01', '2022-04-01',
                '2022-05-01', '2022-06-01', '2022-07-01',
                '2022-08-01', '2022-09-01', '2022-10-01',
                '2022-11-01', '2022-12-01');

В следующей таблице показано, как таблица или индекс, использующие эту функцию секционирования на столбце datecol, разбиваются на разделы. 1 февраля является первой точкой границы, определенной в функции. RIGHT Поскольку используется тип диапазона, 1 февраля является нижней границей раздела 2.

Partition	1	2	...	11	12
Values	Datecol<`2022-02-01 12:00AM`	Datecol>= `2022-02-01 12:00AM` И Datecol<`2022-03-01 12:00AM`		Datecol>= `2022-11-01 12:00AM` И COL1<`2022-12-01 12:00AM`	Datecol>= `2022-12-01 12:00AM`

Для обоих RANGE LEFT и RANGE RIGHT левый раздел имеет минимальное значение типа данных в качестве нижнего значения, а правый раздел имеет максимальное значение типа данных в качестве верхнего значения.

Дополнительные примеры функций секционирования, использующих диапазоны типов LEFT и RIGHT, см. в статье CREATE PARTITION FUNCTION.

Схема секционирования

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

Найдите пример синтаксиса для создания схем секционирования в CREATE PARTITION SCHEME.

Filegroups

Существует две причины использования схемы секционирования с несколькими файловыми группами:

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

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

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

Дополнительные сведения о создании файловых групп в SQL Server и Управляемом экземпляре SQL Azure см. в разделе ALTER DATABASE (Transact-SQL) Параметры файлов и файловой группы.

Столбец разбиения

Столбец таблицы или индекса, который является входным элементом функции секционирования. При выборе столбца секционирования применяются следующие рекомендации.

Вычисляемые столбцы, участвующие в функции секционирования, должны быть явно созданы как PERSISTED.
- Поскольку в качестве столбца секционирования может использоваться только один столбец, в некоторых случаях объединение нескольких столбцов в вычисляемом столбце может оказаться полезным.
Столбцы всех типов данных, допустимые для использования в качестве ключевых столбцов индекса, можно использовать в качестве столбца секционирования, кроме метки времени.
Столбцы типов данных больших объектов (LOB), такие как ntext, text, image, xml, varchar(max), nvarchar(max)и varbinary(max) не могут быть указаны.
Столбцы, использующие определяемые пользователем типы данных CLR и типы данных псевдонимов, нельзя указывать.

Чтобы секционировать таблицу или индекс, укажите схему секционирования и столбец секционирования в инструкциях CREATE TABLE, ALTER TABLE и CREATE INDEX .

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

Выровненный индекс

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

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

Секционирование кластеризованных индексов

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

Секционирование некластеризованных индексов

При секционировании уникального некластеризованного индекса столбец секционирования должен содержаться в ключе индекса. При секционировании некластеризованного индекса ядро СУБД добавляет столбец секционирования по умолчанию в качестве неключевого (включенного) столбца индекса, чтобы убедиться, что индекс соответствует базовой таблице. Ядро СУБД не добавляет столбец секционирования в индекс, если он уже присутствует в индексе. Дополнительные сведения о некластеризованных индексах и архитектуре индексов см. в разделе Рекомендации по созданию некластеризованных индексов.

Несовмещенный индекс

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

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

Устранение разделов

Когда предикат запроса ссылается на столбец секционирования, ядро СУБД может устранить или пропустить некоторые секции при чтении секционированных таблиц или индексов. Это может повысить производительность запросов.

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

Limitations

До SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) секционированные таблицы и индексы не были доступны в каждом выпуске SQL Server. Список функций, поддерживаемых выпусками SQL Server, см. в выпусках и поддерживаемых функциях SQL Server 2022.
Секционированные таблицы и индексы доступны во всех уровнях служб Базы данных SQL Azure, базы данных SQL в Fabric и Управляемом экземпляре SQL Azure.
- В Базе данных SQL Azure и базе данных SQL в Fabric все секции должны размещаться в PRIMARY файловой группе, так как предоставляется только файловая PRIMARY группа.
Секционирование таблиц доступно в выделенных пулах SQL в Azure Synapse Analytics с некоторыми различиями синтаксиса. Дополнительные сведения о секционированиях таблиц в выделенном пуле SQL.
Область действия функции и схемы секционирования ограничена базой данных, в которой она была создана. Функции секционирования располагаются в отдельном от других функций пространстве имен внутри базы данных. Функции секционирования и схемы секционирования не принадлежат схеме.
Если в секционированных таблицах есть NULLs в столбце секционирования, эти строки помещаются в левую часть секции. Однако если значение NULL указано в качестве первого значения границы и RANGE RIGHT указано в определении функции секции, то левая часть секции остается пустой, а NULLs помещаются во вторую секцию.
Ядро СУБД поддерживает до 15 000 секций. В версиях, предшествующих SQL Server 2012 (11.x), число секций по умолчанию ограничено 1000.

Рекомендации по повышению производительности

Ядро СУБД поддерживает до 15 000 секций на таблицу или индекс. Однако использование большого количества секций влияет на память, секционированные операции индексов, команды DBCC, изменение схемы и производительность запросов. В этом разделе описываются последствия для производительности проектов, которые включают большое количество секций и предоставляют обходные пути по мере необходимости.

Предупреждение

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

Избегайте проектов с числом секций в сотен или тысяч, если это не строго необходимо.

Использование памяти и рекомендации

При большом количестве используемых секций рекомендуется использовать ОЗУ не менее 16 ГБ. Если в системе недостаточно памяти, операции языка манипулирования данными (DML), операции языка определения данных (DDL) и другие операции могут завершиться ошибкой из-за нехватки памяти. В системах с 16 ГБ ОЗУ, которые выполняют множество ресурсоемких процессов, может не хватить памяти для операций, выполняемых на большом числе секций. Таким образом, чем больше памяти у вас более 16 ГБ, тем меньше вероятность возникновения проблем с производительностью и памятью.

Ограничения памяти могут повлиять на производительность или способность ядра СУБД создавать секционированные индексы. Это особенно касается того, что индекс не соответствует базовой таблице или не соответствует кластеризованному индексу.

В SQL Server и Управляемый экземпляр SQL Azure можно увеличить index create memory (KB) параметр конфигурации сервера. Дополнительные сведения см. в разделе "Конфигурация сервера: создание памяти индекса".

Для базы данных SQL Azure рекомендуется временно или постоянно увеличивать объем вычислительных ресурсов базы данных, чтобы получить больше памяти.

Операции секционированного индекса

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

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

Когда ядро СУБД выполняет сортировку для создания секционированных индексов, сначала создается одна таблица сортировки для каждой секции. Затем он создает таблицы сортировки либо в соответствующей файловой группе каждой секции, либо в tempdb том случае, если SORT_IN_TEMPDB задан параметр индекса. Для всех таблиц сортировки требуется минимальный объем оперативной памяти. При создании секционированного индекса, выравниваемого с базовой таблицей, таблицы сортировки создаются по одному за раз, используя меньше памяти. Однако при создании неупорядоченного секционированного индекса таблицы сортировки создаются одновременно. В результате необходим достаточный объем оперативной памяти, чтобы параллельно их обрабатывать. Чем больше число секций, тем больше требуется оперативной памяти. Для каждой из секций размер таблицы сортировки составляет не менее 40 страниц, по 8 килобайт каждая. Например, для невыровненного секционированного индекса, разбитого на 100 секций, потребуется объем оперативной памяти для одновременной сортировки 4 000 страниц (40*100). Если эта память доступна, операция сборки завершается успешно, но производительность может пострадать. Если эта память недоступна, операция сборки завершается ошибкой. Кроме того, для выравнивания секционированного индекса с 100 секциями требуется только достаточно памяти для сортировки 40 страниц, так как сортировки не выполняются одновременно.

Если ядро СУБД использует параллелизм запросов в операции сборки индекса, требование к памяти может быть больше как для согласованных, так и для несогласованных индексов. Чем выше степень параллелизма (DOP), тем выше требование к памяти. Например, если ядро СУБД задает doP 4, неупорядоченный секционированный индекс с 100 секциями требует достаточно памяти для четырех процессоров для сортировки 4000 страниц одновременно или 16 000 страниц. Если секционированный индекс выровнен, требования оперативной памяти снижаются до 40 страниц для каждого из четырех процессоров, то есть 160 страниц (4*40). Параметр индекса MAXDOP можно использовать для уменьшения степени параллелизма в качестве обходного решения за счет потенциально длительного времени сборки индекса.

Команды DBCC

С большим количеством секций команды DBCC, такие как DBCC CHECKDB и DBCC CHECKTABLE , могут занять больше времени, чтобы выполнить по мере увеличения числа секций.

Queries

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

Например, предположим, что таблица содержит 100 миллионов строк и столбцовA.B

В сценарии 1 таблица делится на 1000 секций в столбце A.
В примере 2 таблица делится на 10,000 секций по столбцу A.

Запрос к таблице с предложением WHERE, фильтрующим по столбцу A, выполнит устранение разделов и сканирует подмножество всех разделов. Этот же запрос может выполняться быстрее в сценарии 2, так как есть меньше строк для сканирования в секции. Запрос, включающий предложение WHERE с фильтром по столбцу B, будет сканировать все секции. Запрос может выполняться быстрее в сценарии 1, чем в сценарии 2, так как для сканирования меньше секций.

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

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

Если часто выполняются запросы, связанные с сопоставлением между двумя или несколькими секционированных таблицами, их столбцы секционирования должны совпадать со столбцами, на которых объединяются таблицы. Дополнительно: таблицы или их индексы должны быть упорядочены. Это означает, что они либо используют ту же именованную функцию секционирования, либо используют разные функции секционирования, которые по сути одинаковы, в том, что они:

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

Таким образом, оптимизатор запросов может быстрее обработать соединение, так как соединение обрабатывает данные из пар с коллокированными секциями. Если запрос присоединяет две таблицы, которые не объединены или не секционированы в поле соединения, наличие секций может на самом деле замедлить обработку запросов, а не ускорить ее.

В некоторых запросах может быть полезно использовать $PARTITION . Дополнительные сведения см. в $PARTITION.

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

Вычисление статистики во время операций с разделенными индексами

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

Чтобы создать или обновить статистику по секционированным индексам, сканируя большую выборку или все строки в таблице, используйте CREATE STATISTICS или UPDATE STATISTICS с SAMPLE или FULLSCAN предложениями.

Обратная связь

Были ли сведения на этой странице полезными?

Last updated on 2025-12-29