两表join不走索引，如何解决

[darkread]

exchange_record 12376372条记录
字段
gid varchar 36 主键
channel_gid varchar 36
exbuff_cnt int 11
ts datetime 0
st tinyint 4
st tinyint 4
索引 channel_gid,st BTREE

channel 15条记录，符合src=2的是10条
字段
gid varchar 36 主键
src int 11
dst int 11
seckey varchar 128
ds_gid varchar 36
tbl varchar 128
pk varchar 64
st tinyint 4
索引 无

select r.gid from exchange_record r join channel c on r.channel_gid=c.gid where r.st=1 and c.src=2 limit 0, 20000

执行时间 1788 秒

select r.gid from channel c join exchange_record r on r.channel_gid=c.gid where r.st=1 and c.src=2 limit 0, 20000

执行时间2000+ 秒

SELECT r.gid from exchange_record r where r.st=1 and r.channel_gid = '2974a96...' 
UNION
SELECT r.gid from exchange_record r where r.st=1 and r.channel_gid = '5acd4db...' 
UNION
SELECT r.gid from exchange_record r where r.st=1 and r.channel_gid = '5fff5482-...' 
UNION
SELECT r.gid from exchange_record r where r.st=1 and r.channel_gid = '61a00107...' 
UNION
SELECT r.gid from exchange_record r where r.st=1 and r.channel_gid = '69d80226...' 
UNION
SELECT r.gid from exchange_record r where r.st=1 and r.channel_gid = '6e809973...' 
UNION
SELECT r.gid from exchange_record r where r.st=1 and r.channel_gid = '7300dbd5...' 
UNION
SELECT r.gid from exchange_record r where r.st=1 and r.channel_gid = '77e8be0a...' 
UNION
SELECT r.gid from exchange_record r where r.st=1 and r.channel_gid = '79e73120...' 
UNION
SELECT r.gid from exchange_record r where r.st=1 and r.channel_gid = '7eeb2d21...' LIMIT 0,20000

执行时间34.88秒

[zjcxc]

去掉 limit 呢？

[zjcxc]

另外，给出 explain 的结果

[iamboy_11]

根据你提供的信息，我创建这两张表，然后用explain 分析你的查询语句发现是用到了索引的

[darkread]

这是join的explain


这是提取 channel_gid后 union的 explain


这是show table create exchange_record的结果

select r.gid from exchange_record r where 
exists(select * from channel c where r.channel_gid=c.gid )
r.st=1 and c.src=2 limit 0, 20000

就从2000秒降到了0.6秒，挺尴尬的。而且 explain和本截图1同。

[iamboy_11]

图片已经贴得很清楚了，explain的key栏位不就是用到索引了吗？

[Rotel-刘志东]

看一下执行计划