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

Откуда:
Сообщений: 194
Привет.
Вдруг обнаружилось, что не знаю как составной ключ участвует в поиске.
Есть вторичный ключ по полям A, B и C

1. Участвует ли он в оптимизации по поиску select * from Table where B=value?
2. Участвует ли он в оптимизации по поиску select * from Table where A=value and/or B=value?
3. Участвует ли он в оптимизации по поиску select * from Table where B=value and/or C=value?
4. Так в каком запросе он наверняка участвует?
Спасибо.
27 авг 15, 22:52    [18078694]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
mabanza
4. Так в каком запросе он наверняка участвует?


из приведенных

a =... and b =...
27 авг 15, 23:08    [18078740]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
invm
Member

Откуда: Москва
Сообщений: 9842
mabanza
4. Так в каком запросе он наверняка участвует?
Строго говоря, ни в каком.

Может быть задействован как Index Seek в запросе с where A = value and B = value, если оптимизатор сочтет это выгодным.
В остальных случаях может быть задействован как Index Scan, опять же, если оптимизатор сочтет это выгодным.
28 авг 15, 00:05    [18078926]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
mabanza
Member

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

Хорошо, допустим оптимизатор считает это выгодным. Каково основное правило? Последовательность байт слева направо? Почему B=value не работает?
И еще. Играет ли роль порядок записи в запросе, т. е. "A=value and B=value" ищет с использованием ключа, а "B=value and A=value" уже нет. Так ли это?
Спасибо.
28 авг 15, 00:21    [18078945]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
mabanza
Почему B=value не работает?


а вы мысленно представьте B+ дерево с ключем A, B, C и как вы пытаетесь по предикату B=value спуститься к листовому уровню. (топик ведь об Index Seek?)

mabanza
И еще. Играет ли роль порядок записи в запросе


нет. играет роль порядок колонок в ключе индекса
28 авг 15, 00:27    [18078946]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
mabanza
Почему B=value не работает? .
Представьте себе, что у вас есть бумажный телефонный справочник, где все данные отсоритрованы сначала по имени, а потом по фамилии. По сути индекс по полям firstname, lastname. А теперь, сколько времени у вас займет найти телефоны всех Пупкиных, если вы не знаете его имени?
28 авг 15, 02:13    [18079027]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31985
mabanza
Так в каком запросе он наверняка участвует?
В запросе 2а: where A=value and B=value.
В остальных точно не может использоваться (ну, если строго, то может для сканирования).
28 авг 15, 09:27    [18079412]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21253
alexeyvg
В остальных точно не может использоваться (ну, если строго, то может для сканирования).
Если индекс покрывающий, то он очень даже может использоваться при отсутствии ещё более подходящих. Или именно это имелось в виду под словами "для сканирования"?
28 авг 15, 09:32    [18079433]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31985
Akina
alexeyvg
В остальных точно не может использоваться (ну, если строго, то может для сканирования).
Если индекс покрывающий, то он очень даже может использоваться при отсутствии ещё более подходящих. Или именно это имелось в виду под словами "для сканирования"?
Да, я про это. Поиск то невозможен, само собой, но если таблица широкая, а индекс покрывающий, то сканирование индекса вместо таблицы тоже может дать хороший выйгрыш.
28 авг 15, 09:44    [18079498]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31985
alexeyvg
Akina
пропущено...
Если индекс покрывающий, то он очень даже может использоваться при отсутствии ещё более подходящих. Или именно это имелось в виду под словами "для сканирования"?
Да, я про это. Поиск то невозможен, само собой, но если таблица широкая, а индекс покрывающий, то сканирование индекса вместо таблицы тоже может дать хороший выйгрыш.
То есть, в примере Mind со справочником, может оказаться достаточным прочитать оглавление в 100 страниц, а не весь справочник в 1000 страниц, пусть даже настоящий эффективный поиск по оглавлению невозможен, т.к. неизвестны имена.
28 авг 15, 09:47    [18079522]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
ziktuw
Member

Откуда:
Сообщений: 3552
mabanza
Вдруг обнаружилось, что не знаю как составной ключ участвует в поиске.
Есть вторичный ключ по полям A, B и C ....


С каких пор вторичный ключ стал индексом?
Ни в каком поиске он участвовать не будет.
30 авг 15, 22:05    [18088990]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
ziktuw, а как как вы себе представляет вторичный ключ на ms sql? Есть какие - то варианты кроме как индекс или констреинт (= индекс)?
31 авг 15, 01:36    [18089426]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6202
Mike_za
или констреинт (= индекс)?

