Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
|
EXPLAIN ANALYZE SELECT a.aircraft_code AS a_code, a.model, ( SELECT count( r.aircraft_code ) FROM routes r WHERE r.aircraft_code = a.aircraft_code ) AS num_routes FROM aircrafts a GROUP BY 1, 2 ORDER BY 3 DESC; А в этом варианте коррелированный подзапрос раскрыт и заменен внешним соединением. Причина использования внешнего соединения в том, что может найтись модель самолета, не обслуживающая ни одного маршрута, и если не использовать внешнее соединение, она вообще не попадет в результирующую выборку. EXPLAIN ANALYZE SELECT a.aircraft_code AS a_code, a.model, count( r.aircraft_code ) AS num_routes FROM aircrafts a LEFT OUTER JOIN routes r ON r.aircraft_code = a.aircraft_code GROUP BY 1, 2 ORDER BY 3 DESC; Исследуйте планы выполнения обоих запросов. Попытайтесь найти объяснение различиям в эффективности их выполнения. Чтобы получить усредненную кар- тину, выполните каждый запрос несколько раз. Поскольку таблицы, участвую- щие в запросах, небольшие, то различие по абсолютным затратам времени вы- 248 полнения будет незначительным. Но если бы число строк в таблицах было боль- шим, то экономия ресурсов сервера могла оказаться заметной. Предложите аналогичную пару запросов к базе данных «Авиаперевозки». Про- ведите необходимые эксперименты с вашими запросами. 9. Одним из способов повышения производительности является изменение схе- мы данных, связанное с денормализацией, а именно: создание материализо- ванных представлений. В главе 5 было описано такое материализованное пред- ставление — «Маршруты» (routes). Команда для его создания была приведена в главе 6. Проведите эксперимент: сначала выполните выборку из готового представле- ния, а затем ту выборку, которая это представление формирует. EXPLAIN ANALYZE SELECT * FROM routes; EXPLAIN ANALYZE WITH f3 AS ( SELECT f2.flight_no, ... Поскольку второй запрос очень громоздкий, то можно поступить таким обра- зом: сначала сохраните его в текстовом файле, а затем выполните с помощью команды \i утилиты psql. Вы увидите, что затраты времени отличаются практически на два порядка. Ко- нечно, нужно помнить, что материализованные представления необходимо пе- риодически обновлять, чтобы их содержимое было актуальным. 10.* Одним из способов повышения производительности является изменение схе- мы данных, связанное с денормализацией, а именно: использование вычисляе- мых столбцов таблиц. В качестве примера рассмотрим таблицу «Бронирования» (bookings). В ней столбец «Полная сумма бронирования» (total_amount) являет- ся вычисляемым. Мы не будем сейчас говорить о том, каким образом его значе- ния синхронизируются с данными в таблице «Перелеты» (ticket_flifgts), а лишь рассмотрим два запроса, возвращающие полные суммы бронирований. Пред- положим, что указанного столбца в таблице bookings не было бы. Тогда запрос, возвращающий полные суммы бронирований, выглядел бы так: EXPLAIN ANALYZE SELECT b.book_ref, sum( tf.amount ) FROM bookings b, tickets t, ticket_flights tf WHERE b.book_ref = t.book_ref AND t.ticket_no = tf.ticket_no GROUP BY 1 ORDER BY 1; Но благодаря наличию вычисляемого столбца total_amount те же сведения мож- но получить с гораздо меньшими затратами ресурсов: EXPLAIN ANALYZE SELECT book_ref, total_amount FROM bookings ORDER BY 1; Попробуйте предложить еще какой-нибудь вычисляемый столбец для одной из таблиц базы данных «Авиаперевозки». Проведите эксперименты, подтвержда- ющие эффективность вашего решения. 249 11.* Одним из способов повышения производительности является изменение схемы данных, а именно: использование временных таблиц. Предположим, что нам предстоит сделать много выборок из представления «Рейсы» (flights_v), в таком случае имеет смысл подумать о создании временной таблицы: CREATE TEMP TABLE flights_tt AS SELECT * FROM flights_v; Сформируйте планы для получения простой выборки из представления и из временной таблицы и сравните полученные результаты. Как вы думаете, поче- му план, сформированный для получения даже простой выборки из представ- ления, многоуровневый? EXPLAIN ANALYZE SELECT * FROM flights_v; EXPLAIN ANALYZE SELECT * FROM flights_tt; Выполните более сложные запросы к представлению и временной таблице и сравните полученные результаты. Включайте в команду EXPLAIN опцию ANALYZE, чтобы увидеть фактические затраты времени. Подумайте, при выполнении каких запросов к базе данных «Авиаперевозки» было бы целесообразно создать временную таблицу. Создайте ее и проведите эксперименты, подтверждающие эффективность ее использования. 12. Одним из способов повышения производительности является изменение схемы данных, связанное с денормализацией, а именно: создание индексов. Выполните следующий простой запрос к таблице «Билеты» (tickets): EXPLAIN ANALYZE SELECT count( * ) FROM tickets WHERE passenger_name = 'IVAN IVANOV'; Создайте индекс по столбцу passenger_name: CREATE INDEX passenger_name_key ON tickets ( passenger_name ); Теперь повторите запрос и сравните полученные планы и фактические резуль- таты. Предложите какой-нибудь запрос к базе данных «Авиаперевозки», для выпол- нения которого было бы целесообразно создать индекс. Создайте индекс и по- вторите запрос. Изучите полученный план, посмотрите, используется ли индекс планировщиком. 13.* В самом конце главы мы выполняли оптимизацию запроса путем создания ин- декса и модификации текста запроса. Был сформирован такой запрос: EXPLAIN ANALYZE SELECT num_tickets, count( * ) AS num_bookings FROM ( SELECT b.book_ref, count( * ) FROM bookings b, tickets t WHERE date_trunc( 'mon', b.book_date ) = '2016-09-01' AND t.book_ref = b.book_ref GROUP BY b.book_ref ) AS count_tickets( book_ref, num_tickets ) 250 GROUP by num_tickets ORDER BY num_tickets DESC; Были проведены два эксперимента с параметрами планировщика enable_hashjoin и enable_nestloop при наличии индекса по таблице tickets. SET enable_hashjoin = off; SET SET enable_nestloop = off; SET Однако полученные планы детально рассмотрены не были. Задание. Проанализируйте эти планы. Посмотрите, в каких случаях использу- ются и в каких не используются индексы по таблицам bookings и tickets. Вспом- ните о таком понятии, как селективность, т. е. доля строк, выбираемых из таб- лицы. 14. В столбцах таблиц могут содержаться значения NULL. При сортировке строк по значениям таких столбцов СУБД по умолчанию ведет себя так, как будто зна- чение NULL превосходит по величине любые другие значения. В результате по- лучается, что если задан возрастающий порядок сортировки, то значения NULL будут идти последними, если же порядок сортировки убывающий, тогда они бу- дут первыми. Принимая решение о создании индексов, нужно учитывать требу- емый порядок сортировки и желаемое расположение строк со значениями NULL в выборке (в ее начале или в конце). Давайте создадим таблицу, содержащую такое число строк, что использование индекса планировщиком становится очень вероятным, и выполним с этой таб- лицей ряд экспериментов. CREATE TABLE nulls AS SELECT num::integer, 'TEXT' || num::text AS txt FROM generate_series( 1, 200000 ) AS gen_ser( num ); SELECT 200000 Проиндексируем таблицу по числовому столбцу. Поскольку мы не указываем порядок сортировки явным образом, то по умолчанию он будет возрастающим, как если бы мы использовали ключевое слово ASC. CREATE INDEX nulls_ind ON nulls ( num ); CREATE INDEX Добавим в таблицу одну строку, содержащую значение NULL в индексируемом столбце: INSERT INTO nulls VALUES ( null, 'TEXT' ); INSERT 0 1 Теперь посмотрим, будет ли использоваться индекс в следующем запросе: 251 EXPLAIN SELECT * FROM nulls ORDER BY num; Да, индекс используется. QUERY PLAN ---------------------------------------------------------------- Index Scan using nulls_ind on nulls (cost=0.42..5852.42 rows=200000 width=14) ,→ (1 строка) Вы можете убедиться, что строка со значением NULL окажется в выводе самой последней. Поскольку в нашей таблице очень много строк, воспользуемся пред- ложением OFFSET: SELECT * FROM nulls ORDER BY num OFFSET 199995; num | txt --------+------------ 199996 | TEXT199996 199997 | TEXT199997 199998 | TEXT199998 199999 | TEXT199999 200000 | TEXT200000 | TEXT (6 строк) Модифицируем запрос, явно указав, что значения NULL должны располагаться в самом начале выборки, и посмотрим, будет ли использоваться индекс теперь. EXPLAIN SELECT * FROM nulls ORDER BY num NULLS FIRST; Теперь индекс не используется. Вместо этого выполняется последовательное сканирование таблицы и сортировка выбранных строк. QUERY PLAN -------------------------------------------------------------------- Sort (cost=24110.14..24610.14 rows=200000 width=14) Sort Key: num NULLS FIRST -> Seq Scan on nulls (cost=0.00..3081.00 rows=200000 width=14) (3 строки) Задание 1. Ниже приведена модифицированная команды выборки из таблицы nulls. Не выполняя эту команду, попытайтесь ответить, будет ли использовать- ся созданный нами индекс при выполнении такой выборки, а затем проверьте вашу гипотезу на практике. EXPLAIN SELECT * FROM nulls ORDER BY num DESC NULLS FIRST; Обратите внимание на ключевое слово Backward в плане выполнения запроса. Что оно означает? Задание 2. Модифицируйте команду создания индекса таким образом, чтобы он использовался при выполнении следующей выборки: 252 SELECT * FROM nulls ORDER BY num NULLS FIRST; Задание 3. Выполните аналогичные эксперименты, задавая убывающий поря- док сортировки с помощью ключевого слова DESC и изменяя расположение зна- чений NULL в выборке с помощью ключевых слов NULLS FIRST и NULLS LAST предложения ORDER BY. С помощью команды EXPLAIN ANALYZE посмотрите, каким будет фактическое время выполнения команд. За дополнительной ин- формацией обратитесь к описанию команды CREATE INDEX, приведенному в документации. 15. Обратитесь к запросам в главе 6. Выполните команду EXPLAIN для всех этих за- просов и ознакомьтесь с планами, которые создаст планировщик. В планах мо- гут встречаться наименования методов, которые не были рассмотрены в тексте главы, однако они должны быть вам интуитивно понятны. 16. В разделе документации 19.7 «Планирование запросов» приведены парамет- ры, с помощью которых можно влиять на решения, принимаемые планиров- щиком. В тексте главы мы уже говорили о параметрах, управляющих выбо- ром способа соединения наборов строк, и показали простой пример. Также бы- ло сказано и о том, что при установке значений параметров enable_hashjoin, enable_mergejoin и enable_nestloop в «off» не накладывается полного запрета на использование соответствующих методов. Вместо этого конкретному методу назначается очень высокая стоимость. Давайте проведем следующий экспери- мент: запретим использование всех методов соединения наборов строк и вы- полним запрос, в котором соединяются две таблицы. SET enable_hashjoin = off; SET SET enable_mergejoin = off; SET SET enable_nestloop = off; SET Запрос выводит информацию о числе мест в самолетах всех моделей. EXPLAIN SELECT a.model, count( * ) FROM aircrafts a, seats s WHERE a.aircraft_code = s.aircraft_code GROUP BY a.aircraft_code; QUERY PLAN -------------------------------------------------------------------- GroupAggregate (cost=10000000000.41..10000000109.95 rows=9 width=56) Group Key: a.aircraft_code -> Nested Loop (cost=10000000000.41..10000000103.16 rows=1339 width=48) -> Index Scan using aircrafts_pkey on aircrafts a (cost=0.14..12.27 rows=9 width=48) -> Index Only Scan using seats_pkey on seats s 253 (cost=0.28..8.61 rows=149 width=4) Index Cond: (aircraft_code = a.aircraft_code) (6 строк) Обратите внимание на оценки стоимости выполнения запроса. Резкое повы- шение оценок происходит именно в узле, отвечающем за соединение наборов строк. Эти оценки не означают, что время выполнения запроса будет стремить- ся к бесконечности. С помощью команды EXPLAIN ANALYZE выполните запрос и убедитесь в этом сами. Задание. Самостоятельно ознакомьтесь с содержанием раздела документации 19.7 «Планирование запросов», а также раздела 14.3 «Управление планировщи- ком с помощью явных предложений JOIN» и проведите эксперименты с запро- сами, приведенными в главе 6 пособия, получая различные варианты планов и сравнивая их. Ваша задача — понять, как изменения значений этих параметров влияют на план выполнения запроса. Однако для того чтобы понимать, когда и почему нуж- но изменять значения конкретных параметров, правильно оценивать степень и направленность их влияния, понимать взаимосвязь параметров, требуется опыт и изучение документации. 17. Самостоятельно ознакомьтесь с разделом документации 14.2 «Статистика, ис- пользуемая планировщиком». 18. Команда EXPLAIN имеет опцию BUFFERS. Ознакомьтесь с ней самостоятельно по разделу документации 14.1 «Использование EXPLAIN». 19. При массовом вводе данных в базу данных производительность СУБД может снижаться по ряду причин, например, при наличии индексов они обновляют- ся при вводе каждой новой строки в таблицу, а это требует дополнительных за- трат ресурсов. Для повышения производительности СУБД в подобных ситуаци- ях в документации предлагается ряд мер, например, удаление индексов перед началом массового ввода данных и пересоздание индексов после завершения такого ввода. Ознакомьтесь с этими мерами самостоятельно по разделу доку- ментации 14.4 «Наполнение базы данных». Смоделируйте ситуации, описанные в этом разделе документации, и выполните рекомендуемые действия. 254 11 Рекомендуемые источники [1] Гарсиа-Молина, Г. Системы баз данных. Полный курс: пер. с англ. / Гектор Гарсиа- Молина, Джеффри Д. Ульман, Дженнифер Уидом. — М.: Вильямс, 2003. — 1088 с.: ил. [2] Грофф, Дж. Р. SQL. Полное руководство: пер. с англ. / Джеймс Р. Грофф, Пол Н. Вайнберг, Эндрю Дж. Оппель. — 3-е изд. — М.: Вильямс, 2015. — 960 с.: ил. [3] Дейт, К. Дж. Введение в системы баз данных: пер. с англ. / Крис Дж. Дейт. — 8-е изд. — М.: Вильямс, 2005. — 1328 с.: ил. [4] Новиков Б. Настройка приложений баз данных / Борис Новиков, Генриетта Дом- бровская. — СПб.: БХВ-Петербург, 2012. — 240 с.: ил. [5] Селко, Д. Стиль программирования Джо Селко на SQL: пер. с англ. / Джо Селко. — М.: Русская редакция; СПб.: Питер, 2006. — 206 с.: ил. [6] Официальный сайт PostgreSQL: http://www.postgresql.org [7] Postgres Professional: http://postgrespro.ru 255 Учебное издание Моргунов Евгений Павлович ЯЗЫК SQL. БАЗОВЫЙ КУРС Учебно-практическое пособие при поддержке Postgres Professional http://postgrespro.ru Редакторы Е. В. Рогов, П. В. Лузанов Оригинал-макет и верстка И. Е. Панченко Обложка А. В. Климковский 256 |