It’s the table creation statement you posted earlier. You can test it in version 6.5, collect statistics, and see if it meets your requirements.
| history_wti_alarm | CREATE TABLE `history_wti_alarm` (
`id` bigint(20) NOT NULL,
`alarm_id` varchar(64) NOT NULL COMMENT 'unique alarm id',
`start_time` timestamp(3) NOT NULL,
`end_time` timestamp(3) NULL DEFAULT NULL COMMENT 'Last sample timestamp',
PRIMARY KEY (`alarm_id`,`start_time`) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY `uniq_alarm_id` (`alarm_id`,`start_time`),
KEY `idx_start_time` (`start_time`),
KEY `idx_end_time` (`end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (FLOOR(UNIX_TIMESTAMP(`start_time`)))
(PARTITION `p1` VALUES LESS THAN (1640966400),
PARTITION `p2` VALUES LESS THAN (1641744000),
PARTITION `p3` VALUES LESS THAN (1642608000),
PARTITION `p4` VALUES LESS THAN (1643558400),
PARTITION `p5` VALUES LESS THAN (1643644800),
PARTITION `p6` VALUES LESS THAN (1644422400),
PARTITION `p7` VALUES LESS THAN (1645286400),
PARTITION `p8` VALUES LESS THAN (1645977600),
PARTITION `p9` VALUES LESS THAN (1646064000),
PARTITION `p10` VALUES LESS THAN (1646841600),
PARTITION `p11` VALUES LESS THAN (1647705600),
PARTITION `p12` VALUES LESS THAN (1648656000),
PARTITION `p13` VALUES LESS THAN (1648742400),
PARTITION `p14` VALUES LESS THAN (1649520000),
PARTITION `p15` VALUES LESS THAN (1650384000),
PARTITION `p16` VALUES LESS THAN (1651248000),
PARTITION `p17` VALUES LESS THAN (1651334400),
PARTITION `p18` VALUES LESS THAN (1652112000),
PARTITION `p19` VALUES LESS THAN (1652976000),
PARTITION `p20` VALUES LESS THAN (1653926400),
PARTITION `p21` VALUES LESS THAN (1654012800),
PARTITION `p22` VALUES LESS THAN (1654790400),
PARTITION `p23` VALUES LESS THAN (1655654400),
PARTITION `p24` VALUES LESS THAN (1656518400),
PARTITION `p25` VALUES LESS THAN (1656604800),
PARTITION `p26` VALUES LESS THAN (1657382400),
PARTITION `p27` VALUES LESS THAN (1658246400),
PARTITION `p28` VALUES LESS THAN (1659196800),
PARTITION `p29` VALUES LESS THAN (1659283200),
PARTITION `p30` VALUES LESS THAN (1660060800),
PARTITION `p31` VALUES LESS THAN (1660924800),
PARTITION `p32` VALUES LESS THAN (1661875200),
PARTITION `p33` VALUES LESS THAN (1661961600),
PARTITION `p34` VALUES LESS THAN (1662739200),
PARTITION `p35` VALUES LESS THAN (1663603200),
PARTITION `p36` VALUES LESS THAN (1664467200),
PARTITION `p37` VALUES LESS THAN (1664553600),
PARTITION `p38` VALUES LESS THAN (1665331200),
PARTITION `p39` VALUES LESS THAN (1666195200),
PARTITION `p40` VALUES LESS THAN (1667145600),
PARTITION `p41` VALUES LESS THAN (1667232000),
PARTITION `p42` VALUES LESS THAN (1668009600),
PARTITION `p43` VALUES LESS THAN (1668873600),
PARTITION `p44` VALUES LESS THAN (1669737600),
PARTITION `p45` VALUES LESS THAN (1669824000),
PARTITION `p46` VALUES LESS THAN (1670601600),
PARTITION `p47` VALUES LESS THAN (1671465600),
PARTITION `p48` VALUES LESS THAN (1672416000),
PARTITION `p49` VALUES LESS THAN (1672502400),
PARTITION `p50` VALUES LESS THAN (1673280000),
PARTITION `p51` VALUES LESS THAN (1674144000),
PARTITION `p52` VALUES LESS THAN (1675094400),
PARTITION `p53` VALUES LESS THAN (1675180800),
PARTITION `p54` VALUES LESS THAN (1675958400),
PARTITION `p55` VALUES LESS THAN (1676822400),
PARTITION `p56` VALUES LESS THAN (1677513600),
PARTITION `p57` VALUES LESS THAN (1677600000),
PARTITION `p58` VALUES LESS THAN (1678377600),
PARTITION `p59` VALUES LESS THAN (1679241600),
PARTITION `p60` VALUES LESS THAN (1680192000),
PARTITION `p61` VALUES LESS THAN (1680278400),
PARTITION `p62` VALUES LESS THAN (1681056000),
PARTITION `p63` VALUES LESS THAN (1681920000),
PARTITION `p64` VALUES LESS THAN (1682784000),
PARTITION `p65` VALUES LESS THAN (1682870400),
PARTITION `p66` VALUES LESS THAN (1683648000),
PARTITION `p67` VALUES LESS THAN (1684512000),
PARTITION `p68` VALUES LESS THAN (1685462400),
PARTITION `p69` VALUES LESS THAN (1685548800),
PARTITION `p70` VALUES LESS THAN (1686326400),
PARTITION `p71` VALUES LESS THAN (1687190400),
PARTITION `p72` VALUES LESS THAN (1688054400),
PARTITION `p73` VALUES LESS THAN (1688140800),
PARTITION `p74` VALUES LESS THAN (1688918400),
PARTITION `p75` VALUES LESS THAN (1689782400),
PARTITION `p76` VALUES LESS THAN (1690732800),
PARTITION `p77` VALUES LESS THAN (1690819200),
PARTITION `p78` VALUES LESS THAN (1691596800),
PARTITION `p79` VALUES LESS THAN (1692460800),
PARTITION `p80` VALUES LESS THAN (1693411200),
PARTITION `p81` VALUES LESS THAN (1693497600),
PARTITION `p82` VALUES LESS THAN (1694275200),
PARTITION `p83` VALUES LESS THAN (1695139200),
PARTITION `p84` VALUES LESS THAN (1696003200),
PARTITION `p85` VALUES LESS THAN (1696089600),
PARTITION `p86` VALUES LESS THAN (1696867200),
PARTITION `p87` VALUES LESS THAN (1697731200),
PARTITION `p88` VALUES LESS THAN (1698681600),
PARTITION `p90` VALUES LESS THAN (1698768000),
PARTITION `p91` VALUES LESS THAN (1699545600),
PARTITION `p92` VALUES LESS THAN (1700409600),
PARTITION `p93` VALUES LESS THAN (1701273600),
PARTITION `p94` VALUES LESS THAN (1701360000),
PARTITION `p95` VALUES LESS THAN (1702137600),
PARTITION `p96` VALUES LESS THAN (1703001600),
PARTITION `p97` VALUES LESS THAN (1703952000),
PARTITION `p1024` VALUES LESS THAN (MAXVALUE))
MySQL [test]> SELECT start_time FROM history_wti_alarm WHERE start_time>=‘2022-11-10 00:00:00.000’ and alarm_id=‘abc’;
±------------------------+
| start_time |
±------------------------+
| 2022-11-10 00:59:59.996 |
±------------------------+
1 row in set (0.01 sec)
MySQL [test]> explain SELECT start_time FROM history_wti_alarm WHERE start_time>=‘2022-11-10 00:00:00.000’ and alarm_id=‘abc’;
±-------------------------±--------±----------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±-------------------------±--------±----------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------+
| Projection_4 | 1.00 | root | | test.history_wti_alarm.start_time |
| └─IndexReader_8 | 1.00 | root | partition:p43,p44,p45,p46,p47,p48,p49,p50,p51,p52,p53,p54,p55,p56,p57,p58,p59,p60,p61,p62,p63,p64,p65,p66,p67,p68,p69,p70,p71,p72,p73,p74,p75,p76,p77,p78,p79,p80,p81,p82,p83,p84,p85,p86,p87,p88,p90,p91,p92,p93,p94,p95,p96,p97,p1024 | index:IndexRangeScan_7 |
| └─IndexRangeScan_7 | 1.00 | cop[tikv] | table:history_wti_alarm, index:uniq_alarm_id(alarm_id, start_time) | range:[“abc” 2022-11-10 00:00:00.000,“abc” +inf], keep order:false |
±-------------------------±--------±----------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------+
3 rows in set (0.00 sec)
|
username: TI表弟
|
username: 
|
username: 
