SQL-4 — Надёжность: индексы, транзакции, безопасность

Вы уже умеете писать SELECT с JOIN, считать агрегаты, группировать данные. Запросы работают. Кажется, всё готово.

Но между «работает у меня на ноутбуке» и «работает в продакшне» — пропасть. Запрос, который летал на ста строках, висит на миллионе. Сервер упал посреди перевода денег — баланс исчез. Кто-то ввёл в форму поиска строку, которая удалила вашу таблицу. Пользователь обновил приложение — оно упало, потому что схема базы расхилилась с кодом.

Это не гипотетические сценарии. Это реальные инциденты из реальных продакшн-систем. Сегодня закрываем все четыре темы: индексы, транзакции, SQL-инъекции, миграции.

Индексы

Без индекса база читает таблицу строка за строкой — от первой до последней. Это называется full table scan. На сотне строк незаметно. На миллионе — запрос висит секунды или минуты.

Индекс — это отдельная структура внутри базы, которая хранит отсортированные значения колонки со ссылками на нужные строки. Как оглавление в книге: не листаете всё подряд, а открываете сразу на нужной странице. Когда вы пишете WHERE email = 'ivan@example.com' — база идёт не в таблицу, а в индекс, находит там значение и прыгает сразу к нужной строке.

-- Обычный индекс по одной колонке
CREATE INDEX idx_users_email ON users(email);

-- Составной индекс — по нескольким колонкам
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

Имя лучше давать по схеме idx_таблица_колонка — потом не придётся гадать что это и зачем.

Составной индекс и порядок колонок. Индекс (user_id, created_at) работает как телефонная книга: данные отсортированы сначала по user_id, потом внутри каждого user_id по created_at. Найти всех пользователей с user_id = 42 — легко. Найти записи с user_id = 42 AND created_at > '2024-01-01' — тоже легко. Найти все строки с created_at > '2024-01-01' без указания user_id — бесполезно: значения дат разбросаны по всему индексу в порядке пользователей, глобальной сортировки по дате нет. Порядок колонок в составном индексе — не мелочь.

Когда создавать, когда нет:

Каждый индекс — это накладные расходы при записи: при каждом INSERT, UPDATE и DELETE база обновляет и сам индекс. Индексы «на всякий случай» замедляют запись и занимают место, не давая ничего взамен.

Room и индексы. Room не создаёт индексы автоматически — кроме первичного ключа. Хотите индекс на колонку — добавляете @Index в @Entity:

@Entity(
    tableName = "users",
    indices = [Index(value = ["email"], unique = true)]
)
data class User(
    @PrimaryKey val id: Int,
    val email: String,
    val name: String
)

unique = true дополнительно запрещает дублирование значений в колонке — Room бросит исключение при попытке вставить дубликат.

Транзакции

Пользователь переводит 1000 рублей с аккаунта А на аккаунт Б. Это два SQL-запроса: вычесть из A, прибавить к Б. Сервер упал после первого запроса — свет отключили, процесс убили, что угодно. Деньги списаны, но не зачислены. Баланс исчез.

Транзакция — это механизм, который говорит базе: «Эти запросы — одно неделимое целое. Либо все выполняются, либо ни один». Называется атомарность.

BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

COMMIT;

BEGIN запускает транзакцию. COMMIT подтверждает: всё прошло успешно, зафиксировать изменения. До COMMIT изменения существуют как черновик внутри транзакции — другие сессии их не видят. Если что-то пошло не так:

BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;

-- Возникла ошибка — откатываем всё:
ROLLBACK;

ROLLBACK — полный откат. Всё что было после BEGIN отменяется. База возвращается к исходному состоянию, как будто ничего не происходило. В коде ROLLBACK вызывается в блоке catch.

Что происходит при аварийном сбое — без явного ROLLBACK, просто выключили питание. Большинство баз данных используют WAL — Write-Ahead Log, журнал опережающей записи. Любое изменение сначала пишется в журнал, и только потом применяется к данным. При следующем старте база читает журнал: если видит BEGIN без COMMIT — сама откатывает незавершённую транзакцию. Консистентные данные без вашего участия.

В Android и бэкенд-фреймворках управлять транзакциями вручную не нужно:

// Room
db.runInTransaction {
    accountDao.debit(fromId, amount)
    accountDao.credit(toId, amount)
}

// Exposed
transaction {
    Accounts.update({ Accounts.id eq fromId }) { it[balance] = balance - amount }
    Accounts.update({ Accounts.id eq toId }) { it[balance] = balance + amount }
}

Форма разная, идея одна: оборачиваете несколько операций в один блок — фреймворк ставит BEGIN, COMMIT и ROLLBACK при исключении.

За транзакциями стоит концепция ACID — четыре свойства надёжной базы данных:

• Atomicity — транзакция выполняется целиком или не выполняется вообще
• Consistency — после транзакции данные не нарушают правила базы
• Isolation — параллельные транзакции не мешают друг другу
• Durability — результат сохранён даже после сбоя

Этот термин часто спрашивают на собеседованиях.

SQL-инъекции

Одна из самых известных уязвимостей — и при этом одна из самых легко предотвратимых.

Backend-разработчик написал вот такой код:

