Разделы диапазона и списка очень похожи относительно того, как обработано добавление и удаление разделов. По этой причине мы обсуждаем здесь управление обеими сортами выделения разделов.
Удаление раздела из таблицы, которая разбита на разделы RANGE или LIST может быть выполнено, используя инструкцию ALTER TABLE с предложением DROP PARTITION. Имеется базисный пример, который предполагает, что Вы уже создали таблицу, которая разбита на разделы диапазоном и затем заполняется 10 записями, использующими следующие инструкции CREATE TABLE и INSERT:
mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
– > PARTITION BY RANGE(YEAR(purchased)) (
– > PARTITION p0 VALUES LESS THAN (1990),
– > PARTITION p1 VALUES LESS THAN (1995),
– > PARTITION p2 VALUES LESS THAN (2000),
– > PARTITION p3 VALUES LESS THAN (2005));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO tr VALUES
– > (1, 'desk organiser', '2003-10-15'),
– > (2, 'CD player', '1993-11-05'),
– > (3, 'TV set', '1996-03-10'),
– > (4, 'bookcase', '1982-01-10'),
– > (5, 'exercise bike', '2004-05-09'),
– > (6, 'sofa', '1987-06-05'),
– > (7, 'popcorn maker', '2001-11-22'),
– > (8, 'aquarium', '1992-08-04'),
– > (9, 'study desk', '1984-09-16'),
– > (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.01 sec)
Вы можете видеть, которые элементы должны быть вставлены в раздел p2 как показано здесь:
mysql> SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+----+-----------+------------+
| id | name | purchased |
+----+-----------+------------+
| 3 | TV set | 1996-03-10 |
| 10 | lava lamp | 1998-12-25 |
+----+-----------+------------+
2 rows in set (0.00 sec)
Чтобы удалить раздел p2, выполните следующую команду:
mysql> ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.03 sec)
Обратите внимание: в MySQL 5.1 NDB Cluster не поддерживает ALTER TABLE … DROP PARTITION. Это, однако, поддерживает другие связанные с выделением разделов расширения ALTER TABLE, которые описаны в этой главе.
Очень важно не забыть, что, когда Вы удаляете раздел, Вы также удаляете все данные, которые был сохранены в этом разделе. Вы можете видеть, что дело обстоит именно так, делая повторный запуск предыдущего запроса SELECT:
SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)
Из-за этого в MySQL 5.1.10 было добавлено требование, что Вы имеете привилегию DROP для таблицы прежде, чем Вы сможете выполнять ALTER TABLE … DROP PARTITION на этой таблице.
Если Вы желаете удалить все данные из всех разделов при сохранении определения таблицы и схемы выделения разделов, используйте команду TRUNCATE TABLE.
Если Вы предполагаете изменять выделение разделов таблицы без потерь данных, используйте вместо этого ALTER TABLE … REORGANIZE PARTITION.
Если Вы теперь выполняете команду SHOW CREATE TABLE, Вы можете видеть, как выделение разделов таблицы было изменено:
mysql> SHOW CREATE TABLE tr\G
*************************** 1. row ***************************
Table: tr
Create Table: CREATE TABLE `tr` (`id` int(11) default NULL,
`name` varchar(50) default NULL,
`purchased` date default NULL)
ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(purchased)) (
PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM)
1 row in set (0.01 sec)
Когда Вы вставляете новые строки в измененную таблицу со значениями столбца purchased между '1995-01-01' и '2004-12-31' включительно, те строки будут сохранены в разделе p3. Вы можете проверять этот факт следующим образом:
mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tr WHERE purchased
– > BETWEEN '1995-01-01' AND '2004-12-31';
+----+----------------+------------+
| id | name | purchased |
+----+----------------+------------+
| 11 | pencil holder | 1995-07-12 |
| 1 | desk organiser | 2003-10-15 |
| 5 | exercise bike | 2004-05-09 |
| 7 | popcorn maker | 2001-11-22 |
+----+----------------+------------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE tr DROP PARTITION p3;
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT * FROM tr WHERE purchased
– > BETWEEN '1995-01-01' AND '2004-12-31';
Empty set (0.00 sec)
Обратите внимание, что число строк, удаленных из таблицы в результате ALTER TABLE … DROP PARTITION не сообщено сервером, поскольку это было бы эквивалентом запроса DELETE.
Удаление разделов LIST использует такой же синтаксис same ALTER TABLE … DROP PARTITION, как и для RANGE. Однако, имеется одно важное различие в эффекте, который это имеет на вашем использовании таблицы позже: Вы больше не можете вставлять в таблицу никакие строки, имеющие любое из значений, которые были включены в список значения, определяющие удаленный раздел.
Чтобы добавлять новый диапазон или раздел списка к предварительно разбитой на разделы таблице, используйте инструкцию ALTER TABLE … ADD PARTITION. Для таблиц, которые разбиты на разделы RANGE, это может использоваться, чтобы добавить новый диапазон к концу списка существующих разделов. Например, предположите, что Вы имеете разбитую на разделы таблицу, содержащую данные членства для Вашей организации, которая определена следующим образом:
CREATE TABLE members (id INT, fname VARCHAR(25), lname VARCHAR(25), dob DATE)
PARTITION BY RANGE(YEAR(dob)) (PARTITION p0 VALUES LESS THAN (1970),
PARTITION p1 VALUES LESS THAN (1980),
PARTITION p2 VALUES LESS THAN (1990));
Предположите далее, что минимальный возраст для элементов 3. Поскольку календарь приближается к концу 2005, Вы понимаете, что Вы будете скоро допускать элементы, которые были рождены в 1990 (и позже в последующих годах). Вы можете изменять таблицу элементов, чтобыразместить новые элементы members, рожденные в годах 1990-1999 как показано здесь:
ALTER TABLE ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));
Важно: с таблицами, которые разбиты на разделы диапазоном, Вы можете использовать ADD PARTITION, чтобы добавить новые разделы только к верхнему концу списка разделов. Попытки добавлять новый раздел этим способом между или прежде, чем существующие разделы, приведут к ошибке, как показано здесь:
mysql> ALTER TABLE members
– > ADD PARTITION (PARTITION p3 VALUES LESS THAN (1960));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly
increasing for each partition
В подобном режиме Вы можете добавлять новые разделы к таблице, которая разбита на разделы LIST. Например, данная таблица определена подобно этому:
CREATE TABLE tt (id INT, data INT)
PARTITION BY LIST(data) (PARTITION p0 VALUES IN (5, 10, 15),
PARTITION p1 VALUES IN (6, 12, 18));
Вы можете добавлять новый раздел, чтобы сохранить строки, имеющие значения столбца data 7, 14 и 21 как показано здесь:
ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));
Обратите внимание, что Вы не можете добавлять новый раздел LIST, включающий любые значения, которые уже включены в список значений существующего раздела (сервер не поймет, в какой именно раздел ему дописывать данные). Если Вы пытаетесь сделать так, выйдет ошибка:
mysql> ALTER TABLE tt ADD PARTITION
– > (PARTITION np VALUES IN (4, 8, 12));
ERROR 1465 (HY000): Multiple definition of same constant ┬╗
in list partitioning
Потому что любые строки со значением столбца data 12 уже были назначены в раздел p1, Вы не можете создавать новый раздел в таблице tt, который включает 12 в список значения. Чтобы выполнять это, Вы могли бы удалить p1, добавить np, а затем новый p1 с изменяемым определением. Однако, как сказано ранее, это привело бы к потере всех данных, сохраненных в p1, и это часто имеет место. Другое решение: сделать копию таблицы с новым выделением разделов и скопировать данные в нее, используя CREATE TABLE … SELECT …, затем удалить старую таблицу и переименовать новую, но это могло бы быть очень долго, когда имеешь дело с большими количествами данных. Это также не может быть возможно в ситуациях, где высокая доступность является требованием.
Начиная с MySQL 5.1.6, Вы можете добавлять много разделов в одиночной команде ALTER TABLE … ADD PARTITION, как показано здесь:
CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL, hired DATE NOT NULL)
PARTITION BY RANGE(YEAR(hired)) (
PARTITION p1 VALUES LESS THAN (1991),
PARTITION p2 VALUES LESS THAN (1996),
PARTITION p3 VALUES LESS THAN (2001),
PARTITION p4 VALUES LESS THAN (2005));
ALTER TABLE employees ADD PARTITION (PARTITION p5 VALUES LESS THAN (2010),
PARTITION p6 VALUES LESS THAN MAXVALUE);
Реализация выделения разделов в MySQL обеспечивает способы переопределить разделы без потерь данных. Выберите таблицу элементов members, которая теперь определена как показано здесь:
mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
Table: members
Create Table: CREATE TABLE `members` (`id` int(11) default NULL,
`fname` varchar(25) default NULL,
`lname` varchar(25) default NULL,
`dob` date default NULL)
ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(dob)) (
PARTITION p0 VALUES LESS THAN (1970) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM.
PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM)
Предположите, что Вы хотели бы переместить все строки, представляющие элементы, рожденные перед 1960 в отдельный раздел. Мы уже видели, что это не может быть выполнено, используя ALTER TABLE … ADD PARTITION. Однако, Вы можете использовать другое связанное с разделом расширение ALTER TABLE, чтобы выполнить это:
ALTER TABLE members REORGANIZE PARTITION p0 INTO (
PARTITION s0 VALUES LESS THAN (1960),
PARTITION s1 VALUES LESS THAN (1970));
В действительности эти команды разделяют раздел p0 на два новых раздела s0 и s1. Это также перемещает данные, которые были сохранены в p0, в новые разделы согласно правилам, воплощенным в двух предложениях PARTITION … VALUES …, так, чтобы s0 содержал только те записи, для которых YEAR(dob) меньше, чем 1960 и s1 хранил те строки, в которых YEAR(dob) является большим чем или равным 1960, но меньше чем 1970.
Предложение REORGANIZE PARTITION может также использоваться для объединения смежных разделов. Вы можете возвращать таблицу элементов members к предыдущему выделению разделов, как показано здесь:
ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
PARTITION p0 VALUES LESS THAN (1970));
Никакие данные не потеряны при разбиении или объединении разделов, использующих REORGANIZE PARTITION. При выполнении вышеупомянутой инструкции MySQL перемещает все записи, которые были сохранены в разделах s0 и s1 в раздел p0.
Общий синтаксис для REORGANIZE PARTITION:
ALTER TABLE tbl_name
REORGANIZE PARTITION partition_list
INTO (partition_definitions);
Здесь tbl_name имя разбитой на разделы таблицы, partition_list разделяемый запятыми список имен одного или большего количества существующих разделов, которые будут изменены. partition_definitions разделяемый запятыми список новых определений разделов, которые следуют тем же самым правилам, что касаются списка partition_definitions, используемого в CREATE TABLE. Должно быть отмечено, что Вы не ограничены объединением нескольких разделов в один или разбиением одного раздела на много, когда используете REORGANIZE PARTITION. Например, Вы можете реорганизовать все четыре раздела таблицы элементов members в два следующим образом:
ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
PARTITION m0 VALUES LESS THAN (1980),
PARTITION m1 VALUES LESS THAN (2000));
Вы можете также использовать REORGANIZE PARTITION с таблицами, которые разбиты на разделы LIST. Вернемся к проблеме добавления нового раздела к разбитой на разделы списком таблице tt и тому, что новый раздел имел значение, которое было уже представлено в списке значений одного из существующих разделов. Мы можем обрабатывать это, добавляя раздел, который содержит только не находящиеся в противоречии значения, а затем реорганизуя новый и существующий разделы так, чтобы значение, которое было сохранено в существующем, переместилось в новый:
ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
PARTITION p1 VALUES IN (6, 18),
PARTITION np VALUES in (4, 8, 12));
Имеются некоторые ключевые точки, которые следует иметь в виду, когда используете ALTER TABLE … REORGANIZE PARTITION, чтобы заново выделить разделы таблиц, которые разбиты на разделы RANGE или LIST:
Предложения PARTITION используемые, чтобы определить новую схему выделения разделов, подчинены тем же самым правилам, какие использовали с инструкцией CREATE TABLE.
Наиболее важно, Вы должны не забыть, что новая схема выделения разделов не может иметь любые диапазоны перекрывания (применяется к таблицам, разбитым на разделы RANGE) или наборов значений (при реорганизации таблиц, разбитых на разделы LIST).
Обратите внимание: до MySQL 5.1.4 Вы не могли бы многократно использовать имена существующих разделов в предложении INTO, даже когда те разделы удалялись удалялись или переопределялись.
Комбинация разделов в списке partition_definitions должна объявить тот же самый диапазон или полный набор значений, что и объединенные разделы, именованные в partition_list.
Например, в таблице элементов members, используемой в этом разделе, выделяются разделы p1 и p2, которые вместе покрывают годы с 1980 по 1999. Следовательно, любая реорганизация этих двух разделов должна покрыть тот же самый диапазон полных лет.
Для таблиц, разбитых на разделы RANGE Вы можете реорганизовать только смежные разделы. Вы не можете перескакивать над разделами диапазона.
Например, Вы не могли бы реорганизовать таблицу элементов members, используемую в этом разделе, используя инструкцию, начинающуюся ALTER TABLE members REORGANIZE PARTITION p0, p2 INTO …, поскольку p0 покрывает годы до 1970, а p2 годы с 1990 по 1999 включительно, и таким образом это не смежные разделы.
Вы не можете использовать REORGANIZE PARTITION, чтобы изменить тип выделения разделов таблицы, то есть Вы не можете (например) изменять разделы RANGE на HASH или наоборот. Вы также не можете использовать эту команду, чтобы изменить выражение выделения разделов или столбец. Чтобы выполнять любую из этих задач без того, чтобы удалить и вновь создать таблицу, Вы можете использовать ALTER TABLE … PARTITION BY …. Например:
ALTER TABLE members PARTITION BY HASH(YEAR(dob)) PARTITIONS 8;