Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
|
8.5 Частичные индексы PostgreSQL поддерживает очень интересный тип индексов — частичные индексы. Такой индекс формируется не для всех строк таблицы, а лишь для их подмножества. Это достигается с помощью использования условного выражения, называемого пре- дикатом индекса . Предикат вводится с помощью предложения WHERE. В качестве иллюстрации создадим частичный индекс для таблицы «Бронирования» (bookings). Представим, что руководство компании интересуют бронирования на сумму свыше одного миллиона рублей. Такая выборка выполняется с помощью за- проса 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, соответствовало предикату индекса. Это означает, что либо условие должно быть точно таким же, как использованное в предикате частичного индекса при его создании, либо условие запроса должно математически сводиться к предикату индекса, а система должна суметь это понять. Например, в таком запросе индекс будет использоваться: SELECT * FROM bookings WHERE total_amount > 1100000 ... 194 А в таком не будет: 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 Проведите следующий эксперимент: выполните этот запрос несколько раз под- ряд при отсутствии индекса, а затем создайте индекс и опять выполните этот запрос несколько раз подряд. SELECT count( * ) FROM tickets WHERE passenger_name = 'IVAN IVANOV'; Вы увидите, что время выполнения повторных запросов к таблице сокращает- ся, причем, когда создан индекс, оно сокращается на порядок. Как вы думаете, почему? 3. Известно, что индекс значительно ускоряет работу, если при выполнении за- проса из таблицы отбирается лишь небольшая часть строк. Если же эта доля ве- лика, скажем, половина строк или более, то большого положительного эффекта от наличия индекса уже не будет, а возможно даже, что не будет практически никакого эффекта. Наша задача — проверить это утверждение на практике. Обратимся к таблице «Перелеты» (ticket_flights). В ней есть столбец «Класс обслуживания» (fare_conditions). Этот столбец отличается тем, что в нем мо- гут присутствовать лишь три различных значения: «Comfort», «Business» и «Economy». Если секундомер в утилите psql выключен, то включите его. 195 Выполните запросы, подсчитывающие количество строк, в которых атрибут fare_conditions принимает одно из трех возможных значений. Каждый из запро- сов выполните три-четыре раза, поскольку время может немного изменяться, и подсчитайте среднее время. Обратите внимание на число строк, возвращае- мое функцией count для каждого значения атрибута fare_conditions. При этом среднее время выполнения запросов для трех различных значений атрибута fare_conditions будет различаться незначительно, поскольку в каждом случае СУБД просматривает все строки таблицы. SELECT count( * ) FROM ticket_flights WHERE fare_conditions = 'Comfort'; SELECT count( * ) FROM ticket_flights WHERE fare_conditions = 'Business'; SELECT count( * ) FROM ticket_flights WHERE fare_conditions = 'Economy'; Создайте индекс по столбцу fare_conditions. Конечно, в реальной ситуации та- кой индекс вряд ли целесообразно создавать, но нам он нужен для эксперимен- тов. Проделайте те же эксперименты с таблицей ticket_flights. Будет ли различать- ся среднее время выполнения запросов для различных значений атрибута fare_conditions? Почему это имеет место? В завершение этого упражнения отметим, что в случае ошибки планировщи- ка при использовании индекса возможно не только отсутствие положительного эффекта, но и значительный отрицательный эффект. 4. Для одной из таблиц создайте индекс по двум столбцам, причем по одному из них укажите убывающий порядок значений столбца, а по другому — возраста- ющий. Значения NULL у первого столбца должны располагаться в начале, а у второго — в конце. Посмотрите полученный индекс с помощью команд утили- ты psql \d имя_таблицы \di+ имя_индекса Обратите внимание, что первая команда выведет не только имя индекса, но так- же и имена столбцов, по которым он создан, а вторая команда выведет размер индекса. Подберите запросы, в которых созданный индекс предположительно должен использоваться, а также запросы, в которых он использоваться, по вашему мне- нию, не будет. Проверьте ваши гипотезы, выполнив запросы. Объясните полу- ченные результаты. 5. В сложных базах данных целесообразно использование комбинаций индек- сов. Иногда бывают более полезны комбинированные индексы по нескольким столбцам, чем отдельные индексы по единичным столбцам. В реальных ситуа- циях часто приходится делать выбор, т. е. находить компромисс, между, напри- мер, созданием двух индексов по каждому из двух столбцов таблицы либо со- зданием одного индекса по двум столбцам этой таблицы, либо созданием всех трех индексов. Выбор зависит от того, запросы какого вида будут выполняться 196 чаще всего. Предложите какую-нибудь таблицу в базе данных «Авиаперевозки» и смоделируйте ситуации, в которых вы приняли бы одно из этих трех возмож- ных решений. Воспользуйтесь документацией на PostgreSQL. 6. Предложите какую-нибудь таблицу в базе данных «Авиаперевозки» и смодели- руйте ситуацию, в которой было бы целесообразно использование индекса на основе функции или скалярного выражения от двух или более столбцов. 7.* В разделе документации 5.3.5 «Внешние ключи» говорится о том, что в неко- торых ситуациях бывает целесообразно создавать индекс по столбцам внешне- го ключа ссылающейся таблицы. Это позволит ускорить выполнение операций DELETE и UPDATE над главной (ссылочной) таблицей. Подумайте, есть ли такие таблицы в базе данных «Авиаперевозки», в отноше- нии которых было бы целесообразно поступить так, как говорится в докумен- тации. 8.* В тексте главы был показан пример использования частичного индекса для таб- лицы «Бронирования» (bookings). Для его создания мы выполняли команду 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 «Частичные ин- дексы» и попробуйте смоделировать ситуацию в предметной области «Авиапе- ревозки», когда частичный индекс дал бы больший эффект, чем обычный ин- декс. 9. Когда выполняются запросы с поиском по шаблону LIKE или регулярными выра- жениями POSIX, тогда для того, чтобы использовался индекс, нужно предусмот- реть следующее. Если параметры локализации системы отличаются от стан- дартной настройки «C» (например, «ru_RU.UTF-8»), тогда при создании индекса необходимо указать так называемый класс операторов. Существуют различные классы, например, для столбца типа text это будет text_pattern_ops. 197 CREATE INDEX tickets_pass_name ON tickets ( passenger_name text_pattern_ops ); Индексы со специальными классами операторов пригодны не для всех типов за- просов. Поэтому, возможно, потребуется создать еще и индекс с классом опера- торов по умолчанию. Самостоятельно изучите этот вопрос с помощью раздела документации 11.9 «Семейства и классы операторов». 198 9 Транзакции Детальное понимание механизмов выполнения транзакций придет с опытом. В этом разде- ле мы дадим самое первое представление об этом важном и мощном инструменте, которым обладают все серьезные СУБД, включая PostgreSQL. Транзакция — это совокупность операций над базой данных, которые вместе образу- ют логически целостную процедуру, и могут быть либо выполнены все вместе, либо не будет выполнена ни одна из них. В простейшем случае транзакция состоит из од- ной операции. Транзакции являются одним из средств обеспечения согласованности (непротиво- речивости) базы данных, наряду с ограничениями целостности (constraints), накла- дываемыми на таблицы. Транзакция переводит базу данных из одного согласован- ного состояния в другое согласованное состояние. В качестве примера транзакции в базе данных «Авиаперевозки» можно привести процедуру бронирования билета. Она будет включать операции INSERT, выполняемые над таблицами «Бронирования» (bookings), «Билеты» (tickets) и «Перелеты» (ticket_flights). В результате выполнения этой транзакции должно обеспечиваться следующее соотношение: значение атрибу- та total_amount в строке таблицы bookings должно быть равно сумме значений ат- рибута amount в строках таблицы ticket_flights, связанных с этой строкой таблицы bookings. Если операции данной транзакции будут выполнены частично, тогда может оказаться, например, что общая сумма бронирования будет не равна сумме стоимо- стей перелетов, включенных в это бронирование. Очевидно, что это несогласованное состояние базы данных. Транзакция может иметь два исхода: первый — изменения данных, произведенные в ходе ее выполнения, успешно зафиксированы в базе данных, а второй исход таков — транзакция отменяется, и отменяются все изменения, выполненные в ее рамках. От- мена транзакции называется откатом (rollback). Сложные информационные системы, как правило, предполагают одновременную ра- боту многих пользователей с базой данных, поэтому современные СУБД предлагают специальные механизмы для организации параллельного, т. е. одновременного, вы- полнения транзакций. Реализованы такие механизмы и в PostgreSQL. Реализация транзакций в СУБД PostgreSQL основана на многоверсионной модели (Multiversion Concurrency Control, MVCC). Эта модель предполагает, что каждый SQL- оператор видит так называемый снимок данных (snapshot), т. е. то согласованное со- стояние (версию) базы данных, которое она имела на определенный момент време- ни. При этом параллельно исполняемые транзакции, даже вносящие изменения в базу данных, не нарушают согласованности данных этого снимка. Такой результат в PostgreSQL достигается за счет того, что когда параллельные транзакции изменяют одни и те же строки таблиц, тогда создаются отдельные версии этих строк, доступные соответствующим транзакциям. Это позволяет ускорить работу с базой данных, од- нако требует больше дискового пространства и оперативной памяти. И еще одно важ- ное следствие применения MVCC — операции чтения никогда не блокируются опе- рациями записи, а операции записи никогда не блокируются операциями чтения. Согласно теории баз данных, транзакции должны обладать следующими свойства- ми: 199 1. Атомарность (atomicity). Это свойство означает, что либо транзакция будет за- фиксирована в базе данных полностью, т. е. будут зафиксированы результаты выполнения всех ее операций, либо не будет зафиксирована ни одна операция транзакции. 2. Согласованность (consistency). Это свойство предписывает, чтобы в результате успешного выполнения транзакции база данных была переведена из одного со- гласованного состояния в другое согласованное состояние. 3. Изолированность (isolation). Во время выполнения транзакции другие транзак- ции должны оказывать по возможности минимальное влияние на нее. 4. Долговечность (durability). После успешной фиксации транзакции пользователь должен быть уверен, что данные надежно сохранены в базе данных и впослед- ствии могут быть извлечены из нее, независимо от последующих возможных сбоев в работе системы. Для обозначения всех этих четырех свойств используется аббревиатура ACID. При параллельном выполнении транзакций теоретически возможны следующие фе- номены. 1. Потерянное обновление (lost update). Когда разные транзакции одновременно изменяют одни и те же данные, то после фиксации изменений может оказаться, что одна транзакция перезаписала данные, обновленные и зафиксированные другой транзакцией. 2. «Грязное» чтение (dirty read). Транзакция читает данные, измененные парал- лельной транзакцией, которая еще не завершилась. Если эта параллельная транзакция в итоге будет отменена, тогда окажется, что первая транзакция про- читала данные, которых нет в системе. 3. Неповторяющееся чтение (non-repeatable read). При повторном чтении тех же самых данных в рамках одной транзакции оказывается, что другая транзакция успела изменить и зафиксировать эти данные. В результате тот же самый запрос выдает другой результат. 4. Фантомное чтение (phantom read). Транзакция выполняет повторную выбор- ку множества строк в соответствии с одним и тем же критерием. В интервале времени между выполнением этих выборок другая транзакция добавляет но- вые строки и успешно фиксирует изменения. В результате при выполнении по- вторной выборки в первой транзакции может быть получено другое множество строк. 5. Аномалия сериализации (serialization anomaly). Результат успешной фиксации группы транзакций, выполняющихся параллельно, не совпадает с результатом ни одного из возможных вариантов упорядочения этих транзакций, если бы они выполнялись последовательно. Перечисленные феномены, а также ситуации, в которых они имеют место, будут рас- смотрены подробно и проиллюстрированы примерами. Поясним кратко, в чем состоит смысл концепции сериализации. Для двух транзак- ций, скажем, A и B, возможны только два варианта упорядочения при их последова- тельном выполнении: сначала A, затем B или сначала B, затем A. Причем результаты 200 реализации двух вариантов могут в общем случае не совпадать. Например, при вы- полнении двух банковских операций — внесения некоторой суммы денег на какой-то счет и начисления процентов по этому счету — важен порядок выполнения операций. Если первой операцией будет увеличение суммы на счете, а второй — начисление процентов, тогда итоговая сумма будет больше, чем при противоположном порядке выполнения этих операций. Если описанные операции выполняются в рамках двух различных транзакций, то оказываются возможными различные итоговые результа- ты, зависящие от порядка их выполнения. Сериализация двух транзакций при их параллельном выполнении означает, что полу- ченный результат будет соответствовать одному из двух возможных вариантов упоря- дочения транзакций при их последовательном выполнении. При этом нельзя сказать точно, какой из вариантов будет реализован. Если распространить эти рассуждения на случай, когда параллельно выполняется бо- лее двух транзакций, тогда результат их параллельного выполнения также должен быть таким, каким он был бы в случае выбора некоторого варианта упорядочения транзакций, если бы они выполнялись последовательно, одна за другой. Конечно, чем больше транзакций, тем больше вариантов их упорядочения. Концепция сериа- лизации не предписывает выбора какого-то определенного варианта. Речь идет лишь об одном из них. В том случае, если СУБД не сможет гарантировать успешную сериализацию группы параллельных транзакций, тогда некоторые из них могут быть завершены с ошибкой. Эти транзакции придется выполнить повторно. Для конкретизации степени независимости параллельных транзакций вводится по- нятие уровня изоляции транзакций. Каждый уровень характеризуется перечнем тех феноменов, которые на данном уровне не допускаются. Всего в стандарте SQL предусмотрено четыре уровня. Каждый более высокий уровень включает в себя все возможности предыдущего. 1. READ UNCOMMITTED. Это самый низкий уровень изоляции. Согласно стандарту SQL, на этом уровне допускается чтение «грязных» (незафиксированных) дан- ных. Однако в PostgreSQL требования, предъявляемые к этому уровню, более строгие, чем в стандарте: чтение «грязных» данных на этом уровне не допуска- ется. 2. READ COMMITTED. Не допускается чтение «грязных» (незафиксированных) данных. Таким образом, в PostgreSQL уровень READ UNCOMMITTED совпадает с уровнем READ COMMITTED. Транзакция может видеть только те незафиксиро- ванные изменения данных, которые произведены в ходе выполнения ее самой. 3. REPEATABLE READ. Не допускается чтение «грязных» (незафиксированных) данных и неповторяющееся чтение. В PostgreSQL на этом уровне не допускает- ся также фантомное чтение. Таким образом, реализация этого уровня является более строгой, чем того требует стандарт SQL. Это не противоречит стандарту. 4. SERIALIZABLE. Не допускается ни один из феноменов, перечисленных выше, в том числе и аномалии сериализации. 201 Конкретный уровень изоляции обеспечивает сама СУБД с помощью своих внутрен- них механизмов. Его достаточно указать в команде при старте транзакции. Одна- ко программист может дополнительно использовать некоторые операторы и прие- мы программирования, например, устанавливать блокировки на уровне отдельных строк или всей таблицы. Это будет показано в конце главы. По умолчанию PostgreSQL использует уровень изоляции READ COMMITTED. |