The OpenNET Project / Index page

[ новости /+++ | форум | wiki | теги | ]

Каталог документации / Раздел "Базы данных, SQL" / Оглавление документа
Глава 3. Выделение разделов (Partitioning)

Из-за проблем с формированием двоичный дистрибутив MySQL 5.1.12 не содержат NDB Cluster или Partitioning. Пожалуйста, обновитесь до 5.1.14. Если Вы формируете пакет из исходника, Вы можете выполнять выбор конфигурации configure с параметрами --with-ndbcluster и --with-partition.

Эта глава обсуждает определяемое пользователем выделение разделов, как выполнено в MySQL 5.1.

MySQL поддерживает несколько типов выделения разделов, а также подвыделение разделов.

Методы добавления, удаления и изменения разделов в существующих разбитых на разделы таблицах рассмотрены в " 3.3. Управление разделами".

Важно: разбитые на разделы таблицы, созданные в MySQL до версии 5.1.6, не могут читаться версией 5.1.6 или позже. Кроме того, таблица INFORMATION_SCHEMA.TABLES не может использоваться, если такие таблицы присутствуют на сервере 5.1.6. Начиная с MySQL 5.1.7, подходящее предупреждающее сообщение сгенерировано о том, что несовместимые разбитые на разделы таблицы были найдены сервером.

Важно: если Вы используете разбитые на разделы таблицы, которые были созданы в MySQL 5.1.5 или ранее, убедитесь, что изучили список изменений.

Реализация выделения разделов в MySQL 5.1 все еще подвергается изменениям. Дополнительные ресурсы по теме:

Двоичная версия MySQL 5.1 теперь доступна с http://dev.mysql.com/downloads/mysql/5.1.html. Однако, для последнего исправления механизма выделения разделов и добавления свойств, Вы можете получать исходник из архива BitKeeper. Чтобы допускать выделению разделов, Вы должны компилировать сервер, используя опцию --with-partition. Если Вы имеете проблемы при компилировании MySQL 5.1 с допускаемым выделением разделов, почитайте форум http://forums.mysql.com/list.php?106 и поищите ответ там.

3.1. Краткий обзор выделения разделов в MySQL

Этот раздел обеспечивает концептуальный краткий обзор выделения разделов в MySQL 5.1.

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

Partitioning берет это понятие и продвигает на шаг далее, позволяя Вам распределить части индивидуальных таблиц по файловым системам согласно правилам, которые Вы можете устанавливать в значительной степени так, как необходимо. В действительности, различные части таблицы сохранены как отдельные таблицы в различных местах. Выбранное пользователем правило, которым выполнен раздел данных, известно как функция выделения разделов, которая в MySQL может быть модулем, простым соответствием набору диапазонов или списков, внутренней или линейной хэш-функцией. Функция выбрана согласно типу выделения разделов, определенному пользователем, и берет как параметр значение обеспеченного пользователем выражения. Это выражение может быть целочисленным значением столбца или функция, действующая на один или большее количество значений столбца, и возвращающая целое число. Значение этого выражения передано функции выделения разделов, которая возвращает целочисленное значение, представляющее номер раздела, в котором эта специфическая запись должна быть сохранена. Эта функция должна быть непостоянная и непроизвольная. Это не может содержать любые запросы, но может использовать фактически любое выражение SQL, которое является допустимым в MySQL, поскольку то выражение возвращает положительное целое число меньше, чем MAXVALUE (самое большое возможное положительное целое число). Примеры выделения разделов функций могут быть найдены в обсуждениях выделения разделов позже в этой главе.

Это известно как горизонтальное выделение разделов (horizontal partitioning), то есть различные строки таблицы могут быть назначены к различным физическим разделам. MySQL 5.1 не поддерживает вертикальное выделение разделов (vertical partitioning), в котором различные столбцы таблицы назначены различным физическим разделам. Не имеется никаких планов представить вертикальное выделение разделов в MySQL 5.1.

Выделение разделов включено в -max выпуски MySQL 5.1 (то есть двоичные версии 5.1 -max сформированы с --with-partition). Если MySQL сформирован с выделением разделов, ничто далее не должно быть выполнено, чтобы допустить это (например, никакие специальные записи не требуются в Вашем файле my.cnf). Вы можете определять, поддерживает ли сервер выделение разделов посредством команды SHOW VARIABLES типа этого:

mysql> SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

Если Вы не видите, что переменная have_partitioning со значением YES перечислена как показано выше в выводе соответствующей SHOW VARIABLES, то Ваша версия MySQL не поддерживает выделение разделов.

