主页 / dba / 问题 / 249456
Accepted
Chloe
Asked: 2019-09-24 20:46:53 +0800 CST

将一个大表拆分为 12 个滚动月度表并将它们用于报告或保留大表并删除超过 1 年的行是否更快？

  • 772

我的同事想将一个 158M 行的大型统计表拆分为 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.5m。当总行数相同时，为什么许多较小的表比一个大表花费的时间要短得多？

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
mysql database-design

1 个回答

  1. Best Answer

    不要拆分表。请改用 Range Partitionig。学习MySQL 8.0 参考手册/分区。使用MySQL 8.0 参考手册/.../ALTER TABLE 分区操作。请记住，最好提前为未来的时期创建分区（并且不要忘记创建LESS THAN MAXVALUE分区）。创建新分区并同时将现有数据移动到它们可能会更昂贵。

    不要永久删除数据。将其移动到单独的存档表中。如果您没有足够的磁盘空间 - 备份此类存档表，检查其有效性，只有在成功时才删除该表。如有必要（一定会的！），您可以恢复并使用这些数据。

    • 2

相关问题