时间戳

首页 > 解决方案 > Mysql：有没有更高效的嵌套聚合更新方式？

mysql - Mysql：有没有更高效的嵌套聚合更新方式？

问题描述

我正在尝试根据计算出的 2 个嵌套查询之和的结果来更新表中的单个值。这是我尝试过的查询，它似乎有效，但速度很慢。

update WALLET w
set total = 
 (
    select 
        min(
            (select sum(earned) from WALLET_TRANSACTION t where t.wallet_id = w.id and type not in ('FEE', 'REDEEM')) + 
            (select sum(earned) from WALLET_TRANSACTION t where t.wallet_id = w.id and type in ('FEE', 'REDEEM'))
        ) as sumredeemed
    from WALLET_TRANSACTION
 );

目前更新单个 WALLET 条目需要 52.77 秒。

无论如何我可以优化这个查询以在 Mysql 8 中加速它吗？

钱包表：

mysql> show create table WALLET\G
*************************** 1. row ***************************
       Table: WALLET
Create Table: CREATE TABLE `WALLET` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `reference` varchar(50) NOT NULL,
  `total` decimal(8,2) NOT NULL DEFAULT '0.00',
  `user_id` bigint(20) NOT NULL DEFAULT '0',
  `target` decimal(8,2) NOT NULL DEFAULT '0.00',
  `target_manually_adjusted` bit(1) DEFAULT b'0',
  `deleted` bit(1) DEFAULT b'0',
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(50) DEFAULT NULL,
  `last_modified` datetime DEFAULT CURRENT_TIMESTAMP,
  `last_modified_by` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `reference` (`reference`,`user_id`),
  KEY `wallet_reference` (`reference`),
  KEY `wallet_user_id` (`user_id`),
  KEY `wallet_target_manually_adjusted` (`target_manually_adjusted`)
) ENGINE=InnoDB AUTO_INCREMENT=1611167816726 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.02 sec)

钱包交易：

    Table: WALLET_TRANSACTION
Create Table: CREATE TABLE `WALLET_TRANSACTION` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `wallet_id` bigint(20) NOT NULL DEFAULT '0',
  `user_id` bigint(20) NOT NULL DEFAULT '0',
  `merchant_id` bigint(20) DEFAULT NULL,
  `merchant_name` varchar(100) DEFAULT NULL,
  `merchant_logo_url` varchar(200) DEFAULT NULL,
  `product_id` bigint(20) DEFAULT '0',
  `product_redeem_window_id` bigint(20) DEFAULT NULL,
  `offer_id` bigint(20) DEFAULT NULL,
  `outlet_id` bigint(20) DEFAULT NULL,
  `offer_type` varchar(30) DEFAULT NULL,
  `product_delta_type` varchar(30) DEFAULT NULL,
  `external_reference` varchar(100) DEFAULT NULL,
  `client_external_reference` varchar(100) DEFAULT NULL,
  `earned` decimal(8,2) NOT NULL DEFAULT '0.00',
  `spent` decimal(8,2) NOT NULL DEFAULT '0.00',
  `earned_percent` decimal(8,2) DEFAULT '0.00',
  `partner` varchar(10) DEFAULT NULL,
  `type` varchar(25) DEFAULT NULL,
  `status` varchar(100) NOT NULL,
  `note` varchar(1000) DEFAULT NULL,
  `commission` decimal(8,2) DEFAULT '0.00',
  `date_of_transaction` datetime DEFAULT NULL,
  `approved` bit(1) DEFAULT b'0',
  `enabled` bit(1) DEFAULT b'1',
  `deleted` bit(1) DEFAULT b'0',
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(50) DEFAULT NULL,
  `last_modified` datetime DEFAULT CURRENT_TIMESTAMP,
  `last_modified_by` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`,`wallet_id`),
  KEY `wallet_transaction_type` (`type`),
  KEY `wallet_transaction_status` (`status`),
  KEY `wallet_transaction_wallet_id` (`wallet_id`),
  KEY `wallet_transaction_approved` (`approved`),
  KEY `wallet_transaction_spent` (`spent`),
  KEY `wallet_transaction_earned` (`earned`),
  KEY `wallet_transaction_prwid` (`product_redeem_window_id`),
  KEY `wallet_transaction_client_external_reference` (`client_external_reference`)
) ENGINE=InnoDB AUTO_INCREMENT=1611097810451 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

标签： mysqlsqlquery-optimization

解决方案

嗯。. . 您似乎只想要earned匹配行的总和：

update WALLET w
    set total = (select sum(t.earned)
                 from WALLET_TRANSACTION t
                 where t.wallet_id = w.id
                );

我认为没有理由将其分为收费与免费，然后将结果加在一起。

推荐阅读