какой оператор языка sql объединяет фрагменты в исходную таблицу при вертикальном секционировании

Секционирование в SQL Server

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

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

Там же перечислены основные преимущества:

Поговорим о стопроцентном преимуществе, которое вы однозначно получаете вместе с секционированием (но которым тоже нужно суметь воспользоваться) – это гарантированное повышение удобства управления вашей БД. Например, у вас есть таблица с миллиардом записей, из которых 900 миллионов относятся к старым («закрытым») периодам и используются только для чтения. С помощью секционирования вы можете вынести эти старые данные в отдельную файловую группу только для чтения, забэкапить её и больше не тащить их во все свои ежедневные бэкапы – скорость создания резервной копии возрастёт, а размер уменьшится. Вы можете перестраивать индекс не по всей таблице, а по выбранным секциям. Кроме того, вырастает доступность вашей БД – если одно из устройств, содержащих файловую группу с секцией, выйдет из строя, остальные будут по-прежнему доступны.

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

Во-первых, создадим базу с 4 файловыми группами, в которой будем проводить эксперименты:

Создадим таблицу, которую будем мучать.

И заполним её данными за один год:

Теперь таблица pTest содержит по одной записи за каждый час 2018-го года.

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

Мы будем секционировать нашу таблицу по столбцу dt (datetime) таким образом, чтобы каждая секция содержала в себе данные за 4 месяца (тут я облажался — на самом деле первая секция будет содержать данные за 3, вторая за 4, третья за 5 месяцев, но для целей демонстрации — это не проблема)

Вроде бы всё нормально, но здесь я сознательно допустил одну «ошибку». Если посмотреть синтаксис в msdn, то вы увидите, что при создании можно указывать к какой секции будет относиться указанная граница – к левой, или к правой. По умолчанию, по какой-то неведомой причине, указанная граница относится к «левой» секции, поэтому для моего случая корректно было бы создать функцию секционирования следующим образом:

В то время, как я, фактически, выполнил:

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

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

Как вы видите, все три наши секции будут лежать в разных файловых группах. Теперь пришло время секционировать нашу таблицу. Для этого нам нужно создать кластерный индекс и вместо указания файловой группы, в которой он должен располагаться, указать схему секционирования:

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

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

Таким образом, мы получили три не очень удачные секции – первая хранит данные с начала времён по 01.04.2018 00:00:00 включительно, вторая – с 01.04.2018 00:00:01 по 01.08.2018 00:00:00 включительно, третья с 01.08.2018 00:00:01 до конца света (доли секунд я сознательно упустил, потому что не помню с какой градацией SQL Server записывает эти доли, но смысл передан верно).
Теперь создадим некластерный индекс по полю dummy_int, «выровненный» по той же схеме секционирования.

И посмотрим, почему я говорил, что ваши запросы могут стать медленнее, после внедрения секционирования. Выполним запрос:

И посмотрим статистику выполнения:

Поскольку наш индекс «выровнен» по секциям, условно, на каждой секции создан свой собственный индекс, «не связанный» с индексами на других секциях. Условий на поле, по которому секционирован индекс, мы не наложили, поэтому SQL Server вынужден выполнять Index Seek в каждой секции, фактически, 3 Index Seek вместо одного.

Давайте попробуем исключить одну секцию:

И посмотрим статистику выполнения:

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

Некластерный индекс нам больше не нужен, поэтому я его удаляю

Теперь рассмотрим следующий сценарий: данные из этой таблицы мы каждые 4 месяца архивируем – убираем старые данные и добавляем секцию для следующих четырёх месяцев (организация «скользящего окна» описана в msdn и куче блогов).

Разобьём задачу на мелкие и понятные подзадачи:

1. Объявляем, что новая секция будет создана в файловой группе FG1, потому что она у нас скоро освободится:

И меняем функцию секционирования, добавляя новую границу:

Всего в таблице (кластерном индексе) 8809 страниц, так что количество чтений, конечно, за гранью добра и зла. Посмотрим, что у нас теперь есть по секциям.

В целом, всё как и ожидалось – появилась новая секция с верхней границей (помните, что граничные условия у нас относятся к левой секции) 01.01.2019 и пустая секция, в которой будут остальные данные, у которых дата больше.

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

2. Создаём stage-таблицу.

Для переключения (switch) секции в таблицу и обратно, нам требуется пустая таблица, в которой созданы все те же ограничения и индексы, что и на нашей секционированной таблице. Таблица должна быть в той же файловой группе, что и секция, которую мы хотим туда «переключить». Первая (архивная) секция лежит в FG1, поэтому создаём таблицу и кластерный индекс там же:

Секционировать эту таблицу не нужно.

3. Теперь мы готовы к переключению:

И вот, что мы получаем:

Забавно, посмотрим, что у нас в индексах:

Помните, я писал, что нужно было делать уникальный кластерный индекс на секционированной таблице? Вот именно поэтому и нужно было. При создании уникального кластерного индекса, SQL Server потребовал бы явного включения столбца, по которому мы секционируем таблицу, в индекс, а так он добавил его сам и забыл сказать об этом. И я правда не понимаю почему так.
Но, в общем, проблема понятна, пересоздаём кластерный индекс на stage-таблице.

И теперь ещё раз пробуем выполнить переключение секции:

Та-дам! Секция переключена, смотрим чего нам это стоило:

А ничего. Переключение секции в пустую таблицу и наоборот (полной таблицы в пустую секцию) – это операция исключительно над метаданными и это именно то, из-за чего секционирование — это очень и очень крутая штука.

Посмотрим, что там с нашими секциями:

