Временные файлы SQLite: типы, назначение и оптимизация

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

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

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

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы.

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

Девять типов временных файлов SQLite

В настоящее время SQLite использует девять различных типов временных файлов:

• Журналы отката (Rollback journals)
• Супер-журналы (Super-journals)
• Файлы журнала с упреждающей записью (Write-ahead Log, WAL)
• Файлы общей памяти (Shared-memory files)
• Журналы операторов (Statement journals)
• Базы данных TEMP
• Материализации представлений и подзапросов
• Транзитные индексы (Transient indices)
• Транзитные базы данных, используемые VACUUM

Дополнительная информация о каждом из этих типов приведена далее.

Журналы отката

Журнал отката — это временный файл, который необходим для выполнения атомарной фиксации и отката транзакций в SQLite. Он располагается в каталоге файла базы данных и носит такое же имя, но с добавлением 8 символов -journal. Этот файл создается при первой транзакции и удаляется после ее успешной фиксации или отката. Без этого файла невозможен откат незавершенной транзакции, что может привести к повреждению базы данных в случае сбоя.

Журнал отката создаётся и удаляется в начале и конце транзакции, однако бывают исключения из этого правила.

Если во время транзакции происходит сбой или отключение, файл журнала сохраняется на диске. При следующем открытии базы данных SQLite его обнаружит и применит данные из этого журнала, чтобы вернуть базу данных в состояние, предшествующее незавершенной транзакции.

Если приложение переводит SQLite в режим эксклюзивной блокировки с помощью директивы:

PRAGMA locking_mode=EXCLUSIVE;

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

Создание и удаление журнала контролируется директивой journal_mode. В стандартном режиме DELETE файл удаляется после завершения каждой транзакции. В режиме PERSIST файл журнала сохраняется, но его заголовок обнуляется, что мешает откату другими процессами, создавая видимость его удаления.

WAL-файлы (журнал с упреждающей записью)

Файл с упреждающей записью (WAL) заменяет журнал отката в режиме WAL, обеспечивая атомарную фиксацию и откат. WAL-файл находится в том же каталоге, что и основная база данных, с тем же именем, плюс 4 символа -wal. Он создаётся при первом подключении к базе данных и обычно удаляется при закрытии последнего подключения, но может оставаться при некорректном завершении.

Файлы разделяемой памяти (-shm) в режиме WAL

При использовании режима WAL все подключения к базе данных должны совместно использовать память, служащую индексом для WAL-файла. Эта память чаще всего реализуется вызовом mmap() для файла разделяемой памяти, который располагается в том же каталоге и имеет то же имя, добавляя -shm. Файлы разделяемой памяти существуют только в режиме WAL.

Файл разделяемой памяти не содержит постоянных данных и служит для предоставления блоков памяти нескольким процессам, работающим с одной и той же базой данных в режиме WAL. Если VFS способен использовать альтернативный доступ к памяти, он будет выбран вместо разделяемого файла.

Файл разделяемой памяти существует параллельно с WAL-файлом, создаётся при его создании и удаляется при удалении. Во время восстановления содержимое WAL-файла используется для воссоздания файла разделяемой памяти.

Файлы супер-журнала

Файл супер-журнала (super-journal) используется как часть процесса атомарной фиксации, когда одна транзакция вносит изменения в несколько баз данных, добавленных к одному подключению к базе данных с помощью оператора ATTACH.

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

Транзакция с несколькими базами данных фиксируется в момент удаления файла супер-журнала. Дополнительные сведения см. в документации «Atomic Commit In SQLite».

Без супер-журнала фиксация транзакции в многобазовой транзакции была бы атомарной для каждой базы данных по отдельности, но не была бы атомарной для всех баз данных в совокупности. Иными словами, если бы фиксация была прервана в середине из-за сбоя или потери питания, изменения в одной из баз данных могли бы завершиться, тогда как изменения в другой базе данных могли бы быть откатаны.

Супер-журнал обеспечивает либо откат, либо совместную фиксацию всех изменений во всех базах данных.