С чего бы это?
use tempdb
go
if object_id('tempdb..First') is not null
  drop table dbo.[First];
create table dbo.[First] (
  n int not null primary key
);
if object_id('tempdb..Second') is not null
  drop table dbo.[Second];
create table dbo.[Second] (
  n int not null foreign key references dbo.[First] check (n>0)
);

Проверяем:
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME='Second'

- 2 записи: для foreign key constraint и для check constraint
select * from sys.indexes where object_id=object_id('tempdb..Second')

- 1 запись с типом HEAP. По констрейнтам индексов нет.
31 авг 15, 03:12    [18089444]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Сон Веры Павловны,
Речь же шла о ключах. Констреинт уникальности значения на поле индекс создаст?
31 авг 15, 10:11    [18089798]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6202
Mike_za
Сон Веры Павловны,
Речь же шла о ключах.

Ну так foreign key вполне себе ключ - однако ж без индекса.
31 авг 15, 10:24    [18089840]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Сон Веры Павловны, и вам реально именно он пришел в голову при вопросе автора про "вторичный ключ"?
31 авг 15, 10:28    [18089852]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6202
Mike_za
Сон Веры Павловны, и вам реально именно он пришел в голову при вопросе автора про "вторичный ключ"?

Он мне пришел в голову, когда я увидел
Mike_za
констреинт (= индекс)?

Что же до вторичных ключей - поскольку как объекты они в MSSQL отсутствуют, то, по моему скромному, это чисто логическая сущность в виде условно уникальных полей (candidate keys), не выбранных в качестве первичного ключа. Эта уникальность может поддерживаться за счет соответствующих индексов, а может и не поддерживаться.
31 авг 15, 10:46    [18089920]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Сон Веры Павловны
может поддерживаться за счет соответствующих индексов, а может и не поддерживаться
Это касается любых констрейнтов.
Можно наделать триггеров и вручную всё там контролировать совсем без них.
А в случае, например, обеспечения ссылочной целостности
объектов в разных базах только так и придётся делать.

Я чаще видел термин "альтернативный ключ (AK)", а не "вторичный ключ".
Наверно, не те книжки читал.
Помню, в ErWin это обозначалось именно как AK.
31 авг 15, 13:52    [18090855]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
ziktuw
Member

Откуда:
Сообщений: 3552
Mike_za
ziktuw, а как как вы себе представляет вторичный ключ на ms sql? Есть какие - то варианты кроме как индекс или констреинт (= индекс)?


Цитирую из азбуки по СУБД:
"Внешний (вторичный) ключ - это одно или несколько полей (столбцов) в таблице, содержащих ссылку на поле или поля первичного ключа в другой таблице. Внешний ключ определяет способ объединения таблиц."

Т.е. вторичный ключ - это FOREIGN KEY, т.е. обеспечитель целостности (констрейн) и ничего больше. Оно в MSSQL индексом не является, поэтому на поиск никак не влияет.
31 авг 15, 19:51    [18092871]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
invm
Member

Откуда: Москва
Сообщений: 9842
ziktuw
Т.е. вторичный ключ - это FOREIGN KEY
В общепринятой терминологии:
http://www.sqa.org.uk/e-learning/MDBS01CD/page_15.htm
Secondary Key

An entity may have one or more choices for the primary key. Collectively these are known as candidate keys. One is selected as the primary key. Those not selected are known as secondary keys.
Или синоним - alternate key.

Foreign key называет "вторичным ключом" один автор в своей книге - Информатика. Курс лекций: Учебное пособие, да еще в нескольких русскоязычных статьях - т.е. отсебятина чистой воды.
31 авг 15, 20:20    [18092941]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
ziktuw
Member

Откуда:
Сообщений: 3552
Раз термин неоднозначно воспринят обществом, то может быть, топикстартеру имеет смысл дать пояснения, что же он имел в виду под "вторичным ключом" - FOREIGN KEY или UNIQUE ?

UNIQUE образует индекс (если что).
31 авг 15, 20:36    [18092973]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по составному ключу  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
ziktuw
Раз термин неоднозначно воспринят обществом, то может быть, топикстартеру имеет смысл дать пояснения, что же он имел в виду под "вторичным ключом" - FOREIGN KEY или UNIQUE ?
Все всё уже давно поняли (и даже ответили), кроме вас.
31 авг 15, 22:47    [18093336]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить