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

Глава 20. Разделение Эта глава обсуждает определяемое пользователем разделение. В MySQL 8.0 есть поддержка разделения для механизма хранения InnoDB. Механизм хранения NDB, используемый MySQL Cluster также оказывает поддержку разделения, но NDB не входит в MySQL 8.0. MySQL 8.0 в настоящее время не поддерживает разделение таблиц, используя любой механизм хранения кроме InnoDB. Попытка составить разделенные таблицы, используя механизм хранения, который не предоставляет поддержку разделения, терпит неудачу с ER_CHECK_NOT_IMPLEMENTED. MySQL 8.0 Community включает поддержку разделения, оказанную InnoDB. Для информации о разделении в версии для предприятий см. главу 27. Если Вы собираете MySQL 8.0 из исходных текстов, конфигурируя с InnoDB этого достаточно, чтобы произвести двоичные модули с поддержкой разделения InnoDB. Невозможно отключить поддержку разделения InnoDB. Несколько типов разделения поддержаны, так же как подразделение, см. разделы 20.2 и 20.2.6. Раздел 20.3 описывает методы добавления, удаления и изменения разделения в существующих разделенных таблицах. Раздел 20.3.4, обсуждает табличные команды обслуживания для использования с разделенными таблицами. Таблица PARTITIONS в INFORMATION_SCHEMA предоставляет информацию о разделении и разделенных таблицах. См. раздел 22.14 . Для некоторых примеров запросов к этой таблицы см. раздел 20.2.7. Для известных проблем с разделением в MySQL 8.0 см. раздел 20.6. Вы можете также найти, что следующие ресурсы полезны, работая с разделенными таблицами. MySQL Partitioning Forum Это официальный дискуссионный форум для заинтересованных или экспериментирующих с разделением в MySQL. Это показывает объявления и обновления от разработчиков MySQL и других. Это проверено членами развития разделения и документации. Mikael Ronstrц╤m's Blog MySQL Partitioning Architect and Lead Developer Mikael Ronstrц╤m часто отправляет сюда статьи относительно его работы с MySQL Partitioning и MySQL Cluster. PlanetMySQL Новостной сайт MySQL, показывающий MySQL-связанные блоги, которые должны представлять интерес для любого, использующего MySQL. 20.1. Краткий обзор разделения в MySQL Этот раздел обеспечивает концептуальный краткий обзор разделения в MySQL 8.0. Для информации об ограничениях разделения см. раздел 20.6. Стандарт SQL не обеспечивает руководства относительно физических аспектов хранения данных. Сам SQL язык предназначен, чтобы работать независимо от любых структур данных, лежащих в основе схем, таблиц, строк или столбцов, с которыми он работает. Тем не менее, наиболее усовершенствованные системы управления базой данных развили некоторые средства определения физического местоположения, которое будет использоваться для того, чтобы сохранить определенные части данных с точки зрения файловой системы, аппаратных средств или даже обоих. В MySQL механизм хранения InnoDB долго поддерживал понятие табличного пространства (см. раздел 16.7), а MySQL Server даже до введения разделения мог быть сконфигурирован, чтобы использовать различные физические каталоги для того, чтобы сохранить различные базы данных (см. раздел 9.12.2, для объяснения того, как это сделано). Разделение двигает это понятие на шаг далее, позволяя Вам распределить части отдельных таблиц по файловой системе согласно правилам, которые Вы можете установить в значительной степени как необходимо. В действительности, различные части таблицы сохранены как отдельные таблицы в различных местоположениях. Выбранное пользователем правило, по которому достигнуто разделение данных, известно как функция разделения, которая в MySQL может быть модулем, простым соответствием ряда диапазонов или списку, внутренней хеширующей функцией или линейной хеширующей функции. Функция выбрана согласно типу разделения, определенному пользователем, и берет в качестве параметра значение снабженного пользователем выражения. Это выражение может быть значением столбца, функцией, действующей на одно или более значений столбцов, или набором значений столбцов, в зависимости от типа разделения, который используется. В случае RANGE, LIST, и [LINEAR] HASH значение столбца разделения передают функции разделения, которая возвращает целочисленное значение, представляющее число разделения, в котором должна быть сохранена именно эта запись. Эта функция должна быть непостоянной и неслучайной. Это, возможно, не содержит запросов, но может использовать выражение SQL, которое допустимо в MySQL, пока это выражение возвращает NULL или целое число intval таким образом, что -MAXVALUE <= intval <= MAXVALUE (MAXVALUE используется, чтобы представить наименьшее значение верхней границы для типа рассматриваемого целого числа. -MAXVALUE представляет самую большую нижнюю границу. Для [LINEAR] KEY, RANGE COLUMNS и LIST COLUMNS выражение разделения состоит из списка из одного или более столбцов. Для [LINEAR] KEY функция разделения поставляется MySQL. Для получения дополнительной информации о разрешенных типах столбца и функций разделения см. раздел 20.2 и раздел 14.1.15, который обеспечивает описания синтаксиса разделения и дополнительные примеры. Для информации об ограничениях на разделение функций см. раздел 20.6.3. Это известно как горизонтальное разделение, то есть, различные строки таблицы могут быть назначены на различное физическое разделение. MySQL 8.0 не поддерживает вертикальное разделение, в котором различные столбцы таблицы назначены на различное физическое разделение. Нет никаких планов ввести вертикальное разделение в MySQL. Для того, чтобы составить разделенные таблицы, Вы должны использовать механизм хранения, который поддерживает их. В MySQL 8.0 все разделение той же самой разделенной таблицы должно использовать тот же самый механизм хранения. Однако, нет ничего препятствующего Вам использовать различные механизмы хранения для различных разделенных таблиц на том же самом сервере MySQL или даже в той же самой базе данных. В MySQL 8.0 единственный механизм хранения, который поддерживает разделение это InnoDB. Разделение не может использоваться с механизмами хранения, которые не поддерживают его, они включают MyISAM, MERGE, CSV и FEDERATED . Составляя разделенную таблицу, механизм хранения по умолчанию используется как для создания любой таблицы. Чтобы переопределить это поведение, необходимо только использовать опцию [STORAGE] ENGINE, как для таблицы, которая не разделена. Целевой механизм хранения должен оказать поддержку разделения, или запрос терпит неудачу. Вы должны иметь в виду, что [STORAGE] ENGINE (и другие табличные опции), должна быть перечислена прежде, чем любые опции разделения используются в CREATE TABLE. Этот пример показывает, как составить таблицу, которая разделена хешем на 6 разделов и использует механизм хранения InnoDB (независимо от значения default_storage_engine): 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 8.0 это не имеет никакого эффекта. Если иное не определено, остающиеся примеры в этом обсуждении принимают, что default_storage_engine InnoDB. Разделение относится ко всем данным и индексам таблицы: Вы не можете разделить только данные и не разделить индексы или наоборот, и при этом Вы не можете разделить только часть таблицы. Данные и индексы для каждого разделения, могут быть назначены на определенный каталог, используя опции DATA DIRECTORY и INDEX DIRECTORY предложения PARTITION запроса CREATE TABLE. InnoDB поддерживает опции DATA DIRECTORY и INDEX DIRECTORY для отдельных разделов и подразделов. Все столбцы, используемые в выражении разделения таблицы, должны быть частью уникального ключа, который таблица может иметь, включая любой первичный ключ. Это означает, что таблица, такая как эта, создаваемая следующим запросом, не может быть разделена: CREATE TABLE tnp (id INT NOT NULL AUTO_INCREMENT, ref BIGINT NOT NULL, name VARCHAR(255), PRIMARY KEY pk (id), UNIQUE KEY uk (name)); Поскольку ключи pk и uk не имеют никаких столбцов вместе, нет никаких столбцов, доступных для использования в выражении разделения. Возможные обходные решения в этой ситуации включают добавление столбца name к первичному ключу таблицы, добавляя столбец id к uk, или просто удаляя уникальный ключ в целом. См. раздел 20.6.1 для получения дополнительной информации. Кроме того, MAX_ROWS и MIN_ROWS могут использоваться, чтобы определить максимальные и минимальные числа строк, соответственно, которые могут быть сохранены в каждом разделении. См. раздел 20.3. Некоторые преимущества разделения перечислены здесь: Разделение позволяет хранить больше данных в одной таблице, чем можно сохранить на единственном диске или разделе файловой системы. Данные, которые теряют полноценность, могут часто легко удаляться из разделенной таблицы, удаляя раздел или разделы, содержащие только эти данные. Наоборот процесс добавления новых данных может в некоторых случаях быть очень облегчен, добавляя один или более разделов для того, чтобы хранить определенные данные. Некоторые запросы могут быть очень оптимизированы в силу факта что данные, удовлетворяющие предложению WHERE могут быть сохранены только в одном разделе, который автоматически исключает любые остающиеся разделы из поиска. Поскольку разделение может быть изменено после того, как разделенная таблица была составлена, Вы можете реорганизовать свои данные, чтобы улучшить частые запросы, которые не могли часто использоваться, когда схема разделения была сначала настроена. Эта способность исключить несоответствие разделения (и таким образом любые строки, которые они содержат) часто упоминается как сокращение разделения. См. раздел 20.4. Кроме того, MySQL 8.0 допускает явный выбор разделения для запросов. Например, SELECT * FROM t PARTITION (p0,p1) WHERE c < 5 выбирает только те строки в разделениях p0 и p1, которые соответствуют условию WHERE. В этом случае MySQL не проверяет никакое другое разделение таблицы t, это может очень ускорить запросы, когда Вы уже знаете, какое разделение Вы хотите исследовать. Выбор разделения также поддержан для запросов модификации данных DELETE, INSERT, REPLACE, UPDATE, LOAD DATA и LOAD XML. См. описания этих запросов для получения дополнительной информации и примеров. Другая выгода, обычно связанная с разделением, включена в список ниже. Эти опции в настоящее время не реализуются в разделении MySQL, но высоки в нашем списке приоритетов. Запросы, вовлекающие совокупность, функционируют так, что SUM() и COUNT() можно легко распараллелить. Простой пример такого запроса: SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id;. Мы подразумеваем, что запрос может быть выполнен одновременно на каждом разделении, а окончательный результат получен просто суммируя результаты, полученные для всех разделений. Достижение большей пропускной способности запроса в силу данных, распространяющихся по многим дискам. 20.2. Типы разделения Этот раздел обсуждает типы разделения, которые доступны в MySQL 8.0. Они включают типы, перечисленные здесь: RANGE. Этот тип разделения назначает строки на разделение, основанное на значениях столбцов, находящихся в пределах данного диапазона. См. раздел 20.2.1. Для информации о расширении к этому типу RANGE COLUMNS см. раздел 20.2.3.1. LIST. Подобно RANGE, за исключением того, что разделение выбрано основываясь на столбцах, соответствующих одному из ряда дискретных значений. См. раздел 20.2.2. Для информации о расширении к этому типу LIST COLUMNS см. раздел 20.2.3.2. HASH. С этим типом разделение выбрано основываясь на значении, возвращенном определяемым пользователем выражением, которое воздействует на значения столбцов в строках, которые будут вставлены в таблицу. Функция может состоять из любого выражения, допустимого в MySQL, которое приводит к неотрицательному целочисленному значению. Расширение к этому типу LINEAR HASH также доступно. См. раздел 20.2.4. KEY. Этот тип разделения подобен HASH за исключением того, что только один или более столбцов, которые будут оценены, поставляются, а сервер MySQL обеспечивает свою собственную хеширующую функцию. Эти столбцы могут содержать разные данные кроме целочисленных значений, так как хеширующая функция, поставляемая MySQL, гарантирует результат целого числа независимо от типа данных столбца. Расширение к этому типу LINEAR KEY. См. раздел 20.2.5. Обычное использование разделения базы данных должно выделять данные по времени. Некоторые системы базы данных поддерживают явное разделение даты, которое MySQL не осуществляет в 8.0. Однако, не трудно в 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 8.0 также возможно использовать столбцы DATE или DATETIME как столбцы разделения RANGE COLUMNS и LIST COLUMNS. Другие типы разделения требуют выражения разделения, которое приводит к целочисленному значению или 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); Дополнительные примеры использования разделения дат могут быть найдены в следующих разделах этой главы: раздел 20.2.1. раздел 20.2.4. раздел 20.2.4.1. Для более сложных примеров основанного на дате разделения см. следующие разделы: раздел 20.4. раздел 20.2.6. Разделение MySQL оптимизировано для использования с функциями TO_DAYS(), YEAR() и TO_SECONDS(). Однако, Вы можете использовать другую функцию даты и времени, которые возвращают целое число или NULL, как WEEKDAY(), DAYOFYEAR() или MONTH(). См. раздел 13.7. Независимо от типа разделения разделы всегда нумеруется автоматически и в порядке создания, начиная с 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, даже если это оценивается к целочисленному значению. Десятичные дроби не разрешены. В разделах, которые следуют далее, мы не обязательно обеспечиваем все возможные формы для синтаксиса, который может использоваться для того, чтобы создать каждый тип разделения, для этой информации, см. раздел 14.1.15. 20.2.1. RANGE Таблица, которая разделена диапазоном, разделена таким способом, что каждый раздел содержит строки, для которых значение выражения разделения находится в пределах данного диапазона. Диапазоны должны быть непрерывными, но без наложений и определены, используя оператор 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); Таблица employees, используемая здесь, не имеет никаких основных или уникальных ключей. В то время как примеры работают как показано в целях существующего обсуждения, Вы должны иметь в виду, что таблицы чрезвычайно вероятно имеют на деле первичные или уникальные ключи или те и те, и что допустимый выбор для того, чтобы разделить столбцы, зависит от столбцов, используемых для этих ключей. Для обсуждения этих проблем см. раздел 20.6.1. Эта таблица может быть разделена диапазоном многими способами, в зависимости от Ваших потребностей. Один путь состоял бы в том, чтобы использовать столбец 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, Вы можете думать об этом как о сходстве с серией проверок if ... elseif ... в C или Java. Легко решить что новая строка, содержащая данные (72, 'Mitchell', 'Wilson', '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); Как с другими примерами в этой главе, мы предполагаем, что механизм хранения по умолчанию InnoDB. Другой способ избежать ошибки, когда никакое значение соответствия не найдено, состоит в том, чтобы использовать ключевое слово IGNORE как часть запроса INSERT . Для примера см. раздел 20.2.2. Основная информация про IGNORE есть в разделе 14.2.5. MAXVALUE представляет целочисленное значение, которое всегда больше чем самое большое целочисленное значение (на математическом языке, оно служит наименьшим числом верхней границы). Теперь, любые строки, чье значение столбца store_id больше чем или равно 16 (самое высокое определенное значение) сохранены в разделе p3. В некоторый момент, когда число хранилищ увеличилось до 25, 30 или больше, Вы можете использовать ALTER TABLE, чтобы добавить новые разделы для хранилищ 21-25, 26-30 и т.д. (см. раздел 20.3 для деталей о том, как это сделать). Почти таким же способом Вы могли разделить таблицу, основанную на коде работы, то есть, на диапазонах значений столбца job_code. Например, предположим, что коды с двумя цифрами используются для регулярных рабочих (в магазине), коды с тремя цифрами используются для офиса и поддерживающего персонала, а коды с четырьмя цифрами используются для управления, Вы могли бы составить разделенную таблицу, используя следующий запрос: 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)); В этом случае все строки, касающиеся рабочих в магазине, были бы сохранены в разделе 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. Также возможно разделить таблицу RANGE, основываясь на значении столбца TIMESTAMP, применяя UNIX_TIMESTAMP() , как показано в этом примере: CREATE TABLE quarterly_report_status (report_id INT NOT NULL, report_status VARCHAR(20) NOT NULL, report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) PARTITION BY RANGE (UNIX_TIMESTAMP(report_updated)) (PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2008-01-01 00:00:00') ), PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2008-04-01 00:00:00') ), PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2008-07-01 00:00:00') ), PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2008-10-01 00:00:00') ), PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2009-01-01 00:00:00') ), PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP('2009-04-01 00:00:00') ), PARTITION p6 VALUES LESS THAN (UNIX_TIMESTAMP('2009-07-01 00:00:00') ), PARTITION p7 VALUES LESS THAN (UNIX_TIMESTAMP('2009-10-01 00:00:00') ), PARTITION p8 VALUES LESS THAN (UNIX_TIMESTAMP('2010-01-01 00:00:00') ), PARTITION p9 VALUES LESS THAN (MAXVALUE)); Любое другое вовлечение выражений TIMESTAMP не разрешено (см. Bug #42849). Разделение диапазона особенно полезно, когда одно или больше следующих условий верно: Вы хотите или должны удалить старые данные. Если Вы используете схему разделения, показанную ранее для таблицы employees, Вы можете просто использовать ALTER TABLE employees DROP PARTITION p0;, чтобы удалить все строки, касающиеся служащих, которые прекратили работать на фирму до 1991 (см. разделы 14.1.7 и 20.3. Для таблицы с очень многими строками это может быть намного более эффективным, чем выполнение DELETE, например, DELETE FROM employees WHERE YEAR(separated) <= 1990;. Вы хотите использовать столбец, содержащий дату или время или содержащий значения, являющиеся результатом некоторого другого ряда. Вы часто выполняете запросы, которые зависят непосредственно от столбца, используемого для того, чтобы разделить таблицу. Например, выполняя такой запрос, как EXPLAIN PARTITIONS SELECT COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id;, MySQL может быстро определить тот единственный раздел p2, который будет просмотрен, потому что остающиеся разделы не могут содержать отчеты, удовлетворяющие WHERE. См. раздел 20.4 для получения дополнительной информации о том, как это достигнуто. Разновидность на этом типе разделения RANGE COLUMNS. Разделение RANGE COLUMNS позволяет использовать много столбцов для того, чтобы определить диапазоны разделения, которые применяются к размещению строк в разделении и для того, чтобы определить включение или исключение определенного раздела, выполняя сокращение разделения. См. раздел 20.2.3.1. Разделение схем, основанных на временных интервалах. Если Вы хотите осуществить схему разделения, основанную на диапазонах или интервалах времени в MySQL 8.0, у Вас есть две опции: Разделите таблицу RANGE и для выражения разделения используйте функцию, воздействующую на столбец 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 8.0 также возможно разделить таблицу RANGE, основываясь на значении столбца TIMESTAMP , применив функцию UNIX_TIMESTAMP() как показано в этом примере: CREATE TABLE quarterly_report_status (report_id INT NOT NULL, report_status VARCHAR(20) NOT NULL, report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) PARTITION BY RANGE (UNIX_TIMESTAMP(report_updated)) (PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2008-01-01 00:00:00')), PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2008-04-01 00:00:00')), PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2008-07-01 00:00:00')), PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2008-10-01 00:00:00')), PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2009-01-01 00:00:00')), PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP('2009-04-01 00:00:00')), PARTITION p6 VALUES LESS THAN (UNIX_TIMESTAMP('2009-07-01 00:00:00')), PARTITION p7 VALUES LESS THAN (UNIX_TIMESTAMP('2009-10-01 00:00:00')), PARTITION p8 VALUES LESS THAN (UNIX_TIMESTAMP('2010-01-01 00:00:00')), PARTITION p9 VALUES LESS THAN (MAXVALUE)); В MySQL 8.0 любое другое вовлечение выражений TIMESTAMP недопустимо (Bug #42849). Также возможно в MySQL 8.0 использовать UNIX_TIMESTAMP(timestamp_column) как выражение разделения для таблиц, которые разделены LIST. Однако, это обычно непрактично. Разделите таблицу RANGE COLUMNS с использованием столбца DATE или DATETIME как столбца разделения. Например, таблица members могла быть определена, используя столбец joined непосредственно, как показано здесь: 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 COLUMNS(joined) (PARTITION p0 VALUES LESS THAN ('1960-01-01'), PARTITION p1 VALUES LESS THAN ('1970-01-01'), PARTITION p2 VALUES LESS THAN ('1980-01-01'), PARTITION p3 VALUES LESS THAN ('1990-01-01'), PARTITION p4 VALUES LESS THAN MAXVALUE); Использование разделения столбцов, использующих дату или время, кроме типов DATE или DATETIME не поддержано с RANGE COLUMNS. 20.2.2. LIST Разделение списка в MySQL подобно диапазону, делящему разными способами. Как в разделении RANGE, каждое разделение должно быть явно определено. Главное различие между двумя типами разделения то, что в разделении списка каждое разделение определено и выбрано основываясь на членстве значения столбца в одном из ряда списков значения, а не в одном из ряда непрерывных диапазонов значений. Это сделано при использовании PARTITION BY LIST(expr), где expr значение столбца или выражение, основанное на значении столбца и возвращении целочисленного значения, и затем определении каждого разделения посредством VALUES IN (value_list ), где value_list список разделенных запятой целых чисел. В MySQL 8.0 возможно соответствовать только списку целых чисел (и возможно NULL, см. раздел 20.2.7). Однако, другие типы столбца могут использоваться в списках значения, используя разделение LIST COLUMN, которое описано позже в этом разделе. В отличие от случая с разделением, определенным диапазоном, разделение списка не должно быть объявлено ни в каком особом порядке. Для более подробной синтаксической информации см. раздел 14.1.15. Для примеров, которые следуют далее, мы предполагаем, что основное определение таблицы, которая будет разделена, обеспечено 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.2.1. Как с другими примерами разделения, мы предполагаем, что default_storage_engine InnoDB. Предположите, что есть 20 видеомагазинов, распределенных среди 4 франшиз, как показано в следующей таблице. Регион ID хранилища North 3, 5, 6, 9, 17 East 1, 2, 10, 11, 19, 20 West 4, 12, 13, 14, 18 Central 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)); Это облегчает добавление или удаление записи, касающиеся определенных областей. Например, предположите, что все хранилища в Западном регионе проданы другой компании. В MySQL 8.0 все строки, касающиеся служащих, работающих в хранилищах в том регионе, могут быть удалены запросом ALTER TABLE employees TRUNCATE PARTITION pWest, который может быть выполнен намного более эффективно, чем эквивалент DELETE FROM employees WHERE store_id IN (4,12,13,14,18);. Использование ALTER TABLE employees DROP PARTITION pWest также удалит все эти строки, но также удалит и раздел pWest из определения таблицы: Вы должны были бы использовать ALTER TABLE ... ADD PARTITION, чтобы восстановить оригинальную схему разделения таблицы. Как с разделением RANGE, возможно объединить разделение LIST с разделением хешем или ключом, чтобы произвести сложное разделение (подразделение). См. раздел 20.2.6. В отличие от случая с RANGE, нет никакого MAXVALUE: все математические ожидания для выражения разделения должны быть покрыты в PARTITION ... VALUES IN (...). Запрос INSERT, содержащий неправильное значение столбца разделения, терпит неудачу с ошибкой, как показано в этом примере: mysql> CREATE TABLE h2 (c1 INT, c2 INT) -> PARTITION BY LIST(c1) -> (PARTITION p0 VALUES IN (1, 4, 7), -> PARTITION p1 VALUES IN (2, 5, 8)); Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO h2 VALUES (3, 5); ERROR 1525 (HY000): Table has no partition for value 3 Вставляя много строки, используя один запрос INSERT в одну таблицу, InnoDB, считает заявление единственной транзакцией так, чтобы присутствие любых неправильных значений заставило запрос терпеть неудачу полностью, и таким образом, никакие строки не вставлены. Вы можете заставить этот тип ошибки быть проигнорированным при использовании IGNORE. Если Вы делаете так, строки, содержащие неправильные значения столбцов разделения, не вставлены, но любые строки с соответствием значений вставлены, и ни о каких ошибках не сообщают: mysql> TRUNCATE h2; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM h2; Empty set (0.00 sec) mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9); Query OK, 3 rows affected (0.00 sec) Records: 5 Duplicates: 2 Warnings: 0 mysql> SELECT * FROM h2; +----+----+ | c1 | c2 | +----+----+ | 7 | 5 | | 1 | 9 | | 2 | 5 | +----+----+ 3 rows in set (0.00 sec) MySQL 8.0 также оказывает поддержку для LIST COLUMNS разновидности разделения LIST, которое позволяет Вам использовать столбцы любых типов, кроме типов целого числа для того, чтобы разделить столбцы, и использовать много столбцов в качестве разделения ключей. Для получения дополнительной информации см. раздел 20.2.3.2. 20.2.3. COLUMNS Следующие два раздела обсуждают разделение COLUMNS, которое является разновидностью RANGE и LIST. COLUMNS включает использование многих столбцов в разделении ключей. Все эти столбцы приняты во внимание с целью размещения строк в разделении и для определения, которое разделение должно быть проверено на соответствие строк в сокращении разделения. Кроме того, оба RANGE COLUMNS и LIST COLUMNS допускают использование столбцов нецелого числа для того, чтобы определить диапазоны значения или участников списка. Разрешенные типы данных показывают в следующем списке: Все типы целого числа: TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER) и BIGINT (это то же самое, как RANGE и LIST). Другие типы числовых данных (такие, как DECIMAL или FLOAT) не поддержаны как разделение столбцов. DATE и DATETIME. Столбцы, использующие другие типы данных, касающиеся даты или времени, не поддержаны как разделение столбцов. Следующие строковые типы: CHAR, VARCHAR, BINARY и VARBINARY. Столбцы TEXT и BLOB не поддержаны как разделение столбцов. Обсуждения RANGE COLUMNS и LIST COLUMNS в следующих двух разделах предполагает, что Вы уже знакомы с разделением, основанным на диапазонах и списках как поддержано в MySQL 5.1 и выше, для получения дополнительной информации о них см. разделы 20.2.1 и 20.2.2, соответственно. 20.2.3.1. RANGE COLUMNS Разделение столбцов диапазона подобно разделению диапазона, но позволяет Вам определить разделение, используя диапазоны, основанные на многих значениях столбцов. Кроме того, Вы можете определить столбцы использования диапазонов типов кроме типов целого числа. Разделение RANGE COLUMNS значительно отличается от RANGE в следующих моментах: RANGE COLUMNS не принимает выражения, только названия столбцов. RANGE COLUMNS принимает список из одного или более столбцов. RANGE COLUMNS основано на сравнениях между кортежами (списки значений столбцов), а не сравнениях между скалярными значениями. Размещение строк в RANGE COLUMNS также основано на сравнениях между кортежами, это обсуждается позже в этом разделе. Столбцы в RANGE COLUMNS не ограничены столбцами целого числа: строка, DATE и DATETIME могут также использоваться в качестве разделения столбцов. См. раздел 20.2.3. Основной синтаксис для того, чтобы составить таблицу, разделенную RANGE COLUMNS такой: CREATE TABLE table_name PARTITIONED BY RANGE COLUMNS(column_list) ( PARTITION partition_name VALUES LESS THAN (value_list)[, PARTITION partition_name VALUES LESS THAN (value_list)][, ...]) column_list: column_name[, column_name][, ...] value_list: value[, value][, ...] Не все опции CREATE TABLE, могут использоваться, когда создается таблица. Подробности в разделе 14.1.15. В синтаксисе column_list список из одного или более столбцов (иногда названный списком столбцов разделения), а value_list список значений (то есть, это список значений определения разделения). value_list должен поставляться для каждого определения разделения, и каждый value_list должен иметь то же самое число значений, как column_list имеет столбцов. Вообще говоря, если Вы используете N столбцов в предложении COLUMNS, тогда каждое предложение VALUES LESS THAN должно также поставляться со списком из N значений. Элементы в списке столбца разделения и в списке значения, определяющем каждое разделение, должны быть в том же самом порядке. Кроме того, каждый элемент в списке значения должен иметь тот же самый тип данных как соответствующий элемент в списке столбца. Однако, порядок имен столбцов в списке столбца разделения и списках значения не должен быть тем же самым, как порядок определений столбцов таблицы в основной части CREATE TABLE. Как с таблицей, разделенной RANGE, Вы можете использовать MAXVALUE таким образом, что любое допустимое значение, вставленное в данный столбец, всегда меньше, чем это значение. Вот пример CREATE TABLE, который помогает проиллюстрировать все эти тезисы: mysql> CREATE TABLE rcx (a INT, b INT, c CHAR(3), d INT) -> PARTITION BY RANGE COLUMNS(a,d,c) -> (PARTITION p0 VALUES LESS THAN (5,10,'ggg'), -> PARTITION p1 VALUES LESS THAN (10,20,'mmmm'), -> PARTITION p2 VALUES LESS THAN (15,30,'sss'), -> PARTITION p3 VALUES LESS THAN -> (MAXVALUE,MAXVALUE,MAXVALUE)); Query OK, 0 rows affected (0.15 sec) Таблица rcx содержит столбцы a, b, c и d. Список столбцов разделения, поставляемый предложением COLUMNS использует 3 из этих столбцов в порядке a, d, c. Каждый список значений, используемый, чтобы определить разделение, содержит 3 значения в том же самом порядке, то есть, у каждого кортежа списка значения есть форма (INT, INT, CHAR(3)), которая соответствует типам данных, используемым столбцами a, d и c (в этом порядке). Размещение строк в разделении определено, сравнивая кортеж от строки, которая будет вставлена, который соответствует списку столбцов в COLUMNS с кортежами, используемыми в VALUES LESS THAN , чтобы определить раздел таблицы. Поскольку мы сравниваем кортежи (то есть, списки или наборы значений), а не скалярные значения, семантика VALUES LESS THAN несколько отличается от случая с простым RANGE. В RANGE строка, производящая значение выражения, которое равно предельному значению в VALUES LESS THAN никогда не помещается в соответствующий раздел, однако, используя RANGE COLUMNS это иногда возможно. Рассмотрите таблицу RANGE, которую создали так: CREATE TABLE r1 (a INT, b INT) PARTITION BY RANGE (a) (PARTITION p0 VALUES LESS THAN (5), PARTITION p1 VALUES LESS THAN (MAXVALUE)); Если мы вставляем 3 строки в эту таблицу, таким образом, что значение столбца для a = 5 для каждой строки, все 3 строки сохранены в разделе p1, потому что значение столбца a в каждом случае не меньше 5, как мы можем видеть, выполняя надлежащий запрос INFORMATION_SCHEMA.PARTITIONS: mysql> INSERT INTO r1 VALUES (5,10), (5,11), (5,12); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT PARTITION_NAME,TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'r1'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 0 | | p1 | 3 | +----------------+------------+ 2 rows in set (0.00 sec) Теперь рассмотрите подобную таблицу rc1, которая использует разделение RANGE COLUMNS с обоими столбцами a и b в COLUMNS, создаваемыми как показано здесь: CREATE TABLE rc1 (a INT, b INT) PARTITION BY RANGE COLUMNS(a, b) (PARTITION p0 VALUES LESS THAN (5, 12), PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)); Если мы вставляем точно те же самые строки в rc1, мы только что вставили в r1, распределение строк очень отличается: mysql> INSERT INTO rc1 VALUES (5,10), (5,11), (5,12); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT PARTITION_NAME,TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'rc1'; +--------------+----------------+------------+ | TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS | +--------------+----------------+------------+ | p | p0 | 2 | | p | p1 | 1 | +--------------+----------------+------------+ 2 rows in set (0.00 sec) Это потому, что мы сравниваем строки, а не скалярные значения. Мы можем сравнить значения строки, вставленные с ограничивающим значением строки из VALUES THAN LESS THAN, используя для определения раздела p0 в таблице rc1, так: mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12); +-----------------+-----------------+-----------------+ | (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) | +-----------------+-----------------+-----------------+ | 1 | 1 | 0 | +-----------------+-----------------+-----------------+ 1 row in set (0.00 sec) Эти 2 кортежа (5,10) и (5,11) оцениваются как меньше (5,12), таким образом, они сохранены в разделе p0. А вот (5,12) не меньше (5,12) и сохранен в разделе p1. SELECT в предыдущем примере, возможно, также было написано, используя явные конструкторы строки: SELECT ROW(5,10) < ROW(5,12), ROW(5,11) < ROW(5,12), ROW(5,12) < ROW(5,12); Для таблицы, разделенной RANGE COLUMNS, используя только единственный столбец разделения, хранение строк в разделении то же самое, как в таблице, которая разделена RANGE. Следующий запрос CREATE TABLE составляет таблицу, разделенную RANGE COLUMNS с использованием 1 столбца разделения: CREATE TABLE rx (a INT, b INT) PARTITION BY RANGE COLUMNS (a) (PARTITION p0 VALUES LESS THAN (5), PARTITION p1 VALUES LESS THAN (MAXVALUE)); Если мы вставляем строки (5,10), (5,11) и (5,12) в эту таблицу, мы можем видеть, что их размещение то же самое, как для таблицы r, которую мы создали и заполнили ранее: mysql> INSERT INTO rx VALUES (5,10), (5,11), (5,12); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT PARTITION_NAME,TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'rx'; +--------------+----------------+------------+ | TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS | +--------------+----------------+------------+ | p | p0 | 0 | | p | p1 | 3 | +--------------+----------------+------------+ 2 rows in set (0.00 sec) Также возможно составить таблицы, разделенные RANGE COLUMNS, где предельные значения для одного или более столбцов повторены в последовательных определениях разделения. Вы можете сделать это, пока кортежи значений столбцов, используемых, чтобы определить разделение, строго увеличиваются. Например, каждый из следующих запросов CREATE TABLE допустим: CREATE TABLE rc2 (a INT, b INT) PARTITION BY RANGE COLUMNS(a,b) (PARTITION p0 VALUES LESS THAN (0,10), PARTITION p1 VALUES LESS THAN (10,20), PARTITION p2 VALUES LESS THAN (10,30), PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)); CREATE TABLE rc3 (a INT, b INT) PARTITION BY RANGE COLUMNS(a,b) (PARTITION p0 VALUES LESS THAN (0,10), PARTITION p1 VALUES LESS THAN (10,20), PARTITION p2 VALUES LESS THAN (10,30), PARTITION p3 VALUES LESS THAN (10,35), PARTITION p4 VALUES LESS THAN (20,40), PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)); Следующий запрос также преуспевает, даже при том, что могло бы показаться на первый взгляд, что это не будет работать, начиная с предельного значения столбца b 25 для раздела p0, 20 для p1, и предельного значения столбца c 100 для p1 (и 50 для p2): CREATE TABLE rc4 (a INT, b INT, c INT) PARTITION BY RANGE COLUMNS(a,b,c) (PARTITION p0 VALUES LESS THAN (0,25,50), PARTITION p1 VALUES LESS THAN (10,20,100), PARTITION p2 VALUES LESS THAN (10,30,50) PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)); Разрабатывая таблицы, разделенные RANGE COLUMNS, Вы можете всегда проверять последовательные определения разделения, сравнивая желаемые кортежи, используя клиент mysql: mysql> SELECT (0,25,50) < (10,20,100), (10,20,100) < (10,30,50); +-------------------------+--------------------------+ | (0,25,50) < (10,20,100) | (10,20,100) < (10,30,50) | +-------------------------+--------------------------+ | 1 | 1 | +-------------------------+--------------------------+ 1 row in set (0.00 sec) Если CREATE TABLE содержит определения разделения, которые не находятся в строго увеличивающемся порядке, он терпит неудачу с ошибкой, как показано в этом примере: mysql> CREATE TABLE rcf (a INT, b INT, c INT) -> PARTITION BY RANGE COLUMNS(a,b,c) -> (PARTITION p0 VALUES LESS THAN (0,25,50), -> PARTITION p1 VALUES LESS THAN (20,20,100), -> PARTITION p2 VALUES LESS THAN (10,30,50), -> PARTITION p3 VALUES LESS THAN -> (MAXVALUE, MAXVALUE, MAXVALUE)); ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition Когда Вы получаете такую ошибку, Вы можете вывести, какие определения разделения недопустимы, делая сравнение меньше чем между их списками столбцов. В этом случае проблема с определением раздела p2, потому что кортеж, использованный для его определения, не меньше, чем кортеж для определения p3, как показано здесь: mysql> SELECT (0,25,50) < (20,20,100), (20,20,100) < (10,30,50); +-------------------------+--------------------------+ | (0,25,50) < (20,20,100) | (20,20,100) < (10,30,50) | +-------------------------+--------------------------+ | 1 | 0 | +-------------------------+--------------------------+ 1 row in set (0.00 sec) Также возможно для MAXVALUE появиться для того же самого столбца больше, чем в одном VALUES LESS THAN при использовании RANGE COLUMNS. Однако, предельные значения для отдельных столбцов в последовательных определениях разделения должны иначе увеличиваться, должно быть не больше, чем одно разделение, где MAXVALUE используется в качестве верхнего предела для всех значений столбцов, и это определение разделения должно быть последним в списке PARTITION ... VALUES LESS THAN. Кроме того, Вы не можете использовать MAXVALUE как предельное значение для первого столбца больше чем в одном определении разделения. Как заявлено ранее, также возможно с RANGE COLUMNS использовать столбцы нецелого числа в качестве разделения столбцов. (См. раздел 20.2.3 для их полного списка). Предположим, что таблица employees (которая не разделена), создали с использованием следующего запроса: 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); Используя разделение RANGE COLUMNS, Вы можете создать версию этой таблицы, которая хранит каждую строку в одном из четырех разделов, основываясь на фамилии служащего: CREATE TABLE employees_by_lname (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 COLUMNS (lname) (PARTITION p0 VALUES LESS THAN ('g'), PARTITION p1 VALUES LESS THAN ('m'), PARTITION p2 VALUES LESS THAN ('t'), PARTITION p3 VALUES LESS THAN (MAXVALUE)); Альтернативно, Вы могли переделать employees, используя эту схему, выполняя следующий запрос ALTER TABLE : ALTER TABLE employees PARTITION BY RANGE COLUMNS (lname) (PARTITION p0 VALUES LESS THAN ('g'), PARTITION p1 VALUES LESS THAN ('m'), PARTITION p2 VALUES LESS THAN ('t'), PARTITION p3 VALUES LESS THAN (MAXVALUE)); Поскольку у различных наборов символов и сопоставлений есть различные порядки сортировки, наборы символов и сопоставления в использовании могут иметь влияние на распределение строк по таблице RANGE COLUMNS, используя строковые столбцы в качестве разделения столбцов. Кроме того, изменения набора символов или сопоставления для данной базы данных, таблицы или столбца после того, как такая таблица составлена, может вызвать изменения в том, как распределены строки. Например, используя чувствительное к регистру сопоставление 'and' сортируется ДО 'Andersen', но используя сопоставление, которое является нечувствительным к регистру, верно как раз обратное. Точно так же Вы можете сделать таблицу employees, которая будет разделена таким способом, которым каждая строка сохранена в одном из нескольких разделов, основываясь на десятилетии, в котором соответствующий служащий был нанят, используя запрос ALTER TABLE , как показано здесь: ALTER TABLE employees PARTITION BY RANGE COLUMNS (hired) (PARTITION p0 VALUES LESS THAN ('1970-01-01'), PARTITION p1 VALUES LESS THAN ('1980-01-01'), PARTITION p2 VALUES LESS THAN ('1990-01-01'), PARTITION p3 VALUES LESS THAN ('2000-01-01'), PARTITION p4 VALUES LESS THAN ('2010-01-01'), PARTITION p5 VALUES LESS THAN (MAXVALUE)); 20.2.3.2. LIST COLUMNS MySQL 8.0 оказывает поддержку для разделения LIST COLUMNS. Это разновидность разделения LIST, которое позволяет использование многих столбцов как ключей разделения, и использовать столбцы разных типов данных в качестве разделения столбцов: Вы можете использовать строковые типы, DATE и DATETIME. Для получения дополнительной информации о разрешенных типах данных для столбцов COLUMNS см. раздел 20.2.3. Предположите, что у Вас есть бизнес, у которого есть клиенты в 12 городах, которые, для продаж и маркетинговых целей, Вы организуете в 4 области по 3 города в каждой, как показано в следующей таблице: Область Города 1 Oskarshamn, Hц╤gsby, Mц╤nsterц╔s 2 Vimmerby, Hultsfred, Vц╓stervik 3 Nц╓ssjц╤, Eksjц╤, Vetlanda 4 Uppvidinge, Alvesta, Vц╓xjo С разделением LIST COLUMNS Вы можете составить таблицу для данных о клиентах, которая назначает строку на любой из 4 разделов, соответствующего этим областям, основываясь на названии города, где клиент находится, как показано здесь: CREATE TABLE customers_1 (first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), renewal DATE) PARTITION BY LIST COLUMNS(city) (PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Hц╤gsby', 'Mц╤nsterц╔s'), PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Vц╓stervik'), PARTITION pRegion_3 VALUES IN('Nц╓ssjц╤', 'Eksjц╤', 'Vetlanda'), PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Vц╓xjo')); Как с разделением RANGE COLUMNS, Вы не должны использовать выражения в COLUMNS(), чтобы преобразовать значения столбцов в целые числа. Фактически, использование выражений кроме имен столбцов не разрешается с COLUMNS(). Также возможно использовать столбцы DATE и DATETIME, как показано в следующем примере, который использует то же самое имя и столбцы как таблица customers_1, но использует разделение LIST COLUMNS, основанное на столбце renewal, чтобы сохранить строки в одном из 4 разделов в зависимости от недели в феврале 2010: CREATE TABLE customers_2 (first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), renewal DATE) PARTITION BY LIST COLUMNS(renewal) (PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03', '2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'), PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10', '2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'), PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17', '2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'), PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24', '2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')); Это работает, но становится тяжело определить и поддержать, если число вовлеченных дат становится очень большим. В таких случаях обычно более практично использовать RANGE или RANGE COLUMNS . В этом случае мы хотим использовать как ключ разделения столбец DATE и применяем разделение RANGE COLUMNS, как показано здесь: CREATE TABLE customers_3 (first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), renewal DATE) PARTITION BY RANGE COLUMNS(renewal) (PARTITION pWeek_1 VALUES LESS THAN('2010-02-09'), PARTITION pWeek_2 VALUES LESS THAN('2010-02-15'), PARTITION pWeek_3 VALUES LESS THAN('2010-02-22'), PARTITION pWeek_4 VALUES LESS THAN('2010-03-01')); Кроме того (как с RANGE COLUMNS), Вы можете использовать много столбцов в редложении COLUMNS(). 20.2.4. HASH Разделение HASH используется прежде всего, чтобы гарантировать распределение данных среди предопределенного числа разделов. С диапазоном или разделением списка, Вы должны определить явно, где данное значение столбца или набор значений столбцов должны быть сохранены. С разделением хеша это решение принимается сервером и Вы должны только определить значение столбца или выражение, основанное на значении столбца, которое будет хешировано, и число разделов, на которое должна быть поделена таблица. Чтобы разделить таблицу с применением HASH, надо передать CREATE TABLE предложение PARTITION BY HASH (expr), где expr выражение, которое возвращает целое число. Это может просто быть названием столбца, тип которого один из типов целого числа MySQL. Кроме того, Вы наиболее вероятно хотите управлять этим с PARTITIONS num, где num положительное целое число, представляющее число разделов, на которые должна быть разделена таблица. Для простоты таблицы в примерах, которые следуют ниже, не используют ключей. Вы должны знать, что, если у таблицы есть какие-либо уникальные ключи, каждый столбец, используемый в выражении разделения для этой таблицы, должен быть частью каждого уникального ключа, включая первичный ключ. См. раздел 20.6.1. Следующий запрос составляет таблицу, которая использует хеширование на столбце 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 должен возвратить непостоянное, неслучайное целочисленное значение (другими словами, это должно изменяться, но быть детерминировано) и не должен содержать запрещенные конструкции как описано в разделе 20.6. Вы должны также иметь в виду, что это выражение оценено каждый раз, когда строка вставлена или обновлена (или возможно удалена). Это означает, что очень сложные выражения могут дать начало исполнительным проблемам, особенно выполняя операции (такие, как пакет вставок), которые затрагивают очень много строк. Самая эффективная хеширующая функция та, которая работает на единственном столбце таблицы и чьи значения последовательно растут или уменьшаются со значением столбца, поскольку это учитывает уменьшение на диапазонах разделения. Таким образом, чем точнее выражение меняется в зависимости от значения столбца, на котором базируется, тем более эффективно MySQL может использовать это выражение для разделения. Например, где столбец 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 с 5 до 6 вызывает изменение -1 в значении выражения, но изменение значения int_col от 6 до 7 вызывает изменение уже в -7 в значении выражения. Другими словами, лучше, если график значения столбца как можно ближе к прямой линии y=cx, где c некоторая константа отличная от нуля. Это имеет отношение к факту что, чем более нелинейно выражение, тем более неравное распределение данных среди разделов оно производит. В теории сокращение также возможно для выражений, вовлекающих больше, чем одно значение столбца, но определение, какие из таких выражений являются подходящими, может быть довольно трудным и отнимающим много времени. Поэтому использование выражений хеширования, вовлекающих много столбцов, особенно не рекомендуется. Когда PARTITION BY HASH используется, механизм хранения определяет, который раздел 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, а значение col3 = '2005-09-15', раздел, в котором это сохранено, определен следующим образом: MOD(YEAR('2005-09-01'),4) = MOD(2005,4) = 1 MySQL 8.0 также поддерживает разновидность разделения HASH, известную как linear hashing, которая использует более сложный алгоритм для того, чтобы определить размещение новых строк, вставленных в разделенную таблицу. См. раздел 20.2.4.1. Пользовательская функция оценена каждый раз, когда запись вставлена или обновлена. Если у таблицы, которая будет разделена, есть ключ UNIQUE, тогда любые столбцы, поставляемые как параметры пользовательской функции HASH или KEY column_list должны быть частью этого ключа. 20.2.4.1. LINEAR HASH Линейное хеширование отличается от регулярного хеширования, в котором линейное хеширование использует линейный алгоритм, тогда как регулярное хеширование использует модуль значения хеширующей функции. Синтаксически, единственная разница между линейным разделением хеша и регулярным хешированием это добавление ключевого слова 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.7004397181413. CEILING(3.7004397181411) 4, а V = POWER(2,4) = 16. Пусть 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,6) )) = 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(8 / 2) = 6 & 3 = 2 2 >= 6 FALSE: запись сохранена в раздел #2 Преимущество в разделении линейным хешем состоит в том, что добавление, удаление и слияние разделов сделаны намного быстрее, что может быть выгодно, имея дело с таблицами, содержащими чрезвычайно большое количество (терабайты) данных. Недостаток: данные, вероятно, будут менее равномерно распределены между разделами по сравнению с распределением, полученным, используя регулярное разделение хеша. 20.2.5. KEY Разделение ключом подобно разделению хешем, за исключением того, что там, где разделение хеша использует определяемое пользователем выражение, хеширующая функция для ключевого разделения поставляется сервером MySQL. Эта внутренняя хеширующая функция основана на том же самом алгоритме, как PASSWORD(). Синтаксис для CREATE TABLE ... PARTITION BY KEY подобен тому, которым составляют таблицу, разделенную хешем. Существенные различия перечислены здесь: KEY используется вместо HASH. KEY берет только список из ноля или больше имен столбцов. Любые столбцы, используемые в качестве ключа разделения, должны включать часть или весь первичный ключ таблицы, если у таблицы он есть. Где никакое имя столбца не определено как ключ разделения, первичный ключ таблицы используется, если есть. Например, следующий запрос CREATE TABLE допустим в MySQL 8.0: 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 первичный ключ таблицы. Для разделенной ключом таблицы Вы не можете выполнить ALTER TABLE DROP PRIMARY KEY, поскольку выполнение этого производит ошибку ERROR 1466 (HY000): Field in list of fields for partition function not found in table. Также возможно разделить таблицу линейным ключом. Вот простой пример: CREATE TABLE tk (col1 INT NOT NULL, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY (col1) PARTITIONS 3; Ключевое слово LINEAR имеет тот же самый эффект на KEY, как на HASH с числом разделов, получаемым, используя алгоритм степеней двух, а не арифметики модуля. См. раздел 20.2.4.1. 20.2.6. Подразделение Подразделение также известно как композитное разделение и является дальнейшим подразделением каждого разделения в разделенной таблице. Рассмотрите следующий запрос 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 из них хранят только записи со значением столбца purchased меньше 1990. Возможно подразделить таблицы, которые разделены RANGE или LIST. Подразделение может использовать также HASH или KEY разделение. Это также известно как композитное разделение. SUBPARTITION BY HASH и SUBPARTITION BY KEY вообще следуют тем же самым правилам синтаксиса, как PARTITION BY HASH и PARTITION BY KEY, соответственно. Исключение: SUBPARTITION BY KEY (в отличие от PARTITION BY 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 должен включать (как минимум) название подраздела. Вы можете установить любую желаемую опцию для подраздела или позволить этому принимать свою настройку по умолчанию. Имена подразделения должны быть уникальными для всей таблицы. Например, следующий запрос CREATE TABLE допустим: 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)); 20.2.7. Как MySQL обрабатывает NULL в разделах Разделение в MySQL не делает ничего, чтобы отвергнуть NULL как значение выражения разделения, является ли это значением столбца или значением данного пользователем выражения. Даже при том, что разрешено использовать NULL как значение выражения, которое должно иначе привести к целому числу, важно иметь в виду, что NULL не число. NULL рассматривается как значение, меньше любого не-NULL, как в ORDER BY. Это означает, что обработка NULL изменяется между разделениями различных типов и может произвести поведение, которое Вы не ожидаете. Это рассмотрено позднее. Обработка NULL с RANGE. Если Вы вставляете строку в таблицу, разделенную RANGE таким образом, что значение столбца, используемое для определения раздела NULL, строка вставлена в самое низкое разделение. Считайте эти две таблицы в базе данных p, созданными следующим образом:: 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 t2 (c1 INT, c2 VARCHAR(20)) -> PARTITION BY RANGE(c1) -> (PARTITION p0 VALUES LESS THAN (-5), -> PARTITION p1 VALUES LESS THAN (0), -> PARTITION p2 VALUES LESS THAN (10), -> PARTITION p3 VALUES LESS THAN MAXVALUE); Query OK, 0 rows affected (0.09 sec) Вы можете видеть разделение, создаваемое этими двумя CREATE TABLE, используя следующий запрос к таблице PARTITIONS в INFORMATION_SCHEMA: mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, > DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS > WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_'; +------------+----------------+------------+----------------+-------------+ | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | +------------+----------------+------------+----------------+-------------+ | t1 | p0 | 0 | 0 | 0 | | t1 | p1 | 0 | 0 | 0 | | t1 | p2 | 0 | 0 | 0 | | t2 | p0 | 0 | 0 | 0 | | t2 | p1 | 0 | 0 | 0 | | t2 | p2 | 0 | 0 | 0 | | t2 | p3 | 0 | 0 | 0 | +------------+----------------+------------+----------------+-------------+ 7 rows in set (0.00 sec) Теперь заполним каждую из этих таблиц единственной строкой, содержащей NULL в столбце, используемом в качестве ключа разделения, и проверьте, что строки были вставлены, используя пару запросов SELECT: 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) Вы можете видеть, какой раздел используется, чтобы сохранить вставленные строки, запуская повторно предыдущий запрос INFORMATION_SCHEMA.PARTITIONS : mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, > DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS > WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_'; +------------+----------------+------------+----------------+-------------+ | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | +------------+----------------+------------+----------------+-------------+ | t1 | p0 | 1 | 20 | 20 | | t1 | p1 | 0 | 0 | 0 | | t1 | p2 | 0 | 0 | 0 | | t2 | p0 | 1 | 20 | 20 | | t2 | p1 | 0 | 0 | 0 | | t2 | p2 | 0 | 0 | 0 | | t2 | p3 | 0 | 0 | 0 | +------------+----------------+------------+----------------+-------------+ 7 rows in set (0.01 sec) Вы можете также продемонстрировать, что эти строки были сохранены в разделе с самым низким номером каждой таблицы, удаляя этот раздел, и затем запуская повторно 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) NULL также обработан таким образом для выражений разделения, которые применяют функции SQL. Предположите, что мы определяем таблицу, используя CREATE TABLE: 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. Строка со значением столбца dt NULL обработана, как если бы выражение разделения оценено к значению меньше, чем любое другое значение, и вставлена в раздел p0. Обработка NULL с LIST. Таблица, которая разделена 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) Запрашивая INFORMATION_SCHEMA.PARTITIONS, Вы можете определить, какое разделение использовалось, чтобы сохранить строки (мы принимаем, как в предыдущих примерах, что разделенные таблицы были составлены в базе данных p): mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, > DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS > WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 'ts_'; +------------+----------------+------------+----------------+-------------+ | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | +------------+----------------+------------+----------------+-------------+ | ts2 | p0 | 0 | 0 | 0 | | ts2 | p1 | 0 | 0 | 0 | | ts2 | p2 | 0 | 0 | 0 | | ts2 | p3 | 1 | 20 | 20 | | ts3 | p0 | 0 | 0 | 0 | | ts3 | p1 | 1 | 20 | 20 | | ts3 | p2 | 0 | 0 | 0 | +------------+----------------+------------+----------------+-------------+ 7 rows in set (0.01 sec) Как показано ранее в этом разделе, Вы можете также проверить, какой раздел использовался для того, чтобы сохранить строки, удаляя этот раздел и затем запуская SELECT. Обработка NULL с HASH и KEY. NULL обработан несколько по-другому для таблиц, разделенных HASH или KEY. В этих случаях любое выражение разделения, которое приводит к NULL обработано, как если бы его возвращаемое значение было нолем. Мы можем проверить это поведение, исследуя эффекты составления таблицы, разделенной HASH и заполнения ее записями, содержащими соответствующие значения. Предположите, что у Вас есть таблица th (тоже в базе данных p), созданная так: mysql> CREATE TABLE th (c1 INT, c2 VARCHAR(20)) -> PARTITION BY HASH(c1) PARTITIONS 2; Query OK, 0 rows affected (0.00 sec) Разделение, принадлежащее этой таблице, может быть рассмотрено, используя запрос, показанный здесь: mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH, > DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS > WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th'; +------------+----------------+------------+----------------+-------------+ | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | +------------+----------------+------------+----------------+-------------+ | th | p0 | 0 | 0 | 0 | | th | p1 | 0 | 0 | 0 | +------------+----------------+------------+----------------+-------------+ 2 rows in set (0.00 sec) Отметьте это TABLE_ROWS для каждого раздела 0. Теперь вставьте две строки в th чей столбец c1 имеет значения NULL и 0, а затем проверьте, что эти строки были вставлены, как показано здесь: mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM th; +------+---------+ | c1 | c2 | +------+---------+ | NULL | mothra | +------+---------+ |0 | gigan | +------+---------+ 2 rows in set (0.01 sec) Вспомните, что для любого целого числа N, значение NULL MODN всегда NULL. Для таблиц, которые разделены HASH или KEY, этот результат определяет правильный раздел как 0. Проверяя INFORMATION_SCHEMA.PARTITIONS мы можем видеть, что обе строки были вставлены в раздел p0: mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, > DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS > WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th'; +------------+----------------+------------+----------------+-------------+ | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | +------------+----------------+------------+----------------+-------------+ | th | p0 | 2 | 20 | 20 | | th | p1 | 0 | 0 | 0 | +------------+----------------+------------+----------------+-------------+ 2 rows in set (0.00 sec) Повторяя последнее использование в качестве примера PARTITION BY KEY вместо PARTITION BY HASH в определении таблицы Вы можете проверить, что NULL также обработан как 0 для этого типа разделения. 20.3. Управление разделением Есть много способов использовать запросы SQL, чтобы изменить разделенные таблицы: возможно добавить, удалить, пересмотреть, слить или разделить существующее разделение, используя расширения разделения для ALTER TABLE . Есть также способы получить информацию о разделенных таблицах и разделении. Мы обсуждаем эти темы в разделах далее. Для информации об управлении разделением в таблицах, разделенных RANGE или LIST см. раздел 20.3.1. Для информации об управлении разделением в таблицах, разделенных HASH и KEY см. раздел 20.3.2. См. раздел 20.3.5 для обсуждения механизмов, обеспеченных в MySQL 8.0 для того, чтобы получить информацию о разделенных таблицах и разделении. Для обсуждения операций обслуживания см. раздел 20.3.4. У всех разделов таблицы должно быть то же самое число подразделов: невозможно изменить подраздел, как только таблица была составлена. Чтобы изменить схему разделения таблицы, необходимо использовать только 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;. ALTER TABLE ... ENGINE = ... меняет только механизм хранения, используемый таблицей, и схема разделения таблицы остается прежней. Запрос преуспевает, только если целевой механизм хранения оказывает поддержку разделения. Вы можете использовать ALTER TABLE ... REMOVE PARTITIONING, чтобы удалить разделение таблицы, см. раздел 14.1.7. Только одно предложение PARTITION BY, ADD PARTITION, DROP PARTITION, REORGANIZE PARTITION или COALESCE PARTITION может использоваться в данном запросе ALTER TABLE . Если Вы (например) хотите удалить раздел и реорганизовать остающееся разделение таблицы, Вы должны сделать это за два отдельных запроса ALTER TABLE (первый использует DROP PARTITION, затем второй реализует REORGANIZE PARTITIONS). Вы можете удалить все строки из одного или более выбранного раздела с помощью ALTER TABLE ... TRUNCATE PARTITION. 20.3.1. Управление RANGE и LIST Диапазон и разделение списка подобны относительно того, как добавление и удаление раздела обработаны. По этой причине мы обсуждаем управление обоими видами разделения в этом разделе. Для информации о работе с таблицами, которые разделены хешем или ключом см. раздел 20.3.2. Удаление 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) Очень важно помнить, что когда Вы удаляете раздел, Вы также удаляете и все данные, которые хранились в этом разделе. Вы можете видеть, что дело обстоит именно так, запуская повторно предыдущий запрос SELECT: mysql> SELECT * FROM tr WHERE purchased -> BETWEEN '1995-01-01' AND '1999-12-31'; Empty set (0.00 sec) Из-за этого Вы должны иметь привилегию DROP для таблицы прежде, чем Вы сможете выполнить ALTER TABLE ... DROP PARTITION на этой таблице. Если Вы хотите исключить все данные из всего разделения, сохраняя табличное определение и его схему разделения, используйте TRUNCATE TABLE. Если Вы намереваетесь изменить разделение таблицы НЕ теряя данные, надо использовать ALTER TABLE ... REORGANIZE PARTITION. Больше информации о REORGANIZE PARTITION может быть найдено в другом месте в этом разделе или см. раздел 14.1.7.1. Если Вы теперь выполняете 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 использует точно тот же самый синтаксис ALTER TABLE ... DROP PARTITION как и для RANGE. Однако, есть одно важное различие в эффекте, который это имеет на Ваше использование таблицы позже: Вы больше не можете вставить в таблицу строки, имеющие любое из значений, которые были включены в список значения, определяющий удаленный раздел. См. раздел 20.2.2. Чтобы добавить новый диапазон или разделение списка к ранее разделенной таблице, используйте 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)); Предположите далее, что минимальный возраст для участников 16 лет. Поскольку календарь приближается к концу 2005, Вы понимаете, что будете скоро принимать членов, кто родился в 1990 (и позже в последующие годы). Вы можете изменить таблицу members, чтобы разместить новых участников, родившихся в годы с 1990 по 1999, как показано здесь: ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000)); С таблицами, которые разделены диапазоном, Вы можете использовать ADD PARTITION, чтобы добавить новый раздел только к верхнему концу списка разделов. Попытка добавить новый раздел в этой манере между или перед существующими результатами разделения приведет к ошибке: mysql> ALTER TABLE members ADD PARTITION (PARTITION n > VALUES LESS THAN (1960)); ERROR 1463 (HY000): VALUES LESS THAN value must be strictly б╩ increasing for each partition Вы можете обойти эту проблем, реорганизуя первый раздел на два новых, которые разделяют диапазон между ними: ALTER TABLE members REORGANIZE PARTITION p0 INTO (PARTITION n0 VALUES LESS THAN (1960), PARTITION n1 VALUES LESS THAN (1970)); Используя SHOW CREATE TABLE Вы можете видеть, что ALTER TABLE имел желаемый эффект: 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=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (YEAR(dob)) (PARTITION n0 VALUES LESS THAN (1960) ENGINE = InnoDB, PARTITION n1 VALUES LESS THAN (1970) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (1980) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (1990) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2000) ENGINE = InnoDB) */ 1 row in set (0.00 sec) Вы можете также использовать ALTER TABLE ... ADD PARTITION, чтобы добавить новый раздел к таблице, которая разделена LIST. Предположите таблицу tt, определенную, используя следующий запрос CREATE TABLE: 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 ... , затем удалить старую таблицу и переименовать новую, но это может быть отнимающим очень много времени, имея дело с большими объемами данных. Это также не может быть выполнимо в ситуациях, где высокая доступность является требованием. Вы можете добавить много разделов за один запрос 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); Возможно пересмотреть разделение, не теряя данные. Давайте смотреть сначала на пару простых примеров с RANGE. Вспомните таблицу 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). Комбинация разделения в списке 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; 20.3.2. Управление HASH и KEY Таблицы, которые разделены хешем или ключом, очень подобны друг другу относительно произведения изменений в установке разделения, и сильно отличаются от таблиц, которые были разделены диапазоном или списком. По этой причине, этот раздел обращается к модификации таблиц, разделенных хешем или ключом только. Для обсуждения добавления и удаления разделов таблиц, которые разделены диапазоном или списком, см. раздел 20.3.1. Вы не можете исключить разделение из таблиц, которые разделены HASH или KEY таким же образом, как для таблиц, которые разделены RANGE или LIST. Однако, Вы можете слить HASH или KEY, применив ALTER TABLE ... COALESCE PARTITION. Предположите, что таблица clients, содержащая данные о клиентах, разделена на 12 разделов, создаваемых как показано здесь: CREATE TABLE clients (id INT, fname VARCHAR(30), lname VARCHAR(30), signed DATE) PARTITION BY HASH(MONTH(signed)) PARTITIONS 12; Чтобы сократить количество разделов с 12 до 8, выполните следующий запрос 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; 20.3.3. Обмен разделами с таблицами В MySQL 8.0 возможно обменять табличное разделение или подразделение применив ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt, где pt разделенная таблица и p разделение или подразделение pt для обмена с неразделенной таблицей nt при условии, что следующие заявления истина: Таблица nt самостоятельно не разделена. Таблица nt не временная. Структура таблиц pt и nt идентична. Таблица nt не содержит ссылок внешнего ключа, и ни у какой другой таблицы нет внешних ключей, которые обращаются к nt. Нет никаких строк в nt, которые вне границ определения разделения для p. Это условие не применяется, если используется WITHOUT VALIDATION. В дополнение к привилегиям ALTER , INSERT и CREATE обычно требуемым для ALTER TABLE , Вы должны иметь DROP для выполнения ALTER TABLE ... EXCHANGE PARTITION. Вы должны также знать о следующих эффектах ALTER TABLE ... EXCHANGE PARTITION : Выполнение ALTER TABLE ... EXCHANGE PARTITION не вызывает триггеров в таблицах. Любые столбцы AUTO_INCREMENT в обмененной таблице сброшены. Ключевое слово IGNORE не имеет никакого эффекта, когда используется с ALTER TABLE ... EXCHANGE PARTITION. Синтаксис для ALTER TABLE ... EXCHANGE PARTITION показан здесь, где pt разделенная таблица, p разделение (или подразделение) на обмен, а nt неразделенная таблица, которая будет обменена с p: ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt; Произвольно, Вы можете приложить WITH VALIDATION или WITHOUT VALIDATION. Когда указано WITHOUT VALIDATION , ALTER TABLE ... EXCHANGE PARTITION не выполняет проверки допустимости строк, обменивая таблицы и разрешая администраторам базы данных принять на себя ответственность за то, что строки в пределах границ определения разделения. WITH VALIDATION значение по умолчанию. Одно и только одно разделение или подразделение может быть обменено с одной и только одной неразделенной таблицей в одном запросе ALTER TABLE EXCHANGE PARTITION. Чтобы обменять много разделений, используйте много запросов ALTER TABLE EXCHANGE PARTITION. EXCHANGE PARTITION невозможно объединить с другими опциями ALTER TABLE. Разделение и (если применимо) подразделение используемое разделенной таблицей может иметь какой угодно тип или типы, поддержанные в MySQL 8.0. Обмен разделения с неразделенной таблицей Предположите что разделенная таблица e была создана и заполнена с использованием следующих запросов SQL: CREATE TABLE e (id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30)) PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (50), PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (150), PARTITION p3 VALUES LESS THAN (MAXVALUE)); INSERT INTO e VALUES (1669, "Jim", "Smith"), (337, "Mary", "Jones"), (16, "Frank", "White"), (2005, "Linda", "Black"); Теперь мы создаем неразделенную копию e, назовем ее e2. Это может быть сделано, используя клиент mysql: mysql> CREATE TABLE e2 LIKE e; Query OK, 0 rows affected (1.34 sec) mysql> ALTER TABLE e2 REMOVE PARTITIONING; Query OK, 0 rows affected (0.90 sec) Records: 0 Duplicates: 0 Warnings: 0 Вы можете видеть, который раздел в e содержит строки, запрашивая INFORMATION_SCHEMA.PARTITIONS: mysql> SELECT PARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 1 | | p1 | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4 rows in set (0.00 sec) Для разделенных таблиц InnoDB количество строк, поданное столбцом TABLE_ROWS в INFORMATION_SCHEMA.PARTITIONS только ориентировочная величина, используемая в оптимизации SQL, и не всегда точная. Обменять раздел p0 в таблице e с таблицей e2 Вы можете через ALTER TABLE : mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2; Query OK, 0 rows affected (0.28 sec) Вы можете наблюдать, как прошел обмен, запрашивая таблицу INFORMATION_SCHEMA.PARTITIONS как прежде. Строка таблицы, которая была ранее найдена в разделе p0 больше не присутствует: mysql> SELECT PARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'e'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 0 | | p1 | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4 rows in set (0.00 sec) Если Вы запрашиваете таблицу e2, Вы можете видеть, что строка может быть теперь найдена там: mysql> SELECT * FROM e2; +----+-------+-------+ | id | fname | lname | +----+-------+-------+ | 16 | Frank | White | +----+-------+-------+ 1 row in set (0.00 sec) Таблица, которая будет обменена с разделением, должна не обязательно быть пустой. Чтобы продемонстрировать это, мы сначала вставляем новую строку в таблицу e, удостоверяясь, что эта строка сохранена в разделе e, выбирая значение столбца id, которое меньше 50, и проверяя это позже, запрашивая таблицу PARTITIONS: mysql> INSERT INTO e VALUES (41, "Michael", "Green"); Query OK, 1 row affected (0.05 sec) mysql> SELECT PARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'e'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 1 | | p1 | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4 rows in set (0.00 sec) Теперь мы еще раз обмениваем раздел p0 с таблицей e2 с использованием того же самого запроса ALTER TABLE как ранее: mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2; Query OK, 0 rows affected (0.28 sec) Вывод следующих запросов показывает, что строка таблицы, которая была сохранена в разделе p0 и строка таблицы, которая была сохранена в таблице e2, до ALTER TABLE , теперь поменялись местами: mysql> SELECT * FROM e; +------+-------+-------+ | id | fname | lname | +------+-------+-------+ | 16 | Frank | White | | 1669 | Jim | Smith | | 337 | Mary | Jones | | 2005 | Linda | Black | +------+-------+-------+ 4 rows in set (0.00 sec) mysql> SELECT PARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'e'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 1 | | p1 | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM e2; +----+---------+-------+ | id | fname | lname | +----+---------+-------+ | 41 | Michael | Green | +----+---------+-------+ 1 row in set (0.00 sec) Несоответствие строк Вы должны иметь в виду, что любые строки в неразделенной таблице до ALTER TABLE ... EXCHANGE PARTITION должны удовлетворять условиям, требуемым для их сохранения в целевом разделе, иначе, запрос терпит неудачу. Чтобы видеть, как это происходит, сначала вставьте строку в e2 вне границ определения для p0 таблицы e. Например, вставьте строку с значением столбца id, которое является слишком большим. Теперь попытайтесь обменять таблицу с разделением снова: mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald"); Query OK, 1 row affected (0.08 sec) mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2; ERROR 1707 (HY000): Found row that does not match the partition Ключевое слово IGNORE принято, но не имеет никакого эффекта, когда используется с EXCHANGE PARTITION, как показано здесь: mysql> ALTER IGNORE TABLE e EXCHANGE PARTITION p0 WITH TABLE e2; ERROR 1707 (HY000): Found row that does not match the partition Только опция WITHOUT VALIDATION разрешила бы этому запросу выполниться успешно: mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION; Query OK, 0 rows affected (0.02 sec) Когда разделение обменено с таблицей, которая содержит строки, которые не соответствуют определение разделения, обязанность администратора базы данных установить несоответствующие строки, которые могут быть выполнены, используя REPAIR TABLE или ALTER TABLE ... REPAIR PARTITION. Обмен разделения без проверки допустимости строки Чтобы избежать трудоёмкой проверки допустимости, обменивая разделение с таблицей, у которой есть много строк, возможно пропустить шаг проверки допустимости строки, указав опцию WITHOUT VALIDATION ALTER TABLE ... EXCHANGE PARTITION. Следующий пример сравнивает различие между временем выполнения, обменивая разделение с неразделенной таблицей с и без проверки допустимости. Разделенная таблица (e) содержит два раздела по 1 миллиону строк каждый. Строки в p0 таблицы e удалены, и p0 обменен с неразделенной таблицей в 1 миллион строк. Операция WITH VALIDATION занимает 0.74 секунды. А вот WITHOUT VALIDATION всего 0.01 секунды. # Create a partitioned table with 1 million rows in each partition CREATE TABLE e (id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30)) PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (1000001), PARTITION p1 VALUES LESS THAN (2000001)); mysql> SELECT COUNT(*) FROM e; +----------+ | COUNT(*) | +----------+ | 2000000 | +----------+ 1 row in set (0.27 sec) # View the rows in each partition SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e'; +----------------+-------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+-------------+ | p0 | 1000000 | | p1 | 1000000 | +----------------+-------------+ 2 rows in set (0.00 sec) # Create a nonpartitioned table of the same structure and populate it # with 1 million rows CREATE TABLE e2 (id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30)); mysql> SELECT COUNT(*) FROM e2; +----------+ | COUNT(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.24 sec) # Create another nonpartitioned table of the same structure and populate # it with 1 million rows CREATE TABLE e3 (id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30)); mysql> SELECT COUNT(*) FROM e3; +----------+ | COUNT(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.25 sec) # Drop the rows from p0 of table e mysql> DELETE FROM e WHERE id < 1000001; Query OK, 1000000 rows affected (5.55 sec) # Confirm that there are no rows in partition p0 mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 0 | | p1 | 1000000 | +----------------+------------+ 2 rows in set (0.00 sec) # Exchange partition p0 of table e with the table e2 'WITH VALIDATION' mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITH VALIDATION; Query OK, 0 rows affected (0.74 sec) # Confirm that the partition was exchanged with table e2 mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 1000000 | | p1 | 1000000 | +----------------+------------+ 2 rows in set (0.00 sec) # Once again, drop the rows from p0 of table e mysql> DELETE FROM e WHERE id < 1000001; Query OK, 1000000 rows affected (5.55 sec) # Confirm that there are no rows in partition p0 mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 0 | | p1 | 1000000 | +----------------+------------+ 2 rows in set (0.00 sec) # Exchange partition p0 of table e with the table e3 'WITHOUT VALIDATION' mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e3 WITHOUT VALIDATION; Query OK, 0 rows affected (0.01 sec) # Confirm that the partition was exchanged with table e3 mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 1000000 | | p1 | 1000000 | +----------------+------------+ 2 rows in set (0.00 sec) Если раздел обменен с таблицей, которая содержит строки, которые не соответствуют определению разделения, обязанность администратора базы данных установить несоответствующие строки, используя REPAIR TABLE или ALTER TABLE ... REPAIR PARTITION. Обмен подразделения с неразделенной таблицей Вы можете также обменять подразделение таблицы (см. раздел 20.2.6) с неразделенной таблицей, используя ALTER TABLE ... EXCHANGE PARTITION . В следующем примере мы сначала составляем таблицу es, разделенную RANGE и подразделенную KEY, заполним эту таблицу, как в случае с таблицей e, а затем создадим пустую, неразделенную копию es2 таблицы, как показано здесь: mysql> CREATE TABLE es (id INT NOT NULL, fname VARCHAR(30), -> lname VARCHAR(30)) -> PARTITION BY RANGE (id) -> SUBPARTITION BY KEY (lname) SUBPARTITIONS 2 -> (PARTITION p0 VALUES LESS THAN (50), -> PARTITION p1 VALUES LESS THAN (100), -> PARTITION p2 VALUES LESS THAN (150), -> PARTITION p3 VALUES LESS THAN (MAXVALUE)); Query OK, 0 rows affected (2.76 sec) mysql> INSERT INTO es VALUES -> (1669, "Jim", "Smith"), -> (337, "Mary", "Jones"), -> (16, "Frank", "White"), -> (2005, "Linda", "Black"); Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE es2 LIKE es; Query OK, 0 rows affected (1.27 sec) mysql> ALTER TABLE es2 REMOVE PARTITIONING; Query OK, 0 rows affected (0.70 sec) Records: 0 Duplicates: 0 Warnings: 0 Хотя мы явно не называли ни одного подразделения, составляя таблицу es, мы можем получить произведенные названия включением столбца SUBPARTITION_NAME таблицы PARTITIONS из INFORMATION_SCHEMA, как показано здесь: mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'es'; +----------------+-------------------+------------+ | PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS | +----------------+-------------------+------------+ | p0 | p0sp0 | 1 | | p0 | p0sp1 | 0 | | p1 | p1sp0 | 0 | | p1 | p1sp1 | 0 | | p2 | p2sp0 | 0 | | p2 | p2sp1 | 0 | | p3 | p3sp0 | 3 | | p3 | p3sp1 | 0 | +----------------+-------------------+------------+ 8 rows in set (0.00 sec) Следующий запрос ALTER TABLE обменивает подраздел p3sp0 в таблице es с неразделенной таблицей es2: mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2; Query OK, 0 rows affected (0.29 sec) Вы можете проверить, что строки были обменены, выпуская следующие запросы: mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'es'; +----------------+-------------------+------------+ | PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS | +----------------+-------------------+------------+ | p0 | p0sp0 | 1 | | p0 | p0sp1 | 0 | | p1 | p1sp0 | 0 | | p1 | p1sp1 | 0 | | p2 | p2sp0 | 0 | | p2 | p2sp1 | 0 | | p3 | p3sp0 | 0 | | p3 | p3sp1 | 0 | +----------------+-------------------+------------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM es2; +------+-------+-------+ | id | fname | lname | +------+-------+-------+ | 1669 | Jim | Smith | | 337 | Mary | Jones | | 2005 | Linda | Black | +------+-------+-------+ 3 rows in set (0.00 sec) Если таблица подразделена, Вы можете обменять только подраздел с неразделенной таблицей, как показано здесь: mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2; ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition Структуры таблиц сравнены строгим способом; число, порядок, имена и типы столбцов и индексов разделенной таблицы и неразделенная таблица должны соответствовать точно. Кроме того, обе таблицы должны использовать тот же самый механизм хранения: mysql> CREATE TABLE es3 LIKE e; Query OK, 0 rows affected (1.31 sec) mysql> ALTER TABLE es3 REMOVE PARTITIONING; Query OK, 0 rows affected (0.53 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE es3\G *************************** 1. row *************************** Table: es3 Create Table: CREATE TABLE `es3` ( `id` int(11) NOT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> ALTER TABLE es3 ENGINE = MyISAM; Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3; ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL 20.3.4. Обслуживание разделения Много таблиц и задач обслуживания разделения могут быть выполнены на разделенных таблицах, используя запросы SQL, предназначенные для таких целей. Табличное обслуживание разделенных таблиц может быть достигнуто, используя using the statements CHECK TABLE , OPTIMIZE TABLE, ANALYZE TABLE и REPAIR TABLE, которые поддержаны для разделенных таблиц. Вы можете использовать много расширений для ALTER TABLE для того, чтобы выполнить операции этого типа на одном или более разделении непосредственно, как описано в следующем списке: Восстановление разделения. Восстанавливает разделение, это имеет тот же самый эффект как удаление всех записей, сохраненных в разделении, затем повторно вставляя их. Это может быть полезно в целях дефрагментации. Пример: ALTER TABLE t1 REBUILD PARTITION p0, p1; Оптимизация разделения. Если Вы удалили большое количество строк из раздела или произвели много изменений в разделенной таблице со строками переменной длины (то есть, имея столбцы VARCHAR, BLOB, или TEXT), Вы можете использовать ALTER TABLE ... OPTIMIZE PARTITION, чтобы восстановить любое неиспользуемое место и дефрагментировать файл с данными. Пример: ALTER TABLE t1 OPTIMIZE PARTITION p0, p1; Использование OPTIMIZE PARTITION на данном разделе эквивалентно выполнению CHECK PARTITION, ANALYZE PARTITION и REPAIR PARTITION на этом разделе. Некоторые механизмы хранения MySQL, включая InnoDB, не поддерживают оптимизацию разделов, в этих случаях ALTER TABLE ... OPTIMIZE PARTITION анализирует и восстанавливает всю таблицу и выставляет соответствующее предупреждение (Bug #11751825, Bug #42822). Используйте ALTER TABLE ... REBUILD PARTITION и ALTER TABLE ... ANALYZE PARTITION, чтобы избежать этой проблемы. Анализ разделения. Это читает и сохраняет ключевые распределения для раздела. Пример: ALTER TABLE t1 ANALYZE PARTITION p3; Восстановление разделения. Это восстанавливает поврежденное разделение. Пример: ALTER TABLE t1 REPAIR PARTITION p0,p1; Обычно REPAIR PARTITION терпит неудачу, когда разделение содержит ошибки дублирования ключа. Вы можете использовать ALTER IGNORE TABLE с этой опцией, когда все строки, которые не могут быть перемещены из-за дубликатов ключа, удалены из разделения (Bug #16900947). Проверка разделения. Вы можете проверить разделение на ошибки почти таким же способом, которым Вы можете использовать CHECK TABLE с неразделенными таблицами. Пример: ALTER TABLE trb3 CHECK PARTITION p1; Эта команда скажет Вам, повреждены ли данные или индексы в разделе p1 таблицы t1. Если это верно, надо использовать ALTER TABLE ... REPAIR PARTITION для ремонта раздела. Обычно CHECK PARTITION терпит неудачу, когда разделение содержит ошибки дублирования ключа. Вы можете использовать ALTER IGNORE TABLE с этой опцией, когда запрос возвращает содержание каждой строки в разделении, где найдено нарушение дублирования ключа. Отметьте, что сообщается только о значениях для столбцов в выражении разделения таблицы (Bug #16900947). Каждый из запрсов в списке также поддерживает ключевое слово ALL вместо списка имен разделов. Использование ALL заставляет запрос действовать на все разделение в таблице. Вы можете также усечь раздел с помощью ALTER TABLE ... TRUNCATE PARTITION . Этот запрос может использоваться, чтобы удалить все строки из одного или более разделов почти таким же способом, которым TRUNCATE TABLE удаляет все строки из таблицы. ALTER TABLE ... TRUNCATE PARTITION ALL усекает все разделение в таблице. 20.3.5. Получение информации о разделении Этот раздел обсуждает получение информации о существующем разделении, которое может быть сделано многими способами. Методы получения такой информации включают следующее: Использование 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) Вывод SHOW TABLE STATUS для разделенных таблиц тот же самый, как для неразделенных таблиц, за исключением того, что столбец Create_options содержит строку partitioned. Столбец Engine содержит название механизма хранения, используемого всем разделением таблицы. См. раздел 14.7.5.36. Вы можете также получить информацию о разделении от INFORMATION_SCHEMA, которая содержит таблицу PARTITIONS. См. раздел 22.14. Возможно определить, какой раздел разделенной таблицы вовлечен в данный запрос 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. Столбец rows вывода EXPLAIN PARTITIONS выводит на экран общее количество строк в таблице. 20.4. Сокращение разделения Оптимизация, известная как сокращение разделения, основана на относительно простом понятии, которое может быть описано как не просматривать раздел, где не может быть никакого соответствия . Предположите разделенную таблицу 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: SELECT fname, lname, region_code, dob FROM t1 WHERE region_code > 125 AND region_code < 130; Легко видеть, что ни одна из строк, которые должны быть возвращены, не находится ни в одном из разделов p0 или p3, то есть, мы должны искать только в разделах p1 и p2 . Ограничивая поиск, возможно израсходовать намного меньше времени и усилий в обнаружении соответствия строк, чем просматривая все разделение в таблице. Этот путь известен как сокращение. Когда оптимизатор может использовать сокращение в выполнении этого запроса, выполнение запроса может быть на порядок быстрее, чем тот же самый запрос на неразделенной таблице, содержащей те же самые определения столбца и данные. Оптимизатор может выполнить сокращение всякий раз, когда выражение WHERE может быть уменьшено до любого из следующих двух случаев: partition_column = constant partition_column IN (constant1, constant2, ..., constantN) В первом случае оптимизатор просто оценивает выражение разделения для данного значения, определяет, какой раздел содержит то значение, и просматривает только этот раздел. Во многих случаях знак "равно" может быть заменен другим арифметическим сравнением, включая <, >, <=, >= и <>. Некоторое запросы BETWEEN в WHERE могут также использовать в своих интересах сокращение разделения. Во втором случае оптимизатор оценивает выражение разделения для каждого значения в списке, создает список соответствия разделения, и затем просматривает только раздел в этом списке. SELECT, DELETE и UPDATE поддерживают сокращение разделения. INSERT сейчас так не умеет. Сокращение может также быть применено к малым диапазонам, которые оптимизатор может преобразовать в эквивалентные списки значений. Например, в предыдущем примере WHERE может быть преобразован в WHERE region_code IN (126, 127, 128, 129). Тогда оптимизатор может решить, что первые два значения в списке найдены в разделе p1, остальные два значения в разделе p2, а другие разделы не содержат соответствующих значений. Оптимизатор может также выполнить сокращение для условия WHERE, которое вовлекает сравнения предыдущих типов на многих столбцах для таблиц RANGE COLUMNS или LIST COLUMNS. Этот тип оптимизации может быть применен всякий раз, когда выражение разделения состоит из равенства или диапазона, который может быть уменьшен до ряда равенств, или когда выражение разделения представляет увеличение или уменьшение отношений. Сокращение может также быть применено для таблиц, разделенных на столбцах DATE или DATETIME, когда выражение разделения использует функцию YEAR() или TO_DAYS(). Сокращение может также быть применено для таких таблиц, когда выражение разделения использует функцию TO_SECONDS(). Предположите таблицу 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'; UPDATE t2 SET region_code = 8 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25'; DELETE 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. Остающиеся разделы могут быть безопасно проигнорированы. Недопустимые значения DATE и DATETIME, на которые ссылаются в условии WHERE, обработаны как NULL. Это означает что такой запрос, как SELECT * FROM partitioned_table WHERE date_column < '2008-12-00' не возвращает значений (см. Bug #40972). До сих пор мы только смотрели на использование в качестве примера разделения 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 или [LINEAR] 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; Может быть сокращен запрос, который сравнивает значение столбца с константой: UPDATE 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). Эта оптимизация используется, только если размер диапазона меньше, чем число разделов. Рассмотрите этот запрос: DELETE FROM t4 WHERE region_code BETWEEN 4 AND 12; Диапазон в WHERE касается 9 значений (4, 5, 6, 7, 8, 9, 10, 11, 12), но t4 имеет только 8 разделов. Это означает, что DELETE не может быть сокращен. Когда таблица разделена HASH или [LINEAR] KEY, сокращение может использоваться только на столбцах целого числа. Например, этот запрос не может использовать сокращение, потому что dob столбец типа DATE: SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15'; Однако, если таблица хранит значения года в столбце INT, то запрос, имеющий WHERE year_col >= 2001 AND year_col <=2005 может быть сокращен. Таблицы, использующие механизм хранения, который обеспечивает автоматическое разделение, такой как NDB, используемый MySQL Cluster (не поддерживается сейчас в MySQL 8.0), могут быть сокращены, если они явно разделены. 20.5. Выбор разделения Явный выбор разделения и подразделения для строк, соответствующих данному WHERE поддержан. Выбор разделения подобен сокращению разделения, но отличается по двум ключевым отношениям: Разделение, которое будет проверено, определено запросом, в отличие от сокращения разделения, которое является автоматическим. Принимая во внимание, что сокращение разделения применяется только к запросам, явный выбор разделения поддержан для многих запросов и DML. Запросы SQL, поддерживающие явный выбор разделения, перечислены здесь: SELECT DELETE INSERT REPLACE UPDATE LOAD DATA. LOAD XML. Явный выбор разделения осуществлен, используя опцию PARTITION. Для всех поддержанных запросов эта опция использует синтаксис, показанный здесь: PARTITION (partition_names) partition_names: partition_name, ... Эта опция всегда следует за названием таблицы, которой принадлежит разделение. partition_names это список разделенных запятой значений разделения или подразделения, которое будет использоваться. Каждое имя в этом списке должно быть названием существующего разделения или подразделения указанной таблицы, если какое-либо разделение или подразделение не найдено, запрос терпит неудачу с ошибкой (partition 'partition_name' doesn't exist). Разделение и подразделение, названное в partition_names могут быть перечислены в любом порядке и могут наложиться. Когда опция PARTITION используется, только перечисленные разделение и подразделение проверены на соответствие строк. Эта опция может использоваться в SELECT, чтобы определить, какие строки принадлежат данному разделению. Считайте разделенную таблицу, названную employees, создаваемой и заполняемой с использованием показанных запросов: SET @@SQL_MODE = ''; CREATE TABLE employees (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(25) NOT NULL, lname VARCHAR(25) NOT NULL, store_id INT NOT NULL, department_id INT NOT NULL) PARTITION BY RANGE(id) (PARTITION p0 VALUES LESS THAN (5), PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (15), PARTITION p3 VALUES LESS THAN MAXVALUE); INSERT INTO employees VALUES ('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2), ('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4), ('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3), ('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1), ('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4), ('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2), ('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3), ('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2), ('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2); Вы можете видеть, какие строки сохранены в разделе p1: mysql> SELECT * FROM employees PARTITION (p1); +----+-------+--------+----------+---------------+ | id | fname | lname | store_id | department_id | +----+-------+--------+----------+---------------+ | 5 | Mary | Jones | 1 | 1 | | 6 | Linda | Black | 2 | 3 | | 7 | Ed | Jones | 2 | 1 | | 8 | June | Wilson | 3 | 1 | | 9 | Andy | Smith | 1 | 3 | +----+-------+--------+----------+---------------+ 5 rows in set (0.00 sec) Результат тот же самый, какой получен запросом SELECT * FROM employees WHERE id BETWEEN 5 AND 9. Чтобы получить строки из многих разделов, укажите их имена как разграниченный запятой список. Например, SELECT * FROM employees PARTITION (p1, p2) вернет все строки из разделов p1 и p2 в то же время исключая строки от остающегося раздела. Любой допустимый запрос может быть переписан с опцией PARTITION, чтобы ограничить результат одним или более желаемым разделом. Вы можете использовать условия WHERE, ORDER BY и опцию LIMIT. Вы можете также использовать совокупные функции с опциями HAVING и GROUP BY. Каждый из следующих запросов приводит к допустимому результату, когда работает с таблицей employees, определенной ранее: mysql> SELECT * FROM employees PARTITION (p0, p2) WHERE lname LIKE 'S%'; +----+-------+-------+----------+---------------+ | id | fname | lname | store_id | department_id | +----+-------+-------+----------+---------------+ | 4 | Jim | Smith | 2 | 4 | | 11 | Jill | Stone | 1 | 4 | +----+-------+-------+----------+---------------+ 2 rows in set (0.00 sec) mysql> SELECT id, CONCAT(fname, ' ', lname) AS name -> FROM employees PARTITION (p0) ORDER BY lname; +----+----------------+ | id | name | +----+----------------+ | 3 | Ellen Johnson | | 4 | Jim Smith | | 1 | Bob Taylor | | 2 | Frank Williams | +----+----------------+ 4 rows in set (0.06 sec) mysql> SELECT store_id, COUNT(department_id) AS c -> FROM employees PARTITION (p1,p2,p3) -> GROUP BY store_id HAVING c > 4; +---+----------+ | c | store_id | +---+----------+ | 5 | 2 | | 5 | 3 | +---+----------+ 2 rows in set (0.00 sec) Запросы используя выбор разделения могут использоваться с таблицами, используя любой из поддержанных типов разделения. Когда таблица составлена, используя [LINEAR] HASH или [LINEAR] KEY и названия разделов не определены, MySQL автоматически называет разделы p0, p1, p2, ..., p N-1, где N число разделов. Для подразделения не названного явно MySQL назначает автоматически подразделам в каждом разделе pX имена pXsp0, pXsp1, pXsp2, ..., pXspM-1, где M количество подразделов. Выполняя для этой таблицы SELECT (или другой запрос SQL, для которого позволен явный выбор разделения), Вы можете использовать эти произведенные имена в опции PARTITION, как показано здесь: mysql> CREATE TABLE employees_sub (id INT NOT NULL AUTO_INCREMENT, -> fname VARCHAR(25) NOT NULL, -> lname VARCHAR(25) NOT NULL, -> store_id INT NOT NULL, -> department_id INT NOT NULL, -> PRIMARY KEY pk (id, lname)) -> PARTITION BY RANGE(id) SUBPARTITION BY KEY (lname) -> SUBPARTITIONS 2 (PARTITION p0 VALUES LESS THAN (5), -> PARTITION p1 VALUES LESS THAN (10), -> PARTITION p2 VALUES LESS THAN (15), -> PARTITION p3 VALUES LESS THAN MAXVALUE); Query OK, 0 rows affected (1.14 sec) mysql> INSERT INTO employees_sub # reuse data in employees table -> SELECT * FROM employees; Query OK, 18 rows affected (0.09 sec) Records: 18 Duplicates: 0 Warnings: 0 mysql> SELECT id, CONCAT(fname, ' ', lname) AS name -> FROM employees_sub PARTITION (p2sp1); +----+---------------+ | id | name | +----+---------------+ | 10 | Lou Waters | | 14 | Fred Goldberg | +----+---------------+ 2 rows in set (0.00 sec) Вы можете также использовать опцию PARTITION в части SELECT запроса INSERT ... SELECT: mysql> CREATE TABLE employees_copy LIKE employees; Query OK, 0 rows affected (0.28 sec) mysql> INSERT INTO employees_copy -> SELECT * FROM employees PARTITION (p2); Query OK, 5 rows affected (0.04 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM employees_copy; +----+--------+----------+----------+---------------+ | id | fname | lname | store_id | department_id | +----+--------+----------+----------+---------------+ | 10 | Lou | Waters | 2 | 4 | | 11 | Jill | Stone | 1 | 4 | | 12 | Roger | White | 3 | 2 | | 13 | Howard | Andrews | 1 | 2 | | 14 | Fred | Goldberg | 3 | 3 | +----+--------+----------+----------+---------------+ 5 rows in set (0.00 sec) Выбор разделения может также использоваться с соединениями. Предположите, что мы создаем и заполняем две таблицы, используя запросы, показанные здесь: CREATE TABLE stores (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, city VARCHAR(30) NOT NULL) PARTITION BY HASH(id) PARTITIONS 2; INSERT INTO stores VALUES ('', 'Nambucca'), ('', 'Uranga'), ('', 'Bellingen'), ('', 'Grafton'); CREATE TABLE departments (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL) PARTITION BY KEY(id) PARTITIONS 2; INSERT INTO departments VALUES ('', 'Sales'), ('', 'Customer Service'), ('', 'Delivery'), ('', 'Accounting'); Вы можете явно выбрать раздел (подраздел или то и другое сразу) из любых таблиц в соединении. Отметьте, что опция PARTITION, которая выбирает раздел из данной таблицы, немедленно следует за названием таблицы, перед всеми другими опциями, включая любой табличный псевдоним. Например, следующий запрос получает значения name, employee ID, department и city для всех служащих, которые работают в продажах или отделе поставки (раздел p1 таблицы departments) в хранилищах в любом из городов Nambucca и Bellingen (а это уже раздел p0 таблицы stores): mysql> SELECT e.id AS 'Employee ID', -> CONCAT(e.fname, ' ', e.lname) AS Name, -> s.city AS City, d.name AS department FROM employees AS e -> JOIN stores PARTITION (p1) AS s ON e.store_id=s.id -> JOIN departments PARTITION (p0) AS d ON e.department_id=d.id -> ORDER BY e.lname; +-------------+---------------+-----------+------------+ | Employee ID | Name | City | department | +-------------+---------------+-----------+------------+ | 14 | Fred Goldberg | Bellingen | Delivery | | 5 | Mary Jones | Nambucca | Sales | | 17 | Mark Morgan | Bellingen | Delivery | | 9 | Andy Smith | Nambucca | Delivery | | 8 | June Wilson | Bellingen | Sales | +-------------+---------------+-----------+------------+ 5 rows in set (0.00 sec) Когда опция PARTITION используется с DELETE, только тот раздел (и подраздел, если есть) перечисленный с опцией проверены на строки, которые будут удалены. Любые другие разделы проигнорированы, как показано здесь: mysql> SELECT * FROM employees WHERE fname LIKE 'j%'; +----+-------+--------+----------+---------------+ | id | fname | lname | store_id | department_id | +----+-------+--------+----------+---------------+ | 4 | Jim | Smith | 2 | 4 | | 8 | June | Wilson | 3 | 1 | | 11 | Jill | Stone | 1 | 4 | +----+-------+--------+----------+---------------+ 3 rows in set (0.00 sec) mysql> DELETE FROM employees PARTITION (p0, p1) -> WHERE fname LIKE 'j%'; Query OK, 2 rows affected (0.09 sec) mysql> SELECT * FROM employees WHERE fname LIKE 'j%'; +----+-------+-------+----------+---------------+ | id | fname | lname | store_id | department_id | +----+-------+-------+----------+---------------+ | 11 | Jill | Stone | 1 | 4 | +----+-------+-------+----------+---------------+ 1 row in set (0.00 sec) Только эти две строки в разделах p0 и p1, соответствующие условию WHERE были удалены. Как Вы можете видеть из результата, когда выполянется SELECT во второй раз, остается строка в таблице, соответствующая WHERE, но находящяяся в ином разделе (p2). UPDATE, используя явный выбор раздела ведут себя таким же образом: только строки в разделе, на который ссылается опция PARTITION рассматриваются, определяя строки, которые будут обновлены, как может быть замечено, выполняя следующие запросы: mysql> UPDATE employees PARTITION (p0) -> SET store_id = 2 WHERE fname = 'Jill'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> SELECT * FROM employees WHERE fname = 'Jill'; +----+-------+-------+----------+---------------+ | id | fname | lname | store_id | department_id | +----+-------+-------+----------+---------------+ | 11 | Jill | Stone | 1 | 4 | +----+-------+-------+----------+---------------+ 1 row in set (0.00 sec) mysql> UPDATE employees PARTITION (p2) -> SET store_id = 2 WHERE fname = 'Jill'; Query OK, 1 row affected (0.09 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM employees WHERE fname = 'Jill'; +----+-------+-------+----------+---------------+ | id | fname | lname | store_id | department_id | +----+-------+-------+----------+---------------+ | 11 | Jill | Stone | 2 | 4 | +----+-------+-------+----------+---------------+ 1 row in set (0.00 sec) Таким же образом, когда PARTITION используется с DELETE, только строки в разделе, названном в списке разделов, проверены на удаление. Для запросов, которые вставляют строки, поведение отличается в том, что если подходящий раздел не найден, весь запрос отваливается. Это истина для обоих запросов INSERT и REPLACE, как показано здесь: mysql> INSERT INTO employees PARTITION (p2) VALUES (20, 'Jan', 'Jones', 1, 3); ERROR 1729 (HY000): Found a row not matching the given partition set mysql> INSERT INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 1, 3); Query OK, 1 row affected (0.07 sec) mysql> REPLACE INTO employees PARTITION (p0) VALUES (20, 'Jan', 'Jones', 3, 2); ERROR 1729 (HY000): Found a row not matching the given partition set mysql> REPLACE INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 3, 2); Query OK, 2 rows affected (0.09 sec) Для запросов, которые пишут много строк в разделенную таблицу InnoDB: если любая строка в списке после VALUES не может быть записана в один из разделов, определенных в списке partition_names, запрос терпит неудачу, и никакие строки не записаны. Это показывается для INSERT в следующем примере, снова используя таблицу employees, составленную ранее: mysql> ALTER TABLE employees REORGANIZE PARTITION p3 INTO ( -> PARTITION p3 VALUES LESS THAN (20), -> PARTITION p4 VALUES LESS THAN (25), -> PARTITION p5 VALUES LESS THAN MAXVALUE); Query OK, 6 rows affected (2.09 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE employees\G *************************** 1. row *************************** Table: employees Create Table: CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fname` varchar(25) NOT NULL, `lname` varchar(25) NOT NULL, `store_id` int(11) NOT NULL, `department_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 /*! 50100 PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (25) ENGINE = InnoDB, PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec) mysql> INSERT INTO employees PARTITION (p3, p4) VALUES -> (24, 'Tim', 'Greene', 3, 1), (26, 'Linda', 'Mills', 2, 1); ERROR 1729 (HY000): Found a row not matching the given partition set mysql> INSERT INTO employees PARTITION (p3, p4. p5) VALUES -> (24, 'Tim', 'Greene', 3, 1), (26, 'Linda', 'Mills', 2, 1); Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 Это верно для записи многих строк одним запросом INSERT или REPLACE. Выбор разделения отключен для таблиц, использующих механизм хранения, который поставляет автоматическое разделение, такой как NDB (применяется в MySQL Cluster, сейчас не поддерживается в MySQL 8.0). 20.6. Ограничения на разделение Этот раздел обсуждает текущие ограничения на поддержку разделов MySQL. Запрещенные конструкции. Следующие конструкции не разрешены в выражениях разделений: Хранимые процедуры, сохраненные функции, UDF или плагины. Объявленные или пользовательские переменные. Для списка функций SQL, которые разрешены в разделении выражений, см. раздел 20.6.3. Арифметические и логические операторы. Использование арифметических операторов +, - и * разрешен в разделении выражений. Однако, результат должен быть целочисленным значением или NULL (кроме [LINEAR] KEY, как обсуждено в другом месте в этой главе: см. раздел 20.2). Оператор DIV разрешен. Оператор / запрещен. Битовые операторы | , &, ^, <<, >> и ~ не разрешены в разделении выражений. Режимы сервера SQL. Таблицы, использующие определяемое пользователем разделение, не сохраняют режим SQL в то время, когда они создавались. Как обсуждено в другом месте в этом руководстве (см. раздел 6.1.8), результаты многих функций MySQL и операторов могут измениться, согласно режиму SQL сервера. Поэтому изменение в режиме SQL в любое время после создания разделенных таблиц может привести к существенным изменениям в поведении таких таблиц, и легко может привести к повреждению или потере данных. По этим причинам сильно рекомендуется, чтобы Вы никогда не изменяли режим SQL сервера после составления разделенных таблиц. Примеры. Следующие примеры иллюстрируют некоторые изменения в поведении разделенных таблиц из-за изменения в режиме SQL сервера: Обработка ошибок. Обработка особых значений (0 и NULL) может отличаться в различных режимах SQL (см. раздел 6.1.8). Например, ERROR_FOR_DIVISION_BY_ZERO может затронуть, может ли быть 0 вставлен как значение в таблицу, выражения разделения которой использует column DIV value или column MOD value. Табличная доступность. Иногда изменение в режиме SQL может сделать разделенные таблицы непригодными. Следующий запрос CREATE TABLE может быть выполнен успешно только если режим NO_UNSIGNED_SUBTRACTION включен: mysql> SELECT @@sql_mode; +------------+ | @@sql_mode | +------------+ | | +------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED) -> PARTITION BY RANGE(c1 - 10) ( -> PARTITION p0 VALUES LESS THAN (-5), -> PARTITION p1 VALUES LESS THAN (0), -> PARTITION p2 VALUES LESS THAN (5), -> PARTITION p3 VALUES LESS THAN (10), -> PARTITION p4 VALUES LESS THAN (MAXVALUE)); ERROR 1563 (HY000): Partition constant is out of partition function domain mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@sql_mode; +-------------------------+ | @@sql_mode | +-------------------------+ | NO_UNSIGNED_SUBTRACTION | +-------------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED) PARTITION BY RANGE(c1 - 10) -> (PARTITION p0 VALUES LESS THAN (-5), -> PARTITION p1 VALUES LESS THAN (0), -> PARTITION p2 VALUES LESS THAN (5), -> PARTITION p3 VALUES LESS THAN (10), -> PARTITION p4 VALUES LESS THAN (MAXVALUE)); Query OK, 0 rows affected (0.05 sec) Если Вы удаляете режим SQL NO_UNSIGNED_SUBTRACTION после создания tu, Вы больше не в состоянии получить доступ к этой таблице: mysql> SET sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM tu; ERROR 1563 (HY000): Partition constant is out of partition function domain mysql> INSERT INTO tu VALUES (20); ERROR 1563 (HY000): Partition constant is out of partition function domain Режимы SQL также воздействует на репликацию разделенных таблиц. Несоизмеримые режимы SQL на ведущем и ведомом устройствах могут привести к оценке по-другому выражений разделения, это может заставить распределение данных среди разделения отличаться в копиях ведущего и ведомого устройств данной таблицы, и может даже вызвать вставки в разделенные таблицы, которые преуспевают на ведущем устройстве, чтобы потерпеть неудачу на ведомом. Для лучших результатов Вы должны всегда использовать тот же самый режим SQL. Исполнительные соображения. Некоторые эффекты разделения операций даны в следующем списке: Операции файловой системы. Разделение и переразделение операций (таких, как ALTER TABLE с PARTITION BY ..., REORGANIZE PARTITIONS или REMOVE PARTITIONING) зависят от операций файловой системы для их выполнения. Это означает, что скорость этих операций затронута такими факторами, как тип файловой системы и характеристики, дисковая скорость, область подкачки, эффективность обработки файла операционной системы, параметры сервера MySQL и переменные, которые касаются обработки файла. В частности Вы должны удостовериться, что large_files_support включена, а open_files_limit установлена должным образом. Разделение и переразделение таблиц InnoDB могут быть сделаны более эффективными, включая innodb_file_per_table. Табличные блокировки. Процесс, выполняющий работу разделения на таблице, требует блокировку записи на таблице. Чтения таких таблиц не затронуты, ожидающие запросы INSERT и UPDATE выполнены, как только работа разделения завершилась. Индексы, сокращение разделения. Как с неразделенными таблицами, правильное использование индексов может значительно ускорить запросы на разделенных таблицах. Кроме того, разработка разделенных таблиц и запросов на этих таблицах, чтобы использовать в своих интересах сокращение разделения может резко улучшить работу. См. раздел 20.4. Работа с LOAD DATA. В MySQL 8.0 LOAD DATA буферизация использована, чтобы улучшить работу. Вы должны знать, что буфер использует память 130 КБ на раздел. Максимальное количество разделов. В MySQL 8.0 максимально возможное число разделов для таблицы 8192. Это число включает подразделы. Если, составляя таблицы с большим количеством разделов (но меньше чем максимум), Вы сталкиваетесь с сообщением об ошибке Got error ... from storage engine: Out of resources when opening file, Вы можете решить проблему, увеличивая значение переменной open_files_limit. Однако, это зависит от операционной системы, и может быть невозможно или нежелательно на всех платформах, см. раздел B.5.2.17. В некоторых случаях использование большого количества (сотен) разделов, возможно, также нежелательно из-за других проблем, так что использование большего количества разделов автоматически не приводит к лучшим результатам. Кэш запроса не поддержан. Кэш запроса не поддержан для разделенных таблиц, и автоматически отключен для запросов, вовлекающих разделенные таблицы. Кэш запроса не может быть включен для таких запросов. Внешние ключи не поддержаны для разделенных таблиц InnoDB. Разделенные таблицы, используя InnoDB не поддерживают внешние ключи. Более определенно это означает, что следующие два утверждения верны: Никакое определение InnoDB-таблицы, использующей определяемое пользователем разделение, не может содержать ссылки внешнего ключа, таблица InnoDB, определение которой содержит ссылки внешнего ключа, не может быть разделена. Никакое определение InnoDB-таблицы, не может содержать ссылку внешнего ключа на разделенную пользователем таблицу, никакая InnoDB-таблица с определяемым пользователем разделением не может содержать столбцы, на которые ссылаются внешние ключи. Контекст ограничений включает все таблицы, которые используют механизм хранения InnoDB. CREATE TABLE и ALTER TABLE, которые привели бы к таблицам, нарушающим эти ограничения, не позволены. ALTER TABLE ... ORDER BY. ALTER TABLE ... ORDER BY column для разделенной таблицы вызывает упорядочивание строк только в пределах каждого раздела. Влияние на REPLACE с модификацией первичных ключей. Это может быть желательно в некоторых случаях (см. раздел 20.6.1), чтобы изменить первичный ключ таблицы. Знайте, что если Ваше приложение использует REPLACE и Вы делаете это, результаты этих запросов могут быть решительно изменены. См. раздел 14.2.8. Поддержка FULLTEXT. Разделенные таблицы не поддерживают индексы или поиски FULLTEXT. Пространственные столбцы. Столбцы с пространственными типами данных (например, POINT или GEOMETRY) не могут использоваться в разделенных таблицах. Временные таблицы. Временные таблицы не могут быть разделены. Таблицы журнала. Невозможно разделить таблицы журнала: ALTER TABLE ... PARTITION BY ... на такой таблице терпит неудачу с ошибкой. Тип данных разделения ключа. Ключ разделения должен быть столбцом целого числа или выражением, которое решается к целому числу. Использование выражений, применяющих столбцы ENUM недопустимо. Столбец или значение выражения могут также быть NULL : см. раздел 20.2.7 . Есть два исключения: Для [LINEAR] KEY возможно использовать столбцы любого допустимого типа данных MySQL, кроме TEXT или BLOB как разделение ключей, потому что внутренние хеширующие ключ функции производят правильный тип данных из этих типов. Например, следующие два CREATE TABLE допустимы: CREATE TABLE tkc (c1 CHAR) PARTITION BY KEY(c1) PARTITIONS 4; CREATE TABLE tke (c1 ENUM('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet')) PARTITION BY LINEAR KEY(c1) PARTITIONS 6; Для RANGE COLUMNS или LIST COLUMNS возможно использовать строку, DATE и DATETIME. Например, каждый из следующих CREATE TABLE допустим: CREATE TABLE rc (c1 INT, c2 DATE) PARTITION BY RANGE COLUMNS(c2) ( PARTITION p0 VALUES LESS THAN('1990-01-01'), PARTITION p1 VALUES LESS THAN('1995-01-01'), PARTITION p2 VALUES LESS THAN('2000-01-01'), PARTITION p3 VALUES LESS THAN('2005-01-01'), PARTITION p4 VALUES LESS THAN(MAXVALUE)); CREATE TABLE lc (c1 INT, c2 CHAR(1)) PARTITION BY LIST COLUMNS(c2) ( PARTITION p0 VALUES IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'), PARTITION p1 VALUES IN('b', 'e', 'h', 'k', 'n', 'q', 't', 'w', 'z'), PARTITION p2 VALUES IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL)); Ни одно из предыдущих исключений не относится к столбцам типа BLOB или TEXT. Подзапросы. Ключ разделения не может быть подзапросом, даже если этот подзапрос решается к целочисленному значению или NULL. Проблемы с подразделением. Подразделение должно использовать HASH или KEY. Только разделения RANGE и LIST могут быть подразделены, HASH и KEY не могут быть подразделены. SUBPARTITION BY KEY требует, чтобы столбец подразделения или столбцы были определены явно, в отличие от случая с PARTITION BY KEY , где это может быть опущено (когда столбец первичного ключа таблицы используется по умолчанию). Считайте таблицу создаваемой этим запросом: CREATE TABLE ts (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30)); Вы можете составить таблицу, имеющую те же самые столбцы, разделенные KEY: CREATE TABLE ts (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30)) PARTITION BY KEY() PARTITIONS 4; Предыдущий запрос обработан, как если бы он был написан как этот, со столбцом первичного ключа таблицы, используемым в качестве столбца разделения: CREATE TABLE ts (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30)) PARTITION BY KEY(id) PARTITIONS 4; Однако, следующий запрос, который пытается составить подразделенную таблицу, используя столбец значения по умолчанию в качестве столбца подразделения, терпит неудачу, столбец должен быть определен, как показано здесь: mysql> CREATE TABLE ts (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> name VARCHAR(30)) -> PARTITION BY RANGE(id) SUBPARTITION BY KEY() -> SUBPARTITIONS 4 (PARTITION p0 VALUES LESS THAN (100), -> PARTITION p1 VALUES LESS THAN (MAXVALUE)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') mysql> CREATE TABLE ts (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> name VARCHAR(30)) PARTITION BY RANGE(id) -> SUBPARTITION BY KEY(id) -> SUBPARTITIONS 4 (PARTITION p0 VALUES LESS THAN (100), -> PARTITION p1 VALUES LESS THAN (MAXVALUE)); Query OK, 0 rows affected (0.07 sec) Это известная проблема (см. Bug #51470). Опции DATA DIRECTORY и INDEX DIRECTORY. Опции уровня таблицы DATA DIRECTORY и INDEX DIRECTORY игнорируются (см. Bug #32091). Вы можете использовать эти опции для отдельного разделения или подразделения таблиц InnoDB. Восстановление разделенных таблиц. Запросы CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE и REPAIR TABLE поддержаны для разделенных таблиц. Кроме того, Вы можете использовать ALTER TABLE ... REBUILD PARTITION , чтобы восстановить один или более разделов разделенной таблицы. ALTER TABLE ... REORGANIZE PARTITION также можно использовать. См. раздел 14.1.7. ANALYZE, CHECK, OPTIMIZE, REPAIR и TRUNCATE поддержаны с подразделением. См. раздел 14.1.7.1. 20.6.1. Первичные и уникальные ключи Этот раздел обсуждает отношения ключей разделения с первичными и уникальными ключами. Правило, управляющее этими отношениями, может быть выражено следующим образом: все столбцы, используемые в выражении разделения для разделенной таблицы, должны быть частью каждого уникального ключа, который может иметь таблица. Другими словами, каждый уникальный ключ на таблице должен использовать каждый столбец в выражении разделения таблицы. Это также включает первичный ключ таблицы, так как это по определению уникальный ключ. Этот особый случай рассмотрен позже в этом разделе. Например, каждый из следующих табличных запросов создания недопустим: 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 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; Этот пример показывает ошибку, произведенную в таких случаях: mysql> 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; ERROR 1491 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function CREATE TABLE терпит неудачу потому что оба столбца col1 и col3 включены в предложенный ключ разделения, но ни один из этих столбцов не часть обоих уникальных ключей таблицы. Это показывает одно возможное затруднительное положение для недопустимого табличного определения: mysql> 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; Query OK, 0 rows affected (0.05 sec) В этом случае предложенный ключ разделения col3 часть обоих уникальных ключей, и табличный запрос создания работает. Следующая таблица не может быть разделена вообще, потому что нет никакого способа включать в ключ разделения любые столбцы, которые принадлежат обоим уникальным ключам: CREATE TABLE t4 (col1 INT NOT NULL, col2 INT NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col3), UNIQUE KEY (col2, col4)); Так как каждый первичный ключ по определению уникальный, это ограничение также включает первичный ключ таблицы, если он вообще есть. Например, следующие два запроса недопустимы: CREATE TABLE t5 (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 t6 (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 t7 (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 t8 (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; Если у таблицы нет никакого уникального ключа, это включает отсутствие первичного ключа, тогда это ограничение не применяется, и Вы можете использовать любой столбец или столбцы в выражении разделения, пока тип столбца совместим с типом разделения. По той же самой причине Вы не можете позже добавить уникальный ключ к разделенной таблице, если ключ не включает все столбцы, используемые выражением разделения таблицы. Считайте разделенную таблицу создаваемой как показано здесь: mysql> 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)); Query OK, 0 rows affected (0.12 sec) Возможно добавить первичный ключ к t_no_pk, используя любой из запросов ALTER TABLE: # possible PK mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1); Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 # drop this PK mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 # use another possible PK mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2); Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 # drop this PK mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 Однако, следующий запрос терпит неудачу, потому что c1 часть ключа разделения, но не часть предложенного первичного ключа: # fails with error 1503 mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c2); ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function С тех пор, как t_no_pk имеет только c1 в выражении разделения, попытки добавить уникальный ключ на c2 проваливаются. Однако, Вы можете добавить уникальный ключ, который использует оба c1 и c2. Эти правила также относятся к существующим неразделенным таблицам, которые Вы желаете разделить через ALTER TABLE ... PARTITION BY. Рассмотрите таблицу np_pk, создаваемую как показано здесь: mysql> CREATE TABLE np_pk (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), added DATE, PRIMARY KEY (id)); Query OK, 0 rows affected (0.08 sec) Следующий ALTER TABLE терпит неудачу с ошибкой, потому что столбец added не часть любого уникального ключа в таблице: mysql> ALTER TABLE np_pk PARTITION BY HASH(TO_DAYS(added)) PARTITIONS 4; ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function Однако, этот запрос, использующий столбец id для столбца разделения допустим, как показано здесь: mysql> ALTER TABLE np_pk PARTITION BY HASH(id) PARTITIONS 4; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 В случае np_pk единственный столбец, который может использоваться в качестве части выражения разделения, id. Если Вы хотите разделить эту таблицу, используя какой-либо другой столбец (или столбцы) в выражении разделения, Вы должны сначала изменить таблицу, добавляя желаемый столбец или столбцы к первичному ключу, или удаляя первичный ключ в целом. 20.6.2. Ограничения, касающиеся механизмов хранения В MySQL 8.0 поддержка разделов фактически обеспечена не сервером MySQL, а собственным обработчиком разделения механизма хранения таблицы. В MySQL 8.0 только InnoDB обеспечивает обработчик разделения. Это означает, что разделенные таблицы не могут быть составлены, используя любой другой механизм хранения. MySQL Cluster NDB также оказывает поддержку разделения, но в настоящее время не поддерживается в MySQL 8.0. ALTER TABLE ... OPTIMIZE PARTITION не работает правильно с разделенными таблицами, которые применяют InnoDB. Примените ALTER TABLE ... REBUILD PARTITION и ALTER TABLE ... ANALYZE PARTITION. См. раздел 14.1.7.1. Обновление разделенных таблиц. Выполняя обновление, таблицы, которые разделены KEY должны быть выведены и перезагружены. Разделенные таблицы, использующие механизмы хранения, кроме InnoDB, не могут быть обновлены от MySQL 5.7 или ранее к MySQL 8.0: Вы должны или исключить разделение из таких таблиц с ALTER TABLE ... REMOVE PARTITIONING или преобразовать их в InnoDB через ALTER TABLE ... ENGINE=INNODB до обновления. 20.6.3. Ограничения, касающиеся функций Этот раздел обсуждает ограничения в разделении MySQL, имеющие отношение определенно к функциям, используемым в разделении выражений. Только функции MySQL, показанные в следующей таблице, позволены в разделении выражений. ABS() CEILING() (см. CEILING() and FLOOR()) DAY() DAYOFMONTH() DAYOFWEEK() DAYOFYEAR() DATEDIFF() EXTRACT() (см. EXTRACT() function with WEEK specifier) FLOOR() (см. CEILING() и FLOOR()) HOUR() MICROSECOND() MINUTE() MOD() MONTH() QUARTER() SECOND() TIME_TO_SEC() TO_DAYS() TO_SECONDS() UNIX_TIMESTAMP() (со столбцами TIMESTAMP) WEEKDAY() YEAR() YEARWEEK() В MySQL 8.0 сокращение разделения поддержано для функций the TO_DAYS(), TO_SECONDS(), YEAR() и UNIX_TIMESTAMP() . См. раздел 20.4. CEILING() и FLOOR(). Каждая из этих функций возвращает целое число, только если ей передают параметр точного числового типа, такого как один из типов INT или DECIMAL. Это означает, например, что следующий запрос CREATE TABLE терпит неудачу с ошибкой, как показано здесь: mysql> CREATE TABLE t (c FLOAT) PARTITION BY LIST(FLOOR(c))( -> PARTITION p0 VALUES IN (1,3,5), -> PARTITION p1 VALUES IN (2,4,6)); ERROR 1490 (HY000): The PARTITION function returns the wrong type Функция EXTRACT() с опцией WEEK. Значение, возвращенное функцией EXTRACT(), когда используется как EXTRACT(WEEK FROM col), зависит от значения системной переменной default_week_format. Поэтому EXTRACT() не разрешена как функция разделения, когда это определяет модуль как WEEK (Bug #54483). См. раздел 13.6.2 для получения дополнительной информации о типах возвращения этих функций, так же как раздел 12.2.

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

Вы можете направить письмо администратору этой странички, Алексею Паутову.