Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П

• Название: Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
• Дата: 14.11.2022
• Размер: 0.9 Mb.
• Имя файла: 4_1_1_SQL_Postgres_Bazovy_kurs_2017.pdf
• Тип: Учебно-практическое пособие; #787182
• страница: 21 из 28

FROM (
VALUES ( 'SU9', 3, 20, 'F' ),
( '773', 5, 30, 'I' ),
( '763', 4, 25, 'H' ),
( '733', 3, 20, 'F' ),
( '320', 5, 25, 'F' ),
( '321', 4, 20, 'F' ),
( '319', 3, 20, 'F' ),
( 'CN1', 0, 10, 'B' ),
( 'CR2', 2, 15, 'D' )
)
AS aircraft_info (
aircraft_code, max_seat_row_business,
max_seat_row_economy, max_letter
)
Здесь aircraft_info определяет имя виртуальной таблицы, а aircraft_code,
max_seat_row_business, max_seat_row_economy, max_letter — имена ее атрибутов.
Эти атрибуты можно использовать во всех частях команды SELECT, как если бы это были атрибуты обычной таблицы.
Остальные виртуальные таблицы создаются аналогичным способом.
Для соединения таблиц используется ключевое слово CROSS JOIN, хотя в данном случае вместо этого можно было просто поставить запятые.
185

Как это и бывает всегда, четыре таблицы образуют декартово произведение из своих строк, а затем на основе условия WHERE «лишние» строки отбрасывают- ся. В этом условии используется условный оператор CASE. Он позволяет нам поставить допустимый номер ряда в зависимость от класса обслуживания:
WHERE
CASE WHEN fare_condition = 'Business'
THEN seat_row::integer <= max_seat_row_business
WHEN fare_condition = 'Economy'
THEN seat_row::integer > max_seat_row_business
AND seat_row::integer <= max_seat_row_economy
В этом выражении используется операция приведения типа: seat_row::integer.
Она необходима, т. к. в виртуальной таблице номера рядов представлены в ви- де символьных строк, а для выполнения сравнения числовых значений в данной ситуации нужен целый тип. При написании условного оператора нужно учесть,
что в виртуальной таблице мы указали не количество рядов в бизнес-классе и экономическом классе, а номера последних рядов в этих классах. Поэтому воз- никает конструкция
THEN seat_row::integer > max_seat_row_business
AND seat_row::integer <= max_seat_row_economy
Также проверяем еще одно условие, сравнивая символьные строки:
AND letter <= max_letter;
Последний этап в работе оператора SELECT — это формирование списка выра- жений, которые будут выведены в качестве итоговых данных. Для формирова- ния номера места используется операция конкатенации «| |», которая соединяет номер ряда с буквенным обозначением позиции в ряду.
SELECT aircraft_code, seat_row || letter, fare_condition
Итак, SQL-команда, которая позволит за одну операцию ввести в таблицу «Ме- ста» сразу необходимое число строк, выглядит так:
INSERT INTO seats ( aircraft_code, seat_no, fare_conditions )
-- номер места формируется с помощью конкатенации
-- номера ряда и буквы, обозначающей позицию в ряду
SELECT aircraft_code, seat_row || letter, fare_condition
-- сформируем виртуальную таблицу для всех типов самолетов
-- колонки такие: код самолета, максимальные номера рядов кресел
-- в бизнес-классе и в экономическом классе, максимальный
-- номер кресла в ряду (он обозначается латинской буквой)
FROM (
VALUES ( 'SU9', 3, 20, 'F' ),
( '773', 5, 30, 'I' ),
( '763', 4, 25, 'H' ),
( '733', 3, 20, 'F' ),
( '320', 5, 25, 'F' ),
( '321', 4, 20, 'F' ),
( '319', 3, 20, 'F' ),
( 'CN1', 0, 10, 'B' ),
( 'CR2', 2, 15, 'D' )
186

) AS aircraft_info (
aircraft_code, max_seat_row_business,
max_seat_row_economy, max_letter
)
CROSS JOIN
-- классы обслуживания
( VALUES ( 'Business' ), ( 'Economy' ))
AS fare_conditions ( fare_condition )
CROSS JOIN
-- список номеров рядов кресел
( VALUES ( '1' ), ( '2' ), ( '3' ), ( '4' ), ( '5' ),
( '6' ), ( '7' ), ( '8' ), ( '9' ), ( '10' ),
( '11' ), ( '12' ), ( '13' ), ( '14' ), ( '15' ),
( '16' ), ( '17' ), ( '18' ), ( '19' ), ( '20' ),
( '21' ), ( '22' ), ( '23' ), ( '24' ), ( '25' ),
( '26' ), ( '27' ), ( '28' ), ( '29' ), ( '30' )
) AS seat_rows ( seat_row )
CROSS JOIN
-- список номеров (позиций) кресел в ряду
( VALUES ( 'A' ), ( 'B' ), ( 'C' ), ( 'D' ), ( 'E' ),
( 'F' ), ( 'G' ), ( 'H' ), ( 'I' )
) AS letters ( letter )
-- включаем в результат лишь те строки, у которых номер ряда
-- (число) и позиция в ряду (латинская буква) соответствуют
-- диапазонам, указанным в первой виртуальной таблице
WHERE
CASE WHEN fare_condition = 'Business'
THEN seat_row::integer <= max_seat_row_business
WHEN fare_condition = 'Economy'
THEN seat_row::integer > max_seat_row_business
AND seat_row::integer <= max_seat_row_economy
END
AND letter <= max_letter;
Задание:
модифицируйте команду с учетом того, что в салоне бизнес-класса число мест в ряду должно быть меньше, чем в салоне экономического класса (в приведенном решении мы для упрощения задачи принимали эти числа одина- ковыми).
Попробуйте упростить подзапрос, отвечающий за формирование списка номе- ров рядов кресел:
( VALUES ( '1' ), ( '2' ), ( '3' ), ( '4' ), ( '5' ), ...
Воспользуйтесь функцией generate_series, описанной в разделе документации
9.24 «Функции, возвращающие множества».
187

8 Индексы
Индексы позволяют повысить производительность базы данных. PostgreSQL поддерживает различные типы индексов. Мы ограничимся рассмотрением только индексов на основе B- дерева.
Индекс — это специальная структура данных, которая связана с таблицей и создается на основе данных, содержащихся в ней. Основная цель создания индексов — повы- шение производительности функционирования базы данных.
8.1 Общая информация
Строки в таблицах хранятся в неупорядоченном виде. При выполнении операций вы- борки, обновления и удаления СУБД должна отыскать нужные строки. Для ускорения этого поиска и создается индекс. В принципе он организован таким образом: на ос- нове данных, содержащихся в конкретной строке таблицы, формируется значение элемента (записи) индекса, соответствующего этой строке. Для поддержания соот- ветствия между элементом индекса и строкой таблицы в каждый элемент помеща- ется указатель на строку. Индекс является упорядоченной структурой. Элементы (за- писи) в нем хранятся в отсортированном виде, что значительно ускоряет поиск дан- ных в индексе. После отыскания в нем требуемой записи СУБД переходит к соответ- ствующей строке таблицы по прямой ссылке. Записи индекса могут формироваться на основе значений одного или нескольких полей соответствующих строк таблицы.
Значения этих полей могут комбинироваться и преобразовываться различными спо- собами. Все это определяет разработчик базы данных при создании индекса.
При выполнении поиска конкретных строк в таблице специальная подсистема СУБД,
называемая планировщиком, проверяет, имеется ли для этой таблицы индекс, со- зданный на основе тех же столбцов, что указаны, например, в условии предложения
WHERE. Если такой индекс существует, то планировщик оценивает целесообразность его использования в данном конкретном случае. Если его использование целесооб- разно, то сначала выполняется поиск необходимых значений в индексе, а затем, если такие значения в нем найдены, производится обращение к таблице с использовани- ем указателей, которые хранятся в записях индекса. Таким образом, полный перебор строк в таблице может быть заменен поиском в упорядоченном индексе и переходом к строке таблицы по прямому указателю (ссылке).
Следует учитывать, что индексы требуют и некоторых накладных расходов на их со- здание и поддержание в актуальном состоянии при выполнении обновлений данных в таблицах. Поэтому использовать индексы нужно осмотрительно.
Когда вы создавали таблицы, то видели, что, как правило, для них предусматрива- лось создание первичного ключа — PRIMARY KEY. В таких случаях СУБД сама создает индекс, который позволяет поддерживать реализацию этого ограничения. Ведь при наличии первичного ключа не допускается появление в таблице строк с одинаковы- ми его значениями. Индекс позволяет выполнять проверку на дублирование очень быстро.
188

Для некоторых таблиц, например, «Посадочные талоны» (boarding_passes), было предусмотрено и ограничение уникальности UNIQUE. В этих случаях СУБД также ав- томатически создает индекс, который используется для обеспечения уникальности значений.
Для того чтобы увидеть индексы, созданные для данной таблицы, нужно воспользо- ваться командой утилиты psql:
\d имя_таблицы
Например,
\d boarding_passes
Индексы:
"boarding_passes_pkey" PRIMARY KEY, btree (ticket_no, flight_id)
"boarding_passes_flight_id_boarding_no_key" UNIQUE CONSTRAINT, btree
(flight_id, boarding_no)
,→
"boarding_passes_flight_id_seat_no_key" UNIQUE CONSTRAINT, btree
(flight_id, seat_no)
,→
Каждый индекс, который был создан самой СУБД, имеет типовое имя, состоящее из следующих компонентов:
– имени таблицы и суффикса pkey — для первичного ключа;
– имени таблицы, имен столбцов, по которым создан индекс, и суффикса key — для уникального ключа.
В описании также присутствует список столбцов, по которым создан индекс, и тип индекса — в данном случае это btree, т. е. B-дерево. PostgreSQL может создавать ин- дексы различных типов, но по умолчанию используется так называемое B-дерево.
Такой индекс подходит для большинства типовых задач. В этой главе мы будем рас- сматривать только индексы на основе B-дерева.
Наличие индекса может ускорить выборку строк из таблицы, если он создан по столб- цам, на основе значений которых и производится выборка. Поэтому, как правило,
при разработке и эксплуатации баз данных не ограничиваются только индексами,
которые автоматически создает СУБД, а создают дополнительные индексы с учетом наиболее часто выполняющихся выборок.
Для создания индексов предназначена команда
CREATE INDEX имя_индекса ON имя_таблицы ( имя_столбца, ...);
В этой команде имя индекса можно не указывать. В качестве примера давайте созда- дим индекс для таблицы «Аэропорты» (airports) по столбцу airport_name.
CREATE INDEX ON airports ( airport_name );
CREATE INDEX
Посмотрим описание нового индекса:
\d airports
189

Индексы:
"airports_airport_name_idx" btree (airport_name)
Обратите внимание, что имя индекса, сформированное автоматически, включает имя таблицы, имя столбца и суффикс idx.
Прежде чем приступить к экспериментам с индексами, нужно включить в утилите psql секундомер с помощью следующей команды:
\timing on
Когда необходимость в использовании секундомера отпадет, для его отключения нужно будет сделать так:
\timing off
Теперь psql будет сообщать время, затраченное на выполнение всех команд.
Для практической проверки влияния индекса на скорость выполнения выборок сна- чала выполним следующий запрос:
SELECT count( * ) FROM tickets
WHERE passenger_name = 'IVAN IVANOV';
count
-------
200
(1 строка)
Время: 373,232 мс
Показатели времени, полученные на вашем компьютере, конечно, будут отличаться от значений, приведенных в книге, и — возможно — значительно. Эти показатели нужно рассматривать лишь как качественные ориентиры.
Создадим индекс по столбцу passenger_name, при этом никакого суффикса в имени индекса использовать не будем, поскольку его наличие не является обязательным:
CREATE INDEX passenger_name
ON tickets ( passenger_name );
CREATE INDEX
Время: 4023,408 мс
Посмотрим описание нового индекса:
\d tickets
Индексы:
"passenger_name" btree (passenger_name)
Теперь выполним ту же выборку из таблицы tickets:
190

SELECT count( * ) FROM tickets
WHERE passenger_name = 'IVAN IVANOV';
count
-------
200
(1 строка)
Время: 17,660 мс
Вы видите, что время выполнения выборки при наличии индекса оказалось значи- тельно меньше.
Просмотреть список всех индексов в текущей базе данных можно с помощью коман- ды
\di
или
\di+
Для удаления индекса используется команда:
DROP INDEX имя_индекса;
Давайте удалим созданный нами индекс для таблицы tickets:
DROP INDEX passenger_name;
DROP INDEX
Когда индекс уже создан, о его поддержании в актуальном состоянии заботится СУБД.
Конечно, следует учитывать, что это требует от СУБД затрат ресурсов и времени. Ин- декс, созданный по столбцу, участвующему в соединении двух таблиц, может позво- лить ускорить процесс выборки записей из таблиц. При выборке записей в отсорти- рованном порядке индекс также может помочь, если сортировка выполняется по тем столбцам, по которым индекс создан.
8.2 Индексы по нескольким столбцам
Индексы могут создаваться не только по одному столбцу, но и по нескольким. Напри- мер, индекс для поддержания первичного ключа таблицы «Перелеты» (ticket_flights)
создан по двум столбцам: ticket_no и flight_id.
Если в SQL-запросе есть предложение ORDER BY, то индекс может позволить избе- жать этапа сортировки выбранных строк. Однако если SQL-запрос просматривает значительную часть таблицы, то явная сортировка выбранных строк может оказаться быстрее, чем использование индекса. Создавая индексы с целью ускорения доступа к данным, нужно учитывать предполагаемую долю строк таблицы (селективность),
выбираемых при выполнении типичных запросов, в которых создаваемый индекс будет использоваться. Если эта доля велика (т. е. селективность — низкая), тогда на- личие индекса может не дать ожидаемого эффекта. Индексы более полезны, когда
191

из таблицы выбирается лишь небольшая доля строк, т. е. при высокой селективности
выборки. В случае использования предложения ORDER BY в комбинации с LIMIT n
явная сортировка (при отсутствии индекса) потребует обработки всех строк таблицы ради того, чтобы определить первые n строк. Но если есть индекс по тем же столбцам,
по которым производится сортировка ORDER BY, то эти первые n строк могут быть извлечены непосредственно, без сканирования остальных строк вообще.
Если для таблицы «Билеты» (tickets) еще не создан индекс по столбцу book_ref, то со- здайте его:
CREATE INDEX tickets_book_ref_test_key
ON tickets ( book_ref );
CREATE INDEX
Выполните запрос, в котором используется предложение LIMIT:
SELECT * FROM tickets ORDER BY book_ref LIMIT 5;
Время: 0,442 мс
Удалите этот индекс и повторите запрос. Время его выполнения увеличится, вероят- но, на два порядка.
При создании индексов может использоваться не только возрастающий порядок зна- чений в индексируемом столбце, но также и убывающий. По умолчанию порядок воз- растающий, при этом значения NULL, которые также могут присутствовать в индек- сируемых столбцах, идут последними. При создании индекса можно модифициро- вать поведение по умолчанию с помощью ключевых слов ASC (возрастающий поря- док), DESC (убывающий порядок), NULLS FIRST (эти значения идут первыми) и NULLS
LAST (эти значения идут последними). Например:
CREATE INDEX имя_индекса
ON имя_таблицы
( имя_столбца NULLS FIRST, ... );
CREATE INDEX имя_индекса
ON имя_таблицы
( имя_столбца DESC NULLS LAST, ... );
8.3 Уникальные индексы
Индексы могут также использоваться для обеспечения уникальности значений ат- рибутов в строках таблицы. В таком случае создается уникальный индекс. Для его создания используется команда:
CREATE UNIQUE INDEX имя_индекса
ON имя_таблицы
( имя_столбца, ...);
Например, создадим уникальный индекс по столбцу model для таблицы «Самолеты»
(aircrafts):
192

CREATE UNIQUE INDEX aircrafts_unique_model_key
ON aircrafts ( model );
В этом случае мы уже не сможем ввести в таблицу aircrafts строки, имеющие одина- ковые наименования моделей самолетов. Конечно, мы могли при создании таблицы задать ограничение уникальности для столбца model, и тогда уникальный индекс был бы создан автоматически.
Важно, что в уникальных индексах допускается наличие значений NULL, посколь- ку они считаются не совпадающими ни с какими другими значениями, в том числе и друг с другом. Если уникальный индекс создан по нескольким атрибутам, то сов- падающими считаются лишь те комбинации значений атрибутов в двух строках, в которых совпадают значения всех соответственных атрибутов.
8.4 Индексы на основе выражений
В команде создания индекса можно использовать не только имена столбцов, но также функции и скалярные выражения, построенные на основе столбцов таблицы. Напри- мер, если мы захотим запретить значения столбца model в таблице aircrafts, отлича- ющиеся только регистром символов, то создадим такой индекс:
CREATE UNIQUE INDEX aircrafts_unique_model_key
ON aircrafts ( lower( model ) );
Если теперь попытаться добавить строку, в которой значение атрибута model будет
«Cessna 208 CARAVAN», то PostgreSQL выдаст сообщение об ошибке, даже если значе- ние атрибута aircraft_code будет уникальным.
INSERT INTO aircrafts VALUES ( '123', 'Cessna 208 CARAVAN', 1300);
ОШИБКА: повторяющееся значение ключа нарушает ограничение уникальности "aircrafts_unique_model_key"
,→
ПОДРОБНОСТИ: Ключ "(lower(model))=(cessna 208 caravan)" уже существует.
Встроенная функция lower преобразует все символы в нижний регистр. Индекс стро- ится уже на основе преобразованных значений, поэтому при поиске строки в табли- це искомое значение сначала переводится в нижний регистр, а затем осуществляется поиск в индексе.
Индексы на основе выражений требуют больше ресурсов для их создания и поддер- жания при вставке и обновлении строк в таблице. Зато при выполнении выборок, по- строенных на основе сложных выражений, работа происходит с меньшими наклад- ными расходами, поскольку в индексе хранятся уже вычисленные значения этих вы- ражений, пусть даже самых сложных. Поэтому такие индексы целесообразно исполь- зовать тогда, когда выборки производятся многократно, и время, затраченное на со- здание и поддержание индекса, компенсируется (окупается) при выполнении выбо- рок из таблицы.
193