Sql Sever性能优化之指定索引

背景：生产环境sql语句查询过慢（数据总量在350万左右），日志中心一直报警

解决过程：分析无果后，求助于公司的dba，dba分析后建议在语句中指定索引

解决：在sql语句中指定索引，效果相当明显，亲测有效

优化前sql：

select  row_number() over ( order by sc.modifytime desc ) as rownumber ,
        sc.commoditycode as channelcommoditycode ,
        sc.outshopcommoditycode as outitemid ,
        sc.isshelf ,
        sc.originprice ,
        sc.saleprice ,
        sc.createby ,
        sc.createtime ,
        sc.modifytime ,
        sc.storecode ,
        sc.shopcommodityid as channelstoremappingid ,
        sc.shopcommodityname as o2ocommodityname ,
        sc.shopcommoditytitle as o2ocommoditytitle ,
        sc.remark as comment ,
        sc.outshopspucode ,
        sc.modifyby ,
        sc.channelcode
from    dbo.channel_shopcommodity as sc with ( nolock )
where   sc.disabled = 0
        and sc.channelcode = '9000000009'
        and sc.cityid = 'eabbe02f-59e0-46e6-90e7-cd8a89dbb98f';

优化后sql（指定使用索引ix_cityid）：

select  row_number() over ( order by sc.modifytime desc ) as rownumber ,
        sc.commoditycode as channelcommoditycode ,
        sc.outshopcommoditycode as outitemid ,
        sc.isshelf ,
        sc.originprice ,
        sc.saleprice ,
        sc.createby ,
        sc.createtime ,
        sc.modifytime ,
        sc.storecode ,
        sc.shopcommodityid as channelstoremappingid ,
        sc.shopcommodityname as o2ocommodityname ,
        sc.shopcommoditytitle as o2ocommoditytitle ,
        sc.remark as comment ,
        sc.outshopspucode ,
        sc.modifyby ,
        sc.channelcode
from    dbo.channel_shopcommodity as sc with ( nolock, index= [ix_cityid] )
where   sc.disabled = 0
        and sc.channelcode = '9000000009'
        and sc.cityid = 'eabbe02f-59e0-46e6-90e7-cd8a89dbb98f';

注意事项：使用指定索引后，必须保证where条件中有这个筛选条件，否则索引不生效，查询语句耗时仍然会很长

select  row_number() over ( order by sc.modifytime desc ) as rownumber ,
        sc.commoditycode as channelcommoditycode ,
        sc.outshopcommoditycode as outitemid ,
        sc.isshelf ,
        sc.originprice ,
        sc.saleprice ,
        sc.createby ,
        sc.createtime ,
        sc.modifytime ,
        sc.storecode ,
        sc.shopcommodityid as channelstoremappingid ,
        sc.shopcommodityname as o2ocommodityname ,
        sc.shopcommoditytitle as o2ocommoditytitle ,
        sc.remark as comment ,
        sc.outshopspucode ,
        sc.modifyby ,
        sc.channelcode
from    dbo.channel_shopcommodity as sc with ( nolock, index= [ix_cityid] )
where   sc.disabled = 0
        and sc.channelcode = '9000000009'
        and sc.storecode in ( '10000723' );

以下截图为上面语句的耗时：

去除指定索引后再次执行

select  row_number() over ( order by sc.modifytime desc ) as rownumber ,
        sc.commoditycode as channelcommoditycode ,
        sc.outshopcommoditycode as outitemid ,
        sc.isshelf ,
        sc.originprice ,
        sc.saleprice ,
        sc.createby ,
        sc.createtime ,
        sc.modifytime ,
        sc.storecode ,
        sc.shopcommodityid as channelstoremappingid ,
        sc.shopcommodityname as o2ocommodityname ,
        sc.shopcommoditytitle as o2ocommoditytitle ,
        sc.remark as comment ,
        sc.outshopspucode ,
        sc.modifyby ,
        sc.channelcode
from    dbo.channel_shopcommodity as sc with ( nolock )
where   sc.disabled = 0
        and sc.channelcode = '9000000009'
        and sc.storecode in ( '10000723' );

我们会发现耗时明显减少。

由此总结：如果确保某个条件一定会传并且该字段上加了索引，可以使用指定索引提升性能，但是使用指定索引要慎重，避免因为指定索引的where条件不传又引起的查询耗时变长