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

Question

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
                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
WHERE

br.tenant_id ='5VRJD01'

AND br.flag_id = 0

Answer 1

找到合适的字段加索引