Файл супер-журнала создаётся только для операций COMMIT, затрагивающих несколько файлов баз данных, где как минимум две из баз данных удовлетворяют всем следующим требованиям:

• База данных изменяется транзакцией
• Параметр PRAGMA synchronous не установлен в OFF
• Параметр PRAGMA journal_mode не установлен в OFF, MEMORY или WAL

Это означает, что транзакции SQLite не являются атомарными для нескольких файлов баз данных при потере питания, если для файлов баз данных отключена синхронизация или если они используют режимы журналирования OFF, MEMORY или WAL. При synchronous OFF и при journal_mode OFF и MEMORY база данных, как правило, будет повреждена, если фиксация транзакции прервана потерей питания.

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

Файлы журнала операторов

Файл журнала оператора (statement journal) используется для отката частичных результатов выполнения одного оператора в рамках более крупной транзакции. Например, предположим, что оператор UPDATE попытается изменить 100 строк в базе данных. Но после изменения первых 50 строк UPDATE сталкивается с нарушением ограничения, которое должно заблокировать весь оператор.

Журнал оператора используется для отмены изменений первых 50 строк, чтобы база данных была восстановлена в состояние, в котором она находилась в начале выполнения оператора.

Журнал оператора создаётся только для оператора UPDATE или INSERT, который может изменить несколько строк базы данных и который может столкнуться с ограничением или исключением RAISE внутри триггера и, таким образом, потребовать отмены частичных результатов.

Если UPDATE или INSERT не заключён в BEGIN…COMMIT и если нет других активных операторов для того же подключения к базе данных, журнал оператора не создаётся, поскольку вместо него может использоваться обычный журнал отката. Журнал оператора также не создаётся, если используется альтернативный алгоритм разрешения конфликтов. Например:

UPDATE OR FAIL ...
UPDATE OR IGNORE ...
UPDATE OR REPLACE ...
UPDATE OR ROLLBACK ...
INSERT OR FAIL ...
INSERT OR IGNORE ...
INSERT OR REPLACE ...
INSERT OR ROLLBACK ...
REPLACE INTO ....

Журналу оператора присваивается случайное имя, не обязательно в том же каталоге, что и основная база данных, и он автоматически удаляется по завершении транзакции. Размер журнала оператора пропорционален объёму изменений, реализованных оператором UPDATE или INSERT, который вызвал создание журнала оператора.

Базы данных TEMP

Таблицы, созданные с использованием синтаксиса CREATE TEMP TABLE, видны только тому подключению к базе данных, в котором оператор CREATE TEMP TABLE был первоначально выполнен. Эти TEMP-таблицы вместе со всеми связанными индексами, триггерами и представлениями совместно хранятся в отдельном временном файле базы данных, который создаётся сразу после обнаружения первого оператора CREATE TEMP TABLE.

Этот отдельный временный файл базы данных также имеет связанный журнал отката. Временный файл базы данных, используемый для хранения TEMP-таблиц, автоматически удаляется при закрытии подключения к базе данных с помощью sqlite3_close().

Файл базы данных TEMP очень похож на вспомогательные файлы баз данных, добавляемые с помощью оператора ATTACH, хотя и обладает рядом особых свойств. База данных TEMP всегда автоматически удаляется при закрытии подключения к базе данных. База данных TEMP всегда использует параметры PRAGMA synchronous=OFF и journal_mode=PERSIST.

Кроме того, база данных TEMP не может использоваться с DETACH, и другой процесс не может подключить базу данных TEMP с помощью ATTACH.

Временные файлы, связанные с базой данных TEMP и её журналом отката, создаются только в том случае, если приложение использует оператор CREATE TEMP TABLE.

Материализация представлений и подзапросов

Запросы, содержащие подзапросы, иногда должны вычислять подзапросы отдельно и сохранять результаты во временной таблице, а затем использовать содержимое этой временной таблицы для вычисления внешнего запроса. Это называется «материализацией» (materialization) подзапроса. Оптимизатор запросов в SQLite пытается избежать материализации, но иногда её нельзя легко избежать.

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

Подзапрос в правой части оператора IN зачастую должен быть материализован. Например:

SELECT * FROM ex1 WHERE ex1.a IN (SELECT b FROM ex2);

В приведённом выше запросе подзапрос SELECT b FROM ex2 вычисляется, а его результаты сохраняются во временной таблице (фактически во временном индексе), которая позволяет определить, существует ли значение ex2.b, с помощью простого бинарного поиска. После построения этой таблицы выполняется внешний запрос, и для каждой потенциальной строки результата проверяется, содержится ли ex1.a во временной таблице. Строка выводится только в том случае, если проверка даёт истинный результат.

Чтобы избежать создания временной таблицы, запрос можно переписать следующим образом:

SELECT * FROM ex1 WHERE EXISTS(SELECT 1 FROM ex2 WHERE ex2.b=ex1.a);

Последние версии SQLite (версия 3.5.4 от 2007-12-14 и более поздние) выполняют это преобразование автоматически, если существует индекс по столбцу ex2.b.

Если правая часть оператора IN представляет собой список значений, как в следующем примере:

SELECT * FROM ex1 WHERE a IN (1,2,3);

то они обрабатываются как подзапрос, который должен быть материализован. Иными словами, предыдущий оператор действует так, как если бы он был записан следующим образом:

SELECT * FROM ex1 WHERE a IN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3);

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

Подзапросы также могут нуждаться в материализации, когда они появляются в предложении FROM оператора SELECT. Например:

SELECT * FROM ex1 JOIN (SELECT b FROM ex2) AS t ON t.b=ex1.a;

В зависимости от запроса SQLite может потребоваться материализовать подзапрос (SELECT b FROM ex2) во временную таблицу, а затем выполнить соединение между ex1 и этой временной таблицей. Оптимизатор запросов пытается избежать этого путём «уплощения» (flattening) запроса. В предыдущем примере запрос может быть уплощён, и SQLite автоматически преобразует запрос в:

SELECT ex1.*, ex2.b FROM ex1 JOIN ex2 ON ex2.b=ex1.a;

Более сложные запросы могут или не могут использовать уплощение запроса для избежания временной таблицы. Возможность уплощения запроса зависит от таких факторов, как наличие или отсутствие агрегатных функций в подзапросе или внешнем запросе, наличие предложений ORDER BY или GROUP BY, предложений LIMIT и так далее. Правила, определяющие, когда запрос может и не может быть уплощён, весьма сложны и выходят за рамки данного документа.

Временные (транзитные) индексы

SQLite может использовать временные индексы (transient indices) для реализации таких возможностей языка SQL, как:

• Предложение ORDER BY или GROUP BY
• Ключевое слово DISTINCT в агрегатном запросе
• Составные операторы SELECT, объединённые с помощью UNION, EXCEPT или INTERSECT

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

SQLite стремится реализовывать предложения ORDER BY с использованием уже существующего индекса. Если подходящий индекс уже существует, SQLite будет обходить индекс, а не базовую таблицу, для извлечения запрошенной информации, тем самым обеспечивая вывод строк в нужном порядке.

Но если SQLite не может найти подходящий индекс, он вычислит запрос и сохранит каждую строку в транзитном индексе, данными которого являются данные строки, а ключом — термины ORDER BY. После вычисления запроса SQLite возвращается и обходит транзитный индекс от начала до конца, чтобы вывести строки в нужном порядке.

SQLite реализует GROUP BY путём упорядочивания выходных строк в порядке, определяемом термами GROUP BY. Каждая выходная строка сравнивается с предыдущей, чтобы определить, начинает ли она новую «группу». Упорядочивание по термам GROUP BY выполняется точно так же, как упорядочивание по термам ORDER BY. Если возможно, используется уже существующий индекс, но если подходящего индекса нет, создаётся транзитный индекс.

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

Оператор UNION для составных запросов реализуется путём создания транзитного индекса во временном файле и сохранения результатов левого и правого подзапросов в транзитном индексе с отбрасыванием дубликатов. После вычисления обоих подзапросов транзитный индекс обходится от начала до конца для формирования окончательного вывода.

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