До MySQL 5.1.6 эта переменная была именована have_partition_engine (Глюк #16718).

Для создания разбитых на разделы таблиц, Вы можете использовать большинство типов хранения, которые обеспечиваются сервером MySQL. MySQL-выделение разделов выполняется в отдельном уровне и может взаимодействовать с любыми из них. В MySQL 5.1 все разделы той же самой разбитой на разделы таблицы должны использовать тот же самый тип памяти, например, Вы не можете использовать MyISAM для одного раздела, а InnoDB для другого. Однако, не имеется ничего предотвращающего Вас от использования различных типов памяти для различных разбитых на разделы таблиц на том же самом сервере MySQL или даже в той же самой базе данных.

Обратите внимание:: выделение разделов MySQL не может использоваться с типами памяти MERGE или CSV. До MySQL 5.1.6 также было невозможно создать разбитую на разделы таблицу, использующую BLACKHOLE (Глюк #14524). Выделение разделов KEY обеспечивается для использования с NDBCluster, но другие типы определяемого пользователем выделения разделов не обеспечиваются для таблиц Cluster в MySQL 5.1.

Чтобы использовать специфический тип памяти для разбитой на разделы таблицы, необходимо только использовать опцию [STORAGE] ENGINE точно как для не разбитой на разделы таблицы. Однако, Вы должны иметь в виду, что [STORAGE] ENGINE (и другие параметры таблицы) должен быть перечислен прежде, чем любые параметры выделения разделов используются в инструкции CREATE TABLE. Этот пример показывает, как создать таблицу, которая разбита на 6 разделов по hash и использует тип памяти InnoDB:

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
       ENGINE=INNODB PARTITION BY
       HASH(MONTH(tr_date)) PARTITIONS 6;

Обратите внимание, что каждое предложение PARTITION может включать опцию [STORAGE] ENGINE, но в MySQL 5.1 это не имеет никакого эффекта.

Обратите внимание: выделение разделов применяется ко всем данным и индексам таблицы. Вы не можете выделять разделы только данных и не индексы или наоборот, при этом Вы не можете выделять разделы только части таблицы.

Данные и индексы для каждого раздела могут быть назначены к специфическому каталогу, используя опции DATA DIRECTORY и INDEX DIRECTORY для предложения PARTITION инструкции CREATE TABLE, используемой чтобы создать разбитую на разделы таблицу. Кроме того, MAX_ROWS и MIN_ROWS могут использоваться, чтобы определить максимальные и минимальные числа строк, соответственно, которые могут быть сохранены в каждом разделе таблицы.

Некоторые из преимуществ выделения разделов:

Другие выгоды, обычно связываемые с выделением разделов, включены в следующий список. Эти свойства в настоящее время не выполнены в MySQL Partitioning, но высоки в списке приоритетов.

3.2. Типы раздела

Этот раздел обсуждает типы выделения разделов, которые доступны в MySQL 5.1. Они включают:

Очень общее использование выделения разделов базы данных должно выделять данные по времени. Некоторые системы баз данных поддерживают явное выделение разделов даты, которое MySQL не выполняет в 5.1. Однако, нетрудно создать в MySQL схемы выделения разделов, основанные на столбцах DATE, TIME, DATETIME или на выражениях, использующих такие столбцы.

При выделении разделов KEY или LINEAR KEY, Вы можете использовать столбец DATE, TIME или DATETIME как столбец выделения разделов без того, чтобы выполнить любую модификацию значения столбца. Например, эта инструкция создания таблицы совершенно допустима в MySQL:

CREATE TABLE members (firstname VARCHAR(25) NOT NULL,
                      lastname VARCHAR(25) NOT NULL,
                      username VARCHAR(16) NOT NULL,
                      email VARCHAR(35), joined DATE NOT NULL)
       PARTITION BY KEY(joined) PARTITIONS 6;

Другие типы выделения разделов MySQL, однако, требуют выражения выделения разделов, которое выдает целочисленное значение или NULL. Если Вы желаете использовать дата-основанное выделение разделов RANGE, LIST, HASH или LINEAR HASH, Вы можете просто использовать функцию, которая функционирует на столбце DATE, TIME или DATETIME и возвращает такое значение, как показано здесь:

CREATE TABLE members (firstname VARCHAR(25) NOT NULL,
                      lastname VARCHAR(25) NOT NULL,
                      username VARCHAR(16) NOT NULL,
                      email VARCHAR(35), joined DATE NOT NULL)
       PARTITION BY RANGE(YEAR(joined)) (
                 PARTITION p0 VALUES LESS THAN (1960),
                 PARTITION p1 VALUES LESS THAN (1970),
                 PARTITION p2 VALUES LESS THAN (1980),
                 PARTITION p3 VALUES LESS THAN (1990),
                 PARTITION p4 VALUES LESS THAN MAXVALUE);

Выделение разделов в MySQL оптимизирован для использования с функциям. TO_DAYS() и YEAR(). Однако, Вы можете использовать другие функции даты и времени, которые возвращают целое число или NULL, типа WEEKDAY(), DAYOFYEAR() или MONTH().

Важно помнить, что независимо от типа выделения разделов, которое Вы используете, разделы всегда нумеруются автоматически и в той последовательности, в какой созданы, при старте с 0. Когда новая строка вставлена в разбитую на разделы таблицу, это числа раздела, которые используются в идентификации правильного раздела. Например, если Ваша таблица использует 4 раздела, эти разделы пронумерованы 0, 1, 2 и 3. Для типов разделов RANGE и LIST необходимо гарантировать, что имеется раздел, определенный для каждого номера раздела. Для выделения разделов HASH использованная функция пользователя должна возвратить целочисленное значение большее, чем 0. Для выделения разделов KEY об этой проблеме позаботится автоматическая хэш-функция, которую сервер MySQL использует внутренне.

Имена разделов вообще следуют правилам для других MySQL-идентификаторов, типа тех, что применяются для таблиц и баз данных. Однако, Вы должны обратить внимание, что имена раздела не чувствительны к регистру. Например, следующая инструкция CREATE TABLE терпит неудачу как показано:

mysql> CREATE TABLE t2 (val INT)
    ->        PARTITION BY LIST(val) (
    ->                  PARTITION mypart VALUES IN (1,3,5),
    ->                  PARTITION MyPart VALUES IN (2,4,6));
ERROR 1488 (HY000): Duplicate partition name mypart

Сбой происходит потому, что MySQL не видит никакого различия между именами разделов mypart и MyPart.

Когда Вы определяете число разделов для таблицы, это должно быть выражено как положительный ненулевой целочисленный литерал без начальных нулей, и не может быть выражением типа 0.8E+01 или 6-2, даже если это оценивается как целое число. Начиная с MySQL 5.1.12, десятичные дроби больше не усечены, но взамен отвергнуты полностью.

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

3.2.1. RANGE Partitioning

Таблица, которая разбита на разделы диапазоном, разбита на разделы таким способом, которым каждый раздел содержит строки, для которых значение выражения выделения разделов находится внутри данного диапазона. Диапазоны должны быть непрерывны, но не перекрываться и определены, используя оператор VALUES LESS THAN. Для следующих немногих примеров, предположите, что Вы создаете таблицу типа следующей, чтобы сохранить персональные записи для цепочки из 20 видеоклипов, пронумерованных от 1 до 20:

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),
                        lname VARCHAR(30),
                        hired DATE NOT NULL DEFAULT '1970-01-01',
                        separated DATE NOT NULL DEFAULT '9999-12-31',
                        job_code INT NOT NULL, store_id INT NOT NULL);

Эта таблица может быть разбита на разделы диапазоном по-разному, в зависимости от Ваших потребностей. Один способ состоит в том, чтобы использовать столбец store_id. Например, Вы могли бы выделять разделы таблицы 4 способами, добавляя предложение PARTITION BY RANGE как показано здесь:

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),
                        lname VARCHAR(30),
                        hired DATE NOT NULL DEFAULT '1970-01-01',
                        separated DATE NOT NULL DEFAULT '9999-12-31',
                        job_code INT NOT NULL, store_id INT NOT NULL)
       PARTITION BY RANGE (store_id) (PARTITION p0 VALUES LESS THAN (6),
                                      PARTITION p1 VALUES LESS THAN (11),
                                      PARTITION p2 VALUES LESS THAN (16),
                                      PARTITION p3 VALUES LESS THAN (21));

В этой схеме выделения разделов все строки, соответствующие записям, занимающим номера от 1 до 5, сохранены в разделе p0, от 6 до 10 в p1 и т. д. Обратите внимание, что каждый раздел определен чтобы хранить номера от самого низкого до самого высокого. Это требование синтаксиса PARTITION BY RANGE: Вы можете думать об этом как об аналоге переключателя switch ... case в C или Java в этом отношении.

