加速计算合规率及配送总量的SQL查询优化求助

你的问题很典型：用相关子查询做统计时，大数据量下重复扫描表会导致性能雪崩。35万条数据的表，每个承运人都要跑两次子查询，相当于反复遍历全表，5分钟的耗时一点都不奇怪。下面是具体的优化方案：

核心问题分析 #

原查询里的相关子查询会为每一个承运人单独执行一次，等于把LIVRAISON和TOURNEE表扫描了N次（N是承运人的数量）。这种方式在数据量小的时候没问题，但35万条数据下，IO和计算量直接拉满，自然慢得离谱。

优化后的查询语句 #

我们可以用一次聚合查询搞定所有统计，只扫一次表就能拿到每个承运人的总配送数和合规数，再关联获取名称就行：

SELECT 
    t.idTrans AS id,
    t.nomTrans,
    -- 处理总数为0的情况，避免除以0报错
    CASE WHEN agg.total_deliveries = 0 THEN 0 
         ELSE agg.compliant_deliveries / agg.total_deliveries 
    END AS Taux,
    agg.total_deliveries AS total_livraisons
FROM (
    -- 一次性统计每个承运人的总配送和合规配送数
    SELECT 
        idTrans,
        COUNT(*) AS total_deliveries,
        -- 这里替换成你的合规状态判断条件，比如codeSt = 'OK'
        SUM(CASE WHEN codeSt = '合规状态标识' THEN 1 ELSE 0 END) AS compliant_deliveries
    FROM LIVRAISON 
    NATURAL JOIN TOURNEE 
    -- 用CURDATE()更准确，避免SYSDATE()的时间部分干扰
    WHERE DateTrn = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
    GROUP BY idTrans
) AS agg
-- 关联承运人名表，假设nomTrans存在TRANSPORTEUR表中
JOIN TRANSPORTEUR t ON agg.idTrans = t.idTrans;

优化点说明 #

• 单次聚合扫描：子查询只执行一次，遍历LIVRAISON和TOURNEE表一次就统计出所有承运人的数据，彻底避免重复扫描。
• 用SUM+CASE统计合规数：代替原来的子查询计数，把合规判断和统计合并到一次扫描里。
• 除以0防护：添加CASE处理总配送数为0的场景，避免查询报错。
• 日期函数优化：用CURDATE()代替SYSDATE()，因为CURDATE()返回纯日期，和DateTrn的DATE类型匹配更精准，也能更好利用索引。

索引优化建议 #

光改查询还不够，给关键字段加索引能进一步提速：

• 给TOURNEE.DateTrn创建单独索引：快速定位昨天的记录，减少需要扫描的数据量。
• 给关联字段创建联合索引：比如如果LIVRAISON和TOURNEE是通过idTrn关联的，创建TOURNEE(idTrn, DateTrn)或者LIVRAISON(idTrn, idTrans)的联合索引，让关联和过滤更高效。
• 确保TRANSPORTEUR.idTrans是主键（或有唯一索引）：关联承运人名的时候能快速定位。

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