Временные файлы, используемые 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.) Журнал отката всегда находится в той же директории, что и файл базы данных, и имеет то же имя, что и файл базы данных, за исключением приставки «-journal». Журнал отката обычно создаётся при первом запуске транзакции и обычно удаляется при фиксации или отмене транзакции. Файл журнала отката необходим для реализации атомарных возможностей фиксации и отката в SQLite. Без журнала отката SQLite не смог бы отменить неполную транзакцию, и если произошла ошибка или потеря питания во время транзакции, вся база данных, скорее всего, будет повреждена без журнала отката.

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

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

Если приложение помещает SQLite в режим эксклюзивного блокирования с помощью пragma:

PRAGMA locking_mode=EXCLUSIVE;

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

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

2.2. Журналы записи вперёд (WAL)

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

2.3. Файлы общей памяти

При работе в режиме WAL все подключения SQLite к одной базе данных должны использовать общую память, которая используется в качестве индекса для файла WAL. В большинстве реализаций эта общая память реализуется путём вызова mmap() для файла, созданного для этой единственной цели: файла общей памяти. Файл общей памяти, если он существует, находится в той же директории, что и файл базы данных, и имеет то же имя, что и файл базы данных, за исключением приставки «-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 не являются атомарными для нескольких файлов базы данных при отключении питания, когда файлы базы данных имеют выключенный синхронизм или когда они используют режимы журнала OFF, MEMORY или WAL. При синхронизме OFF и для journal_modes 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, и другой процесс не может подключиться к временной базе данных.

Временные файлы, связанные с временной базой данных и ее журналом отката, создаются только в том случае, если приложение использует инструкцию «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);

Список значений в правой части оператора IN обрабатывается как подзапрос, который должен быть материализован. Другими словами, предыдущее утверждение действует так, как если бы оно было:

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().

Эта страница была в последний раз изменена 8 января 2022 г. в 05:02:57 UTC