А с ними всё здорово. В первой секции осталось ноль записей, они благополучно уехали в таблицу stageTest. Можем двигаться дальше

4. Всё, что нам осталось – это удалить нашу пустую первую секцию. Выполним и посмотрим, что произойдёт:

И это тоже операция только над метаданными, в нашем случае. Смотрим на секции:

У нас осталось, как и было, всего 3 секции, каждая в своей файловой группе. Миссия выполнена. Что можно было бы тут улучшить? Ну, во-первых, хотелось бы, чтобы граничные значения относились к «правым» секциям, чтобы секции содержали все данные за 4 месяца. И хотелось бы, чтобы создание новой секции обходилось меньшей кровью. Читать данных в десять раз больше, чем сама таблица – перебор.

С первым мы сделать сейчас ничего не можем, а вот со вторым – попробуем. Создадим новую секцию, которая будет содержать данные с 01.01.2019 по 01.04.2019, а не до конца времён:

Ха! То есть теперь это операция только над метаданными? Да, если вы «делите» пустую секцию – это операция только над метаданными, поэтому правильным решением будет держать и слева, и справа по гарантированно пустой секции и при необходимости выделения новой – «вырезать» их оттуда.

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

Ну неплохо, т.е. прочитали только левую секцию (которую делим) и всё. Окей. Чтобы переключить несекционированную непустую таблицу в секцию секционированной таблицы, на таблице-источнике обязательно нужны ограничения, чтобы SQL Server знал, что всё будет хорошо и переключение можно сделать как операцию над метаданными (а не читать всё подряд и проверять – подходит под условия секции или нет):

Опять-таки, операция только над метаданными. Смотрим, что там с нашими секциями:

Окей. Вроде разобрались. А теперь попробуем пересоздать функцию и схему секционирования (я удалил схему и функцию секционирования, пересоздал и перезаполнил таблицу и заново создал кластерный индекс по новой схеме секционирования):

Посмотрим, какие секции есть у нас сейчас:

Отлично, теперь у нас три «логичных» секции – с начала времен до 01.04.2018 00:00:00 (не включительно), с 01.04.2018 00:00:00 (включительно) по 01.08.2018 00:00:00 (не включительно) и третья, всё, что больше или равно 01.08.2018 00:00:00.

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

1. Добавляем новую секцию:

Неплохо, по крайней мере разумно – прочитали только крайнюю секцию. Смотрим, что там у нас по секциям:

Обратите внимание, что теперь, заполненная третья секция осталась на месте, в FG3, а новая пустая секция создалась в FG1.

2. Создаём stage-таблицу и ПРАВИЛЬНЫЙ кластерный индекс по ней

3. Переключаем секцию

Статистика говорит, что операция над метаданными:

Теперь уже всё без сюрпризов.

4. Убираем ненужную секцию

А вот тут нас ждёт сюрприз:

Смотрим, что там у нас с секциями:

И вот тут становится понятно: наша секция #2 переехала из файловой группы fg2 в файловую группу fg1. Класс. Можем ли мы с этим что-то сделать?

Можем, просто нам всегда надо иметь пустую секцию и «уничтожать» границу между «вечнопустой» левой секций и той секцией, которую мы «переключили» (switch) в другую таблицу.

В качестве заключения:

Источник

Partitioned Tables and Indexes

SQL Server поддерживает секционирование таблиц и индексов. Данные секционированных таблиц и индексов делятся на блоки, которые могут распределяться между несколькими файловыми группами в базе данных. Данные секционируются горизонтально, поэтому группы строк сопоставляются с отдельными секциями. Все секции одного индекса или таблицы должны находиться в одной и той же базе данных. Таблица или индекс рассматриваются как единая логическая сущность при выполнении над данными запросов или обновлений. До версии SQL Server 2016 (13.x); SP1 секционированные таблицы и индексы были доступны не в каждом выпуске SQL Server. Сведения о функциях, поддерживаемых различными выпусками SQL Server, см. в статье Возможности, поддерживаемые различными выпусками SQL Server 2016.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В База данных SQL Azure поддерживаются только первичные файловые группы.

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

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

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

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

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

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

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

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

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

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

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

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

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

Рекомендации относительно процессорных ядер и числа секций

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

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

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

Ограничения оперативной памяти могут повлиять на производительность SQL Server при построении секционированного индекса и даже на саму возможность его построения. Такое случается, например, когда индекс не выровнен со своей базовой таблицей или со своим кластеризованным индексом, если такой существует в таблице. В этом случае может оказаться полезным увеличить параметр конфигурации сервера index create memory. Дополнительные сведения см. в статье Настройка параметра конфигурации сервера index create memory.

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

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

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

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

Как для выровненных, так и для невыровненных индексов может потребоваться больший объем оперативной памяти, если SQL Server применяет степени параллелизма для выполнения данной операции на многопроцессорном компьютере. Чем больше степень параллелизма, тем больше требуется оперативной памяти. Например, если для SQL Server определена степень параллелизма 4, то невыровненному секционированному индексу, содержащему 100 секций, потребуется такой объем памяти, чтобы четыре процессора могли одновременно отсортировать по 4 000 страниц, то есть 16 000 страниц. Если секционированный индекс выровнен, требования оперативной памяти снижаются до 40 страниц для каждого из четырех процессоров, то есть 160 страниц (4*40). С помощью параметра индекса MAXDOP можно вручную снизить степень параллелизма.

Команды DBCC

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

Запросы

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

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

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

Дополнительные сведения об обработке секций при обработке запросов см. в статье Секционированные таблицы и индексы.

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

Связанные задачи

См. также

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

Источник