Введение
SQL остаётся языком номер один для работы с реляционными базами данных, но написание эффективных запросов — это искусство, требующее не только знания синтаксиса, но и понимания внутренней работы СУБД. По данным опроса Stack Overflow за 2023 год, SQL входит в топ-3 наиболее используемых языков среди профессиональных разработчиков, а навыки оптимизации запросов — один из самых востребованных навыков на рынке. В этой статье я собрал 15 промтов (подсказок и шаблонов), которые помогут вам писать чистые, быстрые и безопасные SQL-запросы, а также оптимизировать работу с базами данных. Каждый промт сопровождается реальным примером и пояснением.
Базовые промты для написания запросов
1. Выборка с условиями: используйте WHERE вместо HAVING для фильтрации строк
Промт: Всегда применяйте WHERE для фильтрации строк до агрегации, а HAVING — только для фильтрации после GROUP BY. Это снижает нагрузку на базу.
Пример:
-- Плохо: фильтруем после группировки
SELECT department, COUNT(*) as emp_count
FROM employees
GROUP BY department
HAVING department != 'Sales';
-- Хорошо: фильтруем до группировки
SELECT department, COUNT(*) as emp_count
FROM employees
WHERE department != 'Sales'
GROUP BY department;
Результат: Второй запрос обрабатывает меньше строк на этапе группировки, что ускоряет выполнение на больших таблицах.
2. Избегайте SELECT * — явно перечисляйте поля
Промт: Никогда не используйте SELECT * в продакшене. Это увеличивает объём передаваемых данных и может сломать код при изменении схемы.
Пример:
-- Плохо
SELECT * FROM orders WHERE status = 'shipped';
-- Хорошо
SELECT id, customer_id, total_amount, status FROM orders WHERE status = 'shipped';
Результат: Снижение нагрузки на сеть и базу данных, а также улучшение читаемости кода.
3. Используйте JOIN вместо подзапросов, когда это возможно
Промт: JOIN часто выполняется быстрее, чем коррелированные подзапросы, особенно в PostgreSQL и MySQL.
Пример:
-- Подзапрос (может быть медленным)
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);
-- JOIN (оптимизированнее)
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.total > 1000;
Результат: Оптимизатор запросов может использовать индексы более эффективно.
Продвинутые промты для оптимизации
4. Используйте EXPLAIN ANALYZE для профилирования
Промт: Перед оптимизацией всегда выполняйте EXPLAIN ANALYZE (или EXPLAIN в MySQL) для понимания плана выполнения запроса. Это покажет, где тратится время.
Пример:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending';
-- Результат покажет: Seq Scan vs Index Scan, время выполнения, количество строк
Результат: Вы увидите, что запрос использует последовательное сканирование (Seq Scan) вместо индекса, и сможете добавить индекс.
5. Создавайте составные индексы для покрытия запросов
Промт: Индексы должны соответствовать условиям WHERE и JOIN. Для запросов с сортировкой добавляйте поля в индекс в правильном порядке.
Пример:
-- Запрос: SELECT * FROM orders WHERE customer_id = 123 ORDER BY created_at DESC;
-- Создаём составной индекс:
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);
Результат: Индекс покрывает и фильтрацию, и сортировку, что ускоряет запрос в 10-100 раз на больших таблицах.
6. Используйте LIMIT и OFFSET с умом
Промт: Для пагинации избегайте больших OFFSET — база всё равно сканирует все строки до смещения. Используйте курсорную пагинацию на основе уникального поля.
Пример:
-- Плохо для больших OFFSET
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10000;
-- Хорошо: курсорная пагинация
SELECT * FROM products WHERE id > 10000 ORDER BY id LIMIT 10;
Результат: Второй запрос использует индекс по id и не сканирует лишние строки.
7. Избегайте функций в WHERE — они блокируют использование индексов
Промт: Обёртка столбца в функцию (например, LOWER(name)) делает индекс бесполезным. Используйте функциональные индексы или альтернативные конструкции.
Пример:
-- Плохо: функция на столбце
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- Хорошо: функциональный индекс (PostgreSQL)
CREATE INDEX idx_lower_email ON users (LOWER(email));
-- Или храните email в нижнем регистре
Результат: Индекс используется, запрос ускоряется.
8. Используйте WITH (CTE) для сложных запросов
Промт: Общие табличные выражения (Common Table Expressions) улучшают читаемость и могут быть оптимизированы СУБД.
Пример:
WITH top_customers AS (
SELECT customer_id, SUM(total) as total_spent
FROM orders
WHERE created_at > '2025-01-01'
GROUP BY customer_id
HAVING SUM(total) > 10000
)
SELECT c.name, tc.total_spent
FROM customers c
JOIN top_customers tc ON c.id = tc.customer_id
ORDER BY tc.total_spent DESC;
Результат: Запрос становится модульным и легче для отладки.
Экспертные промты для сложных сценариев
9. Используйте PARTITION BY для оконных функций вместо GROUP BY
Промт: Оконные функции (ROW_NUMBER(), RANK(), SUM() OVER) позволяют выполнять агрегации без потери детализации строк.
Пример:
-- Получить топ-3 продукта в каждой категории по продажам
SELECT category, product, sales,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rn
FROM product_sales
QUALIFY rn <= 3; -- PostgreSQL 15+ / Snowflake / BigQuery
Результат: Один запрос вместо нескольких подзапросов.
10. Используйте UNNEST для работы с массивами
Промт: В PostgreSQL и BigQuery UNNEST преобразует массив в строки, что упрощает анализ.
Пример:
-- Развернуть массив тегов в строки
SELECT id, unnest(tags) as tag
FROM articles
WHERE id = 123;
Результат: Удобно для денормализованных данных.
11. Используйте MERGE (UPSERT) для синхронизации данных
Промт: MERGE (или INSERT ... ON CONFLICT в PostgreSQL) позволяет вставлять или обновлять данные за один запрос, что снижает количество транзакций.
Пример:
-- PostgreSQL: UPSERT
INSERT INTO users (id, name, email)
VALUES (1, 'John', 'john@example.com')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email;
Результат: Снижение нагрузки на БД при синхронизации.
12. Используйте MATERIALIZED VIEW для тяжёлых отчётов
Промт: Материализованные представления хранят результат запроса физически, что ускоряет повторные запросы ценой свежести данных.
Пример:
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT date, product_id, SUM(amount) as total
FROM sales
GROUP BY date, product_id;
-- Обновление:
REFRESH MATERIALIZED VIEW daily_sales_summary;
Результат: Отчёты выполняются за миллисекунды вместо минут.
13. Используйте JSON/JSONB для гибких схем
Промт: В PostgreSQL используйте тип JSONB для хранения полуструктурированных данных и индексы GIN для быстрого поиска.
Пример:
CREATE TABLE events (
id SERIAL PRIMARY KEY,
payload JSONB
);
-- Индекс для поиска по ключу
CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);
-- Запрос
SELECT * FROM events WHERE payload @> '{"type": "click"}';
Результат: Быстрый поиск в JSON-данных.
14. Используйте LATERAL JOIN для вызова функций на каждую строку
Промт: LATERAL позволяет использовать столбцы из предыдущей таблицы в подзапросе, что полезно для расчётов на каждую строку.
Пример:
SELECT u.id, u.name, recent_order.total
FROM users u
LEFT JOIN LATERAL (
SELECT total FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1
) recent_order ON true;
Результат: Получаем последний заказ каждого пользователя без сложных подзапросов.
15. Используйте pg_stat_statements для мониторинга производительности
Промт: В PostgreSQL расширение pg_stat_statements собирает статистику по всем запросам: частоту выполнения, среднее время, количество прочитанных строк.
Пример:
-- Включение (требует прав суперпользователя)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Топ-10 самых медленных запросов
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
Результат: Выявление узких мест в реальном времени.
Заключение
Эти 15 промтов — не просто синтаксические трюки, а проверенные на практике паттерны, которые помогут вам писать SQL-запросы, работающие быстро даже на миллионах строк. Начните с малого: внедрите EXPLAIN ANALYZE в ежедневную практику, избавьтесь от SELECT * и добавляйте индексы только после профилирования. Помните: оптимизация без измерений — это гадание. Используйте встроенные инструменты мониторинга, такие как pg_stat_statements для PostgreSQL или performance_schema для MySQL, и вы увидите, как ваши запросы станут не только быстрее, но и надёжнее. Если вы работаете с данными из внешних сервисов, например, анализируете логи или транзакции, помните, что автоматизация сбора данных может быть интегрирована с вашими базами через API. ASI Biont поддерживает подключение к PostgreSQL и MySQL через API — подробнее на asibiont.com/courses. SQL — это язык, который не теряет актуальности, и его оптимизация — навык, который окупается многократно.
Комментарии