付费解决mysql sum 多字段统计慢的问题

最近遇见一个sum 统计特别慢的问题。

大概有13个字段需要sum 和一个count

数据五六万就要去到5秒

微ID amu-ajin 欢迎大佬联系我

佣金：500起

CREATE TABLE "order_orders" (
  "id" bigint unsigned NOT NULL AUTO_INCREMENT,
  "oid" char(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  "user_id" bigint unsigned NOT NULL,
  "username" varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  "nickname" varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  "quantity" bigint unsigned NOT NULL DEFAULT '1',
  "amount" decimal(20,4) NOT NULL,
  "consumption" decimal(20,4) NOT NULL DEFAULT '0.0000',
  "pay_amount" decimal(20,4) NOT NULL DEFAULT '0.0000',
  "discount" decimal(20,4) NOT NULL DEFAULT '0.0000',
  "order_at_day" date NOT NULL,
  "created_at" timestamp NULL DEFAULT NULL,
  "updated_at" timestamp NULL DEFAULT NULL,
  PRIMARY KEY ("id"),
  KEY "order_orders_oid_index" ("oid"),
  KEY "order_orders_user_id_index" ("user_id"),
  KEY "order_orders_order_at_day_index" ("order_at_day")
);

DB::table('order_orders')
    ->select(
        [
            'username',
            'nickname',
            DB::raw('count(quantity) as quantity'),
            DB::raw('sum(amount) as amount'),
            DB::raw('sum(consumption) as consumption'),
            DB::raw('sum(pay_amount) as pay_amount'),
            DB::raw('sum(discount) as discount'),

        ]
    )
    ->whereIn('user_id', [1, 2, 3, 5, 8, 11])
    ->whereBetween('order_at_day', $this->request->dateRange)
    ->groupBy(
                'username',
                'nickname',
        )
    ->cursor();