1. Введение

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

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

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

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

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

1. Rollback journals
2. Super-journals
3. Write-ahead Log (WAL) files
4. Shared-memory files
5. Statement journals
6. TEMP databases
7. Materializations of views and subqueries
8. Transient indices
9. Transient databases used by VACUUM

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

2.1. Журналы обратной перемотки

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

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

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

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

PRAGMA locking_mode=EXCLUSIVE;

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

Создание журнала и удаление также изменяются journal_mode pragma. Умолчанием является DELETE, который является поведением по умолчанию удаления файла журнала обратной перемотки в конце каждой транзакции, как описано выше. Режим журнала PERSIST предшествует удалению файла журнала и вместо этого переписывает заголовок журнала обратной перемотки нолями, что препятствует тому, чтобы другие процессы использовали журнал и таким образом имеет тот же самый эффект как удаление файла журнала, хотя без фактического удаления файла с диска. Другими словами, режим PERSIST показывает то же самое поведение, как в режиме блокировки EXCLUSIVE. Режим журнала OFF заставляет SQLite опускать журнал обратной перемотки полностью. Другими словами, никакой журнал обратной перемотки никогда не пишется, если режим журнала установлен в OFF. Режим журнала OFF отключает атомную передачу и возможности обратной перемотки SQLite. Команда ROLLBACK не доступна, когда режим журнала OFF установлен. И если катастрофа или потери мощности происходят посреди транзакции, которая использует режим журнала OFF, никакое восстановление невозможно, и файл базы данных, вероятно, будет поврежден. Режим журнала MEMORY заставляет журнал обратной перемотки быть сохраненным в памяти, а не на диске. Команда ROLLBACK все еще работает, когда режим журнала MEMORY, но потому что никакой файл не существует на диске для восстановления, катастрофа или потери мощности посреди транзакции, которая использует режим журнала 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(), который создал соединение с базой данных) с рандомизированным суффиксом. Файл супержурнала содержит названия всех различных приложенных вспомогательных баз данных, которые были изменены во время) транзакции. Мультитранзакция базы данных передается, когда файл супержурнала удален. Посмотрите подробности здесь.

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

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

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

Это означает, что трназакции SQLite не атомные через многократные файлы базы данных, когда файлы базы данных имеют synchronous = off или когда они используют режимы журнала OFF, MEMORY или WAL. Для synchronous = 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. Базы данных TEMP

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

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

Временные файлы, связанные с базой данных TEMP и ее журналом обратной перемотки, создаются только, если применение использует "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 ( version 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 pragma и размера временного файла.

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

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

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

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

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

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

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

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

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

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

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

5. Временные местоположения хранилища файлов

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

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

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

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

В Windows папки просмотрены в следующем порядке:

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

SQLite не обращает внимания на переменные окружения в этом случае, хотя по-видимому системный вызов GetTempPath() это делает. Алгоритм поиска отличается для сборки в CYGWIN. Проверьте исходный код для деталей.