MariaDB视图project_sum加载性能优化求助

问题背景 #

当前project_sum视图基于accounting_record表，按project_id分组，通过4个CASE表达式分别统计不同条件下的value_in_euro求和结果。由于要求数据实时准确，只能实时计算，导致页面加载缓慢；已创建单字段索引但性能提升有限，且担心预计算的并发竞态问题。

具体优化方案 #

• 创建覆盖型复合索引
  单字段索引无法满足当前查询的过滤、分组、多条件判断及求和需求，建议创建包含所有查询依赖字段的复合索引，让数据库无需回表即可完成计算：

  CREATE INDEX idx_acc_covering ON accounting_record(
      is_initial,
      project_id,
      datev_type,
      planned_actual_indicator,
      value_in_euro
  );
  

  索引顺序逻辑：先通过is_initial过滤数据，再按project_id分组，后续字段覆盖CASE判断及求和所需的全部列，彻底避免全表扫描或回表操作。

• 移除强制索引指定
  原查询中的USE INDEX (is_initial)会强制数据库使用单字段索引，阻止优化器选择更高效的复合索引，直接删除该语句即可，让优化器自动选择最优执行计划。

• 简化CASE表达式写法
  用IF()函数替代冗余的CASE结构，语法更简洁，同时不影响计算逻辑，优化器处理效率更高：

  DROP VIEW IF EXISTS project_sum;
  
  CREATE VIEW project_sum AS
  SELECT
      project_id,
      SUM(IF(planned_actual_indicator = 'planned' AND datev_type = 'incoming', value_in_euro, 0)) AS sum_incoming_planned,
      SUM(IF(planned_actual_indicator = 'actual' AND datev_type = 'incoming', value_in_euro, 0)) AS sum_incoming_actual,
      SUM(IF(planned_actual_indicator = 'planned' AND datev_type = 'outgoing', value_in_euro, 0)) AS sum_outgoing_planned,
      SUM(IF(planned_actual_indicator = 'actual' AND datev_type = 'outgoing', value_in_euro, 0)) AS sum_outgoing_actual
  FROM accounting_record
  WHERE is_initial = false
  GROUP BY project_id;
  