Оператор INTERSECT для составных запросов реализуется путём создания двух отдельных транзитных индексов, каждый в отдельном временном файле. Левый и правый подзапросы вычисляются каждый в отдельный транзитный индекс. Затем два индекса обходятся совместно, и записи, присутствующие в обоих индексах, выводятся.

Обратите внимание, что оператор UNION ALL для составных запросов сам по себе не использует временные индексы (хотя, разумеется, правый и левый подзапросы UNION ALL могут использовать временные индексы в зависимости от того, как они составлены).

Временная база данных для команды VACUUM

Команда VACUUM работает путём создания временного файла и последующего перестроения всей базы данных в этот временный файл. Затем содержимое временного файла копируется обратно в исходный файл базы данных, а временный файл удаляется.

Временный файл, создаваемый командой VACUUM, существует только на протяжении выполнения самой команды. Размер временного файла не будет превышать размер исходной базы данных.

Параметр компиляции SQLITE_TEMP_STORE и pragma temp_store

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

То, записываются ли временные файлы, отличные от журналов отката, супер-журналов и журналов операторов, на диск или хранятся только в памяти, зависит от параметра компиляции SQLITE_TEMP_STORE, pragma temp_store и размера временного файла.

Параметр компиляции SQLITE_TEMP_STORE — это директива #define, значение которой является целым числом от 0 до 3 включительно. Значения параметра компиляции SQLITE_TEMP_STORE следующие:

• 0 — Временные файлы всегда хранятся на диске независимо от настройки pragma temp_store.
• 1 — Временные файлы по умолчанию хранятся на диске, но это поведение можно переопределить с помощью pragma temp_store.
• 2 — Временные файлы по умолчанию хранятся в памяти, но это поведение можно переопределить с помощью pragma temp_store.
• 3 — Временные файлы всегда хранятся в памяти независимо от настройки pragma temp_store.

Значение по умолчанию параметра компиляции SQLITE_TEMP_STORE равно 1, что означает хранение временных файлов на диске с возможностью переопределить это поведение с помощью pragma temp_store.

Pragma temp_store имеет целочисленное значение, которое также влияет на решение о том, где хранить временные файлы. Значения pragma temp_store имеют следующий смысл:

• 0 — Использовать дисковое или оперативное хранилище для временных файлов в соответствии с параметром компиляции SQLITE_TEMP_STORE.
• 1 — Если параметр компиляции SQLITE_TEMP_STORE указывает на хранение временных файлов в памяти, переопределить это решение и использовать дисковое хранилище. В противном случае следовать рекомендации параметра компиляции SQLITE_TEMP_STORE.
• 2 — Если параметр компиляции SQLITE_TEMP_STORE указывает на хранение временных файлов на диске, переопределить это решение и использовать хранилище в памяти. В противном случае следовать рекомендации параметра компиляции SQLITE_TEMP_STORE.

Значение по умолчанию для pragma temp_store равно 0, что означает следование рекомендации параметра компиляции SQLITE_TEMP_STORE.

Важно понимать: параметр компиляции SQLITE_TEMP_STORE и pragma temp_store влияют только на временные файлы, отличные от журнала отката и супер-журнала. Журнал отката и супер-журнал всегда записываются на диск независимо от настроек параметра компиляции SQLITE_TEMP_STORE и pragma temp_store.

Оптимизации временных файлов

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

Если SQLite необходимо использовать временный индекс или таблицу, а параметр компиляции SQLITE_TEMP_STORE и pragma temp_store настроены на хранение временных таблиц и индексов на диске, информация всё равно изначально хранится в памяти в кэше страниц. Временный файл не открывается и информация по-настоящему не записывается на диск до тех пор, пока кэш страниц не заполнится.

Это означает, что во многих распространённых случаях, когда временные таблицы и индексы невелики (достаточно малы, чтобы поместиться в кэш страниц), временные файлы не создаются и дисковый ввод-вывод не происходит. Только когда временные данные становятся слишком большими, чтобы поместиться в оперативной памяти, информация сбрасывается на диск.

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

Каждая временная таблица и индекс получают собственный кэш страниц, который может хранить максимальное количество страниц базы данных, определяемое параметром компиляции SQLITE_DEFAULT_TEMP_CACHE_SIZE. Значение по умолчанию — 500 страниц. Максимальное количество страниц базы данных в кэше страниц одинаково для каждой временной таблицы и индекса.

