Устаревшие индексы выражений

1. Обзор

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

Чтобы индекс работал правильно, значение выражения не должно меняться, пока соответствующая строка таблицы остается неизменной. Поэтому в индексируемых выражениях должны использоваться детерминированные функции — функции, которые всегда возвращают один и тот же результат для одних и тех же входных данных. К детерминированным функциям относятся, например, "abs(X)" и "concat(X,Y)". К недетерминированным функциям относятся, например, "datetime('now')" и "random()".

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

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

• База данных SQLite переносится с одной архитектуры процессора на другую (например, с x86_64 на ARM);
• База данных SQLite переносится с одной операционной системы на другую (например, с Windows на Mac);
• Меняется версия библиотеки SQLite и/или внешней библиотеки, которую SQLite использует для реализации SQL-функций.

2. Детерминированные функции, которые не всегда детерминированы на 100%

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

1. Трансцендентные математические SQL-функции работают, вызывая соответствующие функции системной библиотеки C. Например, SQL-функция "sin(X)" просто вызывает функцию sin() из стандартной математической библиотеки C. Различные используемые сегодня математические библиотеки C удивительно согласованны. Тем не менее в стандартах языка C нет требования, чтобы sin(X) возвращала в разных системах в точности одно и то же значение. Достаточно, чтобы sin(X) возвращала число, «близкое» к правильному значению (с точностью до одной или двух единиц последнего разряда (ULP)), чтобы результат считался «правильным».

2. Предположим, вы используете функцию расширения ICU "lower()" и связываете ее с системной библиотекой libicu.so. Затем происходит обновление, в результате которого системная libicu.so заменяется более новой версией, возможно, использующей другую версию Unicode. Результаты функции lower() в редких крайних случаях могут немного измениться.

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

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

Примечательный пример случая 3 — обновление внутренних процедур преобразования текста в число с плавающей точкой в SQLite для повышения точности и/или производительности. Такое происходило, например, при переходе с версии 3.42 на 3.43, а затем с 3.51 на 3.52. Для принудительного преобразования текста в число с плавающей точкой явный "CAST(... AS REAL)" не требуется. Преобразование может происходить из-за автоматического приведения типа. Еще один распространенный «скрытый» случай преобразования текста в число с плавающей точкой возникает при использовании оператора ->> для извлечения значения с плавающей точкой из документа JSON. Предположим, вы хотите искать в большой коллекции документов JSON по диапазону значений "mtime" — числа с плавающей точкой, обозначающего количество секунд с начала эпохи Unix. Вы можете написать:

CREATE INDEX doc_mtime ON docstore(doc->>'mtime');

Если затем перейти на другую версию SQLite, в которой преобразование текста в число с плавающей точкой реализовано иначе, иногда значение выражения "doc->>'mtime'" может отличаться на одну ULP от значения, сохраненного в индексе.

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

3. Действительно ли устаревшие индексы выражений представляют проблему?

Устаревшие индексы выражений встречаются редко. Вряд ли вам когда-либо доведется столкнуться с таким индексом на практике.

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

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

Попытка удалить или обновить строку, для которой существует запись в устаревшем индексе выражений, в старых версиях SQLite приведет к ошибке (но не в SQLite 3.53.0 (2026-04-09) или более поздних версиях — см. ниже). Ошибки также будут обнаружены при запуске PRAGMA integrity_check. Однако такие проблемы легко устранить, выполнив REINDEX. Если пользователи когда-либо сталкивались с такими проблемами, они не сообщали о них разработчикам SQLite.

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

4. Самовосстанавливающиеся индексы выражений

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

Команда PRAGMA integrity_check по-прежнему сообщает в своем выводе об устаревших записях индекса. Однако в обычном случае, когда устаревшее значение — число с плавающей точкой, отличающееся всего на одну или две ULP, вместо сообщения о повреждении индекса команда PRAGMA integrity_check выводит сообщение вроде следующего:

index NAME stores an imprecise floating-point value for row N

5. Как избежать устаревших индексов выражений и/или исправить их

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

Пример индекса doc_mtime, приведенный выше, можно изменить примерно так:

CREATE TABLE doc(
  doc JSON,
  mtime AS (doc->>'mtime) STORED
);
CREATE INDEX doc_mtime ON doc(mtime);

Если у вас есть база данных, которую вы не проектировали сами, и вы хотите узнать, какие индексы являются индексами выражений, откройте базу данных в CLI (версии 3.53.0 или новее) и введите команду:

.indexes --expr

Если вы считаете, что у вас есть устаревшие индексы выражений (например, если вы знаете, что изменили ADF, используемую в выражении индекса), обновить все индексы можно командой REINDEX. Начиная с версии SQLite 3.53.0 (2026-04-09), можно сэкономить время и операции ввода-вывода, выполнив:

REINDEX EXPRESSIONS;

Команда REINDEX EXPRESSIONS перестраивает только индексы выражений, оставляя остальные индексы без изменений. Если в базе данных нет индексов выражений (что обычно и бывает), команда REINDEX EXPRESSIONS ничего не делает.

Последнее обновление этой страницы: 2026-03-30 16:10:33Z