• 安全实现预计算（解决并发竞态）
  如果后续仍想通过预计算进一步提升性能，可借助MariaDB InnoDB的行级锁和原子操作避免竞态：

  1. 创建汇总表project_sum_summary存储预计算值：

     CREATE TABLE project_sum_summary (
         project_id INT PRIMARY KEY,
         sum_incoming_planned DECIMAL(18,2) DEFAULT 0,
         sum_incoming_actual DECIMAL(18,2) DEFAULT 0,
         sum_outgoing_planned DECIMAL(18,2) DEFAULT 0,
         sum_outgoing_actual DECIMAL(18,2) DEFAULT 0
     );
     

  2. 为accounting_record表创建触发器，在数据插入/更新/删除时原子更新汇总表：

     -- 插入触发器
     DELIMITER //
     CREATE TRIGGER trg_acc_insert AFTER INSERT ON accounting_record
     FOR EACH ROW
     BEGIN
         IF NEW.is_initial = false THEN
             INSERT INTO project_sum_summary (project_id, sum_incoming_planned, sum_incoming_actual, sum_outgoing_planned, sum_outgoing_actual)
             VALUES (
                 NEW.project_id,
                 IF(NEW.planned_actual_indicator = 'planned' AND NEW.datev_type = 'incoming', NEW.value_in_euro, 0),
                 IF(NEW.planned_actual_indicator = 'actual' AND NEW.datev_type = 'incoming', NEW.value_in_euro, 0),
                 IF(NEW.planned_actual_indicator = 'planned' AND NEW.datev_type = 'outgoing', NEW.value_in_euro, 0),
                 IF(NEW.planned_actual_indicator = 'actual' AND NEW.datev_type = 'outgoing', NEW.value_in_euro, 0)
             )
             ON DUPLICATE KEY UPDATE
                 sum_incoming_planned = sum_incoming_planned + VALUES(sum_incoming_planned),
                 sum_incoming_actual = sum_incoming_actual + VALUES(sum_incoming_actual),
                 sum_outgoing_planned = sum_outgoing_planned + VALUES(sum_outgoing_planned),
                 sum_outgoing_actual = sum_outgoing_actual + VALUES(sum_outgoing_actual);
         END IF;
     END //
     DELIMITER ;
     
     -- 更新触发器（处理旧值扣除与新值增加）
     DELIMITER //
     CREATE TRIGGER trg_acc_update AFTER UPDATE ON accounting_record
     FOR EACH ROW
     BEGIN
         IF OLD.is_initial = false THEN
             UPDATE project_sum_summary
             SET
                 sum_incoming_planned = sum_incoming_planned - IF(OLD.planned_actual_indicator = 'planned' AND OLD.datev_type = 'incoming', OLD.value_in_euro, 0),
                 sum_incoming_actual = sum_incoming_actual - IF(OLD.planned_actual_indicator = 'actual' AND OLD.datev_type = 'incoming', OLD.value_in_euro, 0),
                 sum_outgoing_planned = sum_outgoing_planned - IF(OLD.planned_actual_indicator = 'planned' AND OLD.datev_type = 'outgoing', OLD.value_in_euro, 0),
                 sum_outgoing_actual = sum_outgoing_actual - IF(OLD.planned_actual_indicator = 'actual' AND OLD.datev_type = 'outgoing', OLD.value_in_euro, 0)
             WHERE project_id = OLD.project_id;
         END IF;
         IF NEW.is_initial = false THEN
             UPDATE project_sum_summary
             SET
                 sum_incoming_planned = sum_incoming_planned + IF(NEW.planned_actual_indicator = 'planned' AND NEW.datev_type = 'incoming', NEW.value_in_euro, 0),
                 sum_incoming_actual = sum_incoming_actual + IF(NEW.planned_actual_indicator = 'actual' AND NEW.datev_type = 'incoming', NEW.value_in_euro, 0),
                 sum_outgoing_planned = sum_outgoing_planned + IF(NEW.planned_actual_indicator = 'planned' AND NEW.datev_type = 'outgoing', NEW.value_in_euro, 0),
                 sum_outgoing_actual = sum_outgoing_actual + IF(NEW.planned_actual_indicator = 'actual' AND NEW.datev_type = 'outgoing', NEW.value_in_euro, 0)
             WHERE project_id = NEW.project_id;
         END IF;
     END //
     DELIMITER ;
     
     -- 删除触发器
     DELIMITER //
     CREATE TRIGGER trg_acc_delete AFTER DELETE ON accounting_record
     FOR EACH ROW
     BEGIN
         IF OLD.is_initial = false THEN
             UPDATE project_sum_summary
             SET
                 sum_incoming_planned = sum_incoming_planned - IF(OLD.planned_actual_indicator = 'planned' AND OLD.datev_type = 'incoming', OLD.value_in_euro, 0),
                 sum_incoming_actual = sum_incoming_actual - IF(OLD.planned_actual_indicator = 'actual' AND OLD.datev_type = 'incoming', OLD.value_in_euro, 0),
                 sum_outgoing_planned = sum_outgoing_planned - IF(OLD.planned_actual_indicator = 'planned' AND OLD.datev_type = 'outgoing', OLD.value_in_euro, 0),
                 sum_outgoing_actual = sum_outgoing_actual - IF(OLD.planned_actual_indicator = 'actual' AND OLD.datev_type = 'outgoing', OLD.value_in_euro, 0)
             WHERE project_id = OLD.project_id;
         END IF;
     END //
     DELIMITER ;
     

  这种方式利用ON DUPLICATE KEY UPDATE和InnoDB的行级锁保证操作原子性，同一project_id的并发操作会被串行处理，完全避免求和遗漏问题，同时汇总表的数据可直接用于查询，彻底解决实时计算的性能瓶颈。

内容的提问来源于stack exchange，提问作者chris