CREATE TABLE modes AS
SELECT num::integer, 'LOW' || num::text AS mode
FROM generate_series( 1, 100000 ) AS gen_ser( num )
UNION ALL
SELECT num::integer, 'HIGH' || ( num - 100000 )::text AS mode
FROM generate_series( 100001, 200000 ) AS gen_ser( num );
SELECT 200000
Проиндексируем таблицу по числовому столбцу.
CREATE INDEX modes_ind ON modes ( num );
CREATE INDEX
Из всего множества строк нас будут интересовать только две:
SELECT * FROM modes WHERE mode IN ( 'LOW1', 'HIGH1' );
223
num
| mode
--------+-------
1 | LOW1 100001 | HIGH1
(2 строки)
На первом терминале начнем транзакцию и
обновим одну строку из тех двух строк, которые были показаны в предыдущем запросе.
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN
UPDATE modes SET mode = 'HIGH1' WHERE num = 1;UPDATE 1
На втором терминале тоже начнем транзакцию и обновим другую строку из тех двух строк, которые были показаны выше.
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN
UPDATE modes SET mode = 'LOW1' WHERE num = 100001;UPDATE 1
Обратите внимание, что обе команды UPDATE были выполнены, ни одна из них не ожидает завершения другой транзакции.
Попробуем завершить транзакции. Сначала — на первом терминале:
COMMIT;COMMIT
А потом на втором терминале:
COMMIT;COMMIT
Посмотрим, что получилось.
SELECT * FROM modes WHERE mode IN ( 'LOW1', 'HIGH1' );num
| mode
--------+-------
1 | HIGH1 100001 | LOW1
(2 строки)
Теперь система смогла сериализовать параллельные транзакции и зафиксиро- вать их обе. Как вы думаете, почему это удалось? Обосновывая ваш ответ, при- мите во внимание тот результат, который был бы получен при последователь- ном выполнении транзакций.
224
10.* В тексте главы был рассмотрен пример транзакции над таблицами базы дан- ных «Авиаперевозки». Давайте теперь создадим две параллельные транзакции и выполним их с уровнем изоляции SERIALIZABLE. Отправим также двоих пас- сажиров теми же самыми рейсами, что и ранее, но операции распределим меж- ду двумя транзакциями. Отличие заключается в том, что в начале транзакции будут выполняться выборки из таблицы ticket_flights. Для упрощения ситуации не будем предварительно проверять наличие свободных мест, т. к. сейчас для нас важно не это. Итак, первая транзакция:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
SELECT * FROM ticket_flights WHERE flight_id = 13881;
ticket_no
| flight_id | fare_conditions | amount
---------------+-----------+-----------------+----------
0005433848165 |
13881 | Business
| 99800.00 0005433848007 |
13881 | Economy
| 33300.00
(82 строки)
INSERT INTO bookings ( book_ref, book_date, total_amount )
VALUES ( 'ABC123', bookings.now(), 0 );
INSERT 0 1
INSERT INTO tickets
( ticket_no, book_ref, passenger_id, passenger_name )
VALUES
( '9991234567890', 'ABC123', '1234 123456',
'IVAN PETROV' );
INSERT 0 1
INSERT INTO ticket_flights
( ticket_no, flight_id, fare_conditions, amount )
VALUES
( '9991234567890', 13881, 'Business', 12500 );
INSERT 0 1
UPDATE bookings
SET total_amount = 12500
WHERE book_ref = 'ABC123';
UPDATE 1
COMMIT;
COMMIT
Вторая транзакция:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
225
SELECT * FROM ticket_flights WHERE flight_id = 5572;
ticket_no
| flight_id | fare_conditions | amount
---------------+-----------+-----------------+----------
0005433847924 |
5572 | Business
| 99800.00 0005433847890 |
5572 | Economy
| 33300.00
(100 строк)
INSERT INTO bookings
( book_ref, book_date, total_amount )
VALUES
( 'ABC456', bookings.now(), 0 );
INSERT 0 1
INSERT INTO tickets
( ticket_no, book_ref, passenger_id, passenger_name )
VALUES
( '9991234567891', 'ABC456', '4321 654321',
'PETR IVANOV' );
INSERT 0 1
INSERT INTO ticket_flights
( ticket_no, flight_id, fare_conditions, amount )
VALUES
( '9991234567891', 5572, 'Business', 12500 );
INSERT 0 1
UPDATE bookings
SET total_amount = 12500
WHERE book_ref = 'ABC456';
UPDATE 1
COMMIT;
ОШИБКА: не удалось сериализовать доступ из-за зависимостей чтения/записи между транзакциями
,→
ПОДРОБНОСТИ: Reason code: Canceled on identification as a pivot,
during commit attempt.
,→
ПОДСКАЗКА: Транзакция может завершиться успешно при следующей попытке.
,→
Задание 1.
Попытайтесь объяснить, почему транзакции не удалось сериализо- вать. Что можно сделать, чтобы удалось зафиксировать обе транзакции? Одно из возможных решений — понизить уровень изоляции. Другим решением мо- жет быть создание индекса по столбцу flight_id для таблицы ticket_flights. По- чему создание индекса может помочь? Обратитесь за разъяснениями к разделу документации 13.2.3 «Уровень изоляции Serializable».
Задание 2.
В первой транзакции условие в команде SELECT такое: ...WHERE
flight_id = 13881. В команде вставки в таблицу ticket_flights значение поля
226
flight_id также равно 13881. Во второй транзакции в этих же командах исполь- зуется значение 5572. Поменяйте местами значения в командах SELECT и по- вторите эксперименты, выполнив транзакции параллельно с уровнем изоляции
SERIALIZABLE. Почему сейчас наличие индекса не помогает зафиксировать обе транзакции? Вспомните, что аномалия сериализации — это ситуация, когда па- раллельное выполнение транзакций приводит к результату, невозможному ни при каком из вариантов упорядочения этих же транзакций при их последова- тельном выполнении.
227
10 Повышение производительностиЗаставить PostgreSQL работать быстро — эта задача может возникнуть с ростом объема данных.
Мы покажем лишь самые простые методы ее решения.
10.1 Основные понятияДля понимания материала этой главы необходимо сначала познакомиться с такими важными понятиями, как метод доступа и способ соединения наборов строк.
Метод доступахарактеризует тот способ, который используется для просмотра таб- лиц и извлечения только тех строк, которые соответствуют критерию отбора. Су- ществуют различные методы доступа: последовательный просмотр (sequential scan),
при котором индекс не используется, и группа методов, основанных на использова- нии индекса. К ней относятся: просмотр по индексу (index scan), просмотр исклю- чительно на основе индекса (index only scan) и
просмотр на основе битовой карты(bitmap scan).
Поскольку и таблицы, и индексы хранятся на диске, то для работы с ними эти объ- екты считываются в память, в которой они представлены разбитыми на отдельные фрагменты, называемые страницами. Эти страницы имеют специальную структуру.
Размер страниц по умолчанию составляет 8 килобайт.
При выполнении
последовательного просмотра (sequential scan) обращения к ин- дексам не происходит, а строки извлекаются из табличных страниц в соответствии с критерием отбора. В том случае, когда в запросе нет предложения WHERE, тогда из- влекаются все строки таблицы. Данный метод применяется, когда требуется выбрать все строки таблицы или значительную их часть, т. е. когда так называемая
селектив-ностьвыборки низка. В таком случае обращение к индексу не ускорит процесс про- смотра, а возможно даже и замедлит.
Просмотр на основе индекса(index scan) предполагает обращение к индексу, со- зданному для данной таблицы. Поскольку в индексе для каждого ключевого значения содержатся уникальные идентификаторы строк в таблицах, то после отыскания в ин- дексе нужного ключа производится обращение к соответствующей странице таблицы и извлечение искомой строки по ее идентификатору. При этом нужно учитывать, что хотя записи в индексе упорядочены, но обращения к страницам таблицы происходят хаотически, поскольку строки в таблицах не упорядочены. В таком случае при низкой селективности выборки, т. е. когда из таблицы отбирается значительное число строк,
использование индексного поиска
может не только не давать ускорения работы, но даже и снижать производительность.
Просмотр исключительно на основе индекса(index only scan), как следует из на- звания метода, не должен, казалось бы, требовать обращения к строкам таблицы, по- скольку все данные, которые нужно получить с помощью запроса, в этом случае при- сутствуют в индексе. Однако в индексе нет информации о
видимости строк транзак- циям — нельзя быть уверенным, что данные, полученные из индекса, видны текущей транзакции. Поэтому сначала выполняется обращение к карте видимости (visibility
228
map), которая существует для каждой таблицы. В ней одним битом отмечены стра- ницы, на которых содержатся только те версии строк, которые видны всем без ис- ключения транзакциям. Если полученная из индекса версия строки находится на та- кой странице, значит, эта строка видна текущей транзакции и обращаться к самой таблице не требуется. Поскольку размер карты видимости очень мал, то в результа- те сокращается объем операций ввода/вывода. Если же строка находится на страни- це, не отмеченной в карте видимости, тогда происходит обращение и к таблице; в результате никакого выигрыша по быстродействию в сравнении с обычным индекс- ным поиском не достигается. Просмотр исключительно на основе индекса особенно эффективен, когда выбираемые данные изменяются редко. Он может применяться,
когда в предложении SELECT
указаны только имена столбцов, по которым создан ин- декс.
Просмотр на основе битовой карты(bitmap scan) является модификацией про- смотра на основе индекса. Данный метод позволяет оптимизировать индексный по- иск за счет того, что сначала производится поиск в индексе для всех искомых строк и формирование так называемой битовой карты, в которой указывается, в каких стра- ницах таблицы эти строки содержатся. После того как битовая карта сформирована,
выполняется извлечение строк из страниц таблицы, но при этом обращение к каждой странице производится
только один раз.
Другим важным понятием является
способ соединения наборов строк (join). Набор строк может быть получен из таблицы с помощью одного их методов доступа, опи- санных выше. Набор строк может быть получен не только из одной таблицы, а может быть результатом соединения других наборов. Важно различать способ соединения таблиц (JOIN) и способ соединения наборов строк. Первое понятие относится к языку
SQL и является высокоуровневым, логическим, оно не касается вопросов реализа- ции. А второе относится именно к реализации, это — механизм непосредственного выполнения соединения наборов строк. Принципиально важным является то, что за один раз соединяются только два набора строк.
Существует три способа соединения: вложенный цикл (nested loop), хеширование
(hash join) и слияние (merge join). Они имеют свои особенности, которые PostgreSQL
учитывает при выполнении конкретных запросов.
Суть способа
«вложенный цикл» в том, что перебираются строки из «внешнего»
набора и для каждой из них выполняется поиск соответствующих строк во «внутрен- нем» наборе.
Если соответствующие строки найдены, то выполняется их соединение со строкой из «внешнего» набора. При этом способы выбора строк из обоих наборов могут быть различными. Метод поддерживает соединения как на основе равенства значений атрибутов (эквисоединения), так и любые другие виды условий. Поскольку он не требует подготовительных действий, то способен быстро приступить к непо- средственной выдаче результата. Метод эффективен для небольших выборок.
При
соединении хешированием строки одного набора помещаются в хеш-таблицу,
содержащуюся в памяти, а строки из второго набора перебираются, и для каждой из них проверяется наличие соответствующих строк в хеш-таблице. Ключом хеш- таблицы является тот столбец, по которому выполняется соединение наборов строк.
Как правило, число строк в том наборе, на основе которого строится хеш-таблица,
меньше, чем во втором наборе. Это позволяет уменьшить ее размер и ускорить про- цесс обращения к ней. Данный метод работает только при выполнении эквисоедине-
229
ний, поскольку для хеш-таблицы имеет смысл только проверка на равенство прове- ряемого значения одному из ее ключей. Метод эффективен для больших выборок.
Соединение методом слиянияпроизводится аналогично сортировке слиянием. В
этом случае оба набора строк должны быть предварительно отсортированы по тем столбцам, по которым производится соединение. Затем параллельно читаются стро- ки из обоих наборов и сравниваются значения столбцов, по которым производится соединение. При совпадении значений формируется результирующая строка. Этот процесс продолжается до исчерпания строк в обоих наборах. Этот метод, как и ме- тод соединения хешированием, работает только при выполнении эквисоединений.
Он пригоден для работы с большими наборами строк.
10.2 Методы просмотра таблицТеперь мы можем перейти к рассмотрению планов выполнения запросов.
Прежде чем приступить к непосредственному выполнению каждого запроса,
PostgreSQL формирует
план его выполнения. Чтобы достичь хорошей производитель- ности, этот план должен учитывать свойства данных. Планированием занимается специальная подсистема — планировщик (planner). Просмотреть план выполнения запроса можно с помощью команды EXPLAIN. Для детального понимания планов вы- полнения сложных запросов требуется опыт. Мы изложим лишь основные приемы работы с этой командой.
Структура плана запроса представляет собой дерево, состоящее из так называемых
узлов плана(plan nodes). Узлы на нижних уровнях дерева отвечают за просмотр и вы- дачу строк таблиц, которые
осуществляются с помощью методов доступа, описанных выше. Если конкретный запрос требует выполнения операций агрегирования, соеди- нения таблиц, сортировки, то над узлами выборки строк будут располагаться допол- нительные узлы дерева плана. Например, для соединения наборов строк будут ис- пользоваться способы, которые мы только что рассмотрели. Для каждого узла дерева плана команда EXPLAIN выводит по одной строке, при этом выводятся также оцен- ки стоимости выполнения операций на каждом узле, которые делает планировщик.
В случае необходимости для конкретных узлов могут выводиться дополнительные строки. Самая первая строка плана содержит общую оценку стоимости выполнения данного запроса.
Запустите утилиту psql и введите простой запрос:
EXPLAIN SELECT * FROM aircrafts;В ответ получим план выполнения запроса:
QUERY PLAN
----------------------------------------------------------
Seq Scan on aircrafts (cost=0.00..1.09 rows=9 width=52)
(1 строка)
Поскольку в этом запросе нет предложения WHERE, он должен просмотреть все строки таблицы, поэтому планировщик выбирает
последовательный просмотр(sequential scan). В скобках приведены важные параметры плана.
230
Первое число означает оценку ресурсов, требуемых для того, чтобы приступить к вы- воду данных. В нашем примере эта оценка равна нулю, поскольку никакие дополни- тельные операции с выбранными строками не предполагаются, и PostgreSQL может сразу же выводить прочитанные строки.
Второе число — это оценка общей стоимости выполнения запроса. Формируя эту оценку, планировщик исходит из предположения, что данный узел плана запроса вы- полняется до конца, т. е. извлекаются все имеющиеся строки таблицы. Однако в ряде случаев на практике это может оказаться и не так, если узел-родитель прекратит свою работу досрочно, например, в случае использования в запросе SELECT предложения
LIMIT, которое ограничивает выборку записей из таблицы конкретным их числом.
Обе оценки стоимости выполнения выражаются в неких
условных единицах, которые вычисляются на основе ряда параметров сервера баз данных. При этом не важно, в каких конкретно единицах производится измерение стоимости: важны соотноше- ния стоимостей. Для каждого запроса планировщик формирует несколько планов.
При
сравнении различных вариантов плана, как правило, для выполнения выбира- ется тот, который имеет наименьшую общую стоимость выполнения запроса. Одна- ко при работе с курсорами этот принцип можно изменить с помощью специального параметра планировщика cursor_tuple_fraction (курсоры в учебном пособии не рас- сматриваются).
Далее в выводе идет общее число строк, которые должны быть извлечены (возвра- щены) на данном узле плана, также при условии выполнения этого узла до полного завершения. В нашем примере число строк равно 9. Это число является оценкой, ко- торую планировщик получает на основе статистики, накапливаемой в специальных системных таблицах.
Последним параметром узла плана идет оценка среднего размера строк, которые вы- водятся на данном узле плана запроса. В нашем примере размер (ширина) строки данных оценивается в 52 байта.
В том случае, когда нас не интересуют численные оценки, можно воспользоваться параметром COSTS OFF:
EXPLAIN ( COSTS OFF ) SELECT * FROM aircrafts;QUERY PLAN
-----------------------
Seq Scan on aircrafts
(1 строка)
Сформируем запрос с предложением WHERE:
EXPLAIN SELECT * FROM aircrafts WHERE model 'Air';QUERY PLAN
---------------------------------------------------------
Seq Scan on aircrafts (cost=0.00..1.11 rows=1 width=52)
Filter: (model 'Air'::text)
(2 строки)
Поскольку наложено дополнительное условие на строки, выбираемые из таблицы,
то ниже узла плана, отвечающего за их последовательную выборку, добавляется еще один узел, описывающий критерий отбора строк.
231
Filter: (model 'Air'::text)
Поскольку наложено условие отбора строк, то оценка их числа изменилась с 9 на 1. В
данном случае планировщик неточно оценил число выбираемых строк — фактически их будет три.
Обратите внимание, что по своей форме вывод команды EXPLAIN также является вы- боркой, поэтому в конце выборки, как обычно,
выводится информация о числе строк в ней, т. е. в дереве плана. Это не число строк, которые будут выбраны из таблицы. В
данном случае это
(2 строки)
Теперь усложним запрос, добавив в него сортировку данных:
Скачать 0.9 Mb.