Subpartitioning также известно как составное выделение разделов (composite partitioning), что является дальнейшим делением каждого раздела в разбитой на разделы таблице. Например, рассмотрите следующую инструкцию CREATE TABLE:
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH(TO_DAYS(purchased))
SUBPARTITIONS 2 (PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE);
Таблица ts имеет 3 раздела RANGE. Каждый из этих разделов p0, p1 и p2 далее разделен на 2 подраздела. В действительности вся таблица разделена на 3*2=6 разделов. Однако, из-за действия предложения PARTITION BY RANGE первые 2 хранят только записи со значением меньше, чем 1990 в столбце purchased.
В MySQL 5.1 возможно подвыделить разделы таблиц, которые разбиты на разделы RANGE или LIST. Подразделы могут использовать выделение разделов HASH или KEY. Это также известно как составное выделение разделов.
Также возможно определить подразделы, явно использующие предложения SUBPARTITION, чтобы определить параметры для индивидуальных подразделов. Например, более подробный режим создания той же самой таблицы ts, как показано в предыдущем примере был бы:
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0, SUBPARTITION s1),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2, SUBPARTITION s3),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4, SUBPARTITION s5));
Некоторые синтаксические элементы:
Каждый раздел должен иметь то же самое число подразделов.
Если Вы явно определяете любые подразделы, использующие SUBPARTITION на любом разделе разбитой на разделы таблицы, Вы должны определить их все. Другими словами, следующая инструкция будет терпеть неудачу:
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH(TO_DAYS(purchased)) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0, SUBPARTITION s1),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2, SUBPARTITION s3));
Эта инструкция свалится даже если добавить предложение SUBPARTITIONS 2.
Каждое предложение SUBPARTITION должно включить (как минимум) имя для подраздела. Иначе Вы можете устанавливать любую желательную опцию для подраздела или позволить принимать настройку по умолчанию для этой опции.
В MySQL 5.1.7 и ранее имена подразделов должны быть уникальны внутри каждого раздела, но не должны быть уникальны внутри таблицы в целом. Начиная с MySQL 5.1.8, имена подразделов должны быть уникальны для всей таблицы. Например, следующая инструкция CREATE TABLE допустима в MySQL 5.1.8 и позже:
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH(TO_DAYS(purchased)) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0, SUBPARTITION s1),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2, SUBPARTITION s3),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4, SUBPARTITION s5));
Предыдущая инструкция также допустима для версий MySQL до 5.1.8.
Подразделы могут использоваться с особенно большими таблицами, чтобы распределить данные и индексы на много дисков. Предположите, что Вы имеете 6 дисков, установленные как /disk0, /disk1, /disk2 и т. д. Теперь рассмотрите следующий пример:
CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH(TO_DAYS(purchased)) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0 DATA DIRECTORY = '/disk0/data'
INDEX DIRECTORY = '/disk0/idx',
SUBPARTITION s1 DATA DIRECTORY = '/disk1/data'
INDEX DIRECTORY = '/disk1/idx'),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2 DATA DIRECTORY = '/disk2/data'
INDEX DIRECTORY = '/disk2/idx',
SUBPARTITION s3 DATA DIRECTORY = '/disk3/data'
INDEX DIRECTORY = '/disk3/idx'),
PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION s4
DATA DIRECTORY = '/disk4/data'
INDEX DIRECTORY = '/disk4/idx',
SUBPARTITION s5 DATA DIRECTORY = '/disk5/data'
INDEX DIRECTORY = '/disk5/idx'));
В этом случае отдельный диск используется для данных и индексов каждого RANGE. Много других разновидностей возможны, другой пример мог бы быть таким:
CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH(TO_DAYS(purchased)) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0a DATA DIRECTORY = '/disk0' INDEX DIRECTORY='/disk1',
SUBPARTITION s0b DATA DIRECTORY = '/disk2' INDEX DIRECTORY='/disk3'),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s1a DATA DIRECTORY = '/disk4/data'
INDEX DIRECTORY = '/disk4/idx',
SUBPARTITION s1b DATA DIRECTORY = '/disk5/data'
INDEX DIRECTORY = '/disk5/idx'),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2a, SUBPARTITION s2b));
Здесь хранение следующее:
Строки с датами purchased до 1990 занимают обширное количество места, поэтому поделены 4 путями: с отдельным диском, специализированным для данных, и с дополнительным диском для индексов для каждого из двух подразделов (s0a и s0b), входящих в раздел p0. Другими словами:
Данные для подраздела s0a сохранены на /disk0.
Индексы для подраздела s0a сохранены на /disk1.
Данные для подраздела s0b сохранены на /disk2.
Индексы для подраздела s0b сохранены на /disk3.
Строки, содержащие даты в пределах от 1990 до 1999 (раздел p1) не требуют так много памяти, как даты до 1990. Они размазаны между 2 дисками (/disk4 и /disk5):
Данные и индексы, принадлежащие первому подразделу (s1a) раздела p1, сохранены на /disk4: данные в /disk4/data, а индексы в /disk4/idx.
Данные и индексы, принадлежащие второму подразделу (s1b) раздела p1, сохранены на /disk5: данные в /disk5/data, а индексы в /disk5/idx.
Строки, отражающие даты с года 2000 до текущей (раздел p2), не занимают так много места, как любой из двух предыдущих диапазонов. В настоящее время достаточно сохранить всех их в заданном по умолчанию расположении.
В будущем, когда число purchases в течение десятилетия, начинающегося годом 2000, вырастет до отметки, где заданное по умолчанию расположение больше не обеспечивает достаточного места, соответствующие строки могут перемещаться, используя ALTER TABLE … REORGANIZE PARTITION.