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

MySql 码拜 6年前 (2016-02-07) 629次浏览
查询速度很慢,查询语句如下:
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的结果如下:
5000w+大表的关联查询,速度奇慢,请SQL高手帮看!
各个表的情况如下:
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万条左右。
解决方案

10

b 表上没有索引可以利用。
AND b.willarise > 0
ORDER BY b.freq;
或LZ认为的查询方案是什么?先从哪个表查起会理想一些? 可以考虑强制索引?

30

引用 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;
或LZ认为的查询方案是什么?先从哪个表查起会理想一些? 可以考虑强制索引?

这个确实是疏忽,本人没检查出来。从业务的角度而言,从a表先查起来比较立项一些,满足exdate条件的的A表记录也就八九千条,再满足B表条件的也就20以内。其它的表,纯粹是为了获取一些属性信息,没有过滤作用。

这样的话基本就没有歧义了,a上用exdate的索引,过滤出8~9千条,这第一步以后的数据集就很小了。
假如用这个方案,对于这个sql,b上的willarise有没有索引就无所谓了。
假如特别需要提高效率,进一步可以考虑(comp, willarise)的包含索引,或是willarise的partitioin(partition有诸多限制,要谨慎)。


CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明5000w+大表的关联查询,速度奇慢,请SQL高手帮看!
喜欢 (0)
[1034331897@qq.com]
分享 (0)