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

Откуда:
Сообщений: 11
Добрый день, в рамках самообразования (сам я скорее программист чем DBA) наткнулся на мнение что clustered index это во многих случаях зло.
http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key
Вкраце, аргументация такая - если у таблицы есть более одного индекса (которые нужны для разных способов фильтрации данных через where), то clustered index замедляет доступ к данным, когда он идет через non-clustered index, потому что в non-clustered index хранится clustered index key а не физическое расположение данных.

Пример - таблица customers и таблица orders в которой есть id int identity primary key, customerid int references customers(id) и created datetime.

В системе одинаково нужны как запросы order'а по ID, так и запросы всех order'ов для данного customer'а, так и запросы всех order'ов для данной даты. То есть как минимум три индекса.
Запроса order'ов по ID between X and Y не предвидится.

Собственно вопрос - а не подложил ли MSSQL всем свинью, создавая clustered index по умолчанию когда в таблице есть id int not null identity primary key ? Ведь вероятность что будет куча range запросов по ID достаточно мала, а замедление доступа к данным по всем остальным индексам присутствует. Или есть какой-то иной смысл в clustered key кроме как быстрое исполнение range запросов?

Есть желание прибить clustered key со всех таблиц, но с одной стороны это затратно по времени, а с другой страшно, вдруг я чего-то не понял и сделаю хуже. Собственно поэтому интересуюсь мнением сообщества :)
13 апр 15, 12:03    [17507426]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
a_voronin
Member

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

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

Как правило в OLTP базах кластерный индекс требуется, если это не какой-то временный буфер. В DWH решениях есть вот такая рекомендация

http://www.kimballgroup.com/2006/07/design-tip-81-fact-table-surrogate-key/

Remember, surrogate keys for dimension tables are a great idea. Surrogate keys for fact tables are not logically required but can be very helpful in the back room ETL processing.
13 апр 15, 12:12    [17507485]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
o-o
Guest
a_voronin
насколько я помню кластерный индекс есть всегда

нет не всегда.
кластерный индекс упорядочен, и вставка идет ровно в конкретное место, указанное ключом.
в куче вставка идет на первое попавшееся место.
соответственно, куча и весь ее объем это только data records.
кластерный и вообще любой индекс это кроме листового уровня еще и навигационное дерево.
13 апр 15, 12:24    [17507550]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
хмхмхм
Guest
Ando2
Есть желание прибить clustered key со всех таблиц

А вы проэксперементировали? Deadlock-и на страничном уровне не появятся?
13 апр 15, 12:33    [17507592]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
o-o
a_voronin
насколько я помню кластерный индекс есть всегда

нет не всегда.
кластерный индекс упорядочен, и вставка идет ровно в конкретное место, указанное ключом.
в куче вставка идет на первое попавшееся место.
соответственно, куча и весь ее объем это только data records.
кластерный и вообще любой индекс это кроме листового уровня еще и навигационное дерево.


Я тут пару месяцев назад копался в теме перестройки индексов и обнаружил, что на 2012 появилась новая опция перестройки кучи. А вот что она даёт?
13 апр 15, 12:34    [17507600]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
Ando2
Member

Откуда:
Сообщений: 11
хмхмхм
Ando2
Есть желание прибить clustered key со всех таблиц

А вы проэксперементировали? Deadlock-и на страничном уровне не появятся?


Пока не экспериментировал, потому что изменение это на уровне всей базы достаточно радикальное, и хочется сначала услышать мнение сообщества.
13 апр 15, 12:36    [17507605]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
o-o
Guest
я не хочу сейчас перечислять все отличия, это же обязательно что-то будет упущено.
но с кучами греха не оберешься, факт.
вот примеры:
удаление из кластерного и из кучи организовано по-разному,
в результате удаление из кучи может закончиться невысвобождением места:
Deleting Rows by Using DELETE
When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database.

вот эту баговину для куч в 2012, 2014 Рэндал подтвердил тоже:
17321289 -- там с картинками и в конце ссылка на Рэндала

ну и кроме скорости доступа, задумайтесь над ребилдом всех некластерных.
если вы ребилдите кластерный (версия >= 2005), некластерныx это не коснется,
т.к. они "ссылаются" на ключ кластерного, а он остается неизменным.
если же вы ребилдите кучу (допустим, вам понравилось data compression),
все некластерные будут перестроены
13 апр 15, 12:37    [17507610]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
o-o
Guest
a_voronin
Я тут пару месяцев назад копался в теме перестройки индексов и обнаружил, что на 2012 появилась новая опция перестройки кучи. А вот что она даёт?

