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

Откуда:
Сообщений: 314
Всем привет.
На текущем проекте абсолютно все таблицы - кластеризованные.
Меня, как бывшего ораклиста, мучает вопрос- почему никто не использует кучи или используют очень редко?
Оракловый аналог RID Lookup - это table access by index rowid и это там чуть ли не основной оператор для доступа к данным.
Решил замерить производительность оператора select на кучу и на кластеризованную таблицу:

-- кластеризованная таблица
CREATE TABLE dbo.t_sample_clustered 
(
	[ID] INT IDENTITY(1,1) NOT NULL,
	A INT NOT NULL,
	B INT NOT NULL 
	CONSTRAINT [PK_t_sample_clustered] PRIMARY KEY CLUSTERED([ID] )
)

INSERT INTO dbo.t_sample_clustered  (a,b)
SELECT	TOP 1000000 
		ABS(CONVERT(INT, (CONVERT(BINARY(4), (NEWID()))))) % 1000 AS a,
		ABS(CONVERT(INT, (CONVERT(BINARY(4), (NEWID()))))) % 1000 AS b
FROM sysobjects A
CROSS JOIN sysobjects B
CREATE NONCLUSTERED INDEX [IDX_t_sample_clustered_a] ON dbo.t_sample_clustered (a)

UPDATE STATISTICS dbo.t_sample_clustered  WITH FULLSCAN

-- куча
CREATE TABLE dbo.t_sample 
(
	[ID] INT IDENTITY(1,1) NOT NULL,
	A INT NOT NULL,
	B INT NOT NULL 
	CONSTRAINT [PK_t_sample] PRIMARY KEY NONCLUSTERED([ID] )
)
-- заполнение
INSERT INTO dbo.t_sample  (a,b)
SELECT	A,B
FROM dbo.t_sample_clustered 
-- 
CREATE NONCLUSTERED INDEX [IDX_t_sample_a] ON dbo.t_sample (a)


UPDATE STATISTICS dbo.t_sample  WITH FULLSCAN

SELECT t.b
FROM dbo.t_sample_clustered t
WHERE t.a = 45

SELECT t.b
FROM dbo.t_sample t
WHERE t.a = 45

Результаты :
Для кластерной таблицы : Duration = 20,CPU Cost = 31, Reads = 3036
Для кучи : Duration = 8,CPU Cost = 0, Reads = 993
14 май 19, 16:44    [21884607]     Ответить | Цитировать Сообщить модератору
 Re: Key lookup vs RID Lookup  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36317
А теперь сравните скорость выборки по полю id на вашей структуре.
14 май 19, 17:03    [21884622]     Ответить | Цитировать Сообщить модератору
 Re: Key lookup vs RID Lookup  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 868
результаты ожидаемы, поэтому вставку делают обычно в кучи или стедж таблицы.
Для каких-то задач используются кучи, для каких-то кластеризованные таблицы.
Вы попробуйте пожить с вашей кучей, удалить, вставить данные, перестроить и объем хороший сделать.
14 май 19, 17:05    [21884623]     Ответить | Цитировать Сообщить модератору
 Re: Key lookup vs RID Lookup  [new]
Cristiano_Rivaldo
Member

Откуда:
Сообщений: 314
Гавриленко Сергей Алексеевич,

Для кластерной таблицы : Duration = 1,CPU Cost = 16, Reads = 3
Для кучи : Duration = 1,CPU Cost = 0, Reads = 4

Запросы по id ,в общем случае, редкое явление при условии что таблица не является справочником.
14 май 19, 17:10    [21884629]     Ответить | Цитировать Сообщить модератору
 Re: Key lookup vs RID Lookup  [new]
msLex
Member

Откуда:
Сообщений: 6343
Cristiano_Rivaldo

1. RID Lookup в MSSQL это доступ по физическому расположению (file_id + page_id + slot_id) записи в куче.
2. Вы сравниваете неэквивалентные случае, в первом у вас два индекса (один из них кластерный) и поиск можно осуществлять по любому из них. Сделайте и там и там по одному индексу и сравните.
3. При любых перемещении исходных данных в кластерном индексе не требуется обновлять некластерный.
4. В MSSQL у куч есть некоторое количество неприятных особенностей.
14 май 19, 17:12    [21884632]     Ответить | Цитировать Сообщить модератору
 Re: Key lookup vs RID Lookup  [new]
Cristiano_Rivaldo
Member

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

В обоих случаях по два индекса.
14 май 19, 17:23    [21884642]     Ответить | Цитировать Сообщить модератору
 Re: Key lookup vs RID Lookup  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36317
Cristiano_Rivaldo
Гавриленко Сергей Алексеевич,

Для кластерной таблицы : Duration = 1,CPU Cost = 16, Reads = 3
Для кучи : Duration = 1,CPU Cost = 0, Reads = 4
Т.е. ровно на 25% хуже в вашем случе. Для деревьев глубиной 5-6 будет 10-15% ухудшения. Весьма существенно.

Cristiano_Rivaldo
Запросы по id ,в общем случае, редкое явление при условии что таблица не является справочником.
Открою секрет -- кластерный индекс можно делать не только по полю id.
14 май 19, 17:23    [21884643]     Ответить | Цитировать Сообщить модератору
 Re: Key lookup vs RID Lookup  [new]
msLex
Member

Откуда:
Сообщений: 6343
Cristiano_Rivaldo
msLex,

В обоих случаях по два индекса.

