分区mysql表按列时间戳

Question

我正在尝试划分我的表MySQL innoDB。现在，location表中大约有200万行(并且一直在增长)历史数据行。我必须按年顺序删除旧的数据集，我使用MySQL 5.7.22社区服务器。

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实际上是毫秒，是la吗？如果没有，则决定如何处理* 1000。

SUBPARTITIONs是无用的，不要为它们操心。如果您确实希望按月或季度进行分区，那么只需在PARTITION级别进行分区即可。

建议:不超过50个分区。

你有多少个“司机”？我怀疑你没有数万亿美元。所以，不要盲目地将BIGINT用于ids。每个字节需要8个字节。例如，SMALLINT UNSIGNED只需要2个字节，并允许64K驱动程序(等等)。

如果X和Y是纬度和经度，那么可以更清楚地将它们命名为经度和纬度。http://mysql.rjweb.org/doc.php/latlng#representation_choices是要使用的数据类型，而不是8字节的DOUBLE，这取决于您的分辨率(和需要)。对于车辆来说，4字节的FLOATs可能足够好。

这个表有几个多余的索引；抛出它们。另外，请注意，当您有INDEX(a,b,c)时，也有INDEX(a,b)是多余的。

还请参阅我关于分区的http://mysql.rjweb.org/doc.php/partitionmaint，特别是与时间序列相关的内容，如您的。

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

另外一点:在2019年年初之前不要创建p2019。如果没有及时添加分区，那么就有pmax了。我在讨论中提到的REORGANIZE PARTITION介绍了如何从这样的傻瓜中恢复过来。