Партиционирование (секционирование) данных — это реально крутая штука при больших объемах данных. Вот рабочий пример. Допустим, у нас есть два списка: грамматические категории и словоформы. Первому списку соответствует таблица grammar, второму — forms. В таблицах 65 и 4058805 записей соответственно. Отношение между таблицами — многие-ко-многим, роль вспомогательной таблицы выполняет таблица morphology. В ней 23044224 записей. Её-то мы и будем партиционировать.

Важное замечание: не предполагается добавление новых записей. Иными словами, количество категорий не меняется.

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

Создаем чистую таблицу:

CREATE TABLE IF NOT EXISTS `grammar` (
  `id` int(11) NOT NULL,
  `id_form` int(11) NOT NULL,
  `id_category` int(11) NOT NULL,
  UNIQUE KEY (`id_category`, `id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PARTITION BY LIST(`id_category`) (
PARTITION p0 VALUES IN (4),
PARTITION p1 VALUES IN (5),
PARTITION p2 VALUES IN (8),
PARTITION p3 VALUES IN (9),
PARTITION p4 VALUES IN (11),
PARTITION p5 VALUES IN (12),
PARTITION p6 VALUES IN (14),
PARTITION p7 VALUES IN (22),
PARTITION p8 VALUES IN (24),
PARTITION p9 VALUES IN (27),
PARTITION p10 VALUES IN (30),
PARTITION p11 VALUES IN (32),
PARTITION p12 VALUES IN (33),
PARTITION p13 VALUES IN (40),
PARTITION p14 VALUES IN (43),
PARTITION p15 VALUES IN (49),
PARTITION p16 VALUES IN (50),
PARTITION p17 VALUES IN (51),
PARTITION p18 VALUES IN (52),
PARTITION p19 VALUES IN (54),
PARTITION p20 VALUES IN (57),
PARTITION p21 VALUES IN (60),
PARTITION p22 VALUES IN (61),
PARTITION p23 VALUES IN (62),
PARTITION p24 VALUES IN (1,2,3,6,7,10,13,15,16,17,18,19,20,
21,23,25,26,28,29,31,34,35,36,37,38,39,41,42,44,45,46,47,
48,53,55,56,58,59,63,64,65)
)

Результат простого запроса с одним джойном:

   SELECT * 
     FROM grammar AS g
LEFT JOIN morphology AS m 
       ON g.id = m.id_category
    WHERE g.category IN ('зват')

Showing rows 0 - 1 (2 total, Query took 0.0011 sec)

Добавив в SQL-запрос перед селектом EXPLAIN PARTITIONS можно увидеть, сколько данных обработала БД: 230442 (сократили область поиска на два порядка).

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

UNIQUE KEY (`id_category`, `id`)

Теперь представим, что мы сделали так:

UNIQUE KEY (`id`, `id_category`)

Тогда наш запрос выполнялся бы дольше:

   SELECT *
     FROM grammar AS g
LEFT JOIN morphology AS m 
       ON g.id = m.id_category
    WHERE g.category IN ('зват')

Showing rows 0 - 1 (2 total, Query took 1.7585 sec)

EXPLAIN PARTITIONS показывает, что поиск осуществлялся по всем 23044224 записям. Поэтому скорость поиска упала на три (!) порядка.

Почему так происходит? Вернемся к нашим ключам:

UNIQUE KEY (`id_category`, `id`)
UNIQUE KEY (`id`, `id_category`)

— это сокращенная запись. MySQL интерпретирует их следующим образом:

UNIQUE KEY `id_category` (`id_category`, `id`)
UNIQUE KEY `id` (`id`, `id_category`)

В результате получается, что в первом случае БД оптимизирована, а во втором — нет и MySQL перебирает все записи.