Учебное пособие СанктПетербург бхвпетербург

• Название: Учебное пособие СанктПетербург бхвпетербург
• Дата: 12.02.2023
• Размер: 1.88 Mb.
• Имя файла: sql_primer.pdf
• Тип: Учебное пособие; #933464
• страница: 14 из 20

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

Глава 8. Индексы
строк в таблице может быть заменен поиском в упорядоченном индексе и переходом к строке таблицы по прямому указателю (ссылке).
Следует учитывать, что индексы требуют и некоторых накладных расходов на их со- здание и поддержание в актуальном состоянии при выполнении обновлений данных в таблицах. Поэтому использовать индексы нужно осмотрительно.
Когда вы создавали таблицы, то видели, что, как правило, для них предусматрива- лось создание первичного ключа — PRIMARY KEY. В таких случаях СУБД сама создает индекс, который позволяет поддерживать реализацию этого ограничения. Ведь при наличии первичного ключа не допускается появление в таблице строк с одинаковы- ми его значениями. Индекс позволяет выполнять проверку на дублирование очень быстро.
Для некоторых таблиц, например «Посадочные талоны» (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 — для уникального ключа.
242

8.1. Общая информация
В описании также присутствует список столбцов, по которым создан индекс, и тип индекса — в данном случае это btree, т. е. B-дерево. PostgreSQL может создавать ин- дексы различных типов, но по умолчанию используется так называемое B-дерево.
Такой индекс подходит для большинства типовых задач. В этой главе мы будем рас- сматривать только индексы на основе B-дерева.
Наличие индекса может ускорить выборку строк из таблицы, если он создан по столб- цам, на основе значений которых и производится выборка. Поэтому, как правило,
при разработке и эксплуатации баз данных не ограничиваются только индексами,
которые автоматически создает СУБД, а создают дополнительные индексы с учетом наиболее часто выполняющихся выборок.
Для создания индексов предназначена команда
CREATE INDEX имя-индекса
ON имя-таблицы ( имя-столбца, ...);
В этой команде имя индекса можно не указывать. В качестве примера давайте созда- дим индекс для таблицы «Аэропорты» по столбцу airport_name.
CREATE INDEX
ON airports ( airport_name );
CREATE INDEX
Посмотрим описание нового индекса:
\d airports
Индексы:
"airports_airport_name_idx" btree (airport_name)
Обратите внимание, что имя индекса, сформированное автоматически, включает имя таблицы, имя столбца и суффикс idx.
Прежде чем приступить к экспериментам с индексами, нужно включить в утилите psql секундомер с помощью следующей команды:
\timing on
243

Глава 8. Индексы
Когда необходимость в использовании секундомера отпадет, для его отключения нужно будет сделать так:
\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.
244

8.1. Общая информация
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
Когда индекс уже создан, о его поддержании в актуальном состоянии заботится СУБД.
Конечно, следует учитывать, что это требует от СУБД затрат ресурсов и времени. Ин- декс, созданный по столбцу, участвующему в соединении двух таблиц, может позво- лить ускорить процесс выборки записей из таблиц. При выборке записей в отсорти- рованном порядке индекс также может помочь, если сортировка выполняется по тем столбцам, по которым индекс создан.
245

Глава 8. Индексы
8.2. Индексы по нескольким столбцам
Индексы могут создаваться не только по одному столбцу, но и сразу по несколь- ким. Например, индекс для поддержания первичного ключа таблицы «Перелеты»
(ticket_flights) создан по двум столбцам: ticket_no и flight_id.
Если в SQL-запросе есть предложение ORDER BY, то индекс может позволить избе- жать этапа сортировки выбранных строк. Однако если SQL-запрос просматривает значительную часть таблицы, то явная сортировка выбранных строк может оказаться быстрее, чем использование индекса. Создавая индексы с целью ускорения доступа к данным, нужно учитывать предполагаемую долю строк таблицы (селективность),
выбираемых при выполнении типичных запросов, в которых создаваемый индекс будет использоваться. Если эта доля велика (т. е. селективность низка), тогда наличие индекса может не дать ожидаемого эффекта. Индексы более полезны, когда из табли- цы выбирается лишь небольшая доля строк, т. е. при высокой селективности выборки.
В случае использования предложения ORDER BY в комбинации с LIMIT n явная сорти- ровка (при отсутствии индекса) потребует обработки всех строк таблицы ради того,
чтобы определить первые n строк. Но если есть индекс по тем же столбцам, по кото- рым производится сортировка ORDER BY, то эти первые n строк могут быть извлечены непосредственно, без сканирования остальных строк вообще.
Если для таблицы «Билеты» еще не создан индекс по столбцу 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 мс
Удалите этот индекс и повторите запрос. Время его выполнения увеличится, вероят- но, на два порядка.
246

8.3. Уникальные индексы
При создании индексов может использоваться не только возрастающий порядок зна- чений в индексируемом столбце, но и убывающий. По умолчанию порядок возраста- ющий, при этом значения 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 для таблицы «Самолеты»:
CREATE UNIQUE INDEX aircrafts_unique_model_key
ON aircrafts ( model );
В этом случае мы уже не сможем ввести в таблицу aircrafts строки, имеющие оди- наковые наименования моделей самолетов. Конечно, мы могли при создании табли- цы задать ограничение уникальности для столбца model, и тогда уникальный индекс был бы создан автоматически.
Важно, что в уникальных индексах допускается наличие значений NULL, поскольку они считаются не совпадающими ни с какими другими значениями, в том числе и друг с другом. Если уникальный индекс создан по нескольким атрибутам, то совпа- дающими считаются лишь те комбинации значений атрибутов в двух строках, в ко- торых совпадают значения всех соответствующих атрибутов.
247

Глава 8. Индексы
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 преобразует все символы в нижний регистр. Индекс стро- ится уже на основе преобразованных значений, поэтому при поиске строки в таблице искомое значение сначала переводится в нижний регистр, а затем осуществляется поиск в индексе.
Индексы на основе выражений требуют больше ресурсов для их создания и поддер- жания при вставке и обновлении строк в таблице. Зато при выполнении выборок,
построенных на основе сложных выражений, работа происходит с меньшими на- кладными расходами, поскольку в индексе хранятся уже вычисленные значения этих выражений, пусть даже самых сложных. Поэтому такие индексы целесообразно ис- пользовать тогда, когда выборки производятся многократно, и время, затраченное на создание и поддержание индекса, компенсируется (окупается) при выполнении выборок из таблицы.
8.5. Частичные индексы
PostgreSQL поддерживает очень интересный тип индексов — частичные индексы.
Такой индекс формируется не для всех строк таблицы, а лишь для их подмножества.
248

8.5. Частичные индексы
Это достигается с помощью использования условного выражения, называемого пре-
дикатом индекса
. Предикат вводится с помощью предложения WHERE.
В качестве иллюстрации создадим частичный индекс для таблицы «Бронирования».
Представим, что руководство компании интересуют бронирования на сумму свыше одного миллиона рублей. Такая выборка выполняется с помощью запроса
SELECT *
FROM bookings
WHERE total_amount > 1000000
ORDER BY book_date DESC;
book_ref |
book_date
| total_amount
----------+------------------------+--------------
D7E9AA
| 2016-10-06 09:29:00+08 |
1062800.00
EF479E
| 2016-09-30 19:58:00+08 |
1035100.00 3AC131
| 2016-09-28 05:06:00+08 |
1087100.00 3B54BB
| 2016-09-02 21:08:00+08 |
1204500.00 65A6EA
| 2016-08-31 10:28:00+08 |
1065600.00
(5 строк)
Время: 90,996 мс
Хотя сортировка строк производится по датам бронирования в убывающем порядке,
т. е. от более поздних дат к более ранним, тем не менее, включать ключевое слово
DESC в индексное выражение, когда индекс создается только по одному столбцу, нет необходимости. Это объясняется тем, что PostgreSQL умеет совершать обход индекса как по возрастанию, так и по убыванию с одинаковой эффективностью.
Обратите внимание, что индексируемый столбец book_date не участвует в форми- ровании предиката индекса — в предикате используется столбец total_amount. Это вполне допустимая ситуация.
CREATE INDEX bookings_book_date_part_key
ON bookings ( book_date )
WHERE total_amount > 1000000;
CREATE INDEX
Повторим вышеприведенный запрос. Теперь он выдаст результат за время, на поря- док меньшее, чем без использования частичного индекса.
В разделе документации 11.8 «Частичные индексы» сказано, что для того, чтобы
СУБД использовала частичный индекс, необходимо, чтобы условие, записанное в за- просе в предложении WHERE, соответствовало предикату индекса. Это означает, что
249

Глава 8. Индексы
либо условие должно быть точно таким же, как использованное в предикате частич- ного индекса при его создании, либо условие запроса должно математически сво- диться к предикату индекса, а система должна суметь это понять. Например, в таком запросе индекс будет использоваться:
SELECT *
FROM bookings
WHERE total_amount > 1100000 ...
А в таком не будет:
SELECT *
FROM bookings
WHERE total_amount > 900000 ...
Частичные индексы выглядят очень привлекательно, но в большинстве случаев их преимущества по сравнению с обычными индексами будут минимальными (см. за- дание 9). Однако размер частичного индекса будет меньше, чем размер обычного.
Для получения заметного полезного эффекта от их применения необходимы опыт и понимание того, как работают индексы в PostgreSQL.
Контрольные вопросы и задания
1. Предположим, что для какой-то таблицы создан уникальный индекс по двум столбцам: column1 и column2. В таблице есть строка, у которой значение ат- рибута column1 равно ABC, а значение атрибута column2 — NULL. Мы решили добавить в таблицу еще одну строку с такими же значениями ключевых атри- бутов, т. е. column1 — ABC, а column2 — NULL.
Как вы думаете, будет ли операция вставки новой строки успешной или завер- шится с ошибкой? Объясните ваше решение.
2. В тексте главы шла речь о выполнении одной и той же выборки из таблицы «Би- леты» (tickets) при наличии индекса по столбцу passenger_name и при его отсутствии. Вы видели, что наличие индекса ускоряет выполнение запроса по- чти на порядок.
Если секундомер в утилите psql выключен, то включите его с помощью команды
\timing on
250

Контрольные вопросы и задания
Проведите следующий эксперимент: выполните этот запрос несколько раз под- ряд при отсутствии индекса, а затем создайте индекс и опять выполните этот запрос несколько раз подряд.
SELECT count( * )
FROM tickets
WHERE passenger_name = 'IVAN IVANOV';
Вы увидите, что время выполнения повторных запросов к таблице сокращает- ся, причем, когда создан индекс, оно сокращается на порядок. Как вы думаете,
почему?
3. Известно, что индекс значительно ускоряет работу, если при выполнении за- проса из таблицы отбирается лишь небольшая часть строк. Если же эта доля велика, скажем, половина строк или более, то большого положительного эффек- та от наличия индекса уже не будет, а возможно даже, что не будет практически никакого эффекта. Наша задача — проверить это утверждение на практике.
Обратимся к таблице «Перелеты» (ticket_flights). В ней имеется столбец
«Класс обслуживания» (fare_conditions), который отличается от остальных тем, что в нем могут присутствовать лишь три различных значения: Comfort,
Business и Economy.
Если секундомер в утилите psql выключен, то включите его.
Выполните запросы, подсчитывающие количество строк, в которых атрибут fare_conditions принимает одно из трех возможных значений. Каждый из запросов выполните три-четыре раза, поскольку время может немного изме- няться, и подсчитайте среднее время. Обратите внимание на число строк, ко- торые возвращает функция count для каждого значения атрибута. При этом среднее время выполнения запросов для трех различных значений атрибута fare_conditions будет различаться незначительно, поскольку в каждом слу- чае СУБД просматривает все строки таблицы.
SELECT count( * )
FROM ticket_flights
WHERE fare_conditions = 'Comfort';
SELECT count( * )
FROM ticket_flights
WHERE fare_conditions = 'Business';
251

Глава 8. Индексы
SELECT count( * )
FROM ticket_flights
WHERE fare_conditions = 'Economy';
Создайте индекс по столбцу fare_conditions. Конечно, в реальной ситуации такой индекс вряд ли целесообразен, но нам он нужен для экспериментов.
Проделайте те же эксперименты с таблицей ticket_flights. Будет ли разли- чаться среднее время выполнения запросов для различных значений атрибута fare_conditions? Почему это имеет место?
В завершение этого упражнения отметим, что в случае ошибки планировщи- ка при использовании индекса возможно не только отсутствие положительного эффекта, но и значительный отрицательный эффект.
4. Для одной из таблиц создайте индекс по двум столбцам, причем по одному из них укажите убывающий порядок значений столбца, а по другому — возрастаю- щий. Значения NULL у первого столбца должны располагаться в начале, а у вто- рого — в конце. Посмотрите полученный индекс с помощью команд psql
\d имя_таблицы
\di+ имя_индекса
Обратите внимание, что первая команда выведет не только имя индекса, но так- же и имена столбцов, по которым он создан, а вторая команда выведет размер индекса.
Подберите запросы, в которых созданный индекс предположительно должен использоваться, а также запросы, в которых он использоваться, по вашему мне- нию, не будет. Проверьте ваши гипотезы, выполнив запросы. Объясните полу- ченные результаты.
5. В сложных базах данных целесообразно использование комбинаций индек- сов. Иногда бывают более полезны комбинированные индексы по нескольким столбцам, чем отдельные индексы по единичным столбцам. В реальных ситу- ациях часто приходится делать выбор, т. е. находить компромисс, между, на- пример, созданием двух индексов по каждому из двух столбцов таблицы либо созданием одного индекса по двум столбцам этой таблицы, либо созданием всех трех индексов. Выбор зависит от того, запросы какого вида будут выполняться чаще всего. Предложите какую-нибудь таблицу в базе данных «Авиаперевозки»
и смоделируйте ситуации, в которых вы приняли бы одно из этих трех возмож- ных решений. Воспользуйтесь документацией на PostgreSQL.
252

Контрольные вопросы и задания
6. Предложите какую-нибудь таблицу в базе данных «Авиаперевозки» и смодели- руйте ситуацию, в которой было бы целесообразно использование индекса на основе функции или скалярного выражения от двух или более столбцов.
7.* В разделе документации 5.3.5 «Внешние ключи» говорится о том, что в неко- торых ситуациях бывает целесообразно создавать индекс по столбцам внешне- го ключа ссылающейся таблицы. Это позволит ускорить выполнение операций
DELETE и UPDATE над главной (ссылочной) таблицей.
Подумайте, есть ли такие таблицы в базе данных «Авиаперевозки», в отноше- нии которых было бы целесообразно поступить так, как говорится в докумен- тации.
8.* В тексте главы был показан пример использования частичного индекса для таб- лицы «Бронирования». Для его создания мы выполняли команду
CREATE INDEX bookings_book_date_part_key
ON bookings ( book_date )
WHERE total_amount > 1000000;
Проведите эксперимент с целью сравнения эффекта от создания частичного ин- декса с эффектом от создания обычного индекса по столбцу total_amount. Для этого удалите частичный индекс, а затем создайте обычный индекс.
DROP INDEX bookings_book_date_part_key;
CREATE INDEX bookings_total_amount_key
ON bookings ( total_amount );
Теперь выполните тот же запрос к таблице bookings, который был приведен в тексте главы:
SELECT *
FROM bookings
WHERE total_amount > 1000000
ORDER BY book_date DESC;
Сравните время выполнения с тем временем, которое было получено при ис- пользовании частичного индекса. Очень вероятно, что различия времени вы- полнения запроса будут незначительными.
Самостоятельно ознакомьтесь с разделом документации 11.8 «Частичные ин- дексы» и попробуйте смоделировать ситуацию в предметной области «Авиапе- ревозки», когда частичный индекс дал бы больший эффект, чем обычный ин- декс.
253

Глава 8. Индексы
9. Когда выполняются запросы с поиском по шаблону LIKE или регулярными вы- ражениями POSIX, тогда для того, чтобы использовался индекс, нужно преду- смотреть следующее. Если параметры локализации системы отличаются от стандартной настройки «C» (например, «ru_RU.UTF-8»), тогда при создании индекса необходимо указать так называемый класс операторов. Существуют различные классы операторов, например, для столбца типа text это будет text_pattern_ops.
CREATE INDEX tickets_pass_name
ON tickets ( passenger_name text_pattern_ops );
Индексы со специальными классами операторов пригодны не для всех типов за- просов. Поэтому, возможно, потребуется создать еще и индекс с классом опера- торов по умолчанию. Самостоятельно изучите этот вопрос с помощью раздела документации 11.9 «Семейства и классы операторов».
254