PostgreSQL против MySQL против SQLite: сравнение производительности SQL-запросов в разных системах.

Ознакомьтесь с практическим тестированием трех популярных баз данных SQL на основе реальных аналитических задач.

Изображение предоставлено автором.

# Введение

При проектировании приложения выбор правильного механизма базы данных SQL может существенно повлиять на производительность.

Три наиболее распространенных варианта — это PostgreSQL, MySQL и SQLite. Каждый из этих движков обладает уникальными преимуществами и стратегиями оптимизации, что делает его подходящим для различных сценариев.

PostgreSQL, как правило, отлично справляется со сложными аналитическими запросами, а MySQL также обеспечивает высокую производительность в задачах общего назначения. С другой стороны, SQLite предлагает легковесное решение для встроенных приложений.

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

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

# Понимание трех SQL-движков

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

PostgreSQL — это многофункциональная реляционная база данных с открытым исходным кодом, известная своей расширенной поддержкой SQL и сложной оптимизацией запросов. Она эффективно обрабатывает сложные аналитические запросы, имеет мощную поддержку оконных функций, CTE и различных стратегий индексирования.

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

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

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

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

Мы проверим их производительность по времени выполнения. Postgres и MySQL тестировались на платформе StrataScratch (серверная версия), а SQLite — локально в оперативной памяти.

# Решение задач среднего уровня сложности

// Ответ на вопрос №1 на собеседовании: Рискованные проекты

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

Таблицы данных: Вам предоставлены три таблицы: linkedin_projects (с бюджетами и датами), linkedin_emp_projects и linkedin_employees.

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

В PostgreSQL решение выглядит следующим образом:

SELECT a.title, a.budget, CEILING((a.end_date — a.start_date) * SUM(c.salary) / 365) AS prorated_employee_expense FROM linkedin_projects a INNER JOIN linkedin_emp_projects b ON a.id = b.project_id INNER JOIN linkedin_employees c ON b.emp_id = c.id GROUP BY a.title, a.budget, a.end_date, a.start_date HAVING CEILING((a.end_date — a.start_date) * SUM(c.salary) / 365) > a.budget ORDER BY a.title ASC;

PostgreSQL элегантно обрабатывает арифметические операции с датами с помощью прямого вычитания (( text{конечная_дата} — text{начальная_дата} )), которое возвращает количество дней между датами.

Вычисления просты и легко читаются благодаря встроенной в систему обработке дат.

В MySQL решение следующее:

SELECT a.title, a.budget, CEILING(DATEDIFF(a.end_date, a.start_date) * SUM(c.salary) / 365) AS prorated_employee_expense FROM linkedin_projects a INNER JOIN linkedin_emp_projects b ON a.id = b.project_id INNER JOIN linkedin_employees c ON b.emp_id = c.id GROUP BY a.title, a.budget, a.end_date, a.start_date HAVING CEILING(DATEDIFF(a.end_date, a.start_date) * SUM(c.salary) / 365) > a.budget ORDER BY a.title ASC;

В MySQL для арифметических операций с датами необходима функция DATEDIFF(), которая явно вычисляет, сколько дней находится между двумя датами.

Хотя это и добавляет вызов функции, оптимизатор запросов MySQL эффективно справляется с этим.

Наконец, давайте рассмотрим решение на основе SQLite:

SELECT a.title, a.budget, CAST( (julianday(a.end_date) — julianday(a.start_date)) * (SUM(c.salary) / 365) + 0.99 AS INTEGER) AS prorated_employee_expense FROM linkedin_projects a INNER JOIN linkedin_emp_projects b ON a.id = b.project_id INNER JOIN linkedin_employees c ON b.emp_id = c.id GROUP BY a.title, a.budget, a.end_date, a.start_date HAVING CAST( (julianday(a.end_date) — julianday(a.start_date)) * (SUM(c.salary) / 365) + 0.99 AS INTEGER) > a.budget ORDER BY a.title ASC;

В SQLite используется функция julianday() для преобразования дат в числовые значения для выполнения арифметических операций.

Поскольку в SQLite отсутствует функция CEILING(), мы можем имитировать её, добавив 0,99 и преобразовав в целое число, что обеспечит точное округление.

// Оптимизация запросов

Для каждого из трех движков индексы могут использоваться по столбцам объединения (project_id, emp_id, id), что значительно повышает производительность. Преимущества PostgreSQL обусловлены использованием составных индексов по столбцам (title, budget, end_date, start_date) в предложении GROUP BY.

Правильное использование первичного ключа имеет важное значение, поскольку механизм InnoDB в MySQL автоматически кластеризует данные по первичному ключу.

// Ответ на вопрос №2 из интервью: Поиск покупок пользователей

Цель этого вопроса в ходе интервью — получить идентификаторы постоянных клиентов, совершивших вторую покупку в течение 1–7 дней после первой покупки (за исключением повторных покупок в тот же день).

Таблицы данных: Единственная таблица — amazon_transactions. Она содержит записи транзакций с идентификатором (id), идентификатором пользователя (user_id), товаром (item), датой создания (created_at) и доходом (nuance).

Решение на основе PostgreSQL:

WITH daily AS ( SELECT DISTINCT user_id, created_at::date AS purchase_date FROM amazon_transactions ), ranked AS ( SELECT user_id, purchase_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date) AS rn FROM daily ), first_two AS ( SELECT user_id, MAX(CASE WHEN rn = 1 THEN purchase_date END) AS first_date, MAX(CASE WHEN rn = 2 THEN purchase_date END) AS second_date FROM ranked WHERE rn <= 2 GROUP BY user_id ) SELECT user_id FROM first_two WHERE second_date IS NOT NULL AND (second_date - first_date) BETWEEN 1 AND 7 ORDER BY user_id;

В PostgreSQL решение заключается в использовании CTE (Common Table Expressions) для разбиения задачи на логичные и читаемые этапы.

Функция преобразования даты в дату преобразует временные метки, а оконные функции с помощью ROW_NUMBER() ранжируют покупки в хронологическом порядке. Встроенная в PostgreSQL функция вычитания дат обеспечивает аккуратность и эффективность итогового фильтра.

Решение на основе MySQL:

WITH daily AS ( SELECT DISTINCT user_id, DATE(created_at) AS purchase_date FROM amazon_transactions ), ranked AS ( SELECT user_id, purchase_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date) AS rn FROM daily ), first_two AS ( SELECT user_id, MAX(CASE WHEN rn = 1 THEN purchase_date END) AS first_date, MAX(CASE WHEN rn = 2 THEN purchase_date END) AS second_date FROM ranked WHERE rn <= 2 GROUP BY user_id ) SELECT user_id FROM first_two WHERE second_date IS NOT NULL AND DATEDIFF(second_date, first_date) BETWEEN 1 AND 7 ORDER BY user_id;

Решение MySQL аналогично предыдущей структуре PostgreSQL, используя CTE и оконные функции.

Основное отличие здесь заключается в использовании функций DATE() и DATEDIFF() для извлечения и сравнения дат. MySQL 8.0+ эффективно поддерживает CTE, тогда как более ранние версии требуют использования подзапросов.

Решение на основе SQLite:

WITH daily AS ( SELECT DISTINCT user_id, DATE(created_at) AS purchase_date FROM amazon_transactions ), ranked AS ( SELECT user_id, purchase_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date) AS rn FROM daily ), first_two AS ( SELECT user_id, MAX(CASE WHEN rn = 1 THEN purchase_date END) AS first_date, MAX(CASE WHEN rn = 2 THEN purchase_date END) AS second_date FROM ranked WHERE rn <= 2 GROUP BY user_id ) SELECT user_id FROM first_two WHERE second_date IS NOT NULL AND (julianday(second_date) - julianday(first_date)) BETWEEN 1 AND 7 ORDER BY user_id;

SQLite (версия 3.25+) также поддерживает CTE и оконные функции, что делает структуру идентичной двум предыдущим. В этом случае единственное отличие заключается в арифметике дат, которая использует функцию julianday() вместо встроенной функции вычитания или DATEDIFF().

// Оптимизация запросов

В этом случае индексы также можно использовать для эффективного разделения данных в оконных функциях, в частности, по user_id. PostgreSQL может извлечь выгоду из частичных индексов по активным пользователям.
При работе с большими наборами данных можно также рассмотреть возможность материализации ежедневных CTE в PostgreSQL. Для оптимальной производительности CTE в MySQL убедитесь, что вы используете версию 8.0 и выше.

# Решение сложных задач

// Ответ на вопрос №3 на собеседовании: Динамика выручки с течением времени

В этом вопросе на собеседовании вас просят рассчитать скользящее среднее за 3 месяца по общей выручке от покупок.

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

Таблицы данных:
amazon_purchases: Содержит записи о покупках с указанием user_id, created_at и purchase_amt.

Для начала давайте рассмотрим решение на базе PostgreSQL:

