将一个大表拆分成12个滚动月表，并将其用于报表，还是保留大于1年的大表和删除行，会更快吗？

Question

我的同事想把一个大的1.58m行统计表分成stats_jan，stats_feb，.并使用UNION从它们中选择报表。这是一种标准做法吗?它是否比仅使用已就位的大型表并删除超过一年的行更快？这张表是许多小行。

mysql> describe stats;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| id             | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| badge_id       | bigint(20) unsigned | NO   | MUL | NULL    |                |
| hit_date       | datetime            | YES  | MUL | NULL    |                |
| hit_type       | tinyint(4)          | YES  |     | NULL    |                |
| source_id      | bigint(20) unsigned | YES  | MUL | NULL    |                |
| fingerprint_id | bigint(20) unsigned | YES  |     | NULL    |                |
+----------------+---------------------+------+-----+---------+----------------+

我手动将表拆分，并将行复制到适当的月份表中，并创建了一个巨大的UNION查询。大型UNION查询采用14s，而单个表查询则为4.50万。为什么许多较小的表所用的时间要比一个大表要短得多，因为它的行总数是相同的？

create table stats_jan (...);
create table stats_feb (...);
...
create index stats_jan_hit_date_idx on stats_jan (hit_date);
...
insert into stats_jan select * from stats where hit_date >= '2019-01-01' and hit_date < '2019-02-01';
...
delete from stats where hit_date < '2018-09-01';
...

月表有170万行到3500万行。

select host as `key`, count(*) as value from stats join sources on source_id = sources.id where hit_date >= '2019-08-21 19:43:19' and sources.host != 'NONE' group by source_id order by value desc limit 10;
4 min 30.39 sec

flush tables;
reset query cache;

select host as `key`, count(*) as value from stats_jan join sources on source_id = sources.id where hit_date >= '2019-08-21 19:43:19' and sources.host != 'NONE' group by source_id
UNION
...
order by value desc limit 10;
14.16 sec

Answer 1

不要把桌子分开。使用范围分区代替。研究MySQL 8.0参考手册/分区。使用MySQL 8.0参考手册/./ ALTER分区操作。请记住，最好提前为以后的时间段创建分区(并且不要忘记创建LESS THAN MAXVALUE分区)。创建新分区并同时将现有数据移动到它们可能会更昂贵。

不要永久删除数据。将其移动到单独的归档表中。如果您没有足够的磁盘空间--对这样一个存档表进行备份，检查它的有效性，并且只有在成功的情况下才删除该表。如果有必要(请确保！)，您可以恢复并使用这些数据。