# Опасно! Никогда так не делать:
user_name = input("Введите имя: ")
query = "SELECT * FROM users WHERE name = '" + user_name + "'"
cursor.execute(query)

Пользователь вводит имя — разработчик склеивает строку и отправляет в базу. Выглядит просто. А теперь пользователь вводит ' OR '1'='1:

Ввод:     ' OR '1'='1

Запрос:   SELECT * FROM users WHERE name = '' OR '1'='1'

'1'='1' — условие, которое всегда истинно. WHERE срабатывает для каждой строки таблицы. Пользователь получил все данные без пароля и без авторизации.

Это мягкий вариант. Вот агрессивный — ввод '; DROP TABLE users; --:

Ввод:     '; DROP TABLE users; --

Запрос:   SELECT * FROM users WHERE name = ''; DROP TABLE users; --'

Апостроф закрывает строку, точка с запятой заканчивает первый запрос, -- комментирует хвост. База видит два запроса: SELECT и DROP TABLE users. Второй выполняется. Таблица удалена.

Почему это работает — SQL и данные пользователя смешались в одну строку. База не умеет отличить «это часть команды» от «это данные»: она видит один поток символов и выполняет его как SQL.

Защита — параметризованные запросы. SQL и данные передаются раздельными каналами: база сначала разбирает структуру запроса, потом подставляет значение уже как данные, никогда как команду. Что бы пользователь ни ввёл — это будет строковое значение, а не SQL-код.

# Python — параметризованный запрос
cursor.execute(
    "SELECT * FROM users WHERE name = ?",
    (user_name,)
)

// Kotlin / JDBC — PreparedStatement
val stmt = conn.prepareStatement("SELECT * FROM users WHERE name = ?")
stmt.setString(1, userName)
val result = stmt.executeQuery()

// Room — параметризация по умолчанию
@Query("SELECT * FROM users WHERE name = :name")
fun findByName(name: String): List<User>

В Room параметризация встроена — именованные параметры :name это и есть параметризованные запросы, конкатенации нет. В JDBC — PreparedStatement. В большинстве ORM — по умолчанию.

Конкатенация строк для передачи в базу данных — красный флаг, независимо от языка и платформы. Если видите склейку строк перед execute — меняйте немедленно.

Миграции схемы

Вы выпустили приложение, пользователи установили, данные накапливаются. Потом решаете добавить колонку last_seen_at в таблицу users.

Если просто изменить CREATE TABLE в коде — новые установки создадут базу правильно. Но у тех, кто уже установил приложение, таблица существует без этой колонки. Приложение обновилось, попытается туда писать — и упадёт. В Room это IllegalStateException: Migration didn't properly handle прямо при старте. Пользователь открывает приложение — получает краш.

Миграция — инструкция по изменению уже существующей базы. Не пересоздать с нуля, потеряв все данные, а точечно: добавить колонку, создать индекс, переименовать поле.

// Room: миграция с версии 1 на версию 2
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL(
            "ALTER TABLE users ADD COLUMN last_seen_at INTEGER"
        )
    }
}

// Регистрируем при построении БД
Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .addMigrations(MIGRATION_1_2)
    .build()

Объект Migration(1, 2) — цифры это версии схемы: «откуда» и «куда». Room при старте сравнивает версию в коде с версией в базе на устройстве и сам вызывает нужную миграцию. ALTER TABLE users ADD COLUMN last_seen_at INTEGER — добавляем колонку к уже существующей таблице.

На бэкенде — Flyway или Liquibase: папка с пронумерованными SQL-файлами, где каждый описывает одно изменение схемы. Инструмент сам отслеживает какие файлы уже применены и применяет новые при деплое.

Ограничение для Android: DROP COLUMN. В SQLite команда DROP COLUMN появилась только в версии 3.35. На старых Android-устройствах этой команды нет — придётся пересоздавать таблицу вручную:

-- Обходной путь для удаления колонки на старых версиях SQLite
ALTER TABLE users RENAME TO users_old;
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, name TEXT);
INSERT INTO users SELECT id, email, name FROM users_old;
DROP TABLE users_old;

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

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

Итого

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

Индексы ускоряют чтение — вместо перебора всех строк база прыгает сразу к нужной. Цена: замедление записи и место на диске. Создавайте точечно по колонкам в WHERE, ORDER BY, JOIN ON. В составном индексе порядок колонок важен — индекс работает слева направо. В Room — @Index в @Entity, сам не создаётся.

Транзакции гарантируют атомарность: несколько операций выполняются либо целиком, либо никак. BEGIN / COMMIT / ROLLBACK в SQL, runInTransaction в Room, transaction {} в Exposed. WAL-журнал обеспечивает откат даже при аварийном сбое.

SQL-инъекции возникают от конкатенации строк: данные пользователя смешиваются с SQL-кодом, база выполняет то, чего вы не планировали. Единственная защита — параметризованные запросы. SQL и данные идут раздельно, значение всегда остаётся данными.

Миграции — управляемая эволюция схемы. Схема в коде расходится со схемой в базе на устройстве — приложение падает. Миграции решают это версионированными инструкциями. Старые миграции не редактируют и не удаляют.

В следующем уроке переходим к продвинутым инструментам: FTS5 для быстрого полнотекстового поиска, Views для читаемых составных запросов и CTE — рекурсивные подзапросы, которые делают сложную логику понятной.