Как разбить таблицу на столбец datetime?

Я хочу разбить таблицу mysql на столбец datetime. Однажды раздел. Создание сценариев таблицы выглядит следующим образом:

CREATE TABLE raw_log_2011_4 (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  logid char(16) NOT NULL,
  tid char(16) NOT NULL,
  reporterip char(46) DEFAULT NULL,
  ftime datetime DEFAULT NULL,
  KEY id (id)
) ENGINE=InnoDB AUTO_INCREMENT=286802795 DEFAULT CHARSET=utf8
PARTITION BY hash (day(ftime)) partitions 31;

Но когда я выбираю данные какого-то дня. Не удалось найти раздел. Оператор select выглядит так:

explain partitions select * from raw_log_2011_4 where day(ftime) = 30;

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

explain partitions select * from raw_log_2011_4 where ftime = '2011-03-30';

Кто-нибудь скажет мне, как я мог бы выбрать данные в какой-то день и использовать раздел. Спасибо!

Ответ 1

Разделы HASH - очень плохая идея с столбцами datetime, потому что они не могут использовать обрезку разделов. Из документов MySQL:

Обрезка может использоваться только для целочисленных столбцов таблиц, разбитых на HASH или KEY. Например, этот запрос в таблице t4 не может использовать обрезку потому что dob является столбцом DATE:

SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';

Однако, если таблица хранит значения года в столбце INT, тогда запрос с WHERE year_col >= 2001 AND year_col <= 2005 может быть обрезка.

Таким образом, вы можете сохранить значение TO_DAYS (DATE()) в дополнительном столбце INTEGER, чтобы использовать обрезку.

Другой вариант - использовать разделение RANGE:

CREATE TABLE raw_log_2011_4 (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  logid char(16) NOT NULL,
  tid char(16) NOT NULL,
  reporterip char(46) DEFAULT NULL,
  ftime datetime DEFAULT NULL,
  KEY id (id)
) ENGINE=InnoDB AUTO_INCREMENT=286802795 DEFAULT CHARSET=utf8
  PARTITION BY RANGE( TO_DAYS(datetime) ) (
    PARTITION p20110401 VALUES LESS THAN (TO_DAYS('2011-04-02')),
    PARTITION p20110402 VALUES LESS THAN (TO_DAYS('2011-04-03')),
    PARTITION p20110403 VALUES LESS THAN (TO_DAYS('2011-04-04')),
    PARTITION p20110404 VALUES LESS THAN (TO_DAYS('2011-04-05')),
    ...
    PARTITION p20110426 VALUES LESS THAN (TO_DAYS('2011-04-27')),
    PARTITION p20110427 VALUES LESS THAN (TO_DAYS('2011-04-28')),
    PARTITION p20110428 VALUES LESS THAN (TO_DAYS('2011-04-29')),
    PARTITION p20110429 VALUES LESS THAN (TO_DAYS('2011-04-30')),
    PARTITION future VALUES LESS THAN MAXVALUE
  );

Теперь следующий запрос будет использовать только раздел p20110403:

SELECT * FROM raw_log_2011_4 WHERE ftime = '2011-04-03';

Ответ 2

Привет. Вы делаете неправильный раздел в определении таблицы, которое должно выглядеть так:

CREATE TABLE raw_log_2011_4 (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  logid char(16) NOT NULL,
  tid char(16) NOT NULL,
  reporterip char(46) DEFAULT NULL,
  ftime datetime DEFAULT NULL,
  KEY id (id)
) ENGINE=InnoDB AUTO_INCREMENT=286802795 DEFAULT CHARSET=utf8
PARTITION BY hash (TO_DAYS(ftime)) partitions 31;

И ваша команда выбора будет:

explain partitions 
    select * from raw_log_2011_4 where TO_DAYS(ftime) = '2011-03-30';

Вышеуказанная команда будет выбирать всю требуемую дату, как если бы вы использовали команду TO_DAYS как

mysql> SELECT TO_DAYS(950501);
        -> 728779
mysql> SELECT TO_DAYS('2007-10-07');
        -> 733321

Зачем использовать TO_DAYS AS Оптимизатор MySQL распознает две функции, основанные на дате, для целей обрезки разделов: 1.TO_DAYS() 2.YEAR()

и это решит вашу проблему.

Ответ 3

Недавно я прочитал сообщение в блоге MySQL об этом, http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html.

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

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

Ответ 4

Не используйте CHAR, используйте VARCHAR. Это сэкономит много места, следовательно, уменьшит количество операций ввода-вывода, следовательно, ускорит выполнение запросов.

reporterip: (46) излишне большой для IP-адреса, даже IPv6. См. Мой блог для дальнейшего обсуждения, в том числе о том, как сжать его до 16 байт.

PARTITION BY RANGE(TO_DAYS(...)), как предположил @Steyx, но не более 50 разделов. Чем больше у вас разделов, тем медленнее возникают запросы, несмотря на "обрезку". Разделение HASH практически бесполезно.

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