5000w+大表的关联查询,速度奇慢,请SQL高手帮忙看!

pweish 2016-04-25 04:42:59
查询速度很慢,查询语句如下:
SELECT
a.stockid ,
b.freq ,
d.vwap ,
a.exdate,
a.comp ,
c.methodid - 6000000 ,
c.nums ,
c.nums * b.freq / 100
FROM
ana a,
credit b,
result c,
source d
WHERE
a.exdate = '2016-04-15'
AND a.comp = b.comp
AND b.willarise > 0
AND c.methodid = b.methodid
AND c.comp = b.comp
AND d.qtid = a.stockid
AND d.date = a.exdate
ORDER BY b.freq;

explain的结果如下:

各个表的情况如下:
b表比较小,96万多点,索引:
PRIMARY KEY (`sid`),
UNIQUE KEY `methodid` (`methodid`,`comp`) USING BTREE,
KEY `comp` (`comp`) USING BTREE

a,d,都是2300多万条数据,
a表索引:
PRIMARY KEY (`sid`),
UNIQUE KEY `stockid` (`stockid`,`exdate`) USING BTREE,
KEY `iddate` (`sid`,`stockid`,`exdate`) USING BTREE,
KEY `comp1` (`comp`,`arise1`) USING BTREE,
KEY `comp2` (`comp`,`arise2`) USING BTREE,
KEY `comp3` (`comp`,`arise3`) USING BTREE,
KEY `comp4` (`comp`,`arise4`) USING BTREE,
KEY `comp5` (`comp`,`arise5`) USING BTREE,
KEY `comp6` (`comp`,`arise6`) USING BTREE,
KEY `comp7` (`comp`,`arise7`) USING BTREE,
KEY `comp8` (`comp`,`arise8`) USING BTREE,
KEY `comp9` (`comp`,`arise9`) USING BTREE,
KEY `comp10` (`comp`,`arise10`) USING BTREE,

d表的索引:
PRIMARY KEY (`sid`),
KEY `dd` (`qtid`,`date`,`volume`,`value`) USING BTREE,
KEY `date` (`date`) USING BTREE,
KEY `sss` (`sid`,`date`,`volume`,`vwap`) USING BTREE


c表,5000万出头,索引:
PRIMARY KEY (`sid`),
KEY `comp` (`comp`) USING BTREE,
KEY `numsgt0` (`nums`,`gt0`) USING BTREE,
KEY `methodidcomp` (`methodid`,`comp`) USING BTREE


本人不擅长SQL,请高手帮忙优化一下。机器性能不错,插入能每秒4万条左右。
...全文
215 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
pweish 2016-04-28
  • 打赏
  • 举报
回复
引用 6 楼 gikod 的回复:
[quote=引用 4 楼 pweish 的回复:] [quote=引用 2 楼 gikod 的回复:] 虽然b看起来数量少,但是b.willarise > 0过滤可能极差,而且需要再结合a过滤exdate,很可能负荷并不小。 所以应该考虑a上先过滤当天的exdate。 a上的UNIQUE KEY `stockid` (`stockid`,`exdate`) USING BTREE,mysql的range skip scan不行,目前exdate上效率差。 先考虑能不能该成(`exdate`,`stockid`);如果不行,那么就单独建立一个KEY `exdate` (`exdate`) USING BTREE。
b.willarise 就两个数值,1, -1。1的70000多,-1的102万。我换索引试一下,结果给大家反馈。
引用 1 楼 ACMAIN_CHM 的回复:
b 表上没有索引可以利用。 AND b.willarise > 0 ORDER BY b.freq; 或者楼主认为的查询方案是什么?先从哪个表查起会理想一些? 可以考虑强制索引?
这个确实是疏忽,自己没检查出来。从业务的角度而言,从a表先查起来比较立项一些,满足exdate条件的的A表记录也就八九千条,再满足B表条件的也就20以内。其它的表,纯粹是为了获取一些属性信息,没有过滤作用。[/quote] 这样的话基本就没有歧义了,a上用exdate的索引,过滤出8~9千条,这第一步以后的数据集就很小了。 如果用这个方案,对于这个sql,b上的willarise有没有索引就无所谓了。 如果特别需要提高效率,进一步可以考虑(comp, willarise)的包含索引,或者是willarise的partitioin(partition有诸多限制,要谨慎)。[/quote] 感谢!!! 反馈一下速度,原来的应该在10分钟左右,有时候还会超时。现在基本在30多秒,由于服务器还有其它程序,所以耗时不是太固定。
gikod 2016-04-26
  • 打赏
  • 举报
回复
引用 4 楼 pweish 的回复:
[quote=引用 2 楼 gikod 的回复:] 虽然b看起来数量少,但是b.willarise > 0过滤可能极差,而且需要再结合a过滤exdate,很可能负荷并不小。 所以应该考虑a上先过滤当天的exdate。 a上的UNIQUE KEY `stockid` (`stockid`,`exdate`) USING BTREE,mysql的range skip scan不行,目前exdate上效率差。 先考虑能不能该成(`exdate`,`stockid`);如果不行,那么就单独建立一个KEY `exdate` (`exdate`) USING BTREE。
b.willarise 就两个数值,1, -1。1的70000多,-1的102万。我换索引试一下,结果给大家反馈。
引用 1 楼 ACMAIN_CHM 的回复:
b 表上没有索引可以利用。 AND b.willarise > 0 ORDER BY b.freq; 或者楼主认为的查询方案是什么?先从哪个表查起会理想一些? 可以考虑强制索引?
这个确实是疏忽,自己没检查出来。从业务的角度而言,从a表先查起来比较立项一些,满足exdate条件的的A表记录也就八九千条,再满足B表条件的也就20以内。其它的表,纯粹是为了获取一些属性信息,没有过滤作用。[/quote] 这样的话基本就没有歧义了,a上用exdate的索引,过滤出8~9千条,这第一步以后的数据集就很小了。 如果用这个方案,对于这个sql,b上的willarise有没有索引就无所谓了。 如果特别需要提高效率,进一步可以考虑(comp, willarise)的包含索引,或者是willarise的partitioin(partition有诸多限制,要谨慎)。
gikod 2016-04-25
  • 打赏
  • 举报
回复
引用 2 楼 gikod 的回复:
虽然b看起来数量少,但是b.willarise > 0过滤可能极差,而且需要再结合a过滤exdate,很可能负荷并不小。 所以应该考虑a上先过滤当天的exdate。 a上的UNIQUE KEY `stockid` (`stockid`,`exdate`) USING BTREE,mysql的range skip scan不行,目前exdate上效率差。 先考虑能不能该成(`exdate`,`stockid`);如果不行,那么就单独建立一个KEY `exdate` (`exdate`) USING BTREE。
另外,如果这个库的主要操作,都是以exdate为主,能修改程序的话,可以考虑冷热数据分离。 不修改程序的话,可以考虑partition,只是需要慎重考虑partition后的限制。
gikod 2016-04-25
  • 打赏
  • 举报
回复
虽然b看起来数量少,但是b.willarise > 0过滤可能极差,而且需要再结合a过滤exdate,很可能负荷并不小。 所以应该考虑a上先过滤当天的exdate。 a上的UNIQUE KEY `stockid` (`stockid`,`exdate`) USING BTREE,mysql的range skip scan不行,目前exdate上效率差。 先考虑能不能该成(`exdate`,`stockid`);如果不行,那么就单独建立一个KEY `exdate` (`exdate`) USING BTREE。
ACMAIN_CHM 2016-04-25
  • 打赏
  • 举报
回复
b 表上没有索引可以利用。 AND b.willarise > 0 ORDER BY b.freq; 或者楼主认为的查询方案是什么?先从哪个表查起会理想一些? 可以考虑强制索引?
pweish 2016-04-25
  • 打赏
  • 举报
回复
引用 2 楼 gikod 的回复:
虽然b看起来数量少,但是b.willarise > 0过滤可能极差,而且需要再结合a过滤exdate,很可能负荷并不小。 所以应该考虑a上先过滤当天的exdate。 a上的UNIQUE KEY `stockid` (`stockid`,`exdate`) USING BTREE,mysql的range skip scan不行,目前exdate上效率差。 先考虑能不能该成(`exdate`,`stockid`);如果不行,那么就单独建立一个KEY `exdate` (`exdate`) USING BTREE。
b.willarise 就两个数值,1, -1。1的70000多,-1的102万。我换索引试一下,结果给大家反馈。
引用 1 楼 ACMAIN_CHM 的回复:
b 表上没有索引可以利用。 AND b.willarise > 0 ORDER BY b.freq; 或者楼主认为的查询方案是什么?先从哪个表查起会理想一些? 可以考虑强制索引?
这个确实是疏忽,自己没检查出来。从业务的角度而言,从a表先查起来比较立项一些,满足exdate条件的的A表记录也就八九千条,再满足B表条件的也就20以内。其它的表,纯粹是为了获取一些属性信息,没有过滤作用。

56,940

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