按列时间戳划分的 Partiton mysql 表

Question

我正在尝试对我的表MySQL innoDB进行分区。现在，位置表中大约有 200 万行(并且一直在增长(历史数据行。我必须逐年删除旧的数据集 我使用 MySQL 5.7.22 Community Server。

CREATE TABLE `geo_data` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`ID_DISP` bigint(20) DEFAULT NULL,
`SYS_TIMESTAMP` datetime DEFAULT NULL,
`DATA_TIMESTAMP` bigint(20) DEFAULT NULL,
`X` double DEFAULT NULL,
`Y` double DEFAULT NULL,
`SPEED` bigint(20) DEFAULT NULL,
`HEADING` bigint(20) DEFAULT NULL,
`ID_DATA_TYPE` bigint(20) DEFAULT NULL,
`PROCESSED` bigint(20) DEFAULT NULL,
`ALTITUDE` bigint(20) DEFAULT NULL,
`ID_UNIT` bigint(20) DEFAULT NULL,
`ID_DRIVER` bigint(20) DEFAULT NULL,
UNIQUE KEY `part_id` (`ID`,`DATA_TIMESTAMP`,`ID_DISP`),
KEY `Index_idDisp_dataTS_type` (`ID_DISP`,`DATA_TIMESTAMP`,`ID_DATA_TYPE`),
KEY `Index_idDisp_dataTS` (`ID_DISP`,`DATA_TIMESTAMP`),
KEY `Index_TS` (`DATA_TIMESTAMP`),
KEY `idx_sysTS_idDisp` (`ID_DISP`,`SYS_TIMESTAMP`),
KEY `idx_clab_geo_data_ID_UNIT_DATA_TIMESTAMP_ID_DATA_TYPE` (`ID_UNIT`,`DATA_TIMESTAMP`,`ID_DATA_TYPE`),
KEY `idx_idUnit_dataTS` (`ID_UNIT`,`DATA_TIMESTAMP`),
KEY `idx_clab_geo_data_ID_DRIVER_DATA_TIMESTAMP_ID_DATA_TYPE` (`ID_DRIVER`,`DATA_TIMESTAMP`,`ID_DATA_TYPE`)
) ENGINE=InnoDB AUTO_INCREMENT=584390 DEFAULT CHARSET=latin1;

我必须按DATA_TIMESTAMP分区(格式时间戳日期 gps(。

ALTER TABLE geo_data
PARTITION BY RANGE (year(from_unixtime(data_timestamp)))
(
PARTITION p2018 VALUES LESS THAN ('2018'),
PARTITION p2019 VALUES LESS THAN ('2019'),
PARTITION pmax VALUES LESS THAN MAXVALUE
);

错误代码：1697。分区"p2018"的值值必须具有 INT 类型。

我该怎么办？

我想稍后按ID_DISP添加一个细分范围。我该怎么办？

提前感谢！

Answer 1

由于data_timestamp实际上是一个BIGINT，所以不允许使用日期函数。 似乎有两个错误，这可能会修复它们：

ALTER TABLE geo_data
PARTITION BY RANGE (data_timestamp)
(
PARTITION p2018 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-01') * 1000),
PARTITION p2019 VALUES LESS THAN (UNIX_TIMESTAMP('2019-01-01') * 1000),
PARTITION pmax VALUES LESS THAN MAXVALUE
);

我假设你的data_timestamp真的是毫秒，就像Java一样？ 如果没有，请决定如何处理* 1000。

SUBPARTITIONs是无用的;不要打扰它们。 如果您真的想按月或季度进行分区，那么只需在PARTITION级别进行分区即可。

建议：分区不要超过 50 个。

你有多少"司机"？ 我怀疑你没有数万亿。 所以，不要盲目地用BIGINT作为 id。 每个需要 8 个字节。 例如，SMALLINT UNSIGNED将仅占用 2 个字节并允许 64K 驱动程序(等(。

如果X和Y是纬度和经度，那么将它们命名可能会更清楚。以下是使用哪种数据类型而不是 8 字节DOUBLE，具体取决于您拥有(和需要(的分辨率。 4 字节FLOATs对于车辆来说可能已经足够了。

该表有几个冗余索引;扔掉它们。 另外，请注意，当您有INDEX(a,b,c)时，也具有INDEX(a,b)是多余的。

另请参阅我关于分区的讨论，特别是与时间序列相关的讨论，例如您的。

嗯......我想知道 63 位精度的SPEED是否会让你在它们以光速运行时记录它们？

另一点：在2019年初之前不要创建p2019。 您必须pmax以防万一您出错并且无法及时添加该分区。 我讨论中提到的REORGANIZE PARTITION涵盖了如何从这种愚蠢中恢复过来。

Answer 2

更新：

似乎您不能在PARTITION BY RANGE查询中使用from_unixtime，因为哈希分区必须基于整数表达式。更多信息请参阅此答案

它期望INT而不是STRING(根据错误消息(，因此请尝试：

ALTER TABLE geo_data
PARTITION BY RANGE (year(from_unixtime(data_timestamp)))
(
PARTITION p2018 VALUES LESS THAN (2018),
PARTITION p2019 VALUES LESS THAN (2019),
PARTITION pmax VALUES LESS THAN MAXVALUE
);

在这里，我将分区值中的年份指定为 int 即 2018/2019，而不是像"2018"/"2019"那样的字符串