Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Некластерный индекс по полям кластерного. В чем фишка?  [new]
godsql
Member

Откуда:
Сообщений: 198
Рассматриваю базу. Наткнулся на такой интересный прием.
На таблице могут висеть одновременно кластерный и некластерный индекс.
В чем преимущество данного метода перед "просто кластерным"? Или я чего-то не замечаю?

Скрипты прилагаю
ALTER TABLE [dbo].[T1] ADD  CONSTRAINT [PK__T1____] PRIMARY KEY CLUSTERED
(
       [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [PK_T1] ON [dbo].[T1]
(
       [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
24 апр 21, 15:04    [22313593]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
aleks222
Member

Откуда:
Сообщений: 1355
Ну... если основной запрос exists( select * from table where ID = @ID)
то это будут чуток бодрее.
Но надо поставить UNIQUE.
24 апр 21, 15:36    [22313606]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
godsql
Member

Откуда:
Сообщений: 198
ну, там вообще основной поиск ведется по другим полям, так что прибавка шустрости не гарантируется :)
24 апр 21, 15:50    [22313612]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1757
godsql
ну, там вообще основной поиск ведется по другим полям, так что прибавка шустрости не гарантируется :)


тогда содержание некластерного индекса это - пустая трата свободного места.
24 апр 21, 15:56    [22313615]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
invm
Member

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

Ну например, select count(*) from dbo.T1 with (index = PK_T1) будет а) значительно быстрее, если таблица широкая и б) не будет ждать завершения обновлений таблицы из других сеансов.
24 апр 21, 16:45    [22313640]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
SERG1257
Member

Откуда:
Сообщений: 2844
godsql
На таблице могут висеть одновременно кластерный и некластерный индекс.
Я бы предположил Бритву Хэнлона
Скрипт для кластерного индекса генерировался роботом, а некластерного человеком.

Я бы на вашем месте прогнал скриптик по другим таблицам/индексам
https://www.sqlservercentral.com/articles/finding-and-eliminating-duplicate-or-overlapping-indexes-1
24 апр 21, 17:56    [22313666]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
godsql
Member

Откуда:
Сообщений: 198
в общем, понятно. Сильно специфичное использование.
24 апр 21, 17:58    [22313669]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
godsql
Member

Откуда:
Сообщений: 198
SERG1257
godsql
На таблице могут висеть одновременно кластерный и некластерный индекс.
Я бы предположил Бритву Хэнлона
Скрипт для кластерного индекса генерировался роботом, а некластерного человеком.


Если я начну копаться основательно, то основную работу делать не смогу :) Там такой ужас, что кажется, что это студенты писали. Хотя стоит эта хрень - дофига. Правлю то, с чем работаю.

За статью спасибо, почитаю.
24 апр 21, 18:07    [22313678]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
SERG1257
Member

Откуда:
Сообщений: 2844
godsql
Если я начну копаться основательно, то основную работу делать не смогу :) Там такой ужас, что кажется, что это студенты писали. Хотя стоит эта хрень - дофига.
это норма (с) Елена Малышева
Я бы предложил прогнать скриптик чтобы получить список подозрительных индексов (это быстро). Кстати не забывайте про INCLUDE поля.
Далее, некластерные индексы с идентичным набором полей (как в вашем случае) это абсолютное зло и кандидат на удаление.
Если боитесь удалять (запрещено вендором) - есть вариант с disable https://docs.microsoft.com/en-us/sql/relational-databases/indexes/disable-indexes-and-constraints?view=sql-server-ver15
Засада может быть только если имя индекса используется как подсказка в запросах (см пример от invm)

В случае частичного пересечения не все так однозначно.
Если будете копать, то посмотрите в сторону утилиты от Брента Озара
https://www.brentozar.com/blitzindex/
https://www.brentozar.com/blitzindex/sp_blitzindex-duplicate-indexes/
24 апр 21, 20:59    [22313723]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
fkthat
Member [заблокирован]

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

