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

Откуда:
Сообщений: 8768
Добрый день! Объясните пожалуйста как работает индекс с несколькими столбцами? Например есть NONCLUSTER INDEX (C1,C2,C3), а условие запроса только по С1. Данный индекс тогда бесполезен или как?
20 фев 12, 12:46    [12121592]     Ответить | Цитировать Сообщить модератору
 Re: индекс с несколькими столбцами  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
netivan
Добрый день! Объясните пожалуйста как работает индекс с несколькими столбцами? Например есть NONCLUSTER INDEX (C1,C2,C3), а условие запроса только по С1. Данный индекс тогда бесполезен или как?

В данном примере индекс будет полезен (по индексу будет поиск).
Индексы. Теоретические основы.
20 фев 12, 12:49    [12121627]     Ответить | Цитировать Сообщить модератору
 Re: индекс с несколькими столбцами  [new]
netivan
Member

Откуда:
Сообщений: 8768
gds,

статью читал, видимо забыл что-то.
20 фев 12, 12:53    [12121679]     Ответить | Цитировать Сообщить модератору
 Re: индекс с несколькими столбцами  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5116
добавлю, что если в вашем запросе кроме "условие запроса только по С1" есть ещё и выборка C2 и\или C3 то они возьмуться прямо из индекса.
20 фев 12, 12:56    [12121709]     Ответить | Цитировать Сообщить модератору
 Re: индекс с несколькими столбцами  [new]
netivan
Member

Откуда:
Сообщений: 8768
автор
В создании композитного (сложного) индекса участвуют несколько полей таблицы. При создании индекса следует обращать внимание на порядок следования полей в индексе.

Например, если создается индекс по полям Field1, Field2, то он может быть применен только в запросе где в критериях используются оба этих поля. Так же этот индекс будет полезен для условий, построенных для одного Field1. Для одного Field2 этот индекс не может быть применен.

Да, понял. А если в моем примере будет поиск по СОЛ2, тогда видимо индекс бесполезен.
20 фев 12, 12:56    [12121712]     Ответить | Цитировать Сообщить модератору
 Re: индекс с несколькими столбцами  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
netivan
Да, понял. А если в моем примере будет поиск по СОЛ2, тогда видимо индекс бесполезен.
Для поиска да, но в целом для улучшения запроса не факт, если c2 достаточно селективно, то, например, может быть применен просмотр этого индекса, вместо кластерного.
20 фев 12, 13:07    [12121830]     Ответить | Цитировать Сообщить модератору
 Re: индекс с несколькими столбцами  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
netivan
автор
В создании композитного (сложного) индекса участвуют несколько полей таблицы. При создании индекса следует обращать внимание на порядок следования полей в индексе.

Например, если создается индекс по полям Field1, Field2, то он может быть применен только в запросе где в критериях используются оба этих поля. Так же этот индекс будет полезен для условий, построенных для одного Field1. Для одного Field2 этот индекс не может быть применен.

Да, понял. А если в моем примере будет поиск по СОЛ2, тогда видимо индекс бесполезен.

Не факт. возможен скан индекса. Если количество считываемых страниц вашего индекса + кластерного или кучи (при условии вывода полей) будет меньше чем полное сканирование кластерного индекса или кучи. Ну это совсем вкраце.
20 фев 12, 13:28    [12122132]     Ответить | Цитировать Сообщить модератору
 Re: индекс с несколькими столбцами  [new]
lookup - зло
Guest
[quot gds]
netivan
пропущено...Не факт. возможен скан индекса. Если количество считываемых страниц вашего индекса + кластерного или кучи (при условии вывода полей) будет меньше чем полное сканирование кластерного индекса или кучи. Ну это совсем вкраце.



даже если количество страниц которое нужно считать из кластерного индекса будет больше в 10 раз, сервер все равно "свалиться" в его скан, дабы избежать lookup.
20 фев 12, 14:20    [12122777]     Ответить | Цитировать Сообщить модератору
 Re: индекс с несколькими столбцами  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
lookup - зло
gds
пропущено...




даже если количество страниц которое нужно считать из кластерного индекса будет больше в 10 раз, сервер все равно "свалиться" в его скан, дабы избежать lookup.

Серьезно? А если проверить?

Скрипт для тестов был взять отсюда и немного изменен.
use tempdb;
go
create table t(id int identity primary key, a char(50), b char(50) default 'b',c int,d int);
go
with g as(select top (1000) rn = row_number() over (order by(select null)) from master..spt_values v1, master..spt_values v2)
insert t(a,c,d)
select top(20000) 'a'+ replace(str(g2.rn,5),' ','0'),
 g2.rn,
 g2.rn
from 
	g g1
	join g g2 on g1.rn <= g2.rn
order by g2.rn;

-- создаем индекс
create index ix_1 on dbo.t(a,c,d)
go

-- перестраиваем
alter index all on dbo.t rebuild
GO


