MySQL 分区Partition的使用

崇文馆居士

已于 2023-06-21 16:29:30 修改

阅读量341

点赞数

文章标签： mysql 数据库

于 2022-09-23 11:53:00 首次发布

本文链接：https://blog.csdn.net/qq_34677276/article/details/127008026

版权

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

按时间分区时，时间要和ID参共同生成主键索引。
许要找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);