CREATE TABLE `electricprice` (
`ID` int NOT NULL AUTO_INCREMENT,
`PriceName` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`PriceValue` float NULL DEFAULT NULL,
`PriceUnit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
增量表
CREATE TABLE `increment_electric` (
`id` int NOT NULL AUTO_INCREMENT,
`tag_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`increment_value` float NULL DEFAULT NULL,
`price_id` tinyint NULL DEFAULT NULL,
`count_time` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`count_year` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`count_month` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`count_day` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`count_hour` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_y_m_v`(`count_year`, `count_month`, `increment_value`) USING BTREE,
INDEX `idx_m_d_v`(`count_month`, `count_day`, `increment_value`) USING BTREE,
INDEX `idx_d_h_v`(`count_day`, `count_hour`, `increment_value`) USING BTREE,
USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 938653 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
慢SQL
select count_month, sum(increment_value * PriceValue) as cost
from increment_electric INNER JOIN electricprice
on price_id = electricprice.ID
where count_year = "2021"
GROUP BY count_month order by count_month
查询时间:3.306s
建索引
create index idx_y_m_p_v on increment_electric(count_year,count_month,price_id,increment_value)
优化后SQL
select count_month, sum(cost) as cost
from
(select count_month, sum(increment_value * PriceValue) as cost
from increment_electric INNER JOIN electricprice
on price_id = electricprice.ID
where count_year = "2021"
GROUP BY count_month, price_id order by count_month) as a
GROUP BY count_month
查询时间:0.456s
二、
优化前SQL
SELECT area, count_month, count_day, sum(increment_value) as sum
FROM meters
left join equipmenttag on equipmenttag.EquipmentID = meters.id
LEFT JOIN increment_electric on equipmenttag.tag_id = increment_electric.tag_id
where count_month = "2021-11"
GROUP BY area, count_day
查询时间:22s。。。。
建立索引
create index idx_tag_m_d_v on increment_electric(tag_id,count_month,count_day,increment_value)
优化后
SELECT area, count_month, count_day, sum(increment_value) as sum
FROM meters
left join equipmenttag on equipmenttag.EquipmentID = meters.id
LEFT JOIN increment_electric on equipmenttag.tag_id = increment_electric.tag_id
where count_month = "2021-11"
GROUP BY area, count_day
查询时间:0.526s
ohohohohohoh...