ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Некластерный индекс по полям кластерного. В чем фишка?
25 сообщений из 48, страница 1 из 2
  1  все
Некластерный индекс по полям кластерного. В чем фишка?
    #40065423
godsql
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
godsql
Гость
Рассматриваю базу. Наткнулся на такой интересный прием.
На таблице могут висеть одновременно кластерный и некластерный индекс.
В чем преимущество данного метода перед "просто кластерным"? Или я чего-то не замечаю?

Скрипты прилагаю
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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
...
Рейтинг: 0 / 0
24.04.2021, 15:04
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40065429
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Ну... если основной запрос exists( select * from table where ID = @ID)
то это будут чуток бодрее.
Но надо поставить UNIQUE.
...
Рейтинг: 0 / 0
24.04.2021, 15:36
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40065434
godsql
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
godsql
Гость
ну, там вообще основной поиск ведется по другим полям, так что прибавка шустрости не гарантируется :)
...
Рейтинг: 0 / 0
24.04.2021, 15:50
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40065436
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
godsql
ну, там вообще основной поиск ведется по другим полям, так что прибавка шустрости не гарантируется :)


тогда содержание некластерного индекса это - пустая трата свободного места.
...
Рейтинг: 0 / 0
24.04.2021, 15:56
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40065452
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
godsql,

Ну например, select count(*) from dbo.T1 with (index = PK_T1) будет а) значительно быстрее, если таблица широкая и б) не будет ждать завершения обновлений таблицы из других сеансов.
...
Рейтинг: 0 / 0
24.04.2021, 16:45
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40065464
SERG1257
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SERG1257
Участник
godsql На таблице могут висеть одновременно кластерный и некластерный индекс.Я бы предположил Бритву Хэнлона
Скрипт для кластерного индекса генерировался роботом, а некластерного человеком.

Я бы на вашем месте прогнал скриптик по другим таблицам/индексам
https://www.sqlservercentral.com/articles/finding-and-eliminating-duplicate-or-overlapping-indexes-1
...
Рейтинг: 0 / 0
24.04.2021, 17:56
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40065466
godsql
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
godsql
Гость
в общем, понятно. Сильно специфичное использование.
...
Рейтинг: 0 / 0
24.04.2021, 17:58
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40065469
godsql
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
godsql
Гость
SERG1257
godsql На таблице могут висеть одновременно кластерный и некластерный индекс.
Я бы предположил Бритву Хэнлона
Скрипт для кластерного индекса генерировался роботом, а некластерного человеком.


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

За статью спасибо, почитаю.
...
Рейтинг: 0 / 0
24.04.2021, 18:07
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40065490
SERG1257
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SERG1257
Участник
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/
...
Рейтинг: 0 / 0
24.04.2021, 20:59
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40065641
fkthat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fkthat
Участник
godsql,

Если есть запросы, которые выбирают только ID то м.б. профит от покрытия такого запроса некластерным индексом.
...
Рейтинг: 0 / 0
25.04.2021, 18:52
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40065650
Фотография SIMPLicity_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SIMPLicity_
Участник
Возможно, что второй индекс (nonclustered) был изначально [ID] DEsc (а не [ID] ASC), и использовался для а) выборки последних Икс айдишников (select top 10 id from table (noock) order by id desc), б) для избегания блокировок при быстром сёрфинге по таблице (с тем же nolock-ом, например) ... Короткевич про что-то похожее, кстати, когда-то рассказывал. Но я точно не помню. Извините, есличо ;)
...
Рейтинг: 0 / 0
25.04.2021, 20:01
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40065653
SERG1257
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SERG1257
Участник
SIMPLicity_ был изначально [ID] DEsc (а не [ID] ASC), и использовался для а) выборки последних Икс айдишников (select top 10 id from table (noock) order by id desc),А вот с этого места поподробнее.
Я всю жизнь был уверен что сервер прекрасно умеет ASC индекс для desc запросов.
...
Рейтинг: 0 / 0
25.04.2021, 20:24
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40065672
godsql
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
godsql
Гость
Ради эксперимента сделал запросы
Select top 200000 * from table order by id asc
Select top 200000 * from table order by id desc

Везде используется Скан кластерного индекса (Отсортировано = true)
время выборки приблизительно одинаковое, причем desc - выигрывает и по количеству логических чтений (3785 против 4266), так и по затраченному времени
...
Рейтинг: 0 / 0
25.04.2021, 21:52
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40065698
Ведущий профессионал
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ведущий профессионал
Гость
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) будут обращаться только к этом индексу и потребуют на порядки меньше операций чтения страниц.

