请教sql语句优化

[ayun00]

select count(1)  FROM  Article_tbl  where IsRead  = 1 and (ArtideImgCount > 0 )  

 and (select count(1) from `imagelist_tbl` where imagelist_tbl.ArticleID =Article_tbl.ArticleID AND (`imagelist_tbl`.`ImageStatus` = 1)  ) >0

表 Article_tbl 有20w记录, imagelist_tbl有250w记录
表 Article_tbl 创建了IsRead, ArtideImgCount, ArticleTime 和 ArticleTime 2个索引
表 imagelist_tbl 创建了 ArticleID, ImageStatus, ImageSize 索引
如果不加imagelist_tbl 的条件, 执行结果0.1s, 如果加上imagelist_tbl 条件 执行结果22s
请教应该如何优化

[二月十六]

不用子查询，用关联试试，类似这样

select count(DISTINCT Article_tbl.id)  FROM  Article_tbl 
 JOIN `imagelist_tbl` ON imagelist_tbl.ArticleID =Article_tbl.ArticleID 
 AND (`imagelist_tbl`.`ImageStatus` = 1)  where IsRead  = 1 and (ArtideImgCount > 0 )  

[ayun00]

引用 1 楼 sinat_28984567 的回复:

不用子查询，用关联试试，类似这样

select count(DISTINCT Article_tbl.id)  FROM  Article_tbl 
 JOIN `imagelist_tbl` ON imagelist_tbl.ArticleID =Article_tbl.ArticleID 
 AND (`imagelist_tbl`.`ImageStatus` = 1)  where IsRead  = 1 and (ArtideImgCount > 0 )  

这个语句执行依然要3s以上, 另外 我是要判断 (select count(1) from `imagelist_tbl` where imagelist_tbl.ArticleID =Article_tbl.ArticleID AND (`imagelist_tbl`.`ImageStatus` = 1) ) >0 这不是join 一下 就行吧?

[二月十六]

引用 2 楼 ayun00 的回复:

[quote=引用 1 楼 sinat_28984567 的回复:] 不用子查询，用关联试试，类似这样

select count(DISTINCT Article_tbl.id)  FROM  Article_tbl 
 JOIN `imagelist_tbl` ON imagelist_tbl.ArticleID =Article_tbl.ArticleID 
 AND (`imagelist_tbl`.`ImageStatus` = 1)  where IsRead  = 1 and (ArtideImgCount > 0 )  

这个语句执行依然要3s以上, 另外 我是要判断 (select count(1) from `imagelist_tbl` where imagelist_tbl.ArticleID =Article_tbl.ArticleID AND (`imagelist_tbl`.`ImageStatus` = 1) ) >0 这不是join 一下 就行吧?[/quote] 就是判断一下是否在这表中存在用

[ayun00]

引用 3 楼 sinat_28984567 的回复:

[quote=引用 2 楼 ayun00 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:] 不用子查询，用关联试试，类似这样

select count(DISTINCT Article_tbl.id)  FROM  Article_tbl 
 JOIN `imagelist_tbl` ON imagelist_tbl.ArticleID =Article_tbl.ArticleID 
 AND (`imagelist_tbl`.`ImageStatus` = 1)  where IsRead  = 1 and (ArtideImgCount > 0 )  

这个语句执行依然要3s以上, 另外 我是要判断 (select count(1) from `imagelist_tbl` where imagelist_tbl.ArticleID =Article_tbl.ArticleID AND (`imagelist_tbl`.`ImageStatus` = 1) ) >0 这不是join 一下 就行吧?[/quote] 就是判断一下是否在这表中存在用[/quote] JOIN  是 只要一边没有值 就不会出现在结果集中吗? 另外刚刚用mssql试了下, 在不加任何索引的情况下, 执行时间都不超过0.1s

[二月十六]

你看是这个意思不

--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([id] int,[name] nvarchar(22))
Insert #T1
select 1,N'张三' union all
select 2,N'李四' union all
select 3,N'王五' union all
select 4,N'赵六'
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([id] int,[tid] int,[name] nvarchar(23))
Insert #T2
select 1,1,N'测试1' union all
select 2,1,N'测试2' union all
select 3,2,N'测试3'
Go
--测试数据结束
Select COUNT(1) from #T1 WHERE EXISTS(SELECT * FROM #T2 WHERE #T1.id=#T2.tid)

