mysql 9月份200万条数据，查询慢怎么解决呢

Z时代
2024-01-10
分类：技术分享

CREATE TABLE report_device_working (
id bigint(20) NOT NULL AUTO_INCREMENT,
device_id varchar(128) COLLATE utf8_unicode_ci NOT NULL COMMENT '设备ID',
deivce_type_id varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '设备类型ID',
factory_id bigint(20) NOT NULL COMMENT '工厂ID',
workshop_id bigint(20) NOT NULL COMMENT '车间ID',
production_id bigint(20) NOT NULL COMMENT '产线ID',
device_status bigint(20) NOT NULL COMMENT '运行状态码 1 离线 2 停机 3 设定 4 空闲 5 运行',
start_time timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '开始时间',
end_time timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '结束时间',
working_duration bigint(20) NOT NULL COMMENT '运行时长（秒）',
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
append_flag char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
details_id varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (id),
KEY index_1 (factory_id,workshop_id,production_id),
KEY rdw_deviceid_index (device_id),
KEY datetime_index (start_time,end_time) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2121472966 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

SELECT

faultRate.factory_id factoryId, f.node_name factoryName, f.parent_node_id AS pid, faultRate.workshop_id workshopId, w1.node_name workshopName, faultRate.production_id productionId, br.cnc_type cncType, prd.node_name productionName, faultRate.device_id deviceName, r.device_alias deviceAlias, round( alarm_duration * 100 / total_duration, 2 ) faultRate, round( utilzion_duration * 100 / total_utilzion_duration, 2

) utilizationRate

FROM

(
SELECT
tmp.id,
tmp.device_id,
tmp.deivce_type_id,
tmp.factory_id,
tmp.workshop_id,
tmp.production_id,
sum(
CASE
WHEN device_status = 3 THEN
TIMESTAMPDIFF(SECOND, start_time, end_time)
ELSE
END
) alarm_duration,
sum(
CASE
WHEN device_status = 5 THEN
TIMESTAMPDIFF(SECOND, start_time, end_time)
ELSE
END
) utilzion_duration,
sum(
CASE
WHEN device_status IN (1, 3, 4, 5) THEN
TIMESTAMPDIFF(SECOND, start_time, end_time)
ELSE
END
) total_duration,
sum(
TIMESTAMPDIFF(SECOND, start_time, end_time)
) total_utilzion_duration
FROM
(
SELECT
id,
device_id,
deivce_type_id,
factory_id,
workshop_id,
production_id,
device_status,
CASE
WHEN start_time < '2020-09-01 00:00:00' THEN
'2020-09-01 00:00:00'
ELSE
start_time
END start_time,
end_time AS end_time,
update_time,
create_time
FROM
report_device_working
WHERE
start_time <= end_time
AND (
start_time BETWEEN '2020-09-01 00:00:00'
AND '2020-09-31 23:59:59'
OR end_time BETWEEN '2020-09-01 00:00:00'
AND '2020-09-31 23:59:59'
)
) tmp
GROUP BY
factory_id,
workshop_id,
production_id,
device_id) faultRate

LEFT JOIN factory_struct f ON faultRate.factory_id = f.node_id
LEFT JOIN factory_struct w1 ON faultRate.workshop_id = w1.node_id
LEFT JOIN factory_struct prd ON faultRate.production_id = prd.node_id
LEFT JOIN device_assign_rel r ON faultRate.device_id = r.device_id
LEFT JOIN base_register br ON faultRate.device_id = br.device_id
WHERE