#30 分库分表分区

CREATE TABLE `device_status_history2` (
  `id` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT 'UUID',
  `name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '设备名称',
  `project_id` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '项目ID',
  `tj_id` int DEFAULT NULL COMMENT '同济数据库的project_id',
  `device_type` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '设备种类 CH主机 CP冷却泵 CT冷却塔 PP一次泵 SP二次泵',
  `device_id` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '设备ID',
  `running_status` tinyint(1) DEFAULT NULL COMMENT '运行状态 是否在运行',
  `evap_wtr_out_tp` float DEFAULT NULL COMMENT '蒸发出水温度',
  `evap_wtr_in_tp` float DEFAULT NULL COMMENT '蒸发进水温度',
  `evap_pressure` float DEFAULT NULL COMMENT '蒸发器饱和压力',
  `cond_wtr_out_tp` float DEFAULT NULL COMMENT '冷凝器出水温度',
  `cond_wtr_in_tp` float DEFAULT NULL COMMENT '冷凝器进水温度',
  `cond_pressure` float DEFAULT NULL COMMENT '冷凝器饱和压力',
  `hr_wtr_in_tp` float DEFAULT NULL COMMENT '热回水进水温度',
  `hr_wtr_out_tp` float DEFAULT NULL COMMENT '热回收出水温度',
  `hr_flow_rate` float DEFAULT NULL COMMENT '热回收水流量',
  `evap_flow_rate` float DEFAULT NULL COMMENT '蒸发器流量',
  `cond_flow_rate` float DEFAULT NULL COMMENT '冷凝器流量',
  `comp_status` float DEFAULT NULL COMMENT '压缩机运行状态',
  `comp_freq` float DEFAULT NULL COMMENT '压缩机变频器频率',
  `unit_power` float DEFAULT NULL COMMENT '机组功率',
  `unit_load_pct` float DEFAULT NULL COMMENT '机组功率百分比',
  `discharge_temp` float DEFAULT NULL COMMENT '排气温度',
  `frequency` float DEFAULT NULL COMMENT '频率 只有冷却塔和冷却泵有',
  `power` float DEFAULT NULL COMMENT '功率 只有冷却塔和冷却泵有',
  `data_type` int DEFAULT NULL COMMENT '数据种类 0：actual 1:prediction 2:optimization ',
  `opt_type` int DEFAULT NULL COMMENT '算法优化数据种类 1-能效最优；2-不切换设备；3-最少运行设备',
  `date_time` timestamp NULL DEFAULT NULL COMMENT '日期时间，整点，与采样时间同步',
  `version` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '版本',
  `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_modify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_del` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `evap_temp` float DEFAULT NULL COMMENT 'ch需要的字段',
  `cond_temp` float DEFAULT NULL COMMENT 'ch需要的字段',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;

RDS MySQL对表分区有以下限制：

只能对数据表的整型列进行分区，或者数据列可以通过分区函数转换成整型列。

单表最大分区数目（包含子分区）不能超过8192。

如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键内。

不支持外键。

不支持全文索引（FULL TEXT）。

数据库类型:MySQL 5.6； CPU:8核； 最大连接数:8000； 最大IOPS:12000；

创建新的分区表，与原表保持同样的结构，将创建时间类型改为datetime； 分批写入历史数据，每次1000万，大约7分钟时间；（查出最近14天会更新的最小ID，这批数据停机插入！） 停机插入最后的14天数据； Rename表，重启服务；

# project_id 改为int

SET sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

ALTER TABLE `device_status_history2`
MODIFY COLUMN `project_id` int(11) NOT NULL COMMENT '项目ID' AFTER `name`;


SELECT DISTINCT
    YEAR(device_status_history2.create_date) AS year
FROM 
    device_status_history2;


# 设置联合主键
ALTER TABLE device_status_history2 DROP PRIMARY KEY, ADD PRIMARY KEY (id, create_date, project_id);


# 分区

ALTER TABLE device_status_history2
PARTITION BY RANGE (UNIX_TIMESTAMP(create_date)) 
SUBPARTITION BY HASH (project_id)
SUBPARTITIONS 4 (
    PARTITION part2404 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01 00:00:00')),
    PARTITION part2405 VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-01 00:00:00')),
    PARTITION part2406 VALUES LESS THAN (UNIX_TIMESTAMP('2024-06-01 00:00:00')),
    PARTITION part2407 VALUES LESS THAN (UNIX_TIMESTAMP('2024-07-01 00:00:00')),
    PARTITION part2408 VALUES LESS THAN (UNIX_TIMESTAMP('2024-08-01 00:00:00')),
    PARTITION part2409 VALUES LESS THAN (UNIX_TIMESTAMP('2024-09-01 00:00:00')),
    PARTITION part2410 VALUES LESS THAN (UNIX_TIMESTAMP('2024-10-01 00:00:00')),
    PARTITION part2411 VALUES LESS THAN (UNIX_TIMESTAMP('2024-11-01 00:00:00')),
    PARTITION part2412 VALUES LESS THAN (UNIX_TIMESTAMP('2024-12-01 00:00:00')),
    PARTITION part2501 VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01 00:00:00')),
    PARTITION part2502 VALUES LESS THAN (UNIX_TIMESTAMP('2025-02-01 00:00:00')),
    PARTITION part2503 VALUES LESS THAN (UNIX_TIMESTAMP('2025-03-01 00:00:00')),
    PARTITION part2504 VALUES LESS THAN (UNIX_TIMESTAMP('2025-04-01 00:00:00')),
    PARTITION part2505 VALUES LESS THAN (UNIX_TIMESTAMP('2025-05-01 00:00:00')),
    PARTITION part2506 VALUES LESS THAN (UNIX_TIMESTAMP('2025-06-01 00:00:00')),
    PARTITION part2507 VALUES LESS THAN (UNIX_TIMESTAMP('2025-07-01 00:00:00')),
    PARTITION part2508 VALUES LESS THAN (UNIX_TIMESTAMP('2025-08-01 00:00:00')),
    PARTITION part2509 VALUES LESS THAN (UNIX_TIMESTAMP('2025-09-01 00:00:00')),
    PARTITION part2510 VALUES LESS THAN (UNIX_TIMESTAMP('2025-10-01 00:00:00')),
    PARTITION part2511 VALUES LESS THAN (UNIX_TIMESTAMP('2025-11-01 00:00:00')),
    PARTITION part2512 VALUES LESS THAN (UNIX_TIMESTAMP('2025-12-01 00:00:00')),
    PARTITION part2601 VALUES LESS THAN (UNIX_TIMESTAMP('2026-01-01 00:00:00')),
    PARTITION part2602 VALUES LESS THAN (UNIX_TIMESTAMP('2026-02-01 00:00:00')),
    PARTITION part2603 VALUES LESS THAN (UNIX_TIMESTAMP('2026-03-01 00:00:00')),
    PARTITION part2604 VALUES LESS THAN (UNIX_TIMESTAMP('2026-04-01 00:00:00')),
    PARTITION part2605 VALUES LESS THAN (UNIX_TIMESTAMP('2026-05-01 00:00:00')),
    PARTITION part2606 VALUES LESS THAN (UNIX_TIMESTAMP('2026-06-01 00:00:00')),
    PARTITION part2607 VALUES LESS THAN (UNIX_TIMESTAMP('2026-07-01 00:00:00')),
    PARTITION part2608 VALUES LESS THAN (UNIX_TIMESTAMP('2026-08-01 00:00:00')),
    PARTITION part2609 VALUES LESS THAN (UNIX_TIMESTAMP('2026-09-01 00:00:00')),
    PARTITION part2610 VALUES LESS THAN (UNIX_TIMESTAMP('2026-10-01 00:00:00')),
    PARTITION part2611 VALUES LESS THAN (UNIX_TIMESTAMP('2026-11-01 00:00:00')),
    PARTITION part2612 VALUES LESS THAN (UNIX_TIMESTAMP('2026-12-01 00:00:00')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

project_system_info_history 表：

CREATE TABLE `project_system_info_history` (
  `id` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT 'UUID',
  `name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '项目名称',
  `project_id` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '能管中心项目ID',
  `wb_tp` float DEFAULT NULL COMMENT '湿球温度',
  `supply_ch_wtr_tp` float DEFAULT NULL COMMENT '冷冻水供水温度',
  `return_ch_wtr_tp` float DEFAULT NULL COMMENT '冷冻水回水温度',
  `leave_cl_wtr_tp` float DEFAULT NULL COMMENT '冷却水出水温度',
  `enter_cl_wtr_tp` float DEFAULT NULL COMMENT '冷却水进水温度',
  `cl_wtr_flow_rate` float DEFAULT NULL COMMENT '冷却水流量',
  `ch_wtr_flow_rate` float DEFAULT NULL COMMENT '冷冻水流量',
  `total_power_chiller` float DEFAULT NULL COMMENT '主机总功率',
  `total_power_p_pump` float DEFAULT NULL COMMENT '冷冻泵总功率',
  `total_power_c_pump` float DEFAULT NULL COMMENT '冷却泵总功率',
  `total_power_c_tower` float DEFAULT NULL COMMENT '冷却塔总功率',
  `total_power_plant` float DEFAULT NULL COMMENT '机房总功率',
  `plant_cop` float DEFAULT NULL COMMENT '机房瞬时COP',
  `data_type` int DEFAULT NULL COMMENT '数据种类 0：actual 1:prediction 2:optimization ',
  `opt_type` int DEFAULT NULL COMMENT '算法优化数据种类 1-能效最优；2-不切换设备；3-最少运行设备',
  `version` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '版本',
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `lastModify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_del` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `c_tower_out_tp` float DEFAULT NULL COMMENT '冷却水出塔温度（旁通混水前）',
  `return_hr_wtr_tp` float DEFAULT NULL COMMENT '热回收回水温度',
  `supply_hr_wtr_tp` float DEFAULT NULL COMMENT '热回收供水温度',
  `supply_ch_wtr_p` float DEFAULT NULL COMMENT '冷冻水供水压力',
  `return_ch_wtr_p` float DEFAULT NULL COMMENT '冷冻水回水压力',
  `leave_cl_wtr_p` float DEFAULT NULL COMMENT '冷却水供水压力',
  `enter_cl_wtr_p` float DEFAULT NULL COMMENT '冷却水回水压力',
  `ch_wtr_dp` float DEFAULT NULL COMMENT '冷冻水供回水压差',
  `cl_wtr_dp` float DEFAULT NULL COMMENT '冷却水供回水压差',
  `hr_wtr_flow_rate` float DEFAULT NULL COMMENT '热回收总流量',
  `date_time` timestamp NULL DEFAULT NULL COMMENT '最新时间',
  `db_tp` float DEFAULT NULL COMMENT '新增字段db_tp',
  `rh` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '新增字段rh',
  `CH_ids` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '具体开机的id list，由算法服务回传',
  `CT_ids` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '新增字段CT_ids',
  `CP_ids` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '新增字段CP_ids',
  `PP_ids` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '新增字段PP_ids',
  `SP_ids` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '新增字段SP_ids',
  `plant_eer` float DEFAULT NULL COMMENT '机房EER',
  `CT` int DEFAULT NULL COMMENT '冷却塔开启台数，由算法服务回传',
  `CP` int DEFAULT NULL COMMENT '冷却泵开启台数，由算法服务回传',
  `PP` int DEFAULT NULL COMMENT '一次泵开启台数，由算法服务回传',
  `SP` int DEFAULT NULL COMMENT '二次泵开启台数，由算法服务回传',
  `CH` int DEFAULT NULL COMMENT '主机开启台数，由算法服务回传',
  `ave_freq_c_pump` float DEFAULT NULL COMMENT '冷却泵平均运行频率',
  `ave_freq_c_tower` float DEFAULT NULL COMMENT '冷却塔平均运行频率',
  `supply_wtr_tp_spt` float DEFAULT NULL COMMENT '出水温度设定值',
  `cooling_capacity` float DEFAULT NULL COMMENT '机房瞬时制冷量',
  `saving` float DEFAULT NULL COMMENT '节能潜力， 仅optimization有',
  `tj_id` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '同济能管中心项目Id',
  `ave_freq_p_pump` float DEFAULT NULL COMMENT '冷冻泵平均运行频率',
  `cl_wtr_dtp` float DEFAULT NULL,
  `ct_appr_tp` float DEFAULT NULL,
  `cooling_cop` float DEFAULT NULL,
  `total_power_s_pump` float DEFAULT NULL COMMENT '二次泵功率',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_project_id` (`project_id`) USING BTREE,
  KEY `idx_date_time` (`date_time`) USING BTREE,
  KEY `idx_date_project` (`project_id`,`date_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;


# project_id 改为int

SET sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

ALTER TABLE `project_system_info_history`
MODIFY COLUMN `project_id` int(11) NOT NULL COMMENT '项目ID' AFTER `name`;


SELECT DISTINCT
    YEAR(project_system_info_history.createDate) AS year
FROM 
    project_system_info_history;


# 设置联合主键
ALTER TABLE project_system_info_history DROP PRIMARY KEY, ADD PRIMARY KEY (id, createDate, project_id);


# 分区

ALTER TABLE project_system_info_history
PARTITION BY RANGE (UNIX_TIMESTAMP(createDate)) 
SUBPARTITION BY HASH (project_id)
SUBPARTITIONS 4 (
    PARTITION part22404 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01 00:00:00')),
    PARTITION part22405 VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-01 00:00:00')),
    PARTITION part22406 VALUES LESS THAN (UNIX_TIMESTAMP('2024-06-01 00:00:00')),
    PARTITION part22407 VALUES LESS THAN (UNIX_TIMESTAMP('2024-07-01 00:00:00')),
    PARTITION part22408 VALUES LESS THAN (UNIX_TIMESTAMP('2024-08-01 00:00:00')),
    PARTITION part22409 VALUES LESS THAN (UNIX_TIMESTAMP('2024-09-01 00:00:00')),
    PARTITION part22410 VALUES LESS THAN (UNIX_TIMESTAMP('2024-10-01 00:00:00')),
    PARTITION part22411 VALUES LESS THAN (UNIX_TIMESTAMP('2024-11-01 00:00:00')),
    PARTITION part22412 VALUES LESS THAN (UNIX_TIMESTAMP('2024-12-01 00:00:00')),
    PARTITION part22501 VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01 00:00:00')),
    PARTITION part22502 VALUES LESS THAN (UNIX_TIMESTAMP('2025-02-01 00:00:00')),
    PARTITION part22503 VALUES LESS THAN (UNIX_TIMESTAMP('2025-03-01 00:00:00')),
    PARTITION part22504 VALUES LESS THAN (UNIX_TIMESTAMP('2025-04-01 00:00:00')),
    PARTITION part22505 VALUES LESS THAN (UNIX_TIMESTAMP('2025-05-01 00:00:00')),
    PARTITION part22506 VALUES LESS THAN (UNIX_TIMESTAMP('2025-06-01 00:00:00')),
    PARTITION part22507 VALUES LESS THAN (UNIX_TIMESTAMP('2025-07-01 00:00:00')),
    PARTITION part22508 VALUES LESS THAN (UNIX_TIMESTAMP('2025-08-01 00:00:00')),
    PARTITION part22509 VALUES LESS THAN (UNIX_TIMESTAMP('2025-09-01 00:00:00')),
    PARTITION part22510 VALUES LESS THAN (UNIX_TIMESTAMP('2025-10-01 00:00:00')),
    PARTITION part22511 VALUES LESS THAN (UNIX_TIMESTAMP('2025-11-01 00:00:00')),
    PARTITION part22512 VALUES LESS THAN (UNIX_TIMESTAMP('2025-12-01 00:00:00')),
    PARTITION part22601 VALUES LESS THAN (UNIX_TIMESTAMP('2026-01-01 00:00:00')),
    PARTITION part22602 VALUES LESS THAN (UNIX_TIMESTAMP('2026-02-01 00:00:00')),
    PARTITION part22603 VALUES LESS THAN (UNIX_TIMESTAMP('2026-03-01 00:00:00')),
    PARTITION part22604 VALUES LESS THAN (UNIX_TIMESTAMP('2026-04-01 00:00:00')),
    PARTITION part22605 VALUES LESS THAN (UNIX_TIMESTAMP('2026-05-01 00:00:00')),
    PARTITION part22606 VALUES LESS THAN (UNIX_TIMESTAMP('2026-06-01 00:00:00')),
    PARTITION part22607 VALUES LESS THAN (UNIX_TIMESTAMP('2026-07-01 00:00:00')),
    PARTITION part22608 VALUES LESS THAN (UNIX_TIMESTAMP('2026-08-01 00:00:00')),
    PARTITION part22609 VALUES LESS THAN (UNIX_TIMESTAMP('2026-09-01 00:00:00')),
    PARTITION part22610 VALUES LESS THAN (UNIX_TIMESTAMP('2026-10-01 00:00:00')),
    PARTITION part22611 VALUES LESS THAN (UNIX_TIMESTAMP('2026-11-01 00:00:00')),
    PARTITION part22612 VALUES LESS THAN (UNIX_TIMESTAMP('2026-12-01 00:00:00')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

DROP TABLE device_status_history2;