SELECT COUNT(DISTINCT #T1.id) FROM #T1 JOIN #T2 ON #T1.id=#T2.tid

[ayun00]

引用 5 楼 sinat_28984567 的回复:

你看是这个意思不

--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([id] int,[name] nvarchar(22))
Insert #T1
select 1,N'张三' union all
select 2,N'李四' union all
select 3,N'王五' union all
select 4,N'赵六'
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([id] int,[tid] int,[name] nvarchar(23))
Insert #T2
select 1,1,N'测试1' union all
select 2,1,N'测试2' union all
select 3,2,N'测试3'
Go
--测试数据结束
Select COUNT(1) from #T1 WHERE EXISTS(SELECT * FROM #T2 WHERE #T1.id=#T2.tid)

SELECT COUNT(DISTINCT #T1.id) FROM #T1 JOIN #T2 ON #T1.id=#T2.tid

恩 是这个意思, imagelist_tbl 是 Article_tbl 的子集, n:1的关系, 要在查询 Article_tbl 子集不为空的总数

[ayun00]

引用 5 楼 sinat_28984567 的回复:

你看是这个意思不

--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([id] int,[name] nvarchar(22))
Insert #T1
select 1,N'张三' union all
select 2,N'李四' union all
select 3,N'王五' union all
select 4,N'赵六'
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([id] int,[tid] int,[name] nvarchar(23))
Insert #T2
select 1,1,N'测试1' union all
select 2,1,N'测试2' union all
select 3,2,N'测试3'
Go
--测试数据结束
Select COUNT(1) from #T1 WHERE EXISTS(SELECT * FROM #T2 WHERE #T1.id=#T2.tid)

SELECT COUNT(DISTINCT #T1.id) FROM #T1 JOIN #T2 ON #T1.id=#T2.tid

执行结果, 速度还要2s左右, 依然很慢

[二月十六]

引用 7楼我是你的主体 的回复:

[quote=引用 5 楼 sinat_28984567 的回复:] 你看是这个意思不

--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([id] int,[name] nvarchar(22))
Insert #T1
select 1,N'张三' union all
select 2,N'李四' union all
select 3,N'王五' union all
select 4,N'赵六'
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([id] int,[tid] int,[name] nvarchar(23))
Insert #T2
select 1,1,N'测试1' union all
select 2,1,N'测试2' union all
select 3,2,N'测试3'
Go
--测试数据结束
Select COUNT(1) from #T1 WHERE EXISTS(SELECT * FROM #T2 WHERE #T1.id=#T2.tid)

SELECT COUNT(DISTINCT #T1.id) FROM #T1 JOIN #T2 ON #T1.id=#T2.tid

执行结果, 速度还要2s左右, 依然很慢[/quote]比22秒快很多了

[ayun00]

引用 8 楼 sinat_28984567 的回复:

[quote=引用 7楼我是你的主体 的回复:][quote=引用 5 楼 sinat_28984567 的回复:] 你看是这个意思不

--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([id] int,[name] nvarchar(22))
Insert #T1
select 1,N'张三' union all
select 2,N'李四' union all
select 3,N'王五' union all
select 4,N'赵六'
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([id] int,[tid] int,[name] nvarchar(23))
Insert #T2
select 1,1,N'测试1' union all
select 2,1,N'测试2' union all
select 3,2,N'测试3'
Go
--测试数据结束
Select COUNT(1) from #T1 WHERE EXISTS(SELECT * FROM #T2 WHERE #T1.id=#T2.tid)

SELECT COUNT(DISTINCT #T1.id) FROM #T1 JOIN #T2 ON #T1.id=#T2.tid

执行结果, 速度还要2s左右, 依然很慢[/quote]比22秒快很多了[/quote] 就不能少于1s吗? 同样的数据量 用mssql 只要0.1s

[ACMAIN_CHM]

以文本方式贴出(不要贴图!) explain select .... 及 show index from 以供分析