Попробуйте вот эти запросы.
...
Рейтинг: 0 / 0
25.04.2021, 23:02
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40065699
Ведущий профессионал
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ведущий профессионал
Гость
aleks222
Ну... если основной запрос exists( select * from table where ID = @ID)
то это будут чуток бодрее.
Но надо поставить UNIQUE.
А индексы по первичному ключу не подразумевают автоматически UNIQUE? Оптимизатор сам не догадывается?
...
Рейтинг: 0 / 0
25.04.2021, 23:03
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40065765
Фотография Ennor Tiegael
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ennor Tiegael
Участник
SERG1257
SIMPLicity_ был изначально [ID] DEsc (а не [ID] ASC), и использовался для а) выборки последних Икс айдишников (select top 10 id from table (noock) order by id desc),
А вот с этого места поподробнее.
Я всю жизнь был уверен что сервер прекрасно умеет ASC индекс для desc запросов.Умеет, не вопрос. Проблема в том, что почему-то в MSSQL не реализован параллелизм для таких запросов.

Desc поиск по asc индексу всегда будет однопоточным.
...
Рейтинг: 0 / 0
26.04.2021, 03:09
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40065774
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Ведущий профессионал
aleks222
Ну... если основной запрос exists( select * from table where ID = @ID)
то это будут чуток бодрее.
Но надо поставить UNIQUE.
А индексы по первичному ключу не подразумевают автоматически UNIQUE? Оптимизатор сам не догадывается?

Чтению обучают в первом классе.
...
Рейтинг: 0 / 0
26.04.2021, 06:58
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40066154
Фотография SIMPLicity_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SIMPLicity_
Участник
Скрипт на существующий кластерный индекс и создание нового, идентичного но НЕкластерного, с обратной сортировкой

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
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);



Вероятно есть какие-то причины использования НЕкластерного индекса. Запрос отработал (почему-то) вот так:
...
Рейтинг: 0 / 0
26.04.2021, 23:34
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40066157
Фотография SIMPLicity_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SIMPLicity_
Участник
SIMPLicity_
Скрипт на существующий кластерный индекс и создание нового, ....


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

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

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

PS Есличо - могу ошибаться - готов выслушать критику....
...
Рейтинг: 0 / 0
26.04.2021, 23:37
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40066161
Фотография SIMPLicity_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SIMPLicity_
Участник
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 ,- это было всего лишь МОЁ ПРЕДПОЛОЖЕНИЕ,- не более...
...
Рейтинг: 0 / 0
26.04.2021, 23:51
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40066201
Ведущий профессионал
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ведущий профессионал
Гость
SIMPLicity_
SIMPLicity_
Скрипт на существующий кластерный индекс и создание нового, ....


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

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

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

PS Есличо - могу ошибаться - готов выслушать критику....
Я уже писал, что дело в размерах. Просто посмотрите, сколько страниц занимает кластерный индекс, и сколько не кластерный.
...
Рейтинг: 0 / 0
27.04.2021, 08:05
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40066357
Фотография Критик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Критик
Участник
godsql
в общем, понятно. Сильно специфичное использование.


Я бы сказал, что с вероятность 99% - это чьи-то кривые руки, возможно, после задачи типа "сделай индексы на все ID-поля".
Рекорд, который я встречал, это кластерный + 4 идентичных ему некластерных на немаленькой таблице )
...
Рейтинг: 0 / 0
27.04.2021, 14:31
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40066402
Фотография StarikNavy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
...
Рейтинг: 0 / 0
27.04.2021, 16:31
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40066439
Фотография Ennor Tiegael
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ennor Tiegael
Участник
Помнится, старые версии PowerDesigner так делали, если сразу не запретить. Там для MSSQL в дефолтных настройках было что-то типа "создавать индекс на каждый PK и UQ".
...
Рейтинг: 0 / 0
27.04.2021, 17:56
| Ответить | Цитировать | Написать  
Некластерный индекс по полям кластерного. В чем фишка?
    #40066468
Фотография Yasha123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yasha123
Участник
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 индекса, ПК и уникальный,
один кластерный, другой некластерный, на широченной таблице.
некластерный, разумеется, гораздо меньше весит,
лично мне надо для проверки ФК, ибо на эту таблицу смотрит туча других.
вот статистика по чтениям, сервер еще как обрадовался новому некластерному и юзает его,
хотя сделан не так давно:
...
Рейтинг: 0 / 0
27.04.2021, 19:01
| Ответить | Цитировать | Написать  
25 сообщений из 48, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Некластерный индекс по полям кластерного. В чем фишка?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=40065429&tid=1684678]:
 0ms
get settings:
 8ms
get forum list:
 11ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 90ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 52ms
get tp. blocked users:
 1ms
others: 302ms
total:  483ms
созд. / загр.: 483ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]