Просто определить, что новая строка, содержащая данные (72, 'Michael', 'Widenius', '1998-06-25', NULL, 13), вставлена в раздел p2, но что случается, когда Ваша цепочка, добавляет 21-ю запись? Согласно этой схеме, не имеется никакого правила, которое покрывает строку, с store_id большим чем 20, так что результатом будет ошибка, потому что сервер не знает, где поместить это. Вы можете обойти сбой, используя предложение VALUES LESS THAN в инструкции CREATE TABLE, которая обеспечивает все значения большие, чем явно именованное самое высокое значение:

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),
                        lname VARCHAR(30),
                        hired DATE NOT NULL DEFAULT '1970-01-01',
                        separated DATE NOT NULL DEFAULT '9999-12-31',
                        job_code INT NOT NULL, store_id INT NOT NULL)
       PARTITION BY RANGE (store_id) (PARTITION p0 VALUES LESS THAN (6),
                                      PARTITION p1 VALUES LESS THAN (11),
                                      PARTITION p2 VALUES LESS THAN (16),
                                      PARTITION p3 VALUES LESS THAN MAXVALUE);

MAXVALUE представляет самое большое возможное целочисленное значение. Теперь, любые строки, чье значение столбца store_id является большим или равным 16 (самое высокое определенное значение), сохранены в разделе p3. В некоторой точке в будущем, когда число записей увеличится до 25, 30 или больше, Вы можете использовать инструкцию ALTER TABLE, чтобы добавить новые разделы для диапазонов 21-25, 26-30 и т. д.

В аналогичном режиме Вы могли бы выделять разделы таблицы, основанные на кодах работы служащего, то есть на диапазонах значений столбца job_code. Например, приняв, что коды работы с двумя цифрами используются для регулярных (in-store) рабочих, коды с тремя цифрами используются для ведомства и персонала поддержки, а четырехразрядные коды для позиций управления, Вы могли бы создать разбитую на разделы таблицу, используя:

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),
                        lname VARCHAR(30),
                        hired DATE NOT NULL DEFAULT '1970-01-01',
                        separated DATE NOT NULL DEFAULT '9999-12-31',
                        job_code INT NOT NULL, store_id INT NOT NULL)
       PARTITION BY RANGE (job_code) (
                 PARTITION p0 VALUES LESS THAN (100),
                 PARTITION p1 VALUES LESS THAN (1000),
                 PARTITION p2 VALUES LESS THAN (10000));

В этом образце все строки в отношении рабочих in-store были бы сохранены в разделе p0, строки для ведомства и персонала поддержки в p1, а администраторы в разделе p2.

Также возможно использовать выражение в предложениях VALUES LESS THAN. Однако, MySQL должен быть способен оценить возвращаемое значение выражения как часть сравнения LESS THAN (<).

Вы можете использовать выражение, основанное на одном из двух столбцов DATE. Например, предположим, что Вы желаете выделить разделы основанные на годе, в котором каждый служащий оставил компанию, то есть значение YEAR(separated). Пример инструкции CREATE TABLE, которая осуществляет такую схему выделения разделов, показывается здесь:

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),
                        lname VARCHAR(30),
                        hired DATE NOT NULL DEFAULT '1970-01-01',
                        separated DATE NOT NULL DEFAULT '9999-12-31',
                        job_code INT, store_id INT)
       PARTITION BY RANGE (YEAR(separated)) (
                 PARTITION p0 VALUES LESS THAN (1991),
                 PARTITION p1 VALUES LESS THAN (1996),
                 PARTITION p2 VALUES LESS THAN (2001),
                 PARTITION p3 VALUES LESS THAN MAXVALUE);

В этой схеме для всех служащих, кто оставил работу до 1991, строки сохранены в разделе p0, для периода 1991-1995 в p1, для 1996-2000 в p2, а для любых рабочих, кто оставил фирму после 2000 года в p3.

Выделение разделов по диапазону особенно полезно когда:

3.2.2. LIST Partitioning

Как в выделении разделов RANGE, каждый раздел должен быть явно определен. Главное различие в том, что в выделении разделов списка, каждый раздел определен и выбран основываясь на членстве значения столбца в одном наборе значений списков, а не непрерывных диапазонов значений. Это выполнено, используя PARTITION BY LIST(expr) , где expr значение столбца или выражение, основанное на значении столбца и возврате целочисленного значения, а затем определение каждого раздела посредством VALUES IN (value_list), где value_list разделяемый запятыми список целых чисел.

Обратите внимание: В MySQL 5.1 возможно соответствовать только списку целых чисел (и возможно NULL) при выделении разделов LIST.

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

Для примеров ниже будем считать, что базисное определение таблицы, которая будет разбита на разделы обеспечивается инструкцией CREATE TABLE, показанной здесь:

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),
                        lname VARCHAR(30),
                        hired DATE NOT NULL DEFAULT '1970-01-01',
                        separated DATE NOT NULL DEFAULT '9999-12-31',
                        job_code INT, store_id INT);

Предположите, что имеются 20 видеоклипов, распределенных среди 4 привилегий, как показано в следующей таблице:

Область Store ID Numbers
Север3, 5, 6, 9, 17
Восток1, 2, 10, 11, 19, 20
Запад4, 12, 13, 14, 18
Центр7, 8, 15, 16

Чтобы выделять разделы таблицы таким способом, чтобы строки для клипов, принадлежащих к той же самой области, были сохранены в том же самом разделе, Вы могли бы использовать инструкцию CREATE TABLE, показанную здесь:

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),
                        lname VARCHAR(30),
                        hired DATE NOT NULL DEFAULT '1970-01-01',
                        separated DATE NOT NULL DEFAULT '9999-12-31',
                        job_code INT, store_id INT)
       PARTITION BY LIST(store_id) (
                 PARTITION pNorth VALUES IN (3,5,6,9,17),
                 PARTITION pEast VALUES IN (1,2,10,11,19,20),
                 PARTITION pWest VALUES IN (4,12,13,14,18),
                 PARTITION pCentral VALUES IN (7,8,15,16));

Это облегчает добавление или удаление записи в отношении специфических областей. Например, предположите, что все клипы в западной области проданы другой компании. Все строки в их отношении могут быть удалены запросом ALTER TABLE employees DROP PARTITION pWest;, который может быть выполнен намного более эффективно, чем эквивалентная инструкция DELETE FROM employees WHERE store_id IN (4,12,13,14,18);.

Как с RANGE и HASH partitioning, если Вы желаете выделить разделы таблицы столбцом, чье значение не целое число или NULL, Вы должны использовать выражение выделения разделов, основанное на том столбце, который возвращает такое значение. Например, предположите, что таблица, содержащая данные определена, как показано здесь:

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),
                        lname VARCHAR(30),
                        hired DATE NOT NULL DEFAULT '1970-01-01',
                        separated DATE NOT NULL DEFAULT '9999-12-31',
                        job_code CHAR(1), store_id INT);

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

Категория работы или отделКоды работы
ManagementD, M, O, P
SalesB, L, S
TechnicalA, E, G, I, T
ClericalK, N, Y
SupportC, F, J, R, V
UnassignedEmpty