Проверяем кол-во страниц в индексе
select i.name,st.avg_fragmentation_in_percent,st.page_count 
from sys.dm_db_index_physical_stats(db_id(),object_id('dbo.t'),null,null,'LIMITED') as st
join sys.indexes i on i.object_id = st.object_id and i.index_id = st.index_id

name                      avg_fragmentation_in_percent page_count
------------------------- ---------------------------- --------------------
PK__t__3213E83F07F6335A 0,328947368421053 304
ix_1 0 169

грубо говоря разница получается в 2 раза.

Сперва запускаем поиск по первому полю в индексе
select * from dbo.t
where rtrim (a) = 'a00001'
go

select * from dbo.t
where rtrim (a) = 'a00032'
go


+ план 1-го

select * from dbo.t where rtrim (a) = 'a00001'

|--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[t].[id]))
|--Index Scan(OBJECT:([tempdb].[dbo].[t].[ix_1]), WHERE:(rtrim([tempdb].[dbo].[t].[a])='a00001'))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[t].[PK__t__3213E83F07F6335A]), SEEK:([tempdb].[dbo].[t].[id]=[tempdb].[dbo].[t].[id]) LOOKUP ORDERED FORWARD)

+ план 2-го

select * from dbo.t where rtrim (a) = 'a00032'

|--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[t].[id], [Expr1004]) WITH UNORDERED PREFETCH)
|--Index Scan(OBJECT:([tempdb].[dbo].[t].[ix_1]), WHERE:(rtrim([tempdb].[dbo].[t].[a])='a00032'))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[t].[PK__t__3213E83F07F6335A]), SEEK:([tempdb].[dbo].[t].[id]=[tempdb].[dbo].[t].[id]) LOOKUP ORDERED FORWARD)


А вот уже скан кластерного индекса
select * from dbo.t  where rtrim (a) = 'a00033'


+ план

|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t].[PK__t__3213E83F07F6335A]), WHERE:(rtrim([tempdb].[dbo].[t].[a])='a00033'))


Теперь берем условие по второму полю
Скан индекса с лукапом
select * from dbo.t
where c = 1
go

select * from dbo.t
where c = 32
go


+ план 1-го

select * from dbo.t where c = 1

|--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[t].[id]))
|--Index Scan(OBJECT:([tempdb].[dbo].[t].[ix_1]), WHERE:([tempdb].[dbo].[t].[c]=(1)))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[t].[PK__t__3213E83F07F6335A]), SEEK:([tempdb].[dbo].[t].[id]=[tempdb].[dbo].[t].[id]) LOOKUP ORDERED FORWARD)


+ план 2-го

select * from dbo.t where c = 32

|--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[t].[id], [Expr1004]) WITH UNORDERED PREFETCH)
|--Index Scan(OBJECT:([tempdb].[dbo].[t].[ix_1]), WHERE:([tempdb].[dbo].[t].[c]=(32)))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[t].[PK__t__3213E83F07F6335A]), SEEK:([tempdb].[dbo].[t].[id]=[tempdb].[dbo].[t].[id]) LOOKUP ORDERED FORWARD)


А вот уже поиск по кластерному
select * from dbo.t  where c = 33

+ План

|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t].[PK__t__3213E83F07F6335A]), WHERE:([tempdb].[dbo].[t].[c]=(33)))
20 фев 12, 16:42    [12124513]     Ответить | Цитировать Сообщить модератору
 Re: индекс с несколькими столбцами  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
А если в запросах убрать rtrim, то seek заканчивается на
select * from dbo.t
where a = 'a00089'
go


+ план

|--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[t].[id], [Expr1003]) WITH UNORDERED PREFETCH)
|--Index Seek(OBJECT:([tempdb].[dbo].[t].[ix_1]), SEEK:([tempdb].[dbo].[t].[a]='a00089') ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[t].[PK__t__3213E83F07F6335A]), SEEK:([tempdb].[dbo].[t].[id]=[tempdb].[dbo].[t].[id]) LOOKUP ORDERED FORWARD)

а, скан начинается с
select * from dbo.t
where a = 'a00090'
go

+ план

|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t].[PK__t__3213E83F07F6335A]), WHERE:([tempdb].[dbo].[t].[a]='a00090'))
20 фев 12, 16:47    [12124601]     Ответить | Цитировать Сообщить модератору
 Re: индекс с несколькими столбцами  [new]
lookup - зло
Guest
gds,

lookup - зло
даже если количество страниц которое нужно считать из кластерного индекса будет больше в 10 раз, сервер все равно "свалиться" в его скан, дабы избежать lookup.



тут под количеством страниц я имел ввиду именно кол-во страниц кластерного индекса, т.е. либо поднятое лукапом либо сканном кластерного индекса


т.к. именно для "поднятия" этих страниц серверу и приходиться выбирать м/у лукапом и сканом


в вашем последнем примере таких страниц 90 из 20000, т.е. 0.45%
20 фев 12, 17:19    [12125067]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить