我有一个表,其中包含约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
上表的索引是多余的,因为那已经是主键的最左列。