SQL Academy: всё о реляционных БД и SQL

🕳️ Один NULL — и запрос пустой. Скрытая ловушка NOT IN Это классические грабли и частая задача на собеседованиях. Ты пишешь обычный фильтр, ожидаешь сотню строк, а база возвращает абсолютный ноль. ⚙️ Почему так происходит 🔹 Оператор NOT IN разворачивается базой в цепочку проверок: id != 1 AND id != 2 AND id != NULL. 🔹 В SQL любое сравнение с NULL даёт не False, а статус Unknown (неизвестно). 🔹 Логика базы строгая: True AND Unknown превращается в Unknown. Строка отбрасывается, так как условие не выполнилось на 100%. 📉 Как это выглядит в коде SELECT * FROM users WHERE id NOT IN ( SELECT banned_id FROM ban_list ); Если в таблице ban_list затесался хотя бы один NULL, запрос ничего не вернёт. База не может гарантировать, что пользователь чист, если один из банов «неизвестен». 🚀 Надёжное решение Используй NOT EXISTS. Этот оператор проверяет сам факт наличия связи, а не сравнивает значения напрямую. Пустые значения его не сломают: SELECT * FROM users u WHERE NOT EXISTS ( SELECT 1 FROM ban_list b WHERE b.banned_id = u.id ); 💡 Всегда выбирай NOT EXISTS для фильтрации по подзапросам, чтобы не наступить на мину неявных пустых значений.

👻 Забытые кавычки: почему одно число ломает индекс Ты ищешь пользователя по номеру телефона, пишешь простой запрос, а база внезапно задумывается на долгие секунды. Причина — всего лишь пропущенные кавычки. ⚙️ Как появляется ошибка Обычно номера телефонов хранят в строковых колонках (VARCHAR). Но при поиске разработчик может передать число без кавычек: SELECT * FROM users WHERE phone = 79991234567; 📉 Что происходит под капотом 🔹 База видит несовпадение типов: колонка — строка, а условие поиска — число. 🔹 Чтобы их сравнить, база решает неявно привести всю колонку к числу. По сути, выполняет скрытый CAST(phone AS BIGINT) для каждой строки. 🔹 Любое преобразование данных в колонке моментально отключает её индекс. 🔹 Это как переписать всю телефонную книгу в другой формат ради поиска одного абонента. Запрос уходит в долгое и тяжелое сканирование всей таблицы от начала до конца (Full Table Scan). 🚀 Как правильно 🔸 Всегда передавай строковые значения в кавычках, даже если они состоят только из цифр: SELECT * FROM users WHERE phone = '79991234567'; 💡 Неявное приведение типов — тихий убийца производительности, поэтому всегда следи за типами данных в условиях поиска.

🤯 Парадокс 3VL: почему WHERE блокирует NULL, а CHECK — радостно пропускает? Ты добавил условие CHECK (salary > 0), чтобы в базу не попадали кривые зарплаты. Но вдруг обнаруживаешь там пустые значения. Как так вышло? ⚙️ Трехзначная логика (3VL) В SQL кроме TRUE и FALSE есть состояние UNKNOWN (неизвестно). Если сравнить пустоту с нулем (NULL > 0), результат будет не ложь, а именно неизвестность. И механизмы базы реагируют на это по-разному. 🛡️ Двойные стандарты SQL 🔹 Фильтр WHERE — строгий охранник. Он возвращает строку, только если условие дало TRUE. Состояние UNKNOWN он воспринимает как FALSE и скрывает запись. 🔸 Валидация CHECK — ленивый вахтер. Она отклоняет запись, только если условие дало FALSE. Если результат UNKNOWN, ограничение пожимает плечами и пропускает значение в таблицу. CREATE TABLE emp ( salary INT CHECK (salary > 0) ); -- Запишется без ошибок! INSERT INTO emp (salary) VALUES (NULL); 🚀 Решение проблемы Одного CHECK бывает мало. Если поле не должно содержать пустоту, всегда страхуй его правилом NOT NULL. 💡 Ограничения базы строго судят неверные данные, но пасуют перед неизвестностью.

🧮 Математика с подвохом: почему сложение обнуляет зарплату, а SUM() — нет Считаешь итоговую выплату сотруднику как salary + bonus? Если премии нет (в базе лежит NULL), математика сыграет злую шутку, и человек останется вообще без денег в итоговом отчёте. ⚠️ Ловушка прямого сложения 🔹 NULL — это не ноль, а полная «неизвестность». 🔹 Любая математическая операция с неизвестностью заражает итог. Если оклад 1000, а бонус NULL, выражение 1000 + NULL вернёт пустоту (NULL). 🛡 Почему SUM() ведёт себя иначе 🔸 Агрегатные функции (SUM(), AVG()) созданы для работы с группами строк и спроектированы так, чтобы игнорировать пустые ячейки. 🔸 Если сделать SUM(bonus) для значений 100, 200 и NULL, функция просто перешагнёт через пустоту и спокойно выдаст 300. 🚀 Как спасти строчные вычисления Используй COALESCE — функцию, которая перехватит NULL и заменит его на запасной вариант. SELECT salary + COALESCE(bonus, 0) AS total_pay FROM employees; 💡 При прямом сложении колонок всегда страхуй необязательные поля нулём, чтобы пустота не съела твои данные.

