MySQL跨表计算预订日价并排序的结果异常与性能优化问题

• 所有车辆总价一致的核心原因：原查询的关联子查询没有加vehicles.id = vehicles_prices.vehicle_id的关联条件，导致子查询每次都统计了全表所有车辆在指定日期范围内的价格总和，自然所有行的price_total都相同。
• 大数据量GROUP BY查询过慢的原因：一是vehicles_prices表缺少必要的联合索引，每次查询都要全表扫描；二是price字段为字符串类型，求和时需要做隐式类型转换，额外消耗性能。

1. 表结构与索引优化（性能提升核心） #

首先修改字段类型避免隐式转换，再添加覆盖索引让查询直接命中索引无需回表：

-- 将price改为数值类型，适配求和运算
ALTER TABLE `vehicles_prices` MODIFY COLUMN `price` DECIMAL(10,2) NOT NULL DEFAULT 0;
-- 添加联合覆盖索引，适配关联、日期过滤、求和的全流程查询
ALTER TABLE `vehicles_prices` ADD INDEX `idx_vehicle_day_price` (`vehicle_id`, `day`, `price`);

2. 修正后的查询语句 #

推荐使用关联聚合写法，配合上述索引在大数据量场景下性能远优于子查询：

SELECT 
    v.*, COALESCE(SUM(vp.price), 0) AS price_total
FROM vehicles v
LEFT JOIN vehicles_prices vp 
    ON v.id = vp.vehicle_id 
    AND vp.day BETWEEN '2021-10-02' AND '2021-10-05'
WHERE v.status = 1
GROUP BY v.id
ORDER BY price_total DESC;

注：由于day字段为date类型，直接写日期范围即可匹配，无需补充时分秒

如果是小数据量临时查询，也可以使用修正后的关联子查询写法：

SELECT
  *, (
      SELECT SUM(`price`) 
      FROM `vehicles_prices` 
      WHERE `vehicle_id` = `vehicles`.`id` -- 新增车辆关联条件是核心修正点
      AND `day` BETWEEN '2021-10-02' AND '2021-10-05'
  ) AS `price_total`
FROM
  `vehicles`
WHERE
  `status` = 1
ORDER BY `price_total` DESC

3. 效果说明 #

完成上述优化后，即使是千万级的价格表，查询也可以在毫秒级返回结果，完全避免全表扫描、临时表排序等性能损耗。

内容的提问来源于stack exchange，提问作者Önder Erol