深圳智汇创想科技有限公司

归档查询归档数据库：

查询语句：

数量：

DECLARE @CustomerID INT;
SET @CustomerID=1;
DECLARE @IsPay BIT;
SET @IsPay=1;
DECLARE @StartTime DATETIME;
SET @StartTime='2018-02-01 00:00:00';
DECLARE @EndTime DATETIME;
SET @EndTime='2018-04-01 23:59:59'


SELECT Count(1)
FROM   RB_OrderInfo oi WITH(nolock)
       LEFT JOIN RB_OrderSource os WITH(nolock)
         ON oi.CustomerID = os.CustomerID
            AND oi.OrderSourceID = os.ID
       LEFT JOIN E_Country e WITH(nolock)
         ON oi.Country = e.Shorthand
       LEFT JOIN RB_Transport t WITH(nolock)
         ON oi.CustomerID = t.CustomerID
            AND oi.TransportID = t.ID
       LEFT JOIN RB_Transport_API tapi WITH(nolock)
         ON t.Transport_API_ID = tapi.ID
            AND t.CustomerID = tapi.CustomerID
       LEFT JOIN RB_WareHouse wh WITH(nolock)
         ON oi.CustomerID = wh.CustomerID
            AND oi.WareHouseID = wh.ID
       LEFT JOIN RB_Admin rba WITH(nolock)
         ON rba.CustomerID = os.CustomerID
            AND rba.ID = os.SaleAdminID
       LEFT JOIN RB_BusinessTeam bt WITH(nolock)
         ON bt.CustomerID = os.CustomerID
            AND bt.ID = rba.BusinessTeamID
       LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock)
         ON os.CustomerID = api.CustomerID
            AND os.ID = api.OrderSourceID
WHERE  oi.CustomerID = @CustomerID
       AND oi.IsStockOrder = 0
       AND oi.IsPay = @IsPay
       AND oi.AddTime >= @StartTime
       AND oi.AddTime <= @EndTime
OPTION(recompile, maxdop 2) 

分页：

DECLARE @CustomerID INT;
SET @CustomerID=1;
DECLARE @IsPay BIT;
SET @IsPay=1;
DECLARE @StartTime DATETIME;
SET @StartTime='2018-02-01 00:00:00';
DECLARE @EndTime DATETIME;
SET @EndTime='2018-04-01 23:59:59'

SELECT *
FROM   (SELECT Row_number() OVER (ORDER BY AddTime DESC) AS [PageIndex],
               *
        FROM   (SELECT oi.ID,
                       oi.OrderCode,
                       oi.ClientOrderCode,
                       oi.MainOrderCode,
                       oi.MergeOrderCode,
                       oi.SalesRecordNumber,
                       oi.OrderSourceID,
                       oi.ClientUserAccount,
                       oi.OrderStatus,
                       oi.OrderState,
                       oi.IsFirstDeliver,
                       ( CASE
                           WHEN oi.IsPriority > 0 THEN oi.IsPriority
                           ELSE Isnull(os.PriorityNum, 0)
                         END )                                                                                   AS IsPriority,
                       oi.PackagedIDs,
                       oi.IsASingleInTheEnd,
                       oi.PayerId,
                       oi.Email,
                       oi.SellerOrderId,
                       oi.IsPay,
                       oi.CommentType,
                       oi.TransactionId,
                       oi.PaymentType,
                       Isnull(oi.RefundPrice, 0)                                                                 AS RefundPrice,
                       Isnull(oi.ThrowingRate, 0)                                                                AS ThrowingRate,
                       oi.TotalPrice,
                       oi.ExchangeRMB,
                       oi.ExchangeUSD,
                       oi.TransportPay,
                       oi.OtherPayPrice,
                       oi.ProductTotalPrice,
                       oi.PromotionDiscountAmount,
                       oi.CouponCode,
                       oi.CouponMinusPrice,
                       oi.DiscountMinusPrice,
                       oi.AddPayPrice,
                       oi.AddOrderReasonType,
                       oi.ShareType,
                       oi.ShareCustomerID,
                       oi.IsAccount,
                       oi.AccountTime,
                       oi.ShareTotalPrice,
                       oi.SynchronousTime,
                       oi.IsSynchronous,
                       oi.IsTogeInvoice,
                       Isnull(oi.Encrypted, 0)                                                                   AS Encrypted,
                       oi.FirstName,
                       oi.LastName,
                       oi.Address,
                       oi.District,
                       oi.CabinetNO,
                       oi.City,
                       oi.Province,
                       oi.Country,
                       oi.PostCode,
                       oi.Telephone,
                       oi.ShippingService,
                       oi.DownLoadUpdateTime,
                       oi.PromotionType,
                       oi.TrackNumberTemp,
                       oi.TrackNumbers,
                       oi.UpdatePlatformTime,
                       oi.MarkDeliveryState,
                       oi.EMPSDeliveryState,
                       oi.LogisticsOrderNo,
                       oi.LogisticsOrderNo1,
                       oi.ShipTime,
                       oi.OrderDescription,
                       oi.ISsynchOverseas,
                       oi.ProductNumber,
                       oi.ProductVolume,
                       oi.ProductWeight,
                       oi.PayTime,
                       oi.AddTime,
                       oi.UpdateTime,
                       oi.LatestShipDate,
                       oi.ServiceAdminName,
                       oi.SKUNumber,
                       oi.LatestDeliveryDate,
                       oi.VipDiscount,
                       Isnull(oi.IsTaxFree, 0)                                                                   IsTaxFree,
                       oi.Currency,
                       os.ServiceAdminName                                                                       AS ServiceOSAdminName,
                       Isnull(oi.IsFBATransit, 0)                                                                IsFBATransit,
                       oi.WareHouseID,
                       wh.WareHouseName,
                       os.OrderSourceName,
                       os.OrderSourceType,
                       os.OrderSourceCountry,
                       e.Country_cn,
                       t.TransportName,
                       t.PMSTransport,
                       tapi.Transport_API_Class,
                       wh.WareHouseType,
                       oi.Token,
                       os.SaleAdminName,
                       os.VATNumber,
                       os.VATRate,
                       oi.SaveTrackNumbersTime,
                       t.TrackingNumberValidDays,
                       Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay,
                       bt.BusinessTeamName,
                       t.ID                                                                                      AS TransportID,
                       e.ID                                                                                      AS CountryID,
                       api.CertIDAccessKeyId,
                       (SELECT Count(1)
                        FROM   RB_OrderInfo_Attach att(nolock)
                        WHERE  att.CustomerID = oi.CustomerID
                               AND att.OrderCode = oi.OrderCode)                                                 AS IsAttachMail
                FROM   RB_OrderInfo oi WITH(nolock)
                       LEFT JOIN RB_OrderSource os WITH(nolock)
                         ON oi.CustomerID = os.CustomerID
                            AND oi.OrderSourceID = os.ID
                       LEFT JOIN E_Country e WITH(nolock)
                         ON oi.Country = e.Shorthand
                       LEFT JOIN RB_Transport t WITH(nolock)
                         ON oi.CustomerID = t.CustomerID
                            AND oi.TransportID = t.ID
                       LEFT JOIN RB_Transport_API tapi WITH(nolock)
                         ON t.Transport_API_ID = tapi.ID
                            AND t.CustomerID = tapi.CustomerID
                       LEFT JOIN RB_WareHouse wh WITH(nolock)
                         ON oi.CustomerID = wh.CustomerID
                            AND oi.WareHouseID = wh.ID
                       LEFT JOIN RB_Admin rba WITH(nolock)
                         ON rba.CustomerID = os.CustomerID
                            AND rba.ID = os.SaleAdminID
                       LEFT JOIN RB_BusinessTeam bt WITH(nolock)
                         ON bt.CustomerID = os.CustomerID
                            AND bt.ID = rba.BusinessTeamID
                       LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock)
                         ON os.CustomerID = api.CustomerID
                            AND os.ID = api.OrderSourceID
                WHERE  oi.CustomerID = @CustomerID
                       AND oi.IsStockOrder = 0
                       AND oi.IsPay = @IsPay
                       AND oi.AddTime >= @StartTime
                       AND oi.AddTime <= @EndTime) AS TempTable) AS PagerTable
WHERE  PagerTable.[PageIndex] BETWEEN 1 AND 20
OPTION(recompile, maxdop 2) 

数据库是：158归档库

麻烦看一下是否有优化空间

问题分析：

1.归档库是刚迁移到华为云的sqlserver数据库，

第1个count查询，分析执行计划正确使用了索引，1秒查询出结果了

第2个查询多列的，分析了执行计划，缺少索引，已添加索引，测试很快，1秒出结果 52行数据

2解决方案：

归档库查询2个sql优化结果：

1.count 查询很快；

2. 多列查询，添加了索引IX_RB_Admin_ID_CustomerID_BusinessTeamID，测试也很快，1秒出结果的，在测试看看

麻烦确认一下

正常订单：

归档订单：

分支svn提交记录：