Зачем нужна оптимизация SQL? И особенности PostgreSQL

В этой статье рассказывается, почему оптимизация так важна для разработки баз данных. Вы узнаете о различиях между декларативными языками, таки­ми как SQL, и, возможно, более знакомыми вам императивными языками, такими как Java, и о том, как эти различия влияют на стиль программирова­ния. Мы также продемонстрируем, что оптимизация применяется не только к запросам, но и к проектированию баз данных и к архитектуре приложений.

что Подразумевается Под оптимизацией?

В контексте данной книги оптимизация означает любое преобразование, улучшающее производительность системы. Это определение намеренно но­сит очень общий характер, поскольку мы хотим подчеркнуть, что оптими­зация не является отдельным этапом разработки. Довольно часто разработ­чики баз данных сначала пытаются сделать так, чтобы «просто заработало», а уже потом переходят к оптимизации. Мы считаем такой подход непро­дуктивным. Написание запроса без представления о том, сколько времени потребуется для его выполнения, создает проблему, которой можно было бы избежать, правильно написав запрос с самого начала. Мы надеемся, что к тому времени, когда вы прочтете эту книгу, вы будете готовы рассматри­вать оптимизацию и разработку запросов как единый процесс.

Мы представим несколько конкретных техник; однако наиболее важно понимать, как движок базы данных обрабатывает запрос и как планиров­щик запросов решает, какой путь выполнения выбрать. Когда мы обучаем оптимизации на занятиях, то часто говорим: «Думайте как база данных!» Посмотрите на свой запрос с точки зрения движка базы данных и представьте, что он должен сделать, чтобы выполнить этот запрос; представьте, что вы, а не движок, должны выполнить запрос. Поразмыслив над объемом работы, вы можете избежать неоптимальных планов выполнения. Более подробно этот вопрос обсуждается в последующих статьях.

Если вы достаточно долго будете «мыслить как база данных», это станет естественным способом мышления, и вы сразу сможете правильно писать запросы, часто без необходимости дальнейшей оптимизации.

императивный и декларативный Подходы: Почему это сложно

Почему недостаточно написать инструкцию SQL, возвращающую правиль­ный результат? Ведь так мы поступаем, когда пишем код приложения. По­чему в SQL все иначе, и почему два запроса, дающих одинаковый результат, могут разительно отличаться по времени выполнения? Основной источник проблемы в том, что SQL - декларативный язык. Это означает, что когда мы пишем инструкцию SQL, то описываем результат, который хотим получить, но не указываем, как он должен быть получен. Напротив, в императивном языке мы указываем, что делать для получения желаемого результата, то есть записываем последовательность шагов, которые должны быть выпол­нены.

Как обсуждается в главе 2, оптимизатор базы данных выбирает лучший способ выполнить запрос. Что значит «лучший», определяется множеством различных факторов, таких как структура хранения, индексы и статистика.

Рассмотрим простой пример. Взгляните на запросы из листингов 1.1 и 1.2.

Листинг 1.1 ❖ Запрос для выбора рейса с оператором BETWEEN

SELECT flight_id,

departure_airport, arrival_airport

FROM flight

WHERE scheduled_arrival BETWEEN '2020-10-14' AND '2020-10-15';

Листинг 1.2 ❖ Запрос для выбора рейса на определенную дату

SELECT flight_id,

departure_airport, arrival_airport

FROM flight

WHERE scheduled_arrival::date = '2020-10-14';

Эти два запроса выглядят почти одинаково и должны давать одинаковые результаты. Тем не менее время выполнения будет разным, потому что ра­бота, выполняемая движком базы данных, будет различаться. В главе 5 мы объясним, почему это происходит и как выбрать лучший запрос с точки зре­ния производительности.

Людям свойственно мыслить императивно. Обычно, когда мы думаем о выполнении задачи, то думаем о шагах, которые необходимо предпринять. Точно так же, когда мы думаем о сложном запросе, то думаем о последова­тельности условий, которые нужно применить для достижения желаемого результата. Однако если мы заставим движок базы данных строго следовать этой последовательности, результат может оказаться неоптимальным.

Например, попробуем узнать, сколько часто летающих пассажиров с 4-м уровнем вылетают из Чикаго на День независимости. Если на первом этапе вы хотите выбрать всех часто летающих пассажиров с 4-м уровнем, то можно написать что-то вроде:

