mysql - 按列时间戳对 mysql 表进行分区

Question

我正在尝试对我的 MySQL innoDB 表进行分区。目前，位置表中大约有 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
);

Error Code: 1697. VALUES value for partition 'p2018' must have type 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是纬度和经度，这样命名可能会更清楚。 Here是使用什么数据类型来代替 8 字节 DOUBLE，具体取决于您拥有(和需要)的分辨率。 4 字节 FLOAT 对于车辆来说可能已经足够了。

该表有多个冗余索引；把它们扔掉。另请注意，当您有 INDEX(a,b,c) 时，再有 INDEX(a,b) 是多余的。

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

嗯...我想知道 SPEED 的 63 位精度是否能让您记录它们以光速行驶时的情况？

另一点:直到 2019 年开始之前，才创建 p2019。您有 pmax，以防您因疏忽而未能及时添​​加该分区。我的讨论中提到的REORGANIZE PARTITION涵盖了如何从这样的错误中恢复。