• Откуда: Вильнюс (Литва)
  • Зарегистрирован: 19-05-2005
  • Сообщений: 1,090
  • User Karma: 9

Тема: Percona-Cluster 57 и partitioning

На днях делал обновление https://unix-forum.ru/topic1964.html не из за того что зудит... Решил проверить как мне могут помочь партиции, до этого использовал их только в PgSQL. Не поддерживают пока что FOREIGN KEYs, но все остальное просто супер.

вот такую таблицу имею в оригинале

integrations_transaction | CREATE TABLE `integrations_transaction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` smallint(5) unsigned NOT NULL,
  `status` smallint(5) unsigned NOT NULL,
  `uid` varchar(50) NOT NULL,
  `amount` int(10) unsigned NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `player_id` int(11) NOT NULL,
  `round_id` int(11) DEFAULT NULL,
  `currency` varchar(4),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`),
  KEY `integrations_transa_player_id_97ff06e2_fk_integrations_player_id` (`player_id`),
  KEY `integrations_transaction_97bf3b98` (`round_id`),
  KEY `integrations_transaction_amount_962ae14b_uniq` (`amount`),
  KEY `integrations_transaction_type_8116b991_uniq` (`type`),
  KEY `integrations_transaction_status_d6adf79f_uniq` (`status`),
  CONSTRAINT `integrations_transact_round_id_c1fc6f01_fk_integrations_round_id` FOREIGN KEY (`round_id`) REFERENCES `integrations_round` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=176861045 DEFAULT CHARSET=latin1

ее переделал в

CREATE TABLE `integrations_transaction_part` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` smallint(5) unsigned NOT NULL,
  `status` smallint(5) unsigned NOT NULL,
  `uid` varchar(50) NOT NULL,
  `amount` int(10) unsigned NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `player_id` int(11) NOT NULL,
  `round_id` int(11),
  `currency` varchar(4),
  PRIMARY KEY (`id`,`created_at`),
  UNIQUE KEY `uid` (`uid`,`created_at`),
  KEY `key_player_id` (`player_id`),
  KEY `key_round_id` (`round_id`),
  KEY `key_amount` (`amount`),
  KEY `key_type` (`type`),
  KEY `key_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY HASH (MONTH(created_at)) partitions 12;

Перенес те же самые данные

mysql> SELECT
    ->   TABLE_NAME AS `Table`,
    ->   ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
    -> FROM
    ->   information_schema.TABLES
    -> WHERE
    ->   TABLE_SCHEMA = "slotomotive"
    ->   AND TABLE_NAME like 'integrations_transaction%'
    -> ORDER BY
    ->   (DATA_LENGTH + INDEX_LENGTH)
    -> DESC;
+-------------------------------+-----------+
| Table                         | Size (MB) |
+-------------------------------+-----------+
| integrations_transaction_part |      6043 |
| integrations_transaction      |      5513 |
+-------------------------------+-----------+
2 rows in set (0.00 sec)
mysql> SELECT PARTITION_ORDINAL_POSITION, PARTITION_NAME, TABLE_ROWS, PARTITION_METHOD
    ->        FROM information_schema.PARTITIONS
    ->        WHERE TABLE_SCHEMA = 'slotomotive' AND TABLE_NAME = 'integrations_transaction_part';
+----------------------------+----------------+------------+------------------+
| PARTITION_ORDINAL_POSITION | PARTITION_NAME | TABLE_ROWS | PARTITION_METHOD |
+----------------------------+----------------+------------+------------------+
|                          1 | p0             |          0 | HASH             |
|                          2 | p1             |          0 | HASH             |
|                          3 | p2             |          0 | HASH             |
|                          4 | p3             |          0 | HASH             |
|                          5 | p4             |          0 | HASH             |
|                          6 | p5             |          0 | HASH             |
|                          7 | p6             |          0 | HASH             |
|                          8 | p7             |    3387644 | HASH             |
|                          9 | p8             |    4497268 | HASH             |
|                         10 | p9             |    4317469 | HASH             |
|                         11 | p10            |    7587586 | HASH             |
|                         12 | p11            |          0 | HASH             |
+----------------------------+----------------+------------+------------------+
12 rows in set (0.00 sec)

И собственно результат.

Выборка из оригинальной

mysql> select count(id) from integrations_transaction where created_at >= '2017-08-01' and created_at <= '2017-08-31';
...
1 row in set (29.71 sec)

Выборка из таблицы с партициями

mysql> select count(id) from integrations_transaction_part where created_at >= '2017-08-01' and created_at <= '2017-08-31';
...
1 row in set (11.64 sec)

И правильная выборка, из конкретной партиции, по которой все и сортировалось

mysql> select count(id) from integrations_transaction_part partition (p8) where created_at >= '2017-08-01' and created_at <= '2017-08-31';
...
1 row in set (2.37 sec)

В 10 раз быстрее!
29.71 sec - 11.64 sec - 2.37 sec

Ну и ротация, то бишь удаление того что ненужно

mysql> delete from integrations_transaction_part where created_at >= '2017-10-01' and created_at <= '2017-10-31';
Query OK, 100000 rows affected (3.70 sec)
mysql> delete from integrations_transaction_part PARTITION (p10) where created_at >= '2017-10-01' and created_at <= '2017-10-31';
Query OK, 100000 rows affected (2.31 sec)
mysql> ALTER TABLE integrations_transaction_part TRUNCATE PARTITION p10;
Query OK, 0 rows affected (0.06 sec)

Без комментариев wink