Ознакомьтесь с практическим тестированием трех популярных баз данных 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
