Главная страница
Навигация по странице:

  • SELECT * FROM bookings WHERE total_amount > 1000000 ORDER BY book_date DESC;

  • CREATE INDEX bookings_book_date_part_key ON bookings ( book_date ) WHERE total_amount > 1000000;

  • SELECT * FROM bookings WHERE total_amount > 1100000 ... 194 А в таком не будет:SELECT * FROM bookings WHERE total_amount > 900000 ...

  • Контрольные вопросы и задания

  • SELECT count( * ) FROM tickets WHERE passenger_name = IVAN IVANOV;

  • SELECT count( * ) FROM ticket_flights WHERE fare_conditions = Comfort; SELECT count( * ) FROM ticket_flights WHERE fare_conditions = Business;

  • \d имя_таблицы \di+ имя_индекса

  • DROP INDEX bookings_book_date_part_key; CREATE INDEX bookings_total_amount_key ON bookings ( total_amount );

  • CREATE INDEX tickets_pass_name ON tickets ( passenger_name text_pattern_ops );

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


    Скачать 0.9 Mb.
    НазваниеУчебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
    Дата14.11.2022
    Размер0.9 Mb.
    Формат файлаpdf
    Имя файла4_1_1_SQL_Postgres_Bazovy_kurs_2017.pdf
    ТипУчебно-практическое пособие
    #787182
    страница22 из 28
    1   ...   18   19   20   21   22   23   24   25   ...   28
    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.
    1   ...   18   19   20   21   22   23   24   25   ...   28


    написать администратору сайта