The OpenNET Project / Index page

[ новости /+++ | форум | теги | ]



"Раздел полезных советов: Использование разделов MySQL для ра..."
Вариант для распечатки  
Пред. тема | След. тема 
Форум Разговоры, обсуждение новостей
Изначальное сообщение [ Отслеживать ]

"Раздел полезных советов: Использование разделов MySQL для ра..."  +/
Сообщение от auto_tips (??), 20-Ноя-12, 15:32 
Приходится иметь дело с таблицами, которые содержат редко (или никогда) обновляемые данные, такие как логи. Некоторые таблицы чистятся, некоторые хранят записи "вечно". Чтобы уменьшить нагрузку на диск и ФС, придумали такую вещь как partitioning  (Cекционирование).

Часто необходимо резать таблицу на partition по году по месяцу или по дням месяца/недели. Что-то подсказывает что резать придется по полю типа timestamp.


Сделаем табличку

   CREATE TABLE `foo` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
     `name` varchar(30) DEFAULT NULL,
     `email` varchar(30) DEFAULT NULL,
     PRIMARY KEY (`id`)
   );

и попробуем порезать ее по годам

   ALTER TABLE foo PARTITION BY RANGE (YEAR(date_added))
   (
    PARTITION p2011 VALUES LESS THAN (2012) ,
    PARTITION p2012 VALUES LESS THAN (2013) ,
    PARTITION p2013 VALUES LESS THAN (2014)
   );

получаем:

    ERROR 1486 (HY000): Constant, random or timezone-dependent   expressions in (sub)partitioning function are not allowed

объяснения этому вот какое: "TIMESTAMP is internally converted to the local sessions timezone."

ладно:

   SELECT UNIX_TIMESTAMP('2012-01-01 00:00:00');
   +---------------------------------------+
   | UNIX_TIMESTAMP('2012-01-01 00:00:00') |
   +---------------------------------------+
   |                            1325361600 |
   +---------------------------------------+

   SELECT UNIX_TIMESTAMP('2013-01-01 00:00:00'); = 1356984000
   SELECT UNIX_TIMESTAMP('2014-01-01 00:00:00'); = 1388520000

теперь:

    ALTER TABLE foo PARTITION BY RANGE (UNIX_TIMESTAMP(date_added))
   (
    PARTITION p2011 VALUES LESS THAN (1325361600) ,
    PARTITION p2012 VALUES LESS THAN (1356984000) ,
    PARTITION p2013 VALUES LESS THAN (1388520000) ,
    PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE)
   );
  
вот, теперь получаем:

   ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

это лечится:

   ALTER table foo DROP PRIMARY KEY, add PRIMARY KEY (`id`,`date_added`);

и еще раз:

   ALTER TABLE foo PARTITION BY RANGE (UNIX_TIMESTAMP(date_added))
   (
    PARTITION p2011 VALUES LESS THAN (1325361600) ,
    PARTITION p2012 VALUES LESS THAN (1356984000) ,
    PARTITION p2013 VALUES LESS THAN (1388520000) ,
    PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE)
   );

все ок.

получаем:

   CREATE TABLE `foo` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
     `name` varchar(30) DEFAULT NULL,
     `email` varchar(30) DEFAULT NULL,
     PRIMARY KEY (`id`,`date_added`)
   ) ENGINE=InnoDB
  
   PARTITION BY RANGE (UNIX_TIMESTAMP(date_added))
   (PARTITION p2011 VALUES LESS THAN (1325361600) ENGINE = InnoDB,
    PARTITION p2012 VALUES LESS THAN (1356984000) ENGINE = InnoDB,
    PARTITION p2013 VALUES LESS THAN (1388520000) ENGINE = InnoDB,
    PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ENGINE = InnoDB);


Отлично!

Такой тип "нарезки" подходит если надо архивные данные разложить по файлам "за год" или по месяцам.

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

    PARTITION BY RANGE (MONTH(date))

или так

    PARTITION BY RANGE (DAY(date_add))

поле типа timestamp не подходит.

Гугление говорит что надо использовать datetime.

ок, создадим таблицу

   CREATE TABLE `foo` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `date_added` datetime  DEFAULT NULL,
     `name` varchar(30) DEFAULT NULL,
     `email` varchar(30) DEFAULT NULL,
     PRIMARY KEY (`id`,`date_added`)
   ) ENGINE=InnoDB;

Обратите внимание:

   `date_added` datetime  DEFAULT NULL,

дело в том что, CURRENT_TIMESTAMP в качестве значения по умолчанию для поля типа datetime не катит, NOW() как значение по умолчанию указывать нельзя т.к.  функция.

А  надо чтоб date_added выставлялось автоматом.

выхода 2

1. либо во всех запросах в INSERT добавлять NOW().
2. если первое невозможно, то вешать триггер который при каждом добавлении будет date_added=NOW();

что-то вроде

   DELIMITER $$

   USE `test_db`$$

   CREATE
       /*!50017 DEFINER = 'trigger'@'%' */
       TRIGGER `foo_add` BEFORE INSERT ON `foo`
       FOR EACH ROW BEGIN

         SET NEW.date_added = IFNULL(NEW.date_added, NOW());

       END;
   $$

Теперь у нас таблица с нужными типами, ключами и триггером.

и мы с легкостью можем разрезать таблицу по месяцам:

    ALTER TABLE foo PARTITION BY RANGE (MONTH(date_added))
   (
   PARTITION p01 VALUES LESS THAN (02) ,
   PARTITION p02 VALUES LESS THAN (03) ,
   PARTITION p03 VALUES LESS THAN (04) ,
   PARTITION p04 VALUES LESS THAN (05) ,
   PARTITION p05 VALUES LESS THAN (06) ,
   PARTITION p06 VALUES LESS THAN (07) ,
   PARTITION p07 VALUES LESS THAN (08) ,
   PARTITION p08 VALUES LESS THAN (09) ,
   PARTITION p09 VALUES LESS THAN (10) ,
   PARTITION p10 VALUES LESS THAN (11) ,
   PARTITION p11 VALUES LESS THAN (12) ,
   PARTITION p12 VALUES LESS THAN (13) ,
   PARTITION pmaxval VALUES LESS THAN MAXVALUE );

или даже по дням недели:

    ALTER TABLE foo PARTITION BY RANGE (DAYOFWEEK(date_added))
   (
   PARTITION p01 VALUES LESS THAN (2) ,
   PARTITION p02 VALUES LESS THAN (3) ,
   PARTITION p03 VALUES LESS THAN (4) ,
   PARTITION p04 VALUES LESS THAN (5) ,
   PARTITION p05 VALUES LESS THAN (6) ,
   PARTITION p06 VALUES LESS THAN (7) ,
   PARTITION p07 VALUES LESS THAN (8) ,
   PARTITION pmaxval VALUES LESS THAN MAXVALUE );

или даже 2 дня на partition:

   ALTER TABLE foo PARTITION BY LIST (DAY(date_added))
   (
   PARTITION p00 VALUES IN  (0,1) ,
   PARTITION p02 VALUES IN  (2,3) ,
   PARTITION p04 VALUES IN  (4,5) ,
   PARTITION p06 VALUES IN  (6,7) ,
   PARTITION p08 VALUES IN  (8,9) ,
   PARTITION p10 VALUES IN  (10,11),
   PARTITION p12 VALUES IN  (12,13),
   PARTITION p14 VALUES IN  (14,15),
   PARTITION p16 VALUES IN  (16,17),
   PARTITION p18 VALUES IN  (18,19),
   PARTITION p20 VALUES IN  (20,21),
   PARTITION p22 VALUES IN  (22,23),
   PARTITION p24 VALUES IN  (24,25),
   PARTITION p26 VALUES IN  (26,27),
   PARTITION p28 VALUES IN  (28,29),
   PARTITION p30 VALUES IN  (30,31)
   );

В общем теперь все в ваших руках.


P.S. Подразумевается что выставлена опция innodb_file_per_table


URL:
Обсуждается: https://www.opennet.ru/tips/info/2721.shtml

Ответить | Правка | Cообщить модератору

Оглавление

Сообщения [Сортировка по времени | RSS]


1. "Использование разделов MySQL для разбиения таблицы по дням месяцам и годам"  +1 +/
Сообщение от Nomad (??), 20-Ноя-12, 15:32 
<sarcasm>
Спасибо за репост с хабра!
</sarcasm>
Ответить | Правка | Наверх | Cообщить модератору

