(1)搜索引擎由InnoDB更改为MyISAM
ALTER TABLE `table` ENGINE=MyISAM;
(2)建立BTREE索引
ALTER TABLE `table`
ADD INDEX `create_time` (`create_time`) USING BTREE
(3)create_time设置为datetime格式，设为组合主键
ALTER TABLE `data`
MODIFY COLUMN `create_time` datetime NOT NULL AFTER `ID`，add primary key (create_time); ;
(4)采用range分区可以保证分区均匀
ALTER TABLE `table`
partition by range(to_days(create_time))
(
partition P202109 values less than (to_days('2021-09-01')),
partition P202110 values less than (to_days('2021-10-01')),
partition P202111 values less than (to_days('2021-11-01')),
partition P202112 values less than (to_days('2021-12-01')),
partition P202201 values less than (to_days('2022-01-01')),
partition P202202 values less than (to_days('2022-02-01')),
partition P202203 values less than (to_days('2022-03-01')),
partition P202204 values less than (to_days('2022-04-01')),
partition P202205 values less than (to_days('2022-05-01')),
partition P202206 values less than (to_days('2022-06-01')),
partition P202207 values less than (to_days('2022-07-01')),
partition P202208 values less than (to_days('2022-08-01')),
partition P202209 values less than (to_days('2022-09-01')),
partition P202210 values less than (to_days('2022-10-01')),
partition P202211 values less than (to_days('2022-11-01')),
partition P202212 values less than (to_days('2022-12-01')),
partition P202301 values less than (to_days('2023-01-01')),
partition P202302 values less than (to_days('2023-02-01')),
partition P202303 values less than (to_days('2023-03-01')),
partition P202304 values less than (to_days('2023-04-01')),
partition P202305 values less than (to_days('2023-05-01')),
partition P202306 values less than (to_days('2023-06-01')),
partition P202307 values less than (to_days('2023-07-01')),
partition P202308 values less than (to_days('2023-08-01')),
partition P202309 values less than (to_days('2023-09-01')),
partition P202310 values less than (to_days('2023-10-01')),
partition P202311 values less than (to_days('2023-11-01')),
partition P202312 values less than (to_days('2023-12-01')),
partition P202401 values less than (to_days('2024-01-01')),
partition P202402 values less than (to_days('2024-02-01')),
partition P202403 values less than (to_days('2024-03-01')),
partition P202404 values less than (to_days('2024-04-01')),
partition P202405 values less than (to_days('2024-05-01')),
partition P202406 values less than (to_days('2024-06-01')),
partition P202407 values less than (to_days('2024-07-01')),
partition P202408 values less than (to_days('2024-08-01')),
partition P202409 values less than (to_days('2024-09-01')),
partition P202410 values less than (to_days('2024-10-01')),
partition P202411 values less than (to_days('2024-11-01')),
partition P202412 values less than (to_days('2024-12-01')),
partition P202501 values less than (to_days('2025-01-01')),
partition P202502 values less than (to_days('2025-02-01')),
partition P202503 values less than (to_days('2025-03-01')),
partition P202504 values less than (to_days('2025-04-01')),
partition P202505 values less than (to_days('2025-05-01')),
partition P202506 values less than (to_days('2025-06-01')),
partition P202507 values less than (to_days('2025-07-01')),
partition P202508 values less than (to_days('2025-08-01')),
partition P202509 values less than (to_days('2025-09-01')),
partition P202510 values less than (to_days('2025-10-01')),
partition P202511 values less than (to_days('2025-11-01')),
partition P202512 values less than (to_days('2025-12-01')),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
(5)分区情况查询
SELECT * FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA = schema()
AND TABLE_NAME='data';
注意：本文归作者所有，未经作者允许，不得转载
