sql разлики между индексни полета и индекс с включени полета

Подобрявах производителността на някои sql скриптове за заявки. Например:

SELECT * 
FROM Book b, Library l, [Order] o 
WHERE o.bookid = b.bookID 
AND o.mode = 'A' 
AND o.library_ID = l.library_ID 
AND l.library_ID > 19 
AND b.publisher_id  > 1000 
AND b.print_id > 800 
AND NOT EXISTS (
  SELECT * 
  FROM ExtBOOK 
  WHERE b.bookid = extbookid 
  AND library_ID = l.library_ID
) 
AND o.activated = 'Y' 
AND b.eisbn13 LIKE '978%' 
AND len(o.ext_user_id) > 3 
AND b.bookid > 200000 
AND b.bookid in (
  SELECT bookid 
  FROM category 
  WHERE categoryid  > 2
) 
ORDER BY o.orderid DESC

Когато търся този sql скрипт в SQL Management Studio с „Включи действителен план за изпълнение“, резултатът ме моли да добавя по-долу индекс

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Order] ([MODE],[ACTIVATED],[LIBRARY_ID],[BOOKID])
INCLUDE ([OrderID],[EXT_USER_ID],[APPROVAL_DATE])

Въпреки това, ако изтрия някои условия, като по-долу:

SELECT * FROM Book b, Library l, [Order] o 
WHERE o.bookid = b.bookID 
AND o.mode = 'A' 
AND o.library_ID = l.library_ID 
AND l.library_ID > 19 
ORDER BY o.orderid DESC

Получих друга различна препоръка, като по-долу:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[ORDER] ([MODE])
INCLUDE ([LIBRARY_ID],[BOOKID])

Тъй като условията са променливи, кой индекс трябва да създам? Разбирам какво правят индексите, но не и ползата между полето, което е индексирано, и полето, което е включено. Защо в първия препоръчан индекс BOOKID и LIBRARY_ID са в полетата на индекса, но във втория препоръчан индекс BOOKID и LIBRARY_ID са във включените полета? Какви са разликите и кои трябва да използвам, за да покрия всички възможни условия?

Освен това, от моя тест, добавих всеки от тях, за да тествам производителността, но не виждам разлика. Оценявам всяка помощ.


sql performance tsql indexing non-clustered-index
person danmiao    schedule 20.06.2012    source източник
comment
Индексните колони са част от действителната структура за навигация на индекса, напр. можете да ги използвате в клауза WHERE. Включените колони присъстват само в листовото ниво на индекса - не можете да ги използвате за избиране на редове - и те са там, за да може вашата заявка да бъде удовлетворена (всички колони, поискани от заявката) само от структура на индекса, така че да не се налага да правите скъпо търсене на ключ в действителните данни в таблицата, за да получите една или две допълнителни колони, от които се нуждаете.   -  person marc_s    schedule 20.06.2012
comment
@danmiao: за коя таблица се отнася псевдонимът m (m.libary_ID = l.library_ID)? Това правописна грешка ли е или има друга таблица в заявката?   -  person 8kb    schedule 20.06.2012
comment
Здравей, 8kb, съжалявам, това е моя грешка при въвеждане, това е „o“, актуализирах, благодаря   -  person danmiao    schedule 20.06.2012
comment
Здравейте marc_s, благодаря много, разбрах вашите средства, просто подобен въпрос, мога да използвам включени колони към order by, или трябва да използвам индексни колони към order by, или order by не се нуждае от индекс.   -  person danmiao    schedule 20.06.2012
comment
Ако искате да използвате колона в клауза WHERE или ORDER BY, тя трябва да е част от действителния индекс и да се съхранява в структурата за навигация на индекса. Ако имате колона като включена колона, индексът не може да се използва за ORDER BY   -  person marc_s    schedule 20.06.2012

Отговори (1)


arrow_upward
1
arrow_downward

Вашата стратегия за индексиране ще зависи до известна степен от очакваната променливост на вашите данни - ако вашите данни са стабилни и се актуализират рядко, тогава можете да добавите повече индекси, за да подпомогнете производителността на заявките. Ако обаче вашите данни са непостоянни и се променят често, тогава повече индекси ще доведат до по-бавна производителност, тъй като индексите се регенерират, когато данните се променят.

Зависи и от това колко предсказуеми са вашите заявки - предвидими ли са, в който случай ги капсулирайте в съхранени процедури или параметризирани заявки, или са напълно ad-hoc?

Предполагам, че вече имате индекси на Order.BookID и Order.Library_ID?

Освен това бих преформулирал заявките, за да използвам вътрешния синтаксис за присъединяване - т.е.

SELECT * 
FROM [Order] o 
     INNER JOIN Library l
          ON o.library_ID = l.library_ID  
     INNER JOIN Book b  
          ON o.bookid = b.bookID 
WHERE 
     o.mode = 'A'  
AND 
     l.library_ID > 19
person podiluska    schedule 20.06.2012
comment
Здравей podiluska, наистина имам индекси на Order.BookID и Order.Library_ID, но видях, че просто си променил неявното вътрешно присъединяване към изрично вътрешно присъединяване, има ли разлика между тях за изпълнение на заявката. - person danmiao; 21.06.2012
comment
Не обикновено, но мисля, че прави намерението по-ясно, което може да подобри поддръжката - person podiluska; 21.06.2012