Это значение нельзя изменить во время выполнения или на уровне отдельной таблицы или индекса. Каждый временный файл получает собственный приватный кэш страниц с собственным ограничением SQLITE_DEFAULT_TEMP_CACHE_SIZE.

Расположение временных файлов

Каталог или папка, в которой создаются временные файлы, определяется VFS, специфичным для операционной системы.

На unix-подобных системах каталоги перебираются в следующем порядке:

1. Каталог, заданный с помощью PRAGMA temp_store_directory или глобальной переменной sqlite3_temp_directory
2. Переменная окружения SQLITE_TMPDIR
3. Переменная окружения TMPDIR
4. /var/tmp
5. /usr/tmp
6. /tmp
7. Текущий рабочий каталог (.)

На системах Windows папки перебираются в следующем порядке:

1. Папка, заданная с помощью PRAGMA temp_store_directory или глобальной переменной sqlite3_temp_directory
2. Папка, возвращаемая системным интерфейсом GetTempPath()

Типичные ошибки при работе с временными файлами SQLite

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

Отключение журнала отката без понимания последствий. Установка journal_mode=OFF ради производительности приводит к тому, что при сбое питания или падении процесса база данных с высокой вероятностью окажется повреждена. Это допустимо только для временных данных, которые можно восстановить из другого источника.

Ожидание атомарности при многобазовых транзакциях в режиме WAL. Разработчики нередко предполагают, что если каждая база данных использует WAL, то транзакция через ATTACH будет атомарной для всех файлов сразу. Это не так: супер-журнал не создаётся при journal_mode=WAL, и после сбоя часть файлов может оказаться в разных состояниях.

Игнорирование расположения временных файлов на встроенных системах. Если /tmp смонтирован в tmpfs с ограниченным размером, а временные таблицы или временные индексы вырастают за пределы кэша страниц, SQLite начнёт писать на диск и может исчерпать доступное пространство. Стоит явно задать PRAGMA temp_store_directory или использовать SQLITE_TEMP_STORE=3 при компиляции.

Неправильное понимание поведения PRAGMA locking_mode=EXCLUSIVE. При эксклюзивной блокировке журнал отката не удаляется после каждой транзакции — он лишь усекается или обнуляется. Это нормальное поведение, а не признак ошибки, однако оно удивляет тех, кто ожидает увидеть пустой каталог базы данных после фиксации.

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

Ответы на эти вопросы могут быть для вас полезными

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

Можно ли полностью избежать создания временных файлов на диске? Да. Если установить SQLITE_TEMP_STORE=3 при компиляции или выполнить PRAGMA temp_store=2 во время выполнения, SQLite будет хранить временные таблицы и индексы только в памяти. Журналы отката и супер-журналы при этом всё равно записываются на диск — их поведение этими настройками не контролируется.

Что такое «горячий журнал» и чем он опасен? Горячий журнал (hot journal) — это файл журнала отката, оставшийся на диске после аварийного завершения транзакции. При следующем открытии базы данных SQLite обнаружит его и автоматически выполнит восстановление. Опасность возникает, если горячий журнал удалить вручную: в этом случае база данных останется в повреждённом состоянии.

Когда стоит использовать режим WAL вместо режима DELETE? Режим WAL обеспечивает лучшую параллельность: читатели не блокируют писателей и наоборот. Он хорошо подходит для приложений с частыми чтениями и периодическими записями. Однако при использовании WAL появляются дополнительные файлы -wal и -shm, и транзакции через ATTACH не будут атомарными для нескольких файлов баз данных.

Как узнать, записывает ли SQLite временные данные на диск или держит их в памяти? Прямого API для этого нет. Косвенно можно ориентироваться на размер временных данных относительно SQLITE_DEFAULT_TEMP_CACHE_SIZE (по умолчанию 500 страниц). Если временная таблица или индекс умещаются в этот лимит, файл на диске не создаётся. Превышение лимита приводит к сбросу данных на диск.