MySQL: руководство профессионала - Алексей Паутов

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

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT, store_id INT)

PARTITION BY RANGE (YEAR(separated)) (

PARTITION p0 VALUES LESS THAN (1991),

PARTITION p1 VALUES LESS THAN (1996),

PARTITION p2 VALUES LESS THAN (2001),

PARTITION p3 VALUES LESS THAN MAXVALUE);

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

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

Вы хотите удалить старые данные. Если Вы используете схему выделения разделов, показанную выше, Вы можете просто использовать ALTER TABLE employees DROP PARTITION p0; , чтобы удалять все строки в отношении служащих, оставивших работу до 1991. Для таблицы с очень многими строками, это может быть намного более эффективно, чем выполнение запроса DELETE, например,

DELETE FROM employees WHERE YEAR(separated) <=1990;.

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

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

SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;,

MySQL может быстро определять, что только раздел p2 должен быть просмотрен, потому что остающиеся разделы не могут содержать записи, удовлетворяющие предложению WHERE.

3.2.2. LIST Partitioning

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

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

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

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

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT, store_id INT);

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

Область

Store ID Numbers

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

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

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT, store_id INT)

PARTITION BY LIST(store_id) (

PARTITION pNorth VALUES IN (3,5,6,9,17),

PARTITION pEast VALUES IN (1,2,10,11,19,20),

PARTITION pWest VALUES IN (4,12,13,14,18),

PARTITION pCentral VALUES IN (7,8,15,16));

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

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

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code CHAR(1), store_id INT);

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

Категория работы или отдел

Коды работы

ManagementD, M, O, PSalesB, L, STechnicalA, E, G, I, TClericalK, N, YSupportC, F, J, R, VUnassignedEmpty

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

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code CHAR(1), store_id INT)

PARTITION BY LIST(ASCII(UCASE(job_code))) (

PARTITION management VALUES IN(68, 77, 79, 80),

PARTITION sales VALUES IN(66, 76, 83),

PARTITION technical VALUES IN(65, 69, 71, 73, 84),

PARTITION clerical VALUES IN(75, 78, 89),

PARTITION support VALUES IN(67, 70, 74, 82, 86),

PARTITION unassigned VALUES IN(NULL, 0, 32));

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

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

INSERT INTO employees VALUES

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

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

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

3.2.3. HASH Partitioning

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

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

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

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT, store_id INT)

PARTITION BY HASH(store_id) PARTITIONS 4;

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

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