WebMoney:  WMZ Z294115950220  WMR R409981405661  WME E134003968233	Visa  4274 3200 2453 6495

Глава 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 Partitioning Forum Это официальный форум обсуждения для тех, кого заинтересовала технология MySQL выделение разделов. Это показывает объявления и модификации от MySQL разработчиков и других. Это контролируется членами Partitioning Development and Documentation Teams. Mikael Ronstrm's Blog MySQL Partitioning Architect and Lead Developer Mikael Ronstr├╢m часто регистрирует статьи относительно работы с выделением разделов MySQL и с кластером MySQL. PlanetMySQL Место новостей по MySQL, показывающее MySQL-касающиеся блоги, которые должны быть интересными любому, использующему MySQL. Двоичная версия 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 могут использоваться, чтобы определить максимальные и минимальные числа строк, соответственно, которые могут быть сохранены в каждом разделе таблицы. Некоторые из преимуществ выделения разделов: Можно сохранять большее количество данных в одной таблице, чем может быть записано на одиночном диске или файловой системе. Данные, которые теряют полноценность, часто легко могут быть удалены из таблицы, удаляя раздел, содержащий только эти данные. Наоборот, процесс добавления новых данных в некоторых случаях может быть значительно облегчен, добавляя новый раздел специально для этих данных. Некоторые запросы могут быть значительно оптимизированы в том, что данные, удовлетворяющие предложению WHERE могут быть сохранены только на одном или большем количестве разделов, таким образом исключая любые остающиеся разделы из поиска. Поскольку разделы могут быть изменены после того, как разбитая на разделы таблица была создана, Вы можете реорганизовать данные, чтобы расширить частые запросы, которые, возможно, были медленными, когда схема выделения разделов была сначала установлена. Эта возможность, иногда упоминаемая как сокращение раздела (partition pruning), была выполнена в MySQL 5.1.6. Другие выгоды, обычно связываемые с выделением разделов, включены в следующий список. Эти свойства в настоящее время не выполнены в MySQL Partitioning, но высоки в списке приоритетов. Запросы, включающие составные функции типа SUM() и COUNT(), легко могут быть распараллелены. Простым примером такого запроса мог бы быть SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id;. Запрос может быть выполнен одновременно на каждом разделе, и результат получен просто суммируя результаты, полученные для всех разделов. Достижение большей производительности запроса благодаря разбросу данных по разным дискам. 3.2. Типы раздела Этот раздел обсуждает типы выделения разделов, которые доступны в MySQL 5.1. Они включают: RANGE partitioning : назначает строки разделам, основанным на значениях столбца, попадающих внутрь заданного диапазона. LIST partitioning: подобно выделению разделов диапазоном, за исключением того, что раздел выбран основанным на столбцах, соответствующих одному из набора дискретных значений. HASH partitioning: раздел выбран основанным на значении, возвращенном определяемым пользователем выражением, которое функционирует на значениях столбца в строках, которые будут вставлены в таблицу. Функция может состоять из любого выражения, допустимого в MySQL, которое выдает не отрицательное целочисленное значение. KEY partitioning: подобно выделению разделов hash, за исключением того, что обеспечены только один или большее количество столбцов, которые будут оценены, и сервер MySQL обеспечивает собственную хэш-функцию. Эти столбцы могут содержать не целочисленные значения, так как хэш-функция, обеспеченная MySQL, гарантирует целочисленный результат, независимо от типа данных столбца. Очень общее использование выделения разделов базы данных должно выделять данные по времени. Некоторые системы баз данных поддерживают явное выделение разделов даты, которое 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. Выделение разделов по диапазону особенно полезно когда: Вы хотите удалить старые данные. Если Вы используете схему выделения разделов, показанную выше, Вы можете просто использовать ALTER TABLE employees DROP PARTITION p0; , чтобы удалять все строки в отношении служащих, оставивших работу до 1991. Для таблицы с очень многими строками, это может быть намного более эффективно, чем выполнение запроса DELETE, например, DELETE FROM employees WHERE YEAR(separated) <=1990;. Вы хотите использовать столбец, содержащий значения даты, времени или значения, являющиеся результатом некоторого другого ряда. Вы часто выполняете запросы, которые зависят непосредственно от столбца, используемого для выделения разделов таблицы. Например, при выполнении запроса типа SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;, MySQL может быстро определять, что только раздел p2 должен быть просмотрен, потому что остающиеся разделы не могут содержать записи, удовлетворяющие предложению WHERE. 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 код работы является символом, а не числом. Каждый символ соответствует специфической работе, и мы желаем выделить разделы таблицы таким способом, чтобы записи для служащих, имеющих подобные работы, или работающих в том же самом отделе, были сгруппированы в том же самом разделе, согласно следующей схеме: Категория работы или отдел Коды работы Management D, M, O, P Sales B, L, S Technical A, E, G, I, T Clerical K, N, Y Support C, F, J, R, V Unassigned Empty Так как мы не можем использовать символьные значения в списках, мы должны преобразовать их в целых числа или 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 получен согласно следующему алгоритму: Находят следующую степень 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. Берется N = F(column_list) & (V - 1). Пока N >= num: Берется V=CEIL(V/2) Берется N = N & (V - 1) Например, предположите, что таблица 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 подобен правилам для создания таблицы, которая разбита на разделы хэшем. Главные различия состоят в том что: KEY используется вместо HASH. KEY берет только список из одного или большего количества имен столбцов. Начиная с MySQL 5.1.5, если таблица имеет первичный ключ, столбцы, по которым происходит выделение разделов, должны включать хотя бы его часть (или весь ключ). Начиная с MySQL 5.1.6, KEY берет список из нуля или большего количества имен столбца. Если никакое имя столбца не определено как ключ выделения разделов, используется первичный ключ таблицы, если он имеется. Например, следующая инструкция CREATE TABLE допустима в MySQL 5.1.6 или позже: CREATE TABLE k1 (id INT NOT NULL PRIMARY KEY, name VARCHAR(20)) PARTITION BY KEY() PARTITIONS 2; Если не имеется никакого первичногоключа, но имеется уникальный ключ, то именно уникальный ключ используется для выделения разделов: CREATE TABLE k1 (id INT NOT NULL, name VARCHAR(20), UNIQUE KEY (id)) PARTITION BY KEY() PARTITIONS 2; Однако, если уникальный столбец ключа не был определен как NOT NULL, то предыдущая инструкция будет терпеть неудачу. В обоих из этих случаев ключом выделения разделов является столбец id, даже при том, что это не показывается в выводе SHOW CREATE TABLE или в столбце PARTITION_EXPRESSION таблицы INFORMATION_SCHEMA.PARTITIONS. В отличие от случая с другими типами выделения разделов, столбцы, используемые для выделения разделов KEY, не ограничены значениями NULL или целым числом. Например, следующая инструкция CREATE TABLE допустима: CREATE TABLE tm1 (s1 CHAR(32) PRIMARY KEY) PARTITION BY KEY(s1) PARTITIONS 10; Предшествующая инструкция не была бы допустима для любого другого типа выделения разделов. Примечание: в этом случае, простое использование PARTITION BY KEY() было бы также допустимо и имело бы тот же самый эффект. что и PARTITION BY KEY(s1), поскольку s1 является первичным ключом таблицы. Обратите внимание: также начиная с MySQL 5.1.6, таблицы, использующие NDB Cluster неявно разбиты на разделы KEY, используя первичный ключ таблицы как ключ выделения разделов. Когда таблица кластера не имеет никакого явного первичного ключа, применяется скрытый первичный ключ, сгенерированный NDB для каждой таблицы кластера. Важно: для таблицы с разделением по ключу, использующей любой тип памяти MySQL, кроме NDB Cluster, Вы не можете выполнять ALTER TABLE DROP PRIMARY KEY, так как это сгенерирует ошибку ERROR 1466 (HY000): Field in list of fields for partition function not found in table. Это не проблема для таблиц MySQL Cluster, которые разбиты на разделы 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)); Некоторые синтаксические элементы: Каждый раздел должен иметь то же самое число подразделов. Если Вы явно определяете любые подразделы, использующие SUBPARTITION на любом разделе разбитой на разделы таблицы, Вы должны определить их все. Другими словами, следующая инструкция будет терпеть неудачу: CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH(TO_DAYS(purchased)) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0, SUBPARTITION s1), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s2, SUBPARTITION s3)); Эта инструкция свалится даже если добавить предложение SUBPARTITIONS 2. Каждое предложение SUBPARTITION должно включить (как минимум) имя для подраздела. Иначе Вы можете устанавливать любую желательную опцию для подраздела или позволить принимать настройку по умолчанию для этой опции. В MySQL 5.1.7 и ранее имена подразделов должны быть уникальны внутри каждого раздела, но не должны быть уникальны внутри таблицы в целом. Начиная с MySQL 5.1.8, имена подразделов должны быть уникальны для всей таблицы. Например, следующая инструкция CREATE TABLE допустима в MySQL 5.1.8 и позже: CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH(TO_DAYS(purchased)) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0, SUBPARTITION s1), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2, SUBPARTITION s3), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s4, SUBPARTITION s5)); Предыдущая инструкция также допустима для версий MySQL до 5.1.8. Подразделы могут использоваться с особенно большими таблицами, чтобы распределить данные и индексы на много дисков. Предположите, что Вы имеете 6 дисков, установленные как /disk0, /disk1, /disk2 и т. д. Теперь рассмотрите следующий пример: CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH(TO_DAYS(purchased)) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0 DATA DIRECTORY = '/disk0/data' INDEX DIRECTORY = '/disk0/idx', SUBPARTITION s1 DATA DIRECTORY = '/disk1/data' INDEX DIRECTORY = '/disk1/idx'), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2 DATA DIRECTORY = '/disk2/data' INDEX DIRECTORY = '/disk2/idx', SUBPARTITION s3 DATA DIRECTORY = '/disk3/data' INDEX DIRECTORY = '/disk3/idx'), PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION s4 DATA DIRECTORY = '/disk4/data' INDEX DIRECTORY = '/disk4/idx', SUBPARTITION s5 DATA DIRECTORY = '/disk5/data' INDEX DIRECTORY = '/disk5/idx')); В этом случае отдельный диск используется для данных и индексов каждого RANGE. Много других разновидностей возможны, другой пример мог бы быть таким: CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH(TO_DAYS(purchased)) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0a DATA DIRECTORY = '/disk0' INDEX DIRECTORY='/disk1', SUBPARTITION s0b DATA DIRECTORY = '/disk2' INDEX DIRECTORY='/disk3'), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s1a DATA DIRECTORY = '/disk4/data' INDEX DIRECTORY = '/disk4/idx', SUBPARTITION s1b DATA DIRECTORY = '/disk5/data' INDEX DIRECTORY = '/disk5/idx'), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s2a, SUBPARTITION s2b)); Здесь хранение следующее: Строки с датами purchased до 1990 занимают обширное количество места, поэтому поделены 4 путями: с отдельным диском, специализированным для данных, и с дополнительным диском для индексов для каждого из двух подразделов (s0a и s0b), входящих в раздел p0. Другими словами: Данные для подраздела s0a сохранены на /disk0. Индексы для подраздела s0a сохранены на /disk1. Данные для подраздела s0b сохранены на /disk2. Индексы для подраздела s0b сохранены на /disk3. Строки, содержащие даты в пределах от 1990 до 1999 (раздел p1) не требуют так много памяти, как даты до 1990. Они размазаны между 2 дисками (/disk4 и /disk5): Данные и индексы, принадлежащие первому подразделу (s1a) раздела p1, сохранены на /disk4: данные в /disk4/data, а индексы в /disk4/idx. Данные и индексы, принадлежащие второму подразделу (s1b) раздела p1, сохранены на /disk5: данные в /disk5/data, а индексы в /disk5/idx. Строки, отражающие даты с года 2000 до текущей (раздел p2), не занимают так много места, как любой из двух предыдущих диапазонов. В настоящее время достаточно сохранить всех их в заданном по умолчанию расположении. В будущем, когда число purchases в течение десятилетия, начинающегося годом 2000, вырастет до отметки, где заданное по умолчанию расположение больше не обеспечивает достаточного места, соответствующие строки могут перемещаться, используя ALTER TABLE ... REORGANIZE PARTITION. 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: Предложения 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; 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. Они могут использоваться для выполнения операций этого типа на одном или большем количестве разделов непосредственно, как описано в следующем списке: Rebuilding partitions : восстанавливает раздел: это имеет тот же самый эффект, что и удаление всех записей, сохраненных в разделе, с последующей перевставкой их. Это может быть полезно с целью дефрагментации. Пример: ALTER TABLE t1 REBUILD PARTITION p0, p1; Optimizing partitions: если Вы удалили большое количество строк из раздела или сделали много изменений для разбитой на разделы таблицы со строками переменной длины (то есть, имея столбцы VARCHAR, BLOB или TEXT), Вы можете использовать ALTER TABLE ... OPTIMIZE PARTITION, чтобы восстановить неиспользуемое место и дефрагментировать файл данных раздела: ALTER TABLE t1 OPTIMIZE PARTITION p0, p1; Использование OPTIMIZE PARTITION на данном разделе эквивалентно выполнению CHECK PARTITION, ANALYZE PARTITION и REPAIR PARTITION. Analyzing partitions: читает и сохраняет распределения ключа для разделов. Пример: ALTER TABLE t1 ANALYZE PARTITION p3; Repairing partitions: это восстанавливает разрушенные разделы. Пример: ALTER TABLE t1 REPAIR PARTITION p0,p1; Checking partitions: Вы можете проверять раздел на ошибки способом, которым Вы можете использовать CHECK TABLE с не разбитыми на разделы таблицами: ALTER TABLE trb3 CHECK PARTITION p1; Эта команда сообщит Вам, если данные или индексы в разделе p1 таблицы t1 разрушены. Если дело обстоит так, используйте ALTER TABLE ... REPAIR PARTITION для ремонта раздела. Вы можете также использовать утилиту mysqlcheck или myisamchk, чтобы выполнить эти задачи, действуя на отдельных .MYI-файлах, сгенерированных, выделяя разделы таблицы. 3.3.4. Получение информации относительно разделов Этот раздел обсуждает получение информации относительно существующих разделов, что может быть выполнено несколькими способами. Они включают: Использование инструкции SHOW CREATE TABLE, чтобы просмотреть предложения выделения разделов, используемые в создании разбитой на разделы таблицы. Использование инструкции SHOW TABLE STATUS, чтобы определить, разбита ли таблица на разделы вообще. Запрос таблицы INFORMATION_SCHEMA.PARTITIONS. Использование инструкции EXPLAIN PARTITIONS SELECT, чтобы видеть, которые разделы используются данным SELECT. 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: Вы не можете использовать ключевые слова PARTITIONS и EXTENDED вместе в том же самом запросе EXPLAIN ... SELECT. Попытка сделать так производит синтаксическую ошибку. Если EXPLAIN PARTITIONS используется, чтобы исследовать запрос для не разбитой на разделы таблицы, никакая ошибка не будет произведена, но значение столбца partitions всегда NULL. 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 может быть уменьшено до любого одного из следующего: partition_column = constant partition_column IN (constant1, constant2, ..., constantN) В первом случае, оптимизатор просто оценивает выражение выделения разделов для данного значения, определяет, который раздел содержит то значение, и просматривает только этот раздел. Во втором случае оптимизатор оценивает выражение выделения разделов для каждого значения в списке, создает список соответствия разделов, а затем просматривает только разделы в этом списке. Сокращение может также применяться к коротким диапазонам, которые оптимизатор может преобразовывать в эквивалентные списки значений. Например, в предыдущем примере, предложение 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' В случае последнего запроса оптимизатор может также действовать следующим образом: Найти раздел, содержащий нижний конец диапазона.. YEAR('1984-06-21') выдает значение 1984, которое найдено в разделе d3. Найти раздел, содержащий верхний конец диапазона.. YEAR('1999-06-21') оценивается к 1999, которое найдено в разделе d5. Просмотреть только эти два раздела и любые разделы, которые могут находиться между ними. В этом случае, это означает, что просмотрены только разделы 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: Начиная с MySQL 5.1.12, следующие конструкции не разрешаются в выражениях выделения разделов: Вложенные обращения к функциям (то есть, конструкции типа func1(func2( col_name))). Сохраненные процедуры, функции, UDF или plugins. Объявленные переменные или переменные пользователя. Начиная с MySQL 5.1.12, следующие функции MySQL специально не позволяются в выражениях выделения разделов: GREATEST() ISNULL() LEAST() CASE() IFNULL() NULLIF() BIT_LENGTH() CHAR_LENGTH() CHARACTER_LENGTH() FIND_IN_SET() INSTR() LENGTH() LOCATE() OCTET_LENGTH() POSITION() STRCMP() CRC32() ROUND() SIGN() DATEDIFF() PERIOD_ADD() PERIOD_DIFF() TIMESTAMPDIFF() UNIX_TIMESTAMP() WEEK() CAST() CONVERT() BIT_COUNT() INET_ATON() Использование арифметических операторов +, -, * и / разрешается в выражениях выделения разделов. Однако, результат должен быть целочисленным значением или NULL (за исключением [LINEAR] KEY). Начиная с MySQL 5.1.12, разрядные операторы |, &, ^, <<, >> и ~ не разрешаются в выражениях выделения разделов. Начиная с MySQL 5.1.12, только следующие функции MySQL поддерживаются в выражениях выделения разделов: ABS() ASCII() CEILING() DAY() DAYOFMONTH() DAYOFWEEK() DAYOFYEAR() EXTRACT() FLOOR() HOUR() MICROSECOND() MINUTE() MOD() MONTH() ORD() QUARTER() SECOND() TIME_TO_SEC() TO_DAYS() WEEKDAY() WEEKOFYEAR() YEAR() YEARWEEK() Важно: Вы должны иметь в виду, что результаты многих функций MySQL и операторов могут изменять SQL-режим сервера. По этой причине нежелательно изменять этот режим после создания разбитых на разделы таблиц. Использование функций ASCII() или ORD(), чтобы преобразовать строковое значение (столбца типа CHAR или VARCHAR) к целому числу работает только, когда строка использует 8-разрядный набор символов. Объединение, используемое для строки, может быть любым объединением для связанного набора символов. Однако, объединения latin1_german2_ci, latin2_czech_cs и cp1250_czech_cs не могут использоваться, вследствие того, что эти объединения требуют символьных преобразований "один ко многим". Если при создании таблиц с очень большим количеством разделов Вы сталкиваетесь с сообщением об ошибках типа Got error 24 from storage engine, Вы должны увеличить значение переменной системы open_files_limit. Разбитые на разделы таблицы не поддерживают внешние ключи. Это включает разбитые на разделы таблицы, использующие тип памяти InnoDB. Разбитые на разделы таблицы не поддерживают индексы FULLTEXT. Это включает разбитые на разделы таблицы, использующие тип памяти MyISAM. Разбитые на разделы таблицы не поддерживают столбцы GEOMETRY. Начиная с MySQL 5.1.8, временные таблицы не могут быть разбиты на разделы (Глюк #17497 ). Таблицы, использующие тип памяти MERGE, не могут быть разбиты на разделы. Разбитые на разделы таблицы, использующие тип памяти CSV, не обеспечиваются. Начиная с MySQL 5.1.12, невозможно создать разбитые на разделы таблицы CSV вообще. До MySQL 5.1.6 таблицы, использующие тип памяти BLACKHOLE, также не могли быть разбиты на разделы. Выделение разделов KEY (или LINEAR KEY) представляет собой единственный тип выделения разделов обеспечиваемого для типа памяти NDB. Начиная с MySQL 5.1.12, невозможно создать таблицу Cluster, использующую любое выделение разделов, кроме [LINEAR ] KEY, а попытка это сделать вызывает ошибку. При выполнении обновления, таблицы, использующие любой тип памяти (кроме NDBCLUSTER), которые разбиты на разделы KEY, должны разгрузиться и перезагрузиться. Все разделы таблицы и подразделы (если имеется любой из последних) должны использовать тот же самый тип памяти. Ключ выделения разделов должен быть целочисленным столбцом или выражением, которое решается к целому числу. Столбец или значение выражения может также быть NULL. Одна исключительная ситуация к этому ограничению происходит при выделении разделов [LINEAR] KEY, где возможно использовать столбцы других типов как ключи выделения разделов потому, что MySQL с помощью хэш-функции производит внутренний ключ правильного типа данных из этих типов. Например, следующая инструкция CREATE TABLE допустима: CREATE TABLE tkc (c1 CHAR) PARTITION BY KEY(c1) PARTITIONS 4; Эта исключительная ситуация не относится к типам столбцов BLOB или TEXT. Ключ выделения разделов не может быть подзапросом, даже если этот подзапрос решается к целочисленному значению или NULL. Все столбцы, используемые в выражении выделения разделов для разбитой на разделы таблицы, должны быть частью каждого уникального ключа, который таблица может иметь. Другими словами, каждый уникальный ключ на таблице должен использовать каждый столбец в выражении выделения разделов таблиц. Например, каждая из следующих инструкций создания таблицы недопустима: CREATE TABLE t1 (col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col2)) PARTITION BY HASH(col3) PARTITIONS 4; CREATE TABLE t2 (col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1), UNIQUE KEY (col3)) PARTITION BY HASH(col1 + col3) PARTITIONS 4; CREATE TABLE t3 (col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col2), UNIQUE KEY (col3)) PARTITION BY HASH(col1 + col3) PARTITIONS 4; В каждом случае, предложенная таблица имела бы по крайней мере один уникальный ключ, который не включает все столбцы, используемые в выражении выделения разделов. Каждая из следующих инструкций допустима и представляет один способ, которым соответствующая недопустимая инструкция создания таблицы могла бы быть сделана рабочей: CREATE TABLE t1 (col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col2, col3)) PARTITION BY HASH(col3) PARTITIONS 4; CREATE TABLE t2 (col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col3)) PARTITION BY HASH(col1 + col3) PARTITIONS 4; CREATE TABLE t3 (col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col2, col3), UNIQUE KEY (col3)) PARTITION BY HASH(col3) PARTITIONS 4; Так как каждый первичный ключ по определению уникальный ключ, это ограничение также включает первичный ключ таблицы, если он есть. Например, следующие две инструкции недопустимы: CREATE TABLE t4 (col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, PRIMARY KEY(col1, col2)) PARTITION BY HASH(col3) PARTITIONS 4; CREATE TABLE t5 (col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, PRIMARY KEY(col1, col3), UNIQUE KEY(col2)) PARTITION BY HASH(YEAR(col2)) PARTITIONS 4; В обоих случаях первичный ключ не включает все столбцы, названные в выражении выделения разделов. Однако, обе из следующих двух инструкций допустимы: CREATE TABLE t6 (col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, PRIMARY KEY(col1, col2)) PARTITION BY HASH(col1 + YEAR(col2)) PARTITIONS 4; CREATE TABLE t7 (col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, PRIMARY KEY(col1, col2, col4), UNIQUE KEY(col2, col1)) PARTITION BY HASH(col1 + YEAR(col2)) PARTITIONS 4; Если таблица не имеет никакого уникального или первичного ключа, то это ограничение не применяется, и Вы можете использовать любой столбец или столбцы в выражении выделения разделов, пока тип столбца совместим с типом выделения разделов. По той же самой причине Вы не можете позже добавлять уникальный ключ к разбитой на разделы таблице, если этот ключ не включает все столбцы, используемые выражением выделения разделов таблицы. Рассмотрите разбитую на разделы таблицу, определенную так, как показано здесь: CREATE TABLE t_no_pk (c1 INT, c2 INT) PARTITION BY RANGE(c1) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (30), PARTITION p3 VALUES LESS THAN (40)); Возможно добавить первичный ключ к t_no_pk, используя любую из этих инструкций ALTER TABLE: # possible PK ALTER TABLE t_no_pk ADD PRIMARY KEY(c1); # also a possible PK ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2); Однако, следующие операторы свалятся потому, что c1 представляет собой часть ключа выделения разделов, но не часть предложенного первичного ключа: # fails with ERROR 1482 ALTER TABLE t_no_pk ADD PRIMARY KEY(c2); С тех пор как t_no_pk имеет только c1 в выражении выделения разделов, попытка добавления уникального ключа на c2 всегда провалится. Однако, Вы можете добавлять уникальный ключ, который использует c1 и c2. Эти правила также относятся к существующим не разбитым на разделы таблицам, в которых Вы желаете выделить разделы используя ALTER TABLE ... PARTITION BY. Рассмотрите таблицу np_pk: CREATE TABLE np_pk (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), added DATE, PRIMARY KEY (id)); Следующяя инструкция ALTER TABLE потерпит неудачу с ошибкой, потому что столбец added не является частью любого уникального ключа в таблице: ALTER TABLE np_pk PARTITION BY HASH(TO_DAYS(added)) PARTITIONS 4; Эта инструкция, однако, была бы полностью допустима: ALTER TABLE np_pk PARTITION BY HASH(id) PARTITIONS 4; В случае np_pk единственный столбец, который может использоваться как часть выражения выделения разделов, id. Если Вы желаете выделить разделы этой таблицы, использующие любой другой столбец или столбцы в выражении выделения разделов, Вы должны сначала изменить таблицу, добавляя желательный столбец или столбцы на первичный ключ или удаляя первичный ключ в целом. Подразделы ограничены выделением разделов HASH или KEY. Разделы HASH и KEY не могут быть подразбиты на разделы.

Поиск На сайте В Яндексе