Временные файлы, используемые SQLite

1. Введение

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

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

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

2. Девять видов временных файлов

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

1. Журналы отката
2. Супер-журналы
3. Файлы журнала предзаписи (WAL)
4. Файлы разделяемой памяти
5. Журналы операторов
6. Временные базы данных
7. Материализации представлений и подзапросов
8. Временные индексы
9. Временные базы данных, используемые командой VACUUM

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

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

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

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

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

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

PRAGMA locking_mode=EXCLUSIVE;

SQLite создает новый журнал отката при начале первой транзакции в сеансе с режимом исключительной блокировки. Однако по завершении транзакции журнал отката не удаляется. Журнал отката может быть усечен или его заголовок может быть заполнен нулями (в зависимости от используемой версии SQLite), но сам журнал не удаляется. Журнал отката удаляется только при выходе из режима исключительного доступа.

Создание и удаление журнала отката также меняются в зависимости от параметра journal_mode. Режим ведения журнала по умолчанию — DELETE; в этом режиме файл журнала отката удаляется в конце каждой транзакции, как описано выше. В режиме журнала PERSIST файл журнала не удаляется: вместо этого заголовок журнала отката перезаписывается нулями. Это не позволяет другим процессам откатить журнал и дает тот же эффект, что и удаление файла журнала, но без затрат на фактическое удаление файла с диска. Иными словами, режим журнала PERSIST ведет себя так же, как режим исключительной блокировки EXCLUSIVE. Режим журнала OFF полностью отключает ведение журнала отката. Иными словами, если установлен режим журнала OFF, журнал отката никогда не записывается. Режим журнала OFF отключает возможности атомарной фиксации и отката SQLite. При установленном режиме журнала OFF команда ROLLBACK недоступна. Если во время транзакции, использующей режим журнала OFF, произойдет сбой или отключение питания, восстановление будет невозможно, и файл базы данных, скорее всего, будет поврежден. В режиме журнала MEMORY журнал отката хранится в памяти, а не на диске. Команда ROLLBACK продолжает работать в режиме журнала MEMORY, но поскольку на диске нет файла для восстановления, сбой или отключение питания в середине транзакции, использующей этот режим, скорее всего, приведет к повреждению базы данных.

2.2. Файлы журнала предзаписи (WAL)

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

2.3. Файлы разделяемой памяти

При работе в режиме WAL все подключения SQLite, связанные с одним и тем же файлом базы данных, должны совместно использовать некоторую область памяти, применяемую в качестве индекса WAL-файла. В большинстве реализаций эта разделяемая память создается вызовом mmap() для файла, созданного специально для этой цели: файла разделяемой памяти. Если такой файл существует, он находится в том же каталоге, что и файл базы данных, и имеет то же имя, что и файл базы данных, с добавленными в конце 4 символами «-shm». Файлы разделяемой памяти существуют только при работе в режиме WAL.

Файл разделяемой памяти не содержит сохраняемых данных. Его единственное назначение — предоставить блок разделяемой памяти для использования несколькими процессами, одновременно обращающимися к одной и той же базе данных в режиме WAL. Если VFS может предоставить альтернативный способ доступа к разделяемой памяти, вместо файла разделяемой памяти может использоваться этот способ. Например, если для PRAGMA locking_mode установлено значение EXCLUSIVE (то есть доступ к файлу базы данных может получить только один процесс), разделяемая память выделяется в куче, а не в файле разделяемой памяти, и такой файл никогда не создается.

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

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

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

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

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

1. База данных изменяется транзакцией
2. Значение PRAGMA synchronous не равно OFF
3. Значение PRAGMA journal_mode не равно OFF, MEMORY или WAL

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

2.5. Журналы операторов

Файл журнала оператора используется для отмены частичных результатов одного оператора в рамках более крупной транзакции. Например, предположим, что оператор 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, из-за которого журнал был создан.

2.6. Временные базы данных

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

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

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

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

Для запросов, содержащих подзапросы, иногда требуется отдельно вычислить подзапросы и сохранить результаты во временной таблице, а затем использовать содержимое временной таблицы для вычисления внешнего запроса. Это называется «материализацией» подзапроса. Оптимизатор запросов 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 с временной таблицей. Оптимизатор запросов старается избежать этого путем «уплощения» запроса. В предыдущем примере запрос можно уплощить, и SQLite автоматически преобразует его в

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

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

2.8. Временные индексы

SQLite может использовать временные индексы для реализации таких возможностей языка 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 могут использовать временные индексы в зависимости от их структуры).

2.9. Временная база данных, используемая командой VACUUM

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

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

3. Параметр времени компиляции SQLITE_TEMP_STORE и PRAGMA

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

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

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

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

Параметр temp_store имеет целочисленное значение, которое также влияет на выбор места хранения временных файлов. Значения параметра temp_store означают следующее:

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

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

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

4. Другие оптимизации временных файлов

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

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

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

5. Места хранения временных файлов

Каталог или папка, в которой создаются временные файлы, определяется зависящей от операционной системы 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().

Последнее обновление этой страницы: 2025-06-12 09:10:23Z