Тема: 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)
Без комментариев