请教，mysql 千万级别数据 索引创建

[qq_38017907]

表有1千万+的数据
sql：
select apkid
, sum(case when actionname='showad' then actioncount else 0 end) as showad
from appsearch_sdk_uuc_useraction
where dt >=20170301 and dt <=20170310
group by apkid

耗时12s

建了（dt，apkid）的联合索引，没有效果。


请教大神，语句或者索引，应该如何优化~

[blizzf99]

select apkid , sum(actioncount) as showad from appsearch_sdk_uuc_useraction where dt >=20170301 and dt <=20170310 and actionname='showad' group by apkid 和这个语句结果应该是一样的吧~

[LongRui888]

你可以创建索引，增加2个字段：（dt，apkid，actionname，actioncount）

[qq_38017907]

引用 1 楼 blizzf99 的回复:

select apkid , sum(actioncount) as showad from appsearch_sdk_uuc_useraction where dt >=20170301 and dt <=20170310 and actionname='showad' group by apkid 和这个语句结果应该是一样的吧~

谢谢，原本是这个样子的，省略了 select apkid, sum(case when actionname='showad' then actioncount else 0 end) as showad, sum(case when actionname='downloadad' then actioncount else 0 end) as downloadad, sum(case when actionname='downloadsuccessad' then actioncount else 0 end) as downloadsuccessad, sum(case when actionname='installad' then actioncount else 0 end) as installad, sum(case when actionname='showres' then actioncount else 0 end) as showres, sum(case when actionname='downloadres' then actioncount else 0 end) as downloadres, sum(case when actionname='downloadsuccessres' then actioncount else 0 end) as downloadsuccessres, sum(case when actionname='installres' then actioncount else 0 end) as installres from appsearch_sdk_uuc_useraction where dt >=20170301 and dt <=20170310 group by apkid

[ACMAIN_CHM]

以文本方式贴出(不要贴图!) explain select .... 及 show index from 以供分析

[zjcxc]

where dt >=20170301 and dt <=20170310  的数据很多，那么索引的效果就差

[cloudmissing]

感觉SQL没什么优化空间了 如果建索引后 还没效果， 那就不能用汇总的方式。 应该在 业务单据每次发生时，实时更新对应的汇总统计字段。 最后取时，取这几个字段就行了。 把压力分摊到每笔业务发生时更新

[Mr_Lin_JX]

感觉apkid这个索引失效了,会产生using filesort，可用explain分析,

[totola147]

能否使用索引取决于你 dt范围内的数据量 如果数据量接近全表数据量10%~30% 是无法使用索引的；这个时候其实是用索引反而更慢，所以查询优化器这么决策可能是对的； 如果你非要使用这个字段上的索引可以吧范围拆开用union all 组合起来 像： select sum(showad) from ( select apkid , sum(actioncount) as showad from appsearch_sdk_uuc_useraction where dt =20170301 and actionname='showad' group by apkid union all select apkid , sum(actioncount) as showad from appsearch_sdk_uuc_useraction where dt =20170302 and actionname='showad' group by apkid ....... )temp