а вы покажите (ссылкой), сейчас вы про что, разберемся.
ALTER TABLE REBUILD появилось в 2008 R2 в связи с DATA COMPRESSION,
но если даже не нужно это DATA COMPRESSION, команда отработает,
т.е. куча будет перестроена и это можно использовать для высвобождения места,
если, как у меня постом выше описано, проводился DELETE без TABLOCK и место не высвободилось.
или когда при изменении типов данных вместо изменения имеющегося просто создался новый столбец.
была же не так давно тема, как товарищ хотел "уменьшить" символьное поле, в результате лишь раздул свою таблицу,
вот такое ребилдом кучи можно исправить.
13 апр 15, 12:43    [17507632]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
o-o
a_voronin
Я тут пару месяцев назад копался в теме перестройки индексов и обнаружил, что на 2012 появилась новая опция перестройки кучи. А вот что она даёт?

а вы покажите (ссылкой), сейчас вы про что, разберемся.
ALTER TABLE REBUILD появилось в 2008 R2 в связи с DATA COMPRESSION,
но если даже не нужно это DATA COMPRESSION, команда отработает,
т.е. куча будет перестроена и это можно использовать для высвобождения места,
если, как у меня постом выше описано, проводился DELETE без TABLOCK и место не высвободилось.
или когда при изменении типов данных вместо изменения имеющегося просто создался новый столбец.
была же не так давно тема, как товарищ хотел "уменьшить" символьное поле, в результате лишь раздул свою таблицу,
вот такое ребилдом кучи можно исправить.


Я про ALTER TABLE REBUILD , причём он что-то пытается делать на таблицах, где нет компрессии. Там всё равно есть уровень фрагментации.
13 апр 15, 12:45    [17507643]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
o-o
Guest
a_voronin,

в куче нет логической фрагментации в как в индексах,
ведь раз нет упорядочения, то не может быть и "страницы не в том порядке идут",
может быть неотданное место, тогда да, помогает ALTER TABLE REBUILD, см. пост выше.
13 апр 15, 12:54    [17507687]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
o-o
ALTER TABLE REBUILD появилось в 2008 R2 в связи с DATA COMPRESSION


ну, хмм..

В 2008.
Скажем, для sparse columns тоже используется успешно, иногда (всегда?) для того, чтобы сработало преобразование обычных полей в sparse в существующей таблице надо приметь rebuild.
13 апр 15, 12:55    [17507689]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
o-o
Guest
Winnipuh,

ну да, пардон, 2008.
а про то, зачем ввели, не мое :)
где-то это произнес Рэндал, и если он еще и написал, то сейчас найду,
но мне кажется, я со слуха помню.
т.е. речь шла о ребилде индексов, а заодно было сказано типа "но и для куч отныне можно заюзать ALTER TABLE REBUILD,
изначально задуманное для компрессии"
13 апр 15, 13:02    [17507727]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
o-o
Guest
пока что не удалось найти именно ту фразу,
зато попалась полезная статья по теме, там и про то, как и зачем можно припахать ALTER TABLE REBUILD,
и почему это плохо, и заново вся история с ребилдом всех некластерных как побочный эффект,
и с кучей ссылок "вокруг да около"
A SQL Server DBA myth a day: (29/30) fixing heap fragmentation
13 апр 15, 13:08    [17507758]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
invm
Member

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

1. Не нужно делать далеко идущих выводов ("Есть желание прибить clustered key со всех таблиц") по статье, где рассматривается только один аспект. Почитайте хотя бы это - https://technet.microsoft.com/en-us/library/cc917672.aspx
2. Не существует "волшебной хреновины", после включения которой, любой запрос начнет "летать". Поэтому оптимизация самих запросов даст гораздо больший эффект, чем игрища с clustered/heap.

Ну и вам на закуску для осмысления:
+
use tempdb;
go

create table dbo.t (id int primary key clustered, v int, filler char(200));
create table dbo.t1 (id int primary key clustered, t_id int, filler char(200));
create table dbo.t2 (id int primary key nonclustered, t_id int, filler char(200));
go

