提高Mysql更新查询性能

Question

我们正在尝试将Mysql数据库(v5.7.17)中的一些数据从动态评估更改为数据库中的字段。混凝土场是detectable_status，每晚对设备(可检测设备)进行计算，以便对其进行评估。这是为应用程序中的具体客户端更新字段的查询：

UPDATE tdetectable det
JOIN tdetectable_equipment equipment ON det.id = equipment.id_detectable 
JOIN torganization org ON det.id_organization = org.id
SET det.detectable_status = (CASE 
    WHEN (equipment.setting_up_date IS NOT NULL 
        AND equipment.expiration_periodicity_type IS NOT NULL AND
        fdate_periodicity(equipment.setting_up_date, 
        equipment.expiration_periodicity_type, equipment.expiration_periodicity_value,1) 
        < NOW()) = 1 
        THEN 'EXPIRED' 
    WHEN (EXISTS(
        SELECT *
        FROM trevision_cycle incorrect_cycle
        WHERE equipment.id_detectable = incorrect_cycle.id_detectable 
        AND incorrect_cycle.status = 'NO_OK' 
        AND incorrect_cycle.active <> 0 AND incorrect_cycle.id IS NOT NULL)) 
        THEN 'INCORRECT' 
    WHEN (EXISTS(
        SELECT *
        FROM trevision_cycle need_revision_cycle
        WHERE equipment.id_detectable = need_revision_cycle.id_detectable 
        AND need_revision_cycle.next_revision_date IS NOT NULL 
        AND need_revision_cycle.next_revision_date < NOW() = 1 
        AND equipment.id_detectable = det.id 
        AND need_revision_cycle.active <> 0)) 
        THEN 'NEEDS_REVISION' 
    WHEN (equipment.setting_up_date IS NULL 
        AND (equipment.expiration_periodicity_type IS NOT NULL OR EXISTS(
        SELECT *
        FROM trevision_cycle last_revision_cycle
        WHERE last_revision_cycle.id_detectable = equipment.id_detectable 
        AND last_revision_cycle.last_revision_date IS NULL 
        AND last_revision_cycle.active <> 0))) 
        THEN 'INDETERMINATE' 
    ELSE    'CORRECT' 
    END)
WHERE det.active and org.id_client = ?

根据不同的因素(过期日期、修订日期.)为客户中的每一台设备设置状态。问题是查询非常慢。这里是我的基准：

Affected rows: 131, Duration: 0,375 sec.

Affected rows: 4.208, Duration: 44,585 sec.

Affected rows: 2.036, Duration: 13,712 sec.

Affected rows: 13.288, Duration: 00:10:04

我还想澄清fdate_periodicity函数的内容，它需要一个日期并为其添加一个周期(例如: 2017-09-10 +1年= 2018-09-10)：

BEGIN
IF add_to THEN
    RETURN
        CAST((CASE periodicity_type 
        WHEN 'DAY' THEN (input_date + INTERVAL periodicity_value DAY) 
        WHEN 'WEEK' THEN (input_date + INTERVAL periodicity_value WEEK) 
        WHEN 'MONTH' THEN (input_date + INTERVAL periodicity_value MONTH) 
        WHEN 'YEAR' THEN (input_date + INTERVAL periodicity_value YEAR)
        ELSE NULL
        END
        ) AS DATETIME
        );
ELSE
    RETURN  CAST((CASE periodicity_type 
        WHEN 'DAY' THEN (input_date - INTERVAL periodicity_value DAY) 
        WHEN 'WEEK' THEN (input_date - INTERVAL periodicity_value WEEK) 
        WHEN 'MONTH' THEN (input_date - INTERVAL periodicity_value MONTH) 
        WHEN 'YEAR' THEN (input_date - INTERVAL periodicity_value YEAR)
        ELSE NULL
        END
        ) AS DATETIME
        );
end if;
END

我知道MySql在这里做的工作很繁重，但是对于核心-8+14 is的RAM机器来说，这种执行时间正常吗？我能做些什么来改善它呢？这里是执行计划(解释)：

感谢先进的！

Answer 1

将其转换为4 UPDATES，每个状态一个(EXPIRED，INCORRECT，.)。按照正确的顺序运行更新，以便如果应用了两种情况，您将得到所需的状态。

每个UPDATE将匹配您的一个案例。例如，对于INCORRECT：

UPDATE tdetectable det
JOIN  tdetectable_equipment e  ON det.id = e.id_detectable 
JOIN  torganization org  ON det.id_organization = org.id
SET det.detectable_status = 'INCORRECT'
WHERE ( EXISTS(
        SELECT *
        FROM trevision_cycle
        WHERE id_detectable = e.id_detectable 
          AND status = 'NO_OK' 
          AND active <> 0
          AND id IS NOT NULL -- If `id` is the PK, this test unnecessary
      ) )
      AND det.active
      AND org.id_client = ?

指数：

torganization: INDEX(id_client, id)
det:  INDEX(id, active) -- unless you have PRIMARY KEY(id)
trevision_cycle: INDEX(id_detectable, status, id, active)

那台大小的机器的innodb_buffer_pool_size应该是10克左右。

更多细节

ALTER TABLE trevision_cycle ADD INDEX(id_detectable, status, id, active);

KEY和INDEX是同义词。大多数数据类型(INT、ENUM等，但不是TEXT或BLOB)都可以进行索引。由于它有超过1列，这个索引被称为“复合”或“复合”。索引定义中列的顺序很重要；我提供了一个有用的顺序。