MySQL存储过程优化：未传入参数时自动排除对应查询条件

一、修改存储过程实现动态条件过滤 #

首先要处理programName参数未传入（即参数为NULL）时自动排除program匹配条件的需求。我们可以通过在WHERE子句中添加逻辑判断来实现：当programName为NULL时，跳过该条件；否则匹配program字段。

修改后的存储过程代码如下：

DELIMITER //
CREATE PROCEDURE GetUtilization2( IN programName VARCHAR(30), IN date1 VARCHAR(20), IN date2 VARCHAR(20) )
BEGIN
    SELECT 
        SUM(task_hours) as total,
        (SUM(CASE WHEN task_category = 'Production' THEN task_hours ELSE 0 END)/SUM(task_hours)) * 100 AS production,
        (SUM(CASE WHEN task_category = 'Non Production' THEN task_hours ELSE 0 END)/SUM(task_hours)) * 100 AS non_production,
        (SUM(CASE WHEN task_category = 'Training' THEN task_hours ELSE 0 END)/SUM(task_hours)) * 100 AS Training,
        (SUM(CASE WHEN task_category = 'Absenteeism' THEN task_hours ELSE 0 END)/SUM(task_hours)) * 100 AS Absenteeism,
        (SUM(CASE WHEN utilization_type = 'Extended' THEN task_hours ELSE 0 END) / 
         (SUM(task_hours) - SUM(CASE WHEN utilization_type = 'Extended' THEN task_hours ELSE 0 END))) * 100 AS OT
    FROM data_table
    WHERE 
        -- 处理programName参数：未传入（NULL）时跳过该条件
        (programName IS NULL OR program = programName)
        -- 优化日期条件：避免在列上使用函数，防止索引失效
        AND task_date BETWEEN STR_TO_DATE(date1, '%Y-%m-%d') AND DATE_ADD(STR_TO_DATE(date2, '%Y-%m-%d'), INTERVAL 1 DAY - INTERVAL 1 SECOND);
END //
DELIMITER ;

这里的关键改动是WHERE子句中的(programName IS NULL OR program = programName)：

• 当调用存储过程时不传入programName（或传入NULL），这个条件会自动变为TRUE，相当于跳过program的匹配
• 当传入有效的programName值时，会正常匹配program字段

二、查询效率优化建议 #

针对原查询的性能问题，我们可以从以下几个方面优化：

1. 避免在列上使用函数，保护索引

原查询中date(task_date) between date1 and date2会导致task_date字段上的索引无法被使用（因为MySQL无法对函数处理后的列使用索引）。我们改为将传入的字符串参数转换为日期类型，直接和task_date比较：

task_date BETWEEN STR_TO_DATE(date1, '%Y-%m-%d') AND DATE_ADD(STR_TO_DATE(date2, '%Y-%m-%d'), INTERVAL 1 DAY - INTERVAL 1 SECOND)

这里假设你的date1和date2是YYYY-MM-DD格式，如果是其他格式，需要调整STR_TO_DATE的第二个参数（比如%m/%d/%Y）。

2. 减少重复聚合计算

原查询中SUM(task_hours)被重复计算了多次，我们可以用CTE（公共表表达式）先计算出总工时，减少重复计算的开销：

DELIMITER //
CREATE PROCEDURE GetUtilization2( IN programName VARCHAR(30), IN date1 VARCHAR(20), IN date2 VARCHAR(20) )
BEGIN
    WITH summary AS (
        SELECT 
            SUM(task_hours) AS total_hours,
            SUM(CASE WHEN task_category = 'Production' THEN task_hours ELSE 0 END) AS production_hours,
            SUM(CASE WHEN task_category = 'Non Production' THEN task_hours ELSE 0 END) AS non_production_hours,
            SUM(CASE WHEN task_category = 'Training' THEN task_hours ELSE 0 END) AS training_hours,
            SUM(CASE WHEN task_category = 'Absenteeism' THEN task_hours ELSE 0 END) AS absenteeism_hours,
            SUM(CASE WHEN utilization_type = 'Extended' THEN task_hours ELSE 0 END) AS extended_hours
        FROM data_table
        WHERE 
            (programName IS NULL OR program = programName)
            AND task_date BETWEEN STR_TO_DATE(date1, '%Y-%m-%d') AND DATE_ADD(STR_TO_DATE(date2, '%Y-%m-%d'), INTERVAL 1 DAY - INTERVAL 1 SECOND)
    )
    SELECT 
        total_hours AS total,
        (production_hours / total_hours) * 100 AS production,
        (non_production_hours / total_hours) * 100 AS non_production,
        (training_hours / total_hours) * 100 AS Training,
        (absenteeism_hours / total_hours) * 100 AS Absenteeism,
        (extended_hours / (total_hours - extended_hours)) * 100 AS OT
    FROM summary;
END //
DELIMITER ;

通过CTE先计算出所有需要的聚合值，后续只需要引用这些预计算的值，减少了MySQL重复计算聚合函数的次数，提升查询效率。

3. 添加合适的索引

为data_table创建复合索引可以大幅提升查询速度，建议创建以下索引：

-- 针对program和task_date的过滤条件，以及聚合需要的字段（MySQL 8.0.13+支持INCLUDE）
CREATE INDEX idx_program_taskdate ON data_table(program, task_date) INCLUDE (task_hours, task_category, utilization_type);

如果你的MySQL版本不支持INCLUDE，可以改为：

CREATE INDEX idx_program_taskdate ON data_table(program, task_date, task_hours, task_category, utilization_type);

这个索引可以让MySQL直接从索引中获取需要的所有数据，无需回表查询，极大提升查询性能。

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