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

Откуда: от верблюда
Сообщений: 428
Легенда гласит, что поля в составной ключ нужно добавлять в порядке убывания уникальности, т.е. первым должен быть столбец с содержанием наименьшего числа повторяющихся значений, а последним, соответственно, тот, в котором значения повторяются чаще всего.
Однако не раз замечал, что оптимизатор отказывается использовать такой индекс, предпочитая ему скан. (Чуть ранее, я задавал вопрос о несрабатывающем индексе и проблема оказалась как раз в этой плоскости). Да, все зависит от запроса - скажете вы, ибо если оптимизатор поймет, что результатом выборки будет почти вся таблица, то дешевле использовать скан. Но речь сейчас не о таком случае. Стоит создать еще один индекс, состоящий из тех же полей, но в другом порядке, как оптимизатор с радостью начинает его использовать, а если сделать третий, то оптимизатор отказывается от второго и начинает использовать третий, утверждая, что он еще дешевле. Однако порядок полей в этом индексе полностью противоречит легенде и в нем все с точностью до наоборот. Не могу сказать что так всегда, но я сталкивался не раз. В результате я стал создавать несколько индексов с разным порядком полей, смотреть, какой из них предпочитает оптимизатор и удалять остальные. Почему так происходит, в чем фишка? Зависит ли это от типов полей или их размера, скажем, char нужно ставить после int, например?

У меня сейчас нет под рукой доказательств, но я постараюсь их предоставить, если не верите на слово.
11 авг 16, 23:12    [19531921]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37224
Разговор без структуры, запросов и планов ни о чем.
11 авг 16, 23:36    [19531986]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
Гавриленко Сергей Алексеевич,
ок, завтра все будет
12 авг 16, 00:11    [19532049]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
iap
Member

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

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

Это я в хрустальный шар посмотрел...
12 авг 16, 10:32    [19532852]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
+ структура
CREATE TABLE [dbo].[REST](
	[code_ym] [int] NOT NULL,
	[code_cfo] [varchar](3) NOT NULL,
	[code_goods] [varchar](6) NOT NULL,
	[code_SaleDep] [tinyint] NOT NULL,
	[rest_beg] [decimal](16, 2) NOT NULL,
	[rest_end] [decimal](16, 2) NOT NULL,
	[rest_begs] [decimal](16, 2) NOT NULL,
	[rest_ends] [decimal](16, 2) NOT NULL,
 CONSTRAINT [PK_REST] PRIMARY KEY CLUSTERED 
(
	[code_goods] ASC,
	[code_cfo] ASC,
	[code_ym] ASC,
	[code_SaleDep] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_REST_CODE_YM] ON [dbo].[REST] 
(
	[code_ym] 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) ON [PRIMARY]
GO


DBCC SHOW_STATISTICS  (rest, pk_rest)

UpdatedRowsRowsSampledStepsDensity Average key length
Apr 29 2016 11:50PM 17632152141065200106.371513.51339


К сообщению приложен файл. Размер - 105Kb
12 авг 16, 11:14    [19533089]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
все дело в условии к первому полю? нельзя писать like, а только = ?
12 авг 16, 11:19    [19533129]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
iap
Member

Откуда: Москва
Сообщений: 47083
TJ001
все дело в условии к первому полю? нельзя писать like, а только = ?
ЕМНИП, для того, чтобы этот индекс использовался и для следующего поля, - да.
12 авг 16, 11:23    [19533162]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
UPDATE STATISTICS ... WITH FULLSCAN

Попробуйте статистику обновить и версию SQL Server не плохо было бы указать.
12 авг 16, 11:24    [19533169]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
o-o
Guest
у вас во всех запросах поиск по индексу,
чем вы недовольны?
очень хочется, чтобы он делал лукапы что ли,
чтобы непременно сперва в некластерном пошарился?
у вас кластерный уже по нужным полям, чего еще желать,
вы же все равно выбираете *все поля*
12 авг 16, 11:24    [19533173]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
o-o,

я говорю о том, что кластерный отказывается работать и предпочитает лукап
судя по всему, как я заметил, при написании like к первому полю
12 авг 16, 11:27    [19533190]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
iap
TJ001
все дело в условии к первому полю? нельзя писать like, а только = ?
ЕМНИП, для того, чтобы этот индекс использовался и для следующего поля, - да.

и никак не заставить?
12 авг 16, 11:28    [19533203]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
o-o
Guest
вы ж писали наоборот,
что у вас скан.
или та тема уже не эта тема, вы вновь ее упомянули просто так что ли?
или я чего-то не то читаю?
TJ001
Однако не раз замечал, что оптимизатор отказывается использовать такой индекс, предпочитая ему скан. (Чуть ранее, я задавал вопрос о несрабатывающем индексе и проблема оказалась как раз в этой плоскости).
12 авг 16, 11:31    [19533229]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
o-o
Guest
я думаю, он не пошел напрямую в кластерный просто потому, что кластерный ШИРЕ.
и потому что оценка строк на выходе НОЛь.

если я знаю, что ничего не верну, но все же для порядка надо это проверить,
лучше я в УЗКОМ индексе проверю, что строки нет.
тогда и лукап делать не придется, и загружать кластерный тоже не придется.
12 авг 16, 11:35    [19533267]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
iap
Member

Откуда: Москва
Сообщений: 47083
TJ001
iap
пропущено...
ЕМНИП, для того, чтобы этот индекс использовался и для следующего поля, - да.

и никак не заставить?
Поставьте на первое место code_cfo, на второе - code_ym, на третье - code_goods.
12 авг 16, 11:39    [19533294]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
o-o
Guest
еще различие запросов в том,
что с лайком не параметризуется.
в результате тот, что с равенствами, параметризовался,
и при других параметрах возможно будет больше строк на выходе,
поэтому лукапы нам совсем не нужны.
а тот с лайком он конкретный, и для тех конкретных параметров результат пустой,
см. объяснение выше, проще найти, что строк нет, в узком индексе
12 авг 16, 11:41    [19533315]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
o-o,

про скан: все верно, так и происходит, если оставить только кластерный
12 авг 16, 11:41    [19533319]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
o-o
Guest
TJ001,

что все верно, у вас нету никакого скана,
у вас везде поиск.
по кластерному тоже поиск.
12 авг 16, 11:47    [19533364]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
o-o
TJ001,

что все верно, у вас нету никакого скана,
у вас везде поиск.
по кластерному тоже поиск.


в представленных запросах так и есть потому что:

в первом случае, если убрать обычный индекс, то тоже будет скан

во 2м запросе по кластерному идет поиск потому что есть хинт, если его убрать и удалить обыный индекс, то будет скан

я не могу сейчас продемонстрировать планы, потому что со сканом будет выполняться минут 20, не хочется нагружать машину
12 авг 16, 11:52    [19533398]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в составном ключе  [new]
o-o
Guest
TJ001
в первом случае, если убрать обычный индекс, то тоже будет скан
во 2м запросе по кластерному идет поиск потому что есть хинт, если его убрать и удалить обыный индекс, то будет скан

я не могу сейчас продемонстрировать планы, потому что со сканом будет выполняться минут 20, не хочется нагружать машину

а вы не нагружайте машину:
уберите хинт и покажите оценочный план для второго запроса.

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

никто не пострадает от оценочных
12 авг 16, 12:02    [19533471]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить