各位，我現在有一個資料庫
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秒左右。
請問怎么優化吶
uj5u.com熱心網友回復：
首先增加索引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 ;
uj5u.com熱心網友回復：
type+wallet+changed_amount 是指這三個欄位添加成一個聯和索引么？ 我現在是三個欄位，單獨都有建立索引
uj5u.com熱心網友回復：
三個欄位，組合起來建立索引。最后一個是欄位是changed_amount 。type和wallet，哪個欄位的差異性大，就把那個欄位放在第一位。
舉例說明，大部分人的名字不會重復，而性別，只有男女兩個選擇。如果要用姓名和性別建立索引，因為姓名的差異性比性別大，索引姓名在前，性別在后。
uj5u.com熱心網友回復：
好象還是沒效果...uj5u.com熱心網友回復：
記得有人說過，資料庫是一門玄學，有些時候只能靠祈禱。我這里沒有您的資料庫，光是看您這么描述，也只能給您這些意見了。
uj5u.com熱心網友回復：
先看下執行計劃。。。uj5u.com熱心網友回復：
建一個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的來源，最好是能存入表中，進行關聯
uj5u.com熱心網友回復：
是有一個外鍵關聯表的，但是我發現通過外鍵關聯查詢好象更加慢，所以才用單表查詢的
uj5u.com熱心網友回復：
如果type IN這么多已經包含了全部type，那就不要這個條件了 只用wallet =123 應該會快一些轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/55804.html
標籤：MySQL