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

💣 Цепная реакция: почему ON DELETE CASCADE запрещают на проде В туториалах эту фичу хвалят: удалил пользователя, а база сама зачистила его заказы. На практике это мина, которая может незаметно стереть половину данных. ⚠️ В чем главная опасность 🔹 Невидимая угроза. Одна неточность в запросе: DELETE FROM users WHERE status = 'banned'; И каскад автоматически стирает связанные платежи, историю и профили. 🔹 Тишина в логах. Приложение даже не узнает, что база удалила еще сотни строк в других таблицах. Восстановить хронологию ошибки будет очень сложно. 🔹 Блокировки. Удаление одной строки тянет за собой скрытые проверки в десятках связанных таблиц, замедляя остальные операции. 🚀 Как делают правильно 🔸 Явное удаление. Сначала код приложения отдельными запросами удаляет зависимости, пишет подробные логи, и только потом удаляет самого пользователя. 🔸 Мягкое удаление (Soft Delete). Вместо DELETE строке просто меняют статус на удаленную: is_deleted = true. 🔸 Защита от ошибки. Внешние ключи настраивают с ON DELETE RESTRICT. База физически не даст удалить родительскую строку, пока существуют дочерние. 💡 Удобство автоматической зачистки не окупает риск потерять данные без следа — управляй удалением явно.

🤯 Парадокс 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. 💡 Ограничения базы строго судят неверные данные, но пасуют перед неизвестностью.

🗑 Удалили миллион строк, а место на диске не вернулось Ты радостно делаешь DELETE FROM logs, ждёшь освобождения диска, но ничего не происходит. Современные базы данных нас обманывают. ⚙️ Почему база ничего не удаляет 🔹 В PostgreSQL и MySQL работает механизм MVCC (многоверсионность). 🔹 При операции DELETE физического стирания нет. База просто ставит на строку невидимую метку «удалено». 🔹 Это как вычеркнуть запись ручкой в блокноте: прочитать её уже нельзя, но физическое место на странице она всё ещё занимает. 📉 Зачем нужны «мёртвые зоны» Ради безопасности и параллельной работы. Пока ты удаляешь записи, другой процесс может строить по ним отчёт. База заботливо хранит старую версию данных, пока все текущие запросы не завершатся. 🚀 Как реально освободить диск Встроенные фоновые процессы очищают старые метки и отдают место внутри таблицы для новых строк, но не возвращают его операционной системе. Чтобы ужать файл и вернуть гигабайты диску, нужны команды: 🔹 В PostgreSQL: VACUUM FULL logs; 🔹 В MySQL: OPTIMIZE TABLE logs; ⚠️ Осторожно: эти команды полностью блокируют таблицу на всё время своей работы. 💡 Операция DELETE только прячет данные, а реальное место возвращают лишь тяжёлые команды полного сжатия.

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

🕳️ Один 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 для фильтрации по подзапросам, чтобы не наступить на мину неявных пустых значений.

🧟‍♂️ Подстава Soft Delete: почему удалённый юзер ломает регистрацию Ты внедрил мягкое удаление (is_deleted = true). Пользователь удаляет аккаунт, а через месяц решает вернуться с тем же email. И тут регистрация падает с ошибкой! ⚙️ В чём проблема 🔹 Обычно на колонке email висит уникальный индекс, чтобы в базе не было клонов. 🔹 Но для базы «удалённый» юзер всё ещё существует. Строка физически никуда не делась, поэтому уникальный индекс блокирует новую регистрацию с этим же адресом. 🚀 Идеальное решение Можно усложнять код бэкенда, но лучше поручить эту задачу самой базе с помощью частичного индекса (Partial Index). Он будет следить за уникальностью только среди активных пользователей. CREATE UNIQUE INDEX active_users_email_idx ON users (email) WHERE is_deleted = false; 📈 Почему это круто 🔸 Вся логика защиты от дублей остаётся на уровне БД — никаких костылей в коде. 🔸 Индекс работает быстрее и занимает меньше места, так как просто игнорирует удалённые записи. 💡 Частичный индекс — самое изящное решение для Soft Delete, которое бережёт нервы и дисковое пространство.

🥷 Подстава от LIKE: почему 'user_1' находит чужие данные Все знают, что знак процента (%) в поиске заменяет любой кусок текста. Но многие забывают про скрытую угрозу — нижнее подчёркивание. ⚙️ В чём ловушка В SQL символ _ (underscore) — это тоже спецсимвол. Он означает «ровно один любой знак». Если ты попытаешься найти конкретного пользователя: SELECT * FROM users WHERE login LIKE 'user_1'; База радостно вернёт не только user_1, но и user-1, userA1 или user91. Подчёркивание сработает как джокер в колоде карт. 🚀 Как починить Чтобы база искала именно сам символ подчёркивания, его нужно экранировать с помощью оператора ESCAPE. SELECT * FROM users WHERE login LIKE 'user!_1' ESCAPE '!'; 🔹 Мы сами выбираем символ для экранирования (здесь это !). 🔹 Теперь комбинация !_ воспринимается базой как обычный текст, а не спецсимвол поиска. 💡 Всегда экранируй спецсимволы в LIKE, чтобы точный поиск не превращался в непредсказуемую лотерею.

🚀 Поздний JOIN: как ускорить OFFSET на больших данных в 10 раз Классическая пагинация через OFFSET сильно замедляет базу. Она читает тысячи тяжелых строк целиком только для того, чтобы их отбросить. 📉 В чём проблема 🔹 Представь поиск 100-й страницы в толстой энциклопедии. Вместо оглавления ты читаешь весь текст с первой страницы, пока не дойдёшь до сотой. 🔹 Так же делает база при OFFSET 100000: она собирает с диска все данные (длинные тексты, даты), отсчитывает ненужные сто тысяч строк и просто выбрасывает их. Это огромная трата ресурсов. 🚀 Решение — Поздний JOIN (Deferred Join) Сначала мы быстро находим нужные идентификаторы, а уже к ним присоединяем полные данные: SELECT p.* FROM ( SELECT id FROM products ORDER BY price LIMIT 50 OFFSET 100000 ) AS sub JOIN products p ON p.id = sub.id; ⚙️ Как это работает 🔸 Внутренний запрос работает как оглавление. Он бежит только по легкому индексу (цене и id), мгновенно пропуская 100 тысяч записей, и выдает 50 нужных id. 🔸 Внешний JOIN обращается к самой таблице и загружает тяжелые данные только для этих 50 финальных строк. 💡 Этот контринтуитивный трюк спасет твою пагинацию, когда таблица огромная, а отказаться от навигации по номерам страниц нельзя.

🔑 UUIDv4 тихо убивает твою базу UUID удобен: генерируешь на бэке, ключи уникальны, и никто не подсмотрит число заказов, поменяв цифру в URL. Кажется, идеальный Primary Key. Но обычный UUIDv4 — полностью случайный. А базе это очень не нравится. ⚙️ Почему так Индексы живут в B-Tree, а дереву нужен порядок. 🔹 BigInt с автоинкрементом → новая запись дописывается в конец. Мгновенно. 🔹UUIDv4 → случайный ID лезет в середину дерева. База рвёт заполненную страницу пополам и перетасовывает данные. Это и есть Page Split. 📉 Что происходит в продакшене Пока строк пара сотен тысяч — тишина. На миллионах начинается боль: 🔹 тормозят INSERT — CPU занят не записью, а перебалансировкой 🔹 фрагментация — страницы полупустые, индекс пухнет в разы 🔹 вымывание кэша — раздутый индекс не лезет в RAM, и база уходит на диск 🚀 Решение — UUIDv7 Отказываться от UUID не нужно. Просто бери седьмую версию. В начале строки — timestamp (время до миллисекунды), и только потом случайные биты. 🔹 Ключи всегда растут 🔹 Для базы это почти автоинкремент 🔹Записи ложатся в конец, фрагментация исчезает Скорость вставок остаётся ровной даже на таблицах в десятки гигабайт. 💡 Стартуешь проект? Закладывай UUIDv7 сразу.

CHECK-ограничения в MySQL: валидация прямо в таблице ✅ Когда вы хотите убедиться, что в таблицу попадают только корректные данные — не всегда нужно писать сложную логику в приложении. Иногда достаточно встроенного механизма MySQL: ограничения CHECK. 🔹 Что такое CHECK? Это правило, которое автоматически проверяет значение в колонке при вставке (INSERT) или обновлении (UPDATE). Если правило нарушено — операция отменяется с ошибкой. Пример: CREATE TABLE users ( id INT PRIMARY KEY, age INT, CHECK (age >= 0 AND age <= 120) ); Теперь нельзя вставить пользователя с возрастом -5 или 999 — MySQL просто не даст это сделать. Синтаксис CHECK 🔸 Можно добавлять при создании таблицы (CREATE TABLE) или позже (ALTER TABLE). 🔸 Можно использовать AND, OR, арифметику, сравнения, функции (с ограничениями). 🔸 Поддерживается в MySQL 8.0+. В старых версиях CHECK игнорировался (!). Пример с условием на строку: CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100), category ENUM('book', 'game', 'toy'), price DECIMAL(10,2), CHECK (price >= 0) ); Здесь мы запрещаем отрицательные цены. Добавление ограничения в существующую таблицу: ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0 AND age <= 120); Что произойдёт при нарушении? INSERT INTO users (id, age) VALUES (1, -10); -- ERROR 3819 (HY000): Check constraint 'chk_age' is violated. ⚠️ Важно помнить: 🔹CHECK срабатывает только для новых данных — старые строки не проверяются. 🔹Если значение NULL, правило обычно не нарушается (NULL считается «неизвестным» и не сравнивается напрямую). 🔹Ошибку можно перехватывать в приложении, чтобы сообщить пользователю. ✅ Когда стоит использовать CHECK: 🔹 Для ограничений: возраст, положительная цена, длина строки, формат (через LIKE или REGEXP). 🔹Когда хотите, чтобы в таблице «по умолчанию» всегда были только корректные данные. 🔹Чтобы упростить валидацию и сделать БД самодостаточной (например, при работе с внешними источниками данных).