我有一个表,其中包含约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)。

最初,我认为可以通过在HASH index上设置station来完成此操作,但是显然,这仅适用于MEMORY表。然后我以为我是PARTITION BY KEY (station) PARTITIONS 33333,但似乎分区太多了。

在这种情况下我该怎么办?我无法真正进行实验,因为表太大了,任何修改都需要很长时间。

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

最佳答案

您不必每个站都需要一个分区。 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 |
+------------+----------------+------------+


附注:station上表的索引是多余的,因为那已经是主键的最左列。

10-05 21:02
查看更多