Mysql партицирование. Как разделить таблицу по списку и дате?

Question

[Vladbara705]

Всем привет. Есть следующая таблица (упрощенная):

CREATE TABLE `routes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`created` datetime NOT NULL,
`createdTimezoneOffset` smallint(6) NOT NULL DEFAULT '0',
`deleted` tinyint(1) DEFAULT '0',
 PRIMARY KEY (`id`,`created`,`archive`)
) ENGINE=InnoDB AUTO_INCREMENT=1014699 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Необходимо разделить таблицу следующим образом:

Раздел: Архив = 0
Подраздел: Год создания

Раздел: Архив = 1
Подраздел: Год создания

Делаю так:

ALTER TABLE routes PARTITION BY LIST (archive)
SUBPARTITION BY HASH (YEAR(created))
(
   PARTITION p0 VALUES IN (0)
   (
      SUBPARTITION s0,
      SUBPARTITION s1,
      SUBPARTITION s2,
      SUBPARTITION s3
   ),
   PARTITION p1 VALUES IN (1)
   (
      SUBPARTITION s4,
      SUBPARTITION s5,
      SUBPARTITION s6,
      SUBPARTITION s7
   )
);

И все вроде бы работает. Но когда делаю выборку по периоду поля created - читаются все разделы.

Пример:  EXPLAIN SELECT * FROM routes WHERE archive = 1 AND created >= '2020-01-01 00:00:00';

Исходя из документации - понимаю почему.
Как правильно разделить таблицу таким образом, чтобы запрос выше брал определенную часть таблицы ?

Comments

[Vitsliputsli]

С виду все правильно. Покажите вывод для:
show create table routes
EXPLAIN SELECT * FROM routes WHERE archive = 1 AND created >= '2020-01-01 00:00:00';

[Vladbara705]

CREATE TABLE `routes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `createdTimezoneOffset` smallint(6) NOT NULL DEFAULT '0',
  `archive` tinyint(1) NOT NULL DEFAULT '0',
  `deleted` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`,`created`,`archive`)
) ENGINE=InnoDB AUTO_INCREMENT=1254237 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50100 PARTITION BY LIST (archive)
SUBPARTITION BY HASH (YEAR(created))
(PARTITION p0 VALUES IN (0)
 (SUBPARTITION s0 ENGINE = InnoDB,
  SUBPARTITION s1 ENGINE = InnoDB,
  SUBPARTITION s2 ENGINE = InnoDB,
  SUBPARTITION s3 ENGINE = InnoDB),
 PARTITION p1 VALUES IN (1)
 (SUBPARTITION s4 ENGINE = InnoDB,
  SUBPARTITION s5 ENGINE = InnoDB,
  SUBPARTITION s6 ENGINE = InnoDB,
  SUBPARTITION s7 ENGINE = InnoDB)) */

Это по диапазону дат:

EXPLAIN SELECT * FROM routes WHERE archive = 1 AND created >= '2020-01-01 00:00:00';

id	select_type	table	partitions		type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE		routes	p1_s4,p1_s5,p1_s6,p1_s7	ALL	[NULL]		[NULL]	[NULL]	[NULL]	497 616	3,33		Using where

Это просто по равенству:

EXPLAIN SELECT * FROM routes WHERE archive = 1 AND created = '2020-01-01 00:00:00';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE		routes	p1_s4		ALL	[NULL]		[NULL]	[NULL]	[NULL]	477 588	1		Using where

То есть если я выбираю по диапазону, то он берет все части. С BETWEEN та же история

Answer 1

[Vladbara705]

Решение найдено. Работает так, как надо, оставлю это здесь:

ALTER TABLE routes PARTITION BY RANGE COLUMNS (archive, created)
(
    PARTITION p0 VALUES LESS THAN (0, MAXVALUE),
    PARTITION p1 VALUES LESS THAN (1, '2019-01-01'),
    PARTITION p2 VALUES LESS THAN (1, '2020-01-01'),
    PARTITION p3 VALUES LESS THAN (1, '2020-06-01'),
    PARTITION p4 VALUES LESS THAN (1, MAXVALUE)
);

Comments

[ivan_default]

а есть возможность, сделать чтоб автоматом разбивалось, на новую секцию каждую неделю? неужели все время руками прийдеться делать?

[Vladbara705]

ivan_default, поставь задачу в крон и он будет автоматом разбивать. Там тебе даты только поменять придется и все