Как Оптимизировать SQL-запросы. Часть 1
Медленный SQL-запрос — один из самых простых способов испортить быстрое приложение. У вас может быть чистая архитектура, отличный кэш и мощный сервер — и всё равно страница может зависать из-за того, что один запрос сканирует миллион строк без индекса. Большинство успехов достигается за счёт одного и того же небольшого набора методов, применяемых снова и снова. Некоторые из них очевидны. Некоторые противоречат советам, которые вы, вероятно, уже слышали. Советы можно условно разделить на 6 групп.
Замечание: здесь мы рассматриваем PostgreSQL. Те же принципы применимы и к другим БД, хотя точный синтаксис может отличаться.
Группа I. Написание запросов, удобных для индексации
Индекс полезен только в том случае, если ваш запрос позволяет БД его использовать.
1. Разумно используйте индексы
Индексы — самый мощный метод повышения производительности чтения. Это отсортированная структура данных, которая позволяет БД находить строки, не сканируя всю таблицу, подобно тому, как оглавление книги избавляет вас от необходимости пролистывать все страницы.
Создавайте индексы по столбцам, по которым вы чаще всего выполняете фильтрацию, соединение, сортировку и группировку — столбцам в
WHERE, JOIN, ORDER BY и GROUP BY. Когда используется несколько столбцов одновременно, один составной индекс, охватывающий их, намного лучше, чем отдельные индексы по одному столбцу:-- Составной индекс для частой фильтрации по статусу и дате
CREATE INDEX idx_orders_status_order_date
ON orders (status, order_date);
Этот индекс ускоряет запросы, фильтрующие по статусу, а также по статусу и дате заказа. Порядок столбцов имеет значение: индекс по
(status, order_date) помогает запросам, которые сначала фильтруют по статусу, но не запросам, которые фильтруют только по дате заказа.Вы также можете создать покрывающий индекс, который хранит дополнительные значения столбцов внутри индекса. Это полезно для небольших частых запросов на поиск, когда запросу нужны только столбцы, доступные в индексе, чтобы БД могла избежать чтения фактических строк таблицы:
-- Добавляем часто читаемые данные
CREATE INDEX idx_orders_status_order_date_covering
ON orders (status, order_date)
INCLUDE (customer_id, total_amount);
SELECT customer_id, total_amount
FROM orders
WHERE status = 'paid'
AND order_date >= DATE '2026-01-01';
Замечание: индексы не бесплатны. Каждый индекс необходимо обновлять при каждой вставке, обновлении и удалении, и это занимает место на диске. Индексируйте столбцы, которые фактически используются вашими запросами, а не каждый столбец.
2. Избегайте функций в WHERE
Обёртывание столбца в функцию — один из наиболее распространённых способов случайно отключить индекс. Когда вы вызываете функцию для столбца, БД должна вычислить значение функции для каждой строки, прежде чем сможет сравнить его, поэтому она не может использовать индекс для исходного столбца:
-- Плохо: функция по order_date отключает сканирование по индексу
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2025;
Перепишите условие так, чтобы оно сравнивало исходный столбец с диапазоном:
-- Хорошо: диапазон по чистому значению столбца
SELECT * FROM orders
WHERE order_date >= '2025-01-01'
AND order_date < '2026-01-01';
Оба запроса возвращают одни и те же строки, но только второй может использовать индекс по
order_date.То же правило применяется к
LOWER(email), CAST(…) и арифметическим операциям по столбцу. Если вам часто нужно фильтровать по вычисляемому значению, создайте вместо этого функциональный индекс для этого конкретного выражения.3. Избегайте символов подстановки в начале запроса LIKE
Шаблон LIKE, начинающийся с символа подстановки, не может использовать обычный индекс. БД считывает индекс слева направо, поэтому ей необходимо знать начало значения. Шаблон типа
'%son' скрывает начало и заставляет выполнять полное сканирование:-- Плохо: полное сканирование таблицы
SELECT * FROM customers
WHERE last_name LIKE '%son';
-- Хорошо: сканирование индекса по известному префиксу
SELECT * FROM customers
WHERE last_name LIKE 'Anders%';
Если вам действительно нужно выполнить contains-поиск в тексте, используйте полнотекстовый поиск или триграммный индекс (расширение pg_trgm в PostgreSQL), созданный специально для этой задачи.
4. Точное соответствие типов данных
Сравнение двух разных типов данных заставляет БД преобразовывать один из них, и это преобразование может незаметно отключить индекс.
Если столбец является целым числом, но вы сравниваете его со строкой, или соединяете int-ключ с bigint-ключом, БД добавляет неявное приведение типов — и индекс по исходному столбцу может быть пропущен. Сохраняйте одинаковые типы с обеих сторон каждого соединения и фильтрации:
CREATE TABLE logs (
log_id int PRIMARY KEY,
event_date timestamptz NOT NULL,
user_id int NOT NULL
);
Определите
logs.user_id так, чтобы он соответствовал типу users.id, и тогда соединение будет использовать индекс с обеих сторон. Продолжение следует…
https://antondevtips.com/blog/how-to-optimize-sql-queries-20-proven-best-practices
