Три рабочих приёма в SQL: разгон поиска с 2,5 секунд до 80 мс, кардинальность индексов и поиск дублей через LAGТематическая сводка · дайджест за 21 ноября
Кинематографичный натюрморт с механическим хронометром и расходящимися индексированными карточками
Тематическая сводка3 раздела≈ 5 мин

Три рабочих приёма в SQL: разгон поиска с 2,5 секунд до 80 мс, кардинальность индексов и поиск дублей через LAG

Когда запрос ползёт по две-три секунды или в отчёте всплывают дубли, тянет выбрать один из двух привычных путей: докупить сервер помощнее или выгрузить строки в бэкенд и разгрести циклом. Оба варианта спасают лишь до следующего роста таблицы. Собрали три показательных кейса из SQL Portal, где проблему чинят прямо в запросе — пониманием индексов, кардинальности и окон строк.

Ускоряем поиск товаров с 2,5 секунд до 80 мс без нового железа

Авторы начинают с точной формулы: большинство «проблем с производительностью БД» — это плохо написанный SQL под маской проблем с базой. Каталог products на мощном сервере отвечает по 2,5 секунды:

-- Исходный запрос (~2,5 с): SELECT *, LOWER() и OFFSET
SELECT *
FROM products p
WHERE p.category_id = $1
  AND p.active = TRUE
  AND p.price_cents BETWEEN $2 AND $3
  AND LOWER(p.name) LIKE LOWER('%' || $4 || '%')
ORDER BY p.created_at DESC
OFFSET $5 LIMIT $6;

Тут сошлись четыре ловушки. SELECT * тащит тяжёлое description. LOWER(p.name) LIKE '%...%' с ведущим процентом отключает обычный B-tree индекс. Сортировка по created_at идёт после фильтрации, а OFFSET заставляет сервер на каждой странице перечитывать и отбрасывать предыдущие строки.

Чинится всё без апгрейда. Сначала собирают покрывающий индекс по правилу «равенство → диапазон → сортировка» с INCLUDE (name) — в PostgreSQL (с 11-й версии) это даёт Index-Only Scan без похода в таблицу, пока VACUUM обновляет карту видимости. Затем подключают pg_trgm и вешают триграммный GIN-индекс на name под ILIKE (для 1–2 символов лучше поставить UX-ограничение). Наконец, меняют OFFSET на keyset-пагинацию (seek pagination) по координатам последней строки:

-- Покрывающий индекс (CONCURRENTLY не блокирует запись)
CREATE INDEX CONCURRENTLY idx_products_feed
  ON products (category_id, active, price_cents,
               created_at DESC, product_id DESC)
  INCLUDE (name);

-- Триграммный GIN-индекс для поиска подстроки
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm
  ON products USING gin (name gin_trgm_ops);

-- Быстрый запрос с keyset-пагинацией (~80 мс)
SELECT p.product_id, p.name, p.price_cents, p.created_at
FROM products p
WHERE p.category_id = $1
  AND p.active = TRUE
  AND p.price_cents BETWEEN $2 AND $3
  AND p.name ILIKE '%' || $4 || '%'
  AND (p.created_at, p.product_id) < ($6, $7)
ORDER BY p.created_at DESC, p.product_id DESC
LIMIT $5;

Ответ падает с 2,5 секунды до 80 мс — быстрее в 30+ раз. Проверить план помогает EXPLAIN (ANALYZE, BUFFERS). Мощное железо лишь маскирует плохую архитектуру до следующего скачка нагрузки.

Кардинальность: почему индекс по полу пользователя работает вхолостую

На собеседованиях любят задачку с подвохом: «Что не так с этим скриптом?» Автор честно признаётся: сначала искал опечатку в синтаксисе, а дело оказалось в данных.

-- Синтаксис чистый, но один индекс работает вхолостую
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(50),
    Gender CHAR(1)
);
CREATE INDEX idx_name ON Employees(Name);
CREATE INDEX idx_gender ON Employees(Gender);

Всё решает кардинальность (cardinality) — доля уникальных значений в столбце. На таблице Employees из миллиона строк разница налицо:

СтолбецУникальных значенийКардинальностьПольза от индекса
ID1 000 000ВысокаяМаксимальная
NameСотни тысячВысокаяПолезен
Department10–20СредняяОграниченная
Gender2–3Очень низкаяПочти нулевая

Каждому значению Gender отвечает треть или половина таблицы, так что индекс не сужает поиск. Планировщик его игнорирует и запускает Full Table Scan — полное чтение таблицы. Но при каждом INSERT, UPDATE и DELETE база тратит диск и CPU на обновление idx_gender.

Низкокардинальные поля индексируют иначе: через составной индекс в паре с уникальным полем или частичный индекс под редкую долю строк (например, 5% открытых заказов):

-- Работает для Department и Department + ID,
-- но НЕ для поиска только по ID (левый префикс B-tree)
CREATE INDEX idx_dept_emp ON Employees (Department, ID);

-- Частичный индекс в PostgreSQL для редкого статуса
CREATE INDEX idx_orders_open ON orders (created_at)
  WHERE active = TRUE;

Перед любым CREATE INDEX проверяй распределение значений: слабый индекс лишь тормозит запись.

Ищем повторные покупки за 5 минут через оконную функцию LAG

Классика платёжных систем: таблица CustomerPurchases (PurchaseId, CustomerId, StoreId, ProductId, PurchaseTimestamp). Нужно поймать случаи, когда один клиент купил тот же товар в том же магазине повторно за пять минут — признак дубля или двойного списания.

Тяжёлый self-JOIN здесь не нужен. Задачу за один проход закрывает CTE (WITH ... AS, именованный подзапрос) и оконная функция LAG — она подсматривает время прошлой покупки внутри группы (PARTITION BY):

-- PostgreSQL: вычитаем даты и сравниваем с INTERVAL
WITH PurchasesWithLag AS (
  SELECT
    PurchaseId, CustomerId, StoreId, ProductId,
    PurchaseTimestamp,
    LAG(PurchaseTimestamp) OVER (
      PARTITION BY CustomerId, StoreId, ProductId
      ORDER BY PurchaseTimestamp
    ) AS PreviousPurchaseTimestamp
  FROM CustomerPurchases
)
SELECT
  PurchaseId, CustomerId, StoreId, ProductId,
  PreviousPurchaseTimestamp, PurchaseTimestamp
FROM PurchasesWithLag
WHERE PreviousPurchaseTimestamp IS NOT NULL
  AND PurchaseTimestamp - PreviousPurchaseTimestamp
      <= INTERVAL '5 minutes'
ORDER BY CustomerId, PurchaseTimestamp;

Важны два нюанса. На первой покупке в группе предыдущей строки нет, и LAG вернёт NULL — без IS NOT NULL отбор сломается. И учитывай диалект: в исходном примере под SQL Server стоит DATEDIFF (DATEDIFF(MINUTE, prev, curr) <= 5), но он считает пересечения границ минут (12:00:59 и 12:01:01 дадут 1), так что для точных 300 секунд в T-SQL надёжнее DATEDIFF(SECOND, ...) <= 300, а в PostgreSQL — INTERVAL '5 minutes'.

Хочу подробный разборГолоса помогают выбрать, что разобрать подробно. За этот материал 0 голосов.
Источники · 6