2. "Использование разделов MySQL для разбиения таблицы по дням месяцам и годам"  +/
Сообщение от AlexAT (ok), 21-Ноя-12, 07:45 
Бугага. Неужели хабр настолько деградировал, что начал писать детские примеры, да еще и одобренные КЭПом из мануала по MySQL?

Всем желающим разобраться рекомендую:
http://dev.mysql.com/doc/refman/5.5/en/partitioning.html

Исчерпывающая информация, необходимая для понимания - зачем оно, как сделать, и какие ограничения есть.

Ответить | Правка | Наверх | Cообщить модератору

3. "Использование разделов MySQL для разбиения таблицы по дням м..."  +/
Сообщение от fenist (?), 21-Ноя-12, 16:57 
Большинство новостей подобного рода полезно только одним - можно узнать о какой нибудь фиче в области чуть в стороне от основной специальности. А подобные обрывочные знания о предмете расширяют кругозор. Да, все подробно есть в доках, но какой смысл читать документашки от корки до корки, если это не твоя сфера? Лучше эти часы с семьей провести. А для кругозора - достаточно таких заметок.
Ответить | Правка | Наверх | Cообщить модератору

4. "Использование разделов MySQL для разбиения таблицы по дням месяцам и годам"  +/
Сообщение от anonymous (??), 10-Дек-12, 21:08 
триггеры для логов не очень хорошо, т.к. триггеры медленные в mysql
Ответить | Правка | Наверх | Cообщить модератору

5. "Использование разделов MySQL для разбиения таблицы по дням м..."  +/
Сообщение от AlexAT (ok), 10-Дек-12, 21:10 
> триггеры для логов не очень хорошо, т.к. триггеры медленные в mysql

внезапно - не только в mysql. технология в принципе тормозная

Ответить | Правка | Наверх | Cообщить модератору

6. "Использование разделов MySQL для разбиения таблицы по дням м..."  +/
Сообщение от anonymous (??), 13-Дек-12, 04:52 
проще так написать, вместо того чтобы спрорить с доводами вроде, а вот в oracle/mssql/db2/postresql триггеры намного быстрее чем в mysql поэтому их можно везде пихать.
Ответить | Правка | Наверх | Cообщить модератору

7. "Использование разделов MySQL для разбиения таблицы по дням м..."  +1 +/
Сообщение от AlexAT (ok), 13-Дек-12, 07:25 
> проще так написать, вместо того чтобы спрорить с доводами вроде, а вот
> в oracle/mssql/db2/postresql триггеры намного быстрее чем в mysql поэтому их можно
> везде пихать.

Скажите, где работаете, чтобы я ненароком софт вашей компании не взял (если она конечно софт производит). Приходится поневоле работать с платформой таких вот "пихальщиков" - тормозилово безбожное. Но выкинуть уже в планах.

Ответить | Правка | Наверх | Cообщить модератору

8. "Использование разделов MySQL для разбиения таблицы по дням м..."  +/
Сообщение от Аноним (-), 19-Дек-12, 12:11 
Забавно, проблема с кривым софтом, неправильно использующим базу данных, в вашей компании, а софт вы не хотите использовать моей компании, где такой проблемы никогда не было.
Ответить | Правка | Наверх | Cообщить модератору

9. "Использование разделов MySQL для разбиения таблицы по дням м..."  +/
Сообщение от AlexAT (ok), 19-Дек-12, 14:08 
> а софт вы не хотите использовать моей компании, где такой проблемы
> никогда не было.

У этого кривого софта подходы ровно те же, что вы озвучили ранее.

Ответить | Правка | Наверх | Cообщить модератору

11. "Использование разделов MySQL для разбиения таблицы по дням месяцам и годам"  +/
Сообщение от sdfasemail (?), 06-Авг-23, 10:24 
<script>console.log(1111)</script>
Ответить | Правка | Наверх | Cообщить модератору

Архив | Удалить

Рекомендовать для помещения в FAQ | Индекс форумов | Темы | Пред. тема | След. тема




Партнёры:
PostgresPro
Inferno Solutions
Hosting by Hoster.ru
Хостинг:

Закладки на сайте
Проследить за страницей
Created 1996-2024 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру