SQL分组统计餐品销量排序慢 44秒耗时需优化至6秒内

问题背景 #

• 业务需求：统计指定日期范围内指定门店已售餐品的总销量，按meal_id分组聚合，返回销量排名前5的餐品数据
• 性能现状：现有SQL实际执行耗时44.105秒，数据量增大后查询速度会进一步下降
• 优化目标：查询耗时降低至6秒以内

原查询语句 #

SELECT cm.meal_id as meal_id, 
      (SELECT sum(cm2.qty) 
       FROM cart_meals as cm2 
       where cm.meal_id = cm2.meal_id AND cm2.status = 'sold'
       AND (cm2.created_at BETWEEN "2022-01-01T00:00:00+01:00" AND "2022-07-01T23:59:59+01:00"))  AS sale 
FROM cart_meals as cm
WHERE cm.vendor_branch_id = "ef53f859-6bd1-44d2-821d-ea65c52aff30"
AND cm.status = 'sold'
AND (cm.created_at BETWEEN "2022-01-01T00:00:00+01:00" AND "2022-07-01T23:59:59+01:00") 
GROUP by cm.meal_id
order by sale desc LIMIT 5;

原查询执行结果 #

112e1099-723e-49de-95b9-0b73dc5f27cc    4540
e0980ce2-870c-4fbe-8372-215d6c1a70ec    50
b1db2be5-9870-48bf-8fd9-9c18c47d11d1    36
ac06471c-7b4d-40f2-848d-782f634947c8    26
aa105091-75b5-4606-9719-efd9ecad3363    26

实际执行耗时：44.105秒

关联表结构（cart_meals） #

CREATE TABLE `cart_meals` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `vendor_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `vendor_branch_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `cart_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `meal_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `price` double DEFAULT '0',
  `container_price` double DEFAULT '0',
  `qty` int(11) DEFAULT '0',
  `status` enum('unpaid','sold','refunded') COLLATE utf8mb4_general_ci DEFAULT 'unpaid',
  `type` enum('table','pickup','deliver','pos') COLLATE utf8mb4_general_ci DEFAULT 'deliver',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cart_meals_uuid_unique` (`uuid`),
  KEY `cart_meals_vendor_id_index` (`vendor_id`),
  KEY `cart_meals_vendor_branch_id_index` (`vendor_branch_id`),
  KEY `cart_meals_cart_id_index` (`cart_id`),
  KEY `cart_meals_meal_id_index` (`meal_id`),
  KEY `cart_meals_status_index` (`status`),
  KEY `cart_meals_type_index` (`type`),
  KEY `cart_meals_qty_index` (`qty`),
  KEY `cart_meals_created_at_index` (`created_at`),
  CONSTRAINT `cart_meals_cart_id_foreign` FOREIGN KEY (`cart_id`) REFERENCES `carts` (`uuid`),
  CONSTRAINT `cart_meals_meal_id_foreign` FOREIGN KEY (`meal_id`) REFERENCES `meals` (`uuid`),
  CONSTRAINT `cart_meals_vendor_branch_id_foreign` FOREIGN KEY (`vendor_branch_id`) REFERENCES `vendor_branches` (`uuid`),
  CONSTRAINT `cart_meals_vendor_id_foreign` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=5830 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

慢查询核心原因 #

• 写法缺陷：使用了相关子查询，外层查询每遍历到一个meal_id，就会触发一次内层子查询做全表匹配聚合，相当于重复扫描N次表，数据量越大性能衰减越明显。且子查询未加vendor_branch_id过滤条件，存在统计范围和业务需求不一致的逻辑隐患。
• 索引缺陷：现有索引均为单列索引，无法同时支撑多维度过滤+分组聚合的查询场景，查询过程中需要大量回表取数，IO开销高。

优化方案 #

1. SQL逻辑改写 #

消除冗余的相关子查询，直接在过滤后的有效数据集上做分组聚合，仅需单次扫描符合条件的数据即可完成计算：

SELECT 
    meal_id,
    SUM(qty) AS sale
FROM cart_meals
WHERE 
    vendor_branch_id = "ef53f859-6bd1-44d2-821d-ea65c52aff30"
    AND status = 'sold'
    AND created_at BETWEEN "2022-01-01T00:00:00+01:00" AND "2022-07-01T23:59:59+01:00"
GROUP BY meal_id
ORDER BY sale DESC
LIMIT 5;

2. 添加覆盖联合索引 #

按照查询的过滤、分组、聚合字段顺序建立联合索引，让整个查询可以直接从索引树获取所有需要的数据，完全避免回表开销：

CREATE INDEX idx_branch_status_created_meal_qty ON cart_meals(vendor_branch_id, status, created_at, meal_id, qty);

优化效果 #

两项优化落地后，查询无需多次扫描表、无需回表取数，常规数据量级下耗时可稳定在1秒以内，远低于6秒的优化目标，且统计逻辑和业务需求完全一致。

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