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

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

До SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) секционированные таблицы и индексы не были доступны в каждом выпуске SQL Server. Список функций, поддерживаемых выпусками SQL Server, см. в выпусках и поддерживаемых функциях SQL Server 2022. Секционированные таблицы и индексы доступны во всех уровнях служб База данных SQL Azure и Управляемый экземпляр SQL Azure.

Секционирование таблиц также доступно в выделенных пулах SQL в Azure Synapse Analytics с некоторыми различиями синтаксиса. Дополнительные сведения о секционированиях таблиц в выделенном пуле SQL.

Внимание

Ядро СУБД поддерживает до 15 000 секций по умолчанию. В версиях, предшествующих SQL Server 2012 (11.x), число секций по умолчанию ограничено 1000.

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

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

  • Это позволяет быстро и эффективно переносить подмножества данных и обращаться к ним, сохраняя при этом целостность набора данных. Например, операция, например загрузка данных из OLTP в систему OLAP, занимает только секунды, а не в минутах и часах, когда данные не секционируются.

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

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

Вы можете повысить производительность, включив эскалацию блокировки на уровне секции вместо всей таблицы. Это может уменьшить количество конфликтов блокировок для таблицы. Чтобы снизить состязание блокировок с помощью применения укрупнения блокировок к секциям, задайте для параметра LOCK_ESCALATION инструкции ALTER TABLE значение AUTO.

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

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

Функция секционирования

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

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

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

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

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

Например, следующая функция секционирования секционирует таблицу или индекс на 12 секций, по одному для каждого месяца годовых значений в столбце datetime . Используется диапазон RIGHT, указывающий, что значения границ будут служить нижними ограничивающими значениями в каждой секции. Диапазоны RIGHT часто проще работать при секционирования таблицы на основе столбца типов данных datetime или datetime2, так как строки со значением полуночи будут храниться в той же секции, что и строки с последующими значениями в тот же день. Аналогичным образом, если используется тип данных даты и использование секций месяца или более, диапазон 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 февраля является первой точкой границы, определенной в функции, поэтому она выступает в качестве нижней границы секции 2.

Секция 1 2 ... 11 12
Значения datecol<2022-02-01 12:00AM datecol>= 2022-02-01 12:00AM AND datecol<2022-03-01 12:00AM datecol>= 2022-11-01 12:00AM AND col1<2022-12-01 12:00AM datecol>= 2022-12-01 12:00AM

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

Дополнительные примеры функций секционирования LEFT и RIGHT см. в разделе CREATE PARTITION FUNCTION (Transact-SQL).

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

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

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

Файловые группы

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

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

Примечание.

Секционирование не полностью поддерживается в База данных SQL Azure. Так как в База данных SQL Azure поддерживается только файловая PRIMARY группа, все секции должны размещаться в файловой PRIMARY группе.

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

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

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

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

Чтобы секционировать объект, укажите схему секционирования и столбец секционирования в инструкциях CREATE TABLE (Transact-SQL), ALTER TABLE (Transact-SQL) и CREATE INDEX (Transact-SQL).

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

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

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

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

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

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

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

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

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

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

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

Устранение секций

Процесс, в ходе которого оптимизатор запросов обращается только к определенным секциям в соответствии с фильтром запроса.

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

Ограничения

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

  • Если в секционированных таблицах есть NULLs в столбце секционирования, эти строки помещаются в левую часть секции. Однако если значение NULL указано в качестве первого значения границы и RANGE RIGHT указывается в определении функции секции, то левая часть секции остается пустой, а NULLs помещаются во вторую секцию.

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

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

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

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

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

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

В SQL Server и Управляемый экземпляр SQL Azure можно увеличить index create memory (KB) параметр конфигурации сервера. Дополнительные сведения см. в разделе "Настройка параметра конфигурации сервера памяти". Для База данных SQL Azure рассмотрите возможность временного или постоянного увеличения цели уровня обслуживания для базы данных в портал 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 , могут занять больше времени, чтобы выполнить по мере увеличения числа секций.

Запросы

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

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

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

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

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

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

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

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

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

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

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

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

В База данных SQL Azure Управляемый экземпляр SQL Azure и SQL Server 2012 (11.x) и более поздних версий статистика не создается путем сканирования всех строк в таблице при создании или перестроении секционированного индекса. Вместо этого оптимизатор запросов использует для создания статистики алгоритм выборки по умолчанию.

После обновления базы данных с секционированных индексов из версии SQL Server ниже 2012 (11.x) вы можете заметить разницу в данных гистограммы для этих индексов. Это изменение в поведении может повлиять на производительность запросов. Для получения статистики по секционированным индексам путем сканирования всех строк таблицы используйте инструкции CREATE STATISTICS или UPDATE STATISTICS с предложением FULLSCAN.

Связанный контент

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