使用sum很慢的问题

[a21768541]

各位，我现在有一个数据库

CREATE TABLE `wallet_change`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `created_at` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `version` bigint(20) NOT NULL DEFAULT 0,
  `changed_amount` bigint(20) NULL DEFAULT 0,
  `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `last_amount` bigint(20) NULL DEFAULT 0,
  `payment` bigint(20) NULL DEFAULT 0,
  `type` int(11) NULL DEFAULT NULL,
  `wallet` bigint(20) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `FK_ad1017e22d7c4817b0b4511470e`(`payment`) USING BTREE,
  INDEX `FK_5ced2321ddd04917b6acd6fc5c6`(`type`) USING BTREE,
  INDEX `FK_fa6d9c980f1d4146bd3571a8274`(`wallet`) USING BTREE,
  INDEX `index_created_at`(`created_at`) USING BTREE,
  INDEX `index_changed_amount`(`changed_amount`) USING BTREE,
  CONSTRAINT `wallet_change_ibfk_1` FOREIGN KEY (`commission`) REFERENCES `commissions` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `wallet_change_ibfk_2` FOREIGN KEY (`type`) REFERENCES `wallet_change_type` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `wallet_change_ibfk_3` FOREIGN KEY (`wallet`) REFERENCES `wallets` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `wallet_change_ibfk_4` FOREIGN KEY (`order`) REFERENCES `orders` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 5588657 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

现在我需要求出某几种类型的总金额，

SELECT SUM(wh.changed_amount) AS totlaAmount
FROM wallet_change wh
WHERE wh.type IN(31,32,33,34,35,36,37,38,39,7,40,41,42,43) AND wallet =123

整个表数据量大概有500W，速度很慢.. 大概要40秒左右。
请问怎么优化呐

[a21768541]

好象还是没效果...

[AHUA1001]

记得有人说过，数据库是一门玄学，有些时候只能靠祈祷。
我这里没有您的数据库，光是看您这么描述，也只能给您这些意见了。

[AHUA1001]

首先增加索引type+wallet+changed_amount，然后这样写，我只给你写2个，其它的你自己补齐吧。

然后
SELECT
(SELECT SUM(wh.changed_amount) AS totlaAmount
FROM wallet_change wh
WHERE wh.type = 31 AND wallet =123 )
+
(SELECT SUM(wh.changed_amount) AS totlaAmount
FROM wallet_change wh
WHERE wh.type = 32 AND wallet =123 ) FROM DUAL ;

[a21768541]

引用 1 楼 AHUA1001 的回复:

首先增加索引type+wallet+changed_amount，然后这样写，我只给你写2个，其它的你自己补齐吧。 然后 SELECT (SELECT SUM(wh.changed_amount) AS totlaAmount FROM wallet_change wh WHERE wh.type = 31 AND wallet =123 ) + (SELECT SUM(wh.changed_amount) AS totlaAmount FROM wallet_change wh WHERE wh.type = 32 AND wallet =123 ) FROM DUAL ;

type+wallet+changed_amount 是指这三个字段添加成一个联和索引么？ 我现在是三个字段，单独都有建立索引

[AHUA1001]

三个字段，组合起来建立索引。最后一个是字段是changed_amount 。
type和wallet，哪个字段的差异性大，就把那个字段放在第一位。
举例说明，大部分人的名字不会重复，而性别，只有男女两个选择。如果要用姓名和性别建立索引，因为姓名的差异性比性别大，索引姓名在前，性别在后。

[济南大飞哥]

先看下执行计划。。。

[带我飞的云]

建一个type+wallet的组合索引 想一想，你的数据库中： 1. SELECT count(*) FROM wallet_change wh WHERE wh.type IN(31,32,33,34,35,36,37,38,39,7,40,41,42,43) 2. SELECT count(*) AS totlaAmount FROM wallet_change wh WHERE wh.wallet =123 1和2中，哪个数据量大？ 如果1大，使用 SELECT SUM(wh.changed_amount) AS totlaAmount FROM wallet_change wh WHERE wallet =123 AND wh.type IN(31,32,33,34,35,36,37,38,39,7,40,41,42,43) 如果2大，使用 SELECT SUM(wh.changed_amount) AS totlaAmount FROM wallet_change wh WHERE wh.type IN(31,32,33,34,35,36,37,38,39,7,40,41,42,43) AND wallet =123 还有，需要考虑31,32,33,34,35,36,37,38,39,7,40,41,42,43的来源，最好是能存入表中，进行关联

[a21768541]

引用 7 楼 阿代公主 的回复:

建一个type+wallet的组合索引 想一想，你的数据库中： 1. SELECT count(*) FROM wallet_change wh WHERE wh.type IN(31,32,33,34,35,36,37,38,39,7,40,41,42,43) 2. SELECT count(*) AS totlaAmount FROM wallet_change wh WHERE wh.wallet =123 1和2中，哪个数据量大？ 如果1大，使用 SELECT SUM(wh.changed_amount) AS totlaAmount FROM wallet_change wh WHERE wallet =123 AND wh.type IN(31,32,33,34,35,36,37,38,39,7,40,41,42,43) 如果2大，使用 SELECT SUM(wh.changed_amount) AS totlaAmount FROM wallet_change wh WHERE wh.type IN(31,32,33,34,35,36,37,38,39,7,40,41,42,43) AND wallet =123 还有，需要考虑31,32,33,34,35,36,37,38,39,7,40,41,42,43的来源，最好是能存入表中，进行关联

是有一个外键关联表的，但是我发现通过外键关联查询好象更加慢，所以才用单表查询的

[b445208977]

如果type IN这么多已经包含了全部type，那就不要这个条件了 只用wallet =123 应该会快一些