Руководство по созданию эффективных запросов

Листинг 5.5 

Первичный ключ и ограничение уникальности
ALTER TABLE booking
ADD CONSTRAINT booking_pkey PRIMARY KEY (booking_id);
ALTER TABLE booking
ADD CONSTRAINT booking_booking_ref_key UNIQUE (booking_ref);
Поскольку атрибут 
booking_ref
не допускает неопределенных значений, 
в качестве первичного ключа можно выбрать либо 
booking_id
, либо 
book-
ing_ref
.
Как показано на ER-диаграмме в главе 1, столбец 
frequency_flyer_id
в таб-
лице 
account
может имеет неопределенные значения, а также является уни-
кальным:
ALTER TABLE account
ADD CONSTRAINT account_freq_flyer_unq_key UNIQUE (frequent_flyer_id);
Уникальный индекс можно создать и без формального определения огра-
ничения уникальности. Нужно только добавить ключевое слово 
unique
:
CREATE UNIQUE INDEX account_freq_flyer ON account (frequent_flyer_id);
Если мы создаем такой индекс уже после того, как данные вставлены в таб-
лицу, команда 
CREATE
UNIQUE
INDEX
проверит уникальность значений, и если 
будут обнаружены какие-либо дубликаты, индекс не будет создан. Для любых 
последующих вставок и обновлений уникальность новых значений также 
будет проверяться.
А как насчет внешних ключей? Создают ли они автоматически какие-либо 
индексы? Существует распространенное заблуждение, что наличие внешнего 
ключа обязательно подразумевает наличие индекса в дочерней таблице. Это 
не так.
Внешний
ключ
– это ограничение ссылочной целостности; таблица, в кото-
рой определен внешний ключ, называется дочерней, а таблица, на которую 
он ссылается, – родительской. Ограничение внешнего ключа гарантирует, 

---

72

Короткие запросы и индексы
что для каждого определенного (не 
NULL
) значения из дочерней таблицы в ро-
дительской таблице есть совпадающее с ним уникальное значение.
Например, для таблицы 
flight
существует ограничение внешнего ключа, 
которое гарантирует, что каждый аэропорт прибытия соответствует сущест-
вующему коду аэропорта:
ALTER TABLE flight
ADD CONSTRAINT arrival_airport_fk FOREIGN KEY (arrival_airport)
REFERENCES airport (airport_code);
Данное ограничение не создает индекс автоматически; если поиск по 
аэропорту прибытия работает медленно, индекс должен быть создан явно:
CREATE INDEX flight_arrival_airport ON flight (arrival_airport);
В главе 3 упоминалось, что уникальные индексы делают вложенные циклы 
эффективными. Если вы обратитесь к рис. 3.7, то поймете, что происходит 
при наличии индекса.
Алгоритм соединения вложенным циклом также можно сочетать с до-
ступом к данным на основе индекса, если у таблицы 
S
есть индекс по 
атрибутам, используемым в условии соединения. Для естественных со-
единений внутренний цикл алгоритма с индексным доступом сокращается 
до перебора нескольких строк таблицы 
S
для каждой строки из таблицы 
R
. 
Внутренний цикл может даже полностью исчезнуть, если индекс по табли-
це 
S
уникален, например если атрибут соединения является первичным 
ключом.
Часто это истолковывается неверно, и считается, что вложенные циклы 
всегда эффективны, когда соединение выполняется по связке первичного 
и внешнего ключей. Однако, как упоминалось ранее, это верно, только если 
столбец в дочерней таблице – внешний ключ – проиндексирован.
Всегда ли следует создавать индекс по столбцу с ограничением внешнего 
ключа? Не всегда. Это имеет смысл только в том случае, если количество 
различных значений достаточно большое. Помните, что индексы с высокой 
селективностью вряд ли пригодятся. Например, у таблицы 
flight
есть огра-
ничение внешнего ключа по 
aircraft_code_id
:
ALTER TABLE flight
ADD CONSTRAINT aircraft_code_fk FOREIGN KEY (aircraft_code)
REFERENCES aircraft (code);
Это ограничение внешнего ключа необходимо, потому что каждому рейсу 
должен быть назначен существующий самолет. Чтобы поддерживать ограни-
чение внешнего ключа, в таблицу 
aircraft
был добавлен первичный ключ. 
Однако в этой таблице всего 12 строк. Следовательно, нет необходимости 
создавать индекс по столбцу 
aircraft_code
таблицы 
flight
. У этого столбца 
только 12 различных значений, поэтому индекс по этому столбцу использо-
ваться не будет.
Чтобы проиллюстрировать это утверждение, посмотрим на запрос из лис-
тинга 5.6. Этот запрос выбирает все рейсы между аэропортами Джона Кен-

---

Выбор критериев фильтрации 

73
неди (JFK) и О’Хара (ORD) с 14 по 16 августа 2020 г. Для каждого рейса мы 
выбираем его номер, дату вылета, модель самолета и количество пассажиров.