1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

EXPLAIN
SELECT
    hprt.id AS task_id,
    hprg.id AS main_id,
    hprt.create_time,
    hprti.begin_time,
    hprti.end_time,
    hprt.check_time,
    hprt.check_user_id,
    hprt.check_status,
    CASE
        WHEN hprt.check_status = 0 AND hprti.end_time >= NOW() AND hprti.begin_time <= NOW() THEN '10'
        WHEN hprt.check_status = 2
             OR (hprt.check_status = 0 AND hprti.end_time < NOW()) THEN '11'
        WHEN hprt.check_status = 1 THEN '20'
    END AS STATUS,
    hpro.risk_object_name,
    hpru.risk_unit_name,
    hpd.dangersrc_name,
    hpri.risk_info,
    hpru.risk_type,
    hpri.risk_level,
    hprc.F_Id AS ctrl_id,
    hprc.screening_type,
    hprc.screening_num,
    hprc.screening_department,
    hprc.screening_position,
    hprc.post_psn_id,
    hprc.ctrl_measure,
    hprc.hd_content,
    hprc.post_psn_name,
    hpro.F_CreateUserId
FROM
    risk_task hprt
    INNER JOIN risk_task_info hprti USE INDEX (index_begin_time)
        ON hprt.task_info_id = hprti.task_info_id
    INNER JOIN risk_ctrl hprc
        ON hprt.ctrl_id = hprc.F_Id AND hprc.F_DeleteMark = 0
    INNER JOIN risk_info hpri
        ON hprc.risk_id = hpri.F_Id AND hpri.F_DeleteMark = 0
    INNER JOIN risk_unit hpru
        ON hpri.risk_unit_id = hpru.F_Id AND hpru.F_DeleteMark = 0
    INNER JOIN risk_object hpro
        ON hpru.risk_object_id = hpro.F_Id AND hpru.F_DeleteMark = 0
    INNER JOIN dangersrc hpd
        ON hpri.dangersrc_id = hpd.F_Id AND hpd.F_DeleteMark = 0
    LEFT JOIN risk_governance hprg
        ON hprg.task_id = hprt.id
WHERE
    hprt.F_DeleteMark = 0
    AND hprti.begin_time <= NOW()
    AND hprti.begin_time >= '2025-09-9 00:00:00'
    AND '2025-09-9 23:59:59' >= hprti.begin_time
ORDER BY
    hpro.risk_object_name,
    hpru.risk_unit_name,
    hprti.begin_time DESC,
    hprt.check_time DESC
LIMIT 20;

1
2

KEY `index_end_time` (`end_time`)
KEY `index_begin_time` (`begin_time`)  -- 范围过滤用了

1
2
3
4
5

-- 旧索引（删）
KEY `index_begin_time` (`begin_time`)

-- 新索引：begin_time（过滤）+ task_info_id（JOIN hprt）+ end_time（覆盖 WHERE）
KEY `index_begin_time` (`begin_time`, `task_info_id`, `end_time`)

1
2
3

-- 排序第一字段是 risk_object_name
-- JOIN 字段是 F_Id
KEY `idx_risk_object_name` (`risk_object_name`, `F_Id`)

1
2
3
4
5

-- 删除旧索引
KEY `index_object_id` (`risk_object_id`)

-- 新索引：risk_object_id（JOIN hpro）+ risk_unit_name（排序）+ F_Id（JOIN hpri）
KEY `idx_risk_unit_covering` (`risk_object_id`, `risk_unit_name`, `F_Id`)

1
2
3
4
5
6

-- 删除旧索引
KEY `index_task_info_id` (`task_info_id`)
KEY `index_ctrl_id` (`ctrl_id`)

-- 新索引：task_info_id（JOIN hprti）+ F_DeleteMark（过滤）+ ctrl_id（JOIN hprc）+ check_time（排序）
KEY `idx_task_covering` (`task_info_id`, `F_DeleteMark`, `ctrl_id`, `check_time`)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

-- 1. 删旧索引（INPLACE 操作，瞬间完成）
ALTER TABLE risk_task_info
  ALGORITHM = INPLACE,
  DROP INDEX index_begin_time;

-- 2. 加新索引（INPLACE，不锁表）
ALTER TABLE risk_task_info
  ALGORITHM = INPLACE,
  ADD INDEX idx_hprti_time_covering (begin_time, task_info_id, end_time);

-- 3. 改名复用（SQL 写死了 USE INDEX (index_begin_time)，不能改名）
ALTER TABLE risk_task_info
  ALGORITHM = INPLACE,
  RENAME INDEX idx_hprti_time_covering TO index_begin_time;

-- 4. risk_object 加索引
ALTER TABLE risk_object
  ALGORITHM = INPLACE,
  ADD INDEX idx_risk_object_name (risk_object_name, F_Id);

-- 5. risk_unit 替换索引
ALTER TABLE risk_unit
  ALGORITHM = INPLACE,
  DROP INDEX index_object_id;

CREATE INDEX idx_risk_unit_covering ON risk_unit(
  risk_object_id, risk_unit_name, F_Id
) ALGORITHM = INPLACE;

-- 6. risk_task 替换索引
ALTER TABLE risk_task
  ALGORITHM = INPLACE,
  DROP INDEX index_task_info_id,
  DROP INDEX index_ctrl_id;

CREATE INDEX idx_task_covering ON risk_task(
  task_info_id, F_DeleteMark, ctrl_id, check_time
) ALGORITHM = INPLACE;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

WITH baseInfos AS (
    SELECT d.F_Id AS mId, c.F_Id AS tId, b.F_Id AS oId, a.F_Id AS lId,
           d.NAME AS NAME, a.work_type AS workType
    FROM risk_ctrl a
        INNER JOIN risk_info b ON a.risk_id = b.F_Id
        INNER JOIN risk_object c ON b.risk_object_id = c.F_Id
        INNER JOIN hazard_major_management d ON d.identifier = c.riskobject_code
    WHERE b.F_DeleteMark = 0
      AND c.F_DeleteMark = 0
      AND d.F_DeleteMark = 0
      AND a.work_type IN ('1', '2', '3')
)
SELECT
    t.*,
    p.person_in_charge_main main,
    p.person_in_charge_technical technical,
    p.person_in_charge_operation operation
FROM (
    SELECT
        baseInfos.mId id,
        baseInfos.workType,
        baseInfos.NAME riskObjectName,
        date_format(info.begin_time, '%Y-%m-%d') beginTime,
        date_format(info.end_time, '%Y-%m-%d') endTime,
        IF(count(1) = sum(IF(k.check_status = 1, 1, 0)), '已完成', '未完成') AS checkStatus,
        sum(k.is_risk) hdCount,
        max(k.check_time) checkTime,
        k.check_user_id checkUser
    FROM risk_task k
        INNER JOIN risk_task_info info ON k.task_info_id = info.task_info_id
        INNER JOIN baseInfos ON k.ctrl_id = baseInfos.lId
    WHERE k.F_DeleteMark = 0
    GROUP BY
        baseInfos.mId,
        IF(checkUser IS NULL OR checkUser = '', baseInfos.workType, checkUser),
        info.begin_time,
        info.end_time
    ORDER BY info.begin_time DESC
) t
LEFT JOIN hazard_major_management p ON t.id = p.F_Id
LIMIT 20;

1
2
3
4
5
6
7
8
9

-- 覆盖过滤 + JOIN + 排序
CREATE INDEX idx_k_covering ON risk_task
  (F_DeleteMark, ctrl_id, task_info_id, check_time)
  ALGORITHM = INPLACE LOCK = NONE;

-- 覆盖 JOIN + 排序
CREATE INDEX idx_info_covering ON risk_task_info
  (task_info_id, begin_time, end_time)
  ALGORITHM = INPLACE LOCK = NONE;

1

等值过滤 > 范围过滤 > 排序 > 覆盖字段