复盖索引的使用说明

Question

据我所知，覆盖的索引应该包括SELECT语句中的所有列。如果是这样，如果我有如下查询怎么办：

SELECT  ActionDate  -- Дата мероприятия
       ,ManagerName -- ФИО менеджера
       ,City        -- Город мероприятия
       ,Organazer   -- Юр.Лицо организатора
       ,[Action]    -- Мероприятие
       ,Category    -- Категория
       ,EnteringProfitability    -- Входящая доходность %
       ,PlaceId
       ,PlaceName
       -- Выручка
       ,SUM(isMetroCashDesk * Price) AS RevenueMetro    --'Выручка:Кассы-метро'
       ,SUM(isRestCashDesk * Price) AS RevenueRest   --'Выручка:Кассы-остальные'
       ,SUM(isPortal2CashDesk * Price) AS RevenuePortal2    --'Выручка:Портал 2.0'
       ,SUM(isEurosetCashDesk * Price) AS RevenueEuroset    --'Выручка:Евросеть'
       ,SUM(isPartnersCashDesk * Price) AS RevenuePartners  --'Выручка:Партнеры (субагенты)'
       ,SUM(isCashDesksPlatforms * Price) AS RevenueCashDesksPlatforms   --'Выручка:Кассы-площадки'
       -- Билеты
       ,SUM(isMetroCashDesk) AS TicketsMetro      --'Билеты:Кассы-метро'
       ,SUM(isRestCashDesk) AS TicketsRest  --'Билеты:Кассы-остальные'
       ,SUM(isPortal2CashDesk) AS TicketsPortal2     --'Билеты:Портал 2'
       ,SUM(isEurosetCashDesk) AS TicketsEuroset     --'Билеты:Евросеть'
       ,SUM(isPartnersCashDesk) AS TicketsPartners   --'Билеты:Партнеры (субагенты)'
       ,SUM(isCashDesksPlatforms) AS TicketsCashDesksPlatforms  --'Билеты:Кассы-площадки'
       -- Доход
       ,SUM(isMetroCashDesk * Income) AS IncomeMetro    --'Доход:Кассы-метро'
       ,SUM(isRestCashDesk * Income) AS IncomeRest   --'Доход:Кассы-остальные'
       ,SUM(isPortal2CashDesk * Income) AS IncomePortal2    --'Доход:Портал 2.0'
       ,SUM(isEurosetCashDesk * Income) AS IncomeEuroset    --'Доход:Евросеть'
       ,SUM(isPartnersCashDesk * Income) AS IncomePartners  --'Доход:Партнеры (субагенты)'
       -- Итого
       ,SUM(Price) AS RevenueTotal  --'Выручка:Итого, руб'
       ,COUNT(*) AS TicketsTotal    --'Билеты:Итого, шт'
       ,SUM(Income) AS IncomeTotal  --'Доход:Итого, руб'
       ,CASE SUM(Price) WHEN 0 THEN NULL ELSE 100.0 * SUM(Income)/SUM(Price) END AS Profitability   --'Доходность:Итого,%'
       -- Без касс-площадок и касс-устроителей
     ,SUM(CASE isCashDesksPlatforms WHEN 0 THEN Price ELSE 0 END) AS RevenueWithoutCashDesksPlatforms --Выручка
     ,SUM(CASE isCashDesksPlatforms WHEN 0 THEN 1 ELSE 0 END) AS TicketsWithoutCashDesksPlatforms --Билеты
     ,SUM(CASE isCashDesksPlatforms WHEN 0 THEN Income ELSE 0 END)  AS IncomeWithoutCashDesksPlatforms    --Доход
     ,CASE SUM(CASE isCashDesksPlatforms WHEN 0 THEN Price ELSE 0 END)
         WHEN 0 THEN NULL
         ELSE 100.0 * SUM(CASE isCashDesksPlatforms WHEN 0 THEN Income ELSE 0 END)/SUM(CASE isCashDesksPlatforms WHEN 0 THEN Price ELSE 0 END)
      END AS ProfitabilityWithoutCashDesksPlatforms --Доходность
FROM    #Goran
GROUP BY ActionDate
      ,ManagerName
      ,City
      ,Organazer
      ,[Action]
      ,Category
      ,EnteringProfitability
      ,PlaceId
      ,PlaceName
ORDER BY ActionDate
      ,ManagerName
      ,City
      ,Organazer
      ,[Action]
      ,Category

我想我不能在覆盖索引中包含SELECT部分的所有列。如果是这样，我如何提高这个查询的性能？

Answer 1

试试这个-

指数：

CREATE NONCLUSTERED INDEX [IX_Goran] ON #Goran
(
       ActionDate
     , ManagerName
     , City
     , Organazer
     , [Action]
     , Category
     , EnteringProfitability
     , PlaceName
)
INCLUDE (isMetroCashDesk, Price, ...)

查询：

SELECT   
       ActionDate
     , ManagerName 
     , City  
     , Organazer 
     , [Action]
     , Category 
     , EnteringProfitability
     , PlaceName

     , RevenueMetro = SUM(isMetroCashDesk * Price)
     , RevenueRest = SUM(isRestCashDesk * Price)
     , RevenuePortal2 = SUM(isPortal2CashDesk * Price)
     , RevenueEuroset = SUM(isEurosetCashDesk * Price)
     , RevenuePartners = SUM(isPartnersCashDesk * Price)
     , RevenueCashDesksPlatforms = SUM(isCashDesksPlatforms * Price)

     , TicketsMetro = SUM(isMetroCashDesk)
     , TicketsRest = SUM(isRestCashDesk)
     , TicketsPortal2 = SUM(isPortal2CashDesk)
     , TicketsEuroset = SUM(isEurosetCashDesk)
     , TicketsPartners = SUM(isPartnersCashDesk)
     , TicketsCashDesksPlatforms = SUM(isCashDesksPlatforms)

     , IncomeMetro = SUM(isMetroCashDesk * Income)
     , IncomeRest = SUM(isRestCashDesk * Income)
     , IncomePortal2 = SUM(isPortal2CashDesk * Income)
     , IncomeEuroset = SUM(isEurosetCashDesk * Income)
     , IncomePartners = SUM(isPartnersCashDesk * Income)

     , RevenueTotal = SUM(Price)
     , TicketsTotal = COUNT(1)
     , IncomeTotal = SUM(Income) 
     , Profitability = CASE WHEN SUM(Price) != 0 THEN 100.0 * SUM(Income) / SUM(Price) END

     , RevenueWithoutCashDesksPlatforms = ISNULL(SUM(CASE WHEN isCashDesksPlatforms = 0 THEN Price END), 0)
     , TicketsWithoutCashDesksPlatforms = ISNULL(SUM(CASE WHEN isCashDesksPlatforms = 0 THEN 1 END), 0)
     , IncomeWithoutCashDesksPlatforms = ISNULL(SUM(CASE WHEN isCashDesksPlatforms = 0 THEN Income END), 0)
     , ProfitabilityWithoutCashDesksPlatforms = 
          CASE WHEN SUM(CASE WHEN isCashDesksPlatforms = 0 THEN Price ELSE 0 END) != 0
               THEN 100.0 * SUM(CASE WHEN isCashDesksPlatforms = 0 THEN Income ELSE 0 END) / SUM(CASE WHEN isCashDesksPlatforms = 0 THEN Price ELSE 0 END)
          END
FROM #Goran
GROUP BY 
       ActionDate
     , ManagerName
     , City
     , Organazer
     , [Action]
     , Category
     , EnteringProfitability
     , PlaceName
ORDER BY 
       ActionDate
     , ManagerName
     , City
     , Organazer
     , [Action]
     , Category

Answer 2

您需要创建的唯一复合索引是这个索引，以便尽可能快地工作：

(ActionDate, ManagerName, City,
 Organizer, [Action], Category,
 EnteringProfitability, PlaceId, PlaceName)

基本上，它应该包含GROUP BY中提到的所有字段的列表(您的ORDER BY可能也会使用此索引)。