Являются ли более узкие индексы лишними... или нет?!

добавлено: 07 мар 12
понравилось:0
просмотров: 2120
комментов: 3

теги:

Автор: Andraptor

В догонку к предыдущей записи блога, где я выкладывал скрипт поиска перекрывающихся индексов http://www.sql.ru/blogs/andraptor/1218, мне хотелось бы порассуждать, почему я считаю такие индексы лишними.
Итак, для примера берем табличку Production.WorkOrder из базы AdventureWorks2008R2.
Эта таблица содержит некластерный индекс IX_WorkOrder_ProductID по полю ProductID
1. Создаем еще 2-а индекса, где первым полем будет ProductID
CREATE NONCLUSTERED INDEX IX_ProdID_StartDate
	ON Production.WorkOrder (ProductID,StartDate)
GO
CREATE NONCLUSTERED INDEX IX_ProdID_StartDate_EndDate
	ON Production.WorkOrder (ProductID,StartDate,EndDate)


2. Выполняем запрос, используя индекс IX_WorkOrder_ProductID
declare  @dbid int
set @dbid = db_id('AdventureWorks2008R2')
--очищаем процедурный кэш для базы
dbcc flushprocindb(@dbid)
go
set statistics profile on
set statistics io on

select * from Production.WorkOrder with (index=IX_WorkOrder_ProductID)
where ProductID=732 

set statistics profile off
set statistics io off


Table 'WorkOrder'. Scan count 1, logical reads 44, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

RowsExecutesStmtTextEstimateRowsEstimateIOEstimateCPUTotalSubtreeCostAvgRowSize
211select * from Production.WorkOrder with (index=IX_WorkOrder_ProductID) where ProductID=73221NULLNULL0,06809694NULL
00 |--Compute Scalar(DEFINE:([AdventureWorks2008R2].[Production].[WorkOrder].[StockedQty]=[AdventureWorks2008R2].[Production].[WorkOrder].[StockedQty]))2102,1E-060,0680969460
211 |--Nested Loops(Inner Join, OUTER REFERENCES:([AdventureWorks2008R2].[Production].[WorkOrder].[WorkOrderID]))2108,778E-050,0680948460
211 |--Index Seek(OBJECT:([AdventureWorks2008R2].[Production].[WorkOrder].[IX_WorkOrder_ProductID]), SEEK:([AdventureWorks2008R2].[Production].[WorkOrder].[ProductID]=(732)) ORDERED FORWARD)210,0031250,00018010,003305115
00 |--Compute Scalar(DEFINE:([AdventureWorks2008R2].[Production].[WorkOrder].[StockedQty]=isnull([AdventureWorks2008R2].[Production].[WorkOrder].[OrderQty]-CONVERT_IMPLICIT(int,[AdventureWorks2008R2].[Production].[WorkOrder].[ScrappedQty],0),(0))))101E-070,0647019651
2121 |--Clustered Index Seek(OBJECT:([AdventureWorks2008R2].[Production].[WorkOrder].[PK_WorkOrder_WorkOrderID]), SEEK:([AdventureWorks2008R2].[Production].[WorkOrder].[WorkOrderID]=[AdventureWorks2008R2].[Production].[WorkOrder].[WorkOrderID]) LOOKUP ORDERED FORWARD)10,0031250,00015810,0646998647


3. Выполняем запрос, используя индекс IX_ProdID_StartDate
declare  @dbid int
set @dbid = db_id('AdventureWorks2008R2')
--очищаем процедурный кэш для базы
dbcc flushprocindb(@dbid)
go
set statistics profile on
set statistics io on

select * from Production.WorkOrder with (index=IX_ProdID_StartDate)
where ProductID=732 

set statistics profile off
set statistics io off


Table 'WorkOrder'. Scan count 1, logical reads 44, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

RowsExecutesStmtTextEstimateRowsEstimateIOEstimateCPUTotalSubtreeCostAvgRowSize
211select * from Production.WorkOrder with (index=IX_ProdID_StartDate) where ProductID=73221NULLNULL0,06809694NULL
00 |--Compute Scalar(DEFINE:([AdventureWorks2008R2].[Production].[WorkOrder].[StockedQty]=[AdventureWorks2008R2].[Production].[WorkOrder].[StockedQty]))2102,1E-060,0680969460
211 |--Nested Loops(Inner Join, OUTER REFERENCES:([AdventureWorks2008R2].[Production].[WorkOrder].[WorkOrderID]))2108,778E-050,0680948460
211 |--Index Seek(OBJECT:([AdventureWorks2008R2].[Production].[WorkOrder].[IX_ProdID_StartDate]), SEEK:([AdventureWorks2008R2].[Production].[WorkOrder].[ProductID]=(732)) ORDERED FORWARD)210,0031250,00018010,003305123
00 |--Compute Scalar(DEFINE:([AdventureWorks2008R2].[Production].[WorkOrder].[StockedQty]=isnull([AdventureWorks2008R2].[Production].[WorkOrder].[OrderQty]-CONVERT_IMPLICIT(int,[AdventureWorks2008R2].[Production].[WorkOrder].[ScrappedQty],0),(0))))101E-070,0647019643
2121 |--Clustered Index Seek(OBJECT:([AdventureWorks2008R2].[Production].[WorkOrder].[PK_WorkOrder_WorkOrderID]), SEEK:([AdventureWorks2008R2].[Production].[WorkOrder].[WorkOrderID]=[AdventureWorks2008R2].[Production].[WorkOrder].[WorkOrderID]) LOOKUP ORDERED FORWARD)10,0031250,00015810,0646998639


