Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Composite Indexes  [new]
hey
Guest
Из книжки по опитимизации mssql

Composite, or multicolumn, indexes are slightly more complex. With a composite index, the order of the keys matters. It determines the sort order of the index, and it affects the set of seek predicates that SQL Server can evaluate using the index.

For an easy way to visualize why order matters, think about a phone book. A phone book is like an index with the keys (last name, first name). The contents of the phone book are sorted by last name, and we can easily look someone up if we know their last name. However, if we have only a first name, it is very difficult to get a list of people with that name. We would need another phone book sorted on first name.

In the same way, if we have an index on two columns, we can only use the index to satisfy a predicate on the second column if we have an equality predicate on the first column. Even if we cannot use the index to satisfy the predicate on the second column, we may be able to use it on the first column. In this case, we introduce a "residual" predicate for the predicate on the second column. This predicate is evaluated just like any other scan predicate.

For example, suppose we have a two-column index on columns Col1 and Col2. We can use this index to seek on any of the predicates that worked on the single-column index. We can also use it to seek on these additional predicates:

[Col1] = 3.14 AND [Col2] = 'pi'

[Col1] = 'xyzzy' AND [Col2] <= 0

For the next set of examples, we can use the index to satisfy the predicate on column Col1, but not on column Col2. In these cases, we need a residual predicate for column Col2.

[Col1] > 100 AND [Col2] > 100

[Col1] LIKE 'abc%' AND [Col2] = 2



Не понимаю смысл выделенного - если у нас есть индекс из двух колонок col1 и col2, то почему он не может использоваться для второго случая ?
17 мар 09, 13:15    [6937403]     Ответить | Цитировать Сообщить модератору
 Re: Composite Indexes  [new]
Glory
Member

Откуда:
Сообщений: 104760
hey


Не понимаю смысл выделенного - если у нас есть индекс из двух колонок col1 и col2, то почему он не может использоваться для второго случая ?

например, потому что это невыгодно. Придется прочитать слишком много страниц индекса, т.к. по col1 слишком много записей попадает под фильтр
17 мар 09, 13:21    [6937452]     Ответить | Цитировать Сообщить модератору
 Re: Composite Indexes  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
hey
почему он не может использоваться для второго случая ?
ничего не понял, для какого случая?
для этого
[Col1] > 100 AND [Col2] > 100
?
17 мар 09, 13:22    [6937461]     Ответить | Цитировать Сообщить модератору
 Re: Composite Indexes  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
А что за книжка?
17 мар 09, 13:30    [6937544]     Ответить | Цитировать Сообщить модератору
 Re: Composite Indexes  [new]
hey
Guest
Glory
например, потому что это невыгодно. Придется прочитать слишком много страниц индекса, т.к. по col1 слишком много записей попадает под фильтр

а если записей немного, то он может использоваться ? Судя по цитате, первый предикат обязательно должен быть "равно", а иначе данный индекс для второй колонки не используется. Почему так ?
17 мар 09, 13:31    [6937559]     Ответить | Цитировать Сообщить модератору
 Re: Composite Indexes  [new]
hey
Guest
Inside Microsoft® SQL Server™ 2005: Query Tuning and Optimization
by Kalen Delaney
17 мар 09, 13:32    [6937566]     Ответить | Цитировать Сообщить модератору
 Re: Composite Indexes  [new]
Glory
Member

Откуда:
Сообщений: 104760
hey
Glory
например, потому что это невыгодно. Придется прочитать слишком много страниц индекса, т.к. по col1 слишком много записей попадает под фильтр

а если записей немного, то он может использоваться ? Судя по цитате, первый предикат обязательно должен быть "равно", а иначе данный индекс для второй колонки не используется. Почему так ?

Почему ? Ну автор так выразил свое мнение. Возможно ниже он его обосновал/потдвердил скриптом/планом выполнения.
17 мар 09, 13:35    [6937582]     Ответить | Цитировать Сообщить модератору
 Re: Composite Indexes  [new]
hey
Guest
нет, никаких планов не показано, идет следующий параграф :(
Так все-таки по логике вещей, композитный индекс ведь должен подходить для для поиска по обеим колонкам (в случае and или равно) ?
17 мар 09, 13:38    [6937608]     Ответить | Цитировать Сообщить модератору
 Re: Composite Indexes  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
а на мой вопрос ответите?
17 мар 09, 13:38    [6937614]     Ответить | Цитировать Сообщить модератору
 Re: Composite Indexes  [new]
Glory
Member

Откуда:
Сообщений: 104760
hey
нет, никаких планов не показано, идет следующий параграф :(
Так все-таки по логике вещей, композитный индекс ведь должен подходить для для поиска по обеим колонкам (в случае and или равно) ?

Оптимизатор оперирует понятиями выгодно/невыгодно.
17 мар 09, 13:39    [6937619]     Ответить | Цитировать Сообщить модератору
 Re: Composite Indexes  [new]
hey
Guest
Паганель
а на мой вопрос ответите?

я его просто не совсем понял :)

[Col1] > 100 AND [Col2] > 100

судя по цитате, для этого случая композитный индекс для второй колонки не будет использован. Почему так - непонятно.

Причем использовано слово only, т.е. так всегда, вне зависимости от выгодно/невыгодно

we can only use the index to satisfy a predicate on the second column if we have an equality predicate
17 мар 09, 13:48    [6937702]     Ответить | Цитировать Сообщить модератору
 Re: Composite Indexes  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Пробуйте, сравнивайте:
declare @t table([Col1] int, [Col2] int, unique([Col1], [Col2]))
insert into @t([Col1], [Col2])
select   1,   1 union all
select 100, 100

set statistics profile on
select *
  from @t
 where [Col1] > 100 AND [Col2] > 100

select *
  from @t
 where [Col2] > 100

select *
  from @t
 where [Col1] > 100
set statistics profile off
Может правда, пример и неудачный, просто как иллюстрация,
что вещи, в которых сомневаетесь, в принципе не тяжело проверить
17 мар 09, 13:59    [6937814]     Ответить | Цитировать Сообщить модератору
 Re: Composite Indexes  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> я его просто не совсем понял :)
>
> [Col1]> *100* AND [Col2]> *100*
>
>
> судя по цитате, для этого случая композитный индекс для второй колонки
> не будет использован. Почему так - непонятно.

потому что, при поиске мы можем опуститься к удовлетворяющему условию
значению только по одному пути. а в этом случае таких путей
может быть несколько: то есть, допустим значение
col1 = 1 col2 = 1 - удовлетворяет условию поиска
col1 = 1 col2 = 200 - уже нет
col1 = 2 col2 = 1 - снова удовлетворяет
и т.д.

Posted via ActualForum NNTP Server 1.4

17 мар 09, 14:08    [6937895]     Ответить | Цитировать Сообщить модератору
 Re: Composite Indexes  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
Может так проще будет:

c1	c2
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
1. c1 = 2 and c2 = 2 -- при поиске по B-дереву индекса мы сразу встанем на нужную запись. Оба предиката используются для поиска.

2. c1 = 2 and c2 > 1 -- при поиске мы встанем на листовом уровне на запись с c2 = 2, и потом немного просканируем листовой уровень. И снова оба предиката используются для поиска.

3. c1 > 1 and c2 > 1 -- первый предикат будет использоваться для поиска, в результате мы окажемся на записи (2, 1), а второй предикат для поиска уже никак использовать не удастся. Далее будет выполняться проход по листовому уровню и отфильтровывание записей (residual, о чем и говорит многоуважаемая Delaney). Имеет ли смысл создавать композитный индекс в данном случае? Да, если он будет покрывающим. Начиная с 2005-го, для такого вида условия один из столбцов лучше сделать "included", а индекс построить по столбцу с наибольшим коэффициентом фильтрации.

Отсюда следует rule of thumb при создании индексов:
1. Выбрать все предикаты на равенство, добавить эти столбцы в индекс (в любом порядке, неважно).
2. Из оставшихся выбрать тот предикат, который отфильтровывает больше все записей.
3. Оставшиеся столбцы из предложения where, и столбцы, которые выбираются запросом, но не участвуют в поиске, сделать included.

Пример:
select c1, c3
from atable
where c1 = 2 and c2 = 3 and c3 > 10 and c4 < 100

c3 > 10 удовлетворяют 70% записей, c4 < 100 -- 30%

"Идеальный индекс" для этого запроса будет:
(c1, c2, c4) include(c3)

Как уже сказал Паганель, все это хорошо видно на планах.
10 авг 09, 11:23    [7516416]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить