一次关于top的调优经历

今天抓到一条SQL很耗CPU

代码如下：

SELECT TOP 10 a.userid,a.username,a.c_taste,a.c_envir,a.c_service,a.c_price,dc_bbs_head ,a.pid,pcomment,a.c_taste+a.c_envir+a.c_service,pid,pname,ptime 
from mst_comment a inner join dc_member_bbshead on dc_userid=userid 
WHERE isvalid = '1'  
	and   pid in  (select sub_ceid from sub_chain_enter  where ce_id = '2257') 
ORDER BY gcid DESC

 

执行发现需要5,6秒钟的时间(实际执行的比较频繁)

1.首先检查表的数据量

2.检查索引情况

3.查看执行计划

首先不看执行计划，自己猜测这条语句执行过程：我觉得应该是首先执行select sub_ceid from sub_chain_enter where ce_id = '2257'这条代码，然后再根据pid筛选mst_comment数据。

但是实际情况恰恰相反，首先是mst_comment扫描，然后再查找sub_chain_enter表。如下为实际的执行计划（表之间的link字段都有索引，如上图）

4.猜想数据库的思路

因为SQL语句中有top 10跟order by gcid desc(这个是表mst_comment的主键，从上面的索引情况可以看出),所以对数据库而言，它认为按主键gcid顺序扫描表，然后取前面n条这样的执行方式会比较效率，所以就生成了上图的执行计划。但是因为实际上还有一个条件是pid，而这个值却是很随机的，所以数据库可能需要扫描完所有的数据。所以修改代码，首先拿掉top 10执行，发现瞬间执行完成，所以证明的我的猜想成立，然后接着我把top 10修改为20,30,40,50依次执行，发现到50的时候，数据库就按照新的执行方式来执行SQL了，具体的执行接话如下图(这里指截了top 50的情况，这时候都是索引查找，注意我有把pid in修改为exists，但是不影响执行计划）

5.最后来对比下两条SQL的实际执行情况

 

可以看出效率提高了n倍

6.总结

看来不是有top就一定最好，top也会影响SQL Server的执行计划，这还需要实际情况实际分析

7.改进办法

最简单的就是用强制索引了

SELECT TOP 10 a.userid,a.username,a.c_taste,a.c_envir,a.c_service,a.c_price,dc_bbs_head ,a.pid,pcomment,a.c_taste+a.c_envir+a.c_service,pid,pname,ptime 
from mst_comment a with(index=IX_MST_COMMENT_5) left join dc_member_bbshead on dc_userid=userid 
WHERE isvalid = '1'  
	and   pid in  (select sub_ceid from sub_chain_enter  where ce_id = '2257') 
ORDER BY gcid DESC

declare @rows int
set @rows=10
SELECT TOP (@rows) a.userid,a.username,a.c_taste,a.c_envir,a.c_service,a.c_price,dc_bbs_head ,a.pid,pcomment,a.c_taste+a.c_envir+a.c_service,pid,pname,ptime 
from mst_comment a left join dc_member_bbshead on dc_userid=userid 
WHERE isvalid = '1'  
	and   pid in  (select sub_ceid from sub_chain_enter  where ce_id = '36') ORDER BY gcid DESC
OPTION ( OPTIMIZE FOR (@rows = 50) )


SELECT TOP 10 a.userid,a.username,a.c_taste,a.c_envir,a.c_service,a.c_price,dc_bbs_head ,a.pid,pcomment,a.c_taste+a.c_envir+a.c_service,pid,pname,ptime 
from (sub_chain_enter c inner join mst_comment a on c.sub_ceid=a.pid ) 
	left join dc_member_bbshead on dc_userid=userid 
WHERE isvalid = '1'  
	and c.ce_id = '36'
ORDER BY gcid DESC
option(force order)