да не заметил

тогда у вас в случае кучи оверхед на хранение.
14 май 19, 17:24    [21884646]     Ответить | Цитировать Сообщить модератору
 Re: Key lookup vs RID Lookup  [new]
Cristiano_Rivaldo
Member

Откуда:
Сообщений: 314
Гавриленко Сергей Алексеевич
]Открою секрет -- кластерный индекс можно делать не только по полю id.

Я в курсе :)
14 май 19, 17:25    [21884648]     Ответить | Цитировать Сообщить модератору
 Re: Key lookup vs RID Lookup  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6420
автор
Меня, как бывшего ораклиста, мучает вопрос- почему никто не использует кучи или используют очень редко?

если все задачи с бд сводятся к выбору a = 45, то и ms sql и скорее всего oracle проиграют наверное большиству nosql dbms
14 май 19, 18:01    [21884673]     Ответить | Цитировать Сообщить модератору
 Re: Key lookup vs RID Lookup  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2277
Гавриленко Сергей Алексеевич
Cristiano_Rivaldo
Гавриленко Сергей Алексеевич,

Для кластерной таблицы : Duration = 1,CPU Cost = 16, Reads = 3
Для кучи : Duration = 1,CPU Cost = 0, Reads = 4
Т.е. ровно на 25% хуже в вашем случе. Для деревьев глубиной 5-6 будет 10-15% ухудшения. Весьма существенно.
При поиске по некластерному индексу, куча будет работать быстрее на те же несколько чтений, так что я бы не сказал что у кластерного индекса прям существенное преимущество. Обсуждалось это уже много-много раз.
Основная проблема с кучей это её обслуживание. Не знаю как в Оракле, а в SQL Server все плохо.
14 май 19, 21:00    [21884781]     Ответить | Цитировать Сообщить модератору
 Re: Key lookup vs RID Lookup  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28867
Cristiano_Rivaldo
Запросы по id ,в общем случае, редкое явление при условии что таблица не является справочником
Запросы по RID ещё более редкое явление, даже если таблица является справочником.
14 май 19, 21:12    [21884786]     Ответить | Цитировать Сообщить модератору
 Re: Key lookup vs RID Lookup  [new]
invm
Member

Откуда: Москва
Сообщений: 8712
Cristiano_Rivaldo,

https://www.red-gate.com/simple-talk/sql/t-sql-programming/delete-operation-sql-server-heaps/
14 май 19, 21:18    [21884791]     Ответить | Цитировать Сообщить модератору
 Re: Key lookup vs RID Lookup  [new]
-- KAB --
Member

Откуда: Москва
Сообщений: 22
Mind
Гавриленко Сергей Алексеевич
пропущено...
Т.е. ровно на 25% хуже в вашем случе. Для деревьев глубиной 5-6 будет 10-15% ухудшения. Весьма существенно.
При поиске по некластерному индексу, куча будет работать быстрее на те же несколько чтений, так что я бы не сказал что у кластерного индекса прям существенное преимущество. Обсуждалось это уже много-много раз.
Основная проблема с кучей это её обслуживание. Не знаю как в Оракле, а в SQL Server все плохо.


Не всегда, в куче возможен Forwarding Pointers
16 май 19, 17:14    [21886493]     Ответить | Цитировать Сообщить модератору
 Re: Key lookup vs RID Lookup  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2277
-- KAB --
Mind
пропущено...
При поиске по некластерному индексу, куча будет работать быстрее на те же несколько чтений, так что я бы не сказал что у кластерного индекса прям существенное преимущество. Обсуждалось это уже много-много раз.
Основная проблема с кучей это её обслуживание. Не знаю как в Оракле, а в SQL Server все плохо.


Не всегда, в куче возможен Forwarding Pointers
+1 чтение, а индекс это как минимум 3-4 уровня.
16 май 19, 22:55    [21886690]     Ответить | Цитировать Сообщить модератору
 Re: Key lookup vs RID Lookup  [new]
-- KAB --
Member

Откуда: Москва
Сообщений: 22
Mind
-- KAB --
пропущено...


Не всегда, в куче возможен Forwarding Pointers
+1 чтение, а индекс это как минимум 3-4 уровня.


--drop table #tn, #tc
create table #tn (id int identity primary key nonclustered, name varchar(900))
create table #tc (id int identity primary key clustered, name varchar(900))

;with cte as (
    select 1 i union all
    select i+1 from cte
)
insert #tn
    output inserted.name into #tc
    select top(20000) 'a'
    from cte option(maxrecursion 0)

set statistics io on
select max(name) from #tn
select max(name) from #tc
set statistics io off

update #tn set name = 'ab'
update #tc set name = 'ab'

update #tn set name = 'abc'
update #tc set name = 'abc'

update #tn set name = 'abcd'
update #tc set name = 'abcd'

set statistics io on
select max(name) from #tn
select max(name) from #tc
set statistics io off


Посмотрите результат скрипта на вкладке messages в SSMS - статистика ввода вывода таблица tn - nonclustered и tc - clustered
17 май 19, 14:20    [21887273]     Ответить | Цитировать Сообщить модератору
 Re: Key lookup vs RID Lookup  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2277
-- KAB --
Посмотрите результат скрипта на вкладке messages в SSMS - статистика ввода вывода таблица tn - nonclustered и tc - clustered
Да, согласен. Этого момента я не учел.
17 май 19, 19:16    [21887586]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить