Техническое обслуживание разбиения

Введение

В этой статье рассматриваются

Использование и неиспользование разбиения (PARTITIONing)
Как поддерживать таблицу с временными рядами, разбитую по разделам (PARTITIONed)
Секреты AUTO_INCREMENT

Во-первых, мои соображения по поводу разбиения (PARTITIONing)

Взято из Правил большого пальца Рика (Rick's RoTs)

#1: Не используйте разбиение (PARTITIONing), пока не поймете, как и почему оно может помочь.
Не используйте разбиение (PARTITION), пока у вас не будет более 1 млн. строк.
Не используйте более 50 разделов (PARTITION) в таблице (открытие, показ состояния таблицы и т. п. затронуты) (исправлено в MySQL 5.6.6?; лучшее исправление в конечном итоге в 5.7)
Разбиение по диапазону (PARTITION BY RANGE) — единственный полезный метод.
Подразделения (SUBPARTITION) бесполезны.
Поле разбиения не должно быть первым полем в любом ключе.
Можно использовать AUTO_INCREMENT в качестве первой части составного ключа или в не-УНИКАЛЬном индексе.

Так заманчиво поверить, что разбиение (PARTITIONing) решит проблемы производительности. Но часто это ошибочно.

Разбиение (PARTITIONing) разбивает одну таблицу на несколько меньших таблиц. Но размер таблицы редко является проблемой производительности. Вместо этого проблемами являются время ввода-вывода и индексы.

Распространённое заблуждение: «Разбиение сделает мои запросы быстрее». Нет. Подумайте, что требуется для «запроса по точке». Без разбиения, но с соответствующим индексом, есть дерево B-дерево (индекс), чтобы проиндексировать поиск нужной строки. Для миллиарда строк это может быть глубиной 5 уровней. С разбиением сначала выбирается и «открывается» раздел, а затем просматривается более мелкое B-дерево (скажем, 4 уровня). Ну, выгода от более мелкого B-дерева компенсируется необходимостью открыть раздел. Аналогично, если вы посмотрите на блоки диска, которые нужно коснуться, и какие из них скорее всего будут кэшированы, вы придете к выводу, что количество обращений к диску, вероятно, примерно одинаковое. Поскольку обращения к диску являются основной стоимостью в запросе, разбиение не даёт прироста производительности (по крайней мере, для этого типичного случая). Двумерный случай (ниже) даёт основное противоречие этому обсуждению.

Сценарии использования разбиения (PARTITIONing)

Сценарий использования №1 — временной ряд. Возможно, наиболее распространённый случай, где разбиение (PARTITIONing) блещет, — в наборе данных, где «старые» данные периодически удаляются из таблицы. Разбиение по диапазону (RANGE PARTITIONing) по дням (или другой временной единице) позволяет выполнять почти мгновенное удаление раздела (DROP PARTITION) и переорганизацию раздела (REORGANIZE PARTITION) вместо гораздо более медленного удаления (DELETE). Большая часть этого блога посвящена этому сценарию использования. Этот сценарий также обсуждается в Большие удаления (Big DELETEs)

Большой выигрыш в случае №1: удаление раздела (DROP PARTITION) намного быстрее, чем удаление большого количества строк (DELETE).

Сценарий использования №2 — 2-мерный индекс. Индексы по своей природе одномерны. Если вам нужны два «диапазона» в предложении WHERE, попробуйте перенести один из них в разбиение.

Поиск 10 ближайших пиццерий на карте требует 2-мерного индекса. Обрезка по разделам (partition pruning) как бы даёт второе измерение. См. Индексация широты/долготы, которая использует разбиение по диапазону (PARTITION BY RANGE(latitude)) вместе с первичным ключом (PRIMARY KEY(longitude, ...))

Большой выигрыш в случае №2: просмотр меньшего количества строк.

Сценарий использования №3 — горячая точка. Это немного сложно объяснить. Учитывая это сочетание:

Индекс таблицы слишком велик, чтобы быть кэшированным, но индекс для одного раздела кэшируемый, и
Индекс случайным образом обращается, и
Ввод данных обычно будет ограничен временем ввода-вывода из-за обновления индекса. Разбиение может хранить весь индекс «горячим» в ОЗУ, тем самым избегая большого количества ввода-вывода.

Большой выигрыш в случае №3: повышение кэширования для уменьшения ввода-вывода, чтобы ускорить операции.

AUTO_INCREMENT в разбиении (PARTITION)

Для работы AUTO_INCREMENT (в любой таблице) он должен быть первым полем в каком-либо индексе. Точка. Нет других требований к индексации.
Быть первым полем в каком-либо индексе позволяет движку найти следующее значение при открытии таблицы.
AUTO_INCREMENT не обязательно должен быть УНИКАЛЬНЫМ. Что вы теряете: предотвращение явного вставки дублирующего id. (В любом случае, это редко нужно.)

Примеры (где id — AUTO_INCREMENT):

PRIMARY KEY (...), INDEX(id)
PRIMARY KEY (...), UNIQUE(id, partition_key) — бесполезно
INDEX(id), INDEX(...) (но без УНИКАЛЬНЫХ ключей)
PRIMARY KEY(id), ... — работает только если id — ключ разбиения (не очень полезно)

Техническое обслуживание разбиения для случая временного ряда

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

У вас есть большая таблица, которая растёт с одного конца и обрезается с другого. Примеры включают новости, журналы и другую транзиторную информацию. Разбиение по диапазону (PARTITION BY RANGE) — отличный инструмент для такой таблицы.

Удаление раздела (DROP PARTITION) намного быстрее, чем удаление (DELETE). (Это главная причина для выполнения этого вида разбиения.)
Запросы часто ограничиваются «недавними» данными, тем самым используя «обрезку по разделам» (partition pruning).

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

Нет простого оператора SQL для «удаления разделов, старше 30 дней» или «добавления нового раздела на завтра». Было бы утомительно делать это вручную каждый день.

Общий вид кода

ALTER TABLE tbl
    DROP PARTITION from20120314;
ALTER TABLE tbl
    REORGANIZE PARTITION future INTO (
        PARTITION from20120415 VALUES LESS THAN (TO_DAYS('2012-04-16')),
        PARTITION future     VALUES LESS THAN MAXVALUE);

После чего у вас есть...

    CREATE TABLE tbl (
        dt DATETIME NOT NULL,  -- or DATE
        ...
        PRIMARY KEY (..., dt),
        UNIQUE KEY (..., dt),
        ...
    )
    PARTITION BY RANGE (TO_DAYS(dt)) (
        PARTITION start        VALUES LESS THAN (0),
        PARTITION from20120315 VALUES LESS THAN (TO_DAYS('2012-03-16')),
        PARTITION from20120316 VALUES LESS THAN (TO_DAYS('2012-03-17')),
        ...
        PARTITION from20120414 VALUES LESS THAN (TO_DAYS('2012-04-15')),
        PARTITION from20120415 VALUES LESS THAN (TO_DAYS('2012-04-16')),
        PARTITION future       VALUES LESS THAN MAXVALUE
    );

Почему?

Возможно, вы заметили некоторые странности в примере. Позвольте мне их объяснить.

Имена разделов: сделайте их полезными.
from20120415 ... 04-16: Обратите внимание, что МЕНЬШЕ, чем следующая дата.
Раздел «начало»: см. абзац ниже.
Раздел «будущее»: он обычно пуст, но может перехватывать переполнения; подробнее позже.
Ключ диапазона (dt) должен быть включён в любой первичный или уникальный ключ.
Ключ диапазона (dt) должен быть последним в любых ключах, в которых он находится — Вы уже отсеяли его; он почти бесполезен в индексе, особенно в начале.
DATETIME и т. д. — я выбрал этот тип данных, потому что он типичен для временных рядов. Более новые версии MySQL допускают TIMESTAMP. Можно использовать INT и т. д.
Есть дополнительный день (03-16 по 04-16): Последний день заполнен лишь частично.

Зачем нужен фиктивный раздел «начало»? Если используется недопустимая дата (31 февраля), дата превратится в NULL. NULL помещаются в первый раздел. Поскольку любой SELECT может иметь недействительную дату (да, это чрезмерно), средство обрезки по разделам всегда включает первый раздел в результирующий набор разделов для поиска. Таким образом, если SELECT должен просмотреть первый раздел, было бы немного эффективнее, если бы этот раздел был пустым. Отсюда и фиктивный раздел «начало». Более подробное обсуждение, представленное The Data Charmer 5.5, устраняет фиктивную проверку, но только если вы перейдете на новый синтаксис:

    PARTITION BY RANGE COLUMNS(dt) (
    PARTITION day_20100226 VALUES LESS THAN ('2010-02-27'), ...

Подробнее о разделе «будущее». Рано или поздно задача cron/событие (EVENT) добавления раздела на завтра не будет выполняться. Худшее, что может произойти, — потеря данных на завтра. Самый простой способ предотвратить это — иметь раздел, готовый перехватить их, даже если этот раздел обычно всегда пуст.

Наличие раздела «будущее» немного усложняет скрипт добавления раздела. Вместо этого он должен взять данные на завтра из раздела «будущее» и поместить их в новый раздел. Это делается с помощью команды REORGANIZE, показанной. Обычно ничего не нужно перемещать, и изменение занимает практически ноль времени.

Когда выполнять ALTER?

Удалить (DROP), если самый старый раздел «слишком стар».
Добавить «завтра» около конца сегодняшнего дня, но не пытайтесь добавить его дважды.
Не считайте разделы — есть два дополнительных. Используйте имена разделов или информацию_схемы.РАЗДЕЛЫ.ОПИСАНИЕ_РАЗДЕЛА.
Удалять/Добавлять только один раз в скрипте. Запускайте скрипт снова, если вам нужно больше.
Запускайте скрипт чаще, чем необходимо. Для ежедневных разделов запускайте скрипт дважды в день или даже каждый час. Зачем? Автоматический ремонт.

Варианты

Как я уже говорил много раз, во многих местах, BY RANGE, пожалуй, единственный полезный вариант. И временной ряд — самое распространённое использование разбиения (PARTITIONing).

(как обсуждалось здесь) DATETIME/DATE с TO_DAYS()
DATETIME/DATE с TO_DAYS(), но с интервалами в 7 дней
TIMESTAMP с TO_DAYS(). (версия 5.1.43 или выше)
PARTITION BY RANGE COLUMNS(DATETIME) (5.5.0)
PARTITION BY RANGE(TIMESTAMP) (версия 5.5.15 / 5.6.3)
PARTITION BY RANGE(TO_SECONDS()) (5.6.0)
INT UNSIGNED со значениями, вычисленными как unix-временные метки.
INT UNSIGNED со значениями для некоторых не временных рядов.
MEDIUMINT UNSIGNED, содержащий «идентификатор часа»: FLOOR(FROM_UNIXTIME(timestamp) / 3600)
Месяцы, кварталы и т. д.: придумайте обозначение, которое работает.

Сколько разделов?

Меньше, скажем, 5 разделов — вы получите очень мало преимуществ.
Более, скажем, 50 разделов, и вы столкнетесь с неэффективностью в другом месте.
Некоторые операции (SHOW TABLE STATUS, открытие таблицы и т. д.) открывают каждый раздел.
MyISAM до версии 5.6.6 блокировал все разделы перед обрезкой!
Обрезка по разделам не происходит при INSERT (до версии 5.6.7), поэтому INSERT нужно открыть все разделы.
Возможный сценарий использования 2-раздела: http://forums.mysql.com/read.php?24,633179,633179
8192 разделов — жёсткий предел (1024 до 5.6.7).
До «родных разделов» (5.7.6) каждый раздел потреблял часть памяти.

Подробный код

Реализация ссылки, на Perl, с демонстрацией ежедневных разделов

Сложность кода заключается в обнаружении имён разделов, особенно самого старого и «следующего».

Чтобы запустить демонстрацию,

Установите Perl и DBIx::DWIW (из CPAN).
Скопируйте текстовый файл (ссылка выше) в demo_part_maint.pl
Выполните perl demo_part_maint.pl, чтобы получить остальную часть инструкций

Программа сгенерирует и выполнит (при необходимости) один из этих:

   ALTER TABLE tbl REORGANIZE PARTITION
        future
   INTO (
        PARTITION from20150606 VALUES LESS THAN (736121),
        PARTITION future VALUES LESS THAN MAXVALUE
   )

   ALTER TABLE tbl
                    DROP PARTITION from20150603

Послесловие

Исходное написание — окт. 2012; Добавлены сценарии использования: окт. 2014; Обновлено: июнь 2015; 8.0: сент. 2016

Слайды с Перкона Амстердам 2015

Разбиение (PARTITIONing) требует как минимум MySQL 5.1

Советы в этом документе применимы к MySQL, MariaDB и Percona.

Будущее (как представлялось в 2016 году):

MySQL 5.7.6 имеет "родное разбиение для InnoDB".
Поддержка FOREIGN KEY, возможно, в более поздней версии 8.0.xx.
"ГЛОБАЛЬНЫЙ ИНДЕКС" — это позволит избежать необходимости размещения ключа разбиения в каждом уникальном индексе, но сделает операцию DROP PARTITION более ресурсоемкой. Это будет в более отдаленном будущем.

MySQL 8.0, выпущенный в сентябре 2016 года, пока не является GA)

Разбивать можно только таблицы InnoDB — MariaDB, вероятно, продолжит поддерживать разбиение таблиц, не являющихся InnoDB, но Oracle, очевидно, этого не сделает.
Некоторые проблемы, связанные с большим количеством разделов, уменьшаются благодаря хранению словаря данных в таблице.

Родное разбиение позволит:

Это немного улучшит производительность, объединив два "обработчика" в один.
Уменьшить потребление памяти, особенно при использовании большого количества разделов.

См. также

Рик Джеймс любезно разрешил нам использовать эту статью в базе знаний.

Сайт Рика Джеймса содержит другие полезные советы, руководства, оптимизации и советы по отладке.

Исходный источник: http://mysql.rjweb.org/doc.php/partitionmaint

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

© 2023 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/partition-maintenance/