MySQL 分区Partition的使用_msyql 分区查询 partition

使用Partition可以大幅提高查询速度，因为会按照不同的分区规则，生成不同的分区文件，相当于分库分表，但是在使用上又没有区别。

1. 按时间分区时，时间要和ID参共同生成主键索引。
2. 许要找to_days函数分区，还有year也是可以的，其他不行。如果是其他在explain时，看到会不走分区。

CREATE TABLE Test (
  ID int NOT NULL AUTO_INCREMENT,
  UploadDatetime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (ID, UploadDatetime)
)
ENGINE = INNODB,
AUTO_INCREMENT = 3,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci
PARTITION BY RANGE (to_days(UploadDatetime)) 
(
   PARTITION p2023_h1 VALUES LESS THAN (to_days('2023-07-01')),
   PARTITION p2023_h2 VALUES LESS THAN (to_days('2024-01-01')),
   PARTITION p2024_h1 VALUES LESS THAN (to_days('2024-07-01')),
   PARTITION p2024_h2 VALUES LESS THAN (to_days('2025-01-01')),
   PARTITION p2025_h1 VALUES LESS THAN (to_days('2025-07-01')),
   PARTITION p2025_h2 VALUES LESS THAN (to_days('2026-01-01')),
   PARTITION p2026_h1 VALUES LESS THAN (to_days('2026-07-01')),
   PARTITION p2026_h2 VALUES LESS THAN (to_days('2027-01-01')),
   PARTITION p2027_h1 VALUES LESS THAN (to_days('2027-07-01')),
   PARTITION p2027_h2 VALUES LESS THAN (to_days('2028-01-01')),
   PARTITION p2028_h1 VALUES LESS THAN (to_days('2028-07-01')),
   PARTITION p2028_h2 VALUES LESS THAN (to_days('2029-01-01')),
   PARTITION p2029_h1 VALUES LESS THAN (to_days('2029-07-01')),
   PARTITION p2029_h2 VALUES LESS THAN (to_days('2030-01-01')),
   PARTITION p2030_h1 VALUES LESS THAN (to_days('2030-07-01')),
   PARTITION p2030_h2 VALUES LESS THAN (to_days('2031-01-01')),
   PARTITION p2031_h1 VALUES LESS THAN (to_days('2031-07-01')),
   PARTITION p2031_h2 VALUES LESS THAN (to_days('2032-01-01')),
   PARTITION p2032_h1 VALUES LESS THAN (to_days('2032-07-01')),
   PARTITION p2032_h2 VALUES LESS THAN MAXVALUE
);

ALTER TABLE TStatusHistoryData
ADD INDEX IDX_TStatusHistoryData (UploadDatetime, Name);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35