mysql 分组合计sql求优化

[lnkToKing]

有两表T1，T2。

T1约9000条数据，有以下列：
id,eid,name

T2约50000条数据，有以下列：
id,eid,num


现要查出T1表中eid在T2表中同一个eid的num合计，其中求合计后的数据总数sql如下，耗时5秒，求优化：

SELECT COUNT(*) FROM(
SELECT T1.id,T1.eid,T1.name, t.num FROM T1
LEFT JOIN (SELECT eid, COUNT(*) AS num FROM T2 GROUP BY eid) t ON t.eid = T1.eid
WHERE t.num > 0
) t

[lnkToKing]

引用 5 楼 ACMAIN_CHM 的回复:

[quote=引用 2 楼 ACMAIN_CHM 的回复:] 以文本方式贴出 不要贴截图 explain select ... show index from .. 以供分析。 另外描述一下SQL的的业务逻辑是什么，这样不需要别人从你的SQL语句去反推逻辑。

这些内容贴出以供分析。[/quote] 实际业务比这复杂N倍的，不可能也不能全贴出，这是我找出来最终的问题所在，就是这两个表的关联查询引起的，所以只能这样简单的描述一下主要关联

[致命的西瓜]

SELECT T1.eid,COUNT(T2.eid)AS num FROM T1 
	LEFT JOIN T2 ON T1.eid = T2.eid
		GROUP BY T2.eid
			WHERE num > 0

两个eid 都加上索引

[ACMAIN_CHM]

以文本方式贴出 不要贴截图 explain select ... show index from .. 以供分析。 另外描述一下SQL的的业务逻辑是什么，这样不需要别人从你的SQL语句去反推逻辑。

[lnkToKing]

引用 1 楼 wangjian0228 的回复:

SELECT T1.eid,COUNT(T2.eid)AS num FROM T1 
	LEFT JOIN T2 ON T1.eid = T2.eid
		GROUP BY T2.eid
			WHERE num > 0

两个eid 都加上索引

这个应该不行吧，LEFT JOIN T2 的话，T2.eid可能null，再GROUP BY T2.eid会把为null的分成一组

[lnkToKing]

引用 2 楼 ACMAIN_CHM 的回复:

以文本方式贴出 不要贴截图 explain select ... show index from .. 以供分析。 另外描述一下SQL的的业务逻辑是什么，这样不需要别人从你的SQL语句去反推逻辑。

业务是查出T1表的数据，并算出T1.eid对应T2表中的num总数合计，上面的语句是求总记录数，用来算分页的。其它子查询的count(*) as num 写错了，应该是sum(num) as num的

[ACMAIN_CHM]

引用 2 楼 ACMAIN_CHM 的回复:

以文本方式贴出 不要贴截图 explain select ... show index from .. 以供分析。 另外描述一下SQL的的业务逻辑是什么，这样不需要别人从你的SQL语句去反推逻辑。

这些内容贴出以供分析。

[中国风]

用以下语句测测

select count(*) from T1 where exists(select * from T2 where eid=T1.eid);

[中国风]

在eid列上建上索引

[summerwindcool]

版主说的对 没有explain的话 不好定位你的问题的 但根据经验，T1上建一个（eid,id,name）的索引，T2上建一个（eid）的索引应该就可以了，使用覆盖索引。其他的改写和优化mysql自己会做，关键explain有没有用到这些索引。

[lnkToKing]

引用 9 楼 summerwindcool 的回复:

版主说的对 没有explain的话 不好定位你的问题的

但根据经验，T1上建一个（eid,id,name）的索引，T2上建一个（eid）的索引应该就可以了，使用覆盖索引。其他的改写和优化mysql自己会做，关键explain有没有用到这些索引。

DROP VIEW IF EXISTS v_work_statistical_psm_;
CREATE
    VIEW v_work_statistical_psm_ AS
SELECT eid AS eid, COUNT(*) AS num FROM t_cim_powersystemresource WHERE eid IS NOT NULL GROUP BY eid ORDER BY NULL;

--这里查询要2秒多
SELECT COUNT(*) FROM (
SELECT p.record_id, NULL, 
(SELECT num FROM v_work_statistical_psm_ WHERE eid = p.eid LIMIT 1) AS equipmentNumber
FROM t_work_patrol_plan p
UNION ALL
SELECT plm.record_id, plm.id,
(SELECT num FROM v_work_statistical_psm_ WHERE eid = plm.eid LIMIT 1) AS equipmentNumber
FROM t_work_patrollogmaster plm
WHERE plm.record_id = -1 OR plm.record_id IS NULL
) t

[lnkToKing]

最后查到有个查询字段是varchar(2000)太长，联表查询使用的是ALL类型，要返回全部结果，所以很慢。解决办法：二次查询，这个字段另外单独查