几年前,我使用 Rick James 的分区和索引指南创建了这个,但现在再次阅读该教程后,我不太确定我是否理解正确,以及下表是否是最佳的。(指南参考)
我认为其中一个常规索引是多余的,不确定我是否应该忽略分区键列 (datetime) 或更改其中一个索引以使分区键列 (datetime) 位于最后。在分区内选择特定日期时间范围时,将其作为索引的一部分是否有助于提高性能?
另外,是否有主键(deviceService,datetime)并在自动增量(id)上有一个索引会更好吗?
下面的基于时间序列的表格每个月分区有大约 8000 多万行。它几乎总是针对特定的日期时间范围和 deviceServiceId 的子集进行查询。
分区纯粹是为了存档和表维护,而不是为了性能。
CREATE TABLE `serviceResultLinkState`
(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`deviceServiceId` int(10) unsigned NOT NULL,
`dateTime` datetime NOT NULL DEFAULT current_timestamp(),
`priority` tinyint(1) unsigned NOT NULL,
`alias` varchar(20) NOT NULL,
`active` tinyint(3) unsigned DEFAULT NULL,
`stable` tinyint(3) unsigned DEFAULT NULL,
`attempts` int(5) unsigned DEFAULT NULL,
`retries` int(5) unsigned DEFAULT NULL,
`resultState` tinyint(3) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`,`dateTime`),
KEY `dt_ds_idx` (`deviceServiceId`),
KEY `datetime_idx` (`dateTime`,`deviceServiceId`)
) ENGINE=InnoDB AUTO_INCREMENT=24814201874 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE COLUMNS(`dateTime`)
(PARTITION `p_202405` VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
PARTITION `p_202406` VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
PARTITION `p_202407` VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
PARTITION `p_202408` VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
PARTITION `p_202409` VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
PARTITION `p_202410` VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
PARTITION `p_202411` VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
PARTITION `p_max` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);