Если есть запросы, которые выбирают только ID то м.б. профит от покрытия такого запроса некластерным индексом.
25 апр 21, 18:52    [22314010]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8857
Возможно, что второй индекс (nonclustered) был изначально [ID] DEsc (а не [ID] ASC), и использовался для а) выборки последних Икс айдишников (select top 10 id from table (noock) order by id desc), б) для избегания блокировок при быстром сёрфинге по таблице (с тем же nolock-ом, например) ... Короткевич про что-то похожее, кстати, когда-то рассказывал. Но я точно не помню. Извините, есличо ;)
25 апр 21, 20:01    [22314031]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
SERG1257
Member

Откуда:
Сообщений: 2844
SIMPLicity_
был изначально [ID] DEsc (а не [ID] ASC), и использовался для а) выборки последних Икс айдишников (select top 10 id from table (noock) order by id desc),
А вот с этого места поподробнее.
Я всю жизнь был уверен что сервер прекрасно умеет ASC индекс для desc запросов.
25 апр 21, 20:24    [22314036]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
godsql
Member

Откуда:
Сообщений: 198
Ради эксперимента сделал запросы
Select top 200000 * from table order by id asc
Select top 200000 * from table order by id desc

Везде используется Скан кластерного индекса (Отсортировано = true)
время выборки приблизительно одинаковое, причем desc - выигрывает и по количеству логических чтений (3785 против 4266), так и по затраченному времени
25 апр 21, 21:52    [22314071]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
Ведущий профессионал
Member

Откуда: Санкт-Петербург
Сообщений: 117
godsql
Ради эксперимента сделал запросы
Select top 200000 * from table order by id asc
Select top 200000 * from table order by id desc

Везде используется Скан кластерного индекса (Отсортировано = true)
время выборки приблизительно одинаковое, причем desc - выигрывает и по количеству логических чтений (3785 против 4266), так и по затраченному времени
На каждой странице может быть разное количество записей. Это связано как с тем, что записи могут иметь разный размер, так и с тем, что записи могут обновляться/удаляться. Это объясняет разницу в количестве прочитанных страниц.

Кластерный индекс - это и есть вся таблица. А в топике речь о не кластерном индексе. В нём не хранится ничего, кроме ID. Потому и места на диске он займёт сильно меньше. Соответственно, запросы вида SELECT COINT(*) FROM table, или SELECT TOP 1 ID FROM table ORDER BY ID DESC, или EXISTS(SELECT * FROM table WHERE ID = @ID) будут обращаться только к этом индексу и потребуют на порядки меньше операций чтения страниц.

Попробуйте вот эти запросы.
25 апр 21, 23:02    [22314101]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
Ведущий профессионал
Member

Откуда: Санкт-Петербург
Сообщений: 117
aleks222
Ну... если основной запрос exists( select * from table where ID = @ID)
то это будут чуток бодрее.
Но надо поставить UNIQUE.
А индексы по первичному ключу не подразумевают автоматически UNIQUE? Оптимизатор сам не догадывается?
25 апр 21, 23:03    [22314102]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3396
SERG1257
SIMPLicity_
был изначально [ID] DEsc (а не [ID] ASC), и использовался для а) выборки последних Икс айдишников (select top 10 id from table (noock) order by id desc),
А вот с этого места поподробнее.
Я всю жизнь был уверен что сервер прекрасно умеет ASC индекс для desc запросов.
Умеет, не вопрос. Проблема в том, что почему-то в MSSQL не реализован параллелизм для таких запросов.

Desc поиск по asc индексу всегда будет однопоточным.
26 апр 21, 03:09    [22314180]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
aleks222
Member

Откуда:
Сообщений: 1355
Ведущий профессионал
aleks222
Ну... если основной запрос exists( select * from table where ID = @ID)
то это будут чуток бодрее.
Но надо поставить UNIQUE.
А индексы по первичному ключу не подразумевают автоматически UNIQUE? Оптимизатор сам не догадывается?

Чтению обучают в первом классе.
26 апр 21, 06:58    [22314195]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8857
Скрипт на существующий кластерный индекс и создание нового, идентичного но НЕкластерного, с обратной сортировкой

+
SET ANSI_PADDING ON
GO

