优化SQL查询，汇总行数量并按数量排序，但当有大量数据时，速度很慢

Question

将需要优化这个执行大约40秒的SQL查询。目标是获得特定日期内销售的所有餐食的总和，按餐食ID

分组。查询

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 s

目标是将其减少到少于6秒或更长

表信息

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;

Answer 1

ALTER TABLE cart_meals
ADD INDEX(vendor_branch_id, status, created_at, meal_id),
ADD INDEX(meal_id, status, created_at,  qty),
DROP INDEX cart_meals_vendor_branch_id_index,
DROP INDEX cart_meals_meal_id_index;

(drop是为了去除由于add而产生的冗余索引)