MySQL:将大表拆分成小表的最快方法

Question

我有一个很大的表，里面有将近 3 亿条记录。由于 select 查询对我来说太慢了，我想将它拆分成大约 800 个小表。

数据集如下所示:

XXXXXX column2 column3 column4 ...
XXXXXX column2 column3 column4 ...
XXXXXX column2 column3 column4 ...
YYYYYY column2 column3 column4 ...
YYYYYY column2 column3 column4 ...

我想根据第一列的值拆分表(例如，将带有 XXXXXX 的记录拆分为表 XXXXXX)，最快的方法是什么？

注意:我已经为它添加了 10 个分区，但它并没有很好地加速它。

Answer 1

分区在两种情况下作为性能策略起作用:

1. 该表的主要查询最终执行表或索引扫描，并且在具有足够资源和适当配置的系统上执行高级别并行性。因此，如果所有分区都在同一个物理驱动器上，那对您来说意义不大，因为您与最初一样受到 I/O 限制。但是，如果您使用的是 16 核系统，每个分区都位于物理上不同的磁盘上？分区可能会对系统性能产生惊人的改进。

2. 分区规则使用的索引经常用于针对该表的最普遍查询。如果您要通过该路线获得性能，则应该根据通常用于过滤或约束结果集的索引值进行分区。最常见的候选者是交易日期，因为报告通常是按日历日期范围进行的。然后，查询优化器可以使用分区规则将操作限制到单个(较小的)分区，或者并行运行两个或多个分区扫描(受上述相同限制)。

我假设想要拆分此表的主要原因是为了性能。但是800个分区？如果性能改进是您所追求的，那可能是错误的方法。企业数据库在缓存内存中保留尽可能多的顶级表索引以获得良好的性能。在一个五级 b 树中，对于一个适度使用的表，很可能前三级在第一次访问后总是保留在缓存中(这是一个具有整数主键的 300M 行表的可能配置) .通过将表拆分为 800 个部分，这意味着将有 800 个数据结构试图保持缓存(除了表数据本身)。很有可能，如果您的访问或多或少按主键均匀分布，那么在一个分区上搜索最终会将其他分区推出缓存，最终损害整体性能。

不过，如果您决定这样做，将表分区为 N 个部分的最简单方法是根据主键(primary_key % 800，在你的情况下)。较新版本的 MySQL 也支持散列分区，使得分区成任意数量的集合相当简单:

PARTITION BY HASH(some_column_value) PARTITIONS number_of_partitions

如果您想将数据放入 800 个实际表中，则必须使用编辑器魔术，或使用脚本语言，并在 SQL 中执行:

CREATE TABLE table1 LIKE MasterTable
CREATE TABLE table2 LIKE MasterTable
CREATE TABLE table3 LIKE MasterTable
..
INSERT INTO table1 SELECT * FROM MasterTable WHERE id MOD 800 = 0
INSERT INTO table2 SELECT * FROM MasterTable WHERE id MOD 800 = 1
INSERT INTO table3 SELECT * FROM MasterTable WHERE id MOD 800 = 2

您可以使用动态 SQL 在您最喜欢的编程语言中循环执行此操作:这可能是最容易呈现的。