1.问题分析
归档库查询，测试同事提的bug，报小黄鸡，分析sql发现 缺少索引，
解决方案：
添加索引 IX_RB_Admin_ID_CustomerID_BusinessTeamID
测试 1秒查询出结果
2.SQL
分页：
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)