3. Выполняем запрос, используя индекс IX_ProdID_StartDate_EndDate
declare  @dbid int
set @dbid = db_id('AdventureWorks2008R2')
--очищаем процедурный кэш для базы
dbcc flushprocindb(@dbid)
go
set statistics profile on
set statistics io on

select * from Production.WorkOrder with (index=IX_ProdID_StartDate_EndDate)
where ProductID=732 

set statistics profile off
set statistics io off


Table 'WorkOrder'. Scan count 1, logical reads 45, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

RowsExecutesStmtTextEstimateRowsEstimateIOEstimateCPUTotalSubtreeCostAvgRowSize
211select * from Production.WorkOrder with (index=IX_ProdID_StartDate_EndDate) where ProductID=73221NULLNULL0,06809694NULL
00 |--Compute Scalar(DEFINE:([AdventureWorks2008R2].[Production].[WorkOrder].[StockedQty]=[AdventureWorks2008R2].[Production].[WorkOrder].[StockedQty]))2102,1E-060,0680969460
211 |--Nested Loops(Inner Join, OUTER REFERENCES:([AdventureWorks2008R2].[Production].[WorkOrder].[WorkOrderID]))2108,778E-050,0680948460
211 |--Index Seek(OBJECT:([AdventureWorks2008R2].[Production].[WorkOrder].[IX_ProdID_StartDate_EndDate]), SEEK:([AdventureWorks2008R2].[Production].[WorkOrder].[ProductID]=(732)) ORDERED FORWARD)210,0031250,00018010,003305131
00 |--Compute Scalar(DEFINE:([AdventureWorks2008R2].[Production].[WorkOrder].[StockedQty]=isnull([AdventureWorks2008R2].[Production].[WorkOrder].[OrderQty]-CONVERT_IMPLICIT(int,[AdventureWorks2008R2].[Production].[WorkOrder].[ScrappedQty],0),(0))))101E-070,0647019635
2121 |--Clustered Index Seek(OBJECT:([AdventureWorks2008R2].[Production].[WorkOrder].[PK_WorkOrder_WorkOrderID]), SEEK:([AdventureWorks2008R2].[Production].[WorkOrder].[WorkOrderID]=[AdventureWorks2008R2].[Production].[WorkOrder].[WorkOrderID]) LOOKUP ORDERED FORWARD)10,0031250,00015810,0646998631


Что мы имеем!
Все три запроса выполнились абсолютно идентично, с одинаковой нагрузкой на систему (EstimateIO, EstimateCPU), только последний запрос, с самым широким индексом, сделал на одно логическое чтение больше.
Так давайте решать, стоит ли ради выигрыша в несколько логических чтений, держать еще более узкие индексы, которые в продуктивных средах, особенно на больших таблицах, требуют большое количество ресурсов на сопровождение, не говоря уже о занимаемом месте.
Конечно же не следует забывать, что кластерные индексы не попадают под понятие "Лишние", даже если они перекрываются. Они могут быть, разве что, не оптимально созданы... но это (как говорит Л. Каневский в передаче "Следствие вели") уже совсем другая история.

На основании коментария сделаю оговорку, что если вы решили удалять такие индексы, то не стоит бездумно это делать, а необходимо тестировать систему до и после. Так же рекомендуется заскриптовать удаляемый индекс, чтоб можно было, в случае чего, его восстановить. Каждый случай индивидуальный, я лишь пытался отразить некую тенденцию и свои субъективные умозаключения. Но в целом, я считаю, чистить систему от таких индексов необходимо.

Комментарии


  • Ну зачем так категорично? Давайте лучше думать в каждом конкретном случае.

    drop table dbo.test
    go

    create table dbo.test (
    a int not null
    , b datetime not null
    , c varchar(100) not null
    )
    go

    insert into dbo.test (
    a
    , b
    , c
    )
    select top (1000000)
    abs(checksum(newid())) % 100
    , dateadd(dd, checksum(newid()) % 365, '20120101')
    , replicate(char(abs(checksum(newid())) % 100 + 32), 100)
    from master..sysobjects s1
    cross join master..sysobjects s2
    cross join master..sysobjects s3
    go

    create index ix_a on dbo.test(a)
    create index ix_ab on dbo.test(a, b)
    create index ix_abc on dbo.test(a, b, c)
    go

    set statistics io on
    go

    select a
    from dbo.test with(index(ix_a))
    where a = 42
    go

    select a
    from dbo.test with(index(ix_ab))
    where a = 42
    go

    select a
    from dbo.test with(index(ix_abc))
    where a = 42
    go

    set statistics io off
    go

  • Спасибо за комментарий!
    Но я все равно считаю что на таблице миллионнике, при выборке 10000 записей, разница в 120 логических чтений попадает под понятие "несколько", и не стоит избыточности в виде более узких индексов.
    А думать нужно всегда, тут я с Вами полностью согласен!
    :-)

  • Индексы к сожалению (или к счастью?) не всегда используются для поиска, иногда требуется сделать сканирование всего индекса, и тут как раз разница может быть весьма существенна. Просканировать узкий индекс может быть в разы быстрее чем широкий, особенно если в широком еще и куча INCLUDE полей для того чтобы сделать его покрывающим. Узкий индекс дешевле держать в памяти, чем широкий.



Необходимо войти на сайт, чтобы оставлять комментарии