С версии MySQL 5.1 поддерживается партиция, позволяя Вам распределить части индивидуальных таблиц по файловым системам согласно правилам, которые Вы можете устанавливать в значительной степени так, как необходимо.
Типы Разбиения
RANGE partitioning: назначает строки разделам, основанным на значениях столбца, попадающих внутрь заданного диапазона.
LIST partitioning: подобно выделению разделов диапазоном, за исключением того, что раздел выбран основанным на столбцах, соответствующих одному из набора дискретных значений.
HASH partitioning: раздел выбран основанным на значении, возвращенном определяемым пользователем выражением, которое функционирует на значениях столбца в строках, которые будут вставлены в таблицу. Функция может состоять из любого выражения, допустимого в MySQL, которое выдает не отрицательное целочисленное значение.
KEY partitioning: подобно выделению разделов hash, за исключением того, что обеспечены только один или большее количество столбцов, которые будут оценены, и сервер MySQL обеспечивает собственную хэш-функцию. Эти столбцы могут содержать не целочисленные значения, так как хэш-функция, обеспеченная MySQL, гарантирует целочисленный результат, независимо от типа данных столбца.
От слов к практике. На примере Таблица разбитая на 2 раздела с типом LIST
CREATE TABLE 'contact` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`created` DATETIME NOT NULL,
`text1` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`text2` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`file_name` VARCHAR(40) NOT NULL COLLATE 'utf8mb4_unicode_ci',
`pn` TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
PRIMARY KEY (`id`,`pn`) USING BTREE,
INDEX `file_name_idx` (`file_name`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=3794499
/*!50100 PARTITION BY LIST (pn)
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
PARTITION p1 VALUES IN (1) ENGINE = InnoDB) */;
Тут стоит обратить внимание на строчку PRIMARY KEY (`id`,`pn`) USING BTREE,
Если не добавить в PRIMARY KEY столбец pn то ничего не выйдет.
Для меня в данном случае, разбиение на партиции была нужна для того, чтобы быстро удалять данные относящиеся к 0 или к 1 партиции. Делается это командой
ALTER TABLE contact TRUNCATE PARTITION p0;
Которая за 1 сек может удалить миллионы строк (по сути удаляет файл) из партиции p0
На физ уровне будет два файла таблицы:
contact#P#p0.ibd
contact#P#p1.ibd
Хорошая документация тут http://www.rldp.ru/mysql/mysqlpro/parts.htm
Кстати, update по столбцу pn происходит очень долго, и после такого апдейта команда
ALTER TABLE contact TRUNCATE PARTITION p0;
тоже происходит не мгновенно