insert into dbo.t
 (id, filler)
 select top (10000)
  row_number() over (order by (select 1)),
  'a'
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

insert into dbo.t1
 (id, t_id, filler)
 select top (1000000)
  row_number() over (order by (select 1)),
  cast(rand(checksum(newid())) * 10000 + 1 as int),
  'a'
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

insert into dbo.t2 with (tablock)
 (id, t_id, filler)
 select
  id, t_id, filler
 from
  dbo.t1;

create index IX_t1__t_id on dbo.t1(t_id);
create index IX_t2__t_id on dbo.t2(t_id);
go

declare @f char(400);

set statistics io, time on;

select
 @f = t.filler + t1.filler
from
 dbo.t join
 dbo.t1 on t1.t_id = t.id;

select
 @f = t.filler + t2.filler
from
 dbo.t join
 dbo.t2 on t2.t_id = t.id;

select
 @f = t.filler + t1.filler
from
 dbo.t join
 dbo.t1 with (index = IX_t1__t_id) on t1.t_id = t.id;

select
 @f = t.filler + t2.filler
from
 dbo.t join
 dbo.t2 with (index = IX_t2__t_id) on t2.t_id = t.id;

set statistics io, time off;
go

drop table dbo.t, dbo.t1, dbo.t2;
go
13 апр 15, 14:11    [17508125]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
хмхмхм
Guest
Ando2
хмхмхм
пропущено...

А вы проэксперементировали? Deadlock-и на страничном уровне не появятся?


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


Зря, посмотрели бы сами что произойдет и надо ли оно вам вообще.

Вот я например могу привести простой пример на что виляет кластерный индекс - на операции DML.
Вот простой пример на 1 млн. записей (у меня 1 000 008):

create table dbo.NonClusteredIndexTable(
id int not null
,name nvarchar(1024) null
)

create table dbo.ClusteredIndexTable(
id int not null primary key	-- <-- Clustered index
,name nvarchar(1024) null
)

set nocount on
go

declare @count int = 0

while @count < 1000000
begin
insert into dbo.NonClusteredIndexTable(id, name)
select row_number() over (order by object_id) + @count, o.name from sys.objects as o

set @count += @@ROWCOUNT
end

set @count = 0
while @count < 1000000
begin
insert into dbo.ClusteredIndexTable(id, name)
select row_number() over (order by  object_id) + @count, o.name from sys.objects as o

set @count += @@ROWCOUNT
end

--1000 008 records in each table
select count(1) from dbo.NonClusteredIndexTable
select count(1) from dbo.ClusteredIndexTable

-- Create nonclustered index
create nonclustered index test_01 on NonClusteredIndexTable
(
id asc
)

go
set statistics io on
select * from dbo.NonClusteredIndexTable with (nolock)
set statistics io off
go

--Table 'NonClusteredIndexTable'. Scan count 1, logical reads 5447, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--00:11
set statistics io on
select * from dbo.ClusteredIndexTable with (nolock)
set statistics io off
--Table 'ClusteredIndexTable'. Scan count 1, logical reads 5439, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--00:11

go
set statistics io on
delete dbo.NonClusteredIndexTable
where id % 5 = 1 --every fifth record
set statistics io off
--Table 'NonClusteredIndexTable'. Scan count 5, logical reads 815546, physical reads 0, read-ahead reads 28, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--00:09

go
set statistics io on
delete dbo.ClusteredIndexTable
where id % 5 = 1 --every fifth record
set statistics io off
--Table 'ClusteredIndexTable'. Scan count 5, logical reads 605524, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--00:02

drop table dbo.NonClusteredIndexTable
drop table dbo.ClusteredIndexTable


В примере я делаю выборку, ожидаемо куча дает меньше логических чтений, по времени почти одинаково.
А потом я удаляю каждую 5 запись вызывая скан некластерного и кластерного индекса в разных таблицах.
Отличие во времени впечатляет.
13 апр 15, 14:14    [17508143]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
хмхмхм
Guest
Ando2,

Кстати, вот вам в тему cluster vs heap, почитайте:
https://technet.microsoft.com/library/Cc917672
13 апр 15, 14:16    [17508153]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
хмхмхм
Guest
Ando2,

вот еще пример на тех же таблицах:

индексы есть на каждой (на одной кластерный, на другой некластерный), данные по 1 000 008 в каждой. Вставляем такую же порцию данных в каждую и смотрим на вермя и кол-во записей:

DBCC FREEPROCCACHE
 DBCC FREESESSIONCACHE
 DBCC FREESYSTEMCACHE ('ALL')
 DBCC DROPCLEANBUFFERS


go
set statistics io on
insert into dbo.ClusteredIndexTable(id, name)
select id + 1000008, name from dbo.ClusteredIndexTable as o
set statistics io off
go

--Table 'Worktable'. Scan count 4, logical reads 2728320, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'ClusteredIndexTable'. Scan count 5, logical reads 3220215, physical reads 0, read-ahead reads 4759, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--(1000008 row(s) affected)

--00:15

DBCC FREEPROCCACHE
 DBCC FREESESSIONCACHE
 DBCC FREESYSTEMCACHE ('ALL')
 DBCC DROPCLEANBUFFERS


go
set statistics io on
insert into dbo.NonClusteredIndexTable(id, name)
select id + 1000008, name from dbo.NonClusteredIndexTable as o
set statistics io off
go
--Table 'NonClusteredIndexTable'. Scan count 1, logical reads 5204156, physical reads 44, read-ahead reads 1647, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'Worktable'. Scan count 1, logical reads 2888769, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--(1000008 row(s) affected)
--00:30


15 секунд против 30, почувствуйте разницу.
Конечно может ваше приложение и не делает вставку таких объемов, но если у вас много потоков и они все вставляют по одной записи в таблицу, то отсутствие кластерного ключа может снизить скорость системы в целом.
13 апр 15, 14:31    [17508263]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
Ando2
Member

Откуда:
Сообщений: 11
invm
Ando2,

1. Не нужно делать далеко идущих выводов ("Есть желание прибить clustered key со всех таблиц") по статье, где рассматривается только один аспект. Почитайте хотя бы это - https://technet.microsoft.com/en-us/library/cc917672.aspx
2. Не существует "волшебной хреновины", после включения которой, любой запрос начнет "летать". Поэтому оптимизация самих запросов даст гораздо больший эффект, чем игрища с clustered/heap.



1. В https://technet.microsoft.com/en-us/library/cc917672.aspx рассматривается простейший вариант - либо один clustered либо один non-clustered. И исходя из этого делается далеко идущий вывод что clustered is recommended. Но в реальности альтернатива несколько другая - 1 clustered + 5 non-clustered либо heap + 6 non-clustered.
2. Не могу не согласиться. Интерес скорее академический.

"На закуску" буду изучать...

хмхмхм, судя по вашим тестам, clustered для DML действетельно более оптимальный. Однако это не снимает вопроса для баз где основную нагрузку создают select с join'ами и большим кол-вом индексов, а не delete/insert.
13 апр 15, 15:12    [17508521]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
хмхмхм
Guest
Ando2

2. Не могу не согласиться. Интерес скорее академический.

"На закуску" буду изучать...

хмхмхм, судя по вашим тестам, clustered для DML действетельно более оптимальный. Однако это не снимает вопроса для баз где основную нагрузку создают select с join'ами


Ну так попробуйте Вы, раз интересно, может что-то интересное и получится, а мы потом почитаем Ваш отчет.
13 апр 15, 15:17    [17508542]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
Ando2
В https://technet.microsoft.com/en-us/library/cc917672.aspx рассматривается простейший вариант - либо один clustered либо один non-clustered. И исходя из этого делается далеко идущий вывод что clustered is recommended. Но в реальности альтернатива несколько другая - 1 clustered + 5 non-clustered либо heap + 6 non-clustered.
В статье сравниваются способы физической организации таблицы. Причем тут еще какие-то индексы?
13 апр 15, 15:43    [17508668]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index - а нужен ли он по умолчанию?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
Ando2
Собственно вопрос - а не подложил ли MSSQL всем свинью, создавая clustered index по умолчанию когда в таблице есть id int not null identity primary key ?
Так делайте так, как считаете нужным. Почему "подложил свинью", вы же вибираете модель данных не "по умолчанию", а самостоятельно, осознанно, подумав и всё просчитав.

clustered index для identity primary key - вполне нормально, т.к. при некластерном индексе идентификатор будет длиннее, и выборки в итоге будут медленнее.
Но у вас может быть и по другому.
13 апр 15, 16:05    [17508802]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить