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

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;

SELECTfaultRate.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

0

END

) alarm_duration,

sum(

CASE

WHEN device_status = 5 THEN

TIMESTAMPDIFF(SECOND, start_time, end_time)

ELSE

0

END

) utilzion_duration,

sum(

CASE

WHEN device_status IN (1, 3, 4, 5) THEN

TIMESTAMPDIFF(SECOND, start_time, end_time)

ELSE

0

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

WHEREbr.tenant_id ='5VRJD01'

AND br.flag_id = 0