Меню

Что такое партиционированная таблица

Что такое партиционированная таблица

Пить пиво легко. Громить отельные номера легко. А вот что действительно трудно – это быть отзывчивым. Это настоящий бунт.

«Alice Cooper»

Свежачёёёк

  • Installing Gnome 3 on Ubuntu 12.04
  • Count nginx process open files
  • Boost Error TIME_UTC is a macro in C11
  • Terminator
  • Выполнить команду над всеми папками BASH
  • Рубрики

    • AMQP
      • RabbitMQ
    • DataBase
      • Hypertable
      • Memcached
      • MongoDB
      • MSSQL
      • Mysql
      • OLAP
      • Oracle
      • PostgreSQL
      • Sphinx
    • FreeBSD
    • HighLoad
    • Instant messenger
    • Linux
      • Apache
      • BIND
      • DHCP
      • HAProxy
      • Iptables
      • Nginx
      • Samba
      • SQUID
      • Zabbix
    • NetWork
    • PM
      • Redmine
    • Programming
      • C++
      • JavaScript
      • PHP
      • Python
        • Celery
        • Django
      • SVN
    • SEO
    • VOIP
      • Asterisk
    • Web
      • Панели
    • Windows
      • Hyper-V
      • IIS
      • Terminal Server
    • Work
    • Архитектура
      • Алгоритмы
    • Документация
      • Man
    • Юмор
  • Ссылки

    Календарег

    Июль 2021

    Пн Вт Ср Чт Пт Сб Вс
    « Дек
    1 2 3 4
    5 6 7 8 9 10 11
    12 13 14 15 16 17 18
    19 20 21 22 23 24 25
    26 27 28 29 30 31

    Партиционирование (partitioning) таблиц

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

    Это позволяет базе данных делать интеллектуальную выборку — сначала СУБД уточнит, какой партиции соответствует Ваш запрос (если это реально) и только потом сделает этот запрос, применительно к нужной партиции (или нескольким партициям). Таким образом, Вы распределите нагрузку на таблицу по ее партициям. Следовательно выборка типа “SELECT * FROM articles ORDER BY id DESC LIMIT 10” будет выполняться только над последней партицией, которая значительно меньше всей таблицы.

    Многие СУБД поддерживают партиционирование на том или ином уровне, например:

    • Mysql — реализовано на СУБД версии >= 5.1
    • Postgres
    • MSSQL — реализовано на СУБД версии >= 2005
    • Oracle — реализовано на СУБД версии >= 8

    Основные способы деления таблицы на партиции поддерживаемые всеме вышеизложеными СУБД это:

      RANGE — По диапазону значений

    Парционирование по диапазону (range partitioning) означает распределение строк таблицы на различные предопределенные табличные пространства в зависимости от значения ключа секционирования. Доступ к такой таблице, как и к любой другой, осуществляется по ее имени, причем доступ к секциям, расположенным в каждом табличном пространстве, можно получить отдельно.
    LIST — По списку значений

    Допустим вы пишите трекинговую систему по сбору информации с какихто ваших проэктов, количество проэктов не велико, а данных много. Можно задать парционирование по ID проэкта. Либо вы имеете какойто каталог с фиксированым набором категорий. Вариантов много 🙂
    HASH — По хещ значению

    Хэш-партиционирование (hash partitioning) означает равномерное распределение строк таблицы по назначенным табличным пространствам в зависимости от значения ключа секционирования, который в данном случае хэшируется. Этот вид секционирования удобно применять для строк, у которых распределение значений ключа секционирования неравномерно или плохо группируется.

    Источник

    

    Секционирование таблиц в Oracle на практике

    Oracke Database: секционирование таблиц

    Одним из эффективных способов повышения производительности работы баз данных является секционирование таблиц и индексов. Рассмотрим разные методы секционирования и их особенности

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

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

    Задачи, решаемые секционированием

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

    • Первой и наиболее часто решаемой задачей при секционировании является повышение производительности работы SQL-запросов и DML-операций по модификации строк таблицы. Это достигается за счет того, что поиск и модификация строк в таблице идут не по всей таблице, а только в ее части (в одной или нескольких секциях). Кроме того, разбиение таблицы на секции позволяет увеличит скорость обработки таблицы за счет использования параллелизма.
    • Вторая задача, которая нашла широкое применение в нашей организации, — это быстрое удаление значительного числа строк в больших таблицах за счет выполнения операции truncate секций. Другим широким применением секционирования является освобождение табличного пространства, занимаемого таблицей, после удаления строк из таблицы командой delete. Использование команд Shrink (сжатие таблицы) или Move (перемещение в табличное пространство) для освобождения табличного пространства в большой несекционированной таблице может занимать значительное время. В секционированных таблицах выполнение таких команд в пределах секции будет выполниться существенно быстрее.
    • Третьей задачей секционирования является разбиение большой таблицы на оперативную и архивную части. Особенно это эффективно, если оперативная часть в виде секции интенсивно пополняется и модифицируется, а архивная часть (секции) менее подвержена изменениям, и существенно реже из нее извлекается информация. Строки таблицы из оперативной секции со временем могут быть переведены в архивные секции, при этом архивные секции могут периодически очищаться.
    • Четвертой задачей является существенное снижение конкуренции за строки и индексы таблицы, в том числе уменьшения вероятности блокировок. Так в результате секционирования одной из таблиц по HASH-методу полностью была решена задача множественных блокировок, возникающих в таблице.
    • Пятой задачей является обеспечение устойчивости функционирования таблиц. Поскольку секция — это поименованный самостоятельный фрагмент памяти на дисках, то при возникновении проблем в одних секциях другие продолжают успешно функционировать. Устойчивости функционирования способствует также хранение секций в различных табличных пространствах и на различных физических носителях. Это особенно важно для таблиц, которые обеспечивают работу множества других таблиц (например. справочники, к которым идет интенсивное обращение). Кроме того, секционирование позволяет осуществлять независимое копирование и резервирование секций, оперативное восстановление секций, а также возможности более быстрой и более частой перестройки индексов наиболее активной секции, не затрагивая индексы пассивных секций.

    Ключ секционирования

    Следующим важным шагом в создании секционированной таблицы является определение ключа секционирования. В качестве ключа секционирования может выступить столбец или несколько столбцов, относительно значений которых будет делаться разнесение таблицы на секции. К потенциальным столбцам для создания ключа секционирования относятся столбцы типа date (например, столбец created — дата создания строки или updated — дата изменения строки) для секционирования по методам Range и List . Столбцы типа number с высокой степенью уникальности значений хорошо подходят для секционирования по методам Range и Hash . Столбцы, имеющие список фиксированных значений, подходят для секционирования по списку List .

    В Oracle 11g появилась возможность в качестве ключа секционирования использовать виртуальный столбец (virtual column), построенный на функции к реальному столбцу таблицы. Виртуальный столбец в действительности не хранится в таблице, а каждый раз вычисляется при обращении к нему во время ввода данных в таблицу. Для создания виртуального столбца используется фраза generated always as. после которой идет функция, выполняемая над реальным столбцом таблицы, а далее идет обязательная фраза virtual. Например, PARTID generated always AS (to_char(UPDATED,’MM’)) virtual . Возможен вариант создания виртуального столбца более короткой фразой PARTID AS (to_char(UPDATED,’MM’)) .

    Увидеть, какой столбец в таблице виртуальный позволяет запрос:

    Замечание. При вводе данных в таблицу с виртуальным столбцом следует указать в insert и values перечень столбцов, иначе будет ошибка ORA-00947: not enough value .

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

    Секционирование повышает эффективность работы с таблицами и индексами

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

    • Range -секционирование по диапазону ключа,
    • List — секционирование по списку ключа.
    • Hash — хеш-секционирование,
    • составное секционирование.
    • интервальное секционирование.
    • ссылочное секционирование,
    • системное секционирование

    Последние три появились в Oracle 11g. вместе с тем последние два у нас пока не нашли большого применения.

    Секционирование методом Range по диапазону ключа

    В практике секционирования по методу Range используем два вида секционирования: по диапазону дат и по диапазону значений.

    Секционирование методом Range по диапазону дат

    При секционировании этим методом нами используются секционирование по дням, месяцам и по годам. Секционирование этим методом покажем на примере таблицы HISTLG в схеме AIF. Ключом секционирования выступает столбец updated (дата корректировки строки), при этом секции создаются с шагом секций в один месяц. Команда создания секционированной таблицы create имеет вид:

    В команде CREATE указаны табличное пространство TABLESPACE HSTDATA, в котором будет находиться таблица, метод секционирования и ключ секционирования PARTITION BY RANGE (UPDATED) , имена секций и максимальное значение диапазона ключевого столбца этой секции. Например, первая секция PARTITION PARTMM_2015_01 VALUES LESS THAN TO_DATE(‘01.01.20157DD.MM.YYYY’) говорит о том, что все значения столбца update меньше 01.01.2015 попадут в первую секцию, а значения update меньше 01.02.2015 попадут во вторую секцию и т.д. В таблице создана последняя секция PARTITION PARTMM_MAX VALUES LESS THAN (MAXVALUE) , позволяющая при превышении значения ключа значения диапазона предпоследней секции размещать строки таблицы в эту последнюю секцию (это подстраховка на случай, если забыли создать новую секцию). Фраза COMPRESS определяет, что первая секция будет сжата.

    Следует обратить особое внимание на последнюю фразу ENABLE ROW MOVEMENT , которая позволяет переходить строкам таблицы из секции в секцию. В отсутствии этой фразы Oracle выдаст ошибку. Переход строк по секциям может происходить автоматически при изменении значения ключа (например, столбец updated в результате операции update изменит значение на то. при котором он должен уже принадлежать другой секции) или может происходить специально, например, для перевода строк из оперативной секции в архивную секцию путем изменения значения ключевого столбца. Если не указали эту фразу при создании таблицы, то, чтобы избежать ошибки, следует выполнить команду ALTER TABLE ИМЯ ТАБЛИЦЫ ENABLE ROW MOVEMENT .

    Увидеть секции таблицы можно по запросу:

    А содержимое секции по запросу:

    Секционирование методом RANG по диапазону значений

    Секционирование по диапазону значений похоже на секционирование по диапазону дат, только вместо ключа по дате используется ключ по столбцу, принимающему числовое значение (желательно имеющее равномерное распределение по всему диапазону значений). Для этого хорошо подходит столбец с уникальным значением. Рассмотрим на примере той же таблицы AIF.HISTLG, секционированной выше по диапазону дат. В качестве ключа секционирования используется столбец ISN с уникальными значениями. Команда создания таблицы имеет вид:

    секционированные таблицы (PARTITION) в базе данных Oracle

    где PARTITION BY RANGE (ISN) говорит о секционировании no RANGE при ключе секционирования ISN, интервал создания секции через 1000 значений.

    Создание новой секции в секционированной таблице по методу Range

    Каждый раз при создании секционированной таблицы возникает непростой вопрос: как создавать новые секции. До Oracle 11g было три варианта создания новой секции.

    Первый вариант — это в команде create таблицы вручную создается множество секций (например, на несколько лет вперед). Однако, как показала практика, этот метод приводит к тому, что через несколько лет о том, что таблица была секционирована, могут забыть. Когда об этом вспоминают, то оказывается, что информация длительное время пишется в одну и ту же последнюю секцию THAN (MAXVALUE) . В результате секционированная таблица практически превратилась в обычную таблицу. В этой ситуации надо либо снова создавать новую секционированную таблицу, либо по команде Split разбивают последнюю секцию на несколько секций. Например, для таблицы AIF.HISTLG (секционированной по дате) команда Split по созданию новой секции PARTMM_2016_01 на основе расщепления последней PARTMM.MAX секции имеет вид:

    Фраза UPDATE GLOBAL INDEXES обеспечивает исправность индексов после команды Split.

    Второй вариант — создать процедуру, которая автоматически образует новую секцию. Такая универсальная процедура для секционирования по дням и месяцам была нами разработана. Данная процедура запускается Job Sheduler ежедневно для секционирования по дням или ежемесячно для секционирования по месяцам.

    Данные процедуры успешно работают уже несколько лет, своевременно создавая новые секции. Основой процедуры являются представление ALL_TAB_PARTITIONS ДЛЯ поиска последней секции таблицы и команда Split для расщепления этой секции по команде ALTER , указанной выше.

    Третий вариант (разработан нашими специалистами и успешно применяется в течение несколько лет) — это создание секционированной таблицы с секциями, используемыми по циклу. Под секционированием таблиц по циклу понимаются секционирование, выполненное в соответствии с двумя правилами. Первое правило — таблица должна содержать фиксированное количество секций, равное либо максимальному числу дней в месяце (31 секция), либо максимальному число дней в году (366 секций), либо числу месяцев в году (12 секций). Второе правило: данные в одну и ту же секцию попадают с определенной периодичностью (цикличностью).

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

    В Oracle 11g появилась новая замечательная возможность автоматического создания секций с использованием при создании таблицы фразы INTERVAL (такой подход называется интервальное секционирование Interval Partitioning). Тогда при создании секций методом Range по интервалу дат с использованием фразы Interval команда создания секционированной таблицы примет вид:

    где фраза INTERVAL (INTERVAL ‘1’ MONTH) указывает, что секции будут автоматически создаваться каждый месяц (та же фраза может иметь вид INTERVAL (NUMTOYMINTERVAL (1. ‘MONTH’) . Для секционирования по дням используется фраза INTERVAL (INTERVAL ‘1’ DAY) , а по годам — INTERVAL (INTERVAL ‘1’ YEAR) . При автоматическом создании секций методом Range по интервалу значений с использованием фразы Interval команда создания таблицы примет вид:

    где фраза INTERVAL(1000) задает режим автоматического создания секции через 1000 значений ISN.

    Следует учесть, что новые секции создаются в процессе ввода данных. Следует также иметь в виду, что имя новой автоматически создаваемой секции будет иметь вид SYS_PNNNNN, например, SYS_P28981. При этом при интервальном секционировании не нужно создавать последнюю секцию VALUES LESS THAN (MAXVALUE) . иначе появится ошибка ORA-14761.

    Таким образом, в Oracle 11g у команды create создания секционированной таблицы существенно меньшее число строк, а о создании новой секции своевременно позаботится Oracle.

    Секционирование по списку ключей LIST

    Секционирование по списку применяется, если есть возможность указать конкретный перечень дискретных значений столбца, по которому происходит разбиение на секции. При секционировании по LIST в команде create указываются метод секционирования LIST (PARTITION BY LIST) , ключ секционирования и имена секций, в которых указывается одно или несколько дискретных значений.

    В качестве примера проведем секционирование таблицы AIF.AGREEM. используя в качестве ключа виртуальный столбец partid. При каждом вводе строки в таблицу в виртуальном столбце формируется числовой номер месяца по функции to_number(to_char(updated,’MM’)) . Таблицу разбиваем на 12 секций, кроме того, используем подход секционирования по циклу, когда в следующем году строки января вводятся в ту же секцию января, а перед этим секция за январь чистится от старых данных по delete или по truncate. Команда создания секции примет вид:

    Вместо виртуального столбца может быть введен реальный столбец partid (тип number) , заполняемый при вводе строки в таблицу. Указанный выше вариант эффективно использовался в таблицах как с 366 секциями, так и с 12 с очисткой последних по truncate, поскольку информация в таблицах хранится меньше года. Достоинство этого подхода в том, что создавать новые секции не приходится, а табличное пространство старых секций ежемесячно быстро освобождается по truncate .

    Замечание. Если необходимо очистить табличное пространство секции, то используются либо команды сжатия SHRINK , либо MOVE (перемещения в табличное пространство):

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

    Хеш-секционирование HASH

    Как правило, если не получается секционировать по диапазону RANGE или LIST , то применяется хешсекционирование, основанное на хеш-функции. В этом случае строки таблицы равномерно распределяются между секциями на основании внутренних алгоритмов хеширования Oracle. При этом чем уникальнее значения столбца в таблице, по которому идет секционирование, тем лучше будет распределение данных по разделам. Первичный ключ или уникальный столбец (столбцы) является самым хорошим хеш- ключом. Oracle рекомендует число секций N как степень 2, т.е. N=2,4,8,16,32 и т.д. При этом добавление или удаление какой-то хеш-секции вызывает перезапись всех данных в другие секции. Рассмотрим HASH секционирование на примере индексноорганизованной таблицы LISTIN. Целью HASH секционирования таблицы было добиться существенного снижение числа блокировок, возникающих в этой таблице. Эта цель была успешно реализована за счет секционирования таблицы по 16 секциям (фраза PARTITIONS 16 ), где ключом секционирования выступал столбец TASKISN. Команда создания таблицы имеет вид:

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

    Замечание. Увидеть размер секций в mb по всем указанным выше методам можно по запросу:

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

    При составном секционировании внутри секции создаются подсекции Однако в версиях до Oracle 11g смешанное секционирование разрешалось только по RANGE методу для секции и методам HASH или LIST для подсекции. В Oracle 11g варианты методов секций-подсекций были существенно расширены, и в настоящее время можно осуществлять составное секционирование в следующих комбинациях: Range-Range, Range-Hash , Range-List, List-Range, List-Hash или Ust-List. Надо отметить, что при составном секционировании данные физически хранятся в подсекциях, а секции высту-пают только в роли логических контейнеров.

    Рассмотрим смешанное секционирование на примере таблицы платежей AIF.PAY_ORD_RECORD с делением таблицы на секции по методу RANGE , а на подсекции по методу LIST . Ключом секционирования по секциям выступает столбец PAY_DATA (тип date), а ключом секционирования подсекции выступает столбец STATUS (тип number), принимающий три значения: 0. 1,2. Команда создания секционированной таблицы в Oracle 11g с секционированием по месяцам примет вид:

    где разбиение по секциям задает фраза PARTITION BY RANGE (PAY_DATA) , а по подсекциям фраза SUBPARTITION BY LIST (STATUS) . Далее идет список подсекций со своими значениями: STATUS_0 VALUES (0) . STATUSJ VALUES (1) . STATUSJ? VALUES (2) . Для каждой подсекции может быть задано свое табличное пространства, которое может отличаться от табличного пространства таблицы HSTDATA. С гомощью предложения SUBPARTITION TEMPLATE один и тот же набор подсекций будет автоматически использоваться во всех секциях. Однако создание подсекций можно сделать вручную, указав все подсекции для каждого секции. Просмотреть созданные подсекции по имени таблицы можно по запросу:

    Системное секционирование (system partitioning)

    Появилось в Oracle 11 g и применяется, как правило, для таблиц, которые не могут быть секционированы никакими другими методами. В этом методе Oracle сам управляет, какую строку таблицы в какую секцию помещать. Для этого метода необходимо просто написать название секций, например, секции Р1, Р2, РЗ:

    Увидеть разбиение таблицы на секции можно по запросу:

    Увидеть метод секционирования, что он именно SYSTEM , можно по запросу:

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

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

    Источник

    Partitioned Tables and Indexes

    Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure

    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 ) или нескольких файловых группах. При работе с многоуровневым хранилищем использование нескольких файловых групп позволяет назначать определенные секции определенным уровням хранилища. Все прочие преимущества секционирования применяются независимо от количества используемых файловых групп или размещения секций в определенных файловых группах.

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

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

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

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

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

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

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

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

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

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

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

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

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

    При секционировании кластеризованного индекса столбец секционирования должен содержаться в ключе кластеризации. При секционировании неуникального кластеризованного индекса, если столбец секционирования не указан явно в ключе кластеризации, 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 может занимать больше времени по мере увеличения количества секций.

    Запросы

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

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

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

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

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

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

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

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

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

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

    Задания Раздел
    Описано, как создать функции секционирования и схемы секционирования и применить их к таблице или индексу. Создание секционированных таблиц и индексов

    См. также

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

    Источник

    IT technologies in our life

    Пить пиво легко. Громить отельные номера легко. А вот что действительно трудно – это быть отзывчивым. Это настоящий бунт.

    «Alice Cooper»

    Свежачёёёк

    • Installing Gnome 3 on Ubuntu 12.04
    • Count nginx process open files
    • Boost Error TIME_UTC is a macro in C11
    • Terminator
    • Выполнить команду над всеми папками BASH
  • Рубрики

    • AMQP
      • RabbitMQ
    • DataBase
      • Hypertable
      • Memcached
      • MongoDB
      • MSSQL
      • Mysql
      • OLAP
      • Oracle
      • PostgreSQL
      • Sphinx
    • FreeBSD
    • HighLoad
    • Instant messenger
    • Linux
      • Apache
      • BIND
      • DHCP
      • HAProxy
      • Iptables
      • Nginx
      • Samba
      • SQUID
      • Zabbix
    • NetWork
    • PM
      • Redmine
    • Programming
      • C++
      • JavaScript
      • PHP
      • Python
        • Celery
        • Django
      • SVN
    • SEO
    • VOIP
      • Asterisk
    • Web
      • Панели
    • Windows
      • Hyper-V
      • IIS
      • Terminal Server
    • Work
    • Архитектура
      • Алгоритмы
    • Документация
      • Man
    • Юмор
  • Ссылки

    Календарег

    Июль 2021

    Пн Вт Ср Чт Пт Сб Вс
    « Дек
    1 2 3 4
    5 6 7 8 9 10 11
    12 13 14 15 16 17 18
    19 20 21 22 23 24 25
    26 27 28 29 30 31

    Партиционирование (partitioning) таблиц

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

    Это позволяет базе данных делать интеллектуальную выборку — сначала СУБД уточнит, какой партиции соответствует Ваш запрос (если это реально) и только потом сделает этот запрос, применительно к нужной партиции (или нескольким партициям). Таким образом, Вы распределите нагрузку на таблицу по ее партициям. Следовательно выборка типа “SELECT * FROM articles ORDER BY id DESC LIMIT 10” будет выполняться только над последней партицией, которая значительно меньше всей таблицы.

    Многие СУБД поддерживают партиционирование на том или ином уровне, например:

    • Mysql — реализовано на СУБД версии >= 5.1
    • Postgres
    • MSSQL — реализовано на СУБД версии >= 2005
    • Oracle — реализовано на СУБД версии >= 8

    Основные способы деления таблицы на партиции поддерживаемые всеме вышеизложеными СУБД это:

      RANGE — По диапазону значений

    Парционирование по диапазону (range partitioning) означает распределение строк таблицы на различные предопределенные табличные пространства в зависимости от значения ключа секционирования. Доступ к такой таблице, как и к любой другой, осуществляется по ее имени, причем доступ к секциям, расположенным в каждом табличном пространстве, можно получить отдельно.
    LIST — По списку значений

    Допустим вы пишите трекинговую систему по сбору информации с какихто ваших проэктов, количество проэктов не велико, а данных много. Можно задать парционирование по ID проэкта. Либо вы имеете какойто каталог с фиксированым набором категорий. Вариантов много 🙂
    HASH — По хещ значению

    Хэш-партиционирование (hash partitioning) означает равномерное распределение строк таблицы по назначенным табличным пространствам в зависимости от значения ключа секционирования, который в данном случае хэшируется. Этот вид секционирования удобно применять для строк, у которых распределение значений ключа секционирования неравномерно или плохо группируется.

    Источник

  • Читайте также:  Основные этапы эволюции растительного и животного мира таблица
    Adblock
    detector