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條件不傳又引起的查詢耗時變長