Проверяем планы SQLite для iOS: составные индексы и лимит сканирования

В тестовой базе список заказов клиента открывается мгновенно, но после загрузки исторических данных начинает заметно тормозить. Когда студия ведёт несколько заказных проектов на облачном Mac, при приёмке лучше сначала проверить планы запросов на фиксированных данных, а не раз за разом менять машину и замерять время. Первый подход помогает выявить недостающие индексы, а второй легко позволяет принять прогретый кэш или колебания нагрузки за результат оптимизации. На примере одного запроса к заказам разберём весь процесс: от предварительной проверки в командной строке до повторной проверки через реальное подключение приложения iOS.

1. Сначала фиксируем контракт запроса, затем обсуждаем индексы

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

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

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

План запроса отражает выбор оптимизатора, а не представляет собой стабильный текстовый интерфейс. Не используйте посимвольное совпадение всего вывода как критерий прохождения проверки между версиями.

2. Создаём тестовый набор, который не затронет базу клиента

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

set -eu
work="$(mktemp -d)"
cat > "$work/check.sql" <<'SQL'
.bail on
SELECT sqlite_version();
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  created_at INTEGER NOT NULL
);
WITH RECURSIVE seq(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM seq WHERE x < 10000
)
INSERT INTO orders
SELECT x, x % 100,
       CASE WHEN x % 3 = 0 THEN 'done' ELSE 'open' END,
       x / 10
FROM seq;
EXPLAIN QUERY PLAN
SELECT id, created_at FROM orders
WHERE customer_id = 7 AND status = 'done'
ORDER BY created_at DESC, id DESC LIMIT 20;
CREATE INDEX orders_customer_status_time
ON orders(customer_id, status, created_at DESC, id DESC);
ANALYZE;
EXPLAIN QUERY PLAN
SELECT id, created_at FROM orders
WHERE customer_id = 7 AND status = 'done'
ORDER BY created_at DESC, id DESC LIMIT 20;
SQL
sqlite3 "$work/fixture.sqlite" < "$work/check.sql"
printf '%s\n' "$work"

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

3. Читаем путь доступа, а не заучиваем строки плана

Без индекса план обычно показывает сканирование таблицы заказов и создание временной структуры для сортировки. После добавления индекса проверьте, выполняется ли поиск по клиенту и статусу и нужна ли по-прежнему дополнительная сортировка. Конкретные формулировки зависят от версии SQLite, поэтому рассчитывать на неизменный вывод нельзя.

В составном индексе сначала идут поля условий равенства, затем поля сортировки. Это позволяет движку прочитать первые двадцать записей в нужном диапазоне. Индекс также покрывает все столбцы, используемые в этом примере запроса. Если позже добавить в выборку крупные поля, индекс может перестать быть покрывающим. Тогда проверку нужно повторить, а не продолжать ссылаться на старый отчёт.

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

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

4. Проверяем бюджет сканирования через реальное подключение

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

Если доступно низкоуровневое подключение, перед выполнением обнулите SQLITE_STMTSTATUS_FULLSCAN_STEP через sqlite3_stmt_status, а после выполнения прочитайте значение. Отдельно проверьте число операций сортировки через SQLITE_STMTSTATUS_SORT. Первый счётчик учитывает шаги, связанные с полным сканированием таблицы; второй показывает не время выполнения и не количество отсортированных строк.

Даже нулевые значения обоих счётчиков не означают, что запрос ничего не стоит. Обращений к индексу всё ещё может быть много. Дополнительно можно фиксировать SQLITE_STMTSTATUS_VM_STEP и оценивать его вместе с количеством возвращённых строк. Если обёртка не предоставляет доступ к дескриптору подготовленного выражения, сначала добавьте тестовый адаптер. Не выдавайте время из журналов приложения за статистику сканирования.

Бюджет должен опираться на проверенный базовый результат для фиксированного набора данных, а не на универсальный порог. Перед каждым измерением сбрасывайте счётчики, чтобы при повторном использовании prepared statement к текущему результату не прибавились значения предыдущего выполнения.

5. Включаем изменения распределения данных в приёмочные проверки

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

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

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

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