Так как мы не можем использовать символьные значения в списках, мы должны преобразовать их в целых числа или NULL. Для этой цели мы можем использовать функцию ASCII() на значении столбца. Кроме того, из-за использования различных прикладных программ в разное время коды могут быть верхнего или нижнего регистра, значение empty означает "сейчас не назначен", представлением чего могут быть NULL, пустая строка или пробел. Разбитая на разделы таблица, которая осуществляет эту схему, показывается здесь:

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),
                        lname VARCHAR(30),
                        hired DATE NOT NULL DEFAULT '1970-01-01',
                        separated DATE NOT NULL DEFAULT '9999-12-31',
                        job_code CHAR(1), store_id INT)
       PARTITION BY LIST(ASCII(UCASE(job_code))) (
                 PARTITION management VALUES IN(68, 77, 79, 80),
                 PARTITION sales VALUES IN(66, 76, 83),
                 PARTITION technical VALUES IN(65, 69, 71, 73, 84),
                 PARTITION clerical VALUES IN(75, 78, 89),
                 PARTITION support VALUES IN(67, 70, 74, 82, 86),
                 PARTITION unassigned VALUES IN(NULL, 0, 32));

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

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

INSERT INTO employees VALUES
       (224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 'Q', 21);

Сбой происходит, потому что 81 (код ASCII для прописной буквы 'Q') не найден в любом из списков значения используемых, чтобы определить любой из разделов. Не имеется никаких перехватчиков catch-all для list partitions, аналогичных VALUES LESS THAN(MAXVALUE), который приспосабливает значения, не найденные в любом из списков значения. Другими словами, любое значение, которое должно быть согласовано, должно быть найдено в одном из списков значений.

Как с выделением разделов RANGE, возможно объединить выделение разделов LIST, чтобы произвести составное выделение разделов (подвыделение разделов).

3.2.3. HASH Partitioning

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

Чтобы выделять разделы таблицы, использующей выделение разделов HASH, необходимо конкатенировать к инструкции CREATE TABLE предложение PARTITION BY HASH (expr), где expr выражение, которое возвращает целое число. Это может быть просто имя столбца, чей тип является одним из целочисленных типов MySQL. Кроме того, Вы будете, наиболее вероятно, пользоваться предложением PARTITIONS num, где num неотрицательное целое число, представляющее число разделов, на которые таблица должна быть разделена.

Например, следующая инструкция создает таблицу, которая использует хэширование на столбце store_id и разделена на 4 раздела:

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),
                        lname VARCHAR(30),
                        hired DATE NOT NULL DEFAULT '1970-01-01',
                        separated DATE NOT NULL DEFAULT '9999-12-31',
                        job_code INT, store_id INT)
       PARTITION BY HASH(store_id) PARTITIONS 4;

Если Вы не включаете предложение PARTITIONS, числом разделов по умолчанию будет 1. Использование ключевого слова PARTITIONS без числа после него приводит к синтаксической ошибке.

Вы можете также использовать выражение SQL, которое возвращает целое число для expr. Например, Вы могли бы выделять разделы, основываясь на годе, в котором служащий был нанят. Это может быть выполнено как показано здесь:

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),
                        lname VARCHAR(30),
                        hired DATE NOT NULL DEFAULT '1970-01-01',
                        separated DATE NOT NULL DEFAULT '9999-12-31',
                        job_code INT, store_id INT)
       PARTITION BY HASH(YEAR(hired)) PARTITIONS 4;

Вы можете использовать любое функциональное или другое выражение для expr, которое является допустимым в MySQL, пока это возвращает непостоянное, непроизвольное целочисленное значение. Другими словами, это должно изменяться, но детерминировано. Однако, Вы должны иметь в виду, что это выражение оценено каждый раз, когда строка вставлена или модифицируется (или возможно удалена). Это означает, что очень сложные выражения могут вызывать проблемы эффективности, особенно при выполнении операций (типа пакетных вставок), которые воздействуют на очень многие строки в одно время.

Наиболее эффективная хэш-функция та, которая функционирует на одиночном столбце таблицы, и чье значение увеличивается или уменьшается последовательно со значением столбца, поскольку это учитывает сокращение (pruning) на диапазонах разделов. То есть, выражение изменяется со значением столбца, на котором основано.

Например, если столбец date_col типа DATE, то выражение TO_DAYS(date_col) изменяется непосредственно со значением date_col, потому что для каждого изменения в значении date_col значение выражения изменяется непротиворечивым способом. Дисперсия выражения YEAR(date_col) относительно date_col не так пряма, как TO_DAYS(date_col), потому что не каждое возможное изменение в date_col производит эквивалентное изменение в YEAR(date_col). Даже в этом случае YEAR(date_col) хороший кандидат на хэш-функцию, потому что это изменяется непосредственно с частью date_col, и не имеется никакого возможного изменения в date_col, которое производит непропорциональное изменение в YEAR(date_col).

Посредством контраста, предположите, что Вы имеете столбец int_col типа INT. Теперь рассмотрите выражение POW(5-int_col,3)+6. Это было бы плохим выбором для хэш-функции, потому что изменение в значении int_col не произведет пропорциональное изменение в значении выражения. Изменение значения int_col может производить очень разные изменения в значении выражения. Например, изменение int_col с 5 на 6 производит изменение в значении выражения -1, но при изменении значения int_col с 6 на 7 это будет уже -7.

Другими словами, граф значения столбца против значения выражения более близко следует за прямой строкой по уравнению y=nx, где n некоторая константа, отличная от нуля. Такое выражение лучше подходит для хэширования. Более нелинейный выражение произведет более неравное распределение данных среди разделов.

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

Когда используется PARTITION BY HASH, MySQL определяет который раздел num использовать, основываясь на модуле результата функции пользователя. Другими словами, для выражения expr раздел, в котором запись сохранена, представляет собой номер раздела N, где N=MOD(expr, num). Например, предположите, что таблица t1 определена следующим образом, чтобы имела 4 раздела:

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
       PARTITION BY HASH(YEAR(col3)) PARTITIONS 4;

Если Вы вставляете в t1 запись с '2005-09-15' в col3, то раздел, в котором это будет сохранено, определен следующим образом:

MOD(YEAR('2005-09-01'),4)=MOD(2005,4)=1

MySQL 5.1 также поддерживает вариант HASH partitioning известного как linear hashing (линейное хэширование) , которое использует более сложный алгоритм для определения размещения новых строк, вставленных в разбитую на разделы таблицу.

Функция пользователя оценена каждый раз, когда запись вставлена или модифицируется. Это может также быть в зависимости от обстоятельств, когда записи удалены.

Обратите внимание: если таблица, которая будет разбита на разделы, имеет ключ UNIQUE, то любые столбцы, обеспеченные как параметры к HASH функции пользователя или на KEY column_list, должны быть частью того ключа. Исключительная ситуация: это ограничение не относится к таблицам, использующим NDBCluster.

