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

[pweish]

查询速度很慢，查询语句如下：
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万条左右。

[pweish]

引用 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以内。其它的表，纯粹是为了获取一些属性信息，没有过滤作用。

[ACMAIN_CHM]

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

[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。

[gikod]

引用 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后的限制。

[ACMAIN_CHM]

当您的问题得到解答后请及时结贴. http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html 8、如何给分和结贴？ http://bbs.csdn.net/help#post_dispose

[gikod]

引用 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有诸多限制，要谨慎)。

[pweish]

引用 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多秒，由于服务器还有其它程序，所以耗时不是太固定。