Это статья об основах программирования на Go. На канале я рассказываю об опыте перехода в IT с нуля, структурирую информацию и делюсь мнением.
Хой, джедаи и амазонки!
Исследую оптимизацию запросов PostgeSQL. Почитал об этом несколько публикаций, получил некоторый опыт на практике. Поделюсь наблюдениями.
1. Общие сведения
В самом начале когда я познакомился с PostgreSQL я не писал запросы напрямую, а писал их в коде. Визуальный интерфейс с возможностью администрирования БД был у меня pgAdmin, который я запускал в докер-контейнере.
Далее я начал пользоваться плагином в IDE для работы с БД.
Сейчас, когда понадобилось исследовать sql-запросы, я установил консольную утилиту psql для администрирования PostgreSQL; хотя исследовать можно и в плагине IDE, но мне захотелось освоить новый инструмент. Подключение к БД через psql выглядит примерно так:
Далее нужно будет пароль ввести. И можно работать. Например в psql я открыл для себя простую и полезную команду, которая показывает информацию о таблице, индексах и ключах: \d table_name
Суть статей, которые я прочёл по оптимизации работы c БД в следующем:
- Хорошая архитектура БД;
- Хорошие настройки общения с БД (пулы соединений, память work mem и прочее);
- Грамотные SQL-запросы.
Вот с третьим пунктом и будем разбираться. Если вкратце по первому пункту можно выделить следующее:
- Проектировать таблицы с таким числом столбцов - большинство или все из которых будут считываться при каждом запросе к таблице в БД; т.е. другими словами, количество столбцов в таблице должно быть минимальным и достаточным;
- Для таблиц в которых будет много строк (тысячи-миллионы-миллиарды) настроить партицирование;
- Грамотно использовать индексы: т.е. определить важные столбцы и назначить индексы только им;
- Прочие оптимизации: умело назначать типы данных, разумная связность между таблицами, типы индексов и др.
Делаю вывод, что проектирование БД - это отдельная ветка навыков, а на архитектуру БД влияет в первую очередь понимание, как эта БД будет эксплуатироваться: какие элементы будут запрашиваться вместе, какие по-отдельности, что чаще будет запрашиваться, будут ли массовые вставки или изменения существующих строк и т.д. Архитектура БД - это про оптимизации, а так - можно создать одну таблицу в которую поместить всё.
Как пример, почему важна архитектура - мы живём в квартирах с определёнными планировками - архитекторы заложили её при строительстве дома. А без архитектуры была бы одна площадь какой-то формы: и гостинная, и кухня, и санузел, коридор и всё прочее в одном помещении без стен - кому такое понравится? А ещё нужно учесть, что квартир много, нужно сориентировать стороны дома по сторонам света, подвести инженерию, расположить подъездные пути и парковки, зону отдыха и многое другое.
Мы рассмотрим sql-запросы и способ их исследования.
2. Оптимизация SQL-запросов
2.1. Общие сведения
Сразу скажу, к чему я пришёл:
- В PostgreSQL есть планировщик, который сам решает в какой последовательности выполнить части запроса: мы на это практически никак не можем повлиять;
- Ключевой способ сократить нагрузку на БД, который я нашёл при запросе данных, - это запрашивать минимально необходимые данные, а не как часто это происходит - все данные из таблицы или нескольких таблиц.
- В PostgreSQL есть инструмент анализа запросов.
Вот три нехитрых результата анализа.
У PostgreSQL есть два планировщика: один отвечает за планирование запросов, другой - за планирование заданий.
Здесь речь о планировании запросов.
SQL - декларативный язык, т.е. разработчик сообщает что нужно сделать с БД, а не как. Вот в чём суть исполнения запросов PostgreSQL.
Я столкнулся с пониманием этого, когда смотрел на план выполнения запроса и понимал, что как-будто впереди идёт тяжеловесная операция, которую можно было бы облегчить, если вперёд выполнится другая часть SQL-запроса. И никаким изменением sql-запросов я не мог этого добиться.
Пример рассмотрю ниже в анализе запросов.
2.2. Запрос минимума данных
Можно предположить, что в сервисе есть набор универсальных функций, получающих данные о некой общей сущности.
Рассмотрим пример в песочнице https://sqlplayground.app/sandbox/
SQL-запрос выглядит так:
SELECT * FROM countries
WHERE code = 'RUS';
В реальном проекте астериска, т.е. знака *, скорее всего не будет, будет что-то вроде:
SELECT
code, name, continent, region, surface_area, indep_year, population, life_expectancy, gnp, gnp_old, local_name, government_form, head_of_state, capital
FROM countries
WHERE code = 'RUS';
Чтобы более явно сохранять данные.
Покрупнее покажу, как выглядит схема БД:
Возвращаемся к SQL-запросу с астериском после FROM. Что здесь плохо. Мы извлекаем все данные, хотя возможно фактически используем только 1-2. Сократим этот запрос, чтобы получать данные из двух столбцов, а не из 14:
Какая с этого выгода, даже если нам в действительности нужны только эти данные? Ну и пусть бы из БД возвращалась вся строка. Рассмотрим анализ запросов.
2.3. Анализируем запросы
2.3.1. Знакомство с анализом
Я нашёл два инструмента: нужно перед запросом добавить explain или explain analyze.
Посмотрим только с explain на сокращённый запрос:
Мы видим некоторую информацию - это порядок исполнения запроса. Здесь последовательность только из одного элемента: index scan означает что таблица была просканирована по индексу. Если индексы назначены с умом - это эффективный результат.
Проверим то же самое с анализом:
explain analyze
SELECT name, head_of_state
FROM countries
WHERE code = 'RUS';
Видим результат исполнения 0.021 мс. Время выполнения, кстати, не фиксированное и может варьироваться на одном и том же запросе:
Я брал время исполнения как основную характеристику нагрузки на БД и её оптимизацию.
2.3.2. Тестируем более тяжеловесный запрос
Посмотрим что получили когда выбирали все данные из таблицы:
Результат немного больше. Если запускать не в онлайн-песочнице, а на компьютере - результат может быть ещё более впечатляющий.
Это единственный действенный способ для оптимизации sql-запросов на действующей БД, который я обнаружил. Просто уменьшаем количество считываемых данных.
Т.е. если в проекте есть универсальные функции, которые считывают все данные, а далее используется только малая их часть - можно написать новую функцию, которая будет считывать только требуемые данные. Таким образом в проекте может появиться универсальная функция, считывающая все данные из одной или нескольких таблиц, и функция, которая считывает только требуемые данные. И нужно встроить в проекте эту новую функцию, чтобы ничего не перестало работать.
2.3.3. Тестируем запрос со склейкой таблиц
Особенно ярко экономия на минимизации запрошенных данных выражается, если изначально происходит ещё более тяжеловесная операция со склейкой таблиц:
Здесь мы собрали данные из двух таблиц.
Посмотрим на анализ этого запроса:
Результат - 0,072 мс.
В реальности если у нас есть такой общий запрос, который вызывается из множества разных функций; а для некоторой функции нам нужны намного меньше данных, как в примере выше - то мы можем сократить время обращения к этой БД в несколько раз за счёт того, что напишем новую функцию с меньшей выборкой данных.
2.3.4. Пример анализа поиска не по индексу
В предыдущем запросе со склейкой мы искали по индексам, а в плане запроса отображался index scan.
Допустим в некотором сценарии нам нужно обратиться к БД не по проиндексированному столбцу:
Результат уже 0,115 мс против 0,072 мс. Это при том, что строк ещё не очень-то много. Ну и ключевое что хотел здесь показать - мы в первом действии плана видим не index scan, а seq sqan, - это значит, что в план включено последовательное сканирование всех строк таблицы, т.е. простым перебором. Это медленно.
А второй этап выполнения запроса: уже используется поиск по индексу.
Ещё рекомендуют обращать внимание на диапазон значения time, например 0.063..0.074 - пишут, что если они "сильно" отличаются, то запрос не оптимальный. Что значит "сильно" - не разъясняется, думаю это с опытом приходит. В любом случае, даже если они сильно отличаются - непонятно, что делать: я пока выработал только один инструмент оптимизации sql-запросов и он не влияет на разбег в time.
2.3.4. Доработка запроса поиска не по индексу
Здесь я просто хочу показать, что планировщику не особенно интересно на синтаксис. Изменим предыдущий запрос: удалим блок WHERE и перенесём условие поиска в JOIN:
Результат по плану тот же самый, те же последовательности действий: сканирование перебором - > сканирование по индексу.
Итоговое время 0,103 мс не показатель по сравнению с предыдущим запросом - это естественная вариативность, как я писал выше.
Здесь важен факт того, что при более сложных запросах с большим количеством условий - не особо важно может быть, как мы их разместим в блоке WHERE, JOIN или ещё как-то - планировщик их оценит своими алгоритмами и решит, как их исполнить лучшим образом. Планировщик - наш друг. При условии, что синтаксис SQL не нарушен, конечно.
2.4. Анализ навороченных запросов
2.4.1. Исходный запрос
Теперь посмотрим что получим если использовать чуть больше условий со склейкой большего числа таблиц.
Сценарий: нужно найти информацию о странах Азии в которых говорят на русском языке. При этом нужные данные - название страны, её глава и столица государства.
Очевидно, здесь будет два джойна. Пример возможного запроса:
В результате видим, что по-русски говорят в следующих азиатских странах: Азербайджан, Узбекистан и Грузия.
Посмотрим на анализ запроса:
Видим, что первым этапом будет простой перебор строк по фильтру Asia из таблицы countries. Но мы знаем, что Asia находится не в индексируемом столбце. Как быть? Почему планировщик решил, что так оптимально построить запрос?
Для меня, например, не очевидно, что так быстрее - я считаю, что эффективнее начать с фильтрации по другой таблице - country_languagies, т.к. этот столбец часть первичного ключа, а первичный ключ - индексируется.
2.4.2. Дорабатываем запрос: порядок таблиц
Для начала можно изменить запрос, чтобы исходной таблицей была не countries, а более компактная country_languagies. Возможно это улучшит запрос.
Сперва убедимся, что запрос вообще работает:
Далее посмотрим на результаты анализа:
Изменений в плане нет. Последовательность ровно та же самая: сперва простым перебором с фильтром Asia, которая находится в неиндексируемом столбце.
Вот так работает планировщик. Запрос изменили, план остался тем же.
2.4.3. Дорабатываем запрос: условие из WHERE в JOIN
Проверим сработает ли перенос условия. Для этого возьмём первый запрос и сдвинем условие "AND country_languagies.language = 'Russian'" в JOIN:
Никаких изменений - планировщик всё равно делает по-своему.
Я здесь не нашёл ничего интереснее, кроме как использовать двухступенчатый запрос.
2.4.4. Common Table Expression
Common Table Expression или CTE - механизм создания временной таблицы. В новых версиях PostgreSQL появились настройки, позволяющие создавать эту временную таблицу или использовать её только как своего рода функцию для структурирования SQL-запроса, а ранее он всегда натурально создавал промежуточную временную таблицу.
Доработаем запрос и запустим с созданием временной таблицы:
Запрос:
explain analyze
WITH lang AS MATERIALIZED(
SELECT * FROM country_languagies
WHERE language = 'Russian'
)
SELECT countries.head_of_state, countries.local_name, cities.name
FROM countries
JOIN lang ON countries.code = lang.country_code
JOIN cities ON cities.id = countries.capital
WHERE countries.continent = 'Asia';
Запрос работает. Запустим его анализ:
Ага, теперь видим, что план изменился. Но что же видим ещё? Что фильтр по языку применился как простой перебор, а не как выборка по индексу. Да и последовательность увеличилась - не очень похоже на оптимизацию.
Идём смотреть схему БД, чтобы убедиться, что этот столбец действительно индексируется.
Я в начале решил, что для колонки язык применяется индекс по визуализации схемы БД, т.к. там стоит ключик, а первичный ключ - индексируется:
Ключик пере language. Но посмотрел схему создания БД и понял, что ключ там комбинированный:
Это означает, что индекс сработает только если искать по языку и коду страны. Теперь понятно, почему планировщик решил первой сканировать таблицу с фильтром по региону. Т.к. мы нашли в ней вторую часть индекса таблицы country_languagies и быстро прошлись по ней.
Главная мысль здесь в том, что мы можем как-то менять запросы, но планировщик сам решит в какой последовательности выполнить запрос. А влиять мы на это можем только косвенно, например через создание временной таблицы, и вряд ли это принесёт пользу.
3. Итоги
В публикации я поделился итогами анализа способов оптимизации SQL-запросов и собственным исследованием на тему можно повлиять на планировщик запросов PostgreSQL. Основные выводы:
- Если нужно оптимизировать SQL-запросы в действующей БД и действующем приложении, то основной упор нужно сделать на анализе кода: используются ли все полученные из БД данные, или нет. И если нет, то написать новую функцию обращения к БД с получением меньшего объёма данных.
- В PostgreSQL есть инструмент explain analyze, позволяющий смотреть план исполнения запроса и время его исполнения.
- Планировщик запросов PostgreSQL сам решает, как оптимальнее выполнить запрос. На это можно влиять через создание виртуальных таблиц, но скорее всего результат будет менее эффективным.
- Для разных запросов планировщик может составить одинаковый план.
- Если мы хотим провести анализ эффективности нескольких запросов, дающих один результат - то логично запустить анализ несколько раз и выбрать, скажем так, среднее арифметическое время исполнения запросов. Правда тут может быть проблема: если PostgreSQL кеширует ответы, такой подход может не дать ясной картины. Более ясную картину может дать исследование описательной части плана - используется ли перебор или индексы и т.д.
Ещё я познакомился с оптимизацией по чтению части данных из джейсона. Т.е. если в одном из столбцов хранится JSON-объект, то не обязательно читать содержимое всего столбца, или тем более, всех столбцов строки. Внутри PostgreSQL есть функционал, позволяющий получить значение по ключу.
Также хочу напомнить, что стажировку на которой сейчас работаю, нашёл благодаря необязательной акселерации по трудоустройству после курса Яндекс Практикум. Акселерация здорово может помочь найти первую работу. Ну и как бонус, у меня есть промокод на скидку в ЯП - можно спросить в ЛС в ТГ.
На этом у меня всё, благодарю, что дочитали публикацию до конца. Успехов, и будем на связи.
Бро, ты уже здесь? 👉 Подпишись на канал для начинающих IT-специалистов «Войти в IT» в Telegram, будем изучать IT вместе 👨💻👩💻👨💻