🤡 Иллюзия оптимизации: индекс на is_active только вредит Кажется логичным: если в запросах часто есть WHERE is_active = true, на эту колонку нужен индекс. Но на деле база его проигнорирует, а ты лишь замедлишь работу системы. ⚙️ Почему база его не использует Индексу важно разнообразие значений (кардинальность). Представь предметный указатель в конце книги: если слово встречается на 90% страниц, проще пролистать всю книгу целиком. Оптимизатор рассуждает так же. Если значений всего два (true/false), он выберет полное сканирование таблицы (Seq Scan). Читать данные подряд намного быстрее, чем постоянно прыгать туда-сюда между индексом и таблицей. 📉 В чем реальный вред Проигнорированный индекс становится мертвым грузом: 🔹 При каждом INSERT, UPDATE и DELETE базе приходится тратить время на обновление этой ненужной структуры. 🔹 Индекс впустую съедает место на диске и вытесняет из оперативной памяти действительно полезные данные. 🚀 Что делать Если нужно быстро находить очень редкие статусы (например, 1% ошибок в логах), создавай частичный индекс: CREATE INDEX idx_errors ON logs(status) WHERE status = 'error'; Он займет минимум места и будет реально использоваться. 💡 Индекс полезен, только когда он отсеивает подавляющее большинство строк, а не делит их пополам.

🙈 Повесил индекс, а база его игнорит Ты создал индекс на колонку status, запускаешь поиск, а EXPLAIN нагло выдаёт Seq Scan. Кажется, база сломалась, но на самом деле она спасает твоё время. ⚙️ Почему это происходит Индекс работает как алфавитный указатель в конце книги. Он идеален, если тебе нужно найти 5 строк из миллиона. База быстро находит ссылки и точечно забирает данные. 📉 Когда Full Scan быстрее Если ты ищешь активных пользователей (например, is_active = true), а их в таблице 85%, индекс становится врагом оптимизатора. 🔹 Читать таблицу подряд (Full Scan) — это очень быстро. Как читать книгу страницу за страницей. 🔹 Использовать индекс для 85% строк — это сотни тысяч хаотичных прыжков туда-сюда за каждой отдельной строкой (Random I/O). Это слишком медленно, базе гораздо дешевле прочитать всё подряд и отфильтровать лишнее. 🚀 Что делать 🔸 Не строй индексы на колонки, где всего пара вариантов значений (статусы, пол, флаги). 🔸 Если часто ищешь редкие статусы (например, ошибки), используй частичный индекс: CREATE INDEX idx_errors ON users(status) WHERE status = 'error'; 💡 Индекс нужен для точечного поиска редких данных, а не для перекапывания всей таблицы.

🛑 Как Materialized View может повесить продакшен Ты создал материализованное представление, чтобы ускорить тяжёлые запросы. Но в момент обновления данных приложение вдруг замирает, а пользователи видят бесконечную загрузку. ⚙️ Почему всё зависло Обычная команда REFRESH MATERIALIZED VIEW работает очень грубо. 🔹 Она вешает эксклюзивную блокировку. База полностью запрещает чтение, пока собирает новые данные. Это как закрыть магазин на переучёт — внутрь никого не пускают. 🚀 Как обновить без даунтайма Чтобы отдавать пользователям старые данные, пока база в фоне строит новые, используй эту команду: REFRESH MATERIALIZED VIEW CONCURRENTLY my_report; ⚠️ Главный подвох Слово CONCURRENTLY не сработает просто так. Базе нужно точно понимать, как сопоставлять старые и новые строки при фоновом обновлении. 🔸 Обязательное условие: на вьюшке должен быть создан уникальный индекс (например, по ID). Без него запрос завершится с ошибкой. CREATE UNIQUE INDEX ON my_report (id); 💡 Уникальный индекс плюс обновление в фоне — и пользователи больше не увидят зависаний.

🪓 Убили индекс одной функцией: почему YEAR(date) тормозит базу Ты создал идеальный индекс по дате, но запрос всё равно работает целую вечность. Причина кроется всего в одной функции. ⚙️ Почему база игнорирует индекс 🔹 Как только ты пишешь WHERE YEAR(order_date) = 2023, база перестаёт видеть исходные значения дат. 🔹 Она не может искать по индексу (как по алфавитному указателю в книге), потому что ей нужно сначала вычислить год для каждой строки таблицы. 🔹 Итог — долгое полное сканирование (Seq Scan), даже если нужных строк всего пара штук. 🚀 Как починить (правило SARG) Чтобы индекс сработал, колонка должна оставаться «чистой» — без математики и функций. Перенеси все условия в правую часть от знака равенства. 🔸 Плохо: индекс сломается SELECT * FROM orders WHERE YEAR(order_date) = 2023; 🔸 Хорошо: моментальный поиск SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'; 💡 Оставляй проиндексированную колонку в одиночестве слева от условия, и база ответит моментально.

🪞 Иллюзия скорости: почему обычный VIEW не ускорит запросы Спрятал огромный JOIN в представление, чтобы база работала быстрее? Плохие новости: обычный VIEW вообще ничего не кэширует. ⚙️ Как это работает на самом деле Многие путают представление с физической таблицей. Но обычный VIEW — это просто текстовый макрос. 🔹 Каждый раз, когда ты делаешь SELECT * FROM my_view, база берёт исходный запрос и выполняет его с нуля. 🔹 Никакие результаты на диск не сохраняются. Все тяжёлые вычисления будут происходить заново при каждом обращении. ⚠️ Опасность «матрёшки» Самая частая ошибка — строить одни вьюшки поверх других. 🔸 База вынуждена разворачивать этот клубок в один гигантский запрос. 🔸 Планировщик запросов теряется в абстракциях и может выбрать худший план выполнения. То, что должно работать секунду, выполняется часами. 🚀 Что делать? 🔹 Для реального ускорения и сохранения результата на диск используй: CREATE MATERIALIZED VIEW my_cache AS SELECT ... 🔹 Обычный VIEW оставляй только для удобства: чтобы скрыть сложную логику от других разработчиков или упростить чтение кода. 💡 Обычное представление создано для чистоты кода, а не для производительности.