MySQL关联查询慢

2019年10月28日 506点热度 0人点赞 0条评论

一个简单的关联查询缓慢,出不来结果,SQL语句如下:
select * from kktongjibiao a
where a.content_id not in (select content from kkjiuwangzhanID b);
其中a表数据量424508,b表数据量203619。
经实验模拟,发现是关联字段类型不一致导致,a表字段是数字,b表字段是字符。
以下记录了详细的模拟过程。

create table kktongjibiao as
select jc_content.content_id content_id,jc_content.CHANNEL_ID a,jc_channel_ext.channel_name b,jc_content.SITE_ID c,jc_site.DESCRIPTION d,jc_content_ext.title e
from jc_content_ext
join jc_content   on  jc_content_ext.content_id = jc_content.content_id
join jc_channel_ext on jc_channel_ext.channel_id = jc_content.CHANNEL_ID
join jc_site on jc_site.site_id = jc_content.site_id;
6s
select count(*) from kktongjibiao;
424508
drop table kktongjibiao;

create table kkjiuwangzhanID as
select cast(substring(a,4) as signed) content from (select substring_index(content,';',1) a from jc_log where
log_time > '2019-9-12 00:00:00' and
log_time < '2019-9-16 00:00:00' and
title = '增加文章') jc_log;
1s
select count(*) from kkjiuwangzhanID;
203619
drop table kkjiuwangzhanID;

create index idx_kktongjibiao_cntid on kktongjibiao(content_id);
create index idx_kkjiuwangzhanID_cnt on kkjiuwangzhanID(content);

select * from kktongjibiao where content_id = 1767;
select * from kkjiuwangzhanID where content = 1567667;
select min(content) from kkjiuwangzhanID;
1566846
select max(content) from kkjiuwangzhanID;
1771276
select max(content)-min(content) from kkjiuwangzhanID;
204430

select * from kktongjibiao a
where a.content_id not in (select content from kkjiuwangzhanID b);

liking

我是雪人

文章评论