优化SQL查询，以行总和总和按数量进行排序，但是当有很多数据时它非常慢

Question

将需要优化此SQL查询，该查询需要大约40秒才能执行。 在特定日期内获得所有出售的餐点

。

    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

是

目的

的

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;

（掉落是为了消除由于添加而消除冗余索引。）