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;

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

br.tenant_id ='5VRJD01'

AND br.flag_id = 0

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

回答

找到合适的字段加索引

以上是 mysql 9月份200万条数据,查询慢怎么解决呢 的全部内容, 来源链接: utcz.com/a/61887.html

回到顶部