SELECT t.month, AVG(t.monthly_revenue) OVER( ORDER BY t.month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avg_revenue FROM ( SELECT to_char(created_at::date, 'YYYY-MM') AS month, sum(purchase_amt) AS monthly_revenue FROM amazon_purchases WHERE purchase_amt > 0 GROUP BY to_char(created_at::date, 'YYYY-MM') ORDER BY to_char(created_at::date, 'YYYY-MM') ) t ORDER BY t.month ASC;

PostgreSQL превосходит конкурентов по производительности при использовании оконных функций, поскольку спецификация кадра ROWS BETWEEN 2 PRECEDING AND CURRENT ROW точно определяет скользящее окно.
Функция to_char() форматирует даты в строковый формат «год-месяц» для группировки.

Далее, решение на основе MySQL:

SELECT t.`month`, AVG(t.monthly_revenue) OVER( ORDER BY t.`month` ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avg_revenue FROM ( SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, sum(purchase_amt) AS monthly_revenue FROM amazon_purchases WHERE purchase_amt > 0 GROUP BY DATE_FORMAT(created_at, '%Y-%m') ORDER BY DATE_FORMAT(created_at, '%Y-%m') ) t ORDER BY t.`month` ASC;

В реализации MySQL оконная функция обрабатывается идентично, хотя вместо функции to_char() используется функция DATE_FORMAT().

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

В итоге, решение на основе SQLite выглядит следующим образом:

SELECT t.month, AVG(t.monthly_revenue) OVER( ORDER BY t.month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avg_revenue FROM ( SELECT strftime('%Y-%m', created_at) AS month, SUM(purchase_amt) AS monthly_revenue FROM amazon_purchases WHERE purchase_amt > 0 GROUP BY strftime('%Y-%m', created_at) ORDER BY strftime('%Y-%m', created_at) ) t ORDER BY t.month ASC;

Для форматирования дат в SQLite требуется использование функции strftime(), и этот движок поддерживает тот же синтаксис оконных функций, что и PostgreSQL и MySQL (в версии 3.25 и выше). Производительность сопоставима для небольших и средних наборов данных.

// Оптимизация запросов

Использование оконных функций может быть вычислительно затратным.

Для PostgreSQL рекомендуется создать индекс по полю created_at и, если этот запрос выполняется часто, материализованное представление для агрегирования по месяцам.

MySQL выигрывает от наличия индексов, охватывающих как created_at, так и purchase_amt.

Для работы с SQLite необходимо использовать версию 3.25 или более позднюю, чтобы обеспечить поддержку оконных функций.

// Ответ на вопрос №4 из интервью: Друг общих друзей

Переходим к следующему вопросу на собеседовании: на этот раз вас просят подсчитать количество друзей каждого пользователя, которые также дружат с другими друзьями этого пользователя (по сути, общие связи в сети). Цель — вывести идентификаторы пользователей и количество таких общих связей «друг друга».

Таблицы данных:
google_friends_network: Содержит информацию о дружеских отношениях с user_id и friend_id.

Решение на основе PostgreSQL выглядит следующим образом:

WITH bidirectional_relationship AS ( SELECT user_id, friend_id FROM google_friends_network UNION SELECT friend_id AS user_id, user_id AS friend_id FROM google_friends_network ) SELECT user_id, COUNT(DISTINCT friend_id) AS n_friends FROM ( SELECT DISTINCT a.user_id, c.user_id AS friend_id FROM bidirectional_relationship a INNER JOIN bidirectional_relationship b ON a.friend_id = b.user_id INNER JOIN bidirectional_relationship c ON b.friend_id = c.user_id AND c.friend_id = a.user_id ) base GROUP BY user_id;

В PostgreSQL этот сложный запрос с множественным соединением эффективно обрабатывается благодаря усовершенствованному планировщику запросов.

Первоначальная CTE создает двустороннее представление связей внутри сети, за которым следуют три самосоединения, определяющие треугольные отношения, в которых ( A ) дружит с ( B ), ( B ) дружит с ( C ), и ( C ) также дружит с ( A ).

Решение на основе MySQL:

SELECT user_id, COUNT(DISTINCT friend_id) AS n_friends FROM ( SELECT DISTINCT a.user_id, c.user_id AS friend_id FROM ( SELECT user_id, friend_id FROM google_friends_network UNION SELECT friend_id AS user_id, user_id AS friend_id FROM google_friends_network ) AS a INNER JOIN ( SELECT user_id, friend_id FROM google_friends_network UNION SELECT friend_id AS user_id, user_id AS friend_id FROM google_friends_network ) AS b ON a.friend_id = b.user_id INNER JOIN ( SELECT user_id, friend_id FROM google_friends_network UNION SELECT friend_id AS user_id, user_id AS friend_id FROM google_friends_network ) AS c ON b.friend_id = c.user_id AND c.friend_id = a.user_id ) base GROUP BY user_id;

Решение MySQL повторяет подзапрос UNION три раза вместо использования одного CTE.

Хотя это менее элегантно, такой подход необходим для версий MySQL до 8.0. В современных версиях MySQL можно использовать подход PostgreSQL с CTE для лучшей читаемости и потенциального повышения производительности.

Решение на основе SQLite:

WITH bidirectional_relationship AS ( SELECT user_id, friend_id FROM google_friends_network UNION SELECT friend_id AS user_id, user_id AS friend_id FROM google_friends_network ) SELECT user_id, COUNT(DISTINCT friend_id) AS n_friends FROM ( SELECT DISTINCT a.user_id, c.user_id AS friend_id FROM bidirectional_relationship a INNER JOIN bidirectional_relationship b ON a.friend_id = b.user_id INNER JOIN bidirectional_relationship c ON b.friend_id = c.user_id AND c.friend_id = a.user_id ) base GROUP BY user_id;

SQLite поддерживает CTE и обрабатывает этот запрос идентично PostgreSQL.

Однако производительность может снижаться при обработке больших сетей из-за более простого оптимизатора запросов SQLite и отсутствия сложных стратегий индексирования.

// Оптимизация запросов

Для всех движков можно создавать составные индексы по (user_id, friend_id) для повышения производительности. В PostgreSQL для больших наборов данных можно использовать хэш-соединения при соответствующей настройке параметра work_mem.

Для MySQL убедитесь, что размер буферного пула InnoDB достаточен. SQLite может испытывать трудности с очень большими сетями. В этом случае рассмотрите возможность денормализации или предварительного вычисления связей для использования в производственной среде.

# Сравнение производительности

Примечание: Как упоминалось ранее, производительность PostgreSQL и MySQL тестировалась на платформе StrataScratch (серверная версия), а производительность SQLite — локально в оперативной памяти.

Значительно более высокая скорость работы SQLite объясняется его бессерверной архитектурой с нулевыми накладными расходами (а не превосходной оптимизацией запросов).

При сравнении производительности серверов MySQL превосходит PostgreSQL в более простых запросах (#1, #2), в то время как PostgreSQL быстрее справляется со сложными аналитическими задачами (#3, #4).

# Анализ ключевых различий в показателях эффективности

В ходе этих сравнительных тестов выявилось несколько закономерностей:

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

Однако это преимущество в скорости наиболее заметно при небольших объемах данных.

PostgreSQL демонстрирует превосходную производительность по сравнению с MySQL при выполнении сложных аналитических запросов, особенно тех, которые включают оконные функции и несколько CTE (вопросы № 3 и № 4). Его сложный планировщик запросов и обширные возможности индексирования делают его оптимальным выбором для рабочих нагрузок в области хранилищ данных и аналитики, где сложность запросов важнее их простоты.

MySQL превосходит PostgreSQL в более простых запросах средней сложности (№1 и №2), предлагая конкурентоспособную производительность при простых синтаксических требованиях, таких как DATEDIFF(). Его сильная сторона заключается в высококонкурентных транзакционных нагрузках, хотя современные версии также хорошо справляются с аналитическими запросами.

Вкратце, SQLite отлично подходит для легковесных, встроенных систем с небольшими и средними наборами данных, PostgreSQL — лучший выбор для сложной аналитики в масштабе, а MySQL обеспечивает хороший баланс между производительностью и универсальной надежностью.

# Заключительные замечания

Из этой статьи вы поймете некоторые нюансы различий между PostgreSQL, MySQL и SQLite, что позволит вам выбрать подходящий инструмент для ваших конкретных потребностей.

В очередной раз мы убедились, что MySQL обеспечивает баланс между высокой производительностью и универсальной надежностью, в то время как PostgreSQL превосходит конкурентов по сложности анализа данных благодаря сложным функциям SQL. В то же время SQLite предлагает простоту и легкость для встроенных систем.

Понимание того, как каждый движок выполняет конкретные SQL-операции, позволит добиться лучшей производительности, чем просто выбрав «лучший» из них. Используйте специфические для каждого движка функции, такие как покрывающие индексы MySQL или частичные индексы PostgreSQL, индексируйте столбцы объединений и фильтров, и всегда используйте предложения EXPLAIN или EXPLAIN ANALYZE для понимания планов выполнения запросов.

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

Нейт Розиди — специалист по анализу данных и продуктовой стратегии. Он также является адъюнкт-профессором, преподающим аналитику, и основателем StrataScratch, платформы, помогающей специалистам по анализу данных готовиться к собеседованиям с помощью реальных вопросов от ведущих компаний. Нейт пишет о последних тенденциях на рынке труда, дает советы по прохождению собеседований, делится проектами по анализу данных и освещает все аспекты SQL.

Источник: www.kdnuggets.com