记一次MySQL查询优化(GROUP BY)

一、

1. 价格表

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;

2. 增量表

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;

3. 慢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

4. 建索引

create index idx_y_m_p_v on increment_electric(count_year,count_month,price_id,increment_value)

5. 优化后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

二、

1. 优化前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。。。。

2. 建立索引

create index idx_tag_m_d_v on increment_electric(tag_id,count_month,count_day,increment_value)

3. 优化后

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...

5. 总结
   group by字段后续索引失效，需要把后续字段加入group by