SELECT * FROM frequent_flyer WHERE level = 4

Затем можно выбрать номера их учетных записей:

SELECT * FROM account WHERE frequent_flyer_id IN (

SELECT frequent_flyer_id FROM frequent_flyer WHERE level = 4

)

А потом, если вы хотите найти все бронирования, сделанные этими людь­ми, можно написать следующее:

WITH level4 AS (

SELECT * FROM account WHERE frequent_flyer_id IN (

SELECT frequent_flyer_id FROM frequent_flyer WHERE level = 4

)

)

SELECT * FROM booking WHERE account_id IN (

SELECT account_id FROM level4

)

Возможно, затем вы захотите узнать, какие из этих бронирований от­носятся к рейсам из Чикаго на 3 июля. Если вы продолжите строить запрос аналогичным образом, то следующим шагом будет код из листинга 1.3.

Листинг 1.3 ❖ Императивно построенный запрос

WITH bk AS (

WITH level4 AS (

SELECT * FROM account WHERE frequent_flyer_id IN (

SELECT frequent_flyer_id FROM frequent_flyer WHERE level = 4

)

)

SELECT * FROM booking WHERE account_id IN (

SELECT account_id FROM level4

)

)

SELECT * FROM bk WHERE bk.booking_id IN (

SELECT booking_id FROM booking_leg

WHERE leg_num=1

AND is_returning IS false

AND flight_id IN (

SELECT flight_id FROM flight

WHERE departure_airport IN ('ORD', 'MDW')

AND scheduled_departure::date = '2020-07-04'

В конце можно подсчитать фактическое количество пассажиров. Это мож­но сделать с помощью запроса из листинга 1.4.

Листинг 1.4 ❖ Подсчет общего количества пассажиров

WITH bk_chi AS (

WITH bk AS (

WITH level4 AS (

SELECT * FROM account WHERE frequent_flyer_id IN (

SELECT frequent_flyer_id FROM frequent_flyer WHERE level = 4

)

)

SELECT * FROM booking WHERE account_id IN (

SELECT account_id FROM level4

)

)

SELECT * FROM bk WHERE bk.booking_id IN (

SELECT booking_id FROM booking_leg

WHERE leg_num=1

AND is_returning IS false

AND flight_id IN (

SELECT flight_id FROM flight

WHERE departure_airport IN ('ORD', 'MDW')

AND scheduled_departure::date = '2020-07-04'

)

)

)

SELECT count(*) FROM passenger

WHERE booking_id IN (

SELECT booking_id FROM bk_chi

)

При построенном таким образом запросе вы не даете планировщику за­просов выбрать лучший путь выполнения, потому что последовательность действий жестко зашита в код. Хотя все строки написаны на декларативном языке, они императивны по своей природе.

Вместо этого, чтобы написать декларативный запрос, просто укажите, что вам нужно получить из базы данных, как показано в листинге 1.5.

Листинг 1.5 ❖ Декларативный запрос для расчета количества пассажиров

SELECT count(*)

FROM booking bk

JOIN booking_leg bl ON bk.booking_id = bl.booking_id

JOIN flight f ON f.flight_id = bl.flight_id

JOIN account a ON a.account_id = bk.account_id

JOIN frequent_flyer ff ON ff.frequent_flyer_id = a.frequent_flyer_id

JOIN passenger ps ON ps.booking_id = bk.booking_id

WHERE level = 4

AND leg_num = 1

AND is_returning IS false

AND departure_airport IN ('ORD', 'MDW')

AND scheduled_departure BETWEEN '2020-07-04' AND '2020-07-05'

Таким образом, вы позволяете базе данных решить, какой порядок опе­раций выбрать. Лучший порядок может отличаться в зависимости от рас­пределения значений в соответствующих столбцах.

Эти запросы лучше выполнять после того, как будут построены все необходимые индек­сы в главе 5.

Цели оптимизации

До сих пор подразумевалось, что эффективный запрос - это запрос, кото­рый выполняется быстро. Однако это определение не является точным или полным. Даже если на мгновение мы сочтем сокращение времени выполне­ния единственной целью оптимизации, остается вопрос: какое время вы­полнения является «достаточно хорошим». Для ежемесячного финансового отчета крупной корпорации завершение в течение одного часа может быть отличным показателем. Для ежедневного маркетингового анализа мину­ты - отличное время выполнения. Для аналитической панели руководителя с дюжиной отчетов обновление в течение 10 секунд может быть хорошим достижением. Для функции, вызываемой из веб-приложения, даже сотня миллисекунд может оказаться недопустимо медленно.

Кроме того, для одного и того же запроса время выполнения может варьи­роваться в разное время дня или в зависимости от загрузки базы данных. В некоторых случаях нас может интересовать среднее время выполнения. Если у системы жесткий тайм-аут, нам может понадобиться измерить про­изводительность, ограничив максимальное время исполнения. Есть также субъективная составляющая при измерении времени отклика. В конечном итоге компания заинтересована в удовлетворении потребностей пользова­телей; в большинстве случаев удовлетворенность пользователей зависит от времени отклика, но это также субъективная характеристика.

Однако помимо времени выполнения могут быть приняты во внимание и другие характеристики. Например, поставщик услуг может быть заинте­ресован в максимальном увеличении пропускной способности системы. Небольшой стартап может быть заинтересован в минимизации использо­вания ресурсов без ущерба для времени отклика системы. Мы знаем одну компанию, которая увеличивала оперативную память, чтобы ускорить вы­полнение. Их целью было разместить в оперативной памяти всю базу дан­ных. Некоторое время это помогало, пока база данных не превысила объем оперативной памяти всех доступных конфигураций.

Как определить цели оптимизации? Мы используем систему постановки целей SMART. Аббревиатура SMART означает:

О Specific - конкретность;

О Measurable - измеримость;

О Achievable или Attainable - достижимость;

О Result-based или Relevant - уместность;

О Time-bound - ограниченность во времени.

Большинство знают о целях SMART, которые применяются, когда речь идет о здоровье и фитнесе, но та же самая концепция прекрасно подходит и для оптимизации запросов. Примеры целей SMART представлены в табл. 1.1.

Таблица 1.1. Примеры целей SMART

оптимизация Процессов

Важно помнить, что база данных не существует в вакууме. Она является основой для нескольких, часто независимых приложений и систем. Все поль­зователи (внешние и внутренние) испытывают на себе именно общую про­изводительность системы, и это то, что имеет для них значение.

На уровне организации цель состоит в том, чтобы добиться лучшей произ­водительности всей системы. Это может быть время отклика или пропускная способность (что важно для поставщика услуг) либо (скорее всего) баланс того и другого. Никого не интересует оптимизация базы данных, которая не влияет на общую производительность.

Разработчики и администраторы баз данных часто склонны чрезмерно оптимизировать любой плохой запрос, который привлекает их внимание просто потому, что он плохой. При этом их работа нередко изолирована как от разработки приложений, так и от бизнес-аналитики. Это одна из причин, по которой усилия по оптимизации могут оказаться менее продуктивными, чем могли бы быть. SQL-запрос нельзя оптимизировать изолированно, вне контекста его назначения и окружения, в котором он выполняется.

Поскольку запросы можно писать не декларативно, первоначальная цель запроса может быть неочевидной. Выяснение того, что должно быть сде­лано с точки зрения бизнеса, - возможно, первый и самый важный шаг оптимизации. Более того, вопросы о цели отчета могут привести к выво­ду, что отчет вообще не нужен. Однажды вопросы о назначении наиболее длительных отчетов позволили нам сократить общий трафик на сервере отчетов на 40 %.

Оптимизация OLTP и OLAP

Есть много способов классификации баз данных, и разные классы баз данных могут отличаться как по критериям эффективности, так и по методам оп­тимизации. Два основных класса - это OLTP (оперативная обработка транз­акций) и OLAP (интерактивная аналитическая обработка). OLTP-системы поддерживают приложения, а OLAP-системы - бизнес-аналитику и отчет­ность. На протяжении этой книги мы будем подчеркивать разные подходы к оптимизации OLTP и OLAP. Мы познакомим вас с понятиями коротких и длинных запросов, а также объясним, как их различать.

Подсказка Это не зависит от длины инструкции SQL.

В большинстве случаев в OLTP-системах оптимизируются короткие запро­сы, а в OLAP-системах - и короткие, и длинные запросы.

Проектирование базы данных и производительность

Мы уже упоминали, что нам не нравится концепция «сначала пиши, а потом оптимизируй» и что цель данной книги - помочь вам сразу же писать пра­вильные запросы. Когда разработчику следует задуматься о производитель­ности запроса, над которым он работает? Чем раньше, тем лучше. В идеале оптимизация начинается с требований. На практике это не всегда так, хотя сбор требований очень важен.

Говоря точнее, сбор требований позволяет спроектировать наиболее под­ходящую структуру базы данных, а ее структура может влиять на произво­дительность.

Если вы администратор базы данных, то, скорее всего, время от времени вас будут просить проверить новые таблицы и представления, а это значит, что вам придется оценивать схему чужой базы данных. Если вы незнакомы с тем, что представляет собой новый проект, и не в курсе предназначения новых таблиц и представлений, вы вряд ли сможете определить, является ли предложенная структура оптимальной.

Единственное, что вы можете оценить, не вдаваясь в детали бизнес-требо- ваний, - нормализована ли база данных. Но даже это может быть неочевидно, если не знать специфики бизнеса.

Единственный способ оценить предлагаемую структуру базы данных - задать правильные вопросы. В том числе вопросы о том, какие реальные объекты представляют таблицы. Таким образом, оптимизация начинается со сбора требований. Чтобы проиллюстрировать это утверждение, рассмот­рим следующий пример: нам нужно хранить учетные записи пользователей, и необходимо хранить телефонные номера каждого владельца записи. На рис. 1.1 и 1.2 показаны два возможных варианта.

Рис. 1.1 ❖ Вариант с одной таблицей

Рис. 1.2 ❖ Вариант с двумя таблицами

Какой из двух вариантов правильный? Это зависит от того, как будут ис­пользоваться данные. Если номера телефонов никогда не участвуют в кри­териях поиска и выбираются как часть учетной записи (для отображения на экране службы поддержки клиентов) или если в пользовательском интер­фейсе есть поля, помеченные конкретными типами телефонов, то вариант с одной таблицей более уместен.

Но если мы собираемся искать по номеру телефона независимо от его типа, можно разместить все телефоны в отдельной таблице, и это сделает поиск более производительным.

Кроме того, пользователей часто просят указать, какой номер телефо­на является основным. В вариант с двумя таблицами легко добавить один логический атрибут is_primary, но в варианте с одной таблицей это не так просто. Дополнительные сложности могут возникнуть, если у пользователя нет стационарного или рабочего телефона, а такое случается часто. С другой стороны, у людей бывает несколько сотовых телефонов, или у них может быть виртуальный номер, например Google Voice, и может возникнуть желание записать этот номер в качестве основного, по которому с ними можно свя­заться. Все эти соображения говорят в пользу варианта с двумя таблицами.

Наконец, мы можем оценить частоту каждого варианта использования и критичность времени отклика в каждом случае.

Разработка приложений и производительность

Мы говорим о разработке приложений, а не только о разработке базы дан­ных, поскольку запросы к базе данных не выполняются сами по себе - они яв­ляются частью приложений. Традиционно именно оптимизация отдельных запросов рассматривается как просто «оптимизация», но мы будем смотреть на вещи шире.

Довольно часто, хотя каждый запрос к базе данных, выполняемый прило­жением, возвращает результат менее чем за десятую часть секунды, время отклика страницы приложения может составлять десятки секунд. С техниче­ской точки зрения оптимизация таких процессов - это не «оптимизация базы данных» в традиционном понимании, но разработчик базы данных может многое сделать, чтобы улучшить ситуацию. Мы рассмотрим соответствую­щие методы оптимизации в главах 10 и 13.

Другие этапы жизненного цикла

Жизненный цикл приложения не заканчивается после его выпуска в про­мышленное окружение, и оптимизация - это тоже непрерывный процесс. Хотя нашей целью должна быть долгосрочная оптимизация, трудно пред­сказать, как именно будет развиваться система. Полезно постоянно следить за производительностью системы, обращая внимание не только на время выполнения, но и на тенденции.

Запрос может быть очень производительным, и можно не заметить, что время выполнения начало увеличиваться, потому что оно по-прежнему на­ходится в допустимых пределах и никакие автоматические системы мони­торинга не выдают предупреждение.

Время выполнения запроса может измениться из-за увеличения объема данных, изменения распределения данных или увеличения частоты выпол­нения. Кроме того, в каждом новом выпуске PostgreSQL мы ожидаем увидеть новые индексы и другие улучшения, а некоторые из них могут оказаться настолько значительными, что подтолкнут нас к переписыванию исходных запросов.

Какой бы ни была причина изменения, нельзя считать, что какая-либо часть системы будет всегда оставаться оптимизированной.

особенности PostgreSQL

Хотя принципы, описанные в предыдущем разделе, применимы к любой ре­ляционной базе данных, PostgreSQL, как и любая другая база данных, имеет некоторые особенности, которые нужно учитывать. Если у вас уже есть опыт оптимизации других баз данных, может оказаться, что значительная часть ваших знаний неприменима. Не считайте это недостатком PostgreSQL; прос­то помните, что PostgreSQL многое делает иначе.

Возможно, самая важная особенность, о которой вам следует знать, - в PostgreSQL нет подсказок оптимизатору. Если вы ранее работали с такой базой данных, как Oracle, в которой есть возможность «подсказать» опти­мизатору, то вы можете почувствовать себя беспомощным, столкнувшись с проблемой оптимизации запроса PostgreSQL. Однако есть и хорошие но­вости: в PostgreSQL намеренно нет подсказок. Ключевая группа PostgreSQL верит в необходимость инвестировать в разработку планировщика запросов, способного выбирать самый подходящий путь выполнения без подсказок. В результате движок оптимизации PostgreSQL является одним из лучших среди как коммерческих систем, так и систем с открытым исходным ко­дом. Многие сильные разработчики баз данных перешли на Postgres из-за оптимизатора. Кроме того, исходный код Postgres был выбран в качестве основы для нескольких коммерческих баз данных отчасти из-за оптимиза­тора. В PostgreSQL еще более важно писать инструкции SQL декларативно, позволяя оптимизатору делать свою работу.

Еще одна особенность PostgreSQL, о которой следует знать, - это разница между выполнением параметризованных запросов и динамического SQL. Глава 12 посвящена использованию динамического SQL, которое часто упус­кают из виду.

В PostgreSQL очень важно знать о новых функциях и возможностях, ко­торые появляются с каждым выпуском. В последнее время ежегодно до­бавляется более 180 функций, многие из которых связаны с оптимизацией. Мы не планируем рассматривать их все; более того, за тот период времени, который пройдет с момента написания этой статьи до ее публикации, несо­мненно появится еще больше функций. PostgreSQL имеет невероятно бога­тый набор типов и индексов, и всегда стоит обращаться к последней версии документации, чтобы выяснить, была ли реализована нужная вам функция.

Подробнее об особенностях PostgreSQL мы поговорим позже.

выводы

Написание запроса к базе данных отличается от написания кода приложения с использованием императивного языка. SQL - декларативный язык, а это означает, что мы указываем желаемый результат, но не указываем путь вы­полнения. Поскольку два запроса, дающих одинаковый результат, могут вы­полняться по-разному, используя разные ресурсы и занимая разное время, оптимизация и концепция «мыслить как база данных» являются основными составляющими разработки SQL.

Вместо того чтобы оптимизировать уже написанные запросы, наша цель - правильно писать запросы с самого начала. В идеале оптимизация начи­нается во время сбора требований и проектирования базы данных. Затем можно приступить к оптимизации отдельных запросов и структурированию вызовов базы данных из приложения. Но оптимизация на этом не заканчи­вается; чтобы система оставалась работоспособной, необходимо отслеживать производительность на протяжении всего жизненного цикла системы.

Вас заинтересует / Intresting for you:

Устанавливать или обновлять Po... 1754 просмотров Максим Николенко Sat, 23 Jul 2022, 19:06:02

Установка PostgreSQL 10.5 из и... 2663 просмотров Tarcoola Ningae Sun, 19 Aug 2018, 11:45:55

Установка PostgreSQL из дистри... 4352 просмотров Tarcoola Ningae Sun, 19 Aug 2018, 12:02:39

Обзор СУБД PostgreSQL: в чем п... 3511 просмотров Ирина Светлова Mon, 09 Aug 2021, 04:48:03