mysql - 如何对这么大的MySQL表进行分区？

Question

我有一个包含大约 10 亿行的表，如下所示:

CREATE TABLE `ghcnddata` (
`date`  date NOT NULL ,
`TMIN`  float(6,2) NULL DEFAULT NULL ,
`TMAX`  float(6,2) NULL DEFAULT NULL ,
`PRCP`  float(6,2) NULL DEFAULT NULL ,
`SNOW`  float(6,2) NULL DEFAULT NULL ,
`SNWD`  float(6,2) NULL DEFAULT NULL ,
`station`  varchar(30),
PRIMARY KEY (`station`, `date`),
INDEX `date` (`date`) USING BTREE ,
INDEX `station` (`station`) USING BTREE 
) ENGINE=InnoDB

我运行的所有查询都有一行如下所示:

 WHERE `station` = "ABSUXNNSDIA3"

还有一行如下所示:

 AND `date` BETWEEN "1990-01-01" AND "2010-01-01"

station 字段大约有 30,000 个唯一值，并且没有查询引用超过 1 个车站。理想情况下，我想模拟有 33,333 个不同的表；每个站一个(10 亿/30,000 = 33,333)。

最初，我认为可以通过在 station 上设置 HASH 索引 来实现此目的，但显然这仅适用于 MEMORY 表。然后我想我PARTITION BY KEY (station) PARTITIONS 33333，但看起来这个分区太多了。

遇到这种情况我该怎么办？我无法真正进行实验，因为表太大，任何修改都需要很长时间。

没有主/从、复制、集群或类似的东西。

Answer 1

每个站不一定需要一个分区。 HASH 或 KEY 分区的要点是您定义固定数量的分区，并将多个值映射到该分区。

mysql> alter table ghcnddata partition by key(station) partitions 31;

出于习惯，我为分区数量选择素数，因为如果数据遵循某种模式(例如仅奇数值)，它有助于在分区上更均匀地分布数据。

mysql> insert into ghcnddata (station, date) values ('abc', now());
mysql> insert into ghcnddata (station, date) values ('def', now());
mysql> insert into ghcnddata (station, date) values ('ghi', now());
mysql> insert into ghcnddata (station, date) values ('jkl', now());
mysql> insert into ghcnddata (station, date) values ('mno', now());
mysql> insert into ghcnddata (station, date) values ('qrs', now());
mysql> insert into ghcnddata (station, date) values ('tuv', now());
mysql> insert into ghcnddata (station, date) values ('wxyz', now());

当我使用EXPLAIN PARTITIONS运行查询时，它会告诉我它必须读取哪个分区。

mysql> explain partitions select * from ghcnddata where station='tuv';
+----+-------------+-----------+------------+------+-----------------+---------+---------+-------+------+-------------+
| id | select_type | table     | partitions | type | possible_keys   | key     | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------------+------+-----------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | ghcnddata | p21        | ref  | PRIMARY,station | PRIMARY | 122     | const |    1 | Using where |
+----+-------------+-----------+------------+------+-----------------+---------+---------+-------+------+-------------+

在这种情况下我们可以看到，当我引用电台“tuv”时，仅读取了分区 21。

请注意，分区并不是万能的。仅当您在定义为分区键的同一列中搜索常量值(不是变量或连接条件等)时，它才有助于减少查询工作。

我刚刚插入的行应该大致均匀分布，但不是完美均匀分布。并且不能保证每个分区都有一个 station 值。

mysql> select table_name, partition_name, table_rows 
    from information_schema.partitions where table_name='ghcnddata';

+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| ghcnddata  | p0             |          1 |
| ghcnddata  | p1             |          2 |
| ghcnddata  | p2             |          0 |
| ghcnddata  | p3             |          0 |
| ghcnddata  | p4             |          0 |
| ghcnddata  | p5             |          0 |
| ghcnddata  | p6             |          0 |
| ghcnddata  | p7             |          0 |
| ghcnddata  | p8             |          0 |
| ghcnddata  | p9             |          0 |
| ghcnddata  | p10            |          0 |
| ghcnddata  | p11            |          0 |
| ghcnddata  | p12            |          0 |
| ghcnddata  | p13            |          0 |
| ghcnddata  | p14            |          0 |
| ghcnddata  | p15            |          0 |
| ghcnddata  | p16            |          0 |
| ghcnddata  | p17            |          0 |
| ghcnddata  | p18            |          0 |
| ghcnddata  | p19            |          0 |
| ghcnddata  | p20            |          0 |
| ghcnddata  | p21            |          2 |
| ghcnddata  | p22            |          1 |
| ghcnddata  | p23            |          1 |
| ghcnddata  | p24            |          1 |
| ghcnddata  | p25            |          0 |
| ghcnddata  | p26            |          0 |
| ghcnddata  | p27            |          0 |
| ghcnddata  | p28            |          0 |
| ghcnddata  | p29            |          0 |
| ghcnddata  | p30            |          0 |
+------------+----------------+------------+

P.S.:您的表在 station 上的索引是多余的，因为它已经是您主键的最左边的列。