Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Некластерный индекс по полям кластерного. В чем фишка? / 25 сообщений из 48, страница 1 из 2
  1  все
24.04.2021, 15:04
    #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:36
    #40065429
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Некластерный индекс по полям кластерного. В чем фишка?
Ну... если основной запрос exists( select * from table where ID = @ID)
то это будут чуток бодрее.
Но надо поставить UNIQUE.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.04.2021, 15:50
    #40065434
godsql
godsql
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Некластерный индекс по полям кластерного. В чем фишка?
ну, там вообще основной поиск ведется по другим полям, так что прибавка шустрости не гарантируется :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.04.2021, 15:56
    #40065436
felix_ff
felix_ff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Некластерный индекс по полям кластерного. В чем фишка?
godsql
ну, там вообще основной поиск ведется по другим полям, так что прибавка шустрости не гарантируется :)


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

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

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


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

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

Если есть запросы, которые выбирают только ID то м.б. профит от покрытия такого запроса некластерным индексом.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.04.2021, 20:01
    #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:24
    #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, 21:52
    #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, 23:02
    #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:03
    #40065699
Ведущий профессионал
Ведущий профессионал
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Некластерный индекс по полям кластерного. В чем фишка?
aleks222
Ну... если основной запрос exists( select * from table where ID = @ID)
то это будут чуток бодрее.
Но надо поставить UNIQUE.
А индексы по первичному ключу не подразумевают автоматически UNIQUE? Оптимизатор сам не догадывается?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.04.2021, 03:09
    #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, 06:58
    #40065774
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Некластерный индекс по полям кластерного. В чем фишка?
Ведущий профессионал
aleks222
Ну... если основной запрос exists( select * from table where ID = @ID)
то это будут чуток бодрее.
Но надо поставить UNIQUE.
А индексы по первичному ключу не подразумевают автоматически UNIQUE? Оптимизатор сам не догадывается?

Чтению обучают в первом классе.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.04.2021, 23:34
    #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:37
    #40066157
SIMPLicity_
SIMPLicity_
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Некластерный индекс по полям кластерного. В чем фишка?
SIMPLicity_
Скрипт на существующий кластерный индекс и создание нового, ....


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

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

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

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


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

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

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

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


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


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