MySQL 5.6第一范式数据库子查询过滤的SQL查询优化

一、核心问题定位 #

• 8500万行的Acts表是性能瓶颈，复杂子查询关联时大概率触发全表扫描，且MySQL 5.6对嵌套子查询的优化能力有限
• 第一范式结构导致关联表较多，join时若缺失针对性索引，会放大笛卡尔积或回表查询的性能损耗

二、最新配送条目查询优化（替代低效子查询） #

由于MySQL 5.6不支持窗口函数，采用关联子查询+复合索引的方式实现「每个产品+库存地点的最新配送条目」：

1. 给Acts表建立定向复合索引（需替换为实际业务字段名）：

CREATE INDEX idx_act_product_loc_time ON Acts (entity_product_id, entity_loc_id, act_time DESC);

该索引按「过滤条件+排序方向」构建，能直接定位到目标产品+地点的最新配送记录，避免全表扫描。

2. 重写最新配送查询逻辑：

SELECT 
    p.entity_product_id,
    p.entity_loc_id,
    a.act_id,
    a.delivery_qty,
    a.act_time
FROM participations p
JOIN Acts a ON p.act_id = a.act_id
WHERE a.act_time = (
    SELECT MAX(act_time)
    FROM Acts a2
    JOIN participations p2 ON a2.act_id = p2.act_id
    WHERE p2.entity_product_id = p.entity_product_id
      AND p2.entity_loc_id = p.entity_loc_id
      AND a2.act_type = 'DELIVERY' -- 过滤配送类型，按需调整
);

子查询借助上述复合索引，能快速命中MAX(act_time)对应的行，大幅缩短查询耗时。

三、原查询关联最新配送条目的优化 #

1. 用临时表预计算最新配送数据，再关联原查询：

-- 创建临时表存储预计算结果，加主键加速关联
CREATE TEMPORARY TABLE temp_latest_delivery (
    entity_product_id INT,
    entity_loc_id INT,
    latest_act_id INT,
    latest_delivery_qty DECIMAL(10,2),
    PRIMARY KEY (entity_product_id, entity_loc_id)
) ENGINE=InnoDB;

-- 写入预计算的最新配送数据
INSERT INTO temp_latest_delivery
SELECT 
    p.entity_product_id,
    p.entity_loc_id,
    a.act_id,
    a.delivery_qty
FROM participations p
JOIN Acts a ON p.act_id = a.act_id
WHERE a.act_time = (
    SELECT MAX(act_time)
    FROM Acts a2
    JOIN participations p2 ON a2.act_id = p2.act_id
    WHERE p2.entity_product_id = p.entity_product_id
      AND p2.entity_loc_id = p.entity_loc_id
      AND a2.act_type = 'DELIVERY'
);

-- 关联原库存成本查询与临时表
SELECT 
    orig.*,
    t.latest_delivery_qty,
    t.latest_act_id
FROM (
    -- 替换为你的原库存、成本查询SQL
    SELECT product_id, loc_id, stock_qty, cost_price FROM ...
) orig
LEFT JOIN temp_latest_delivery t 
    ON orig.product_id = t.entity_product_id 
    AND orig.loc_id = t.entity_loc_id;

临时表的主键能让关联操作快速匹配，避免重复计算最新配送数据。

2. 补充关联表索引：
   • 给participations表建立复合索引：CREATE INDEX idx_part_act_product_loc ON participations (act_id, entity_product_id, entity_loc_id);，减少join时的回表查询
   • 确保原库存查询中产品、库存地点相关字段已有索引

四、额外性能优化建议 #

• 限制Acts表扫描范围：若业务允许，添加时间过滤条件（如a.act_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)），减少扫描行数
• 避免SELECT *：仅查询业务所需字段，降低数据传输和内存占用
• 调整MySQL配置：适当调大join_buffer_size、sort_buffer_size（需控制在服务器内存上限内）

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