3.2.3.1. LINEAR HASH Partitioning

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

Синтаксически единственное различие между выделением разделов линейного хэширования и регулярным хэшированием: добавление ключевого слова LINEAR в предложение PARTITION BY, как показано здесь:

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),
                        lname VARCHAR(30),
                        hired DATE NOT NULL DEFAULT '1970-01-01',
                        separated DATE NOT NULL DEFAULT '9999-12-31',
                        job_code INT, store_id INT)
       PARTITION BY LINEAR HASH(YEAR(hired)) PARTITIONS 4;

Данный выражением expr раздел, в котором запись сохранена, когда линейное хэширование используется, представляет собой номер раздела N из числа разделов num, где N получен согласно следующему алгоритму:

  1. Находят следующую степень 2 большую, чем num. Назовем это значение V, это может быть вычислено как:

    V=POWER(2, CEILING(LOG(2, num)))
    

    Например, предположите, что num=13. Тогда LOG(2,13)=3.7004397181411. CEILING(3.7004397181411) 4, а V = POWER(2,4) = 3.

  2. Берется N = F(column_list) & (V - 1).

  3. Пока N >= num:

Например, предположите, что таблица t1 применяет линейное выделение разделов, имеет 6 разделов и создана, используя эту инструкцию:

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
       PARTITION BY LINEAR HASH(YEAR(col3)) PARTITIONS 6;

Теперь примите, что Вы хотите вставлять две записи в t1: у одной значение столбца col3 равно '2003-04-14', а у другой составляет '1998-10-19'. Номер раздела для первой из них определен следующим образом:

V = POWER(2, CEILING( LOG(2,7) )) = 8
N = YEAR('2003-04-14') & (8-1) = 2003 & 7 = 3
(3 >= 6 FALSE: запись сохранена в разделе #3
)

Номер раздела, где сохранена вторая запись, вычислен как показано здесь:

V = 8
N = YEAR('1998-10-19') & (8-1) = 1998 & 7 = 6
(6 >= 6 TRUE: нужен дополнительный шаг
)
N = 6 & CEILING(5 / 2) = 6 & 3 = 2
(2 >= 6 FALSE: запись сохранена в разделе #2
)

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

3.2.4. KEY Partitioning

Выделение разделов ключом подобно выделению разделов хэшем за исключением того, что выделение разделов хэшем использует определяемое пользователем выражение, а хэш-функция для выделения разделов ключом обеспечена MySQL. Здесь MySQL Cluster использует для этой цели MD5(), а для таблиц, использующих другие типы памяти, сервер применяет собственную внутреннюю хэш-функцию, которая основана на том же самом алгоритме, что и PASSWORD().

Правила синтаксиса для CREATE TABLE ... PARTITION BY KEY подобен правилам для создания таблицы, которая разбита на разделы хэшем. Главные различия состоят в том что:

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

CREATE TABLE tk (col1 INT NOT NULL, col2 CHAR(5), col3 DATE)
       PARTITION BY LINEAR KEY (col1) PARTITIONS 3;

Использование LINEAR имеет тот же самый эффект на KEY, как на выделении разделов HASH с номером раздела, получаемым использованием алгоритма степени двух, а не арифметикой модуля.

3.2.5. Подвыделение разделов (Subpartitioning)

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));

Некоторые синтаксические элементы:

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

Здесь хранение следующее:

3.2.6. Как выделитель разделов в MySQL обрабатывает значения NULL

Выделение разделов в MySQL не делает ничего, чтобы отвергнуть NULL как значение выражения выделения разделов независимо от того, является ли это значением столбца или обеспеченного пользователем выражения. Даже разрешается использовать NULL как значение выражения, которое должно выдать целое число, но важно иметь в виду, что NULL числом не является. Начиная с версии 5.1.8, MySQL обрабатывает NULL как будто он меньше, чем любое не нулевое значение, точно как делает ORDER BY.

Из-за этого эта обработка NULL изменяется при выделении разделов различных типов, и может производить поведение, которое Вы не ожидаете. Если Вы вставляете строку в таблицу, разбитую на разделы RANGE так, что значение столбца, используемое, чтобы определить раздел, является NULL, строка вставлена в самый нижний раздел. Например, рассмотрите эти две таблицы, созданные и заполняемые следующим образом:

mysql> CREATE TABLE t1 (c1 INT, c2 VARCHAR(20))
    ->        PARTITION BY RANGE(c1) (
    ->                  PARTITION p0 VALUES LESS THAN (0),
    ->                  PARTITION p1 VALUES LESS THAN (10),
    ->                  PARTITION p2 VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE t1 (c1 INT, c2 VARCHAR(20))
    ->        PARTITION BY RANGE(c1) (
    ->                  PARTITION p0 VALUES LESS THAN (-5),
    ->                  PARTITION p1 VALUES LESS THAN (0),
    ->                  PARTITION p1 VALUES LESS THAN (10),
    ->                  PARTITION p2 VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO t1 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+--------+
| id   | name   |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+--------+
| id   | name   |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)

Вы можете видеть, в который раздел строки сохранены, осматривая файловую систему и сравнивая размеры .MYD-файлов:

/var/lib/mysql/test> ls -l *.MYD
-rw-rw---- 1 mysql mysql 20 2006-03-10 03:27 t1#P#p0.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 03:17 t1#P#p1.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 03:17 t1#P#p2.MYD
-rw-rw---- 1 mysql mysql 20 2006-03-10 03:27 t2#P#p0.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 03:17 t2#P#p1.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 03:17 t2#P#p2.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 03:17 t2#P#p3.MYD

Файлы разделов именованы согласно формату table_name#P#partition_name .extension так, чтобы t1#P#p0.MYD был файлом, в котором сохранены данные, принадлежащие к разделу p0 таблицы t1. Обратите внимание: до MySQL 5.1.5, эти файлы были бы именованы соответственно t1_p0.MYD и t2_p0.MYD.

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

mysql> ALTER TABLE t1 DROP PARTITION p0;
Query OK, 0 rows affected (0.16 sec)
mysql> ALTER TABLE t2 DROP PARTITION p0;
Query OK, 0 rows affected (0.16 sec)
mysql> SELECT * FROM t1;
Empty set (0.00 sec)
mysql> SELECT * FROM t2;
Empty set (0.00 sec)

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

CREATE TABLE tndate (id INT, dt DATE) PARTITION BY RANGE(YEAR(dt)) (
       PARTITION p0 VALUES LESS THAN (1990),
       PARTITION p1 VALUES LESS THAN (2000),
       PARTITION p2 VALUES LESS THAN MAXVALUE);

Как с другими функциями MySQL, YEAR(NULL) вернет NULL. Строка со значением NULL столбца dt обрабатывается, как если бы выражение выделения разделов было оценено к значению меньше, чем любое другое значение, и так вставлено в раздел p0.

Таблица, которая разбита на разделы LIST допускает значения NULL если (и только если!) один из разделов определен, используя список значений, который содержит NULL. Таблица, разбитая на разделы LIST, которая явно не использует NULL в списке значений, отклоняет строки, приводящие к значению NULL для выражения выделения разделов, как показано в этом примере:

mysql> CREATE TABLE ts1 (c1 INT, c2 VARCHAR(20))
    ->        PARTITION BY LIST(c1) (
    ->                  PARTITION p0 VALUES IN (0, 3, 6),
    ->                  PARTITION p1 VALUES IN (1, 4, 7),
    ->                  PARTITION p2 VALUES IN (2, 5, 8));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO ts1 VALUES (9, 'mothra');
ERROR 1504 (HY000): Table has no partition for value 9
mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');
ERROR 1504 (HY000): Table has no partition for value NULL

Только строки, имеющие значение c1 между 0 и 8 включительно, могут быть вставлены в ts1. NULL выходит за пределы этого диапазона точно так же, как число 9. Мы можем создавать таблицы ts2 и ts3 и списки значений, содержащие NULL, как показано здесь:

mysql> CREATE TABLE ts2 (c1 INT, c2 VARCHAR(20))
    ->        PARTITION BY LIST(c1) (
    ->                  PARTITION p0 VALUES IN (0, 3, 6),
    ->                  PARTITION p1 VALUES IN (1, 4, 7),
    ->                  PARTITION p2 VALUES IN (2, 5, 8),
    ->                  PARTITION p3 VALUES IN (NULL));
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE ts3 (c1 INT, c2 VARCHAR(20))
    ->        PARTITION BY LIST(c1) (
    ->                  PARTITION p0 VALUES IN (0, 3, 6),
    ->                  PARTITION p1 VALUES IN (1, 4, 7, NULL),
    ->                  PARTITION p2 VALUES IN (2, 5, 8));
Query OK, 0 rows affected (0.01 sec)

При определении значения для выделения разделов, Вы можете обрабатывать NULL точно как любое другое значение, и допустимы VALUES IN (NULL) и VALUES IN (1, 4, 7, NULL) (равно как и VALUES IN (1, NULL, 4, 7), VALUES IN (NULL, 1, 4, 7) и тому подобное). Вы можете вставлять строку, имеющую NULL для столбца c1 в каждую из таблиц ts2 и ts3:

mysql> INSERT INTO ts2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO ts3 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

Осматривая файловую систему, Вы можете проверить, что первая из этих инструкций вставила новую строку в раздел p3 таблицы ts2, а вторая инструкция вставила новую строку в раздел p1 таблицы ts3:

/var/lib/mysql/test> ls -l ts2*.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 10:35 ts2#P#p0.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 10:35 ts2#P#p1.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 10:35 ts2#P#p2.MYD
-rw-rw---- 1 mysql mysql 20 2006-03-10 10:35 ts2#P#p3.MYD

/var/lib/mysql/test> ls -l ts3*.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 10:36 ts3#P#p0.MYD
-rw-rw---- 1 mysql mysql 20 2006-03-10 10:36 ts3#P#p1.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 10:36 ts3#P#p2.MYD

Как в более ранних примерах, мы принимаем использование оболочки bash в операционной системе для Unix для списка файлов. Например, если Вы используете оболочку DOS в операционной системе Windows эквивалент последнего вывода мог быть получен, выполняя команду dir ts3*.MYD в каталоге C:\Program Files\MySQL\MySQL Server 5.1\data\test.

Как показано ранее в этом разделе, Вы можете также проверять, которые разделы использовались для сохранения значений удаляя их, а затем выполняя SELECT.

NULL обработан несколько по-другому для таблиц, разбитых на разделы HASH или KEY. В этих случаях любое выражение раздела, которое выдает значение NULL, обрабатывается, как если бы возвращаемое значение было нулевым. Мы можем проверять это поведение, исследуя эффекты в файловой системе от создания таблицы, разбитой на разделы HASH и начальной загрузкой с записью, содержащей соответствующие значения. Предположите, что Вы имеете таблицу th, созданную в базе данных test, используя эту инструкцию:

mysql> CREATE TABLE th (c1 INT, c2 VARCHAR(20))
    ->        PARTITION BY HASH(c1) PARTITIONS 2;
Query OK, 0 rows affected (0.00 sec)

При принятии установки RPM MySQL под Linux, эта инструкция создает два .MYD-файла в /var/lib/mysql/test, которые могут просматриваться в оболочке bash следующим образом:

/var/lib/mysql/test> ls th*.MYD -l
-rw-rw---- 1 mysql mysql 0 2005-11-04 18:41 th#P#p0.MYD
-rw-rw---- 1 mysql mysql 0 2005-11-04 18:41 th#P#p1.MYD

Обратите внимание, что размер каждого файла 0 байтов. Теперь вставьте в th строку, чей столбец c1 является NULL, и проверьте, что эта строка была вставлена:

mysql> INSERT INTO th VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM th;
+------+---------+
| c1   | c2      |
+------+---------+
| NULL | mothra  |
+------+---------+
1 row in set (0.01 sec)

Заметьте, что для любого целого числа N значение NULL MOD N всегда NULL. Для таблиц, которые разбиты на разделы HASH илм KEY, этот результат обрабатывается для определения правильного раздела как 0. При возврате к оболочке системы, мы можем видеть, что значение было вставлено в первый раздел (по умолчанию p0), выводя файлы данных еще раз:

var/lib/mysql/test> ls *.MYD -l
-rw-rw---- 1 mysql mysql 20 2005-11-04 18:44 th#P#p0.MYD
-rw-rw---- 1 mysql mysql  0 2005-11-04 18:41 th#P#p1.MYD

Вы можете видеть, что инструкция INSERT изменила только файл th#P#p0.MYD (увеличение размера на диске) без того, чтобы воздействовать на другой файл данных.

Важно: до MySQL 5.1.8 выделение разделов по RANGE значение выражения выделения разделов NULL работало как 0 (единственный способ обходить это должен был разработать таблицы так, чтобы не позволить пустые указатели, обычно объявляя столбцы NOT NULL). Если Вы имеете схему выделения разделов RANGE, которая зависит от этого более раннего поведения, Вы будете должны заново выполнить это при обновлении до MySQL 5.1.8 или позже.

3.3. Управление разделами

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

Обратите внимание: в MySQL 5.1 все разделы разбитой на разделы таблицы должны иметь то же самое число подразделов, и невозможно изменить подвыделение разделов, если только таблица была создана.

Инструкция ALTER TABLE ... PARTITION BY ... доступна с MySQL 5.1.6, предварительно, в MySQL 5.1, это было принято как допустимый синтаксис, но инструкция не делала ничего.

Чтобы изменять схему выделения разделов таблицы, необходимо использовать команду ALTER TABLE с предложением partition_options. Это предложение имеет тот же самый синтаксис, как то, что используется с CREATE TABLE для создания разбитой на разделы таблицы, и всегда начинается с ключевых слов PARTITION BY. Например, предположите, что Вы имеете таблицу, разбитую на разделы диапазоном, использующим следующую инструкцию CREATE TABLE:

CREATE TABLE trb3 (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));

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

ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;