/****** Object:  Index [PK__Document__0476BE1F3B23C0D3]    Script Date: 26.04.2021 22:10:40 ******/
ALTER TABLE [dbo].[Documents_History] ADD PRIMARY KEY CLUSTERED 
(
	[posting_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

create nonclustered index [Documents_History_idx] on [Documents_History] ([posting_number] deSC);


Вероятно есть какие-то причины использования НЕкластерного индекса. Запрос отработал (почему-то) вот так:

К сообщению приложен файл. Размер - 128Kb
26 апр 21, 23:34    [22314808]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8857
SIMPLicity_
Скрипт на существующий кластерный индекс и создание нового, ....


И да, MSSQL 2019, живая система,- да простят меня все за мои эксперименты на продакшне,- в таблице чуть менее 130 тыс строк. Ресурсов в достатке. особенно ночью ;) ...

PS Необычно ещё и то, что поле является единственным полем класт. первичного ключа - PRIMARY KEY CLUSTERED ( [posting_number] ASC ) - но первичный ключ использован не был (на скрине фактический план).

Возможно MSSQL стал настолько умён, что для м**ацких запросов старается избегать блокировок ресурсов (в данном случае - всей таблицы ибо сканится кластерный индекс).

PS Есличо - могу ошибаться - готов выслушать критику....

Сообщение было отредактировано: 26 апр 21, 23:37
26 апр 21, 23:37    [22314811]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8857
godsql
Ради эксперимента сделал запросы
Select top 200000 * from table order by id asc
Select top 200000 * from table order by id desc

Везде используется Скан кластерного индекса (Отсортировано = true)
время выборки приблизительно одинаковое, причем desc - выигрывает и по количеству логических чтений (3785 против 4266), так и по затраченному времени


Коллега, у Вас оба индекса ASC. А то что второй был изначально DESC ,- это было всего лишь МОЁ ПРЕДПОЛОЖЕНИЕ,- не более...
26 апр 21, 23:51    [22314816]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
Ведущий профессионал
Member

Откуда: Санкт-Петербург
Сообщений: 117
SIMPLicity_
SIMPLicity_
Скрипт на существующий кластерный индекс и создание нового, ....


И да, MSSQL 2019, живая система,- да простят меня все за мои эксперименты на продакшне,- в таблице чуть менее 130 тыс строк. Ресурсов в достатке. особенно ночью ;) ...

PS Необычно ещё и то, что поле является единственным полем класт. первичного ключа - PRIMARY KEY CLUSTERED ( [posting_number] ASC ) - но первичный ключ использован не был (на скрине фактический план).

Возможно MSSQL стал настолько умён, что для м**ацких запросов старается избегать блокировок ресурсов (в данном случае - всей таблицы ибо сканится кластерный индекс).

PS Есличо - могу ошибаться - готов выслушать критику....
Я уже писал, что дело в размерах. Просто посмотрите, сколько страниц занимает кластерный индекс, и сколько не кластерный.
27 апр 21, 08:05    [22314873]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35081
Блог
godsql
в общем, понятно. Сильно специфичное использование.


Я бы сказал, что с вероятность 99% - это чьи-то кривые руки, возможно, после задачи типа "сделай индексы на все ID-поля".
Рекорд, который я встречал, это кластерный + 4 идентичных ему некластерных на немаленькой таблице )
27 апр 21, 14:31    [22315102]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2407
Критик,

а вот здесь что лишнее? :)


ALTER TABLE [dbo].[tbl_Primer] ADD CONSTRAINT [PK8reomM] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX [ClustPrimaKeyIx] ON [dbo].[tbl_Primer]
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
27 апр 21, 16:31    [22315176]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3396
Помнится, старые версии PowerDesigner так делали, если сразу не запретить. Там для MSSQL в дефолтных настройках было что-то типа "создавать индекс на каждый PK и UQ".
27 апр 21, 17:56    [22315243]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индекс по полям кластерного. В чем фишка?  [new]
Yasha123
Member

Откуда:
Сообщений: 1973
StarikNavy
Критик,

а вот здесь что лишнее? :)


ALTER TABLE [dbo].[tbl_Primer] ADD CONSTRAINT [PK8reomM] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX [ClustPrimaKeyIx] ON [dbo].[tbl_Primer]
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

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

К сообщению приложен файл. Размер - 23Kb
27 апр 21, 19:01    [22315295]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить