0Pricing
SQL Interview Prep · Урок

Фильтрация по вычисляемым значениям

Узнайте, почему функции над столбцами отключают использование индексов и как это проверяют на собеседованиях.

«Фильтрация по вычисляемым значениям» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.

Почему этот вопрос показывает уровень подготовки

Формулировка звучит безобидно: этот запрос корректен, но медленный — почему? Часто причина в том, что предложение WHERE оборачивает индексированный столбец в функцию. Из-за этого условие становится непригодным для поиска по индексу: оптимизатор больше не может использовать индекс и вынужден просканировать каждую строку.

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

Одно определение пригодности для поиска по индексу

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

  • Пригодное для поиска по индексу: col = 5, col > 100, col LIKE 'abc%'
  • Непригодное для поиска по индексу: FUNC(col) = 5, col + 1 > 100

Антипаттерн «функция над столбцом»

Здесь нужно выбрать заказы, размещённые в 2024 году. Оборачивание столбца в YEAR() заставляет движок вычислить год для каждой строки без исключения, прежде чем выполнить сравнение, поэтому индекс по order_date оказывается бесполезным.

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

-- non-sargable: function on the indexed column
SELECT *
FROM orders
WHERE YEAR(order_date) = 2024;

Переписываем условие как диапазон

Исправление состоит в том, чтобы оставить order_date без преобразований и выразить условие как полуоткрытый диапазон. Теперь индекс по order_date может сразу перейти к началу 2024 года и остановиться на 2025 годе.

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

-- sargable: column stays bare
SELECT *
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date <  '2025-01-01';

Арифметика над столбцом

Та же проблема возникает при арифметических операциях. WHERE salary + bonus > 100000 и WHERE price * 0.9 < 50 вычисляют значение на основе столбца и блокируют использование индекса.

По возможности перенесите вычисления на сторону константы: перепишите price * 0.9 < 50 как price < 50 / 0.9. Литерал вычисляется один раз, а price остаётся без преобразований и может использоваться в индексе.

-- before: math on the column (non-sargable)
WHERE price * 0.9 < 50
-- after: math on the constant (sargable)
WHERE price < 50 / 0.9

Вариант поиска без учёта регистра

WHERE LOWER(email) = 'a@b.com' непригодно для поиска по обычному индексу на email, потому что сначала для каждой строки значение email переводится в нижний регистр.

Есть два исправления для рабочей системы: хранить нормализованную копию в нижнем регистре и индексировать её либо создать функциональный индекс по LOWER(email), чтобы индексировалось само выражение. Упоминание варианта с функциональным индексом показывает практический опыт.

-- functional index makes the expression sargable
CREATE INDEX idx_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';

Когда вычисление действительно необходимо

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

Поэтому выражение нужно либо повторить в WHERE, либо обернуть запрос во вложенный запрос или CTE и отфильтровать вычисляемый столбец во внешнем запросе.

SELECT *
FROM (
  SELECT *, revenue / NULLIF(visits, 0) AS rev_per_visit
  FROM stats
) t
WHERE t.rev_per_visit > 2.5;

Агрегаты помещаются в HAVING, а не в WHERE

Вычисление, являющееся агрегатным, вообще не может находиться в WHERE, поскольку WHERE фильтрует отдельные строки до группировки. WHERE SUM(amount) > 1000 вызывает ошибку.

Условия для агрегатов должны находиться в HAVING, которое выполняется после GROUP BY. Понимание того, какое предложение видит вычисление, — это самостоятельный частый вопрос о порядке выполнения.

SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

Как это проверяют на собеседовании

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

  • Определить, что функция над столбцом делает условие непригодным для поиска по индексу
  • Переписать условие так, чтобы столбец оставался без преобразований (использовать диапазон или вычисление со стороны константы)
  • Если переписывание невозможно, предложить функциональный индекс или сохранённый вычисляемый столбец

Упоминание EXPLAIN для подтверждения того, что план изменился с последовательного сканирования на сканирование индекса, завершает хороший ответ.

Понимание компромиссов

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

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

Функциональные индексы делают вычисление пригодным для индексного поиска

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

  • Тогда оптимизатор сможет использовать индекс, даже если функцию применяют к столбцу.
  • Выражение индекса должно в точности совпадать с выражением предиката.
-- index the expression you filter on
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- now this predicate stays sargable
SELECT * FROM users WHERE lower(email) = 'amy@example.com';

Быстрая проверка

Определите, какое условие оптимизатор может поддержать индексом.

Итоги

Основные выводы:

  • Условие пригодно для поиска по индексу, когда индексированный столбец находится без преобразований, а не внутри функции или арифметического выражения
  • Переписывайте YEAR(col) = 2024 как полуоткрытый диапазон; переносите вычисления на сторону константы
  • Для неизбежных выражений используйте функциональный индекс или сохранённый вычисляемый столбец
  • Нельзя использовать псевдоним из SELECT в WHERE; агрегаты помещаются в HAVING

Классическая задача — медленный запрос; классическое исправление — оставить столбец без преобразований.

Часто задаваемые вопросы

Урок «Фильтрация по вычисляемым значениям» бесплатный?

Да — полный текст урока «Фильтрация по вычисляемым значениям» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Фильтрация по вычисляемым значениям»?

Узнайте, почему функции над столбцами отключают использование индексов и как это проверяют на собеседованиях. Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Interview Prep?

Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.

Сколько времени занимает урок «Фильтрация по вычисляемым значениям»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Interview Prep?

Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Приоритет AND и OR и расстановка скобок
  2. BETWEEN, IN и включение границ
  3. LIKE, подстановочные символы и экранирование
  4. Фильтрация по вычисляемым значениям
← Назад к SQL Interview Prep