Это имеет тот же самый эффект на структуре таблицы как удаление таблицы и создания ее вновь, используя CREATE TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;.

Важно: в MySQL 5.1.7 и ранее ALTER TABLE ... ENGINE = ... удаляет все выделение разделов из обрабатываемой таблицы. Начиная с MySQL 5.1.8, этот оператор меняет только тип памяти, используемый таблицей, и оставляет схему выделения разделов таблицы неповрежденной. С MySQL 5.1.8 применяйте ALTER TABLE ... REMOVE PARTITIONING, чтобы удалить выделение разделов таблицы.

3.3.1. Управление разделами RANGE и LIST

Разделы диапазона и списка очень похожи относительно того, как обработано добавление и удаление разделов. По этой причине мы обсуждаем здесь управление обеими сортами выделения разделов.

Удаление раздела из таблицы, которая разбита на разделы 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:

mysql> 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:

3.3.2. Управление разделами KEY и HASH

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

Вы не можете удалять разделы из таблиц, которые разбиты на разделы HASH или KEY таким же образом, каким Вы можете удалять их из таблиц, которые разбиты на разделы RANGE или LIST. Однако, Вы можете объединять разделы HASH или KEY, используя команду ALTER TABLE ... COALESCE PARTITION . Например, предположите, что Вы имеете таблицу, содержащую данные относительно клиентуры, которая разделена на двенадцать разделов. Таблица clients определена как показано здесь:

CREATE TABLE clients (id INT, fname VARCHAR(30),
                      lname VARCHAR(30), signed DATE)
       PARTITION BY HASH(MONTH(signed)) PARTITIONS 12;

Чтобы уменьшить число разделов с двенадцати до восьми, выполните следующую команду ALTER TABLE:

mysql> ALTER TABLE clients COALESCE PARTITION 4;
Query OK, 0 rows affected (0.02 sec)

COALESCE работает одинаково хорошо с таблицами, которые разбиты на разделы HASH, KEY, LINEAR HASH или LINEAR KEY. Имеется пример, подобный предыдущему, отличаясь только тем, что таблица разбита на разделы LINEAR KEY:

mysql> CREATE TABLE clients_lk (id INT, fname VARCHAR(30),
    ->                          lname VARCHAR(30), signed DATE)
    ->        PARTITION BY LINEAR KEY(signed) PARTITIONS 12;
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE clients_lk COALESCE PARTITION 4;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

Обратите внимание, что число, следующее за COALESCE PARTITION являются числом разделов, которое надлежит удалить из таблицы.

Если Вы пытаетесь удалить большее количество разделов, чем таблица имеет, результатом будет ошибка:

mysql> ALTER TABLE clients COALESCE PARTITION 18;
ERROR 1478 (HY000): Cannot remove all partitions, use DROP TABLE instead

Чтобы увеличить число разделов для таблицы clients с 12 до 18, скомандуйте ALTER TABLE ... ADD PARTITION:

ALTER TABLE clients ADD PARTITION PARTITIONS 6;

3.3.3. Сопровождение разделов

Ряд задач сопровождения выделения разделов может быть выполнен в MySQL 5.1. MySQL не поддерживает команды CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE или REPAIR TABLE для разбитых на разделы таблиц. Вместо этого Вы можете использовать ряд расширений ALTER TABLE, которые были выполнены в MySQL 5.1.5. Они могут использоваться для выполнения операций этого типа на одном или большем количестве разделов непосредственно, как описано в следующем списке:

Вы можете также использовать утилиту mysqlcheck или myisamchk, чтобы выполнить эти задачи, действуя на отдельных .MYI-файлах, сгенерированных, выделяя разделы таблицы.

3.3.4. Получение информации относительно разделов

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

SHOW CREATE TABLE включает в вывод предложение PARTITION BY, используемое, чтобы создать разбитую на разделы таблицу. Например:

mysql> SHOW CREATE TABLE trb3\G
*************************** 1. row ***************************
Table: trb3
Create Table: CREATE TABLE `trb3` (`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 p2 VALUES LESS THAN (2000) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM)
1 row in set (0.00 sec)

Обратите внимание: в ранних версиях MySQL 5.1 предложение PARTITIONS не показывалось для таблиц, разбитых на разделы HASH или KEY. Эта проблема была отфиксирована в MySQL 5.1.6.

SHOW TABLE STATUS работает с разбитыми на разделы таблицами. Начиная с MySQL 5.1.9, вывод такой же, как для не разбитых на разделы таблиц за исключением того, что столбец Create_options содержит строку partitioned. В MySQL 5.1.8 и ранее столбец Engine всегда содержал значение PARTITION. Начиная с MySQL 5.1.9, этот столбец содержит имя типа памяти, используемого всеми разделами таблицы.

Вы можете также получать информацию относительно разделов из INFORMATION_SCHEMA, которая содержит таблицу PARTITIONS.

Начиная с MySQL 5.1.5, можно определить, которые разделы разбитой на разделы таблицы включаются в данном запросе SELECT, применяя EXPLAIN PARTITIONS. Ключевое слово PARTITIONS добавляет столбец partitions к выводу EXPLAIN, перечисляющего столбцы, из которых записи соответствуют запросу.

Предположите, что Вы имеете таблицу trb1 определенную и заполняемую следующим образом:

CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
       PARTITION BY RANGE(id) (PARTITION p0 VALUES LESS THAN (3),
                               PARTITION p1 VALUES LESS THAN (7),
                               PARTITION p2 VALUES LESS THAN (9),
                               PARTITION p3 VALUES LESS THAN (11));
INSERT INTO trb1 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');

Вы можете видеть, которые разделы используются в запросе типа SELECT * FROM trb1;, как показано здесь:

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: trb1
partitions: p0,p1,p2,p3
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using filesort

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

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: trb1
partitions: p0, p1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using where

EXPLAIN PARTITIONS обеспечивают информацию относительно используемых и возможных ключей, точно как со стандартной инструкцией EXPLAIN SELECT:

mysql> ALTER TABLE trb1 ADD PRIMARY KEY (id);
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: trb1
partitions: p0, p1
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 7
Extra: Using where

Вы должны принять во внимание следующие ограничения на EXPLAIN PARTITIONS:

3.4. Сокращение раздела

Этот раздел обсуждает сокращение раздела (partition pruning), оптимизацию, которая была выполнена для разбитых на разделы таблиц в MySQL 5.1.6.

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

CREATE TABLE t1 (fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL,
                 region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL)
       PARTITION BY RANGE(region_code) (
                 PARTITION p0 VALUES LESS THAN (64),
                 PARTITION p1 VALUES LESS THAN (128),
                 PARTITION p2 VALUES LESS THAN (192)
                 PARTITION p3 VALUES LESS THAN MAXVALUE);

Рассмотрите случай, где Вы желаете получить результат запроса типа этого:

SELECT fname, lname, postcode, dob FROM t1
       WHERE region_code > 125 AND
       region_code < 130;

Просто видеть, что ни одна из строк, которые должны быть возвращены, не будет в разделе p0 или p3. То есть, мы должны искать данные только в разделах p1 и p2, чтобы найти строки соответствий. Делая так, можно расходовать намного меньше времени и усилий в нахождении строк соответствий, чем при просмотре всех разделов в таблице. Это и известно как сокращение ( pruning). Когда оптимизатор может использовать сокращение раздела, выполнение запроса может быть на порядок быстрее, чем тот же самый запрос на не разбитой на разделы таблицы, содержащей те же самые определения столбца и данные.

Оптимизатор запроса может выполнять сокращение всякий раз, когда условие WHERE может быть уменьшено до любого одного из следующего:

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

Сокращение может также применяться к коротким диапазонам, которые оптимизатор может преобразовывать в эквивалентные списки значений. Например, в предыдущем примере, предложение WHERE может быть преобразовано в WHERE region_code IN (125, 126, 127, 128, 129, 130). Затем оптимизатор может определять, что первые три значения в списке найдены в разделе p1, следующие три значения в разделе p2 и что другие разделы не содержат никаких релевантных значений.

Этот тип оптимизации может применяться всякий раз, когда выражение выделения разделов состоит из равенства или диапазона, который может быть уменьшен до набора равенств, или же когда выражение выделения разделов представляет связь уменьшения или увеличение. Сокращение может также быть применено для таблиц, разбитых на разделы на основании столбцов DATE или DATETIME, когда выражение выделения разделов использует функцию YEAR() или TO_DAYS(). Обратите внимание: в будущих версиях MySQL планируется добавлять поддержку сокращения для дополнительных функций, которые действуют на значения DATE или DATETIME, возвращают целое число и увеличиваются или уменьшаются. Например, предположите, что таблица t2, определенная как показано здесь, разбита на разделы на столбце DATE:

CREATE TABLE t2 (fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL,
                 region_code TINYINT UNSIGNED NOT NULL,
                 dob DATE NOT NULL)
       PARTITION BY RANGE(YEAR(dob)) (
                 PARTITION d0 VALUES LESS THAN (1970),
                 PARTITION d1 VALUES LESS THAN (1975),
                 PARTITION d2 VALUES LESS THAN (1980),
                 PARTITION d3 VALUES LESS THAN (1985),
                 PARTITION d4 VALUES LESS THAN (1990),
                 PARTITION d5 VALUES LESS THAN (2000),
                 PARTITION d6 VALUES LESS THAN (2005),
                 PARTITION d7 VALUES LESS THAN MAXVALUE);

Следующие запросы к t2 могут использовать сокращение:

SELECT * FROM t2 WHERE dob = '1982-06-23';
SELECT * FROM t2 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';
SELECT * FROM t2 WHERE YEAR(dob) IN (1979, 1980, 1983, 1985, 1986, 1988);
SELECT * FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'

В случае последнего запроса оптимизатор может также действовать следующим образом:

  1. Найти раздел, содержащий нижний конец диапазона..

    YEAR('1984-06-21') выдает значение 1984, которое найдено в разделе d3.

  2. Найти раздел, содержащий верхний конец диапазона..

    YEAR('1999-06-21') оценивается к 1999, которое найдено в разделе d5.

  3. Просмотреть только эти два раздела и любые разделы, которые могут находиться между ними.

    В этом случае, это означает, что просмотрены только разделы d3, d4 и d5. Остающиеся разделы могут безопасно игнорироваться (и игнорируются).

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

Рассмотрите таблицу, которая разбита на разделы LIST, где выражение выделения разделов увеличивается или уменьшается, типа таблицы t3, показанной здесь. В этом примере мы принимаем для краткости, что столбец region_code ограничен значениями от 1 до 10.

CREATE TABLE t3 (fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL,
                 region_code TINYINT UNSIGNED NOT NULL,
                 dob DATE NOT NULL)
       PARTITION BY LIST(region_code) (
                    PARTITION r0 VALUES IN (1, 3),
                    PARTITION r1 VALUES IN (2, 5, 8),
                    PARTITION r2 VALUES IN (4, 9),
                    PARTITION r3 VALUES IN (6, 7, 10));

Для запроса типа SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3 оптимизатор определяет, в которых разделах значения 1, 2 и 3 найдены (r0 и r1) и пропускает остающиеся (r2 и r3).

Для таблиц, которые разбиты на разделы HASH или KEY, сокращение раздела также возможно в случаях, в которых предложение WHERE использует простое отношение = против столбца, используемого в выражении выделения разделов. Рассмотрите таблицу, созданную подобно этому:

CREATE TABLE t4 (fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL,
                 region_code TINYINT UNSIGNED NOT NULL,
                 dob DATE NOT NULL)
       PARTITION BY KEY(region_code) PARTITIONS 8;

Любой запрос типа этого может быть сокращен:

SELECT * FROM t4 WHERE region_code = 7;

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

SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6;
SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;

В обеих случаях, предложения WHERE преобразованы оптимизатором в WHERE region_code IN (3, 4, 5). Важно: эта оптимизация используется только, если размер диапазона меньший, чем число разделов. Рассмотрите этот запрос:

SELECT * FROM t4 WHERE region_code BETWEEN 4 AND 8;

Диапазон в предложении WHERE покрывает 5 значений (4, 5, 6, 7, 8), но t4 имеет только 4 раздела. Это означает, что предыдущий запрос никак не может быть сокращен.

Сокращение может использоваться только на целочисленных столбцах таблиц, разбитых на разделы HASH или KEY. Например, этот запрос на таблице t4 не может использовать сокращение, потому что dob столбец типа DATE:

SELECT * FROM t4 WHERE dob >=- '2001-04-14' AND dob <= '2005-10-15';

Однако, если таблица сохраняет значения года в столбце типа INT, то запрос WHERE year_col >= 2001 AND year_col <= 2005 может быть сокращен.

3.5. Ограничения выделения разделов

Этот раздел обсуждает текущие ограничения поддержки выделения разделов MySQL:




Спонсоры:
Inferno Solutions
Hosting by Hoster.ru
Хостинг:

Закладки на сайте
Проследить за страницей
Created 1996-2021 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру